November 23, 2009 at 2:38 pm
how would i go about changing the following code to use smo instead of dmo.
declare @sql varchar(8000),
@contextvarchar(255),
@objServerint ,
@buffervarchar(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
November 28, 2009 at 4:33 pm
This is because SMO is a .Net API, but sp_OACreate (and DMO) are COM facilities.
To do this you would first have to create and out-of-process COM Server which then called SMO for you. The last post in this thread[/url] at MSDN shows how.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply