Sql Linked Server: exec

Showing posts with label exec. Show all posts

Wednesday, March 28, 2012

Linked Server problem when querying

I have a linked SQL Server on another machine which is created using a stored procedure that executes the following:

EXEC sp_addlinkedserver @.server = 'Achilles\Mixed', @.srvproduct = ' ', @.provider = 'SQLNCLI', @.datasrc = 'Archilles\Mixed', @.catalog = 'DB_INTRANET'

The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:

SELECT * FROM DB_INTRANET...Employees

I get the following error:

OLE DB provider "SQLNCLI" for linked server "DB_Intranet" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.

I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.

Does anyone have any suggestions please.

Hi,

the mapping of the logins did not work. What command did you use to use the WIndows login on the other server ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Please check two things:
1. make sure SQL 2005 allow remote access
2. make sure you set right userid/password for linked server.

Thanks

|||

Hi,

I have the same problem. But the distributed query seems to work on the management studio of the server but bot accross the network on other management studio with the same impersonated logins.

|||

I had to create a SQL authenticated login on the remote server (loginname) and then all local logins are mapped to the one remote SQL login using the Security page of the Linked Servers Properties dialogue.

I could not get it to pass Windows authenticated logins to the remote server at all.

In the Security Page of Linked Servers Properties dialogue.

For a Login not defined in the above list Connections will:

Be made using this security context:

Remote Login: Loginname

With Password: LoginPassword

Monday, March 26, 2012

Linked Server Performance

I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
DaveDoes usp_myproc only work with data on the remote server ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DaveF" <davef@.comcast.net> wrote in message
news:%23ekAsrpbDHA.3248@.tk2msftngp13.phx.gbl...
I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
Dave

Linked Server on SQL2000 to DB2 - desperate...

Hi All,
I am close before throwing the machine out of the window.
I tried to establish a linked server on our SQL2000 server pointing to a DB.

EXEC sp_addlinkedserver
@.server = 'DB2',
@.srvproduct = 'IBM OLE DB Provider for DB2 Servers',
@.provider = 'IBMDADB2',
@.provstr='DSN=JDE_PROD;UID=XXXX;PWD=XXX;DATABASE=J DE_PROD;'

EXEC sp_addlinkedsrvlogin 'DB2', false, NULL, 'XXX', 'XXXX'

If I query the LS using query analyzer or the Enterprise Manager both start
to hang, never responding again.
To be honest I am quite new to this topic but I read a lot and also tried a
lot ;)

Some articles talk about SNA Servers to be installed first, others about
some client tools.
I am confused by now...

Could anybody please be so kind and tell me how to connect the DB2 as a
linked server?
Is my statement wrong, and if so how should it look like? Why does the
application start to hang? Do I really need to install additional software
to do so?

Our DB2 expert also taked about software necessary to access the DB2
mainframe. Any ideas?

Thanks a lot in advance and belive me, any hints are highly appreciated ;)

Seize the Day

Andy

Andreas Bretl
andreas.bretl@.brainlab.com"Bretl Andy" <andreas.bretl@.brainlab.com> wrote in message
news:2rfnulF19390eU1@.uni-berlin.de...
> Hi All,
> I am close before throwing the machine out of the window.
> I tried to establish a linked server on our SQL2000 server pointing to a
> DB.
> EXEC sp_addlinkedserver
> @.server = 'DB2',
> @.srvproduct = 'IBM OLE DB Provider for DB2 Servers',
> @.provider = 'IBMDADB2',
> @.provstr='DSN=JDE_PROD;UID=XXXX;PWD=XXX;DATABASE=J DE_PROD;'
> EXEC sp_addlinkedsrvlogin 'DB2', false, NULL, 'XXX', 'XXXX'
> If I query the LS using query analyzer or the Enterprise Manager both
> start
> to hang, never responding again.
> To be honest I am quite new to this topic but I read a lot and also tried
> a
> lot ;)
> Some articles talk about SNA Servers to be installed first, others about
> some client tools.
> I am confused by now...
> Could anybody please be so kind and tell me how to connect the DB2 as a
> linked server?
> Is my statement wrong, and if so how should it look like? Why does the
> application start to hang? Do I really need to install additional software
> to do so?
> Our DB2 expert also taked about software necessary to access the DB2
> mainframe. Any ideas?
>
> Thanks a lot in advance and belive me, any hints are highly appreciated ;)
> Seize the Day
> Andy
> Andreas Bretl
> andreas.bretl@.brainlab.com

