Ole Automation procedures. DB scope?

I have been looking at the OLE automation procs (ex. sp_OACreate, etc. ) and
am wondering about their scope. Does the OLE process have scope on the
executing transaction? Or must the OLE object establish its own database
connection? For example, I have an example using 'SQLDMO.SQLServer' and it
establishes its own database connection, so some of its parameters are
instance, login name, and PW.
We have several db instances and databases and we move procs around quite
often and I don't want to have to worry about passing db connection info.
Thanks,
MichaelHi
there is no scope in SQL Server 2000. In SQL Server 2005, CLR objects have a
scope.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Snake" wrote:

> I have been looking at the OLE automation procs (ex. sp_OACreate, etc. ) a
nd
> am wondering about their scope. Does the OLE process have scope on the
> executing transaction? Or must the OLE object establish its own database
> connection? For example, I have an example using 'SQLDMO.SQLServer' and i
t
> establishes its own database connection, so some of its parameters are
> instance, login name, and PW.
> We have several db instances and databases and we move procs around quite
> often and I don't want to have to worry about passing db connection info.
> Thanks,
> Michael|||Mike,
I am not sure if you answered my questions in a way that I can understand.
Can you give me a little more detail?
Thanks,
Michael
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> there is no scope in SQL Server 2000. In SQL Server 2005, CLR objects have
a
> scope.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Snake" wrote:
>

OLE Automation and "global variable"

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
DaveAs 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 yo
u
already do so. :)
SQL 2005 also supports (and controls) CLR, maybe that can provide you with
more functionality.
ML
http://milambda.blogspot.com/

OLE automation

How do I start OLE automation in SQL 2005? Thanks.You can enable OLE automation using the SQL Server Surface Area Configuratio
n
Tool. To access the SQL Server Surface Area Configuration Tool select START
| Programs | Microsoft SQL Server 2005 | Configuration Tools | Surface Area
Configuration.
Select Surface Area Configuration for features and Select OLE Automation and
check Enable Ole Automation.
This can also be done programatically using the following script:
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH
OVERRIDE
EXEC sys.sp_configure N'Ole Automation Procedures', N'1'
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH
OVERRIDE
HTH
- Peter Ward
WARDY IT Solutiuons
"00MichaelJordan" wrote:

> How do I start OLE automation in SQL 2005? Thanks.
>
>

OLE automation

How do I start OLE automation in SQL 2005? Thanks.
You can enable OLE automation using the SQL Server Surface Area Configuration
Tool. To access the SQL Server Surface Area Configuration Tool select START
| Programs | Microsoft SQL Server 2005 | Configuration Tools | Surface Area
Configuration.
Select Surface Area Configuration for features and Select OLE Automation and
check Enable Ole Automation.
This can also be done programatically using the following script:
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH
OVERRIDE
EXEC sys.sp_configure N'Ole Automation Procedures', N'1'
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH
OVERRIDE
HTH
- Peter Ward
WARDY IT Solutiuons
"00MichaelJordan" wrote:

> How do I start OLE automation in SQL 2005? Thanks.
>
>

OLE automation

How do I start OLE automation in SQL 2005? Thanks.You can enable OLE automation using the SQL Server Surface Area Configuration
Tool. To access the SQL Server Surface Area Configuration Tool select START
| Programs | Microsoft SQL Server 2005 | Configuration Tools | Surface Area
Configuration.
Select Surface Area Configuration for features and Select OLE Automation and
check Enable Ole Automation.
This can also be done programatically using the following script:
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH
OVERRIDE
EXEC sys.sp_configure N'Ole Automation Procedures', N'1'
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH
OVERRIDE
HTH
- Peter Ward
WARDY IT Solutiuons
"00MichaelJordan" wrote:
> How do I start OLE automation in SQL 2005? Thanks.
>
>