Post reply

Using OLE Automation SPs

  • February 22, 2005 at 3:48 am

    #66571

    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

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • February 22, 2005 at 6:30 am

    #543440

    I am curious as to further detail. The way it looks I would say why not just make a direct call to the table. This looks lik an over complication of what should be a simple select?

  • February 22, 2005 at 6:37 am

    #543441

    Ah, I had a feeling someone was going to ask that.

    Thanks for the reply Antares but unfortunately my scenario isn't that simple.

     

    Under normal circumstances you're right, a direct SELECT * FROM MyTable would be better. However this is ultimately called from an ASP.Net app. The user used by the app does not have direct access to MyTable therefore I have to do it this way.

    And before it is suggested...no, the security model can't be changed.

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • February 22, 2005 at 9:11 pm

    #543601

    Ok so does MyTable exist in the database the user is connected to or is it another? If the same and is owned by DBO then all the SP of course has to do is do a select. I don't think you can do what you are wanting this way. You could try putting the output into a temp table but I have never tried, than that is what you ould call. But it really does seem like making a mountain out of a mole hill. Could you be more detailed and maybe we can see some other way. I was thinking might try XML output or something similar but right off I don't see a solution to your question.

  • February 23, 2005 at 2:31 am

    #543638

    Antares,

    Thanks again for the reply.

    You are correct, the table is in another DB.

    Putting the output into a temp table is exactly what I had in mind but I don't know how to do this. That is basically what I need help on.

    I see what you mean about overcomplicating things but believe me this is valid. We already use these techniques extensively to execute INSERT/UPDATE/DELETE statements and they work absolutely fine. SELECT statements of course are different because they return data and I need to access that returned data.

    Any help/advice much appreciated. My idea at the moment is to cursor over the QueryResults object and put the results into a temp table. I don't actually know how to do this though.

    Hope this makes sense!

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • February 23, 2005 at 7:25 am

    #543687

    >>> The user used by the app does not have direct access to MyTable therefore I have to do it this way. <<<

    I have to differ on that one!

    The main way to do that is through the use of openrowset/opendatasource/openquery and I am pretty sure you can find a workaround with those  to read on the other DB. At least it sounds a lot simpler than what the sp_OA* give you

    HTH

     


    * Noel

  • February 23, 2005 at 7:30 am

    #543688

    I would look at one of these options. It is basically the same thing but more native to SQL itself and means you might can even avoid the temp table.

  • February 23, 2005 at 7:44 am

    #543696

    And Just for the fun of it have you tried:

    INSERT INTO tablename EXEC @hr=master..sp_OAMethod @vObject, 'ExecuteWithResults', @vResult , @vsql

    I haven't

    HTH


    * Noel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply