Help required: Using OLE Automation SPs

Hi,
I am using the sp_OACreate, sp_OAMethod, sp_OASetProperty etc... SPs to
execute a piece of dynamic SQL.
My dynamic SQL needs to return a resultset so I basically did the following:
SET @.vSQL = 'SELECT * FROM MyTable'
EXEC sp_OAMethod @.vObject, 'ExecuteWithResults', @.vResult OUTPUT, @.vSQL
I now have the contents of MyTable in @.vResult (which is a handle). I used
the following to return the results as a string into @.pResults (which is a
varchar):
EXEC @.vHR = sp_OAMethod @.vResult, 'GetRangeString', @.pResults OUTPUT
This is all well and good but I don't want the results returned as a string,
I want it returned as a recordset which will ultimately be the output from
the SP that wraps all of this up. Furthermore the max string that I can
return is 8000 chars (i.e. max length of a varchar) so with any decent sized
dataset this doesn't return everything anyway.
So my question is: Once I have the results as handled by @.vResult...how do I
navigate over it and return the results as a normal rowset? The
GetRangeString function is obviously not the answer.
Thanks in advance
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspxWhat is @.vObject a handle to? Normally you don't need to use OLE Automation
SPs to execute dynamic SQL. I think you will have to explain a little bit
better why you want to shove a table into an output variable and then
process it back into a resultset again. Why not just EXEC('SELECT * FROM
MyTable') ... (and I assume that dynamic SQL is actually required here for
some reason, and that your query is a little more complicated than this...
otherwise please read http://www.sommarskog.se/dynamic_sql.html)...
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamie Thomson" <jamiekthomson@.removethisbit.blueyonder.co.uk> wrote in
message news:xKNSd.196724$K7.125153@.fe2.news.blueyonder.co.uk...
> Hi,
> I am using the sp_OACreate, sp_OAMethod, sp_OASetProperty etc... SPs to
> execute a piece of dynamic SQL.
> My dynamic SQL needs to return a resultset so I basically did the
following:
> SET @.vSQL = 'SELECT * FROM MyTable'
> EXEC sp_OAMethod @.vObject, 'ExecuteWithResults', @.vResult OUTPUT, @.vSQL
> I now have the contents of MyTable in @.vResult (which is a handle). I used
> the following to return the results as a string into @.pResults (which is a
> varchar):
> EXEC @.vHR = sp_OAMethod @.vResult, 'GetRangeString', @.pResults OUTPUT
> This is all well and good but I don't want the results returned as a
string,
> I want it returned as a recordset which will ultimately be the output from
> the SP that wraps all of this up. Furthermore the max string that I can
> return is 8000 chars (i.e. max length of a varchar) so with any decent
sized
> dataset this doesn't return everything anyway.
>
> So my question is: Once I have the results as handled by @.vResult...how do
I
> navigate over it and return the results as a normal rowset? The
> GetRangeString function is obviously not the answer.
> Thanks in advance
>
> Regards
> Jamie Thomson
> An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspx
>
>|||Hi Aaron,
Yes, you're absolutely right. My real query is alot more complicated than
this. :)
The reason I am doing it is a permissions issue. Under our current
application security model the calling app (an ASP.Net app) doesn't have
access to MyTable therefore EXEC(...) doesn't work. I am not allowed to
change the security model either (its not under my control) :o( but I AM
allowed to access it using this method.
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspx
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:udXW3iSGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> What is @.vObject a handle to? Normally you don't need to use OLE
> Automation
> SPs to execute dynamic SQL. I think you will have to explain a little bit
> better why you want to shove a table into an output variable and then
> process it back into a resultset again. Why not just EXEC('SELECT * FROM
> MyTable') ... (and I assume that dynamic SQL is actually required here for
> some reason, and that your query is a little more complicated than this...
> otherwise please read http://www.sommarskog.se/dynamic_sql.html)...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jamie Thomson" <jamiekthomson@.removethisbit.blueyonder.co.uk> wrote in
> message news:xKNSd.196724$K7.125153@.fe2.news.blueyonder.co.uk...
> following:
> string,
> sized
> I
>