accessing results set from sp_oaGetProperty
-
I'm trying to access the results set from the below - the getproperty one - so as to work with the data (obviously). How to do it?
Certainly its generating the results - it reports
"(3278 row(s) affected)
(1 row(s) affected)"
from the getproperty sp, but I don't see how to grab this as the array that BOL2000 talks about.
here's my code:
/************************/
declare @oUsers int, @hr int, @src varchar(255), @desc varchar(255), @help varchar(255)
declare @oUserList int
/* create oUsers as aspuser object and set
domain name*/
exec @hr = sp_OACreate 'Persits.AspUser', @oUsers OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ousers, @src OUT, @desc OUT
SELECT 'create object'=convert(varbinary(4),@hr) , Source=@src, escription=@desc">Description=@desc
RETURN
END
exec @hr = sp_OASetProperty @oUsers,
'Domain','CYC'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ousers, @src OUT, @desc OUT
SELECT 'set domain'=convert(varbinary(4),@hr) , Source=@src, escription=@desc">Description=@desc
RETURN
END
/* reset database */
update cn_users set [checked] = 'no'
/* get users list */
exec @hr = sp_OAGetProperty @oUsers ,'Users'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ousers, @src OUT, @desc OUT
SELECT 'get user list'=convert(varbinary(4),@hr) , Source=@src, [Description]=@desc,[help]=@help
RETURN
END
/*********************************/
-
maybe this would work (never done this) :
Create a temp table (cannot be a table variable)
Then do this :
Insert into #temp (col1, col2) exec @hr = sp_OAGetProperty @oUsers ,'Users'
-
Depending on what your property Users actually returns, you can do one of two things:
1) If it returns an array, as your code here seems to indicate, it will return a resultset to the client. I do not think you can use this resultset in the procedure.
2) If it is actually a Resultset object that is returned you need to specify an int output variable to store the object in, and then continue using sp_OA* methods to manipulate it.
-
Outch, would be nice if we could catch the recordset into a table...
-
I had not seen your post when I posted mine, opened the window and then got busy. I have never tried that, so maybe it is actually possible. Or maybe not with a recordset since it is an object, but if the property returns the recordset as an array. Not sure if it works, but it might.
-
He'll have to try... don't have time for this here.
-
Thanks for the various bits of advice and thoughts!
As far as I can figure from a further delve in BOL, the OLE stuff uses VB data types, which is where the array comes from. Certainly the temp table doesn't work, as it complains that you can't use an object in GerProperty
As I can see no point at all in returning something you cannot then use in the proc, there must be a way to grab the data from OA and play with it. I'll see if there is a way round via the native methods/propertiues of the aspuser component.
-
Can't you simply the query from the vb componant?
-
Sorry - not quite sure what you mean. This proc is based on an asp page I've already written and used to query the domain list and make some corections to the database from it. Problem is, I can't schedule this anything like as easily as an sql proc, and it runs a lot slower.
-
I meant : Can't you simply copy the query from the vb package and use it on the server?
-
by vb package you mean 'aspuser'? My knowledge only extends to asp/vbscript, and t-sql, so you're working a bit beyond me there!
-
What are you doing in that package that returns a recordset?
-
I am not sure you should be using sp_OA* at all, they can easily cause a lot of harm to your system. But I do not really understand what you are trying to do here, so maybe you need them.
Anyway:
>As I can see no point at all in returning something you cannot then use in the proc, there must be a way to grab the data from OA and play with it.
As I said, you can grab a handle to the Recordset and then manipulate that object to work with the data.
-
What I'm attemptying to do here is to use tsql to access the domain user list (via sp_oa and aspuser) on our domain, and then to check the list for new entries, old entries and changes, which are then updated on our intranet, so that new users can be emailed, old users can be archived and changes (name etc) can be reflected next time the user visits their pages.
The sp_oagetproperty call to 'users' (should) return a list of about 3100 users, from which each one can be compared are needed. Now, if there is another way to do this, I'd love to know.
LDAP has been suggested but we don't use AD yet, and I'm not sure if LDAP can be used like this.
>>As I said, you can grab a handle to the Recordset and then manipulate that object to work with the data.
I'm sure you can, but I'd need some guidance on that.
-
can this list be seem using the command prompt?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply