Connect to multi db's and servers

Question

Connect to multi db's and servers

Michael Lee-158245

SSC Eights!

Points: 980
More actions
September 17, 2010 at 11:12 am

#226573

Hi,
We have several databases within a main db server (MainServer). We also have two other (for now) servers(server1 & server2) setup with one database each (Different locations). I have a program to access all the db's and servers from within the program. This program is used to monitor the different units (each unit has its own db) within our company. For example I may need to get attendance for all units. I was doing this before the remote servers where added to the equation by using a query similar to this one:
Select * , 'Alternatives_BP' as NProgram, '1' as ProgramId
from Alternatives_BP.dbo.Admission
where PatientID = @Patientid and AdmissionDate = @DOA
Union
Select * , 'Alternitives_RH' as NarcoProgram, '2' as ProgramId
from Alternitives_Brook.dbo.Admission
where PatientID = @Patientid and AdmissionDate = @DOA
What is the best way to setup the databases and queries to do this?
What is the best way to setup the remote SQL servers?
Note: I've been trying to setup a linked server on the MainServer but have been having problems getting it to work.
Any other advise would be helpful.
Note: We don't have a trained DB Admin, I do the admin myself.
Thanks for any help.

Viewing 5 posts - 1 through 4 (of 4 total)

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

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 1

Linked server is your answer....

make sure on the remote server that you are logging into you have a created a user with rights that you require...example you want user A to have read only right to DBA

then on the main server , create your linked server by right clicking linked sever then under the security tab....select "be made using the security context" enter your new login that created on the remote server

let us know how it goes we should be able to help you out

liebesiech SSCrazy Points: 2580 More actions · Answer 2

I just scripted a linked server I have. Maybe you can ignore the whole server option stuff.

EXEC master.dbo.sp_addlinkedserver @server = N'YourServerName', @srvproduct = N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'YourServerName', @useself = N'True', @locallogin = NULL,

@rmtuser = NULL, @rmtpassword = NULL

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'collation compatible', @optvalue = N'false'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'data access', @optvalue = N'false'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'dist', @optvalue = N'false'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'pub', @optvalue = N'false'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'rpc', @optvalue = N'true'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'rpc out', @optvalue = N'true'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'sub', @optvalue = N'true'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'connect timeout', @optvalue = N'0'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'collation name', @optvalue = null

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'lazy schema validation', @optvalue = N'false'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'query timeout', @optvalue = N'0'

GO

EXEC master.dbo.sp_serveroption @server = N'YourServerName', @optname = N'use remote collation', @optvalue = N'true'

Information on MSDN: http://msdn.microsoft.com/en-en/library/ms190479.aspx

The service account is allowed to access the server I want to link to.

Hope it helps.

Shanalise Hall of Fame Points: 3188 More actions · Answer 3

I found this article to be very useful on linked servers

http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

and there more from msdn

Michael Lee-158245 SSC Eights! Points: 980 More actions · Answer 4

Thanks for the replies. I've got the linked server working with stored procedures (SP) that don't contain a function in them. How can I execute a SP that contains a function.

For example I'd like to run this

Select [RHSQL].Brook2.dbo.GetCurrentAdmissionDate(@PatientId) as AdminDate,

[RHSQL].Brook2.dbo.GetDOTFromDOA(@PatientId, [RHSQL].Brook2.dbo.GetCurrentAdmissionDate(@PatientId)) as DischargeDate

from [RHSQL].Brook2.dbo.Admission

When this run I get the following error message:

Remote function reference 'RH-SQL.Alternitives_Brook2.dbo.GetCurrentAdmissionDate' is not allowed, and the column name 'RH-SQL' could not be found or is ambiguous.

Please help

Thanks

Michael