 <p>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.</p> <p>Most common responses:</p> <ul> <li>Why would you want to do that? </li> <li>You <strong>can not</strong> do that, you <strong>must</strong> fully qualify your objects names</li> </ul> <p>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 &amp; connection strings, and there are so many things that could go wrong, and when one does, the error message is often not terribly enlightening.</p> <p>Regardless, here's how you do it:</p> <p>If you are using static SQL:</p> <pre><code>select * from OPENROWSET('SQLNCLI','Server=ServerName[\InstanceName];Database=AdventureWorks2012;Trusted_Connection=yes','select top 10 * from HumanResources.Department') </code></pre> <p>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):</p> <pre><code>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 </code></pre> <p>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. </p> <p>Relevant reading:</p> <p><a href="http://blogs.technet.com/b/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx" rel="nofollow">http://blogs.technet.com/b/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx</a></p> <p><a href="http://blogs.technet.com/b/wardpond/archive/2009/03/20/database-programming-the-openrowset-trick-revisited.aspx" rel="nofollow">http://blogs.technet.com/b/wardpond/archive/2009/03/20/database-programming-the-openrowset-trick-revisited.aspx</a></p> <p><strong>Conclusion:</strong><br> 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.</p> <p>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:</p> <p><a href="https://technet.microsoft.com/en-us/library/ms187873%28v=sql.90%29.aspx?f=255&amp;MSPPError=-2147217396" rel="nofollow">https://technet.microsoft.com/en-us/library/ms187873%28v=sql.90%29.aspx?f=255&amp;MSPPError=-2147217396</a></p> <p><em>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.</em> </p>