Connect to multi db's and servers

• 456789psw

  SSCarpal Tunnel

  Points: 4728

  More actions

  September 21, 2010 at 9:25 am

  #1224951

  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: 2610

  More actions

  September 22, 2010 at 1:33 am

  #1225358

  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

  September 23, 2010 at 12:24 am

  #1226104

  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

  October 22, 2010 at 10:20 am

  #1241042

  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