SQL 7 OLE Automation Object via Stored Procedure 
Author Message
 SQL 7 OLE Automation Object via Stored Procedure

Not having much luck with my first attempt at this.
The following error message is constructed by my stored procedure,
relaying the results I get from sp_OAGetErrorInfo:

    Error instantiating SQLSPAWNLib.Spawner.
    Source:ODSOLE Extended Stored Procedure,
    Reason: Invalid Class String

I created the component SQLSpawnLib.Spawner using ATL.  Tested it
from a VB application.  Works exactly as expected.

I have a test SQL database on my development machine.  In a user
database I created sp_Spawn, which starts like this...

Alter Procedure dbo.sp_Spawn









As









 BEGIN


  BEGIN



   RETURN

    ...

Then made a simple VBS script to test it (excerpt):
Set cn = WScript.CreateObject("ADODB.Connection")
cn.Open ConnectionString

Set cmd = WScript.CreateObject("ADODB.Command")
cmd.CommandText = "sp_Spawn"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cn

cmd.Parameters.Refresh






cmd.Execute




I tried using the CLSID variation on sp_OACreate, but this did not
help.  Says class not registered.  I explicity registered using
REGSVR32, and still no go.

I haven't found anything in this regard, but do I additionally need
to do some kind of special registration with SQL Server?

Any clues?

--
______________________________________________________________________
Lee Gillie, CCP                                Remove NOSPAM to E-Mail
Online Data Processing, Inc. - 3501 N. Haven -  Spokane, WA 99207-8500



Fri, 25 Apr 2003 03:00:00 GMT  
 SQL 7 OLE Automation Object via Stored Procedure

I resolved this.  Dummy me :)   I used the type library name,
instead of the class name.  So I can now spawn programs via
a SQL Server stored procedure.  Cool.
--
______________________________________________________________________
Lee Gillie, CCP                                Remove NOSPAM to E-Mail
Online Data Processing, Inc. - 3501 N. Haven -  Spokane, WA 99207-8500



Fri, 25 Apr 2003 03:00:00 GMT  
 SQL 7 OLE Automation Object via Stored Procedure

Quote:

> I resolved this.  Dummy me :)   I used the type library name,
> instead of the class name.  So I can now spawn programs via
> a SQL Server stored procedure.  Cool.

Why don't you just use 'xp_cmdshell'. Its already built in !!!!

Loz.



Sat, 26 Apr 2003 03:00:00 GMT  
 SQL 7 OLE Automation Object via Stored Procedure

Loz -

Thanks for your input.

I understand xp_cmdshell is synchronous.  The whole reason for wanting
to shell an application (for me), is that it takes a very long time
to run.  By using it asynchronously, that is the command is simply
started, and the query finishes, it is practical to use for ASP pages,
and other file upload / batch process services we have created.
Also, I can control the priority of this process in my implementation.
I can make this very long process a low priority background task.
xp_cmdshell is built in, but it doesn't offer very much control.
With my own sp_Spawn procedure which uses my own spawner implemented
as an in-process automation component to do the spawning, I have 100%
control over exactly how this all takes place.
--
______________________________________________________________________
Lee Gillie, CCP                                Remove NOSPAM to E-Mail
Online Data Processing, Inc. - 3501 N. Haven -  Spokane, WA 99207-8500


Quote:


> > I resolved this.  Dummy me :)   I used the type library name,
> > instead of the class name.  So I can now spawn programs via
> > a SQL Server stored procedure.  Cool.

> Why don't you just use 'xp_cmdshell'. Its already built in !!!!

> Loz.



Sat, 26 Apr 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. OLE DB to execute an SQL stored procedure.

2. SqlCommand object and stored procedures in SQL Server 2000

3. Stored Procedure's Fetch Fails Via ODBC

4. Resultset via stored procedure in ORACLE

5. CRecordset updated via stored procedure

6. Stored Procedure's Fetch Fails Via ODBC

7. Resultset via stored procedure in ORACLE

8. OLE DB error -2147217887 calling Stored Procedure

9. OLE DB stored procedure binding error?

10. Executing Store procedures using an OLE Provider Connection String

11. Stored procedures and OLE DB

12. calling stored procedures with OLE DB

 

 
Powered by phpBB® Forum Software