SQL Server Forums
Profile | Register | 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
 New Topic  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
13 Posts

Posted - 08/23/2002 :  19:57:11  Show Profile  Visit julesr's Homepage  Send julesr an ICQ Message  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
13 Posts

Posted - 08/24/2002 :  07:35:52  Show Profile  Visit julesr's Homepage  Send julesr an ICQ Message  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
13 Posts

Posted - 08/24/2002 :  11:29:40  Show Profile  Visit julesr's Homepage  Send julesr an ICQ Message  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  
 New 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.1 seconds. Powered By: Snitz Forums 2000