Sql Linked Server: servers

Showing posts with label servers. Show all posts

Friday, March 30, 2012

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Help? I am an Oracle DBA struggling to support SQL Server.
Using EM I added another SQL Server database to Linked Servers with Windows
authentication. In Query Analyzer I attempt a simple SQL query and receive...
Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
I read thru BOL and Robert Vieira's book to setup the distributed query, and
I believe I am doing everything correctly. Scanning Google and MSKB, it
seems I hit a bug with SQL Server, but this is so simple I have a hard time
believing that.
Is my only recourse to call support' Is there anything I can check'
--
ChrisIt depends...the problem can be caused by a ton of
different things which is why it's often recommended to open
up a case with support.
What version of SQL Server? Are you on the latest service
packs?
Does this occur with any server and any database or just one
specific server and one database?
Make sure the servers, databases involved are running
correctly - check both servers server names with
@.@.Servername. Make sure the database you are accessing does
not have any consistency problems - run a dbcc on the
database you are accessing.
You may also want to check your mdac installation on the
servers. You can use the mdac component checker to do this.
It's available from:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Fri, 12 Nov 2004 09:54:03 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:
>Help? I am an Oracle DBA struggling to support SQL Server.
>Using EM I added another SQL Server database to Linked Servers with Windows
>authentication. In Query Analyzer I attempt a simple SQL query and receive...
>Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
>I read thru BOL and Robert Vieira's book to setup the distributed query, and
>I believe I am doing everything correctly. Scanning Google and MSKB, it
>seems I hit a bug with SQL Server, but this is so simple I have a hard time
>believing that.
>Is my only recourse to call support' Is there anything I can check'|||Duh. I should have posted that.
We are currently running SQL Server 2000 with SP3a in Windows 2000 w SP4.
This occurs when I try to query tables on another instance on the same server.
We have a maintenance plan that performs the dbcc checks on a regular basis.
So no problem there.
Basically, I need to be able to compare the tables in the current (dev or
test) databases with a "baseline" database. So I decided to try the
distributed query from the baseline database against the dev database and it
works. But it fails the other way Or when I try to compare dev database
against test database it fails. So for now I'm ok. Later, I will
troubleshoot why I cant go from dev or test database to the baseline database.
Thanks Sue!!
"Sue Hoegemeier" wrote:
> It depends...the problem can be caused by a ton of
> different things which is why it's often recommended to open
> up a case with support.
> What version of SQL Server? Are you on the latest service
> packs?
> Does this occur with any server and any database or just one
> specific server and one database?
> Make sure the servers, databases involved are running
> correctly - check both servers server names with
> @.@.Servername. Make sure the database you are accessing does
> not have any consistency problems - run a dbcc on the
> database you are accessing.
> You may also want to check your mdac installation on the
> servers. You can use the mdac component checker to do this.
> It's available from:
> http://msdn.microsoft.com/data/mdac/default.aspx
> -Sue
> On Fri, 12 Nov 2004 09:54:03 -0800, "Chris"
> <Chris@.discussions.microsoft.com> wrote:
> >Help? I am an Oracle DBA struggling to support SQL Server.
> >
> >Using EM I added another SQL Server database to Linked Servers with Windows
> >authentication. In Query Analyzer I attempt a simple SQL query and receive...
> >
> >Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
> >
> >I read thru BOL and Robert Vieira's book to setup the distributed query, and
> >I believe I am doing everything correctly. Scanning Google and MSKB, it
> >seems I hit a bug with SQL Server, but this is so simple I have a hard time
> >believing that.
> >
> >Is my only recourse to call support' Is there anything I can check'
>

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Linked Server Problems with all providers

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.

Linked Server Problems HELP HELP HELP !

