Remote SQL Linked Server not displaying databases
I have a remote sql server 2008 and I am trying to add it to a 2005 database as a linked server via 2008 management studio. I am using the attached code. I can connect to the remote database fine using management studio and it creates the linked server but under Catalogs it only has 'default' and when i try to drill down or execute the following code:
'select * from web..dbo.tblEmail'
I get the error
'TCP Provider: An existing connection was forcibly closed by the remote host.'
'select * from web..dbo.tblEmail'
I get the error
'TCP Provider: An existing connection was forcibly closed by the remote host.'
/****** Object: LinkedServer [WEB_SQLSRV] Script Date: 04/07/2010 10:40:47 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'web', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=MYIPADDRESS,1433\HAYDEN'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'web',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='######'
GO
ASKER
I see what you mean, I overwrote a ' when i typed in MYIPADDRESS. Script is auto generated and runs OK.
Removing the instance name didn't change anything.
Cheers.
Removing the instance name didn't change anything.
Cheers.
Problem solved?
ASKER
Nope - Removing the instance name didn't change anything.
Can you try to create an alias in the source server and then use that alias name in datasrc parameter?
And what versions/editions and respectives SP's you have for each SQL Server?
And what versions/editions and respectives SP's you have for each SQL Server?
ASKER
Still got the error:
TCP Provider: An existing connection was forcibly closed by the remote host.
Created alias on local machine and remote machine.
Using SQL 2008 on remote machine. Using SQL 2005 on local machine. Need to get the 2 talking basically. I can connect OK to the remote machine using 2008 Management studio and execute code fine.
TCP Provider: An existing connection was forcibly closed by the remote host.
Created alias on local machine and remote machine.
Using SQL 2008 on remote machine. Using SQL 2005 on local machine. Need to get the 2 talking basically. I can connect OK to the remote machine using 2008 Management studio and execute code fine.
None of those SQL Servers are Express Edition?
You need to have SQL Server Browser service running in remote server.
You need to have SQL Server Browser service running in remote server.
ASKER
No all Workgroup edition.
SQL Server Browser is running on the remote server.
SQL Server Browser is running on the remote server.
It's getting harder :o)
Please check this article and see if it helps you: http://support.microsoft.com/kb/293107/en-us
Good luck
Please check this article and see if it helps you: http://support.microsoft.com/kb/293107/en-us
Good luck
For how long does your query run before showing the error you posted here?
In case your query runs for a long time, it's possible that your link connection is being timed out from the remote host.
To correct this, please go to the remote host and look at server properties (SQL server properties), look at the remote connection timeout under "connections" option, then increase the time to allow remote connections on the remote server from the default seconds by increasing the time to that which is sufficient to run your query remotely.
Determine this time by running the query locally when the server is busy, then give it some extra time in case things are different and the server slows down.
Let me know if this corrects your issue.
To correct this, please go to the remote host and look at server properties (SQL server properties), look at the remote connection timeout under "connections" option, then increase the time to allow remote connections on the remote server from the default seconds by increasing the time to that which is sufficient to run your query remotely.
Determine this time by running the query locally when the server is busy, then give it some extra time in case things are different and the server slows down.
Let me know if this corrects your issue.
ASKER
Thanks for your comments pastorchris but I don't think the problem is a timeout one. Under the linked servers I am getting 'default' under catalogs, I haven't got a db called default and none of my databases are showing up. Also the queries I have tried are just select statements returning 1 line, and the error message I am getting doesn't mention anything to do with timeout (TCP Provider: An existing connection was forcibly closed by the remote host.)
VMontalvao - No luck I am afraid. The SQL server is working fine on port 1433 and I can connect through management studio. However connectting it as a linked server fails. Maybe because it is 2005 connecting to 2008?
VMontalvao - No luck I am afraid. The SQL server is working fine on port 1433 and I can connect through management studio. However connectting it as a linked server fails. Maybe because it is 2005 connecting to 2008?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also let me know what you get when you right-click on the linked server and choose "test connection".
Lastly, in your query, specify the remote database name as follows:
SELECT * FROM [web].[replace_with_your_d
Lastly, in your query, specify the remote database name as follows:
SELECT * FROM [web].[replace_with_your_d
Finally, if all things fail, try creating the linked server this way:
EXEC master.dbo.sp_addlinkedser
GO
EXEC master.dbo.sp_addlinkedsrv
GO
Then execute the query as follows:
SELECT * FROM [MYIPADDRESS\HAYDEN].[repl
EXEC master.dbo.sp_addlinkedser
GO
EXEC master.dbo.sp_addlinkedsrv
GO
Then execute the query as follows:
SELECT * FROM [MYIPADDRESS\HAYDEN].[repl
ASKER
Thanks
And if you use port number then you shouldn't need instance name (\HAYDEN).
Cheers