Trace Faling sp_executesql

Question

Trace Faling sp_executesql

Boris G

SSC Enthusiast

Points: 152
More actions
October 27, 2010 at 10:33 am

#242253

Hi There,
I have Server that Has a lot of inserts going trough "sp_executesql" and some of them fail with
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint
Is there a way in Profile to filter those?
Thank You.

Viewing 6 posts - 1 through 5 (of 5 total)

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

AlexSQLForums SSChampion Points: 14277 More actions · Answer 1

Boris-615127 (10/27/2010)
Hi There,
I have Server that Has a lot of inserts going trough "sp_executesql" and some of them fail with
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint
Is there a way in Profile to filter those?
Thank You.

Yes. in Trace Properties click on column filters and select error click on + sign and enter 2627.

Alex S

Boris G SSC Enthusiast Points: 152 More actions · Answer 2

No that filtered out everything...

what is strange to me is that Error column on SP:StmtStarting is blank

and SQLBatch or top trace wrapper returns ''0 - OK" in Error column....

Any insight is appreciated...

Boris

Lowell SSC Guru Points: 323518 More actions · Answer 3

boris with Profiler running agaisnt my 2008 machine, i'm running this command as a crappy example into a table, which i know will produce a pk violation:

CREATE TABLE [dbo].[MYADDRESS] (

[EMPNO] INT NOT NULL,

[ENAME] VARCHAR(100) NULL,

[ADDR1] VARCHAR(100) NULL,

[ADDR2] VARCHAR(100) NULL,

[CITY] VARCHAR(100) NULL,

[STATECODE] VARCHAR(2) NULL,

[ZIPCODE] VARCHAR(100) NULL,

[PHONE] VARCHAR(20) NULL,

[MOREDATA] VARCHAR(100) NULL,

CONSTRAINT [PK__MYADDRESS] PRIMARY KEY CLUSTERED (EMPNO))

INSERT INTO [MYADDRESS] ([EMPNO],[ENAME],[ADDR1],[ADDR2],[CITY],[STATECODE],[ZIPCODE],[PHONE],[MOREDATA])VALUES(1,'Calvin','123 G.R.O.S.S. Clubhouse','','Miami','NY','33024','555-1212','likes snowmen')

INSERT INTO [MYADDRESS] ([EMPNO],[ENAME],[ADDR1],[ADDR2],[CITY],[STATECODE],[ZIPCODE],[PHONE],[MOREDATA])VALUES(2,'Hobbes','123 My Imagination St','','Miami','NY','33024','555-1222','likes to tease calvin')

exec sp_executesql N' insert into [MYADDRESS](EMPNO,ENAME,ADDR1,ADDR2,CITY,STATECODE,ZIPCODE,PHONE,MOREDATA)

SELECT 1,''Calvin'',''123 G.R.O.S.S. Clubhouse'','''',''Miami'',''NY'',''33024'',''555-1212'',''likes snowmen''

'

I certainly see the sp_executesql code being executed in

SQL:BatchStarting and SQL:BatchCompleted, if you include those events, don't you see the same thing i am seeing?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Boris G SSC Enthusiast Points: 152 More actions · Answer 4

Well i see the tSQL as well but i am trying to figure out with one of thousands of sp_executesql in my trace has "Violation of PRIMARY KEY constraint", and filtering on error = 2627 doesn't filter anything....

Thank You

Boris

Boris G SSC Enthusiast Points: 152 More actions · Answer 5

Lowell thank you so much for Second Screen Shot...

that is fantastic, this gives me enough info to trace...

Live and Learn...

Boris