March 11, 2002 at 3:37 pm
i'm calling sqldmo from vb client. all properties seem to work fine except "Default"
if you instantiate a column object
Dim objCol as sqldmo.Column
set objCol = objSQLServer.Databases(n).Tables(n).Columns(n)
msgbox objCol.Default
you will get empty string even though column does have a default. all other properties will be fine.
PLEASE note that variable instantiation, sql server object etc is all fine. the code will iterate thru all properties of column and product correct result EXCEPT for default.
is this a sqldmo bug or am i doing something wrong?
sql2000/win2k
thanks in advance
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
March 11, 2002 at 5:41 pm
Try objCol.DRIDefault.Text.
Andy
March 11, 2002 at 6:02 pm
quote:
Try objCol.DRIDefault.Text.Andy
thx for response - this looks like it *could* work but i already tried it and it always kicks an error ???
"Object doesn't support this property or method"
here is def for DRIDefault:
"The DRIDefault object represents the properties of a Microsoft® SQL Server™ 2000 column DEFAULT constraint."
here is definition for default
"The Default property identifies a Microsoft® SQL Server™ default bound to a column or user-defined data type."
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
March 11, 2002 at 6:08 pm
It works, did you include the .text portion? As far as the defaults, its the difference in how you create them - one is part of the table definition kinda sorta, the other is shown under the Defaults node in EM.
Andy
March 11, 2002 at 6:55 pm
quote:
It works, did you include the .text portion? As far as the defaults, its the difference in how you create them - one is part of the table definition kinda sorta, the other is shown under the Defaults node in EM.Andy
hit me again on this one - forgive me for being slow but i'm went to public school as a kid 🙂
what "works" - .default, .dridefault or both? for me i always get empty string for former and error on latter.
what do you mean about - "did i include text portion?"
and which is part of table definition (dri default) and default is in em? is this what you mean?
if i enter in defaults via em should i be able to interrogate them from sqldmo default property?
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
March 11, 2002 at 7:44 pm
You want to use column.dridefault.text. It doesn't work correctly without the ".text" portion. This is a default that you would enter in EM in the designer - I tested by adding a default of 'abc' to a varchar column. So the answer is yes, you can retrieve defaults set that way. You should also be able to create a true default (under the Defaults node in EM, not in the table designer) and bind it to a column - then retrieve it using column.default.
Andy
March 11, 2002 at 8:11 pm
ok thanks for help. i'm gonna call it a night and try again tommorrow. i understand now the differences between the two which helps but results are the same. i should get a string for dridefault but ALWAYS get and error
the following code illustrates what i am attempting to do
.Default = sqlCol.Default
If Len(.Default) = 0 Then
On Error Resume Next
.Default = sqlCol.DRIDefault
If Err <> 0 Then
Err = 0
Else
Debug.Print .Default
End If
End If
basically i interrogate .default property. if it is empty i try dridefault. if it kicks an erro i ignore it. if it does not kick an error i print it out. but it fails to recognize the defaults created via EM at all ever.
anyway - lemme bang on it some more tommorrow. thanks much for your help
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
March 13, 2002 at 7:23 pm
quote:
You want to use column.dridefault.text. It doesn't work correctly without the ".text" portion. This is a default that you would enter in EM in the designer - I tested by adding a default of 'abc' to a varchar column. So the answer is yes, you can retrieve defaults set that way. You should also be able to create a true default (under the Defaults node in EM, not in the table designer) and bind it to a column - then retrieve it using column.default.Andy
thanks!!! - finally figured it out... i didn't realize dridefault was an object with a property of .text. works fine now as you indicated it would. for benefit of other visitors i will post a couple other code samples.
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
March 13, 2002 at 7:29 pm
sample code to get default via dmo
this simple code returns default value for column Phone in Pubs.Authors table (if you look in the table you'll find a default value for column Phone is UNKNOWN):
Dim oSQLServer As New SQLDMO.SQLServer
Dim oTable As SQLDMO.Table
Dim oColumn As SQLDMO.Column
oSQLServer.Connect "Viktor", "sa", "123"
Set oTable = oSQLServer.Databases("Pubs").Tables("Authors")
Set oColumn = oTable.Columns("Phone")
MsgBox "Default value of column Authors.Phone is " & oColumn.DRIDefault.Text
oSQLServer.DisConnect
Msgbox shows:
Default value of column Authors.Phone is ('UNKNOWN')
In the same manner you'll get a list of defaults for all columns of a particular table (all tables).
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
March 13, 2002 at 7:30 pm
more sqldmo default code
Dim objSQLServer As New SQLDMO.SQLServer
objSQLServer.LoginSecure = True
objSQLServer.Connect "(local)"
Dim objDatabase As SQLDMO.Database
For Each objDatabase In objSQLServer.Databases
If UCase(objDatabase.Name) = UCase("pubs") Then
Exit For
End If
Next objDatabase
Dim objTable As SQLDMO.Table
For Each objTable In objDatabase.Tables
If UCase(objTable.Name) = UCase("AUDIT_LOG_TRANSACTIONS") Then
Exit For
End If
Next objTable
Dim objColumn As SQLDMO.Column
For Each objColumn In objTable.Columns
If UCase(objColumn.Name) = UCase("AUDIT_LOG_TRANSACTION_ID") Then
Exit For
End If
Next objColumn
MsgBox objColumn.DRIDefault.Text
And I got "(newid())"
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
March 14, 2002 at 4:49 am
Thanks for the follow up!
Andy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply