> 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/jamiethomson/category/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...
>> 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/jamiethomson/category/71.aspx
>>
>>
>>
>
>