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:DocumentationProjectsIntegration ServicesBIDS ProjectsTesco DNF
Integration ServicesTescoDNF ProductPromoSQL CodeOBJECTSCreate DB
TescoDNF_SSISPackageManager.sql'

SET @ExecCommand = 'xp_cmdshell ''sqlcmd -S RgalbraithSQL2005_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]