I'm testing an application that is returning a very generic "Duplicte key has
already been added" message to the end-user.

Is it possible to capture errors that are returned by the dbms to the end
user using the profiler or some ohter tool? If so, what is t he best way to
go about this?

tia
Mike

Hi Mike

In the errors and warning events there is an exception event which should
capture this error, although you can not rely on the message to give you the
details of the context. This event can also be captured by a notification
event, but these methods will not give you what has cause this, you can
track it down the statement that caused it in profiler by using other
events.

USE tempdb
GO

CREATE QUEUE NotifyQueue ;
GO
CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
CREATE ROUTE NotifyRoute
WITH SERVICE_NAME = 'NotifyService',
ADDRESS = 'LOCAL';
GO

CREATE EVENT NOTIFICATION ExceptionNotification
ON SERVER
FOR EXCEPTION
TO SERVICE 'NotifyService', 'current database' ;

CREATE TABLE T1 ( id int not null default 1 CONSTRAINT PK_T1 PRIMARY KEY )

INSERT INTO T1 DEFAULT VALUES
INSERT INTO T1 DEFAULT VALUES
-- Error: 2627, Severity: 14, State: 1

SELECT cast( message_body as xml ),*
FROM NotifyQueue

<EVENT_INSTANCE>
  <EventType>EXCEPTION</EventType>
  <PostTime>2009-06-27T00:15:43.717</PostTime>
  <SPID>53</SPID>
  <TextData>Error: 2627, Severity: 14, State: 1</TextData>
  <DatabaseID>2</DatabaseID>
  <TransactionID>34732</TransactionID>
  <NTUserName>User</NTUserName>
  <NTDomainName>MyPC</NTDomainName>
  <HostName>MyPC</HostName>
  <ClientProcessID>1668</ClientProcessID>
  <ApplicationName>Microsoft SQL Server Management Studio -
Query</ApplicationName>
  <LoginName>MyPC\User</LoginName>
  <StartTime>2009-06-27T00:15:43.717</StartTime>
  <Severity>14</Severity>
  <ServerName>MyPC</ServerName>
  <State>1</State>
  <Error>2627</Error>
  <DatabaseName>tempdb</DatabaseName>
  <LoginSid>AAAAAAAAAAAAAAAAoLiZDeuAVAAAU1d06AMAAA==</LoginSid>
  <RequestID>0</RequestID>
  <XactSequence>227633266714</XactSequence>
  <EventSequence>960</EventSequence>
  <IsSystem />
  <SessionLoginName>MyPC\User</SessionLoginName>
  <GroupID>2</GroupID>
</EVENT_INSTANCE>

SELECT TOP 1
  cast( message_body as xml ).value ('(/EVENT_INSTANCE/Error)[1]', 'int' ),
  cast( message_body as xml ).value ('(/EVENT_INSTANCE/TextData)[1]',
'varchar(510)' )
FROM NotifyQueue
ORDER BY queuing_order desc

--DROP EVENT NOTIFICATION ExceptionNotification ON SERVER;
--DROP ROUTE NotifyRoute;
--DROP SERVICE NotifyService;
--DROP QUEUE NotifyQueue ;

John

I'd check if the app has an option to dump out its trapped error verbatim to
a log file. Using Profiler seems to be approaching it in a wrong direction.

Linchi

Show quoteHide quote

Server2000 Std Edition Timeouts
stats_ver_current?
Unable to access the tape for database backup purpose
share Template location for SSMS
dbcc checkdb fix
Access 2007 Import Wizard
Modifying table issue
Securing MSSQL
Missing Index Details
Error with no reference