Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    8

    Unanswered: batch file tp execute SP with IN parameter

    I have a SP with one IN parameter.
    I want to know how to create a batch file to do this.
    I have gone through the sqlcmd tutorials, but if someone can explain the step by step procedure it will be very useful.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    to do what? fire the proc? have you tried anything you learned in your tutorial? do you want to show us? maybe we can (many assumptions here) fix what is wrong with it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Sep 2009
    Posts
    8
    Hi,

    I have a SP with a IN Parameter.
    I created a .sql file and the code within is:

    USE UtilizationData_PVZ;
    GO
    EXEC spFinalProcess 'TESTING1'
    GO

    my batch file has this content within:
    sqlcmd -ic:\PadhmaBatch\testsqlcmd.sql -oC:\PadhmaBatch\myoutput.txt

    It is working, but if you see my .sql file above, I have manually entered the value 'TESTING1' which is a user input for the SP.

    I want to create a batch file which will ask for user input for the SP IN Parameter and then it should use that input for executing the SP.
    Is this possible?
    Last edited by tyson27; 09-09-09 at 13:01.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by tyson27
    Hi,

    I have a SP with a IN Parameter.
    I created a .sql file and the code within is:

    USE UtilizationData_PVZ;
    GO
    EXEC spFinalProcess 'TESTING1'
    GO

    my batch file has this content within:
    sqlcmd -ic:\PadhmaBatch\testsqlcmd.sql -oC:\PadhmaBatch\myoutput.txt

    It is working, but if you see my .sql file above, I have manually entered the value 'TESTING1' which is a user input for the SP.

    I want to create a batch file which will ask for user input for the SP IN Parameter and then it should use that input for executing the SP.
    Is this possible?
    ::.cmd file to do what you want
    @echo off
    :again
    set /p parameter=Please enter your Value here:
    if /i [%parameter%]==[END] endlocal&goto end
    if [%parameter%]==[] goto again

    sqlcmd -E -Q "EXEC spFinalProcess '%Parameter%'" -d "UtilizationData_PVZ"
    :end


    I would employ a bit of error checking, but this is a simple example that would work.
    Last edited by PMASchmed; 09-09-09 at 14:21.

  5. #5
    Join Date
    Sep 2009
    Posts
    8
    Thank you. It worked.

  6. #6
    Join Date
    Sep 2009
    Posts
    8
    I want to employ error checking. How would i do that, is there any article which will tel me that.

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by tyson27
    Thank you. It worked.
    you can separate commands in -Q

    ie -Q"set nocount on;select * from.."

    Another little trick I use in DOS to set a variable from a SQL query:

    e.g.:
    for /f "tokens=1 delims= " %%E in ('osql -E -d "securitymaster" -Q"set nocount on;select dbo.fn_FormatDateTime(dbo.fnPriorBusinessDate('%DA TETODAY%'), 'YYYYMMDD');" -S %SQLHOST% -h-1') do set FileDate=%%E

    -h-1 removes header from output.




    Good site for DOS string manipulation:
    DOS - String Manipulation

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by tyson27
    I want to employ error checking. How would i do that, is there any article which will tel me that.
    Redirect stdout to a log file, and you can FIND or FINDSTR valus in the log file that would indicate failure or success.

    You can check the %errorlevel% to see if it is 0 (for certain commands)

    e.g. dos command:
    if not "%ERRORLEVEL%" == "0" echo ***ERROR*** :: or goto a sub

    check if file exists
    if not exist filename echo ***ERROR*** :: or goto a sub

  9. #9
    Join Date
    Sep 2009
    Posts
    8
    Hi,

    I have this command:
    sqlcmd -E -Q "EXEC spFinalProcess '%Parameter%'" -d "UtilizationData_PVZ" -oC:\UtilizationData\LogFile\UtilDataProcessLogFile .txt

    This will run successfully and show the output of the process in the UtilDataProcessLogFile.txt file.

    This whole process involves, for example 10 devices and there is data collection for these 10 devices.

    The log file above will show me, how many rows are affected and time taken to do the process.

    What I want is to see along with the above information, in the above log file is, what are the devices the data was NOT collected for and why.

    Is this possible? if yes, how and where can i see some examples for such scenarios.

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by tyson27
    Hi,

    I have this command:
    sqlcmd -E -Q "EXEC spFinalProcess '%Parameter%'" -d "UtilizationData_PVZ" -oC:\UtilizationData\LogFile\UtilDataProcessLogFile .txt

    This will run successfully and show the output of the process in the UtilDataProcessLogFile.txt file.

    This whole process involves, for example 10 devices and there is data collection for these 10 devices.

    The log file above will show me, how many rows are affected and time taken to do the process.

    What I want is to see along with the above information, in the above log file is, what are the devices the data was NOT collected for and why.

    Is this possible? if yes, how and where can i see some examples for such scenarios.
    I believe you would need to do that in the sproc, and output whatever message (via sql) that would indicate a device for which data was not collected for, whether it be via a left join, case etc.

  11. #11
    Join Date
    Sep 2009
    Posts
    8
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •