June 23, 2009 at 3:53 pm
I have SQL Server 2005 setup on one of my servers. I need to connect to another SQL 2005 server and push some data over there using T-SQL (i.e. INSERT INTO remoteServer.table SELECT * FROM localServer.table). I've got this working locally between two different databases, but now I need to have it actually talk to the 2nd database server.
I gather I need to use sp_addlinkedserver, but the examples in MSDN are confusing me. How do I just use this to connect to another SQL 2005 server? The docs don't have any mention of how to pass in the username or password.
Thanks.
June 23, 2009 at 6:48 pm
hope this helps; here's a clean, simple example.
on my network, from my local dev machine ,there is a named instance on another server that i can connect to via SSMS, so i'm simply adding the linked server to that:
note that if the sp_tables_ex returns any results, the linked server is set up; if it fails, it some password issue or something....so i would drop it it with the sp_dropserver command, tweak and repeat till successful
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'STORMSQL\SQL2005', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'STORMSQL\SQL2005', @locallogin = NULL , @useself = N'False', @rmtuser = N'sa', @rmtpassword = N'NotARealPassword'
GO
sp_tables_ex [STORMSQL\SQL2005]
--sp_dropserver 'STORMSQL\SQL2005','DropLogins'
Lowell
June 23, 2009 at 7:02 pm
this might help too; sometimes you want an alias for the other server instead of it's real name:
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',@srvproduct = N'', @datasrc = N'STORMSQL\SQL2005', @provider = N'SQLOLEDB';
-- Add Default Login (if applicable)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'sa',
@rmtpassword = 'NotARealPassword';
GO
sp_tables_ex MyLinkedServer
--sp_dropserver 'MyLinkedServer','DropLogins'
Lowell
June 24, 2009 at 9:19 am
Thank you Lowell, that was very helpful.
This SQL is going to be run periodically, I think I will create the linked server, do my stuff, and then disconnect it. But just in case it is still connected for some reason when the script starts, I'd like to disconnect it if it exists. Is there an easy way to do that?
I see sp_linkedservers returns me a list of linked servers, but I am unsure how to hook that up to sp_dropserver so that it only tries to disconnect if its connected.
June 24, 2009 at 9:24 am
that's easy; the views sysservers or sys.servers hold all the linked server information, so something like this would work fine;
if exists(select * from sysservers where name='MyLinkedServer')
BEGIN
exec sp_dropserver 'MyLinkedServer','DropLogins'
END
Lowell
June 24, 2009 at 9:33 am
Thanks.
I had to make a few tweaks to get that to work, but I got it working. Thanks again!
[font="Courier New"]if exists(select * from master.dbo.sysservers where srvname='MyLinkedServer')
BEGIN
exec sp_dropserver 'MyLinkedServer','DropLogins'
END
GO[/font]
June 24, 2009 at 9:39 am
glad i could help out; good luck on your project.
when you have time, you should add how to add other types of linked servers to your snippets of code; it's really handy to be able to add other databases like Oracle, MYSQL,or Access, Excel spreadsheets, even a folder full of raw text files as linked servers.
Lowell
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply