Linked Server Problems from SQL2005

When I run linked server queries from SQL2005 it fails.
Query Example :
SELECT * FROM [servername].master.dbo.sysobjects
Error:
OLE DB provider "SQLNCLI" for linked server " servername" returned
message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote
host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
Notes:
The linked server is configured to use the current logins security
context. It does not matter whether I connect to SQL2000 or SQL2005.
I have a case with MS (6 hours on the phone yesterday), but no help
yet. All spn settings are as recommended by Microsoft. The MS engineer
states that SQL is not passing the login as indicated in a capture file
from netcap.
Any help is appreciated.
TerryHi
Is the remote server SQL2005? Have you tried using a SQL Server login? Can
you use openquery to return the result?
John
"Terry" wrote:

> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>|||Terry wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>
You are connecting to ServerA using Windows Authentication, not a SQL
login. The linked server connection is attempting to pass your SQL
login over to ServerB. You'll have to login to ServerA using a true SQL
login, or setup an explicit user mapping in the linked server definition.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I should be able to use Windows Authenitication, and have always in the
past with SQL2000. Auditing concerns require I know who is connecting.
Mapped logins do work but this is not a solution.
The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
Terry
Tracy McKibben wrote:
> Terry wrote:
> You are connecting to ServerA using Windows Authentication, not a SQL
> login. The linked server connection is attempting to pass your SQL
> login over to ServerB. You'll have to login to ServerA using a true SQL
> login, or setup an explicit user mapping in the linked server definition.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||On the 2000 server, run the instcat.sql from the latest service pack you
have installed. This fixes the connect strings. You have to edit the
instcat.sql to put in the correct SQL version. This is documented in the
instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
your problem.
Jackie
"Terry" <tduffy@.calamos.com> wrote in message
news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...
>I should be able to use Windows Authenitication, and have always in the
> past with SQL2000. Auditing concerns require I know who is connecting.
> Mapped logins do work but this is not a solution.
> The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
> Terry
> Tracy McKibben wrote:
>|||Thank you for the responses...
instcat.sql fixes the schema binding errors on 2005->2000 servers. It
does not resolve my issues. instcat has already been run on my 2000
boxes.
Terry
Jackie Brophy wrote:[vbcol=seagreen]
> On the 2000 server, run the instcat.sql from the latest service pack you
> have installed. This fixes the connect strings. You have to edit the
> instcat.sql to put in the correct SQL version. This is documented in the
> instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
> your problem.
> Jackie
> "Terry" <tduffy@.calamos.com> wrote in message
> news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...|||Hi Terry
I assume that you are using the latest service packs on both instances?
Previously this error has been the result of a virus checker or firewall
blocking communications or possibly a faulty network. Do you get any command
s
running on the remote system?
Windows authentication should not require the user to map a linked server
login, but you can run it with the remote server name parameter only to make
sure these are mapped.
You may want to try and check what protocols you are using and try a
different one!
John
"Terry" wrote:

> Thank you for the responses...
> instcat.sql fixes the schema binding errors on 2005->2000 servers. It
> does not resolve my issues. instcat has already been run on my 2000
> boxes.
> Terry
> Jackie Brophy wrote:
>|||We are still on RTM SQL05, but similar issues (double hops only) exist
on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
Firewall and anti-virus are not any issue. I am not sure what you mean
by "commands
running on the remote system." Also, any ideas what might be "faulty"
in the network.
Linked servers with mapped logins works.
Thanks
John Bell wrote:[vbcol=seagreen]
> Hi Terry
> I assume that you are using the latest service packs on both instances?
> Previously this error has been the result of a virus checker or firewall
> blocking communications or possibly a faulty network. Do you get any comma
nds
> running on the remote system?
> Windows authentication should not require the user to map a linked server
> login, but you can run it with the remote server name parameter only to ma
ke
> sure these are mapped.
> You may want to try and check what protocols you are using and try a
> different one!
> John
> "Terry" wrote:
>|||Hi Terry
Use SQL profiler on both machines to try and see what is happening. Did you
check the protocols being used?
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
You may want to make sure that this still occurs on the latest hotfixes for
each version. If that fails you may want to raise an incident with PSS for
this.
John
"Terry" wrote:

