> OK, here's a trick question I would love an answer to.
> I want to use the result table generated by a stored procedure
> in the stored procedure which called it.
> It is possible to retrieve the result table created by a stored procedure,
> if you use some external library (DB-Lib, ODBC, etc.); is it
> possible to do from with in a stored procedure?
> The specific example I am trying is:
> - Have multiple transaction log dumps to a single dump file
> - The transaction logs in the dump file are serialized (1,2,3...)
> - I can use LOAD HEADERONLY FROM <dumpfile> to retrieve a table
> listing the details (include the index numbers) in the output
> screen (or from an external client), but I can't reference the output
> from sql code.
> There are other ways around this problem (which I have taken, which
> are real *hacks*), but this is a common problem I run into: the info
> I need I can get from a stored procedure, but I can't *use* the info
> once I have it.
> Any suggestions will be appricated.
> >> LKK <<
The only way I am aware of achieving this is by using the mail system you can then play around with the
results as strings or whatever you want. You have to put in some extra code if the query results exeed 255
bytes.
eg:
If you want to retain the results
create table (#)dumpheader
(Dumptype char(8),
DatabaseName char(30),
Striped char(8),
Compressed char(8),
Sequence char(8),
Volume char(8),
Devicetype char(8),
TableName char(30),
Dumpsize char(8),
Stripeset char(8),
StripesetName char(30),
Curseq char(16),
Newseq char(8),
CreationDate char(20),
CreationTime char(16),
ExpireDate char(16))
check status
begin
xp_findnextmsg (parameters) 'get id of next message
xp_readmail (parameters) 'set peek to true which leaves it as unread
(any other parameters) ' with peek set to false
' some code to prevent infinite loop if message never arrives
end
You could do this sort of thing with any results and handle a whole range of different mail subjects which
can be sent from stored procs or any mail user on the network.
The 'reciever' could run continually searching for the mail messages and the sending proc could wait for
the appropriate table entries to appear.
With some good defensive coding it could work quite well.
Greg
by