having a LOT of problems with SQL server linked servers.
I have a server running Windows Server 2003. (Server A)
I want to run a query from a different server (Server B) using query
analyser.
E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
keep getting the following message
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
looked through various pages/groups etc.
my authentication mode is set to mixed.
still getting the message.
starting to annoy me - a LOT.!!
Help please!!!
THanks in advance.Paul
Can you post a script for set up linked server?
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||how?|||This example of set up linekd server to Access (Lookup these SP in the BOL)
USE master
GO
EXEC sp_addlinkedserver
@.server = 'test_Access1',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\WINDOWS\Desktop\uritennis.mdb'
GO
sp_linkedservers
sp_dropserver 'test_Access1', 'droplogins'
USE master
GO
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'test_Access1',
@.useself = false,
@.locallogin = null,
@.rmtuser ='admin',
@.rmtpassword =null
USE master
GO
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
<paul_30s@.hotmail.com> wrote in message
news:1140522241.310199.137970@.g47g2000cwa.googlegroups.com...
> how?
>|||done all this stuff. still same problem.
is there anything on the server that i need to check ?|||Have you ran it on linked server?
exec sp_serveroption 'SERVER','data access','true'
<paul_30s@.hotmail.com> wrote in message
news:1140523365.016551.291200@.g44g2000cwa.googlegroups.com...
> done all this stuff. still same problem.
> is there anything on the server that i need to check ?
>|||still the same.|||It seems like YOUR server is using mixed authorization but LINKED server is
not (trusted connections only)
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||both servers are using mixed authentication.|||paul_30s@.hotmail.com wrote:
> still the same.
>
Have you tried using a SQL user instead of an Windows/NT user account?
If you want to use integrated security (windows user) you have to ensure
that security account delegation is enabled (and that's something that
has to be set up in your Active Directory).
You can read more about security account delegation in Books On Line -
look for "Security Account Delegation".
Regards
Steen

Linked Server Problems HELP HELP HELP !

having a LOT of problems with SQL server linked servers.
I have a server running Windows Server 2003. (Server A)
I want to run a query from a different server (Server B) using query
analyser.
E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
keep getting the following message
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
looked through various pages/groups etc.
my authentication mode is set to mixed.
still getting the message.
starting to annoy me - a LOT.!!
Help please!!!
THanks in advance.
Paul
Can you post a script for set up linked server?
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegr oups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>
|||how?
|||This example of set up linekd server to Access (Lookup these SP in the BOL)
USE master
GO
EXEC sp_addlinkedserver
@.server = 'test_Access1',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\WINDOWS\Desktop\uritennis.mdb'
GO
sp_linkedservers
sp_dropserver 'test_Access1', 'droplogins'
USE master
GO
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'test_Access1',
@.useself = false,
@.locallogin = null,
@.rmtuser ='admin',
@.rmtpassword =null
USE master
GO
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
<paul_30s@.hotmail.com> wrote in message
news:1140522241.310199.137970@.g47g2000cwa.googlegr oups.com...
> how?
>
|||done all this stuff. still same problem.
is there anything on the server that i need to check ?
|||Have you ran it on linked server?
exec sp_serveroption 'SERVER','data access','true'
<paul_30s@.hotmail.com> wrote in message
news:1140523365.016551.291200@.g44g2000cwa.googlegr oups.com...
> done all this stuff. still same problem.
> is there anything on the server that i need to check ?
>
|||still the same.
|||It seems like YOUR server is using mixed authorization but LINKED server is
not (trusted connections only)
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegr oups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>
|||both servers are using mixed authentication.
|||paul_30s@.hotmail.com wrote:
> still the same.
>
Have you tried using a SQL user instead of an Windows/NT user account?
If you want to use integrated security (windows user) you have to ensure
that security account delegation is enabled (and that's something that
has to be set up in your Active Directory).
You can read more about security account delegation in Books On Line -
look for "Security Account Delegation".
Regards
Steen

Linked Server Problems HELP HELP HELP !

Linked Server Problems

