Im having problems with linked servers on one particular computer and am out
of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a linke
d
server of any type, clicking on "tables" or "views" in the enterprise manage
r
list, gives the following error:
Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initialize
returned 0x80004005: ].
This particular LSN is pointing to another SQL server machine. Regardless
of what provider i choose, i get the same error (the error message changes
the provider based on the provider of the LSN).
I am an administrator on both boxes, have disabled virus protection, the
windows firewall on the XP box is disabled (by a group policy on the domain)
,
and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
LSN's created on other computers work fine. Any ideas?You can get this error with timeout issues. Refer to the
following:
Error message when you execute a linked server query in SQL
Server: "Timeout Expired"
http://support.microsoft.com/?id=314530
You can also double check the security mappings for the
linked servers.
You will also want to check the connectivity - try pinging
the other servers from the box you are having problems with.
Ping by server name and IP.
-Sue
On Wed, 15 Mar 2006 06:56:28 -0800, John Kurtis <John
Kurtis@.discussions.microsoft.com> wrote:
>Im having problems with linked servers on one particular computer and am ou
t
>of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a link
ed
>server of any type, clicking on "tables" or "views" in the enterprise manag
er
>list, gives the following error:
>Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
>OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initializ
e
>returned 0x80004005: ].
>This particular LSN is pointing to another SQL server machine. Regardless
>of what provider i choose, i get the same error (the error message changes
>the provider based on the provider of the LSN).
>I am an administrator on both boxes, have disabled virus protection, the
>windows firewall on the XP box is disabled (by a group policy on the domain
),
>and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
>LSN's created on other computers work fine. Any ideas?|||Thanks for the reply. However im 99% sure that its not a timeout issue. I
get the error immediately after i click on "tables" or "views" in enterprise
manager.
Pings from the box that is having problems come back fine, both with IP as
well as name.
Showing posts with label sp3. Show all posts
Showing posts with label sp3. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
linked server problem on SQL Server 2000 sp3
Hello
im trying to connect to a remote server over an internet vpn connection
i asked admin of the remote location to open port 1433 in his firewall, he
did and im able to register the server successfully by IP address on
enterprise manager
i can also make a connection with query analyzer with no problem and select
from tables normally
however when i try to establish a linked server using OLEDB for SQL server ,
connection fails , properties of the connection is ,
product name: empty
data source: the IP address of the server
provider string and catalog: empty
security: no rows in local server login to remote server login mapping but
chose last option in
for a login not defined in the list above connection will: i chose
be made using this security context and i wrote sa and password i used
before in registering server as a test
what is wrong , does linking a server require opening additional ports in
firewall ' which query analyzer does not need if server is registered
normally not linked?
any help very appreciated
BassamBassam (bassam@.nptco.com.eg) writes:
> im trying to connect to a remote server over an internet vpn connection
> i asked admin of the remote location to open port 1433 in his firewall,
> he did and im able to register the server successfully by IP address on
> enterprise manager i can also make a connection with query analyzer with
> no problem and select from tables normally
> however when i try to establish a linked server using OLEDB for SQL
> server , connection fails , properties of the connection is ,
> product name: empty
> data source: the IP address of the server
> provider string and catalog: empty
And the error message is?
You could consider defining a provider string for the linked server. That's
all I can suggest without knowledge about the error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Error msg is :
SQL Server does not exist or access denied.
i tried to define a provider string also by format
srever=ip address;database=xx;user id=xx ; password=xx
but did not work either
question is do i have to open port UDP 1434 also in the firewall ?
Regards
Bassam
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760ACD558EC9Yazorman@.127.0.0.1...
> Bassam (bassam@.nptco.com.eg) writes:
> And the error message is?
> You could consider defining a provider string for the linked server.
> That's
> all I can suggest without knowledge about the error.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Bassam (bassam@.nptco.com.eg) writes:
> Error msg is :
> SQL Server does not exist or access denied.
> i tried to define a provider string also by format
> srever=ip address;database=xx;user id=xx ; password=xx
> but did not work either
> question is do i have to open port UDP 1434 also in the firewall ?
I don't think that would matter.
Is SQL Server running on the same machine as you run QA/EM from? If not,
maybe the remote DBA only opened the firewall for one IP address?
Under which Windows account do SQL Server run? You can check this in
Computer Management/Services. If SQL Server is running under LocalSystem
try changing it to domain account, and restart SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
im trying to connect to a remote server over an internet vpn connection
i asked admin of the remote location to open port 1433 in his firewall, he
did and im able to register the server successfully by IP address on
enterprise manager
i can also make a connection with query analyzer with no problem and select
from tables normally
however when i try to establish a linked server using OLEDB for SQL server ,
connection fails , properties of the connection is ,
product name: empty
data source: the IP address of the server
provider string and catalog: empty
security: no rows in local server login to remote server login mapping but
chose last option in
for a login not defined in the list above connection will: i chose
be made using this security context and i wrote sa and password i used
before in registering server as a test
what is wrong , does linking a server require opening additional ports in
firewall ' which query analyzer does not need if server is registered
normally not linked?
any help very appreciated
BassamBassam (bassam@.nptco.com.eg) writes:
> im trying to connect to a remote server over an internet vpn connection
> i asked admin of the remote location to open port 1433 in his firewall,
> he did and im able to register the server successfully by IP address on
> enterprise manager i can also make a connection with query analyzer with
> no problem and select from tables normally
> however when i try to establish a linked server using OLEDB for SQL
> server , connection fails , properties of the connection is ,
> product name: empty
> data source: the IP address of the server
> provider string and catalog: empty
And the error message is?
You could consider defining a provider string for the linked server. That's
all I can suggest without knowledge about the error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Error msg is :
SQL Server does not exist or access denied.
i tried to define a provider string also by format
srever=ip address;database=xx;user id=xx ; password=xx
but did not work either
question is do i have to open port UDP 1434 also in the firewall ?
Regards
Bassam
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760ACD558EC9Yazorman@.127.0.0.1...
> Bassam (bassam@.nptco.com.eg) writes:
> And the error message is?
> You could consider defining a provider string for the linked server.
> That's
> all I can suggest without knowledge about the error.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Bassam (bassam@.nptco.com.eg) writes:
> Error msg is :
> SQL Server does not exist or access denied.
> i tried to define a provider string also by format
> srever=ip address;database=xx;user id=xx ; password=xx
> but did not work either
> question is do i have to open port UDP 1434 also in the firewall ?
I don't think that would matter.
Is SQL Server running on the same machine as you run QA/EM from? If not,
maybe the remote DBA only opened the firewall for one IP address?
Under which Windows account do SQL Server run? You can check this in
Computer Management/Services. If SQL Server is running under LocalSystem
try changing it to domain account, and restart SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
linked server problem
Hi,
I have server A and B with SQL ent sp3 / Win Adv sp4. On server A I created
a linked server to B, I also selected Collation compatible. My problem,
It is ok to run on Server A (returning records instantly):
select * from b.master.dbo.sysdatabases
But it hangs when I run this on A:
begin tran
select * from b.master.dbo.sysdatabases
commit
I have checked MSDTC is running on both servers.
Did I miss anything?
Thanks.
When you say "It is ok to run on Server A (returning records instantly)",
does that mean,
on ServerB, if you run
select * from a.master.dbo.sysdatabases
it returns records instantly?
If so, and the problem is when you run
select * from b.master.dbo.sysdatabases
OR
begin tran
select * from b.master.dbo.sysdatabases
commit
your best bet would be to,
1. make a connection directly to Server B and run
begin tran
select * from master.dbo.sysdatabases
commit
-- And see if that hangs.
If it doesnt, and it seems teh problem is with Linked servers, then you
might want to look at sysprocesses and see waht the spid is doing - is it
sleeping, waiting on a resource, etc.
Also, when you say it "hangs", what's the longest you've waited for?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
I have server A and B with SQL ent sp3 / Win Adv sp4. On server A I created
a linked server to B, I also selected Collation compatible. My problem,
It is ok to run on Server A (returning records instantly):
select * from b.master.dbo.sysdatabases
But it hangs when I run this on A:
begin tran
select * from b.master.dbo.sysdatabases
commit
I have checked MSDTC is running on both servers.
Did I miss anything?
Thanks.
When you say "It is ok to run on Server A (returning records instantly)",
does that mean,
on ServerB, if you run
select * from a.master.dbo.sysdatabases
it returns records instantly?
If so, and the problem is when you run
select * from b.master.dbo.sysdatabases
OR
begin tran
select * from b.master.dbo.sysdatabases
commit
your best bet would be to,
1. make a connection directly to Server B and run
begin tran
select * from master.dbo.sysdatabases
commit
-- And see if that hangs.
If it doesnt, and it seems teh problem is with Linked servers, then you
might want to look at sysprocesses and see waht the spid is doing - is it
sleeping, waiting on a resource, etc.
Also, when you say it "hangs", what's the longest you've waited for?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Linked Server problem
I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
run a query from Server A on Server B but if I do it the other way I get an
error
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
Why am I getting this error? I can see the Servers entry in Enterprise
Manager in the Linked servers container.I would like to add that this problem started when I moved eevrything from
Server Z to Server A (all system and user databases) and the old name of
server Z was not retained. Earlier the link between Server Z and Server B was
working fine in both the direstions. The sa login and the password is till
the same.
"sharman" wrote:
> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> run a query from Server A on Server B but if I do it the other way I get an
> error
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> Why am I getting this error? I can see the Servers entry in Enterprise
> Manager in the Linked servers container.|||Did you use sp_dropserver and sp_addserver, LOCAL after the move?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>I would like to add that this problem started when I moved eevrything from
> Server Z to Server A (all system and user databases) and the old name of
> server Z was not retained. Earlier the link between Server Z and Server B was
> working fine in both the direstions. The sa login and the password is till
> the same.
> "sharman" wrote:
>> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> run a query from Server A on Server B but if I do it the other way I get an
>> error
>> Server: Msg 17, Level 16, State 1, Line 1
>> SQL Server does not exist or access denied.
>> Why am I getting this error? I can see the Servers entry in Enterprise
>> Manager in the Linked servers container.|||Which server do I have to do this? Is this to be done on the new server on
which I moved all the system and user databases from the old server? Thanks.
"Tibor Karaszi" wrote:
> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >I would like to add that this problem started when I moved eevrything from
> > Server Z to Server A (all system and user databases) and the old name of
> > server Z was not retained. Earlier the link between Server Z and Server B was
> > working fine in both the direstions. The sa login and the password is till
> > the same.
> >
> > "sharman" wrote:
> >
> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> run a query from Server A on Server B but if I do it the other way I get an
> >> error
> >>
> >> Server: Msg 17, Level 16, State 1, Line 1
> >> SQL Server does not exist or access denied.
> >>
> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> Manager in the Linked servers container.
>|||Yep. On the new server, SQL Server think its name is the old servers name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> Which server do I have to do this? Is this to be done on the new server on
> which I moved all the system and user databases from the old server? Thanks.
> "Tibor Karaszi" wrote:
>> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >I would like to add that this problem started when I moved eevrything from
>> > Server Z to Server A (all system and user databases) and the old name of
>> > server Z was not retained. Earlier the link between Server Z and Server B was
>> > working fine in both the direstions. The sa login and the password is till
>> > the same.
>> >
>> > "sharman" wrote:
>> >
>> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> error
>> >>
>> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> SQL Server does not exist or access denied.
>> >>
>> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> Manager in the Linked servers container.
>>|||Thanks for the reply. I did only the following after the move:
Change the name of the originating server in the field originating_server_id
on sysjobs table in msdb database to the new server because it still had the
name of the old server. I was not able to edit change job settings but after
the change I was able to.
Now I will also run sp_dropserver and sp_addserver, LOCAL
Is there anything else that I am forgetting that might cause a problem later
on? Thanks in advance.
"Tibor Karaszi" wrote:
> Yep. On the new server, SQL Server think its name is the old servers name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> > Which server do I have to do this? Is this to be done on the new server on
> > which I moved all the system and user databases from the old server? Thanks.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >I would like to add that this problem started when I moved eevrything from
> >> > Server Z to Server A (all system and user databases) and the old name of
> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> > working fine in both the direstions. The sa login and the password is till
> >> > the same.
> >> >
> >> > "sharman" wrote:
> >> >
> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> error
> >> >>
> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> SQL Server does not exist or access denied.
> >> >>
> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> Manager in the Linked servers container.
> >>
> >>
>|||I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
article on the subject, but I believe you covered what has to be done already:
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> Thanks for the reply. I did only the following after the move:
> Change the name of the originating server in the field originating_server_id
> on sysjobs table in msdb database to the new server because it still had the
> name of the old server. I was not able to edit change job settings but after
> the change I was able to.
> Now I will also run sp_dropserver and sp_addserver, LOCAL
> Is there anything else that I am forgetting that might cause a problem later
> on? Thanks in advance.
> "Tibor Karaszi" wrote:
>> Yep. On the new server, SQL Server think its name is the old servers name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
>> > Which server do I have to do this? Is this to be done on the new server on
>> > which I moved all the system and user databases from the old server? Thanks.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >> >I would like to add that this problem started when I moved eevrything from
>> >> > Server Z to Server A (all system and user databases) and the old name of
>> >> > server Z was not retained. Earlier the link between Server Z and Server B was
>> >> > working fine in both the direstions. The sa login and the password is till
>> >> > the same.
>> >> >
>> >> > "sharman" wrote:
>> >> >
>> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> >> error
>> >> >>
>> >> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> >> SQL Server does not exist or access denied.
>> >> >>
>> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> >> Manager in the Linked servers container.
>> >>
>> >>
>>|||I did as you have mentioned in the post and I also ran
sp_serveroption '<server_name>','data access','true'
Even after running these I get the following error when I try to parse a
query in an ExecuteSQL task in DTS that is connected to the this server ( The
DTS is on another server):
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
exist or access denied.
"Tibor Karaszi" wrote:
> I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
> article on the subject, but I believe you covered what has to be done already:
> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> > Thanks for the reply. I did only the following after the move:
> >
> > Change the name of the originating server in the field originating_server_id
> > on sysjobs table in msdb database to the new server because it still had the
> > name of the old server. I was not able to edit change job settings but after
> > the change I was able to.
> >
> > Now I will also run sp_dropserver and sp_addserver, LOCAL
> >
> > Is there anything else that I am forgetting that might cause a problem later
> > on? Thanks in advance.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Yep. On the new server, SQL Server think its name is the old servers name.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> >> > Which server do I have to do this? Is this to be done on the new server on
> >> > which I moved all the system and user databases from the old server? Thanks.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >> >I would like to add that this problem started when I moved eevrything from
> >> >> > Server Z to Server A (all system and user databases) and the old name of
> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> >> > working fine in both the direstions. The sa login and the password is till
> >> >> > the same.
> >> >> >
> >> >> > "sharman" wrote:
> >> >> >
> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> >> error
> >> >> >>
> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> >> SQL Server does not exist or access denied.
> >> >> >>
> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> >> Manager in the Linked servers container.
> >> >>
> >> >>
> >>
> >>
>|||I suggest you first logon to the local server from a query window and execute the statement that
accesses the linked server. This to simplify the scenario and try to localize where the problem
might be.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
>I did as you have mentioned in the post and I also ran
> sp_serveroption '<server_name>','data access','true'
> Even after running these I get the following error when I try to parse a
> query in an ExecuteSQL task in DTS that is connected to the this server ( The
> DTS is on another server):
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
> exist or access denied.
> "Tibor Karaszi" wrote:
>> I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
>> article on the subject, but I believe you covered what has to be done already:
>> http://www.karaszi.com/SQLServer/info_change_server_name.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
>> > Thanks for the reply. I did only the following after the move:
>> >
>> > Change the name of the originating server in the field originating_server_id
>> > on sysjobs table in msdb database to the new server because it still had the
>> > name of the old server. I was not able to edit change job settings but after
>> > the change I was able to.
>> >
>> > Now I will also run sp_dropserver and sp_addserver, LOCAL
>> >
>> > Is there anything else that I am forgetting that might cause a problem later
>> > on? Thanks in advance.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Yep. On the new server, SQL Server think its name is the old servers name.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
>> >> > Which server do I have to do this? Is this to be done on the new server on
>> >> > which I moved all the system and user databases from the old server? Thanks.
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >> >> >I would like to add that this problem started when I moved eevrything from
>> >> >> > Server Z to Server A (all system and user databases) and the old name of
>> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
>> >> >> > working fine in both the direstions. The sa login and the password is till
>> >> >> > the same.
>> >> >> >
>> >> >> > "sharman" wrote:
>> >> >> >
>> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> >> >> error
>> >> >> >>
>> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> >> >> SQL Server does not exist or access denied.
>> >> >> >>
>> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> >> >> Manager in the Linked servers container.
>> >> >>
>> >> >>
>> >>
>> >>
>>|||It is only when I try to parse a query in an ExecuteSQL task in DTS that is
connected to this server that I get an error.
When I run a select statement I do not get any error. Thanks.
"Tibor Karaszi" wrote:
> I suggest you first logon to the local server from a query window and execute the statement that
> accesses the linked server. This to simplify the scenario and try to localize where the problem
> might be.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
> >I did as you have mentioned in the post and I also ran
> > sp_serveroption '<server_name>','data access','true'
> >
> > Even after running these I get the following error when I try to parse a
> > query in an ExecuteSQL task in DTS that is connected to the this server ( The
> > DTS is on another server):
> >
> > Error Source: Microsoft OLE DB Provider for SQL Server
> > Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
> > exist or access denied.
> >
> > "Tibor Karaszi" wrote:
> >
> >> I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
> >> article on the subject, but I believe you covered what has to be done already:
> >> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> >> > Thanks for the reply. I did only the following after the move:
> >> >
> >> > Change the name of the originating server in the field originating_server_id
> >> > on sysjobs table in msdb database to the new server because it still had the
> >> > name of the old server. I was not able to edit change job settings but after
> >> > the change I was able to.
> >> >
> >> > Now I will also run sp_dropserver and sp_addserver, LOCAL
> >> >
> >> > Is there anything else that I am forgetting that might cause a problem later
> >> > on? Thanks in advance.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Yep. On the new server, SQL Server think its name is the old servers name.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> >> >> > Which server do I have to do this? Is this to be done on the new server on
> >> >> > which I moved all the system and user databases from the old server? Thanks.
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >> >> >I would like to add that this problem started when I moved eevrything from
> >> >> >> > Server Z to Server A (all system and user databases) and the old name of
> >> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> >> >> > working fine in both the direstions. The sa login and the password is till
> >> >> >> > the same.
> >> >> >> >
> >> >> >> > "sharman" wrote:
> >> >> >> >
> >> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> >> >> error
> >> >> >> >>
> >> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> >> >> SQL Server does not exist or access denied.
> >> >> >> >>
> >> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> >> >> Manager in the Linked servers container.
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>|||So you have some query in a DTS ExecuteSQL task. If you take that query and run it from Query
Analyzer it executed OK. But executed from the ExecuteSQL task it fails. If so, you need to look at
the properties if the ExecuteSQL task. Is the servername correctly specified? Login attributes? You
might want to continue this discussion in a DTS group (I'm no DTS expert myself).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:6B13D344-B6A2-43AA-84B8-50489A9245B6@.microsoft.com...
> It is only when I try to parse a query in an ExecuteSQL task in DTS that is
> connected to this server that I get an error.
> When I run a select statement I do not get any error. Thanks.
> "Tibor Karaszi" wrote:
>> I suggest you first logon to the local server from a query window and execute the statement that
>> accesses the linked server. This to simplify the scenario and try to localize where the problem
>> might be.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
>> >I did as you have mentioned in the post and I also ran
>> > sp_serveroption '<server_name>','data access','true'
>> >
>> > Even after running these I get the following error when I try to parse a
>> > query in an ExecuteSQL task in DTS that is connected to the this server ( The
>> > DTS is on another server):
>> >
>> > Error Source: Microsoft OLE DB Provider for SQL Server
>> > Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
>> > exist or access denied.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I think the two should take care of it, unless you are in a cluster or do replication. Here's
>> >> ay
>> >> article on the subject, but I believe you covered what has to be done already:
>> >> http://www.karaszi.com/SQLServer/info_change_server_name.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
>> >> > Thanks for the reply. I did only the following after the move:
>> >> >
>> >> > Change the name of the originating server in the field originating_server_id
>> >> > on sysjobs table in msdb database to the new server because it still had the
>> >> > name of the old server. I was not able to edit change job settings but after
>> >> > the change I was able to.
>> >> >
>> >> > Now I will also run sp_dropserver and sp_addserver, LOCAL
>> >> >
>> >> > Is there anything else that I am forgetting that might cause a problem later
>> >> > on? Thanks in advance.
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Yep. On the new server, SQL Server think its name is the old servers name.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
>> >> >> > Which server do I have to do this? Is this to be done on the new server on
>> >> >> > which I moved all the system and user databases from the old server? Thanks.
>> >> >> >
>> >> >> > "Tibor Karaszi" wrote:
>> >> >> >
>> >> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >> >>
>> >> >> >>
>> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >> >> >> >I would like to add that this problem started when I moved eevrything from
>> >> >> >> > Server Z to Server A (all system and user databases) and the old name of
>> >> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
>> >> >> >> > working fine in both the direstions. The sa login and the password is till
>> >> >> >> > the same.
>> >> >> >> >
>> >> >> >> > "sharman" wrote:
>> >> >> >> >
>> >> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> >> >> >> error
>> >> >> >> >>
>> >> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> >> >> >> SQL Server does not exist or access denied.
>> >> >> >> >>
>> >> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> >> >> >> Manager in the Linked servers container.
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>|||Thanks Tibor for all your help. I'll certainly post this in the DTS group.
"Tibor Karaszi" wrote:
> So you have some query in a DTS ExecuteSQL task. If you take that query and run it from Query
> Analyzer it executed OK. But executed from the ExecuteSQL task it fails. If so, you need to look at
> the properties if the ExecuteSQL task. Is the servername correctly specified? Login attributes? You
> might want to continue this discussion in a DTS group (I'm no DTS expert myself).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:6B13D344-B6A2-43AA-84B8-50489A9245B6@.microsoft.com...
> > It is only when I try to parse a query in an ExecuteSQL task in DTS that is
> > connected to this server that I get an error.
> >
> > When I run a select statement I do not get any error. Thanks.
> >
> > "Tibor Karaszi" wrote:
> >
> >> I suggest you first logon to the local server from a query window and execute the statement that
> >> accesses the linked server. This to simplify the scenario and try to localize where the problem
> >> might be.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
> >> >I did as you have mentioned in the post and I also ran
> >> > sp_serveroption '<server_name>','data access','true'
> >> >
> >> > Even after running these I get the following error when I try to parse a
> >> > query in an ExecuteSQL task in DTS that is connected to the this server ( The
> >> > DTS is on another server):
> >> >
> >> > Error Source: Microsoft OLE DB Provider for SQL Server
> >> > Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
> >> > exist or access denied.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> I think the two should take care of it, unless you are in a cluster or do replication. Here's
> >> >> ay
> >> >> article on the subject, but I believe you covered what has to be done already:
> >> >> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> >> >> > Thanks for the reply. I did only the following after the move:
> >> >> >
> >> >> > Change the name of the originating server in the field originating_server_id
> >> >> > on sysjobs table in msdb database to the new server because it still had the
> >> >> > name of the old server. I was not able to edit change job settings but after
> >> >> > the change I was able to.
> >> >> >
> >> >> > Now I will also run sp_dropserver and sp_addserver, LOCAL
> >> >> >
> >> >> > Is there anything else that I am forgetting that might cause a problem later
> >> >> > on? Thanks in advance.
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Yep. On the new server, SQL Server think its name is the old servers name.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> >> >> >> > Which server do I have to do this? Is this to be done on the new server on
> >> >> >> > which I moved all the system and user databases from the old server? Thanks.
> >> >> >> >
> >> >> >> > "Tibor Karaszi" wrote:
> >> >> >> >
> >> >> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >> >> >> >I would like to add that this problem started when I moved eevrything from
> >> >> >> >> > Server Z to Server A (all system and user databases) and the old name of
> >> >> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> >> >> >> > working fine in both the direstions. The sa login and the password is till
> >> >> >> >> > the same.
> >> >> >> >> >
> >> >> >> >> > "sharman" wrote:
> >> >> >> >> >
> >> >> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> >> >> >> error
> >> >> >> >> >>
> >> >> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> >> >> >> SQL Server does not exist or access denied.
> >> >> >> >> >>
> >> >> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> >> >> >> Manager in the Linked servers container.
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
run a query from Server A on Server B but if I do it the other way I get an
error
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
Why am I getting this error? I can see the Servers entry in Enterprise
Manager in the Linked servers container.I would like to add that this problem started when I moved eevrything from
Server Z to Server A (all system and user databases) and the old name of
server Z was not retained. Earlier the link between Server Z and Server B was
working fine in both the direstions. The sa login and the password is till
the same.
"sharman" wrote:
> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> run a query from Server A on Server B but if I do it the other way I get an
> error
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> Why am I getting this error? I can see the Servers entry in Enterprise
> Manager in the Linked servers container.|||Did you use sp_dropserver and sp_addserver, LOCAL after the move?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>I would like to add that this problem started when I moved eevrything from
> Server Z to Server A (all system and user databases) and the old name of
> server Z was not retained. Earlier the link between Server Z and Server B was
> working fine in both the direstions. The sa login and the password is till
> the same.
> "sharman" wrote:
>> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> run a query from Server A on Server B but if I do it the other way I get an
>> error
>> Server: Msg 17, Level 16, State 1, Line 1
>> SQL Server does not exist or access denied.
>> Why am I getting this error? I can see the Servers entry in Enterprise
>> Manager in the Linked servers container.|||Which server do I have to do this? Is this to be done on the new server on
which I moved all the system and user databases from the old server? Thanks.
"Tibor Karaszi" wrote:
> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >I would like to add that this problem started when I moved eevrything from
> > Server Z to Server A (all system and user databases) and the old name of
> > server Z was not retained. Earlier the link between Server Z and Server B was
> > working fine in both the direstions. The sa login and the password is till
> > the same.
> >
> > "sharman" wrote:
> >
> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> run a query from Server A on Server B but if I do it the other way I get an
> >> error
> >>
> >> Server: Msg 17, Level 16, State 1, Line 1
> >> SQL Server does not exist or access denied.
> >>
> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> Manager in the Linked servers container.
>|||Yep. On the new server, SQL Server think its name is the old servers name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> Which server do I have to do this? Is this to be done on the new server on
> which I moved all the system and user databases from the old server? Thanks.
> "Tibor Karaszi" wrote:
>> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >I would like to add that this problem started when I moved eevrything from
>> > Server Z to Server A (all system and user databases) and the old name of
>> > server Z was not retained. Earlier the link between Server Z and Server B was
>> > working fine in both the direstions. The sa login and the password is till
>> > the same.
>> >
>> > "sharman" wrote:
>> >
>> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> error
>> >>
>> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> SQL Server does not exist or access denied.
>> >>
>> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> Manager in the Linked servers container.
>>|||Thanks for the reply. I did only the following after the move:
Change the name of the originating server in the field originating_server_id
on sysjobs table in msdb database to the new server because it still had the
name of the old server. I was not able to edit change job settings but after
the change I was able to.
Now I will also run sp_dropserver and sp_addserver, LOCAL
Is there anything else that I am forgetting that might cause a problem later
on? Thanks in advance.
"Tibor Karaszi" wrote:
> Yep. On the new server, SQL Server think its name is the old servers name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> > Which server do I have to do this? Is this to be done on the new server on
> > which I moved all the system and user databases from the old server? Thanks.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >I would like to add that this problem started when I moved eevrything from
> >> > Server Z to Server A (all system and user databases) and the old name of
> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> > working fine in both the direstions. The sa login and the password is till
> >> > the same.
> >> >
> >> > "sharman" wrote:
> >> >
> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> error
> >> >>
> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> SQL Server does not exist or access denied.
> >> >>
> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> Manager in the Linked servers container.
> >>
> >>
>|||I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
article on the subject, but I believe you covered what has to be done already:
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> Thanks for the reply. I did only the following after the move:
> Change the name of the originating server in the field originating_server_id
> on sysjobs table in msdb database to the new server because it still had the
> name of the old server. I was not able to edit change job settings but after
> the change I was able to.
> Now I will also run sp_dropserver and sp_addserver, LOCAL
> Is there anything else that I am forgetting that might cause a problem later
> on? Thanks in advance.
> "Tibor Karaszi" wrote:
>> Yep. On the new server, SQL Server think its name is the old servers name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
>> > Which server do I have to do this? Is this to be done on the new server on
>> > which I moved all the system and user databases from the old server? Thanks.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >> >I would like to add that this problem started when I moved eevrything from
>> >> > Server Z to Server A (all system and user databases) and the old name of
>> >> > server Z was not retained. Earlier the link between Server Z and Server B was
>> >> > working fine in both the direstions. The sa login and the password is till
>> >> > the same.
>> >> >
>> >> > "sharman" wrote:
>> >> >
>> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> >> error
>> >> >>
>> >> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> >> SQL Server does not exist or access denied.
>> >> >>
>> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> >> Manager in the Linked servers container.
>> >>
>> >>
>>|||I did as you have mentioned in the post and I also ran
sp_serveroption '<server_name>','data access','true'
Even after running these I get the following error when I try to parse a
query in an ExecuteSQL task in DTS that is connected to the this server ( The
DTS is on another server):
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
exist or access denied.
"Tibor Karaszi" wrote:
> I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
> article on the subject, but I believe you covered what has to be done already:
> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> > Thanks for the reply. I did only the following after the move:
> >
> > Change the name of the originating server in the field originating_server_id
> > on sysjobs table in msdb database to the new server because it still had the
> > name of the old server. I was not able to edit change job settings but after
> > the change I was able to.
> >
> > Now I will also run sp_dropserver and sp_addserver, LOCAL
> >
> > Is there anything else that I am forgetting that might cause a problem later
> > on? Thanks in advance.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Yep. On the new server, SQL Server think its name is the old servers name.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> >> > Which server do I have to do this? Is this to be done on the new server on
> >> > which I moved all the system and user databases from the old server? Thanks.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >> >I would like to add that this problem started when I moved eevrything from
> >> >> > Server Z to Server A (all system and user databases) and the old name of
> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> >> > working fine in both the direstions. The sa login and the password is till
> >> >> > the same.
> >> >> >
> >> >> > "sharman" wrote:
> >> >> >
> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> >> error
> >> >> >>
> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> >> SQL Server does not exist or access denied.
> >> >> >>
> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> >> Manager in the Linked servers container.
> >> >>
> >> >>
> >>
> >>
>|||I suggest you first logon to the local server from a query window and execute the statement that
accesses the linked server. This to simplify the scenario and try to localize where the problem
might be.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
>I did as you have mentioned in the post and I also ran
> sp_serveroption '<server_name>','data access','true'
> Even after running these I get the following error when I try to parse a
> query in an ExecuteSQL task in DTS that is connected to the this server ( The
> DTS is on another server):
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
> exist or access denied.
> "Tibor Karaszi" wrote:
>> I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
>> article on the subject, but I believe you covered what has to be done already:
>> http://www.karaszi.com/SQLServer/info_change_server_name.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
>> > Thanks for the reply. I did only the following after the move:
>> >
>> > Change the name of the originating server in the field originating_server_id
>> > on sysjobs table in msdb database to the new server because it still had the
>> > name of the old server. I was not able to edit change job settings but after
>> > the change I was able to.
>> >
>> > Now I will also run sp_dropserver and sp_addserver, LOCAL
>> >
>> > Is there anything else that I am forgetting that might cause a problem later
>> > on? Thanks in advance.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Yep. On the new server, SQL Server think its name is the old servers name.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
>> >> > Which server do I have to do this? Is this to be done on the new server on
>> >> > which I moved all the system and user databases from the old server? Thanks.
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >> >> >I would like to add that this problem started when I moved eevrything from
>> >> >> > Server Z to Server A (all system and user databases) and the old name of
>> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
>> >> >> > working fine in both the direstions. The sa login and the password is till
>> >> >> > the same.
>> >> >> >
>> >> >> > "sharman" wrote:
>> >> >> >
>> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> >> >> error
>> >> >> >>
>> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> >> >> SQL Server does not exist or access denied.
>> >> >> >>
>> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> >> >> Manager in the Linked servers container.
>> >> >>
>> >> >>
>> >>
>> >>
>>|||It is only when I try to parse a query in an ExecuteSQL task in DTS that is
connected to this server that I get an error.
When I run a select statement I do not get any error. Thanks.
"Tibor Karaszi" wrote:
> I suggest you first logon to the local server from a query window and execute the statement that
> accesses the linked server. This to simplify the scenario and try to localize where the problem
> might be.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
> >I did as you have mentioned in the post and I also ran
> > sp_serveroption '<server_name>','data access','true'
> >
> > Even after running these I get the following error when I try to parse a
> > query in an ExecuteSQL task in DTS that is connected to the this server ( The
> > DTS is on another server):
> >
> > Error Source: Microsoft OLE DB Provider for SQL Server
> > Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
> > exist or access denied.
> >
> > "Tibor Karaszi" wrote:
> >
> >> I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
> >> article on the subject, but I believe you covered what has to be done already:
> >> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> >> > Thanks for the reply. I did only the following after the move:
> >> >
> >> > Change the name of the originating server in the field originating_server_id
> >> > on sysjobs table in msdb database to the new server because it still had the
> >> > name of the old server. I was not able to edit change job settings but after
> >> > the change I was able to.
> >> >
> >> > Now I will also run sp_dropserver and sp_addserver, LOCAL
> >> >
> >> > Is there anything else that I am forgetting that might cause a problem later
> >> > on? Thanks in advance.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Yep. On the new server, SQL Server think its name is the old servers name.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> >> >> > Which server do I have to do this? Is this to be done on the new server on
> >> >> > which I moved all the system and user databases from the old server? Thanks.
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >> >> >I would like to add that this problem started when I moved eevrything from
> >> >> >> > Server Z to Server A (all system and user databases) and the old name of
> >> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> >> >> > working fine in both the direstions. The sa login and the password is till
> >> >> >> > the same.
> >> >> >> >
> >> >> >> > "sharman" wrote:
> >> >> >> >
> >> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> >> >> error
> >> >> >> >>
> >> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> >> >> SQL Server does not exist or access denied.
> >> >> >> >>
> >> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> >> >> Manager in the Linked servers container.
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>|||So you have some query in a DTS ExecuteSQL task. If you take that query and run it from Query
Analyzer it executed OK. But executed from the ExecuteSQL task it fails. If so, you need to look at
the properties if the ExecuteSQL task. Is the servername correctly specified? Login attributes? You
might want to continue this discussion in a DTS group (I'm no DTS expert myself).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:6B13D344-B6A2-43AA-84B8-50489A9245B6@.microsoft.com...
> It is only when I try to parse a query in an ExecuteSQL task in DTS that is
> connected to this server that I get an error.
> When I run a select statement I do not get any error. Thanks.
> "Tibor Karaszi" wrote:
>> I suggest you first logon to the local server from a query window and execute the statement that
>> accesses the linked server. This to simplify the scenario and try to localize where the problem
>> might be.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
>> >I did as you have mentioned in the post and I also ran
>> > sp_serveroption '<server_name>','data access','true'
>> >
>> > Even after running these I get the following error when I try to parse a
>> > query in an ExecuteSQL task in DTS that is connected to the this server ( The
>> > DTS is on another server):
>> >
>> > Error Source: Microsoft OLE DB Provider for SQL Server
>> > Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
>> > exist or access denied.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I think the two should take care of it, unless you are in a cluster or do replication. Here's
>> >> ay
>> >> article on the subject, but I believe you covered what has to be done already:
>> >> http://www.karaszi.com/SQLServer/info_change_server_name.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
>> >> > Thanks for the reply. I did only the following after the move:
>> >> >
>> >> > Change the name of the originating server in the field originating_server_id
>> >> > on sysjobs table in msdb database to the new server because it still had the
>> >> > name of the old server. I was not able to edit change job settings but after
>> >> > the change I was able to.
>> >> >
>> >> > Now I will also run sp_dropserver and sp_addserver, LOCAL
>> >> >
>> >> > Is there anything else that I am forgetting that might cause a problem later
>> >> > on? Thanks in advance.
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Yep. On the new server, SQL Server think its name is the old servers name.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
>> >> >> > Which server do I have to do this? Is this to be done on the new server on
>> >> >> > which I moved all the system and user databases from the old server? Thanks.
>> >> >> >
>> >> >> > "Tibor Karaszi" wrote:
>> >> >> >
>> >> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >> >>
>> >> >> >>
>> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >> >> >> >I would like to add that this problem started when I moved eevrything from
>> >> >> >> > Server Z to Server A (all system and user databases) and the old name of
>> >> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
>> >> >> >> > working fine in both the direstions. The sa login and the password is till
>> >> >> >> > the same.
>> >> >> >> >
>> >> >> >> > "sharman" wrote:
>> >> >> >> >
>> >> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> >> >> >> error
>> >> >> >> >>
>> >> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> >> >> >> SQL Server does not exist or access denied.
>> >> >> >> >>
>> >> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> >> >> >> Manager in the Linked servers container.
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>|||Thanks Tibor for all your help. I'll certainly post this in the DTS group.
"Tibor Karaszi" wrote:
> So you have some query in a DTS ExecuteSQL task. If you take that query and run it from Query
> Analyzer it executed OK. But executed from the ExecuteSQL task it fails. If so, you need to look at
> the properties if the ExecuteSQL task. Is the servername correctly specified? Login attributes? You
> might want to continue this discussion in a DTS group (I'm no DTS expert myself).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:6B13D344-B6A2-43AA-84B8-50489A9245B6@.microsoft.com...
> > It is only when I try to parse a query in an ExecuteSQL task in DTS that is
> > connected to this server that I get an error.
> >
> > When I run a select statement I do not get any error. Thanks.
> >
> > "Tibor Karaszi" wrote:
> >
> >> I suggest you first logon to the local server from a query window and execute the statement that
> >> accesses the linked server. This to simplify the scenario and try to localize where the problem
> >> might be.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
> >> >I did as you have mentioned in the post and I also ran
> >> > sp_serveroption '<server_name>','data access','true'
> >> >
> >> > Even after running these I get the following error when I try to parse a
> >> > query in an ExecuteSQL task in DTS that is connected to the this server ( The
> >> > DTS is on another server):
> >> >
> >> > Error Source: Microsoft OLE DB Provider for SQL Server
> >> > Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
> >> > exist or access denied.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> I think the two should take care of it, unless you are in a cluster or do replication. Here's
> >> >> ay
> >> >> article on the subject, but I believe you covered what has to be done already:
> >> >> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> >> >> > Thanks for the reply. I did only the following after the move:
> >> >> >
> >> >> > Change the name of the originating server in the field originating_server_id
> >> >> > on sysjobs table in msdb database to the new server because it still had the
> >> >> > name of the old server. I was not able to edit change job settings but after
> >> >> > the change I was able to.
> >> >> >
> >> >> > Now I will also run sp_dropserver and sp_addserver, LOCAL
> >> >> >
> >> >> > Is there anything else that I am forgetting that might cause a problem later
> >> >> > on? Thanks in advance.
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Yep. On the new server, SQL Server think its name is the old servers name.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> >> >> >> > Which server do I have to do this? Is this to be done on the new server on
> >> >> >> > which I moved all the system and user databases from the old server? Thanks.
> >> >> >> >
> >> >> >> > "Tibor Karaszi" wrote:
> >> >> >> >
> >> >> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >> >> >> >I would like to add that this problem started when I moved eevrything from
> >> >> >> >> > Server Z to Server A (all system and user databases) and the old name of
> >> >> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> >> >> >> > working fine in both the direstions. The sa login and the password is till
> >> >> >> >> > the same.
> >> >> >> >> >
> >> >> >> >> > "sharman" wrote:
> >> >> >> >> >
> >> >> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> >> >> >> error
> >> >> >> >> >>
> >> >> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> >> >> >> SQL Server does not exist or access denied.
> >> >> >> >> >>
> >> >> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> >> >> >> Manager in the Linked servers container.
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
Monday, March 26, 2012
linked server problem
Hi,
I have server A and B with SQL ent sp3 / Win Adv sp4. On server A I created
a linked server to B, I also selected Collation compatible. My problem,
It is ok to run on Server A (returning records instantly):
select * from b.master.dbo.sysdatabases
But it hangs when I run this on A:
begin tran
select * from b.master.dbo.sysdatabases
commit
I have checked MSDTC is running on both servers.
Did I miss anything?
Thanks.When you say "It is ok to run on Server A (returning records instantly)",
does that mean,
on ServerB, if you run
select * from a.master.dbo.sysdatabases
it returns records instantly?
If so, and the problem is when you run
select * from b.master.dbo.sysdatabases
OR
begin tran
select * from b.master.dbo.sysdatabases
commit
your best bet would be to,
1. make a connection directly to Server B and run
begin tran
select * from master.dbo.sysdatabases
commit
-- And see if that hangs.
If it doesnt, and it seems teh problem is with Linked servers, then you
might want to look at sysprocesses and see waht the spid is doing - is it
sleeping, waiting on a resource, etc.
Also, when you say it "hangs", what's the longest you've waited for?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
I have server A and B with SQL ent sp3 / Win Adv sp4. On server A I created
a linked server to B, I also selected Collation compatible. My problem,
It is ok to run on Server A (returning records instantly):
select * from b.master.dbo.sysdatabases
But it hangs when I run this on A:
begin tran
select * from b.master.dbo.sysdatabases
commit
I have checked MSDTC is running on both servers.
Did I miss anything?
Thanks.When you say "It is ok to run on Server A (returning records instantly)",
does that mean,
on ServerB, if you run
select * from a.master.dbo.sysdatabases
it returns records instantly?
If so, and the problem is when you run
select * from b.master.dbo.sysdatabases
OR
begin tran
select * from b.master.dbo.sysdatabases
commit
your best bet would be to,
1. make a connection directly to Server B and run
begin tran
select * from master.dbo.sysdatabases
commit
-- And see if that hangs.
If it doesnt, and it seems teh problem is with Linked servers, then you
might want to look at sysprocesses and see waht the spid is doing - is it
sleeping, waiting on a resource, etc.
Also, when you say it "hangs", what's the longest you've waited for?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Friday, March 23, 2012
Linked Server issue: DBSCHEMA_COLUMNS
Hi All -
I'm hoping someone can help me with this recurring problem. I have a
server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
me the error.
Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
columns of object 'DBSCHEMA_COLUMNS'.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
ORDINAL=-1]].
In the past, I rebuilt the server and it worked for a couple of months.
When the problem started again, I contacted Microsoft PSS and Chandra
determined the
"instcat.sql
script in the service pack did not run correctly on my
system. We backed up the master database, ran the script,
and I now have access to the linked servers.
"
The same error has occurred again on the same server! None of my other
5 servers(same configuration) has ever received this error.
I have all the servers linked, using sa username and password to
connect so I have full admin rights.
I saw a thread on this issue from April 23, 2003 between Gunter Van
Hauwe and Vikram Jayaram(MS).
I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
the same output as I receive with the query. It happens no matter
which database or table I choose. It happens from all the linked
servers.
If I run the query with OpenQuery, I receive data and not the error,
but this isn't practical for all of our applications.
Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
that and I receive all the tables and columns from the master database
on sql02.
I'm not sure what to do next. I know I can fix it by reinstalling, but
reattaching 100 databases after the reinstall is getting a bit tiring.
Thanks for any help you can provide.
Michelle MorrisI have seen similar issues when the MDAC or other service packs between the
servers were too far apart, say 2.1 to 2.7 (I know, too much a spread, but it
has been a while.)
You might check that compatibility level.
"mimorr" wrote:
> Hi All -
> I'm hoping someone can help me with this recurring problem. I have a
> server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
> Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
> me the error.
> Server: Msg 7351, Level 16, State 2, Line 1
> OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
> columns of object 'DBSCHEMA_COLUMNS'.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
> ORDINAL=-1]].
>
> In the past, I rebuilt the server and it worked for a couple of months.
> When the problem started again, I contacted Microsoft PSS and Chandra
> determined the
> "instcat.sql
> script in the service pack did not run correctly on my
> system. We backed up the master database, ran the script,
> and I now have access to the linked servers.
> "
> The same error has occurred again on the same server! None of my other
> 5 servers(same configuration) has ever received this error.
> I have all the servers linked, using sa username and password to
> connect so I have full admin rights.
> I saw a thread on this issue from April 23, 2003 between Gunter Van
> Hauwe and Vikram Jayaram(MS).
> I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
> the same output as I receive with the query. It happens no matter
> which database or table I choose. It happens from all the linked
> servers.
> If I run the query with OpenQuery, I receive data and not the error,
> but this isn't practical for all of our applications.
> Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
> that and I receive all the tables and columns from the master database
> on sql02.
> I'm not sure what to do next. I know I can fix it by reinstalling, but
> reattaching 100 databases after the reinstall is getting a bit tiring.
> Thanks for any help you can provide.
> Michelle Morris
>|||OK, I gave that a try using Microsoft's cc_pkg.exe
http://support.microsoft.com/default.aspx?scid=kb;en-us;301202
and here are my results:
My client workstation where I run QA: MDAC 2.7 SP1 on Windows XP SP1
SQL02 that is the target in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
SQL03 that is the source in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
Since the servers are the same, I launched Query Analyzer on SQL03 and
ran a query to a SQL02 database. I got the same error as reported
above.
JRPM, thank you for the suggestion.
Michelle
I'm hoping someone can help me with this recurring problem. I have a
server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
me the error.
Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
columns of object 'DBSCHEMA_COLUMNS'.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
ORDINAL=-1]].
In the past, I rebuilt the server and it worked for a couple of months.
When the problem started again, I contacted Microsoft PSS and Chandra
determined the
"instcat.sql
script in the service pack did not run correctly on my
system. We backed up the master database, ran the script,
and I now have access to the linked servers.
"
The same error has occurred again on the same server! None of my other
5 servers(same configuration) has ever received this error.
I have all the servers linked, using sa username and password to
connect so I have full admin rights.
I saw a thread on this issue from April 23, 2003 between Gunter Van
Hauwe and Vikram Jayaram(MS).
I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
the same output as I receive with the query. It happens no matter
which database or table I choose. It happens from all the linked
servers.
If I run the query with OpenQuery, I receive data and not the error,
but this isn't practical for all of our applications.
Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
that and I receive all the tables and columns from the master database
on sql02.
I'm not sure what to do next. I know I can fix it by reinstalling, but
reattaching 100 databases after the reinstall is getting a bit tiring.
Thanks for any help you can provide.
Michelle MorrisI have seen similar issues when the MDAC or other service packs between the
servers were too far apart, say 2.1 to 2.7 (I know, too much a spread, but it
has been a while.)
You might check that compatibility level.
"mimorr" wrote:
> Hi All -
> I'm hoping someone can help me with this recurring problem. I have a
> server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
> Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
> me the error.
> Server: Msg 7351, Level 16, State 2, Line 1
> OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
> columns of object 'DBSCHEMA_COLUMNS'.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
> ORDINAL=-1]].
>
> In the past, I rebuilt the server and it worked for a couple of months.
> When the problem started again, I contacted Microsoft PSS and Chandra
> determined the
> "instcat.sql
> script in the service pack did not run correctly on my
> system. We backed up the master database, ran the script,
> and I now have access to the linked servers.
> "
> The same error has occurred again on the same server! None of my other
> 5 servers(same configuration) has ever received this error.
> I have all the servers linked, using sa username and password to
> connect so I have full admin rights.
> I saw a thread on this issue from April 23, 2003 between Gunter Van
> Hauwe and Vikram Jayaram(MS).
> I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
> the same output as I receive with the query. It happens no matter
> which database or table I choose. It happens from all the linked
> servers.
> If I run the query with OpenQuery, I receive data and not the error,
> but this isn't practical for all of our applications.
> Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
> that and I receive all the tables and columns from the master database
> on sql02.
> I'm not sure what to do next. I know I can fix it by reinstalling, but
> reattaching 100 databases after the reinstall is getting a bit tiring.
> Thanks for any help you can provide.
> Michelle Morris
>|||OK, I gave that a try using Microsoft's cc_pkg.exe
http://support.microsoft.com/default.aspx?scid=kb;en-us;301202
and here are my results:
My client workstation where I run QA: MDAC 2.7 SP1 on Windows XP SP1
SQL02 that is the target in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
SQL03 that is the source in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
Since the servers are the same, I launched Query Analyzer on SQL03 and
ran a query to a SQL02 database. I got the same error as reported
above.
JRPM, thank you for the suggestion.
Michelle
Linked Server issue: DBSCHEMA_COLUMNS
Hi All -
I'm hoping someone can help me with this recurring problem. I have a
server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
me the error.
Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
columns of object 'DBSCHEMA_COLUMNS'.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
ORDINAL=-1]].
In the past, I rebuilt the server and it worked for a couple of months.
When the problem started again, I contacted Microsoft PSS and Chandra
determined the
"instcat.sql
script in the service pack did not run correctly on my
system. We backed up the master database, ran the script,
and I now have access to the linked servers.
"
The same error has occurred again on the same server! None of my other
5 servers(same configuration) has ever received this error.
I have all the servers linked, using sa username and password to
connect so I have full admin rights.
I saw a thread on this issue from April 23, 2003 between Gunter Van
Hauwe and Vikram Jayaram(MS).
I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
the same output as I receive with the query. It happens no matter
which database or table I choose. It happens from all the linked
servers.
If I run the query with OpenQuery, I receive data and not the error,
but this isn't practical for all of our applications.
Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
that and I receive all the tables and columns from the master database
on sql02.
I'm not sure what to do next. I know I can fix it by reinstalling, but
reattaching 100 databases after the reinstall is getting a bit tiring.
Thanks for any help you can provide.
Michelle Morris
I have seen similar issues when the MDAC or other service packs between the
servers were too far apart, say 2.1 to 2.7 (I know, too much a spread, but it
has been a while.)
You might check that compatibility level.
"mimorr" wrote:
> Hi All -
> I'm hoping someone can help me with this recurring problem. I have a
> server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
> Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
> me the error.
> Server: Msg 7351, Level 16, State 2, Line 1
> OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
> columns of object 'DBSCHEMA_COLUMNS'.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
> ORDINAL=-1]].
>
> In the past, I rebuilt the server and it worked for a couple of months.
> When the problem started again, I contacted Microsoft PSS and Chandra
> determined the
> "instcat.sql
> script in the service pack did not run correctly on my
> system. We backed up the master database, ran the script,
> and I now have access to the linked servers.
> "
> The same error has occurred again on the same server! None of my other
> 5 servers(same configuration) has ever received this error.
> I have all the servers linked, using sa username and password to
> connect so I have full admin rights.
> I saw a thread on this issue from April 23, 2003 between Gunter Van
> Hauwe and Vikram Jayaram(MS).
> I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
> the same output as I receive with the query. It happens no matter
> which database or table I choose. It happens from all the linked
> servers.
> If I run the query with OpenQuery, I receive data and not the error,
> but this isn't practical for all of our applications.
> Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
> that and I receive all the tables and columns from the master database
> on sql02.
> I'm not sure what to do next. I know I can fix it by reinstalling, but
> reattaching 100 databases after the reinstall is getting a bit tiring.
> Thanks for any help you can provide.
> Michelle Morris
>
|||OK, I gave that a try using Microsoft's cc_pkg.exe
http://support.microsoft.com/default...b;en-us;301202
and here are my results:
My client workstation where I run QA: MDAC 2.7 SP1 on Windows XP SP1
SQL02 that is the target in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
SQL03 that is the source in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
Since the servers are the same, I launched Query Analyzer on SQL03 and
ran a query to a SQL02 database. I got the same error as reported
above.
JRPM, thank you for the suggestion.
Michelle
sql
I'm hoping someone can help me with this recurring problem. I have a
server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
me the error.
Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
columns of object 'DBSCHEMA_COLUMNS'.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
ORDINAL=-1]].
In the past, I rebuilt the server and it worked for a couple of months.
When the problem started again, I contacted Microsoft PSS and Chandra
determined the
"instcat.sql
script in the service pack did not run correctly on my
system. We backed up the master database, ran the script,
and I now have access to the linked servers.
"
The same error has occurred again on the same server! None of my other
5 servers(same configuration) has ever received this error.
I have all the servers linked, using sa username and password to
connect so I have full admin rights.
I saw a thread on this issue from April 23, 2003 between Gunter Van
Hauwe and Vikram Jayaram(MS).
I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
the same output as I receive with the query. It happens no matter
which database or table I choose. It happens from all the linked
servers.
If I run the query with OpenQuery, I receive data and not the error,
but this isn't practical for all of our applications.
Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
that and I receive all the tables and columns from the master database
on sql02.
I'm not sure what to do next. I know I can fix it by reinstalling, but
reattaching 100 databases after the reinstall is getting a bit tiring.
Thanks for any help you can provide.
Michelle Morris
I have seen similar issues when the MDAC or other service packs between the
servers were too far apart, say 2.1 to 2.7 (I know, too much a spread, but it
has been a while.)
You might check that compatibility level.
"mimorr" wrote:
> Hi All -
> I'm hoping someone can help me with this recurring problem. I have a
> server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
> Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
> me the error.
> Server: Msg 7351, Level 16, State 2, Line 1
> OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
> columns of object 'DBSCHEMA_COLUMNS'.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
> ORDINAL=-1]].
>
> In the past, I rebuilt the server and it worked for a couple of months.
> When the problem started again, I contacted Microsoft PSS and Chandra
> determined the
> "instcat.sql
> script in the service pack did not run correctly on my
> system. We backed up the master database, ran the script,
> and I now have access to the linked servers.
> "
> The same error has occurred again on the same server! None of my other
> 5 servers(same configuration) has ever received this error.
> I have all the servers linked, using sa username and password to
> connect so I have full admin rights.
> I saw a thread on this issue from April 23, 2003 between Gunter Van
> Hauwe and Vikram Jayaram(MS).
> I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
> the same output as I receive with the query. It happens no matter
> which database or table I choose. It happens from all the linked
> servers.
> If I run the query with OpenQuery, I receive data and not the error,
> but this isn't practical for all of our applications.
> Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
> that and I receive all the tables and columns from the master database
> on sql02.
> I'm not sure what to do next. I know I can fix it by reinstalling, but
> reattaching 100 databases after the reinstall is getting a bit tiring.
> Thanks for any help you can provide.
> Michelle Morris
>
|||OK, I gave that a try using Microsoft's cc_pkg.exe
http://support.microsoft.com/default...b;en-us;301202
and here are my results:
My client workstation where I run QA: MDAC 2.7 SP1 on Windows XP SP1
SQL02 that is the target in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
SQL03 that is the source in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
Since the servers are the same, I launched Query Analyzer on SQL03 and
ran a query to a SQL02 database. I got the same error as reported
above.
JRPM, thank you for the suggestion.
Michelle
sql
Wednesday, March 21, 2012
linked server for a named instance
sql2k sp3
Im doing some testing on my local pc. The name of my pc happens to have "-"
's in it. I have two instances of sql on it. So lets say I have the names of
my SQL instances are:
1. aaa-aa-aaa
2.aaa-aa-aaa\Destination
While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
the dashes. So I go into Client Network Utility and create an Alias of
"Destination". Then when I try the revised query "select * from
destination.pubs.dbo.authors" I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
So whats the dealio?
TIA, ChrisR"select * from aaa-aa-aaa\Destination.pubs.dbo.authors"
You will have to configure the linked server to the names instance, type a
name that is without dashes. I you don´t wanna do it, try to set up your
query to use brackets around parts of the name which could be understood as
special charcters or reserved words.
If the linked server is installed properly the authentification will work
like a charme (even the evil dashes are included, because you wont see them
anymore)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23LsOj6cUFHA.2172@.tk2msftngp13.phx.gbl...
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have
> "-" 's in it. I have two instances of sql on it. So lets say I have the
> names of my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
> of the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>|||Create a stored procedure with servername, loginuser, login_password and an
alias to call the server(which can be null). Then write a small portion to
drop link server if it exist:
IF EXISTS (SELECT srvname
FROM master.dbo.sysservers (NOLOCK)
WHERE srvname = @.p_alias)
BEGIN
EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
EXEC master.dbo.sp_dropserver @.p_alias
Then make sure you have declare your variable to excute dynamic sql statement;
DECLARE @.v_sql VARCHAR(500)
And then use this peace to actually link the server.
/* Add linked server */
SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'', ' +
'@.datasrc = ''' + @.p_servername + ''' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
/* Add login for linked server */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = @.p_alias,
@.useself = 'false',
@.rmtuser = @.p_user,
@.rmtpassword = @.p_pw
GO
This is the easy way to do it using TSQL and all credit for this one has to
go to my mentor.
Jared
Now you can link a server in a different way but this script just does all
the work for you.
Hope this helps.
"ChrisR" wrote:
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have "-"
> 's in it. I have two instances of sql on it. So lets say I have the names of
> my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
> the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
>
Im doing some testing on my local pc. The name of my pc happens to have "-"
's in it. I have two instances of sql on it. So lets say I have the names of
my SQL instances are:
1. aaa-aa-aaa
2.aaa-aa-aaa\Destination
While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
the dashes. So I go into Client Network Utility and create an Alias of
"Destination". Then when I try the revised query "select * from
destination.pubs.dbo.authors" I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
So whats the dealio?
TIA, ChrisR"select * from aaa-aa-aaa\Destination.pubs.dbo.authors"
You will have to configure the linked server to the names instance, type a
name that is without dashes. I you don´t wanna do it, try to set up your
query to use brackets around parts of the name which could be understood as
special charcters or reserved words.
If the linked server is installed properly the authentification will work
like a charme (even the evil dashes are included, because you wont see them
anymore)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23LsOj6cUFHA.2172@.tk2msftngp13.phx.gbl...
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have
> "-" 's in it. I have two instances of sql on it. So lets say I have the
> names of my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
> of the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>|||Create a stored procedure with servername, loginuser, login_password and an
alias to call the server(which can be null). Then write a small portion to
drop link server if it exist:
IF EXISTS (SELECT srvname
FROM master.dbo.sysservers (NOLOCK)
WHERE srvname = @.p_alias)
BEGIN
EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
EXEC master.dbo.sp_dropserver @.p_alias
Then make sure you have declare your variable to excute dynamic sql statement;
DECLARE @.v_sql VARCHAR(500)
And then use this peace to actually link the server.
/* Add linked server */
SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'', ' +
'@.datasrc = ''' + @.p_servername + ''' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
/* Add login for linked server */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = @.p_alias,
@.useself = 'false',
@.rmtuser = @.p_user,
@.rmtpassword = @.p_pw
GO
This is the easy way to do it using TSQL and all credit for this one has to
go to my mentor.
Jared
Now you can link a server in a different way but this script just does all
the work for you.
Hope this helps.
"ChrisR" wrote:
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have "-"
> 's in it. I have two instances of sql on it. So lets say I have the names of
> my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
> the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
>
Friday, March 9, 2012
Linked Server and Server Registration
I am trying to set up a linked server from a SQL 7 sp3 box
to a SQL 2000 sp3 named instance server. I keep getting
the following error.
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
I can however connect from a SQL Server 7 sp4 box with no
problem. We have checked connectivity and other options
with no luck. Is there a problem with SQL 7 sp3 connecting
to a SQL 2000 named instance box? Any suggestions?It may have to do with your MDAC version. Can you make an OSQL connection
from the SQL7 box to the SQL2K box?
If you have an older version of MDAC you may need to create an alias with
cliconfg and use the alias when you create the linked server. That's
because older versions of MDAC don't know about named instances.
For instance, if your named instance is SQL1\Inst1, on the SQL7 box run
cliconfg.exe and create an alias named SQL1_Alias.
Point SQL1_Alias to SQL1\Inst1. Then in your linked server query specify
SQL1_Alias.
270126 PRB: How to Manage Client Connectivity to Both SQL Server 2000
Virtual
http://support.microsoft.com/?id=270126
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
to a SQL 2000 sp3 named instance server. I keep getting
the following error.
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
I can however connect from a SQL Server 7 sp4 box with no
problem. We have checked connectivity and other options
with no luck. Is there a problem with SQL 7 sp3 connecting
to a SQL 2000 named instance box? Any suggestions?It may have to do with your MDAC version. Can you make an OSQL connection
from the SQL7 box to the SQL2K box?
If you have an older version of MDAC you may need to create an alias with
cliconfg and use the alias when you create the linked server. That's
because older versions of MDAC don't know about named instances.
For instance, if your named instance is SQL1\Inst1, on the SQL7 box run
cliconfg.exe and create an alias named SQL1_Alias.
Point SQL1_Alias to SQL1\Inst1. Then in your linked server query specify
SQL1_Alias.
270126 PRB: How to Manage Client Connectivity to Both SQL Server 2000
Virtual
http://support.microsoft.com/?id=270126
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
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...
>
>
|||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...
>
>
|||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:
>
>
|||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:
[vbcol=seagreen]
> 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:
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...
>
>
|||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...
>
>
|||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:
>
>
|||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:
[vbcol=seagreen]
> 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:
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 Service
s
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::Prep
are
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 objectCategor
y
= "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...
>
>|||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...
>
>|||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) an
d
> 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:
>
>
>|||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:
[vbcol=seagreen]
> 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:
>
Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service
s
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::Prep
are
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 objectCategor
y
= "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...
>
>|||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...
>
>|||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) an
d
> 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:
>
>
>|||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:
[vbcol=seagreen]
> 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:
>
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.
> > > >
> > >
> > >
> > >
> >
> >
> >
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.
> > > >
> > >
> > >
> > >
> >
> >
> >
Subscribe to:
Posts (Atom)