June 5, 2007 at 2:23 pm
I have a VB6 program that scripts SQL Server database objects to a set
of files. It has no forms, just a module with Sub Main that connects
to the DB and uses SQL-DMO to script the objects. I execute the
program from a SQL Agent job on the DB server. Here is the code:
Sub Main()
Dim oServer As SQLDMO.SQLServer
Dim oDatabase As SQLDMO.Database
Dim oProc As SQLDMO.StoredProcedure
Dim oView As SQLDMO.View
Dim oTable As SQLDMO.Table
Dim ScriptPath As String
Dim fso As Scripting.FileSystemObject
Dim FileName As String
Const EXCLUDE_LIST = "MASTER/MSDB/MODEL/TEMPDB/NORTHWIND/PUBS"
Dim CommandArgs() As String
Dim i As Integer, DestDir As String, DestDrive As String
Set oServer = New SQLServer 'CreateObject("SQLDMO.SqlServer")
Set fso = New FileSystemObject
oServer.LoginSecure = True
oServer.Connect "(local)"
ScriptPath = "\\sfbufs\Shared\ARD\Database Proc Backups\"
For Each oDatabase In oServer.Databases
If InStr(1, EXCLUDE_LIST, UCase(oDatabase.Name)) = 0 Then
FileName = ScriptPath & oServer.NetName & "_" &
oDatabase.Name & "_View.sql"
If fso.FileExists(FileName) Then
fso.DeleteFile FileName
End If
' script all views in the selected database
For Each oView In oDatabase.Views
''SQLDMOScript_AppendToFile=256
''SQLDMOScript_ObjectPermissions=2
''SQLDMOScript_ToFileOnly=64
''SQLDMOScript_PrimaryObject=4
If oView.SystemObject = False Then
oView.Script 2 + 64 + 4 + SQLDMOScript_Drops +
SQLDMOScript_AppendToFile, FileName
End If
Next
End If
Next
''clean up
oServer.DisConnect
Set oServer = Nothing
Set oTable = Nothing
Set oDatabase = Nothing
Set oProc = Nothing
Set fso = Nothing
Set oView = Nothing
End ' I tried it both with and without the "End", neither worked
End Sub
It runs on a schedule, writes the files, and then just hangs there.
You have to login to the server and kill the process with Task
Manager. I also tried creating a batch file that executes the program and calling the batch file from the SQL Agent job, but it behaves the same way. Any ideas?
June 6, 2007 at 1:25 pm
A kind soul on usenet solved my problem. Rearrange the code in the cleanup section so that the connection to the server is disconnected last:
''clean up
Set oTable = Nothing
Set oDatabase = Nothing
Set oProc = Nothing
Set fso = Nothing
Set oView = Nothing
oServer.DisConnect
Set oServer = Nothing
When the SQL-DMO server object is closed, but there are still other objects referring to objects in a collection, it just hangs there.
June 7, 2007 at 3:14 am
Interesting.
Thanks for sharing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply