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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Introduction to SQL-DMO
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/18/2002 :  20:08:38  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

United Kingdom
1 Posts

Posted - 08/19/2002 :  06:37:25  Show Profile  Visit Mark Allison's Homepage  Reply with Quote
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.

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 08/19/2002 :  08:53:02  Show Profile  Visit Merkin's Homepage  Reply with Quote
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
Go to Top of Page

1fred
Posting Yak Master

Canada
158 Posts

Posted - 08/19/2002 :  11:06:11  Show Profile  Reply with Quote
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

Go to Top of Page

1fred
Posting Yak Master

Canada
158 Posts

Posted - 08/19/2002 :  11:20:31  Show Profile  Reply with Quote
Ok I have said nothing.... the week end was hard!!!

Go to Top of Page

ashok
Yak Posting Veteran

Kenya
57 Posts

Posted - 08/20/2002 :  05:37:12  Show Profile  Visit ashok's Homepage  Reply with Quote
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
Go to Top of Page

julesr
Starting Member

United Kingdom
14 Posts

Posted - 08/23/2002 :  19:57:11  Show Profile  Reply with Quote
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
Go to Top of Page

ashok
Yak Posting Veteran

Kenya
57 Posts

Posted - 08/24/2002 :  06:25:35  Show Profile  Visit ashok's Homepage  Reply with Quote
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
Go to Top of Page

julesr
Starting Member

United Kingdom
14 Posts

Posted - 08/24/2002 :  07:35:52  Show Profile  Reply with Quote
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
Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 08/24/2002 :  11:14:21  Show Profile  Visit jasper_smith's Homepage  Reply with 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.

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
Go to Top of Page

julesr
Starting Member

United Kingdom
14 Posts

Posted - 08/24/2002 :  11:29:40  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000