how would i go about changing the following code to use smo instead of dmo.
declare @sql varchar(8000),
@context varchar(255),
@objServer int ,
@buffer varchar(8000),
@servername varchar(150)
select @servername = @@servername
select @sql = 'echo. > c:\script.sql'
exec master..xp_cmdshell @sql
-- prepare scripting object
select @context = 'create dmo object'
exec sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
select @context = 'set integrated security '+@servername+''
exec sp_OASetProperty @objServer, LoginSecure, 1
select @context = 'connect to server '+@servername+''
exec sp_OAMethod @objServer , 'Connect', NULL, @servername
exec sp_OAMethod @objServer, 'databases("<dbname>").script' , @buffer OUTPUT, '20' , 'c:\temp.sql'
select @sql = 'type c:\temp.sql >> c:\script.sql'
exec master..xp_cmdshell @sql
I assumed that changing
exec sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
one of the following would have worked
exec sp_OACreate 'Smo.Server', @objServer OUT
exec sp_OACreate 'Microsoft.SqlServer.Management.Smo.Server', @objServer OUT
exec sp_OACreate 'Microsoft.SqlServer.Smo.Server', @objServer OUT
Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]