This explanation might clarify the question of installing SNA/HIS client
components vs the IBM client drivers:

http://groups.google.com/groups?hl=...%40cpmsftngxa07

Have you checked that you can successfully connect from the SQL Server
server to DB2 using some kind of DB2 client tool, or even just a script? If
that's OK, then at least you know the provider is installed and working, so
the issue is likely to be the linked server definition.

You might want to check the @.provstr parameter - the syntax is specific for
each OLE DB provider, so you should make sure that it's really a valid
string for the IBM provider. There's a sample here:

http://www.connectionstrings.com

Simon|||First of all, Thanks a lot Simon for the hint,
unfortunately I still can not connect to the db2. But now I have another
errormessage never seen before:
"The provider reported an unexpected catastrophic failure."
This seems to happen during inizialisation??
I used the following registration:
EXEC sp_addlinkedserver
@.server = 'DB2',
@.srvproduct = 'Microsoft OLE DB Provider for DB2',
@.catalog = 'JDE_PROD',
@.provider = 'IBMDADB2',
@.provstr='Provider=IBMDADB2;Database=JDE_PROD;HOST NAME=xxxx;PROTOCOL=TCPIP;P
ORT=5000;uid=xxxx;pwd=xxxx;'

If I read the article correctly it should work using the DSN pointing to the
db2 which is installed on the SQL server.
But as mentioned, the error above occures or it seems that the
EnterpriseManager hangs when accessing tabes in the linked-server.

Thanks a lot Simon,

Seize the Day

Andy

Andreas Bretl
andreas.bretl@.brainlab.com|||Bretl,

If you want to use the IBM provider then you need to have IBM's DB2 Connect
product installed on the SQL Server.

If you want to use the MS provider, then you need to have Host Integration
Server 2004 (latest version on SNA Server) installed on the SQL Server as a
"nodeless" install - this is basically a server install without choosing to
install the SNA Server service.

Do you have either of these pre-requisites installed and tested first?

(If you go the MS route it's worth posting future Q's in the Host Integration
Server newsgroup on the MS public servers - you're likely to get better help
there)

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or http://www.ntfaq.com/Articles/Index...epartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||First of all thanks to all who tried heling me with this problem.
Actually it seemes to be a problem with the current fix-pack installation on
the JDE Database-Driver (DB2-ODBC).
Maybe it was damaged or really out of date so it could not communicate with
our SQL Server 2000.
After installing the current fixpack I was able to implement linked-server
over ODBC without problem.

What a hassle when they do not keep their system installation up to date.

Thanks again and have a geat week,

Andy Bretl
andreas.bretl@.brainlab.com

Friday, March 23, 2012

linked server msdb stored procedures

exec [ltd-gln-db2].msdb..sp_help_job
exec [ltd-gln-db2].msdb..sp_help_alert
Neither line fails.
The first returns 0 rows.
The second returns > 0 rows.
Both stored procedures allow 'public' to 'exec'.
Question: Why does sp_help_job return no rows?
Note: SQLServer 2000 SP2 on all db servers.Hi
Are there any jobs on the remote system?
As sp_help_job does not call sp_help_alert therefore I am not sure why you
should think sp_help_job would return anything if sp_help_alert does?
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
>|||
>--Original Message--
>Hi
>Are there any jobs on the remote system?
>As sp_help_job does not call sp_help_alert therefore I am
not sure why you
>should think sp_help_job would return anything if
sp_help_alert does?
>John
>"Larry Storm" <larry.storm@.ltd.org> wrote in message
>news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
>
>.
>When I am connected to the remote system, sp_help_job
displays info for multiple jobs.
The reason I included information for sp_help_alert was to
show the remote server is accessable as a linked server and
permissions on stored procedures allow sp_help_alert to
return results.
My question remains:
Why does sp_help_job return zero rows?|||Hi
Try profiling the procedure and then see what the steps do when you log in
locally as that user.
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:1a0001c5392e$f9f0e4c0$a401280a@.phx.gbl...
>
> not sure why you
> sp_help_alert does?
> displays info for multiple jobs.
> The reason I included information for sp_help_alert was to
> show the remote server is accessable as a linked server and
> permissions on stored procedures allow sp_help_alert to
> return results.
> My question remains:
> Why does sp_help_job return zero rows?
>
>|||Larry Storm wrote:
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
From BOL: "A user who is not a member of the sy

