How can I implement error handling within a SQL script that us

Hi Hugo,
I was thinking in similar lines as the solution you gave (not the one
with severity 20.. I would have never come up with such a solution :)
But I think there is a problem in the other one. We are talking about
different batches. and these batches will be creating database objects.
Can you give an example on how will you implement the error handling with
temp tables for this scenario?
1. Create a view on some existing table first (select *)
2. If the view creation is successful,then create another view on this view.On Thu, 1 Jun 2006 22:35:01 -0700, Omnibuzz wrote:
(snip)
>Can you give an example on how will you implement the error handling with
>temp tables for this scenario?
>1. Create a view on some existing table first (select *)
>2. If the view creation is successful,then create another view on this view.[/color
]
Hi Omnibuzz,
There's one thing I hadn't thought about - CREATE VIEW must be the only
in a batch, so you'll have to use dynamic SQL to make it conditional.
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
EXEC ('CREATE VIEW v1
AS
SELECT 1 AS a')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
EXEC ('CREATE VIEW v2
AS
SELECT a
FROM v1')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
go
Hugo Kornelis, SQL Server MVP

How can i get a return code of 1 for an osql command which has a lower severity..

I am running the following OSQL command and capturing the return code
for the error .Whenver i have an error like server not exists or uable
to login I get a return code of 1 for the %ERRORLEVEL%.However
whenever I have an errorof a wrong dbcompatibility error the retun
code id 1 even though sql returns an iformation message from OSQL that
the right copatibilty levels are 60,70 and 80.How can i get OSQL to
return the right return code whenver a error of this type occurrs from
batch mode sql.The OSQL i am running from the batch is

osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
-Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
ECHO %errorlevel% >> %logbatch%
IF %ERRORLEVEL% NEQ 0 Goto SQLError

sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80

Thanks in anticipation.

Ajay[posted and mailed, please reply in news]

Ajay Garg (ajayz90@.hotmail.com) writes:
> I am running the following OSQL command and capturing the return code
> for the error .Whenver i have an error like server not exists or uable
> to login I get a return code of 1 for the %ERRORLEVEL%.However
> whenever I have an errorof a wrong dbcompatibility error the retun
> code id 1 even though sql returns an iformation message from OSQL that
> the right copatibilty levels are 60,70 and 80.How can i get OSQL to
> return the right return code whenver a error of this type occurrs from
> batch mode sql.The OSQL i am running from the batch is
> osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
> -Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
> ECHO %errorlevel% >> %logbatch%
> IF %ERRORLEVEL% NEQ 0 Goto SQLError
> sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80

Here is a simple example that illustrates:

E:\temp>osql -E -n -Q "EXIT (SELECT 47)"

----
47

(1 row affected)

E:\temp>echo %ERRORLEVEL%
47

E:\temp
By putting the entire SQL batch within EXIT(), OSQL will return the
value of the last result set to the command-line environment.

For details, see the topic on OSQL in Books Online.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402EF532A497Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Ajay Garg (ajayz90@.hotmail.com) writes:
> > I am running the following OSQL command and capturing the return code
> > for the error .Whenver i have an error like server not exists or uable
> > to login I get a return code of 1 for the %ERRORLEVEL%.However
> > whenever I have an errorof a wrong dbcompatibility error the retun
> > code id 1 even though sql returns an iformation message from OSQL that
> > the right copatibilty levels are 60,70 and 80.How can i get OSQL to
> > return the right return code whenver a error of this type occurrs from
> > batch mode sql.The OSQL i am running from the batch is
> > osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
> > -Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
> > ECHO %errorlevel% >> %logbatch%
> > IF %ERRORLEVEL% NEQ 0 Goto SQLError
> > sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80
> Here is a simple example that illustrates:
> E:\temp>osql -E -n -Q "EXIT (SELECT 47)"
> ----
> 47
> (1 row affected)
> E:\temp>echo %ERRORLEVEL%
> 47
> E:\temp>
> By putting the entire SQL batch within EXIT(), OSQL will return the
> value of the last result set to the command-line environment.
> For details, see the topic on OSQL in Books Online.

I noticed someting even more interesting.Even though the return code
was 0
when there was an error the job on sql server which actually failed
showed that it ran sucessfully.Even though when i run the job as an
Xp_cmdshell command on the sql server it shows that it failed what
could be the reason that it behaves that way?

Thanks in anticipation.

Ajay|||Ajay Garg (ajayz90@.hotmail.com) writes:
> I noticed someting even more interesting.Even though the return code
> was 0
> when there was an error the job on sql server which actually failed
> showed that it ran sucessfully.Even though when i run the job as an
> Xp_cmdshell command on the sql server it shows that it failed what
> could be the reason that it behaves that way?

I'm sorry, but I don't follow. Could you clarify with an example?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp