Ever been writing some code and found that you need some data from another SQL Server instance? You have security access to the other instance so what do you do?
Well you could easily use a linked server to obtain it, but what if there is no linked server set up and you lack the rights to (or maybe there’s a company rule forbidding) setting one up?
Or maybe you just don’t want to use a linked server as these can have pitfalls (I’ve encountered a few).
This is where OPENROWSET comes to the fore. It provides a tidy, simple, and very effective way to obtain data across
instances without requiring a linked server.
There are a couple of variations to the most commonly used versions of this command which you will need to know about
but they will all be covered as we go.
Windows Authentication
This is the most common and easiest usage of OPENROWSET.
There are two methods we can use to connect to another instance, a SQL Native Client driver (SQLNCLI), or OLE
DB Provider for ODBC and the SQL Server ODBC driver (MSDASQL), and as we are using our Windows Login to connect
to both servers then this means we can use a “trusted connection”:
select a.* from openrowset('SQLNCLI', 'Server=MYINSTANCE;Trusted_Connection=yes;', 'select * from sys.databases') as a
select a.* from openrowset('MSDASQL', 'Driver={SQL SERVER}; Server=MYINSTANCE;Trusted_Connection=yes;', 'select * from sys.databases') as a
SQL Server Authentication
Not everyone uses Windows Authentication for their databases or server access or maybe the target server you
want to access only allows SQL Server Authentication… in this case we simply need to remove the trusted
connection part of the string and replace it with a userID and password:
select a.* from openrowset('SQLNCLI', 'Server=MYINSTANCE;UID=mySQLUser; PWD=*******;', 'select * from sys.databases') as a
select a.* from openrowset('MSDASQL', 'Driver={SQL SERVER}; Server=MYINSTANCE;UID=mySQLUser; PWD=*******;', 'select * from sys.databases') as a
Additional Notes around usage
Note that if a linked server is not present on your instance to the target instance then SQLNCLI will fail when
using SQL Server Authentication, however it will work with Windows Authentication. Therefore if using SQL Server
Authentication I would advise using the MSDASQL approach.
Secondly, be aware that you can pass multiple statements in an OPENROWSET command, but only the first set of
results will be returned by the command.
Lastly, a good deal of people like to write dynamic SQL and
there’s nothing wrong with this as it’s incredibly powerful and can make life much easier, however, if you wish
to use this with OPENROWSET then there is a slight catch. OPENROWSET will not allow you to pass a variable to it.
Therefore if you wish to use dynamic SQL with OPENROWSET you will need to include it as part of the dynamic code itself.
For example:
declare @top int = 10, @sql nvarchar(max)
select @sql = 'select a.* from openrowset(''SQLNCLI'', ''Server=MYINSTANCE;Trusted_Connection=yes;'', ''select top ' + convert(varchar, @top) + ' * from sys.databases'') as a'
exec sp_executeSQL @sql
select @sql = 'select a.* from openrowset(''MSDASQL'', ''Driver={SQL SERVER}; Server=MYINSTANCE;UID=mySQLUser; PWD=*******;'', ''select * from sys.databases'') as a'
exec sp_executeSQL @sql
Hopefully that will be enough to get you comfortably using this where needed.