To have T-SQL and CmdExec execution in one job step

• Ranch

  Mr or Mrs. 500

  Points: 511

  More actions

  May 11, 2009 at 9:58 am

  #155688

  Hi,

  I want to retrieve some set of records using select query and loop through all the records in the select query.

  While looping it, for each record I want to call a exe to be executed.

  If this is possible, Can somebody let me know how?

• spaghettidba

  SSC Guru

  Points: 105712

  More actions

  May 11, 2009 at 11:08 am

  #992330

  You can do it in a script step, VBScript or JScript.

  Dim connStr

  Dim tmpConn

  Dim rs

  Dim SERVERNAME, Catalog, UserId, pwd

  Dim ws

  SERVERNAME = "myServer"

  Catalog = "myDBName"

  UserId = "user"

  pwd = "pwd"

  Set tmpConn = createObject("ADODB.Connection")

  Set ws = createObject("WScript.Shell")

  If tmpConn.State 1 Then

  connStr = "provider=sqloledb;data source=" & SERVERNAME & _

  ";Initial Catalog="& Catalog &";User ID="& userId &";Password=" & pwd & ";"

  tmpConn.ConnectionTimeout = 350

  tmpConn.CommandTimeout = 0

  tmpConn.ConnectionString = connStr

  tmpConn.CursorLocation = 3

  tmpConn.Open

  End If

  sql = "SELECT * FROM MyTable "

  Set rs = tmpConn.execute(sql)

  While Not rs.EOF

  strExec = rs.fields(0).value

  call ws.run strExec, 0, true

  rs.moveNext

  Wend

  rs.close

  tmpConn.close

  Remember that this will run with the account settings you set in the step properties and that this will run at the sql server machine. Is it REALLY what you want to do? Think twice or more...

  Hope this helps

  Gianluca

  -- Gianluca Sartori