Creating a linked server dynamically and executing a query against it.

Sometimes from a SQL script running on server A we need to execute a query against a database on a different SQL server B or against any other kind of database. If this other database can normally be linked to your main SQL server A than you can definitelly do that at design time and solve your problem. The question is can you do it at run time?

I mean what if you do something in a SQL script on your main server A and you have to reach out and fetch some information from another database on a server whose names are passed as parameters or read from a config file.

The script below shows how to link a Sql database on another server dynamically at runtime and then issue a query against a table from that database.

DECLARE @v_ServerName varchar(100)
DECLARE @qry varchar(500), @v_DataBaseName varchar(50)

IF NOT EXISTS (SELECT 1 Where Exists (Select [SERVER_ID] From sys.servers WHERE [Name]=@v_ServerName))
EXEC sp_addlinkedserver @v_ServerName, N'Any', N'SQLNCLI', @v_ServerName;
SET @qry = 'SELECT * FROM [' + @v_ServerName + '].[' + @v_DataBaseName + '].[dbo].[TableName] WHERE

EXECUTE (@qry);

Try it and let me know it worked for you.