how to handle error Msg 8114 - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

how to handle error Msg 8114

Author

Topic

SPriya
Starting Member

12 Posts

Posted - 2008-05-06 : 10:18:29

Hi,

How to capture the Error Msg 8114 in sql server 2000.

Below is the sample code i have used.

CREATE TABLE Test
(
Column1 int IDENTITY,
Column2 int NOT NULL
)

USE tempdb
go
ALTER PROCEDURE CHK_INSERT
@Column2 int
AS
DECLARE @ExecQuery nVARCHAR(4000)
set @ExecQuery='INSERT test VALUES ('+cast(@Column2 as varchar)+')'
EXEC (@ExecQuery)
IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'
END

EXEC CHK_INSERT Test

Regs,
priya

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-06 : 11:32:50

select * from sys.sysmessages where error = 8114

you are passing a string to insert into a column of int datatype..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:34:52

Does that work on SQL Server 2000, as OP wrote?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:35:59

select * from master..sysmessages where error = 8114

E 12°55'05.25"
N 56°04'39.16"

SPriya
Starting Member

12 Posts

Posted - 2008-05-08 : 01:51:00

Thanks for your reply.

I have just simulated the code from our project, @@ERROR is not caputring this error. How to handle this error????

Regs,
Priya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 02:34:52

Do you have some ANSI settings that is changed from original settings?

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-08 : 13:50:09

Assuming you have both the table and the procedure in tempdb. It should work just fine (or at least it does for me). Is there more to this issue than you have shown us?

SPriya
Starting Member

12 Posts

Posted - 2008-05-09 : 02:03:20

Thanks for the reply.......

Ya its using temp db. r u getting error messgage 'Error Occured'
while executing the procedure.

I am getting the messgae only like this.......
Msg 8114, Level 16, State 5, Procedure CHK_INSERT, Line 0
Error converting data type nvarchar to int.

Is there any way to caputre the error whenever there is a data mismatch ???

Regs,
Priya

SPriya
Starting Member

12 Posts

Posted - 2008-05-09 : 02:04:45

quote:
Originally posted by Peso

Do you have some ANSI settings that is changed from original settings?

E 12°55'05.25"
N 56°04'39.16"

ANSI settings??? Sorry i have no idea :(

Regs,
priya

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-09 : 13:04:07

Sorry, I think I misunderstood what you were asking. As Raky already pointed out, the issue is that you are passing a string (VARCHAR) when an INT is expected. This is not en execution error rather a compilation error. So, @@ERROR cannot trap something that is never executed.

How are you even generating this sort of call? So you have some other bit of dynamic SQL that is causing this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources