@@Error not catching error.

Hi all,

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.

Say suppose i have statment like

select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'

here rowid is integer value so i am getting above mention error.

So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.

thanks in advance.(trialproduct2004@.yahoo.com) writes:

Quote:

Originally Posted by

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.
>
Say suppose i have statment like
>
select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'
>
here rowid is integer value so i am getting above mention error.
>
So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.

If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(trialproduct2004@.yahoo.com) writes:

Quote:

Originally Posted by

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.
>
Say suppose i have statment like
>
select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'
>
here rowid is integer value so i am getting above mention error.
>
So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.

If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

.NET FW SDK QuickStart SQL-Express Error: Msg 102 ... Incorrect syntax.

Hi,

I have followed the steps from the .NET FW SDK QuickStart Configuration (C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Samples\Setup\html\ConfigDetails.htm), and have got an error message trying to grant the ASP.NET User account access to the databases by the command sqlcmd -E -S (local)\SQLExpress -Q "sp_grantlogin LEONEW\ASPNET" where LEONEW is my local machine name, and the ASPNET account exists in the Users group.

I am getting the error message: Msg 102, Level 15, State 1, Server LEONEW\SQLEXPRESS, Line 1
Incorrect syntax near '\'.

Please help if you know how to resolve this. Thanks in advance.

Alex.

Did you try putting apostrophes around the login name:

'LEONEW\ASPNET'

|||I have tried with no success sqlcmd -E -S (local)\SQLExpress -Q "sp_grantlogin N' LEONEW\ASPNET'".|||

Do you get the same error?

What about

sqlcmd -E -S (local)\SQLExpress -Q "sp_grantlogin 'LEONEW\ASPNET'"

If I use it without the apostrophes I get the same error as you:

Msg 102, Level 15, State 1, Server <...>, Line 1
Incorrect syntax near '\'.

If I add the apostrophes the command succeeds.

|||You are absolutely right. Thanks.

(sub)case syntax Question

Hi,

How can i perform a subcase ? I need to do a subcase because when a choose one condition (cdu_integrador='1'), there is another variable that could influence the result of cdu_parcerias (tipoterceiro ='TP' or not).

I've tried 2 ways , one using "AND" on the WHEN line (when 1 and terceiro=....) and use cascading case (when 1 (case tipoterceiro when 'TP'..)..) ... both ways didnt worked...

i get a

Incorrect syntax near the keyword 'CASE'.

ANY IDEA ?

CDU_PARCERIAS =

CASE CDU_INTEGRADOR

WHEN '1' AND TIPOTERCEIRO<>'TP' THEN TIPOTERCEIRO+' + TP'

ELSE TIPOTERCEIRO

END

CDU_PARCERIAS =

CASE CDU_INTEGRADOR

WHEN '1'

CASE TIPOTERCEIRO

WHEN 'TP' THEN TIPOTERCEIRO

ELSE TIPOTERCEIRO+' + TP'

END

ELSE TIPOTERCEIRO

END

Try it like:

CDU_PARCERIAS =
CASE WHEN CDU_INTEGRADOR = '1'
AND TIPOTERCEIRO <> 'TP'
THEN TIPOTERCEIRO + ' + TP'
ELSE TIPOTERCEIRO
END

|||

Use as follow as,

CDU_PARCERIAS =

CASE CDU_INTEGRADOR

WHEN '1' THEN

CASE TIPOTERCEIRO

WHEN 'TP' THEN TIPOTERCEIRO

ELSE TIPOTERCEIRO +' + TP'

END

ELSE TIPOTERCEIRO

END