Error reported How to determime object?

Question

Post reply

Error reported How to determime object?

suvorav

Ten Centuries

Points: 1085
More actions
March 3, 2015 at 3:02 am

#295683

I run XEvent session with Error_repoted event
I also add sql_text ection
Now I can see error "String or binary data would be truncated."
but cannot determine in what SP does it happen

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sunder.bugatha Ten Centuries Points: 1227 More actions · Answer 1

are u running any sql job or SSIS package or stored proc?

suvorav Ten Centuries Points: 1085 More actions · Answer 2

yes

server is under workload

a lot of stored procedures are running

I would like to know in what SP this error exists

EMarkM Ten Centuries Points: 1114 More actions · Answer 3

Ah, good old "string or binary data" - the annoying thing is that SQL must know which field and input clash caused the error, but it doesn't seem to want to tell us.

Without going out of your way to design some error-trapping, I can start you off with a couple of choices:-

1. Run just the SELECT statements from your sprocs, adding some WHERE clauses for where data is of length greater than the intended destination

2. Switch SQL Profiler on and configure it to trap and show the error

Note that #2 can be very server-intensive unless set up correctly - probably best to carry out either of these tests on a non-production copy of the data until you know what you're doing.

suvorav Ten Centuries Points: 1085 More actions · Answer 4

EMarkM (3/3/2015)
Ah, good old "string or binary data" - the annoying thing is that SQL must know which field and input clash caused the error, but it doesn't seem to want to tell us.
Without going out of your way to design some error-trapping, I can start you off with a couple of choices:-
1. Run just the SELECT statements from your sprocs, adding some WHERE clauses for where data is of length greater than the intended destination
2. Switch SQL Profiler on and configure it to trap and show the error
Note that #2 can be very server-intensive unless set up correctly - probably best to carry out either of these tests on a non-production copy of the data until you know what you're doing.

I am using XEvent session instead profiler

but I cannot realize how to configure the session to find out what SP case this error

CREATE EVENT SESSION [Errors] ON SERVER

ADD EVENT sqlserver.error_reported(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)

WHERE ([severity]>(10)))

ADD TARGET package0.event_file(SET filename=N'Errors.xel',max_file_size=(30),max_rollover_files=(4))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=10 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

GO

EMarkM Ten Centuries Points: 1114 More actions · Answer 5

suvorav (3/4/2015)
I am using XEvent session instead profiler
but I cannot realize how to configure the session to find out what SP case this error

Hmmm, not a method with which I am familiar, I have to confess.

Anyone else jump in on that one?

Meanwhile, Suvorav, no chance you could look into the other ideas I posted - in particular the first one - isolating the SELECT queries and running them independently to see where a clash may lie?

suvorav Ten Centuries Points: 1085 More actions · Answer 6

Meanwhile, Suvorav, no chance you could look into the other ideas I posted - in particular the first one - isolating the SELECT queries and running them independently to see where a clash may lie?

This is really good idea I think I have to go this way

but I thought that more easy option exists

it a pity if XEvent so powerful tool but unable to show object where is the error

stewart campbell SSC Veteran Points: 207 More actions · Answer 7

Have a look at this blog post - http://www.brentozar.com/archive/2013/08/what-queries-are-failing-in-my-sql-server/ - it shows how you can query the extended event data to retrieve the sql_text causing the error.