Getting error message in sql server

Question

Getting error message in sql server

anjaliagarwal5

Ten Centuries

Points: 1267
More actions
July 22, 2015 at 12:30 pm

#310079

Hi All,
I need to pass the messages coming from sql server to the asp.net application so I have the following script
CREATE TABLE tbl_sample
(
[ID] INT,
[NAME] VARCHAR(10),
)
GO\
I am trying to insert values to this tables through a stored procedure below is my stored prcoedure
SET ANSI_WARNINGS ON
GO
alter procedure sp_test
(
@Id int,
@name varchar(10)
)
AS
INSERT INTO tbl_sample ([ID],[NAME]) VALUES (@Id,@name)
When I try to insert a statemnt in this table that is bigger than the width of the column, the statement just gets inserted without giving any error. below is the statement:
sp_test 1, 'Bob Jack Creasey'
I want to see the warning/error message saying "String or binary data would be truncated.
The statement has been terminated." I want to send that warning/error message back to my application so that the user know that they are inserting the value in the table that is bigger than the width of the column.
I also tried setting the SET ANSI_WARNINGS ON;
How can I achieve this? Any help will be greatly appreciated.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

anjaliagarwal5 (7/22/2015)
Hi All,
I need to pass the messages coming from sql server to the asp.net application so I have the following script
CREATE TABLE tbl_sample
(
[ID] INT,
[NAME] VARCHAR(10),
)
GOI am trying to insert values to this tables through a stored procedure below is my stored prcoedure
SET ANSI_WARNINGS ON
GO
alter procedure sp_test
(
@Id int,
@name varchar(10)
)
AS
INSERT INTO tbl_sample ([ID],[NAME]) VALUES (@Id,@name)
When I try to insert a statemnt in this table that is bigger than the width of the column, the statement just gets inserted without giving any error. below is the statement:
sp_test 1, 'Bob Jack Creasey'
I want to see the warning/error message saying "String or binary data would be truncated.
The statement has been terminated." I want to send that warning/error message back to my application so that the user know that they are inserting the value in the table that is bigger than the width of the column.
I also tried setting the SET ANSI_WARNINGS ON;
How can I achieve this? Any help will be greatly appreciated.

This is one of those areas where SQL Server does a silent truncation of the data. If the column in the database can only handle up to 10 characters then the application should only accept up to 10 characters for that column.

anjaliagarwal5 Ten Centuries Points: 1267 More actions · Answer 2

Is there any way, I can see those messages in sql server.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

One way round this sort of problem is to declare your parameter as longer than the you want to allow, then test the length to see if it is going to be too long.

e.g.

alter procedure sp_test

(

@Id int,

@name varchar(20)

)

AS

if len(@name)>10 return 10 -- or whatever error number you want

INSERT INTO tbl_sample ([ID],[NAME]) VALUES (@Id,@name)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);