|
Home > Archive > MS SQL Server DTS > March 2006 > ActiveX & SQLDMO
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
|
| Dan Guzman 2006-03-22, 3:27 am |
| > 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_Pri
maryKey))
' ts.WriteLine 1,
(View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE. SQLDMOScript_Indexes
))
End If
Next
MsgBox "Finished scripting indexes."
svr.Disconnect()
ts.Close
Main = DTSTaskExecResult_Su
ccess
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_Single
File = 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. CopyAllStoredProcedu
res = True
oVistas.CopyAllViews = True
oDb.ScriptTransfer oTablas, SQLDMOXfrFile_Single
File,
" \\srvaa8\unidades\De
saGes\GestionAplicac
iones\SQL\SQL2000\Sc
riptsAutomaticos\Tab
las" & oDb.Name & ".txt"
oDb.ScriptTransfer oProcedimientos, SQLDMOXfrFile_Single
File,
" \\srvaa8\unidades\De
saGes\GestionAplicac
iones\SQL\SQL2000\Sc
riptsAutomaticos\Pro
c1" & oDb.Name & ".txt"
oDb.ScriptTransfer oVistas, SQLDMOXfrFile_Single
File,
" \\srvaa8\unidades\De
saGes\GestionAplicac
iones\SQL\SQL2000\Sc
riptsAutomaticos\Vis
tas" & oDb.Name & ".txt"
fichero1 =
" \\srvaa8\unidades\De
saGes\GestionAplicac
iones\SQL\SQL2000\Sc
riptsAutomaticos\Tab
las" & oDb.Name & ".txt"
fichero2 =
" \\srvaa8\unidades\De
saGes\GestionAplicac
iones\SQL\SQL2000\Sc
riptsAutomaticos\Pro
c1" & oDb.Name & ".txt"
fichero3 =
" \\srvaa8\unidades\De
saGes\GestionAplicac
iones\SQL\SQL2000\Sc
riptsAutomaticos\Vis
tas" & oDb.Name & ".txt"
Set inFile = fso. OpenTextFile(fichero
1)
Data1 = inFile.ReadAll
inFile.Close
Set inFile = fso. OpenTextFile(fichero
2)
Data2 = inFile.ReadAll
inFile.Close
Set inFile = fso. OpenTextFile(fichero
3)
Data3 = inFile.ReadAll
inFile.Close
ficherosalida =
" \\srvaa8\unidades\De
saGes\GestionAplicac
iones\SQL\SQL2000\Sc
riptsAutomaticos\" & oDb.Name & ".txt"
Set outfile = fso. CreateTextFile(fiche
rosalida)
'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_Su
ccess
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. SQLDMOScript_DRI_Pri
maryKey))
> ' ts.WriteLine 1,
> (View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE. SQLDMOScript_Indexes
))
> End If
>
> Next
>
> MsgBox "Finished scripting indexes."
>
> svr.Disconnect()
> ts.Close
>
> Main = DTSTaskExecResult_Su
ccess
> End Function
>
>
>
| |
| Dan Guzman 2006-03-22, 7:31 am |
| The problem is that you have not declared SQLDMOScript_DRI_Pri
maryKey. 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_Pri
maryKey = 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_Pri
maryKey))
> ' ts.WriteLine 1,
> (View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE. SQLDMOScript_Indexes
))
> End If
>
> Next
>
> MsgBox "Finished scripting indexes."
>
> svr.Disconnect()
> ts.Close
>
> Main = DTSTaskExecResult_Su
ccess
> End Function
>
>
| |
| Andre 2006-03-22, 11:30 am |
| > 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_Pri
maryKey = 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_Pri
maryKey = 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_Pri
maryKey)
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_Su
ccess
End Function
| |
|
| I figured it out.
Instead of:
View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE. SQLDMOScript_DRI_Pri
maryKey)
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
| |
| Dan Guzman 2006-03-23, 3:31 am |
| > Instead of:
> View.Script(SQLDMO.SQLDMO_SCRIPT_TYPE. SQLDMOScript_DRI_Pri
maryKey)
> 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_Pri
maryKey)
> 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
>
>
|
|
|
|
|