All
Background: I have three SQL servers, SRV1, SRV2, and SRV3. SRV1 is running
SQL Server 2000, and SRV2 and SRV3 are running SQL Server 2005.
I have SRV3 set up as a linked server on SRV1. I have also set up SRV3 as a
linked server on SRV2. When I run a query against SRV3 on SRV1, I never have
any problems. When I try the same query on SRV2, it fails every time, but
inconsistently. Sometimes it will simply not return any records, with no
error messages. Sometimes it will return a few records (differing amounts
each time), but also supply the following error message:
OLE DB provider "SQLNCLI" for linked server "SRV3" returned message
"Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.
Sometimes there will be different error messages (sorry, I don't have them
to hand). I've googled the error messages, but not found anything specific
enough ot start me off on a strategy to address this issue.
Any assistance is very much appreciated.
Regards
Chris Marsh
Hi Chris,
Per my understanding, you created two linked servers: LINK1: SRV1->SRV3 and
LINK2: SRV2->SRV3. SRV1 is running SQL Server 2000, and SRV2 and SRV3 are
running SQL Server 2005. The problem is that LINK1 always worked fine
while LINK2 always failed with inconsistent errors.
If I have misunderstood, please let me know.
For further research, I would like to check with you the following:
1. What are the differences between SRV1 and SRV2 on communication protocol
and SQL Server service startup account?
2. Has SQL Server 2005 SP1 been installed on SRV2 and SRV3?
You can first check the Communication Protocol via SQL Server Configuration
Manager on SRV3; if it supports TCP/IP and Named Pipes, please try
configuring the communication protocol as TCP/IP on LINK2. If TCP/IP
protocol is not enabled on SRV3, please enable it and set the TCP port
under IP Address tab. I recommend that you just set it under the IPAll and
set the Enable option of IP1 and IP2 to No; then restart your SQL Server.
1. On SRV3, run "netstat -nab >C:\portstat.log" from command prompt to see
which TCP port is being listened by your SQL Server;
2. Asssuming that it is listening on the TCP port 1433, run the command
"telnet SRV3 1433" from SRV2 to see if SRV2 can telnet to the SRV3:1433; if
the telnet command fails, please check if Firewall is enabled on SRV3 by
running firewall.cpl from command prompt and add the TCP port to the
Exception list;
3. If you can telnet to SRV3:1433 from SRV2, please run cliconfg.exe from
command prompt on SRV2; switch to the Alias tab, click Add button, select
TCP/IP, input "SRV3" to the server name field, cancel the selection of
"Dynamically determin port" and input your TCP Port number to the Port
number field, and then input an alias to the Server alias field. The alias
name can be customized but I recommended that you use your SQL Server
instance full name for easy memory.
You can check your SQL Server service startup account from Service list,
right click the SQL Server service or MSSQLSERVER service, click
Properties, and switch to the Log On tab. I recommend that you use a domain
account as your SQL Server service startup account.
If your SQL Server 2005 SP1 has not been installed on SRV2 and SRV3, I
recommend that you install it before further research. You can download it
from:
http://www.microsoft.com/sql/sp1.mspx
Also, I recommend that you check if Data Access option was enabled on your
linked server. On SRV2, expand the Server Objects from SQL Server
Management Studio, expand Linked Servers, right click your linked server,
click Properties, select Server Options, and set the Data Acess option to
True.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, Chris,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Linked Server Problems

