Friday, March 18, 2011

How to select data from another sql server

4 comments
We have a target to run a JOB on server to import data from another SQL server instance.

For example take
Server 1 - Main
Server 2 - From which we have to import.

If you have admin rights on both the servers you can "Link the servers" under "Server Objects>>Linked Servers" Right click on the same and provide all the parameters of server or as an alternate you can run following command.

EXEC sp_addlinkedserver
@server = 'db1',
@srvproduct = 'SQLServer OLEDB Provider',
 @provider = 'SQLOLEDB',
 @datasrc = 'server2',
 @provstr='User Id=sa; Password=abc'

The above command parameters:-
 @server- An identification name of server.
@datasrc- Your server instance name or IP.

Your SQL will look like:-
Select * from db1.dbname.dbo.tablename

If sometimes the server will get changed, the above statement gives you the option to change the user name, password and server at any points of time without effecting the relevant SQL and SP written the basis of same.

Second Method (You can use the same if you does not have admin rights to add Linked server)

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=Server2;User ID=sa;Password=abc'
         ).dbname.dbo.tablename
        

In the above case you have to hard code all the parameters which you have to change in all the SQL and SP if something will get changed in future. So, I prefer to use Linked Server

4 comments:

IT Solutions said...

I’m really impressed with your writing skills and also with the layout on your blog. Is this a paid theme or did you modify it yourself? Anyway keep up the nice quality writing, it is rare to see a great blog like this one today.

Great Solution!

ppc experts said...

Inbound Technical Support call | Call at: +91 981-142-0083 | inbound calls for tech support | tech support inbound calls A great online marketing company for tech support inbound calls, Inbound Technical Support call by osiel web

ppc experts said...

Inbound Technical Support call | Call at: +91 981-142-0083 | inbound calls for tech support | tech support inbound calls A great online marketing company for tech support inbound calls, Inbound Technical Support call by osiel web

James Brown said...

I am definitely enjoying your website. You definitely have some great insight and great stories.
SEO services pakistan