Using OLE Automation SPs

Question

Using OLE Automation SPs

Jamie Thomson

SSChampion

Points: 11805
More actions
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

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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?

Jamie Thomson SSChampion Points: 11805 More actions · Answer 2

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

Antares686 SSC Guru Points: 125444 More actions · Answer 3

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.

Jamie Thomson SSChampion Points: 11805 More actions · Answer 4

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

noeld SSC Guru Points: 96590 More actions · Answer 5

>>> 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

Antares686 SSC Guru Points: 125444 More actions · Answer 6

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.

noeld SSC Guru Points: 96590 More actions · Answer 7

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