Script method ignoring flags?

• Matt McGill

  Newbie

  Points: 1

  More actions

  March 7, 2005 at 10:34 pm

  #87800

  Ok, I've been fighting this for almost four hours.

  I'm trying to write a simple DTS package to generate script for the tables and user-defined functions in a database, and while I'm very inexperienced with SQL server and VBScript, this just doesn't seem like it should be hard.

  What it appears to come down to is the Script() method ignoring many of the flags I give it. I cannot for the life of me get the thing to generate the appropriate checks for existence. Here's some code:

  For Each objTable In objDB.Tables

  If objTable.SystemObject = False Then

  For Each objKey In objTable.Keys

  If objKey.Type = 3 Then

  strScript = strScript & objKey.Script (4096 or 1 or 4, strFName)

  End If

  Next

  End If

  Next

  I'd like to use the SQLDMOScript_* constants instead of the integer values, but they don't seem to work either - when I use any of them (except for SQLDMOScript_AppendToFile oddly enough) the function produces no output at all. So I use the numbers. Except that only *sortof* works. I get no existence checks no matter what I do (which are the ones I particularly want), and some others won't work as well.

  To provide a little context, this is part of an ActiveX task in a DTS package. I'm tempted to try a standalone VB project to see if that's any easier. This has been absolutely insane so far, and frustrating in the extreme.

  -Lutrosis

• CAGreensfelder

  SSC Eights!

  Points: 919

  More actions

  March 10, 2005 at 7:50 am

  #546375

  I did it in a stand-alone VB program.  I found this very helpful:

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_2yi7.asp

  Set oSQLServer = CreateObject("SQLDMO.SQLServer2")

  oSQLServer.LoginSecure = True

  oSQLServer.Connect strSqlServerName

  Set oDatabase = oSQLServer.Databases(strSqlDatabaseName)

  For Each oDatabaseObject In oDatabase.Tables

      If Not oDatabaseObject.SystemObject Then

           strScript = oDatabaseObject.Script 

                        (SQLDMOScript_Default + _

                         SQLDMOScript_DRI_Checks + _

                         SQLDMOScript_DRI_Defaults + _

                         SQLDMOScript_DRI_PrimaryKey + _

                         SQLDMOScript_DRI_UniqueKeys, "", "", _

                         SQLDMOScript2_NoCollation)

           'do something with script here.

      End if

  Next

  ' I scripted the FKs, Indexes and Triggers separately

  For Each oDatabaseObject In oDatabase.UserDefinedFunctions

              iScriptOptions = SQLDMOScript_Default + _

                                    SQLDMOScript_Drops

              strScript = oDatabaseObject.Script(iScriptOptions)

              'do something with the script...

  Next