|
|
| Submitted on: 12/13/2000 9:10:33 PM
By: Louis Davis
Level: Advanced
User Rating:
By 7 Users
Compatibility:SQL Server 7.0, SQL Server 6.5 and earlier, Other
Users have accessed this article 5556 times.
| (About the author) |
| | | Steps to use parameters with the OPENROWSET function.
| |
| |
Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for langauges that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description. | | 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.
| |
| |
| Report Bad Submission |
|
| |
| Your Vote! |
|
See Voting Log |
| |
| Other User Comments |
12/13/2000 9:16:49 PM:Devo
Unbelieveable!!! I guess I'll have to
start experimenting myself.
|
12/21/2000 2:09:19 AM:qqq
its really good work. thanks
|
1/8/2001 11:01:03 AM:DECIS
Very useful :-)
|
1/9/2001 4:13:18 AM:mat_in_the_hat
Its a good point for executing *any*
function that does no take parameters
(using full-text indexed searches
springs to mind as something i've used
it for before)...
One quick comment is
to use the system stored procedure
sp_executesql rather than EXEC or
EXECUTE where possible as it can speed
stuff up a bit and offers a little more
functionality...
Sorry if that sounded
like critisism cus the article is
exellent!
|
7/3/2001 12:11:04 PM:Charles Kincaid
This must be due to the way that
statements are parsed. String
constants are passed in as a distinct
data type that no variable can
immitate.
To parameterize this type
of command, you have to prepare a
single string containing the command
and pass it through EXEC so that it
re-enters the parser as a constant
string.
Hey, anybody want to write a
Quick Basic Interpreter that runs as a
stored procedure?
|
5/6/2002 4:55:21 AM:Ravi
Excellent Article. Must have born out
of need.Experimenting strengthen
skills, proved agian.
|
5/22/2002 4:19:53 AM:import from dbf files
Have you tried to open also the dbf
files using Openrowset function ?
Because i intend to import data from
dbf in SQL and I cannot manage
this
Thank you,Mirela
|
|
| Add Your Feedback! |
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular article, please click here.
|
|
