How to select data from other SQL Server tables in sql script?

Question

I want to know how to query data from other SQL Server tables in a SQL script.

I am writing a sql script running on 127.0.0.1\SQLINSTANCE1 , but inside the script, select the data from 127.0.0.2\SQLINSTANCE2 , then return the result data as part of the calculation.

After a google search, the sp_addlinkedserver and sp_addlinkedsrvlogin stored procedures sp_addlinkedserver to do this, but unfortunately the full sample does not work.

I have some work here, but it does not work as expected. For example, when it was executed again, some error will appear, for example, related to the server. But how can I execute this script to bind a server and then abandon it?

 DECLARE @remoteserver VARCHAR = '127.0.0.2\SQLINSTANCE2'; EXEC master.sys.sp_addlinkedserver @server = @remoteserver , @srvproduct = 'SQL Server'; EXEC master.sys.sp_addlinkedsrvlogin @rmtsrvname = @remoteserver , @useself = 'false' , @locallogin = NULL , @rmtuser = 'sa' , @rmtpassword = 'password'; 

On the other hand, how can I create an alias for this ugly name remoteserver? I prefer to use some elegant name like RS in select * from [RS].[db].[dbo].[table]

Answer 1

You can really use

 OPENDATASOURCE 

or

 OPENROWSET 

Note that you must enable the ad hoc option for distributed requests:

 sp_configure 'show advanced options', 1; RECONFIGURE; sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO 

Answer 2

You can use OPENROWSET , so you do not have to create and delete a linked server.