February 9, 2009 at 3:18 am
I have two SQL servers.
One is a 64 bit SQL 2005 server - SP2.
The other is a 32 bit SQL 2000 server - SP4.
I need to run a query on the first server and get data from the second server.
However I get the following error message :-
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Had a look at microsoft and although they suggest a couple of things nothing seems to fix the issue.
Anyone have any ideas ?
Cheers
February 9, 2009 at 3:30 am
Just wanted to know how you are running the query, is it through a linked server or from ssis.
transport level errors many a times get resolved when the query is run from query analyser for the second time.
and also you can get some useful info here at the below blog.
http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx
Sriram
February 9, 2009 at 3:31 am
Good point ! I am running the query via a linked server. Have tried several times but to no avail 🙁
Will have a look at the link as well.
February 9, 2009 at 3:32 am
Simon Smith (2/9/2009)
I have two SQL servers.One is a 64 bit SQL 2005 server - SP2.
The other is a 32 bit SQL 2000 server - SP4.
I need to run a query on the first server and get data from the second server.
However I get the following error message :-
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Had a look at microsoft and although they suggest a couple of things nothing seems to fix the issue.
Anyone have any ideas ?
Cheers
* Create SQL 2000 is a Linked server in SQL Server 2005.
* If same problem persists after creating linked server, check the network utility of SQL Server.
regards.
February 9, 2009 at 3:36 am
How did you connect to the remote server?
What queries you are running on the server?
Have you enable remote connections on the remote server?
Does the firewall is blocking the connection?
Is remote server is listening on some other port?
EDIT:
I was doing some other stuff while this window was open, so I couldn't able to see the above replies...
--Ramesh
February 9, 2009 at 3:39 am
How did you connect to the remote server? - I am using a linked server.
What queries you are running on the server? - Just doing a simple select.
Have you enable remote connections on the remote server? - Yes, other servers can conect to the SQL2000 box (admitidly SQL 2000 to SQL 2000)
Does the firewall is blocking the connection? - No
Is remote server is listening on some other port? - No, usual port.
February 9, 2009 at 3:39 am
Simon Smith (2/9/2009)
Good point ! I am running the query via a linked server. Have tried several times but to no avail 🙁Will have a look at the link as well.
could you please confirm:
* are you able to access SQL 2005 from SQL 2000 (via Query Analyzer) ?
* use "OPENROWSET" in your query.
Regards,
February 9, 2009 at 3:46 am
Yes I can connect from SQL2000 through to SQL2005.
February 9, 2009 at 4:14 am
is RPC In and RPC Out options checked in advanced option??
February 9, 2009 at 5:31 am
Have you mapped the local logins with that of remote logins?
If yes then are you executing the query under the context of the local login?
You can test the linked server using the procedure...
EXECUTE sp_testlinkedserver N'linked_server_name'
--Ramesh
February 9, 2009 at 6:46 am
Thanks for all the help guys.
I have altered the linked server to run off the sa password and it works fine. Not sure why it won`t map a login properly. Was definately using an exisiting user with correct access rights. Something to play with on our test boxes I think.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply