Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Eitan Blumin

Eitan Blumin is a SQL Server database expert and a senior consultant at Madeira SQL Server Services. He has more than 10 years of experience in all fields of the SQL Server DBA role, including but not limited to: Database design, management, development, tuning, replication, backup management, security management, SSIS, SSRS, encryption and more. Eitan also has 10 years of experience in ASP web development, and some experience in a wide variety of development environments such as PHP, C, C++, C#, VB, Java, Perl, Assembler and more.

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:

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:

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

Once the USE command was executed, theoretically it’s like I’m connected directly to the linked server using a query window.

Security could be improved versus SQL Injection (use sp_executesql with parameters instead of EXECUTE() AT) like the example above.

Usability could be improved due to cleaner and more readable code.

If you like this idea, please visit this page and vote for it so it’ll get higher priority on Microsoft’s to-do list:

https://connect.microsoft.com/SQLServer/feedback/details/742327/use-keyword-with-linked-servers#details

The post Suggestion: “USE” keyword with Linked Servers appeared first on .

Comments

Leave a comment on the original post [www.madeirasql.com, opens in a new window]

Loading comments...