All
Background: I have three SQL servers, SRV1, SRV2, and SRV3. SRV1 is running
SQL Server 2000, and SRV2 and SRV3 are running SQL Server 2005.
I have SRV3 set up as a linked server on SRV1. I have also set up SRV3 as a
linked server on SRV2. When I run a query against SRV3 on SRV1, I never have
any problems. When I try the same query on SRV2, it fails every time, but
inconsistently. Sometimes it will simply not return any records, with no
error messages. Sometimes it will return a few records (differing amounts
each time), but also supply the following error message:
OLE DB provider "SQLNCLI" for linked server "SRV3" returned message
"Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.
Sometimes there will be different error messages (sorry, I don't have them
to hand). I've googled the error messages, but not found anything specific
enough ot start me off on a strategy to address this issue.
Any assistance is very much appreciated.
--
Regards
Chris MarshHi Chris,
Per my understanding, you created two linked servers: LINK1: SRV1->SRV3 and
LINK2: SRV2->SRV3. SRV1 is running SQL Server 2000, and SRV2 and SRV3 are
running SQL Server 2005. The problem is that LINK1 always worked fine
while LINK2 always failed with inconsistent errors.
If I have misunderstood, please let me know.
For further research, I would like to check with you the following:
1. What are the differences between SRV1 and SRV2 on communication protocol
and SQL Server service startup account?
2. Has SQL Server 2005 SP1 been installed on SRV2 and SRV3?
You can first check the Communication Protocol via SQL Server Configuration
Manager on SRV3; if it supports TCP/IP and Named Pipes, please try
configuring the communication protocol as TCP/IP on LINK2. If TCP/IP
protocol is not enabled on SRV3, please enable it and set the TCP port
under IP Address tab. I recommend that you just set it under the IPAll and
set the Enable option of IP1 and IP2 to No; then restart your SQL Server.
1. On SRV3, run "netstat -nab >C:\portstat.log" from command prompt to see
which TCP port is being listened by your SQL Server;
2. Asssuming that it is listening on the TCP port 1433, run the command
"telnet SRV3 1433" from SRV2 to see if SRV2 can telnet to the SRV3:1433; if
the telnet command fails, please check if Firewall is enabled on SRV3 by
running firewall.cpl from command prompt and add the TCP port to the
Exception list;
3. If you can telnet to SRV3:1433 from SRV2, please run cliconfg.exe from
command prompt on SRV2; switch to the Alias tab, click Add button, select
TCP/IP, input "SRV3" to the server name field, cancel the selection of
"Dynamically determin port" and input your TCP Port number to the Port
number field, and then input an alias to the Server alias field. The alias
name can be customized but I recommended that you use your SQL Server
instance full name for easy memory.
You can check your SQL Server service startup account from Service list,
right click the SQL Server service or MSSQLSERVER service, click
Properties, and switch to the Log On tab. I recommend that you use a domain
account as your SQL Server service startup account.
If your SQL Server 2005 SP1 has not been installed on SRV2 and SRV3, I
recommend that you install it before further research. You can download it
from:
http://www.microsoft.com/sql/sp1.mspx
Also, I recommend that you check if Data Access option was enabled on your
linked server. On SRV2, expand the Server Objects from SQL Server
Management Studio, expand Linked Servers, right click your linked server,
click Properties, select Server Options, and set the Data Acess option to
True.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi, Chris,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql

Linked Server Problems

All
Background: I have three SQL servers, SRV1, SRV2, and SRV3. SRV1 is running
SQL Server 2000, and SRV2 and SRV3 are running SQL Server 2005.
I have SRV3 set up as a linked server on SRV1. I have also set up SRV3 as a
linked server on SRV2. When I run a query against SRV3 on SRV1, I never have
any problems. When I try the same query on SRV2, it fails every time, but
inconsistently. Sometimes it will simply not return any records, with no
error messages. Sometimes it will return a few records (differing amounts
each time), but also supply the following error message:
OLE DB provider "SQLNCLI" for linked server "SRV3" returned message
"Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.
Sometimes there will be different error messages (sorry, I don't have them
to hand). I've googled the error messages, but not found anything specific
enough ot start me off on a strategy to address this issue.
Any assistance is very much appreciated.
Regards
Chris MarshHi Chris,
Per my understanding, you created two linked servers: LINK1: SRV1->SRV3 and
LINK2: SRV2->SRV3. SRV1 is running SQL Server 2000, and SRV2 and SRV3 are
running SQL Server 2005. The problem is that LINK1 always worked fine
while LINK2 always failed with inconsistent errors.
If I have misunderstood, please let me know.
For further research, I would like to check with you the following:
1. What are the differences between SRV1 and SRV2 on communication protocol
and SQL Server service startup account?
2. Has SQL Server 2005 SP1 been installed on SRV2 and SRV3?
You can first check the Communication Protocol via SQL Server Configuration
Manager on SRV3; if it supports TCP/IP and Named Pipes, please try
configuring the communication protocol as TCP/IP on LINK2. If TCP/IP
protocol is not enabled on SRV3, please enable it and set the TCP port
under IP Address tab. I recommend that you just set it under the IPAll and
set the Enable option of IP1 and IP2 to No; then restart your SQL Server.
1. On SRV3, run "netstat -nab >C:\portstat.log" from command prompt to see
which TCP port is being listened by your SQL Server;
2. Asssuming that it is listening on the TCP port 1433, run the command
"telnet SRV3 1433" from SRV2 to see if SRV2 can telnet to the SRV3:1433; if
the telnet command fails, please check if Firewall is enabled on SRV3 by
running firewall.cpl from command prompt and add the TCP port to the
Exception list;
3. If you can telnet to SRV3:1433 from SRV2, please run cliconfg.exe from
command prompt on SRV2; switch to the Alias tab, click Add button, select
TCP/IP, input "SRV3" to the server name field, cancel the selection of
"Dynamically determin port" and input your TCP Port number to the Port
number field, and then input an alias to the Server alias field. The alias
name can be customized but I recommended that you use your SQL Server
instance full name for easy memory.
You can check your SQL Server service startup account from Service list,
right click the SQL Server service or MSSQLSERVER service, click
Properties, and switch to the Log On tab. I recommend that you use a domain
account as your SQL Server service startup account.
If your SQL Server 2005 SP1 has not been installed on SRV2 and SRV3, I
recommend that you install it before further research. You can download it
from:
http://www.microsoft.com/sql/sp1.mspx
Also, I recommend that you check if Data Access option was enabled on your
linked server. On SRV2, expand the Server Objects from SQL Server
Management Studio, expand Linked Servers, right click your linked server,
click Properties, select Server Options, and set the Data Acess option to
True.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi, Chris,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

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.

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.

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.

