Access multiple data servers

I am using MS SQL 2005.

We have 35 databases split on 2 servers PS-SQL and PS-SQL-02

I need to do a query that accesses data from both servers.  I am looking for the syntax to help with this.

Is there an issue with having - in the server name.

Thanks John
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
use Fully Qualified name

SELECT * FROM  [ServerName].[dbName].[ObjectOwner].TableName
Ved Prakash AgrawalConnect With a Mentor Database Consultant/Performance ArchitectCommented:
There is no issue.

you can create the Linked Server. I can say the name of your linked serer is  LinkesPSSQL02

SELECT * from LinkesPSSQL02.database.dbo.TableName
John_R_PETSAuthor Commented:
Hi aneeshattingal:

I get this error

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

when i run SELECT * FROM  [PS-SQL].[Wholesale].[DBO].Stocktrans

I need to run a query that unions between 2 databases - one on each server.


Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
As Ved17Nov mentioned in his post, you need to create a linked server first.
John_R_PETSAuthor Commented:
Can you advise how to create the link.  My knowledge of this is quite limited
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.