|
|
| |
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. |
First, set the data access option for the server:
exec sp_serveroption '', 'data access', true
Then, let's create a table called "tblNames" in the "Pubs" datbase:
/*
TickerSymbol CompanyName
------------- --------------
AMGN Amgen Inc
KOCoca-Cola Company
MSFT Microsoft Corporation
ORCL Oracle Corporation/DE
PEPPepsiCo Inc
*/
--//////////////////////////////////////
--
Use Pubs
--create a simple stored procedure
CREATE procedure sp_myproc
as
SELECT 'KO' AS Tickersymbol
go
--now join the results of SP with query
--
select A.CompanyName,A.TickerSymbol from tblNames A
join openquery([server_name], '{call pubs..sp_myproc}') B
on A.TickerSymbol = B.TickerSymbol
go
--//////////////////////////////////////
--
| |
Other 3 submission(s) by this author
|
|
| |
| Report Bad Submission |
|
| |
| Your Vote! |
|
See Voting Log |
| |
| Other User Comments |
2/19/2002 6:59:05 AM:James Travis
Awesome, I will need to play with and
see what the overhead is.
|
2/19/2002 7:07:27 AM:James Travis
Oops found a downside. This will not
allow you to pass any parameters, you
get "[OLE/DB provider returned message:
Syntax error or access violation]" as
is. Instead of "{call pubs..sp_myproc}"
use "exec pubs..sp_myproc" this I have
tested will allow you to have a
parameter passed.
|
2/20/2002 6:10:27 AM:Sreekanth
A Very Nice Explanation Regarding
SP
Thanks for the Code too
|
2/20/2002 6:15:20 AM:James Travis
Ok I finally have the results of the
overhead. You will for every individual
connection that runs this end up with a
secound connection of SQL back to
itself with that user which remain open
for about 20 minutes or so. However if
that user runs an other query doing the
same thing the connection will not
increase and is fine. After carefull
study I cannot find a usefull reason
for this as you have to call remotely
and unless you build dynamically all
your variables are static and in each
instance they remain static when built
dynamically. For this I would say neat
but not practical as there is far less
overhead and need to worry about
connections with joins. For your
example:
select
A.CompanyName,A.TickerSymbol from
tblNames A
join (SELECT 'KO' AS
Tickersymbol) B
on A.TickerSymbol =
B.TickerSymbol
Is far better on
server resources.
|
|
| 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.
|
|