Linked server problem

Hi,

I am connecting two sql servers together as linked server, the servers are running windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.

However, my problem appeared when I tried to stress test my application, the connection on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
my transaction details:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) ro prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller), and then
5) The transaction is committed.

The above scenario is working properly for 53 and less cocurrent connection! In case more cocurrent connections are
issued, the connection are blocked almost immediately and never returns till the execution times out. The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.

Please note that:
1) the test was conduct separately from any other activity on the SQL servers. i.e. they was the only connections on the servers.
2) When we moved the the Linked Server database to the local server, i.e. we removed the linked server, all connections completed their commands execution successfully and without timeouts. Also, note that I have ran the test successfully, on the local server, with 256 and 512 connections.

I do highly appreciate any help or support on this.

Regards,Any one can help?|||This is beyond my ability to test. I've never seen the problem with any configuration that I use, so I'd suspect that it is either an interaction between the software versions that you are using or something specific to your code.

My advice is to open an incident with Microsoft Premier Support. If the problem is in your code, it will cost you roughly $1000 US to prove it. If the problem is in their code, it won't cost you a thing and you'll have had some pretty valuable review of your code and configuration, plus you'll get a solution (usually a patch) for the problem too!

-PatP|||You could set a max on the Query Timeout for the linked server. It would still take awhile to resolve. The bigger questions is what's causing the locking in the first place? Is it always one process that's causing this or one area such as a particular table?

Linked server Problem

Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues
like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.

>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider[vbcol=seagreen]
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.

>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>sql

Linked server problem

Hi,
I have two sql servers A and B linked together as LINKED SERVERS. A is
within the domain and B is outside the domain. They were working perfectly
fine a week back but now I can access A from B but I can't access B from A,
although B comes up under the Linked Servers list on A. What can be wrong?
Thanks in advance.Well
Something has changed since last week. Perhaps try to re-create a linked
server on server A to server B
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
> Hi,
> I have two sql servers A and B linked together as LINKED SERVERS. A is
> within the domain and B is outside the domain. They were working perfectly
> fine a week back but now I can access A from B but I can't access B from
> A,
> although B comes up under the Linked Servers list on A. What can be wrong?
> Thanks in advance.|||I have already done that. It does come up on the list of linked servers but
when I click on the tables icon it says "Server Not Accessible".
"Uri Dimant" wrote:

> Well
> Something has changed since last week. Perhaps try to re-create a linked
> server on server A to server B
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
>
>

Linked server problem

