OpenRowSet w/Parameters

				One day I was pondering if it would be possible to query a database on another server without having to set up linked servers.The OPENROWSET function seemed like the perfect choice for doing this. It simple takes the parameters of the server name, log in and password with the appropriate select statement. 
Example: 
SELECT a.*
FROM OPENROWSET('SQLOLEDB', 'ServerName'; 'sa'; '',
 SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
This statement returns all the row information from the authors table in the pubs database when it is run. Seems this was the answer I was looking for. Yet I had one slight problem. What if I had more than one server I wanted to query?
My first instinct was to just substitute parameters for the values and pull the data from a custom table. But doing this only produced an error. Strange....parameters usually can replace an actual value, why not now? I began to research the issue and found that it is not possible to use OPENROWSET with parameters. In fact, in the Microsoft documentation it clearly states that this is not possible.
 Not believing everything I read, I began to experiment and discovered a way to use parameters with the OPENROWSET function. To use parameters, use OPENROWSET within an EXEC() statement. 
EXAMPLE:
 
 EXEC('SELECT a.*
FROM OPENROWSET(''SQLOLEDB'', ''' + @ServerName + '''; ''' + @Login + '''; ''' + @Password + ''',
 ''SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname'') AS a')
Be careful of the apostrophes. Miss one and it will not work. If you have a NULL value for your password, you'll have to change it to a blank character to avoid errors as well. I also found that you need to trim excess spaces from your characters, especially if you are querying a SQL 6.5 Server. SQL considers the extra spaces part of the value and searches for an exact match. Trimming the parameters before passing them with OPENROWSET will avoid any problems.
I have created stored procedures which use this technique and if the Server is online and the parameter information is correct, it works just fine. I wrote other stored procedures to verify a server is on line before querying it, as well as including conditional statements for any errors that might occur. I hope this tweeking of the OPENROWSET function proves useful for one of your applications some day.