CODEWITHSUNDEEP
sql-servert-sql
tbone
tbone

Reputation: 5865

Dynamic SQL without having to use fully qualified table names in SQL (Openrowset?)

I have a large set of pre-existing sql select statements.

From a stored procedure on [Server_A], I would like to execute each of these statements on multiple different SQL Servers & Databases (the list is stored in a local table on [Server_A] , and return the results into a table on [Server_A].

However, I do not want to have to use fully qualified table names in my sql statements. I want to execute "select * from users", not "select * from ServerName.DatabaseName.SchemaName.Users"

I've investigated using Openrowset, but I am unable to find any examples where both the Server name and DatabaseName can be specified as an attribute of the connection, rather than physically embedded within the actual SQL statement.

Is Openrowset capable of this? Is there an alternate way of doing this (from within a stored procedure, as opposed to resorting to Powershell or some other very different approach?)

The inevitable "Why do I want to do this?"

Upvotes: 0

Views: 1256

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48806

This can be done quite easily via SQLCLR. If the result set is to be dynamic then it needs to be a Stored Procedure instead of a TVF.

Assuming you are doing a Stored Procedure, you would just:

  • Pass in @ServerName, @DatabaseName, @SQL
  • Create a SqlConnection with a Connection String of: String.Concat("Server=", ServerName.Value, "; Database=", DatabaseName.Value, "; Trusted_Connection=yes; Enlist=false;") or use ConnectionStringBuilder
  • Create a SqlCommand for that SqlConnection and using SQL.Value.
  • Enable Impersonation via SqlContext.WindowsIdentity.Impersonate();
  • _Connection.Open();
  • undo Impersonation -- was only needed to establish the connection
  • _Reader = Command.ExecuteReader();
  • SqlContext.Pipe.Send(_Reader);
  • Dispose of Reader, Command, Connection, and ImpersonationContext in finally clause

This approach is less of a security issue than enabling Ad Hoc Distributed Query access as it is more insulated and controllable. It also does not allow for a SQL Server login to get elevated permissions since a SQL Server login will get an error when the code executes the Impersonate() method.

Also, this approach allows for multiple result sets to be returned, something that OPENROWSET doesn't allow for:

Although the query might return multiple result sets, OPENROWSET returns only the first one.

UPDATE

Modified pseudo-code based on comments on this answer:

  • Pass in @QueryID
  • Create a SqlConnection (_MetaDataConnection) with a Connection String of: Context Connection = true;
  • Query _MetaDataConnection to get ServerName, DatabaseName, and Query based on QueryID.Value via SqlDataReader
  • Create another SqlConnection (_QueryConnection) with a Connection String of: String.Concat("Server=", _Reader["ServerName"].Value, "; Database=", _Reader["DatabaseName"].Value, "; Trusted_Connection=yes; Enlist=false;") or use ConnectionStringBuilder
  • Create a SqlCommand (_QueryCommand) for _QueryConnection using _Reader["SQL"].Value.
  • Using _MetaDataConnection, query to get parameter names and values based on QueryID.Value
  • Cycle through SqlDataReader to create SqlParameters and add to _QueryCommand
  • _MetaDataConnection.Close();
  • Enable Impersonation via SqlContext.WindowsIdentity.Impersonate();
  • _QueryConnection.Open();
  • undo Impersonation -- was only needed to establish the connection
  • _Reader = _QueryCommand.ExecuteReader();
  • SqlContext.Pipe.Send(_Reader);
  • Dispose of Readers, Commands, Connections, and ImpersonationContext in finally clause

Upvotes: 1

tbone
tbone

Reputation: 5865

This is an interesting problem because I googled for many, many hours, and found several people trying to do exactly the same thing as asked in the question.

Most common responses:

  • Why would you want to do that?
  • You can not do that, you must fully qualify your objects names

Luckily, I stumbled upon the answer, and it is brutally simple. I think part of the problem is, there are so many variations of it with different providers & connection strings, and there are so many things that could go wrong, and when one does, the error message is often not terribly enlightening.

Regardless, here's how you do it:

If you are using static SQL:

select * from OPENROWSET('SQLNCLI','Server=ServerName[\InstanceName];Database=AdventureWorks2012;Trusted_Connection=yes','select top 10 * from HumanResources.Department')

If you are using Dynamic SQL - since OPENROWSET does not accept variables as arguments, you can use an approach like this (just as a contrived example):

declare @sql nvarchar(4000) = N'select * from OPENROWSET(''SQLNCLI'',''Server=Server=ServerName[\InstanceName];Database=AdventureWorks2012;Trusted_Connection=yes'',''@zzz'')'
set @sql = replace(@sql,'@zzz','select top 10 * from HumanResources.Department')
EXEC sp_executesql @sql

Noteworthy: In case you think it would be nice to wrap this syntax up in a nice Table Valued function that accepts @ServerName, @DatabaseName, @SQL - you cannot, as TVF's resultset columns must be determinate at compile time.

Relevant reading:

http://blogs.technet.com/b/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx

http://blogs.technet.com/b/wardpond/archive/2009/03/20/database-programming-the-openrowset-trick-revisited.aspx

Conclusion:
OPENROWSET is the only way that you can 100% avoid at least some full-qualification of object names; even with EXEC AT you still have to prefix objects with the database name.

Extra tip: The prevalent opinion seems to be that OPENROWSET shouldn't be used "because it is a security risk" (without any details on the risk). My understanding is that the risk is only if you are using SQL Server Authentication, further details here:

https://technet.microsoft.com/en-us/library/ms187873%28v=sql.90%29.aspx?f=255&MSPPError=-2147217396

When connecting to another data source, SQL Server impersonates the login appropriately for Windows authenticated logins; however, SQL Server cannot impersonate SQL Server authenticated logins. Therefore, for SQL Server authenticated logins, SQL Server can access another data source, such as files, nonrelational data sources like Active Directory, by using the security context of the Windows account under which the SQL Server service is running. Doing this can potentially give such logins access to another data source for which they do not have permissions, but the account under which the SQL Server service is running does have permissions. This possibility should be considered when you are using SQL Server authenticated logins.

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

If you want to execute a sql statement on every database in a instance you can use (the unsupported, unofficial, but widely used) exec sp_MSforeachdb like this: 

EXEC sp_Msforeachdb 'use [?];  select * from users'

This will be the equivalent of going through every database through a 

use db...
go
select * from users

Upvotes: 0

Related Questions