We're using SS2000 SP4 on both servers. We've been running the following
query for 6 months or so. I'm not sure exactly how long it used to take
because the whole job has about 10 queries. But the whole job used to take
about 2 hours. Now this query alone can take 3 hours.
use branch_master
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from server2.promo2004.dbo.tblmail_resp t, branch_master..tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
However, if we change the query and put it on the remote server it runs in
under t seconds. Here is the query we use on the remote server.
use promo2004 --on maximus2
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from tblmail_resp t, server1.branch_master.dbo.tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
I don't know if it makes any difference but the tblmail_resp table on
server2 has about 8 million rows and the tblzipreassign_new table on server1
has about 2500 rows.
We have the same problem with a couple of other jobs have queries that go
from server1 to server2 and they have slowed down a lot of in some cases run
for over 8 hours before we stop them.
Any ideas?
Thanks,
--
Dan D.Hi Dan,
In order for me to better answer this I suggest you post the entire DDL
of the tables including indexes and the query plan.
Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts
Wednesday, March 28, 2012
linked server problem slow queries
We're using SS2000 SP4 on both servers. We've been running the following
query for 6 months or so. I'm not sure exactly how long it used to take
because the whole job has about 10 queries. But the whole job used to take
about 2 hours. Now this query alone can take 3 hours.
use branch_master
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from server2.promo2004.dbo.tblmail_resp t, branch_master..tblZipReassign_New
z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
However, if we change the query and put it on the remote server it runs in
under t seconds. Here is the query we use on the remote server.
use promo2004 --on maximus2
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from tblmail_resp t, server1.branch_master.dbo.tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
I don't know if it makes any difference but the tblmail_resp table on
server2 has about 8 million rows and the tblzipreassign_new table on server1
has about 2500 rows.
We have the same problem with a couple of other jobs have queries that go
from server1 to server2 and they have slowed down a lot of in some cases run
for over 8 hours before we stop them.
Any ideas?
Thanks,
--
Dan D.Hi Dan,
In order for me to better answer this I suggest you post the entire DDL
of the tables including indexes and the query plan.
query for 6 months or so. I'm not sure exactly how long it used to take
because the whole job has about 10 queries. But the whole job used to take
about 2 hours. Now this query alone can take 3 hours.
use branch_master
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from server2.promo2004.dbo.tblmail_resp t, branch_master..tblZipReassign_New
z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
However, if we change the query and put it on the remote server it runs in
under t seconds. Here is the query we use on the remote server.
use promo2004 --on maximus2
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from tblmail_resp t, server1.branch_master.dbo.tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
I don't know if it makes any difference but the tblmail_resp table on
server2 has about 8 million rows and the tblzipreassign_new table on server1
has about 2500 rows.
We have the same problem with a couple of other jobs have queries that go
from server1 to server2 and they have slowed down a lot of in some cases run
for over 8 hours before we stop them.
Any ideas?
Thanks,
--
Dan D.Hi Dan,
In order for me to better answer this I suggest you post the entire DDL
of the tables including indexes and the query plan.
linked server problem slow queries
We're using SS2000 SP4 on both servers. We've been running the following
query for 6 months or so. I'm not sure exactly how long it used to take
because the whole job has about 10 queries. But the whole job used to take
about 2 hours. Now this query alone can take 3 hours.
use branch_master
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from server2.promo2004.dbo.tblmail_resp t, branch_master..tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
However, if we change the query and put it on the remote server it runs in
under t seconds. Here is the query we use on the remote server.
use promo2004 --on maximus2
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from tblmail_resp t, server1.branch_master.dbo.tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
I don't know if it makes any difference but the tblmail_resp table on
server2 has about 8 million rows and the tblzipreassign_new table on server1
has about 2500 rows.
We have the same problem with a couple of other jobs have queries that go
from server1 to server2 and they have slowed down a lot of in some cases run
for over 8 hours before we stop them.
Any ideas?
Thanks,
Dan D.
Hi Dan,
In order for me to better answer this I suggest you post the entire DDL
of the tables including indexes and the query plan.
query for 6 months or so. I'm not sure exactly how long it used to take
because the whole job has about 10 queries. But the whole job used to take
about 2 hours. Now this query alone can take 3 hours.
use branch_master
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from server2.promo2004.dbo.tblmail_resp t, branch_master..tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
However, if we change the query and put it on the remote server it runs in
under t seconds. Here is the query we use on the remote server.
use promo2004 --on maximus2
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from tblmail_resp t, server1.branch_master.dbo.tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
I don't know if it makes any difference but the tblmail_resp table on
server2 has about 8 million rows and the tblzipreassign_new table on server1
has about 2500 rows.
We have the same problem with a couple of other jobs have queries that go
from server1 to server2 and they have slowed down a lot of in some cases run
for over 8 hours before we stop them.
Any ideas?
Thanks,
Dan D.
Hi Dan,
In order for me to better answer this I suggest you post the entire DDL
of the tables including indexes and the query plan.
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.
Subscribe to:
Posts (Atom)