Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Passing error messages from stored procedure to osql command

    I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:

    IF @@error <> 0
    BEGIN
    ROLLBACK TRANSACTION
    RETURN

    If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.

    Questions:

    How can that be accomplished?

    How to restart the stored procedure ones the error has been corrected?

    Thank you for your help.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Use "/b" with your OSQL, and use RAISERROR in your error trapper. In the batch file check "ERRORLEVEL 1":

    osql ...... /b
    if errorlevel 1 goto blah-blah
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    Do you have a sample that I can look at? Thanks

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Watch out for word wrapping.
    This one runs DBCC INDEXDEFRAG on specified server + database.

    @echo off
    set server=%1
    set db=%2
    set uid=-U%3
    set pwd=-P%4
    if "%1"=="" goto ServerError
    if "%2"=="" set db=master
    if "%3"=="" set uid=-E
    if "%4"=="" set pwd=
    echo Checking for existence of a view on server %server% database %db%...
    osql -S %server% %uid% %pwd% -d %db% -l 1 -Q"if object_id('dbo.vw_DBCC_INDEX_DEFRAG') is not null drop view dbo.vw_DBCC_INDEX_DEFRAG" -b
    if errorlevel 1 goto LoginFailure
    echo Creating a view on server %server% database %db%...
    osql -S %server% %uid% %pwd% -d %db% -i"Create_vw_DBCC_INDEX_DEFRAG.SQL" -b
    if errorlevel 1 goto CreateViewError
    if "%uid%"=="-E" set uid=-T
    echo Generating the final script for server %server% database %db%...
    bcp %db%.dbo.vw_DBCC_INDEX_DEFRAG out %server%_%db%_DBCC_INDEX_DEFRAG.SQL -S %server% %uid% %pwd% -c
    if "%uid%"=="-T" set uid=-E
    echo Processing INDEXDEFRAG script on %server% database %db%...
    osql -S %server% %uid% %pwd% -d %db% -i %server%_%db%_DBCC_INDEX_DEFRAG.SQL -h-1 -n -w 256 -o %server%_%db%_DBCC_INDEX_DEFRAG.LOG -b
    if errorlevel 1 goto ScriptProcessingError
    echo Check %server%_%db%_DBCC_INDEX_DEFRAG.LOG for any errors!
    goto end
    erverError
    echo No server and/or database specified!
    echo Execution returned Error Code %ERRORLEVEL%
    goto end
    :LoginFailure
    echo Failed to login to %server%!
    echo Execution returned Error Code %ERRORLEVEL%
    goto end
    :CreateViewError
    echo Failed to create vw_DBCC_INDEX_DEFRAG!
    echo Execution returned Error Code %ERRORLEVEL%
    goto end
    criptProcessingError
    echo Failed to process the script: %server%_%db%_DBCC_INDEX_DEFRAG.SQL
    echo Execution returned Error Code %ERRORLEVEL%
    goto end
    :end
    @echo on
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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