Deployment 'utility' script using sqlcmd

Hi all,

I'm looking at creating a sample utility script that will invoking

scripts to deploy some SQL code. For example, a utlity script that will

run a SQL script, and on successful completion, execute the next script.

Having not used SQLCMD at all before, and being very new to SQL2005

(< 1 month) please guide me if there is a better way of invoking

this... For example, a way of avoiding the xp_cmdshell invocation!

The following code invokes a script, but I'm trying to find a way of

getting a return code back from sqlcmd, so I can progress and do the

next, or fail if the return code <> 0 (success).

[code]

--Process to create DB, Tables, and Stored Procedures

set nocount on

DECLARE

@.Error int,

@.ExecCommand varchar(512),

@.FullFilePath varchar(255)

--create the database

BEGIN TRY

SET @.FullFilePath =

'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF

Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB

TescoDNF_SSISPackageManager.sql'

SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '

SELECT @.FullFilePath

UNION

SELECT @.ExecCommand

EXEC (@.ExecCommand)

SELECT @.@.ERROR

SELECT @.Error

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

GOTO ErrorAbort

END CATCH
ErrorAbort:

[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set

of SQL scripts to, for example, a server. For example, some pseudo-code

Create Database

If Error abort

Create Table1

If Error abort

Create Table2

If Error Abort

Create Stored Procedure1

If Error Abort

ELSE Complete and report success

I do agree that this is something that could be (better) done in a

"proper" coding language like .Net, c# etc. but (a) it's just a simple

utility script (b) it teaches me moore about usage of SQLCMD and (c) I

do not have any skill in any normal programming language, hence I was

planning to write a quick deployment utility with a script.

The idea might be something as ugly as a table structure that has a

list of scripts registered in it, with some sequence logic - like

creating parent tables before children tables - and then a cursor (or a

better method if I can find it) that fetches a sqlcmd filename

execution command, executes it, and on success fetches the next one

based on the sequence logic.

I can probably do all of that in about 4 hours in T-SQL, if I can find

a way to confirm the successful execution of the previous command....|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files

and execute them, and not merge them all into a single large script.

So, I want utility script to do this:

Run external sql script

On failure abort, on Success

Run external sql script

On Failure abort, On Success

...

You being to see why I referred to a cursor?

The point is that the utility script wouldn't contain any of the client

SQL commands - it would fetch them by referring to the table, and

fetching the path to the SQL file, and building a SQLCMD to execute

that script

I guess, as you say, I could add a generic update ##SQLScriptTracker

table, then check it on the new execution, or abort. I had hoped for a

neater solution - i.e. SQLCMD being able to return a returncode that it

gets from a SQL file it ran....|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as

well, since with the batch file I have to add a file each time.

Thanks|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).