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


/****** 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

Open in new window

suromaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You should check the ('). Looks like some aren't closed.
And if you use port number then you shouldn't need instance name (\HAYDEN).

Cheers
0
suromaAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Problem solved?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

suromaAuthor Commented:
Nope - Removing the instance name didn't change anything.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
suromaAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
None of those SQL Servers are Express Edition?
You need to have SQL Server Browser service running in remote server.
0
suromaAuthor Commented:
No all Workgroup edition.
SQL Server Browser is running on the remote server.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
0
Chris MConsulting - Technology ServicesCommented:
For how long does your query run before showing the error you posted here?
0
Chris MConsulting - Technology ServicesCommented:
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.


0
suromaAuthor Commented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok suroma. I only have one more shot. Can you separate both commands with a 'GO'? Like this:

 /****** 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'
GO
 /* 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris MConsulting - Technology ServicesCommented:
I have a suggestion for you:
Kindly create a user account with default database as "web" on the remote server.

Now try to use this new user's credentials in the linked server and let me know how it goes.
What i am trying to achieve is to eliminate the switching of databases while querying.

0
Chris MConsulting - Technology ServicesCommented:
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_dbname].[dbo].[tblEmail]
0
Chris MConsulting - Technology ServicesCommented:
Finally, if all things fail, try creating the linked server this way:
EXEC master.dbo.sp_addlinkedserver @server = N'MYIPADDRESS\HAYDEN', @srvproduct=N'SQL Server'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYIPADDRESS\HAYDEN',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='######'
GO

Then execute the query as follows:
SELECT * FROM [MYIPADDRESS\HAYDEN].[replace_with_your_dbname].[dbo].[tblEmail]

0
suromaAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.