| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Mark Allison
Starting Member
United Kingdom
1 Posts |
 Posted - 08/19/2002 : 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!
Australia
4970 Posts |
Posted - 08/19/2002 : 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
Canada
158 Posts |
Posted - 08/19/2002 : 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
Canada
158 Posts |
Posted - 08/19/2002 : 11:20:31
|
Ok I have said nothing.... the week end was hard!!!
|
|
|
|
ashok
Yak Posting Veteran
Kenya
57 Posts |
Posted - 08/20/2002 : 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
United Kingdom
13 Posts |
Posted - 08/23/2002 : 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
Kenya
57 Posts |
Posted - 08/24/2002 : 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
United Kingdom
13 Posts |
Posted - 08/24/2002 : 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
United Kingdom
846 Posts |
Posted - 08/24/2002 : 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
United Kingdom
13 Posts |
Posted - 08/24/2002 : 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 |
|
|
| |
Topic |
|
Article: Introduction to SQL-DMO
