ActiveX Script/DMO Question

Question

ActiveX Script/DMO Question

hawg

SSCertifiable

Points: 6044
More actions
November 30, 2005 at 10:26 am

#364335

I am using a script I found here to help me script out all of the jobs I have on my SQL Server. The script works great but I want to add a little more functionality to make it more portable and I'm not sure how to do it.
In the following script, you will see path here where I have to put the output path. I have this path stored in a table in a local database. This table has only one record with one field.
I want to find a way to retrieve the value of that one record in my table. Can someone tell me how I can do that?
Here is the script:
Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strAllJobs
Dim strJob
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect
strPath = "path here"
For Each oJB In conServer.JobServer.Jobs
IF LEFT(OJB.Name, 5) = "DEV -" THEN
  strJob = strJob & "--------------------------------------------------" & vbCrLf
  strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
  strJob = strJob & "--------------------------------------------------" & vbCrLf
  strJob = strJob & oJB.Script() & vbCrLf
  strAllJobs = strAllJobs & "--------------------------------------------------" & vbCrLf
  strAllJobs = strAllJobs & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
  strAllJobs = strAllJobs & "--------------------------------------------------" & vbCrLf
  strAllJobs = strAllJobs & oJB.Script() & vbCrLf
  strFilename = ""
  strFilename = strPath & OJB.Name & ".sql"

  Set fso = CreateObject("Scripting.FileSystemObject")
  Set iFile = fso.CreateTextFile(strFilename, True)
  iFile.Write(strJob)
  iFile.Close
  Set iFile = Nothing
  Set fso = Nothing
  strJob = ""
END IF
Next
Set conServer = Nothing
strFilename = ""
strFilename = strPath & "All Development Jobs.sql"

Set fso = CreateObject("Scripting.FileSystemObject")
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write(strAllJobs)
iFile.Close
Set iFile = Nothing
Set fso = Nothing
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hi,

To get a single value from a database table field with VBscript see below. Replace MyServerName, MyDatabaseName, MyUser, MyPassword, MyTable and MyField with your values.

Code creates a recordset with values from the table specified by your SQLstring. Then a sub DisplayData() goes from the beginning to the end of the recordset and posts message boxes with the values. You would not want it in a job, so take MsgBox line out. Also, there is no need to loop through the recordset. You will have just one value as you say. My select statement in SQLstr says "select top 1..." I left a loop in this example because you may want to select more values in the future. Let me know if you need any error handling. There is a possibility of an error in this script because I was selecting certain lines from a bigger script.

Const adOpenDynamic = 2

Const adLockOptimistic = 3

Const adCmdTableDirect = 512

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

On Error Resume Next

CNNstring = "Provider=sqloledb;Data Source=MyServerName;Initial Catalog=MyDatabaseName;User Id=MyUser;Password=MyPassword;"

SQLstring = "select top 1 MyField from MyTable"

cn.Open CNNstring

rs.Open SQLstring,cn

DisplayData()

rs.Close

cn.Close

sub DisplayData

if not (rs.eof and rs.bof) Then

rs.MoveFirst

do while Not rs.eof

rString = rString & rs.fields("MyField").value & Chr(10) & Chr(13)

rs.MoveNext

loop

MsgBox rstring

End If

end sub

Regards,Yelena Varsha

hawg SSCertifiable Points: 6044 More actions · Answer 2

Thanks for the reply. I can definitely see a use for it in the future.

I got your response while I was working on another solution. I saved the script as a VBS file and call the script file from a stored procedure passing in the path as a parameter. (I have more control over where my script file resides than I do on where I have to change my backups to go to.)

Thanks again,

hawg

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

philcart SSC-Forever Points: 47796 More actions · Answer 3

Using SQL-DMO, you'd have something like this,

 Dim oSrvr ' SQL-DMO server object
 Dim oDb ' SQL-DMO database object
 Dim oQry ' SQL-DMO Query results object
 Dim sSQL ' SQL string to execute
 Dim sFile ' filename from table

 ' build SQL string to retrieve stored value
 sSQL = "SELECT field1 FROM Table1"

 ' use SQL-DMO to connect to server and retrieve stored global variables
 Set oSrvr = CreateObject("SQLDMO.SQLServer")
 oSrvr.LoginSecure = True
 oSrvr.Connect "<< YOUR SQL SERVER >>"
 Set oDb = oSrvr.Databases("<< Your Database >>")
 Set oQry = oDb.ExecuteWithResults(sSQL)

 sFile = oQry.GetColumnString(1,1) ' record number, field number

--------------------
Colt 45 - the original point and click interface