OLE Automation and "global variable"

15 Dec 2005 5:16 PM

Dave

I have an OLE automation object that I am instantiating inside of a stored
proc using
sp_OACreate 'myObject', @object OUT.

The stored proc is called multiple times, each time instantiating a new
instance of the object (and then destroying it when it is finished).

Is it possible in SQL Server to create a global variable to refer to the
OLE automation object?

IOW can I instantiate the object outside the proc, assign the @object
pointer to a global variable and then refererence this global variable
inside my stored proc so I don't have to create the object every time it is
called?

Perhaps a more basic question is: is it possible to create global variables
in T-SQL and if so, how?

Thanks
Dave

15 Dec 2005 10:59 PM

ML

As far as I know, OLE Automation objects in SQL Server can only exist inside
the scope where they were created.

There are also no global variables in SQL, but you could create a temporary
(or permanent) table to store values that can be accessed "globally".

I strongly recommend you destroy all objects after use, even those
implicitly created by the methods used. But then again you mentioned that you
already do so. :)

SQL 2005 also supports (and controls) CLR, maybe that can provide you with
more functionality.

ML

---
http://milambda.blogspot.com/