|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQLCMD error -100I'm trying to execute a script but it will not execute the following
combination: IF EXISTS (SELECT * FROM master.sys.databases WHERE [name] = @Database) :Exit(SELECT 10;) I can have anything else, but never a statement that begins with [:].I've already tried installing MSSQL 2005 SP1 with no success. I've also tried changing the IF statement so that does a goto instead to jump over the :Exit statement, but that failed to. Any ideas on what's wrong. (harris***@yahoo.com.au) writes:
> I'm trying to execute a script but it will not execute the following You mixing apples and oranges here. IF EXISTS is T-SQL, but :EXIT> combination: > > IF EXISTS (SELECT * FROM master.sys.databases WHERE [name] = @Database) > :Exit(SELECT 10;) > > I can have anything else, but never a statement that begins with [:]. > I've already tried installing MSSQL 2005 SP1 with no success. I've > also tried changing the IF statement so that does a goto instead to > jump over the :Exit statement, but that failed to. is a command specific to SQLCMD, so it never gets to SQL server. There may be more one way to do this, but you can use RAISERROR with state = 127 to terminate SQLCMD or OSQL. (Note that this does not have the same effect in Mgmt Studio or Query Analyzer.) For instance: RAISERROR('Taking the easy way out', 16, 127) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > RAISERROR('Taking the easy way out', 16, 127) By doing this, how are you meant to raise a DOS ErrorLevel?Sqlcmd has command line options available to set the O/S ErrorLevel to a
non-zero value when your script exits due to an error. Look up -b, -V, etc. options for sqlcmd at http://msdn2.microsoft.com/en-us/library/ms162773.aspx. RAISERROR raises a T-SQL error which, when the command line options are set correctly, will signal sqlcmd to set ErrorLevel to the appropriate value. <harris***@yahoo.com.au> wrote in message Show quote news:1150681764.234916.215380@c74g2000cwc.googlegroups.com... >> RAISERROR('Taking the easy way out', 16, 127) > > By doing this, how are you meant to raise a DOS ErrorLevel? > >From what I understand, the -b parameter option only works when you use predicament where SQLCMD is unable to use the extended [:] commands:Exit(), not RAISERROR(). So then I get back to my original seemlessly within an SQL script. What I'm trying to achieve is a script for creating and installing a database with error handling. I don't want the script to blow up when it should be a simple test to avoid. I have managed to get around the issue currently, but not very elegantly by creating a cmd file which only runs on Win2k and XP unfortunately. The other problem now is it takes about another 6 sql script files to accomplish the task so that I can do error checking along the way. It would be nice if there was a more graceful solution. Mike C# wrote: Show quote > Sqlcmd has command line options available to set the O/S ErrorLevel to a > non-zero value when your script exits due to an error. Look up -b, -V, etc. > options for sqlcmd at > http://msdn2.microsoft.com/en-us/library/ms162773.aspx. RAISERROR raises a > T-SQL error which, when the command line options are set correctly, will > signal sqlcmd to set ErrorLevel to the appropriate value. Two files:
TEST.SQL consists of one line: RAISERROR(50001, 17, 127) TEST.BAT consists of two lines: sqlcmd -i test.sql -b echo %ERRORLEVEL% It returns an ErrorLevel of '1'. Try it and see what it returns on your system. <harris***@yahoo.com.au> wrote in message Show quote news:1150858564.834252.82990@y41g2000cwy.googlegroups.com... > >From what I understand, the -b parameter option only works when you use > :Exit(), not RAISERROR(). So then I get back to my original > predicament where SQLCMD is unable to use the extended [:] commands > seemlessly within an SQL script. What I'm trying to achieve is a > script for creating and installing a database with error handling. I > don't want the script to blow up when it should be a simple test to > avoid. > > I have managed to get around the issue currently, but not very > elegantly by creating a cmd file which only runs on Win2k and XP > unfortunately. The other problem now is it takes about another 6 sql > script files to accomplish the task so that I can do error checking > along the way. It would be nice if there was a more graceful solution. > > Mike C# wrote: >> Sqlcmd has command line options available to set the O/S ErrorLevel to a >> non-zero value when your script exits due to an error. Look up -b, -V, >> etc. >> options for sqlcmd at >> http://msdn2.microsoft.com/en-us/library/ms162773.aspx. RAISERROR raises >> a >> T-SQL error which, when the command line options are set correctly, will >> signal sqlcmd to set ErrorLevel to the appropriate value. > That does work for me too but I'm not to sure why I decided that was no
good. I think it was because I was after errorlevels other than 1 so that I can tell within an install script roughly where the error occurred to take any necessary actions. Also if there is a critical SQL error, it always returns 1, so then you have no idea where the error came from. That maybe acceptable inhouse, but no good for general comsumer installation. Mike C# wrote: Show quote > Two files: > > TEST.SQL consists of one line: > > RAISERROR(50001, 17, 127) > > TEST.BAT consists of two lines: > > sqlcmd -i test.sql -b > echo %ERRORLEVEL% > > It returns an ErrorLevel of '1'. Try it and see what it returns on your > system. Which brings you back to :EXIT (SELECT ...), which you're having trouble
getting to work right? :( <harris***@yahoo.com.au> wrote in message Show quote news:1151020883.276149.238270@c74g2000cwc.googlegroups.com... > That does work for me too but I'm not to sure why I decided that was no > good. I think it was because I was after errorlevels other than 1 so > that I can tell within an install script roughly where the error > occurred to take any necessary actions. Also if there is a critical > SQL error, it always returns 1, so then you have no idea where the > error came from. That maybe acceptable inhouse, but no good for > general comsumer installation. > > Mike C# wrote: >> Two files: >> >> TEST.SQL consists of one line: >> >> RAISERROR(50001, 17, 127) >> >> TEST.BAT consists of two lines: >> >> sqlcmd -i test.sql -b >> echo %ERRORLEVEL% >> >> It returns an ErrorLevel of '1'. Try it and see what it returns on your >> system. > (harris***@yahoo.com.au) writes:
>>From what I understand, the -b parameter option only works when you use I think that even with the additions in SQL 2005, SQLCMD is a poor>:Exit(), not RAISERROR(). So then I get back to my original > predicament where SQLCMD is unable to use the extended [:] commands > seemlessly within an SQL script. What I'm trying to achieve is a > script for creating and installing a database with error handling. I > don't want the script to blow up when it should be a simple test to > avoid. tool for the task. Better to have a control program in a client langauge such as VBscript, Perl or whatever your favourite may be. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics