Tuesday, August 17, 2010

[QA] Linked server for QA - faking a prod server

In case you use linked server a lot. This creates a linked server named ServerB that actually connects to the local server. I needed it for testing, as a lot of code references linked servers (no comments on _that_, please)

EXEC sp_addlinkedserver
@server = 'ServerB',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=True;'

(allows you to query [prodbox01].yourbigdb.dbo.prodtable, but actually get data from [qabox01].yourbigdb.dbo.prodtable)

EXEC master.dbo.sp_addlinkedserver @server = N'PRODBOX01', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'QAbox01'