I have yet to execute it because I have a couple of other questions that I was hoping you people could help me with first...
Is this process reversible?
I've not found a sp_removelinkedserver or similar yet (it's probably staring me right in the face!)
Does the link affect all databases on a single server, or just the one I'm playing with?
How do I connect to a specific database on a specific database?
Is it a simple case of linking the two servers and then using 4 part naming to do so?
Hmm, I think that pretty much covers it!
1) Yes - check out sp_dropserver
2) It doesn't affect databases at all. What do you mean? The link provides permissions to the remote server as per the security context you specifiy when you create a linked server login.
3) Your question makes no sense . Do you mean a specific database on a specific instance? If so then yes - it is that simple.
Actually - the @server parameter is the logical name and @datasrc is the physical name. So yes - you can alias. I've never bothered - I find it hard enough remembering servernames let alone what the servers are aliased as.
SET NOCOUNT ON
@server = [XXX-XXXX-XXXXX]
, @srvproduct= 'SQL Server'
PRINT 'Linked server created'
EXEC sp_addlinkedsrvlogin [XXX-XXXX-XXXXX
PRINT 'logins ....'
SELECT * FROM [XXX-XXXX-XXXXX].[xxxxx-Test].dbo.Test WHERE firstname = 'george'
PRINT 'SQL Statement returned ' + Convert(varchar,@@RowCount) + ' rows'
EXEC sp_dropserver [XXX-XXXX-XXXXX], 'droplogins'
PRINT 'Server and logins dropped'
--SELECT * FROM sysservers
SET NOCOUNT OFF
Linked server created
Server: Msg 17, Level 16, State 1, Line 2
SQL Server does not exist or access denied.
Server and logins dropped
So I have access via windows authentication on both servers... This is trying to connect from the 2000 box to the 05 one.
Any suggestions as to what I'm missing?