April 3, 2003 at 9:28 am
Hi,
I use the system procedures sp_OA in my stored procedure
to generate scripts of database. I am able to generate all
objects but want to generate one object at the time.
here my code:
---
--Create Object SERVER
---
EXEC @int_Hr = Master.dbo.sp_OACreate 'SQLDMO.SQLServer',
@int_OServer OUTPUT
---
--Set to window mode
---
EXEC @int_Hr = Master.dbo.sp_OASetProperty
@int_OServer, 'LoginSecure', 1
---
-- Connect to Server object
---
EXEC @int_Hr = Master.dbo.sp_OAMethod
@int_OServer, 'Connect', NULL,
@ServerName=@p_vc_server_name
---
--Create a Transfer Objet
---
EXEC @int_Hr = Master.dbo.sp_OACreate 'SQLDMO.Transfer2',
@int_OTransfer OUTPUT
--At this point, I can generate all objects of database
--using property of transfer
object: 'CopyAllObjects', 'CopyAllTables', ...
--and method 'ScriptTransfer' of database object.
EXEC @int_Hr = Master.dbo.sp_OASetProperty
@int_OTransfer, 'copyAllObjects', 1
SET @vc_Command = 'Databases("' + @p_vc_database
+ '").ScriptTransfer'
EXEC @int_Hr = Master.dbo.sp_OAMethod @int_OServer,
@vc_Command , @vc_IndexScript out , @int_OTransfer,
@p_int_SQLDMOScriptType , @vc_filename
--********
--Now I would like to generate a specific object like a
--procedure but don't know how to do this.
--********
--I have tried to use DBobject and method 'AddObject' of
--transfer object but doesn't work.
EXEC @int_Hr = Master.dbo.sp_OACreate 'SQLDMO.DBObject',
@int_ODBObject OUTPUT
EXEC @int_Hr = Master.dbo.sp_OASetProperty
@int_ODBObject, 'Name', 'sp_test'
If you have a solution, please let me know
Thanks
-----
April 3, 2003 at 1:02 pm
Best way I know of scripting a proc dynamically with code is to use sp_helptext 'ProcName'
you can loop through your objects to provide the objectname of the proc. Works great.
Only for procs though....
Edited by - scorpion_66 on 04/03/2003 1:09:23 PM
April 4, 2003 at 6:37 am
In DMO you get a reference to the object, then use the objects script method. SP_OA gives me a headache.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply