March 18, 2004 at 11:45 am
I am trying to write some VB code to check the database status before running another routine. The logic is ' if the database is the process of applying transaction logs, being restored, or is offline, bypass the routine. Otherwise, run the routine. Since I am new to programming, I wonder if someone could help me with some sample codes to do the database status checking.
Thank you very much for your help in advance. Mickey
March 19, 2004 at 1:55 am
I dont belive you can actually do this using ADO, you will need to use SQLDMO for this...
For example you could have a function like:
Public Function chkDBStatusNormal() As Boolean
Dim oSQLServer As SQLDMO.SQLServer2
Dim oPollDB As SQLDMO.Database2
Set oSQLServer = New SQLDMO.SQLServer2
oSQLServer.LoginSecure = True 'Optional (if you want windows security use this)
oSQLServer.Connect "YourServerNameHere", "SQLLogin", "SQLPassword" 'if you use the line above SQLLogin and Password are ignored
Set oPollDB = oSQLServer.Databases("YourDatabaseNameHere")
If oPollDB.Status = SQLDMODBStat_Normal Then
chkDBStatusNormal = True
Else
chkDBStatusNormal = False
End If
Set oPollDB = Nothing
oSQLServer.Close
Set oSQLServer = Nothing
End Function
Dont forget to add a reference to the Microsoft SQLDMO Object Library to your project...
Simon..
March 19, 2004 at 10:29 am
Thank you very much, Simon, for your assistance. Actually, I am modifying the following script (check logspace). I want to check the db status before calling the MSActions as indicated bellow in red. Where should I put your function and how do I call it? Also,
how do I add a reference to the Microsoft SQLDMO Object Library? Please excuse my ignorance. Thanks again for your help.
--------------------------
Dim SQL As Object
Dim Space As Object
Dim version As String
Dim ExcludeItems As Long
Dim dynamicDb As Boolean
Type OBJS_STRUCT
ServName As String
ObjName As String
ObjId As String
FullObj As String
End Type
Private g_objs() As OBJS_STRUCT
Dim g_objcnt As Long
' Extract server name, object name and object id
Sub ParseObj(ServNum As Long, ObjNum As Long, ObjIdNum As Long, Objtype As String)
Dim I As Long
Dim fullobj As String
For I = 1 To g_objcnt
fullobj = Item$(Objtype, I,, ",")
g_objs(I).ServName = Item$(fullobj, ServNum,, ":")
g_objs(I).ObjName = Item$(fullobj, ObjNum,, ":")
g_objs(I).ObjId = Item$(fullobj, ObjIdNum,, ":")
g_objs(I).FullObj = fullobj
Next I
End Sub
' Send data and event - for dynamic, using stream name.
Sub DynamicDataEvent(resname As String, db As String, free As Double, util As Double, freeAgtMsg As String, _
utilAgtMsg As String, freeErrorCode As Long, freeErrorMsg As String, utilErrorCode As Long, utilErrorMsg As String, ServName As String, streamName As String)
Dim detailmsg As String
Dim longm As String
Dim rc As Boolean
Dim AgtMsg As String, legend$
If freeErrorCode < 0 Then
free = -1
eventmsg = "Error: DB " & db & " log space avail @" & ServName
MSActions Severity, eventmsg, AKPID, resname, freeErrorMsg
End If
If utilErrorCode < 0 Then
util = -1
eventmsg = "Error: DB " & db & " log space util @" & ServName
MSActions Severity, eventmsg, AKPID, resname, utilErrorMsg
End If
If free = -1 Or util = -1 Then
GoTo continue
End If
If DO_EVENT = "y" Then
If free < TH_FREE And freeErrorCode <> -4 Then
' SQL Server cannot give data or log size when the data and log are shared.
' Raise event only when they are not shared.
eventmsg = "DB " & db & " log space low @" & ServName
longm = "The log (" & db & ")'s available space is " & Format$(free, "0.00") & "MB; <TH = " & CStr(TH_FREE)
MSActions Severity, eventmsg, AKPID, resname, longm
End If
If util > TH_UTIL Then
eventmsg = "DB " & db & " log space low @" & ServName
longm = "The Log (" & db & ") is " & Format$(util, "0.00") & "% full; >TH = " & Cstr(TH_UTIL)
MSActions Severity, eventmsg, AKPID, resname, longm
End If
End If
...
March 22, 2004 at 11:40 am
I am not sure if Simon is still reading the thread. Any help would be appreciated. I created the function in my code. The script engine is expecting variables for the following lines:
Dim oSQLServer As SQLDMO.SQLServer2
Dim oPollDB As SQLDMO.Database2
..
Set oSQLServer = New SQLDMO.SQLServer2
Please help. Thank You!
March 23, 2004 at 1:44 am
I assume you are using VB Script in an ASP page, not an actual VB project? If this is the case, then you will need to create an instance of the object you need using a Server.CreateObject command, I am not 100% sure of the correct syntax as it has been a long time scince I`ve done any scripting work but it will be something like:
Set oSQLServer = Server.CreateObject("SQLDMO.SQLServer")
Set oPollDB = Server.CreateObject("SQLDMO.Database")
you should be able to use the rest of it as it stands....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply