Cross-server SQL query to read from one table (shared) and write to another (local)

Question

I've been having a very difficult time trying to read a table on one server and writing to another existing table on my hard drive (local... created using SQL Server Express).

Here's my code:

insert into [DPS-ABC1DE2\SQLEXPRESS].my_LOCAL_DATABASE.dbo.SHIPMENTS
select
    CUST_NUMBER,
    SHIPMENT_ID,
    SHIP_DATE,
    MODE_CODE,
    MILES,
    WEIGHT,
    AMOUNT_PAID
from SHARED_DATABASE.dbo.SHIPMENTS
where datepart(year,SHIP_DATE)= 2012 and datepart(month,SHIP_DATE) = 1

I get the following error message when I run this:

Msg 7202, Level 11, State 2, Line 7
Could not find server 'DPS-ABC1DE2\SQLEXPRESS' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

So I've tried using

EXEC sp_addlinkedserver [DPS-ABC1DE2\SQLEXPRESS]

but I get this error:

"Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 29
User does not have permission to perform this action."

I'm a rookie SQL programmer, so I've had to research this extensively but with no success. Any help would be appreciated!

'User does not have permission to perform this action" - You do not have sufficient permissions, simple as that. — Mitch Wheat, Apr 05 '12 at 14:05
FYI, cross-server queries can be [IMHO] extremely difficult to get to work consistantly. Its definitely an advanced subject, and one you should be wary of, at least so long as you consider yourself a rookie. — Philip Kelley, Apr 05 '12 at 14:10

score 2 · Answer 1 · answered Apr 05 '12 at 14:45

sp_addlinkedserver execute permissions default to members of the sysadmin and setupadmin fixed server roles. Check out this link on how to sort it out on sql server 2005.

Once you get rid of that issue you could use the following to link and login to the other server.

--add the linked server and then login
EXEC sp_addlinkedserver 'DPS-ABC1DE2\SQLEXPRESS',N'SQL Server';

EXEC sp_addlinkedsrvlogin @rmtsrvname='DPS-ABC1DE2\SQLEXPRESS', 
        @useself='false',
        @rmtuser='login_username',
        @rmtpassword='login_password'

--do your job here                    
Insert into [DPS-ABC1DE2\SQLEXPRESS].my_LOCAL_DATABASE.dbo.SHIPMENTS
Select...

--drop the linked server login and then drop the server
EXEC sp_droplinkedsrvlogin 'DPS-ABC1DE2\SQLEXPRESS', NULL
EXEC sp_dropserver 'DPS-ABC1DE2\SQLEXPRESS', NULL;

Hope this helps...

Cross-server SQL query to read from one table (shared) and write to another (local)

1 Answers1