Executing SQL scripts via Ado.NET with the results like in SSMS

Question

I have one batch file with contents like:

@Echo Off    
goto skipcomments
rem  
:skipcomments

call setup-CIpatch-db.bat
if not "%UserName%"=="" goto ok

echo You must edit setup-CIpatch-db.bat to set variables.  
echo This script no longer takes command line arguments.
goto end
:ok


if exist *.out  del *.out

echo CoreIssue FleetOne Maintenance Release
echo working... DO NOT CLOSE
REM ************************************************
REM Script in which Print Statement has been removed
REM ************************************************


REM ************************************************
REM Create Scripts for Tables
REM ************************************************


REM ************************************************
REM Alter Scripts for Tables
REM ************************************************
osql -S %ServerName% -d %DBName% -U %sql_login% -P %sql_passwd% -n -i AlterTablecardBacktable07312012.sql -o AlterTablecardBacktable07312012.out





REM ************************************************
REM Data Updates
REM ************************************************

osql -S %ServerName% -d %DBName% -U %sql_login% -P %sql_passwd% -n -i AlterDrpandCrePrimKeyCardback.sql -o AlterDrpandCrePrimKeyCardback.out

REM ************************************************
REM Performance Updates
REM ************************************************




REM ************************************************
REM Security Update Scripts
REM ************************************************


REM ************************************************
REM Reports scripts
REM ************************************************



REM ************************************************
REM New or Altered Stored Procedures
REM ************************************************
osql -S %ServerName% -d %DBName% -U %sql_login% -P %sql_passwd% -n -i sp_RejectAccounts_qsel.sql -o sp_RejectAccounts_qsel.out
osql -S %ServerName% -d %DBName% -U %sql_login% -P %sql_passwd% -n -i sp_RejectAccounts_sel.sql -o sp_RejectAccounts_sel.out
osql -S %ServerName% -d %DBName% -U %sql_login% -P %sql_passwd% -n -i sp_GetCompanyRecordCardRequest_01Aug2012.sql -o sp_GetCompanyRecordCardRequest_01Aug2012.out
osql -S %ServerName% -d %DBName% -U %sql_login% -P %sql_passwd% -n -i SPGetCreditTransactions.sql -o SPGetCreditTransactions.out




REM ************************************************
REM Grant.sql for new or altered SP
REM ************************************************


REM ************************************************
REM Platform TranID Change Scripts
REM ************************************************


REM ************************************************
REM Version number update
REM ************************************************
osql -S %ServerName% -d %DBName% -U %sql_login% -P %sql_passwd% -n -i version.sql -o version.out


echo All Scripts have been started 
:end
pause

The script executes SQL files in the same folder and generates files with the extension of '.out'. These just contain the result; the same as when we execute any query in SSMS messages window, like 1 row affected etc. After the batch file has finished executing all scripts (via ADO.net/C#), we have to check the out files for keywords like "error" etc.

Now I have been given task to automate this and here is what I am doing:
Fetch the .SQL file name in tha batch file and execute them using SqlConnection, SqlCommand etc. The problem is that when the query has any error in it, an exception is thrown. What I need is that the query should execute whatsoever and I should be able to get the results. (like 1 row affected etc). Like in SSMS, when we execute any bad query, it executes and show the error in the Messages Pane.

Can anyone guide me ?

If the script errors how can you expect it to complete? Are you saying that if the first call to osql was to error you want the preceding scripts to still execute that is 2nd, 3rd, nth call to osql? Or are you saying that if a portion of script1.sql was to fail the rest of that script must continue to execute? — Chris Moutray
Still a little confused - so originally the batch file was being executed using C# (still don't understand the link to ADO.Net here) and now your automating the error part so you're no long executing the batch file but stripping out the sql scripts and executing one by one in your c# app? — Chris Moutray
When you execute the command ie SqlCommand.ExecuteNonQuery this will return the number of rows effected. - if theres an error you'll get a SqlException and you should rollback the transaction there 0 rows effected... — Chris Moutray
@ChrisMoutray Can you give me the procedure for executing it like SSMS ? — Akshay J

Chris Moutray Chris Moutray · Accepted Answer · 2012-08-24T06:27:20

Looking at the docs on MSDN, oSql supports 2 params -m and -r which should allow you to better handle error output.

Also as part of your batch script you can check the error-level something like this

NOTE: I've used a label to create a function to execute my script without repeating all the error code

REM run list of scripts
call :UpgradeDatabase AlterTablecardBacktable07312012.sql
call :UpgradeDatabase version.sql

goto :eof

:UpgradeDatabase

set upgradeScript=%~1

echo Running script %upgradeScript% on database %databaseName%...
sqlcmd -S %serverName% -d %databaseName% -U %userName% -P %password% -i %upgradeScript% -o %logPath%\%upgradeScript%.log
if %errorlevel% == 0 (
    echo Script %upgradeScript% on database %databaseName% successful
) ELSE (
    echo Script %upgradeScript% on database %databaseName% FAILED
    echo. > %logPath%\ERROR_%upgradeScript%.log
)
echo.
echo.

goto :eof

Executing SQL scripts via Ado.NET with the results like in SSMS

1 Answers