CODEWITHSUNDEEP
sql-serverlocalsharedcross-server
user1315414
user1315414

Reputation: 21

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

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!

Upvotes: 1

Views: 7188

Answers (1)

Kaf
Kaf

Reputation: 33829

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...

Upvotes: 2

Related Questions