• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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
0
John_R_PETS
Asked:
John_R_PETS
  • 3
  • 2
4 Solutions
 
Ved Prakash AgrawalDatabase 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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
use Fully Qualified name


SELECT * FROM  [ServerName].[dbName].[ObjectOwner].TableName
0
 
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.

Thanks

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Aneesh RetnakaranDatabase AdministratorCommented:
As Ved17Nov mentioned in his post, you need to create a linked server first.
0
 
John_R_PETSAuthor Commented:
Can you advise how to create the link.  My knowledge of this is quite limited
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now