ActiveX & SQLDMO

underprocessable> This code works in VB, but apparently not in VBA. Can someone please tell
> me what I'm doing wrong?
It's difficult to help unless you post a code snippet. My guess is that you
aren't instantiating a DMO object using CreateObject:
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Hope this helps.
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23iIxN%23VTGHA.736@.TK2MSFTNGP12.phx.gbl...
> I'm trying to write an ActiveX task to script my indexes for a couple
> tables, into a file. This will be an ActiveX step in a DTS package. It's
> failing at the step where I'm trying to script the index out. The error
> is
> "object required; SQLDMO".
> This code works in VB, but apparently not in VBA. Can someone please tell
> me what I'm doing wrong?
> Thanks, Andre
>
>|||> It's difficult to help unless you post a code snippet. My guess is that
> you aren't instantiating a DMO object using CreateObject:
Interesting...I attached a file with my exact code. I'll paste it in this
time. Thanks for your time.
Function Main()
Dim svr
Dim strFile
Dim strDB
Dim fso, f1, ts
Const ForWriting = 2
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CreateTextFile ("c:\TableIndexes.txt")
Set f1 = fso.GetFile("c:\TableIndexes.txt")
Set ts = fso.OpenTextFile("c:\TableIndexes.txt", ForWriting, True)
set svr = CreateObject("SQLDMO.SQLServer")
strDB = "MyDB"
' logon to SQL Server
With svr
.Name = "MyServer"
.LoginTimeout = 15
.LoginSecure = True
.Connect()
End With
For Each View In svr.Databases.Item(strDB).Tables
' add index info to text file
If View.Name = "table1" Or View.Name = "table2" Then
'msgbox View.Name
'ts.WriteLine View.Name
ts.WriteLine(View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_PrimaryK
ey))
' ts.WriteLine 1,
(View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes))
End If
Next
MsgBox "Finished scripting indexes."
svr.Disconnect()
ts.Close
Main = DTSTaskExecResult_Success
End Function|||hi Andre,
I've got a a script running properly using DMO:
Perhaps, it would be useful for you, I haven't idea, but in any case, how
odd having issues with DMO dll..
Function Main()
Const SQLDMOXfrFile_SingleFile = 2
Dim sS, i, fichero
Dim Data1,Data2,Data3
Set fso = CreateObject("Scripting.FileSystemObject")
Set oSS = CreateObject("sqldmo.sqlserver")
Set oDb = CreateObject("sqldmo.database")
on error resume next
oSS.Connect "sql1", "usrdts", "dts"
For i = 1 To oSS.Databases.Count
Set oDb = oSS.Databases(i)
Set oTablas = CreateObject("sqldmo.transfer")
Set oProcedimientos = CreateObject("sqldmo.transfer")
Set oVistas = CreateObject("sqldmo.transfer")
oTablas.CopyAllTables = True
oProcedimientos.CopyAllStoredProcedures = True
oVistas.CopyAllViews = True
oDb.ScriptTransfer oTablas, SQLDMOXfrFile_SingleFile,
" \\srvaa8\unidades\DesaGes\GestionAplicac
iones\SQL\SQL2000\ScriptsAutomatico
s\Tablas" & oDb.Name & ".txt"
oDb.ScriptTransfer oProcedimientos, SQLDMOXfrFile_SingleFile,
" \\srvaa8\unidades\DesaGes\GestionAplicac
iones\SQL\SQL2000\ScriptsAutomatico
s\Proc1" & oDb.Name & ".txt"
oDb.ScriptTransfer oVistas, SQLDMOXfrFile_SingleFile,
" \\srvaa8\unidades\DesaGes\GestionAplicac
iones\SQL\SQL2000\ScriptsAutomatico
s\Vistas" & oDb.Name & ".txt"
fichero1 =
" \\srvaa8\unidades\DesaGes\GestionAplicac
iones\SQL\SQL2000\ScriptsAutomatico
s\Tablas" & oDb.Name & ".txt"
fichero2 =
" \\srvaa8\unidades\DesaGes\GestionAplicac
iones\SQL\SQL2000\ScriptsAutomatico
s\Proc1" & oDb.Name & ".txt"
fichero3 =
" \\srvaa8\unidades\DesaGes\GestionAplicac
iones\SQL\SQL2000\ScriptsAutomatico
s\Vistas" & oDb.Name & ".txt"
Set inFile = fso.OpenTextFile(fichero1)
Data1 = inFile.ReadAll
inFile.Close
Set inFile = fso.OpenTextFile(fichero2)
Data2 = inFile.ReadAll
inFile.Close
Set inFile = fso.OpenTextFile(fichero3)
Data3 = inFile.ReadAll
inFile.Close
ficherosalida =
" \\srvaa8\unidades\DesaGes\GestionAplicac
iones\SQL\SQL2000\ScriptsAutomatico
s\" & oDb.Name & ".txt"
Set outfile = fso.CreateTextFile(ficherosalida)
'copiando los 3 ficheros en 1 solo
outfile.Write Data1
outfile.WriteLine
outfile.Write Data2
outfile.WriteLine
outfile.Write Data3
outfile.Close
'esborrat dels fitxers
fso.DeleteFile fichero1
fso.DeleteFile fichero2
fso.DeleteFile fichero3
Set oTablas = Nothing
Set oProcedimientos = Nothing
Set oVistas = Nothing
' if i = 4 then
' exit for
' end if
Next
Main = DTSTaskExecResult_Success
End Function
Current location: Alicante (ES)
"Andre" wrote:

> Interesting...I attached a file with my exact code. I'll paste it in this
> time. Thanks for your time.
> Function Main()
> Dim svr
> Dim strFile
> Dim strDB
> Dim fso, f1, ts
> Const ForWriting = 2
> Set fso = CreateObject("Scripting.FileSystemObject")
> fso.CreateTextFile ("c:\TableIndexes.txt")
> Set f1 = fso.GetFile("c:\TableIndexes.txt")
> Set ts = fso.OpenTextFile("c:\TableIndexes.txt", ForWriting, True)
> set svr = CreateObject("SQLDMO.SQLServer")
> strDB = "MyDB"
> ' logon to SQL Server
> With svr
> .Name = "MyServer"
> .LoginTimeout = 15
> .LoginSecure = True
> .Connect()
> End With
>
> For Each View In svr.Databases.Item(strDB).Tables
> ' add index info to text file
> If View.Name = "table1" Or View.Name = "table2" Then
> 'msgbox View.Name
> 'ts.WriteLine View.Name
> ts.WriteLine(View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOS
cript_DRI_PrimaryKey))
> ' ts.WriteLine 1,
> (View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes))
> End If
> Next
> MsgBox "Finished scripting indexes."
> svr.Disconnect()
> ts.Close
> Main = DTSTaskExecResult_Success
> End Function
>
>|||The problem is that you have not declared SQLDMOScript_DRI_PrimaryKey. You
get the constants via static members when you reference the SQLDMO class on
VB but you need to declare these in VBScript, like you did the ForWriting
FSO constant.
Add the following to you code and all should be fine. See
SQLDMO_SCRIPT_TYPE in the Books Online for a complete list of the constants.
Const SQLDMOScript_DRI_PrimaryKey = 268435456
Hope this helps.
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:OqYKvMXTGHA.776@.TK2MSFTNGP09.phx.gbl...
> Interesting...I attached a file with my exact code. I'll paste it in this
> time. Thanks for your time.
> Function Main()
> Dim svr
> Dim strFile
> Dim strDB
> Dim fso, f1, ts
> Const ForWriting = 2
> Set fso = CreateObject("Scripting.FileSystemObject")
> fso.CreateTextFile ("c:\TableIndexes.txt")
> Set f1 = fso.GetFile("c:\TableIndexes.txt")
> Set ts = fso.OpenTextFile("c:\TableIndexes.txt", ForWriting, True)
> set svr = CreateObject("SQLDMO.SQLServer")
> strDB = "MyDB"
> ' logon to SQL Server
> With svr
> .Name = "MyServer"
> .LoginTimeout = 15
> .LoginSecure = True
> .Connect()
> End With
>
> For Each View In svr.Databases.Item(strDB).Tables
> ' add index info to text file
> If View.Name = "table1" Or View.Name = "table2" Then
> 'msgbox View.Name
> 'ts.WriteLine View.Name
> ts.WriteLine(View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Primar
yKey))
> ' ts.WriteLine 1,
> (View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes))
> End If
> Next
> MsgBox "Finished scripting indexes."
> svr.Disconnect()
> ts.Close
> Main = DTSTaskExecResult_Success
> End Function
>|||> Add the following to you code and all should be fine. See
SQLDMO_SCRIPT_TYPE in the Books Online for a complete list of the constants.
> Const SQLDMOScript_DRI_PrimaryKey = 268435456
I added the line above and I still get the "object required: SQLDMO" error
on line 38. My code now looks like this:
Function Main()
Dim svr
Dim strFile
Dim strDB
Dim fso, f1, ts
Const SQLDMOScript_DRI_PrimaryKey = 268435456
Const SQLDMOScript_Indexes = 73736
Const ForWriting = 2
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CreateTextFile ("c:\TableIndexes.txt")
Set f1 = fso.GetFile("c:\TableIndexes.txt")
Set ts = fso.OpenTextFile("c:\TableIndexes.txt", ForWriting, True)
set svr = CreateObject("SQLDMO.SQLServer")
strDB = "MyDB"
' logon to SQL Server
With svr
.Name = "MyServer"
.LoginTimeout = 15
.LoginSecure = True
.Connect()
End With
' display error msgbox if logon failed
If Err.Number <> 0 Then
MsgBox "Connect Failed" & Err.Description & " (" & Err.Number & ")",
MsgBoxStyle.OKOnly, "Error"
End If
For Each View In svr.Databases.Item(strDB).Tables
' add index info to text file
If View.Name = "MyTable" Then
'msgbox View.Name
'ts.WriteLine View.Name
' ts.WriteLine
View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_PrimaryKey)
ts.WriteLine 1,
(View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes))
End If
' Some simple error handling
If Err.Number <> 0 Then
MsgBox "Script error while scripting " & "Details: " &
Err.Description & VBCLF & "Error number: " & Err.Number
svr.DisConnect()
End If
Next
MsgBox "Finished scripting indexes. The file is in c:\"
svr.Disconnect()
ts.Close
Main = DTSTaskExecResult_Success
End Function|||I figured it out.
Instead of:
View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_PrimaryKey)
It should have been this:
View.Script(SQLDMOScript_DRI_PrimaryKey)
Thanks for all your help - it definitely got me going in the correct
direction.
Andre|||> Instead of:
> View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_PrimaryKey)
> It should have been this:
> View.Script(SQLDMOScript_DRI_PrimaryKey)
That's because you have no 'SQLDMO' object reference like you do in VB.
I'm glad you were able to sort things out.
Hope this helps.
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uxT84FgTGHA.5044@.TK2MSFTNGP09.phx.gbl...
>I figured it out.
> Instead of:
> View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_PrimaryKey)
> It should have been this:
> View.Script(SQLDMOScript_DRI_PrimaryKey)
> Thanks for all your help - it definitely got me going in the correct
> direction.
> Andre
>