Article: Introduction to SQL-DMO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Introduction to SQL-DMO

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-18 : 20:08:38

Most SQL Server administrative tasks are programmable thanks to a set of objects known as SQL-DMO. This article introduces the concepts of programming DMO, and steps you through some basic, and commonly requested examples.

Article Link.

Mark Allison
Starting Member

1 Post

Posted - 2002-08-19 : 06:37:25

Interesting article. Even more interesting is your comment that SQL-DMO could cover a huge book. Well, your dreams have come true, because Allan Mitchell and Mark Allison will be publishing a book on this very topic on September 25th 2002.

See this link for further details:
http://www.apress.com/book/bookDisplay.html?bID=118

Thanks,
Mark.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-08-19 : 08:53:02

Well it could well and truly cover a book. I think a "cookbook" style would suit it nicely, that looks like what you have written.

Good luck with it!

Damian

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-19 : 11:06:11

I made a DataProject in VB add the file sqlDMO.dll in the references.
I copy pasted your code to show the table names in a msgBox and I get an error message on the first line after the Dim.
Compile error: Invalid outside procedure.
Here is my code anyway :

Dim objDMO As SQLServer
Set objDMO = New SQLDMO.SQLServer
objDMO.LoginSecure = True
objDMO.Connect "(local)"

Dim objDB As Database
Set objDB = objDMO.Databases("northwind")

Dim oTable As Table
For Each oTable In objDB.Tables
MsgBox oTable.Name
Next

objDMO.Disconnect
Set objDMO = Nothing

Did I missed something?

Thanks

Fred

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-19 : 11:20:31

Ok I have said nothing.... the week end was hard!!!

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-08-20 : 05:37:12

You will need to the SQLDMO type library to the references.
Is code-complete working in vb on the SQLDMO object ? if it isnt - it means you have not added the proper type library for DMO

julesr
Starting Member

14 Posts

Posted - 2002-08-23 : 19:57:11

Great read. I thought I'd use it to generate some scripts and save me opening EM. In VB Script I used:

set objDMO=server.createobject("SQLDMO.SQLServer")
objDMO.Connect "(local)","sa","sa"
Set objDB = objDMO.Databases("GenericCMS")
Set oTable = objDB.Tables("tiforum1")
Response.Write oTable.Script(4)
objDMO.DisConnect
Set objDMO = nothing

This works up to a point. My table contains a custom default. When I generate the script through EM, the result is:

create table tiforum1
'more stuff
EXEC sp_bindefault N'[dbo].[UW_ZeroDefault]', N'[tiForum1].[MessageChildren]'

So, my question is how can adapt my VB Script so that the output contains the EXEC sp_bindefault line too? I've consulted BOL, and tried changing my code to:

Response.Write oTable.Script(33554432)

but that didn't seem to work. If I use anything other than 4 for the ScriptType then nothing is generated.

Jules
http://www.charon.co.uk

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-08-24 : 06:25:35

You should use OR operator try :
oTable.Script(33554432 OR 4)

instead

quote:

tried changing my code to:
Response.Write oTable.Script(33554432)
but that didn't seem to work. If I use anything other than 4 for the ScriptType then nothing is generated.

Edited by - ashok on 08/24/2002 06:27:18

julesr
Starting Member

14 Posts

Posted - 2002-08-24 : 07:35:52

Thanks for the suggestion. The snippet you gave gives the same result as using 4 on its own. It seems odd that only 4 works. Any other number generates nothing.

quote:

You should use OR operator try :
oTable.Script(33554432 OR 4)
instead

Jules
http://www.charon.co.uk

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-24 : 11:14:21

If you look at the Script constants in BOL you'll see that

SQLDMOScript_Bindings
128
Generate sp_bindefault and sp_bindrule statements. Applies only when scripting references a SQL Server table.

So all you do is add 4 + 128 = 132 (well actually it suggests using logical OR as in (128 OR 4) however you get the same result)

set objDMO=server.createobject("SQLDMO.SQLServer")
objDMO.Connect "(local)","sa","sa"
Set objDB = objDMO.Databases("GenericCMS")
Set oTable = objDB.Tables("tiforum1")

Response.Write oTable.Script(132)
'Or you can use the syntax below
'Response.Write oTable.Script(128 OR 4)

objDMO.DisConnect
Set objDMO = nothing

HTH
Jasper Smith

julesr
Starting Member

14 Posts

Posted - 2002-08-24 : 11:29:40

Thanks a lot, that works. I should read BOL more carefully, I was using the wrong number.

quote:

If you look at the Script constants in BOL you'll see that

SQLDMOScript_Bindings
128
Generate sp_bindefault and sp_bindrule statements. Applies only when scripting references a SQL Server table.

Jules
http://www.charon.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources