Link 2 servers in SQL Server 2005

Question

Post reply

Link 2 servers in SQL Server 2005

mashikoo

Ten Centuries

Points: 1188
More actions
December 20, 2010 at 9:29 am

#230898

Hi All,
I'm trying to link 2 servers to enable me query them at the same time. Does anyone know how I can link them? Any input will be appreciated.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 1

You could create a linked server on each to the other - look under server objects in SSMS then you can query them like

SELECT

*

FROM myTable a

INNER JOIN [otherserver].[mydb].[dbo].[myTable] b ON a.PKVal = b.PKVal

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

The first response is the right track. I would caution you on how you use linked servers. Do not do large table joins, query for discrete recordsets.

While the DB engine often does a good job building the query that is run on the remote server, it as often does not. Add a third server into the mix and it gets messy.

Linked servers can be handy, but take it from someone who over-used them in the past, they can also cause all sorts of stability problems.

Now I often look at how much data I need, how big it is on disk, how current it needs to be, and what my *real* business requirements are. By real I mean what is practical and necessary, far too often the business requirements I have seen do not represent the real world and need to be challenged.

I'll climb off my soapbox now.. Just be cautious.

CEWII

mashikoo Ten Centuries Points: 1188 More actions · Answer 3

Thanks for the guidances All. After creating the linked server and try to run a query, I get this error message

"Msg 7430, Level 16, State 3, Line 1

Out-of-process use of OLE DB provider "SQLNCLI" with SQL Server is not supported"

Looks like am doing something wrong..please help!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Under linked servers|Providers, if you get the properties of SQLNCLI10, is the "All inprocess" box checked?

If not, check it, you might have to restart SQL to get it to work for wait for 10 minutes or so..

CEWII

mashikoo Ten Centuries Points: 1188 More actions · Answer 5

I'm actually linking sqlserver 2005 and sql server 2008. From 2008 to 2005 the connection is successful but from 2005 to 2008, I get this error " test connection to the linked server failed' What can be causing this? Inputs will be appreciated.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Is there a SQLNCLI in the providers list? I don't have a 2005 available at the moment so I can't answer it myself. but look at my questions above and answer them.

CEWII