min fixed role can use sp_
help_job to view only the jobs he/she owns."
HTH,
Igor

Friday, March 9, 2012

Linked Server and Distributed Query

I have a linked SQL Server on another machine which is created using a stored procedure that executes the following:

EXEC sp_addlinkedserver @.server = 'Achilles\Mixed', @.srvproduct = ' ', @.provider = 'SQLNCLI', @.datasrc = 'Archilles\Mixed', @.catalog = 'DB_INTRANET'

The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:

SELECT * FROM DB_INTRANET...Employees

I get the following error:

OLE DB provider "SQLNCLI" for linked server "DB_Intranet" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.

I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.

Does anyone have any suggestions please.

Hi,

the mapping of the logins did not work. What command did you use to use the WIndows login on the other server ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I am a user with windows authenticated login on both servers. I can login to both using SQL Server management Studio and have full rights on all databases. I did use any command to use windows login as I though it would be the default. Do I need to execute a command to use windows logon. It is a mixed mode server but I login OK with windows authentication using the client tools.

Thanks for your help.

Wednesday, March 7, 2012

Linked Server Active Directory (ADSI) Error

Background: MS SQL 2000 SP3 Member Server in Active Directory Domain. Added
Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services
2.5',
'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this security
context' and set it to the domain administrator account. The linked server
object is created successfully howeve:
1. When I click on either the Tables or Views I get this error: ' Error
7301: Could not obtain a required interface from OLE DB Provider
'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
AND
2. When I try to run and OpenQuery in the Query Analyzer I get this error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
The query I am running is formatted as:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE objectCategory
= "person" AND objectClass = "user"')
Go
Thanks for any and all help.the error may be caused by syntax errors in your query. Try this:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Richard
"TCALL" <TCALL@.discussions.microsoft.com> wrote in message
news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
> Background: MS SQL 2000 SP3 Member Server in Active Directory Domain.
> Added
> Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory
> Services
> 2.5',
> 'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this
> security
> context' and set it to the domain administrator account. The linked
> server
> object is created successfully howeve:
> 1. When I click on either the Tables or Views I get this error: ' Error
> 7301: Could not obtain a required interface from OLE DB Provider
> 'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
> IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
> AND
> 2. When I try to run and OpenQuery in the Query Analyzer I get this
> error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare
> returned 0x80040e14].
> The query I am running is formatted as:
> SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE
> objectCategory
> = "person" AND objectClass = "user"')
> Go
>
> Thanks for any and all help.
>|||Thanks Richard I used the query you provided unfortunately I received the
same Msg 7621 error I listed below.
Regards.
TCALL
"Richard Ding" wrote:
> the error may be caused by syntax errors in your query. Try this:
> SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> ''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
> objectCategory
> = ''person'' AND objectClass = ''user''')
>
> Richard
> "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
> > Background: MS SQL 2000 SP3 Member Server in Active Directory Domain.
> > Added
> > Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory
> > Services
> > 2.5',
> > 'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this
> > security
> > context' and set it to the domain administrator account. The linked
> > server
> > object is created successfully howeve:
> >
> > 1. When I click on either the Tables or Views I get this error: ' Error
> > 7301: Could not obtain a required interface from OLE DB Provider
> > 'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
> > IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
> >
> > AND
> >
> > 2. When I try to run and OpenQuery in the Query Analyzer I get this
> > error:
> > Server: Msg 7321, Level 16, State 2, Line 1
> > An error occurred while preparing a query for execution against OLE DB
> > provider 'ADSDSOObject'.
> > OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> > ICommandPrepare::Prepare
> > returned 0x80040e14].
> >
> > The query I am running is formatted as:
> > SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> > LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE
> > objectCategory
> > = "person" AND objectClass = "user"')
> > Go
> >
> >
> > Thanks for any and all help.
> >
>
>|||I do not believe you can use both a named server (the IP, in this case) and
the DC arguements. One or the other should suffice.
Here is a query we run all the time, with or without a linked server.
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,'User ID=;Password=;ADSI Flag=0x11;Page Size=10000'
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://DC=CBSH,DC=COM''
WHERE objectClass = ''organizationalPerson''
AND mail = ''*''
AND extensionAttribute3 <> ''9*''
AND extensionAttribute3 > ''1''
AND extensionAttribute3 <> ''***-*''
AND extensionAttribute3 <> ''n*''
'
)
Sincerely,
Anthony Thomas
"TCALL" <TCALL@.discussions.microsoft.com> wrote in message
news:3B83DA10-E3C9-45F8-9F0C-2111F003EA82@.microsoft.com...
Thanks Richard I used the query you provided unfortunately I received the
same Msg 7621 error I listed below.
Regards.
TCALL
"Richard Ding" wrote:
> the error may be caused by syntax errors in your query. Try this:
> SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> ''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
> objectCategory
> = ''person'' AND objectClass = ''user''')
>
> Richard
> "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
> > Background: MS SQL 2000 SP3 Member Server in Active Directory Domain.
> > Added
> > Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory
> > Services
> > 2.5',
> > 'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this
> > security
> > context' and set it to the domain administrator account. The linked
> > server
> > object is created successfully howeve:
> >
> > 1. When I click on either the Tables or Views I get this error: ' Error
> > 7301: Could not obtain a required interface from OLE DB Provider
> > 'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
> > IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
> >
> > AND
> >
> > 2. When I try to run and OpenQuery in the Query Analyzer I get this
> > error:
> > Server: Msg 7321, Level 16, State 2, Line 1
> > An error occurred while preparing a query for execution against OLE DB
> > provider 'ADSDSOObject'.
> > OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> > ICommandPrepare::Prepare
> > returned 0x80040e14].
> >
> > The query I am running is formatted as:
> > SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> > LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE
> > objectCategory
> > = "person" AND objectClass = "user"')
> > Go
> >
> >
> > Thanks for any and all help.
> >
>
>|||I received this error when I tried your suggestion:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'AdsDsoObject'.
Any ideas on what I am missing?
"AnthonyThomas" wrote:
> I do not believe you can use both a named server (the IP, in this case) and
> the DC arguements. One or the other should suffice.
> Here is a query we run all the time, with or without a linked server.
> SELECT *
> FROM OPENROWSET(
> 'AdsDsoObject'
> ,'User ID=;Password=;ADSI Flag=0x11;Page Size=10000'
> ,'SELECT mail
> ,ExtensionAttribute3
> ,SamAccountName
> FROM ''LDAP://DC=CBSH,DC=COM''
> WHERE objectClass = ''organizationalPerson''
> AND mail = ''*''
> AND extensionAttribute3 <> ''9*''
> AND extensionAttribute3 > ''1''
> AND extensionAttribute3 <> ''***-*''
> AND extensionAttribute3 <> ''n*''
> '
> )
>
> Sincerely,
>
> Anthony Thomas
>
> --
> "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> news:3B83DA10-E3C9-45F8-9F0C-2111F003EA82@.microsoft.com...
> Thanks Richard I used the query you provided unfortunately I received the
> same Msg 7621 error I listed below.
> Regards.
> TCALL
> "Richard Ding" wrote:
> > the error may be caused by syntax errors in your query. Try this:
> >
> > SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> > ''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
> > objectCategory
> > = ''person'' AND objectClass = ''user''')
> >
> >
> > Richard
> >
> > "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> > news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
> > > Background: MS SQL 2000 SP3 Member Server in Active Directory Domain.
> > > Added
> > > Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory
> > > Services
> > > 2.5',
> > > 'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this
> > > security
> > > context' and set it to the domain administrator account. The linked
> > > server
> > > object is created successfully howeve:
> > >
> > > 1. When I click on either the Tables or Views I get this error: ' Error
> > > 7301: Could not obtain a required interface from OLE DB Provider
> > > 'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
> > > IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
> > >
> > > AND
> > >
> > > 2. When I try to run and OpenQuery in the Query Analyzer I get this
> > > error:
> > > Server: Msg 7321, Level 16, State 2, Line 1
> > > An error occurred while preparing a query for execution against OLE DB
> > > provider 'ADSDSOObject'.
> > > OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> > > ICommandPrepare::Prepare
> > > returned 0x80040e14].
> > >
> > > The query I am running is formatted as:
> > > SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> > > LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE
> > > objectCategory
> > > = "person" AND objectClass = "user"')
> > > Go
> > >
> > >
> > > Thanks for any and all help.
> > >
> >
> >
> >
>
>|||Have you found a solution to your problem?
I am having similar issues. I try to add a linked server using
EXEC sp_addlinkedserver
'ADSI',
'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
go
sp_addlinkedSrvlogin 'ADSI',false, 'sa','username','password'
go
but when I try to run a query from QA or view tables from EM i get similar
error messages.
"Sonya" wrote:
> I received this error when I tried your suggestion:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'AdsDsoObject'.
> Any ideas on what I am missing?
> "AnthonyThomas" wrote:
> > I do not believe you can use both a named server (the IP, in this case) and
> > the DC arguements. One or the other should suffice.
> >
> > Here is a query we run all the time, with or without a linked server.
> >
> > SELECT *
> > FROM OPENROWSET(
> > 'AdsDsoObject'
> > ,'User ID=;Password=;ADSI Flag=0x11;Page Size=10000'
> > ,'SELECT mail
> > ,ExtensionAttribute3
> > ,SamAccountName
> >
> > FROM ''LDAP://DC=CBSH,DC=COM''
> > WHERE objectClass = ''organizationalPerson''
> > AND mail = ''*''
> > AND extensionAttribute3 <> ''9*''
> > AND extensionAttribute3 > ''1''
> > AND extensionAttribute3 <> ''***-*''
> > AND extensionAttribute3 <> ''n*''
> > '
> > )
> >
> >
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> > news:3B83DA10-E3C9-45F8-9F0C-2111F003EA82@.microsoft.com...
> > Thanks Richard I used the query you provided unfortunately I received the
> > same Msg 7621 error I listed below.
> >
> > Regards.
> >
> > TCALL
> >
> > "Richard Ding" wrote:
> >
> > > the error may be caused by syntax errors in your query. Try this:
> > >
> > > SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> > > ''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
> > > objectCategory
> > > = ''person'' AND objectClass = ''user''')
> > >
> > >
> > > Richard
> > >
> > > "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> > > news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
> > > > Background: MS SQL 2000 SP3 Member Server in Active Directory Domain.
> > > > Added
> > > > Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory
> > > > Services
> > > > 2.5',
> > > > 'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this
> > > > security
> > > > context' and set it to the domain administrator account. The linked
> > > > server
> > > > object is created successfully howeve:
> > > >
> > > > 1. When I click on either the Tables or Views I get this error: ' Error
> > > > 7301: Could not obtain a required interface from OLE DB Provider
> > > > 'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
> > > > IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
> > > >
> > > > AND
> > > >
> > > > 2. When I try to run and OpenQuery in the Query Analyzer I get this
> > > > error:
> > > > Server: Msg 7321, Level 16, State 2, Line 1
> > > > An error occurred while preparing a query for execution against OLE DB
> > > > provider 'ADSDSOObject'.
> > > > OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> > > > ICommandPrepare::Prepare
> > > > returned 0x80040e14].
> > > >
> > > > The query I am running is formatted as:
> > > > SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> > > > LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE
> > > > objectCategory
> > > > = "person" AND objectClass = "user"')
> > > > Go
> > > >
> > > >
> > > > Thanks for any and all help.
> > > >
> > >
> > >
> > >
> >
> >
> >

Wednesday, March 28, 2012

Linked Server problem when querying

Monday, March 26, 2012

Linked Server Performance

Linked Server on SQL2000 to DB2 - desperate...

Friday, March 23, 2012

linked server msdb stored procedures

Friday, March 9, 2012

Linked Server and Distributed Query

Wednesday, March 7, 2012

Linked Server Active Directory (ADSI) Error

Linked Server Active Directory (ADSI) Error

Linked Server Active Directory (ADSI) Error

Sql Linked Server

Blog Archive

About Me