Stored procedure returning meaningful error message

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procedure returning meaningful error message

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-06 : 08:05:49

Jjoseph writes "Windows Version Windows-2000
SqlServer : SQL Server-2000

I am a relative newcomer to SQL Server ( Many years using Oracle)

I am developing a simple 'SQL Query Manager' . Here the user types in an SQL Query in a VB .net front end. This query is passed into an SQL Server Stored procedure.

All works okey as long as the SQL executes without error.
But once there is an error I cannot devise any way of getting the actual error message back to the client.

My Procedure (Simplified)
<<<<<<<<<<<
PROCEDURE DynamicSql
@SqlStr as VARCHAR(100),
@Ecode as int OUTPUT,
@Emsg as varchar(100) OUTPUT
AS
exec (@SqlStr)
set @ecode=@@Error
if @Ecode >0
select @Emsg=description
from [master].[dbo].sysmessages
where error=@ecode

return 0
>>>>>>>>>>>
I am Calling it from VB.net With Logic similar to below
<<<<<<<<<<<<<
declare
@ECode int,@Emsg varchar(300),@SqlStr varchar(400)

set @SqlStr='select junk'

exec dbo.dynamicsql @SqlStr,@Ecode OUTPUT,@Emsg OUTPUT

if @Ecode>0
print 'Procedure Returned Error = ' + @Emsg
else
print 'Procedure Finished Okey'

>>>>>>>>>>>>>>>
The Following is output
<<<<<<<<<
Invalid column name 'junk'.
Procedure Returned Error = Invalid column name '%.*ls'.
>>>>>>>>>>>

-ie the actual error message returned in @Ermsg variable is
Invalid column name '%.*ls'. whereas I would like to be able to
return Invalid column name 'junk'.

Thanks"

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-06 : 09:39:53

Ummm..