Post reply

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

  • 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?

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

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