result set from extended stored procedure

result set from extended stored procedure

Post by R.A.Veldhuijse » Fri, 28 May 1999 04:00:00



Hi,

I have made an SQL server 6.5 extended stored procedure that returns a
result set (similar to Microsoft's xp_odbc example). The extended stored
procedure is like this:

exec xp_odbcquery <DSN> <Query>

Is it possible to store data from the result set into variables ?
or should I create a cursor from which the data can be fetched ?

Any help appreciated.

Robbert Veldhuijsen

 
 
 

result set from extended stored procedure

Post by Gert Draper » Fri, 28 May 1999 04:00:00


You can only use the result set in a  insert into table exec xp_odbcquery or
you need to use output parameters but then your result set in limited to 1
row only.
Insert into a temp table and cursor over it is the only way to go.

-GertD

No live without an edge.

 
 
 

result set from extended stored procedure

Post by Gert Draper » Sat, 29 May 1999 04:00:00


Or you can maintain state in the XP, have it pass back an id which you use
in consequent fetches to retrieve it in variables. That way you do have to
make sure you close it otherwise you will create a sort of resource leak,
since they are never destroyed. If you do that you need to use Thread Local
Storage (TLS) if you want multiple clients to use this at the same time.

-GertD

No live without an edge.

 
 
 

result set from extended stored procedure

Post by R.A.Veldhuijse » Tue, 01 Jun 1999 04:00:00


Or.. I could create an temporary table
and do an:

INSERT INTO #table_name
EXEC xp_odbcquery 'DataSourceName', 'SELECT * FROM <REMOTE_TABLE>'

After that I can SELECT the required fields and store them into variables

that's much easier for me...
Thanks anyway ;-)


> Or you can maintain state in the XP, have it pass back an id which you use
> in consequent fetches to retrieve it in variables. That way you do have to
> make sure you close it otherwise you will create a sort of resource leak,
> since they are never destroyed. If you do that you need to use Thread
Local
> Storage (TLS) if you want multiple clients to use this at the same time.

> -GertD

> No live without an edge.

 
 
 

result set from extended stored procedure

Post by Gert Draper » Tue, 01 Jun 1999 04:00:00


Yeah that was my first suggestion :)

-GertD

No live without an edge.

 
 
 

result set from extended stored procedure

Post by Gert Draper » Tue, 01 Jun 1999 04:00:00


That was my first suggestion :)

-GertD

No live without an edge.

 
 
 

result set from extended stored procedure

Post by R.A.Veldhuijse » Sat, 05 Jun 1999 04:00:00


thought you meant to create a temp table from within the ESP and return this
table
probably misunderstood

well...  it works!
thanks!

> That was my first suggestion :)

> -GertD

> No live without an edge.

 
 
 

result set from extended stored procedure

Post by Erland Sommarsk » Sun, 06 Jun 1999 04:00:00



>Or.. I could create an temporary table
>and do an:

>INSERT INTO #table_name
>EXEC xp_odbcquery 'DataSourceName', 'SELECT * FROM <REMOTE_TABLE>'

>After that I can SELECT the required fields and store them into variables

Beware though that the procedure that you write then cannot be called
in the same way. There is a restriction in 6.5 (don't know about 7.0)
so you can only have one INSERT EXEC active at a time.

--

This could have been my two cents worth, but alas the Swedish
government has decided that I am not to have any cents.

 
 
 

result set from extended stored procedure

Post by Gert Draper » Sun, 06 Jun 1999 04:00:00


Why can you only have 1 insert exec active at the time ? That seems bogus to
me and does not match with my experience in both 6.5 and 7.0, but please
educate I might be wrong.

-GertD

No live without an edge.