Batch.bat file call Stored Proc, no SQL activity show in Profiler

  • I have a batch file (*.bat) that it contains an osql statement to execute a stored proc in the database, by passing in parameters. I found out in one of the customer server, that the SQL job can trigger this batch file successfully and the activity monitor shows that the process is active and running, but when I traced with SQL Profiler, I found NO SQL activity in my tracing. So far this only happen to one of the customer server.

    NOTE: The stored proc is designed to be called concurrently by passing in different parameters. Therefore I can have 3 batch files calling the same store proc and passing different parameter in order to process different set of data. Two (2) of the processes can be completed without problem, but one of the process will stay and no SQL activity show in Profiler, as mentioned above.

    I have attached the code snippet that shows the content of Batch1.bat and Batch2.bat. Any idea what could possibly caused this, or what I should further check on or look into? Your advice is very much appreciated! Thank you.

    REM Batch1.bat: Process sequence 1 data

    osql -Usa -Ppwd -Sserver -Q"EXEC database1.dbo.name_compare_sp 'sequence1','p2'" >> D:\Test\1.dat

    ********************************************************

    REM Batch2.bat: Process sequence 2 data

    osql -Usa -Ppwd -Sserver -Q"EXEC database1.dbo.name_compare_sp 'sequence2','p2'" >> D:\Test\2.dat

  • What events are you tracing using Profiler?

  • Hi,

    I was using template TSQL_SPs, and use its default events. Which are:

    Audit Login, Logout

    RPC:Starting, SP:Completed, SP:Starting, SP:StmtStarting

    SQL:BatchStarting

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

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