CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ItsZeus
ItsZeus

Reputation: 142

SQL Server Linked Server Join

I have added a linked server in my sql server 2008. I want to fetch data from a table and a table valued function residing on my linked server and join this data on a local table by following below given naming convention.

<server>.<database>.<schema>.<table>

However my first problem is I need to fetch <server> part from a table. So when I try to do something like following it fails

Select * FROM @ServerNameVariable.database.dbo.myTable

Any idea how can I form fully qualified linked server table name with a user defined variable ?

My SP is as follows

CREATE PROCEDURE TEST_SP    
AS
BEGIN
    DECLARE @NetworkDBName VARCHAR(255) 
    SET @NetworkDBName = '[MyLinkedServerName]'
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here 
    select * from @NetworkDBName + '.' + testDatabase.dbo.Invoice_tb
END
GO

Upvotes: 3

Views: 1863

Answers (2)

David Rushton
David Rushton

Reputation: 5030

You cannot use variables in place of database, schema or table names.

Instead you can build and execute dynamic SQL statements, using sp_ExecuteSQL.

This example won't work, as the server name is seen as a string and not a server object.

Failed Example

/* Anti-pattern.
 * Does not work.
 */
DECLARE @Server    SYSNAME = 'Server001';

SELECT
    *
FROM
    @Server.Database1.dbo.Table1
;

This example shows a method that does work. Here the SQL statement is built as a string, which is then executed.

/* Dynamic SQL statement.
 * Will work.
 */
DECLARE @Server    SYSNAME = 'Server001';
DECLARE @Statement    NVARCHAR(255);

SET @Statement = 'SELECT * FROM ' + QUOTENAME(@Server) + '.Database1.dbo.Table1;';

EXECUTE sp_ExecuteSQL @Statement;

As ever; please be careful when generating and executing dynamic SQL statements. You do not want to open yourself up to SQL injection attacks. Look into OPENROWSET or check the passed server name against the code kindly supplied by @Devart above (SELECT name FROM sys.servers WHERE server_id > 0) before executing.

EDIT 1: Added more detail to the paragraph on SQL injection.

EDIT 2: Removed square brackets from 2nd example query, replaced with QUOTENAME, as per @TTs comment.

Upvotes: 6

Abdul Hannan Ijaz
Abdul Hannan Ijaz

Reputation: 844

Use the name of link server it will a 4 part qualifier e.g 

Select * From [Link Server Name ].[Database].[Schema].[Table]

Upvotes: 2

Related Questions