I linked two sql servers (one an x64 sql 2005 server the other a sql 2000 x86 server). The link is from the x64 box to the x86 box and I used Windows authentication to forge the links.

When the developer (who has Windows accounts on both boxes) logs in locally to the x64 box and runs a query, data is returned from the linked server. But when the same developer logs into his own client box and uses Management Studio to access the x64 box (via Windows authentication) and run the same query, he gets this error message"

"Login failed for user '(null)'. Reason: not associated with a trusted SQL Server Connection"

I presume that the developer should be able to access this data from his own box or can the linked data only be gotten from an account logged into the x64 box?

TIA,

barkingdog

I'm a little confused about what you mean by 'windows authentication' to forge the links.

when you create a linked server, you have the following options:

map a local login to a login on the remote server

connect using the user login's security context

connect using a specific (provided) login's security context

Whenever I've seen the above error, it has been because a developer had the server registered incorrectly in EM.

|||

Under SSMS, I thought that those options only applied when the connection trying to access the linked server was not listed in the top box ("Local server login to remote server login mappings".). So I ignored those options. Maybe I dun wrong!

The developer who is trying to access data on the linked server from SSMS from his own box is listed (using Windows authentication) in the Local Login section of the Linked server properties box of the x64 server. "Impersonate" is selected for that developer. No names are supplied for the Remote User\Remote password box as his Windows authentication will be used on the linked box. (Or so I think...)

TIA,

barkingdog

|||

I woul dfill in the other two boxes and see if that works.

I am struggling with my own linked server issues right now, related to windows authentication accross linked servers, but we would be using the

'connect using the user's login'.

You might try that and tell me if it works. ;)

|||

Here's what I have so far:

Suppose Server B is linked to Server A via a "Link Account". When I log on to Server B (using Remote Desktop) as "Link Account" I can successfully pull data from Server A, so the link is working. The problem: I logged into my own local workstation (not Server A or Server B) as "LinkAccount" fired up SSMS, connected to Server B as that account, and tried to pull the same data that I did earlier. The attempt fails saying that I am using a "null" account and no sql account exists.....blah, blah, blah.

I found this SQL 2005 BOL link that describes the situation:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9e302c29-639d-4509-ba1e-cf452582c5c3.htm

Apparently, what I wanted to do was not as simple as I thought it should be. I am creating a "double hop" and the servers need to be prepared as described in the help article. Otherwise, the error message is to be expected!

Linked server Problem

Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.
Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.
|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I
|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.

>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in[vbcol=seagreen]
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider
>
>.
>
|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.

>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>

Linked server problem

Hi,
I have two sql servers A and B linked together as LINKED SERVERS. A is
within the domain and B is outside the domain. They were working perfectly
fine a week back but now I can access A from B but I can't access B from A,
although B comes up under the Linked Servers list on A. What can be wrong?
Thanks in advance.
Well
Something has changed since last week. Perhaps try to re-create a linked
server on server A to server B
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
> Hi,
> I have two sql servers A and B linked together as LINKED SERVERS. A is
> within the domain and B is outside the domain. They were working perfectly
> fine a week back but now I can access A from B but I can't access B from
> A,
> although B comes up under the Linked Servers list on A. What can be wrong?
> Thanks in advance.
|||I have already done that. It does come up on the list of linked servers but
when I click on the tables icon it says "Server Not Accessible".
"Uri Dimant" wrote:

> Well
> Something has changed since last week. Perhaps try to re-create a linked
> server on server A to server B
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
>
>
sql

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.
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>

Friday, March 30, 2012

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Linked Server Problems with all providers

Linked Server Problems HELP HELP HELP !

Linked Server Problems HELP HELP HELP !

Linked Server Problems HELP HELP HELP !

Linked Server Problems

Linked Server Problems

Linked Server Problems

Wednesday, March 28, 2012

linked server problem slow queries

linked server problem slow queries

linked server problem slow queries

Linked server problem

Linked server Problem

Linked server problem

Linked server problem

Linked server Problem

Linked server problem

Linked Server problem

Sql Linked Server

Blog Archive

About Me