sp_addlinkedserver ??


 Hi all!

 I am writing an online remote database migration tool.
 I need to write a stored procedure which can connect
 to a remote SQL Server entered by the web-user.
 The user will enter the hostname, username and password for the remote server.
 How do I connect to this server?
 I found something: sp_addlinkedserver - It seems to link to another server :) But how do I use it and how do I perform my SELECT and DELETE statements?

 Regards,


 D. Walsarie
LVL 2
dwalsarieAsked:
Who is Participating?
 
DecaysChampionConnect With a Mentor Commented:
Dwalsarie,

If you're asking how to use the command...
This is a sample piece of code I wrote for a stored procedure that compares two databases on different servers based on input of the sa user name and password.

Sorry about the mess.  If you copy/paste it into notepad it will look a lot better.

-DecaysChampion

begin
     exec ('sp_addlinkedserver '+@targetsrv)      /* Adds target server as a linked server */
          create table ##tempt2 (w varchar(35),x varchar(35),y varchar(35),z int)
          set @cmd1='%data access%'                                   /* Checks if 'data access' option is set */
          insert into ##tempt2 exec('sp_helpserver '+@targetsrv)
          if ((select y from ##tempt2 where y like @cmd1) is null)
               begin
                    set @cmd2='sp_serveroption '''+@targetsrv+''',''data access'','+'''true'''
                    set @remaccset=1
                    exec (@cmd2)  /* Configures target server for remote data access */
                    drop table ##tempt2
               end
         
     end

/* Sets default mapping to created login */

set @cmd2='sp_addlinkedsrvlogin '''+@targetsrv+''',''false'',''sa'',''scratchlogin'',''xyz123'''
exec (@cmd2)
0
 
zahid_synentiaCommented:
e.g.

select *
from LINKEDSERVERNAME.databasename.dbo.tablename
0
 
dwalsarieAuthor Commented:

 Could you be complete, please?
0
 
zahid_synentiaCommented:
You just query the tables on the linked server as normal except you need to prefix the table name with the linked server name and the databasename on the linked server

so
-------------------------------------------------
select *
from LINKEDSERVERNAME.DATABASENAME.dbo.TABLENAME
------------------------------------------------
where

1, LINKEDSERVERNAME - name of linked server
2, DATABASENAME-  name of database table belongs to on the server
3, dbo - database owner
4, TABLENAME- name of table you want to query on the linked database
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.