SMO in VBS

Question

SMO in VBS

MG-148046

SSCrazy Eights

Points: 9946
More actions
June 7, 2006 at 2:43 pm

#94259

I don't know much about VBS and even less about VB.NET but I need to write a script that will script out database objects. If anyone has done this with SMO, please, please post some examples.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Brian Fenton SSC-Addicted Points: 429 More actions · Answer 1

I'm not sure if this is going to be exactly what you need, but I've found this to work in my environment, adapted from a script I found here:

--This will script out all of the database objects in each db to a local folder on the server

--it needs admin permissions to run due to the xp_cmdshell section. Be sure you change the

--drive letter below to the local install of SQL Server.

--drop proc dbo.sp_scriptdatabase

create proc dbo.sp_ScriptDatabase @dbname sysname

as

declare @command varchar(1000)

declare @texttime varchar(10)

set @texttime = convert(varchar, getdate(), 102)

set @command = '"F:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s ' + @@servername +

' /I /d ' + @dbname + ' /f c:\dbscripts\' + @@servername + '_' + @dbname + '_' + @texttime + '.txt /r'

print @command

exec master..xp_cmdshell @command

go

--run these two lines as a scheduled job weekly at 8 pm saturday on each server

--be sure to create the c:\DbScripts folder first

exec sp_MSForeachDB "exec master..sp_ScriptDatabase ?"

exec master..xp_cmdshell 'del c:\dbscripts\*_pubs*.txt c:\dbscripts\*_northwind*.txt c:\dbscripts\*_tempdb*.txt'

I'd do a search for scptxfr.exe and get the parameters for it. It basically uses SQL DMO to script out db objects, only MS already did the work of writing the program for you. It comes with a SQL Server install. Again, not quite what you asked for but it still may help.

Brian

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 2

Thanks Brian, I appreciate it. I already have a VBScript that uses SQL DMO to script out all the objects. I need to convert that to SQL SMO and the 2 are different enough to cause some real headaches.

I broke down and started using Visual Studio and VB.Net. I can get familiar with the SMO objects since Intellisense will show it all.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Brian Fenton SSC-Addicted Points: 429 More actions · Answer 3

Oh, I misunderstood somewhat what you were after. Try incorporating this:

Server svr = new Server()

Database db = new Database(svr, "MyDatabase")

db.DatabaseOptions.AutoClose = true

Scripter scripter = new Scripter()

scripter.Server = svr

scripter.Options.IncludeHeaders = true

scripter.Options.SchemaQualify = true

SqlSmoObject[] objs = new SqlSmoObject[1]

objs[0] = db

scripter.Script(objs)

foreach (string s in scripter.Script(objs))

Console.WriteLine(s)

Brian

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 4

As I said, I inherited the script and I don't know much at all about scripting. This is what I get:

Set svr = CreateObject("Microsoft.SQLServer.Management.SMO.Server")

'Server svr = new Server(); this doesn't work

Database db = new Database(svr, "AdventureWorks"); 'this throws an error at the end of Database - Expected end of statement

Scripter scripter = new Scripter();

scripter.Server = svr;

scripter.Options.IncludeHeaders = true;

scripter.Options.SchemaQualify = true;

SqlSmoObject[] objs = new SqlSmoObject[1];

objs[0] = db;

scripter.Script(objs);

foreach (string s in scripter.Script(objs))

{

Console.WriteLine(s);

}

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Brian Fenton SSC-Addicted Points: 429 More actions · Answer 5

I was mostly using canned code examples I found various places. I did some more research and I found a quote from a Microsoft rep saying that they didn't intend to put SMO support in VBscript so your .NET option may be the way to go.