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
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Friday, March 30, 2012
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
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 !
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|||tried this. still does not work.|||It works with a SQL server login.
I had our network administrator setup the active directory stuff but i
am still getting the same problem.|||Take a look at this link there is a setting in the DTC Configuration,
that might be your problem.
http://www.dotnetjunkies.com/WebLog/daveadamson/archive/2005/11/22/133928.aspxsql
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|||tried this. still does not work.|||It works with a SQL server login.
I had our network administrator setup the active directory stuff but i
am still getting the same problem.|||Take a look at this link there is a setting in the DTC Configuration,
that might be your problem.
http://www.dotnetjunkies.com/WebLog/daveadamson/archive/2005/11/22/133928.aspxsql
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.
================================================== ====
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
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.
========================================
==============
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.
========================================
==============
linked server problem with error message 7391
I have two computers both running Windows Server 2003 and SQL Server 2000. I
have a trigger on a table located on the first computer which includes a line
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what is
wrong.
After reading the many threads about this I have checked the following items:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.
See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/default...b;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000. I
> have a trigger on a table located on the first computer which includes a line
> of code that inserts into a table on the second computer. The two computers
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup must
> have changed so that it doesn't work anymore, how ever I have no idea what is
> wrong.
> After reading the many threads about this I have checked the following items:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is run
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run over
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same error.
> So essentially I can run the code as a stored procedure but not as a trigger.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
have a trigger on a table located on the first computer which includes a line
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what is
wrong.
After reading the many threads about this I have checked the following items:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.
See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/default...b;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000. I
> have a trigger on a table located on the first computer which includes a line
> of code that inserts into a table on the second computer. The two computers
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup must
> have changed so that it doesn't work anymore, how ever I have no idea what is
> wrong.
> After reading the many threads about this I have checked the following items:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is run
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run over
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same error.
> So essentially I can run the code as a stored procedure but not as a trigger.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
linked server problem with error message 7391
I have two computers both running Windows Server 2003 and SQL Server 2000.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
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 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 from SQL 2005 to SQL 2000 Database
I have been unable to get my linked server to work when running queries against it in SQL Server 2005.
Example SQL Statement on my SQL 2005 server that I'm trying to run:
select top 100 * from [linkedservername].databsename.dbo.tablename
Error Message:
Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.
Print screen of the security properties of my SQL 2005 Linked Server:
http:
The user sqladmin definitely has the right permissions to the right databases on the target linked server.
On your screenshot, your login is dschinkel, it doesn't have a mapping. If you want all of your accounts to automatically use the apexfm\sqladmin account, you should select the radio button that says "Be made using this security context:" and fill in the login information there.
Be advised that if SQLAdmin has full administrative privileges on the remote server than ANY user with access privileges to the local server will have full admin privileges to the remote server. A very scary proposition indeed.
sqlLinked 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?
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,
I am running following script on server A. Server B is configured as linked server to A.
SET xact_abort ON
GO
begin tran
INSERT INTO B.table1.dbo.CUSTOMER (CUSTOMER_ID,PLAN_CODE)
VALUES (1001,100)
rollback tran
The above script when run run on server A gives following error:-
Error..
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Pls. advise why this error occurs. How to insert records in linked server.
Is the Distributed Transaction Coordinator (MSDTC) service running on both computers?|||yes|||change begin tran to BEGIN DISTRIBUTED TRANSACTION and see
Madhu
|||that also doesnot work. Is it because A is windows 2003 & B is windows 2000 server?|||what is the service pack on these box
Madhu
|||Hi Shah,
If you still have tht problem, here is the link for the solution.
http://support.microsoft.com/?kbid=873160
linked server problem
I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
Linked server problem
Hi,
I am running following script on server A. Server B is configured as linked server to A.
SET xact_abort ON
GO
begin tran
INSERT INTO B.table1.dbo.CUSTOMER (CUSTOMER_ID,PLAN_CODE)
VALUES (1001,100)
rollback tran
The above script when run run on server A gives following error:-
Error..
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Pls. advise why this error occurs. How to insert records in linked server.
Is the Distributed Transaction Coordinator (MSDTC) service running on both computers?|||yes|||change begin tran to BEGIN DISTRIBUTED TRANSACTION and see
Madhu
|||that also doesnot work. Is it because A is windows 2003 & B is windows 2000 server?|||what is the service pack on these box
Madhu
|||Hi Shah,
If you still have tht problem, here is the link for the solution.
http://support.microsoft.com/?kbid=873160
linked server problem
I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.
You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>
|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.
You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>
|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
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.
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
Subscribe to:
Posts (Atom)