> We are still on RTM SQL05, but similar issues (double hops only) exist
> on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
> Firewall and anti-virus are not any issue. I am not sure what you mean
> by "commands
> running on the remote system." Also, any ideas what might be "faulty"
> in the network.
> Linked servers with mapped logins works.
> Thanks
>
> John Bell wrote:
>

Linked server problem

SELECT
A.PATIENTID
FROM
xxxx.yyyyy.dbo.Patient A
SELECT
*
FROM
xxxx.yyyyy.dbo.Patient A
The first sql does not work and the second one works over linked server.
Does anybody know??Do you get an error? What is it?|||Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'patientid'.|||Check the spelling of the column name when selecting all columns.|||works fine for me....

select * from server.database.owner.table A
will work OK

select A.column_name from server.database.owner.table A
wiill work OK too....

Spelling most likely problem...

Linked Server Performance with Stored Procedure

I have set up a stored procedure to do a quick lookup similar to
dbo.get_Value_for_Id @.id int

When I invoke the stored procedure from a database on the same server instance as the stored procedure lives, it processes 10K lookups in about 4 seconds.

When I invoke the stored procedure from a different server (via MSSM with a Linked server), it took about 4 minutes to perform 10K lookups. Not good. My question is what can I do to get back to the much faster response times.

I set up a little .Net app to do the remote calls, and it was back to the 4 second range for the 10K calls, in my mind eliminating the network as the primary bottleneck.

Items I've tried:

SET XACT_ABORT is OFF on both ends. No visible results.

SET REMOTE_PROC_TRANSACTIONS OFF . This appears to have put the distributed transactions issue to bed.

Run SP_SERVEROPTION "collation compatible" option to true for the server link. Ran this on both ends. This setting supposedly tells SQL Server the collation sequence and character sets match on both ends. Probably a good idea, even though it didn't make much difference here.

Run the stored procedure remotely using OpenQuery. Took some creativity, given OpenQuery doesn't accept variables as arguments. Got this one working, with run times down to the 90-135 second range (1:30-1:45).

I'm still an order of magnitude slower than I want to be. I suppose I could implement a .NET solution (select the key out of a database on server x, lookup the key on server y), but that sounds more like a workaround than actually using SQL Server capabilities. Any suggestions?

try

exec linkedserver.mydb.dbo.sp_executesql N'exec mysp'

|||

Thanks for the suggestion, Nigel.

I setup two variations of the sp_executesql call, one calling the stored procedure, the second invoking the main select in the stored procedure. In both cases, when run during an MSSM session that was logged into the server that was doing the work, I could loop through 10000 calls in 4-5 seconds. And, in both cases, when logged into my desktop, it took about 4 and a half minutes to run the same script. So this didn't resolve the issue. Interestingly enough, I did see some surging in the activity of my desktop msdtc.exe (in windows task manager), so I'm guessing this process is somehow getting the distributed transaction server involved.

To confirm the involvement of the distributed transaction server, I went into my desktop and turned off the msdtc service. With this service turned off, the script would not run (MSG 8501 MSDTC on server xxx not available). So that seems to confirm I have not evaded the overhead of MSDTC.

To minimize confusion, following is the test script I am using...

set nocount on

set XACT_Abort off

set REMOTE_PROC_TRANSACTIONS off

declare @.Encrypt_id int

declare @.c int

declare @.t table (UnEncrypted_Card varchar(19))

declare @.sql nvarchar(500)

declare @.ParamDef nvarchar(500)

set @.c = 0

while (@.c < 10000)

Begin

set @.Encrypt_id = Rand() * 100000

set @.sql = N'exec dbo.p_Get_Card_Number_By_Id_inst @.Enc_id , ''123456'' ';

-- set @.sql = N'select Value_Encrypted as Value from dbo.tb_Encrypted_Store where Encrypt_Id = @.Enc_Id';

set @.ParamDef = N'@.Enc_Id int';

-- print @.sql

insert into @.t exec linkservername.dbname.dbo.sp_executesql @.sql, @.ParamDef, @.Enc_id = @.Encrypt_Id

set @.c = @.c + 1;

End

Linked Server Performance

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

linked server null

