how to connect to 2 sql servers

  • Thread starter Thread starter Trevor
  • Start date Start date
T

Trevor

I have one adp project.
I have 2 sql servers each with a database.
Both servers have there own username and passwords

I would like to connect my adp project to both servers.

How can I do this?
 
You need to open Enterprise Manager and do this:

Goto Security-Linked Servers
Right click - New linked server
Fill in those properties and you will have a link.

Then when you need to call on data from that server, use the prefix...

mylinkedserver.mylinkeddatabase.dbo.mylinkedtable

For instance, if I had a linked server named NewYork1, the database on that
server was named Sales, I would do this...

Select * from NewYork1.Sales.dbo.Salesmen

I find it is sometimes easier if you make a view on your local database that
selects the data from the link, then use the now local view instead. At
that point you have a middle layer where you can do some adjustment in case
the table design changes on the other server. It also saves you from having
to type he entire linked server qualifier on the queries you write by
allowing you to simply use the view name instead.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
 
Back
Top