sp_OACreate

[Phil]

I am trying to call a com dll using TSQL and am having
problems.

I us sp_OACreate to crate an object then sp_OAMethod to
call a method on the object. I get an error from
sp_OAMethod saying "Unkown Name".

I think the problem is that the method that I am calling
is not on the public interface of the object.

How can I create an object that points to this interface?

Phil.

[Adam Machanic]

You might want to investigate creating an Extended Stored Procedure instead;
I've wasted far too much time playing with those sp_OA* procedures and found
them to be ineffective for all but the simplest of COM calls. But, as
always, YMMV :)

"Phil" <phi...@intex.co.uk> wrote in message
news:314901c3fd39$f591eb90$a001...@phx.gbl...

[Dan Blake]

Here is a quick example using the sp_OACreate:

/*Script Database & Tables in system*/
/*Using SQL DMO Objects !!*/

declare @objDMO int
declare @objDatabase int
declare @resultCode int
declare @dbname varchar(200)
declare @tablename varchar(200)
declare @cmd varchar(300)
declare @temp varchar(8000)
declare @SPName sysname

Set @dbname = 'Trigger_Generator'
Set @tablename = 'tubs'
Set @SPName = 'spz_Auto_OutputTriggerCode'

EXEC @resultcode = sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT
if @resultcode = 0
print 'Created Object'

Exec @resultcode = sp_OASetProperty @objDMO, 'loginsecure', 'true'

EXEC @resultcode = sp_OAMethod @objDMO, 'Connect', NULL, '(local)'
if @resultcode = 0
print 'connected'

--select @objDMO as ObjConnectionCode

Set @cmd = 'databases("' + @dbname + '").tables("' + @tablename + '").script'
--Set @Cmd = 'databases("'+@dbname+'").script(,,8388608)'
--print @CMd

--SET @Cmd = 'databases("'+@dbname+'").StoredProcedures("' +@SPName+ '").script'
--print @CMd
--SET @Cmd = 'Databases.StoredProcedures.(1070626857).script'
--print @CMD
print @Cmd
Exec @resultcode = sp_OAMethod @objDMO, @cmd , @temp OUTPUT, 4
print @temp

if @resultcode <> 0 begin print '/*Error Scripting Object*/' end
--set @cmd = 'databases("'+@dbname+'").table("'+@tablename+'").
-- 16711422
--Exec sp_OAMethod 16711422,'databases.table("tubs").script'
EXEC @resultcode = sp_OADestroy @objDMO
if @resultcode = 0
Print 'destroyed object'