If I remote on to sql05 and open management studio
and select * from sql2000.dbo.tablename
Where sql2000 is a link from sql server05 to sql server 2000
it works
Same thing happens if I just try Test Connection under Linked Servers on my
local machine.
If on my desktop in ManagementStudio I try the same query I get:
TITLE: Microsoft SQL Server Management Studio
"The test connection to the linked server failed."
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476[ /url]
BUTTONS:
OK
Hi Chuck,
I understand that the linked server failed to work on your local machine
with the error:
"TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)"
If I have misunderstood, please let me know.
The error indicates that the linked server login is not associated with a
trusted SQL connection and that the login account did not have the valid
user's password.
I would like to check with you the following questions:
1. How did you add the linked server?
2. Did you use SQL login or Windows login for the linked server?
3. Is your local server in the same domain as the remote server?
4. What is your remote SQL Server login mode, Windows Authentication or
Mixed?
If your servers are in the same domain and your local domain logon account
is allowed on the remote server, I recommend that you run "osql /Stcp:HRSQL
/E" from command prompt to check if you can connect to the remote SQL
Server instance HRSQL via Windows Authentication.
If you can connect to the remote SQL Server instance, remove the linked
server from SSMS:
Server Objects->Linked Servers
Then run the following statements to add the linked server:
sp_addlinkedserver 'HRSQL'
Go
sp_addlinkedsrvlogin 'HRSQL','true'
Go
Then run the query again.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
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.
================================================== ====

linked server null

If I remote on to sql05 and open management studio
and select * from sql2000.dbo.tablename
Where sql2000 is a link from sql server05 to sql server 2000
it works
Same thing happens if I just try Test Connection under Linked Servers on my
local machine.
If on my desktop in ManagementStudio I try the same query I get:
TITLE: Microsoft SQL Server Management Studio
--
"The test connection to the linked server failed."
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click:
http://go.microsoft.com/fwlink?Prod...54&LinkId=20476
BUTTONS:
OK
--Hi Chuck,
I understand that the linked server failed to work on your local machine
with the error:
"TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)"
If I have misunderstood, please let me know.
The error indicates that the linked server login is not associated with a
trusted SQL connection and that the login account did not have the valid
user's password.
I would like to check with you the following questions:
1. How did you add the linked server?
2. Did you use SQL login or Windows login for the linked server?
3. Is your local server in the same domain as the remote server?
4. What is your remote SQL Server login mode, Windows Authentication or
Mixed?
If your servers are in the same domain and your local domain logon account
is allowed on the remote server, I recommend that you run "osql /Stcp:HRSQL
/E" from command prompt to check if you can connect to the remote SQL
Server instance HRSQL via Windows Authentication.
If you can connect to the remote SQL Server instance, remove the linked
server from SSMS:
Server Objects->Linked Servers
Then run the following statements to add the linked server:
sp_addlinkedserver 'HRSQL'
Go
sp_addlinkedsrvlogin 'HRSQL','true'
Go
Then run the query again.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
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.
========================================
==============

linked server dynamic SQL help

declare @.var varchar(50)
select @.var='ServerA'
select name from @.var.master.dbo.sysobjects -- Need help here
Right now its just one server but eventually i want to probably use a group
of servers and kinda use a cursor.You can do it via dynamic sql
declare @.var varchar(50)
select @.var='ServerA'
exec ('select name from '+ @.var + '.master.dbo.sysobjects')
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23uSu9Y7DFHA.1188@.tk2msftngp13.phx.gbl...
> declare @.var varchar(50)
> select @.var='ServerA'
> select name from @.var.master.dbo.sysobjects -- Need help here
> Right now its just one server but eventually i want to probably use a
group
> of servers and kinda use a cursor.
>|||Yup i should have mentioned that i tried that.. and thats why posted the
other message with some code. Its where I want to get it in a cursor
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:epeI$i7DFHA.3824@.TK2MSFTNGP10.phx.gbl...
> You can do it via dynamic sql
> declare @.var varchar(50)
> select @.var='ServerA'
> exec ('select name from '+ @.var + '.master.dbo.sysobjects')
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23uSu9Y7DFHA.1188@.tk2msftngp13.phx.gbl...
> group
>|||Why do you want to use a cursor? They can be performance hogs.
For static sets of data, I use set operations.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OtHIKr7DFHA.1040@.TK2MSFTNGP09.phx.gbl...
> Yup i should have mentioned that i tried that.. and thats why posted the
> other message with some code. Its where I want to get it in a cursor
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:epeI$i7DFHA.3824@.TK2MSFTNGP10.phx.gbl...
>