Suggestion: “USE” keyword with Linked Servers

Using special functions for querying and executing commands at linked servers is not very convenient, and sometimes even very problematic. For example, I need to execute some code on several linked servers but the linked server names are dynamic (queried from a table for example). On each such linked server I need to execute a dynamic query. However because I must use the EXECUTE() AT […] command I’m exposing a big hole here for SQL Injection. I found myself wishing I could use sp_executesql with linked servers but there’s no such way as far as I know.

For example, this is what I’m forced to do:

Transact-SQL

CREATE PROCEDURE BackupOnRemoteServer @Path NVARCHAR(4000) AS DECLARE @Statement NVARCHAR(MAX) SET @Statement = N' BACKUP DATABASE [MyDB] TO DISK = ''' + @Path + '''' EXECUTE (@Statement) AT [SQLINS1]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

CREATE PROCEDURE BackupOnRemoteServer

@Path NVARCHAR(4000)

AS

DECLARE

@Statement NVARCHAR(MAX)

SET @Statement = N'

BACKUP DATABASE [MyDB]

TO DISK = ''' + @Path + ''''

EXECUTE (@Statement) AT [SQLINS1]

I’ve posted a suggestion on Microsoft Connect for the following feature:

It would be very helpful if we were able to connect to databases on linked servers with the “USE” keyword.

Technically, it would be like “opening a new session” from one SQL Server to another.

The sample I’ve posted above could be executed like this:

Transact-SQL

CREATE PROCEDURE BackupOnRemoteServer @Path NVARCHAR(4000) AS DECLARE @ServerName VARCHAR(200), @Statement NVARCHAR(MAX), @Parameters NVARCHAR(MAX) SET @ServerName = 'SQLINS1'; SET @Parameters = N'@Path NVARCHAR(4000)'; SET @Statement = N' USE [' + @ServerName + N'].[MyDB] BACKUP DATABASE [MyDB] TO DISK = @Path' EXEC sp_executesql @Statement, @Parameters, @Path

1

2