Query Analyzer works Enterprise Manager Doesn't 1

[awebb]

I don't understand why a trigger works fine when fired from a statement in the Query Analyzer, but when fired by making a change to the table directly in the EM I get ODBC errors, first 'SqlDumpExceptionHandler: Process x generated fatal exception'. Then 'Communication Link Failure'. Since the code works perfectly from QA, I'm assuming it's the EM connection - but is there any way to make the EM act like QA?

 

[tlbroadbent]

Do you have SET NOCOUNT ON at the start of the trigger? Chances are the trigger is returning data. QA can handle the returned result set. EM cannot. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[awebb]

Thanks for your response. I played around with SET NOCOUNT - unfortunately, to no avail. The trigger does 'fail' (at least in EM) immediately after the first 'Select' - even with SET NOCOUNT ON as the line before the Select. EM has the problem even when only change is a field which is not referenced or altered by the trigger.
Error sequence in EM is always the same.
1. Make change
2. SQL 'EXCEPTION ACCESS VIOLATION'
3. arrow/move off record - try again
4. SQL 'Communication Link failure'
5. escape - return value, go to another record, make change
6. SQL 'Cannot start more transactions on this session.'

I'm a rookie with SQL - is it 'not good practice' to enter/update through EM? It sure is (or I thought it was) easy. Thanks.

 

[tlbroadbent]

You indicate that there is a select statement in the trigger. Triggers should not have select statements that return result sets. If the trigger returns a result set, that is the reason EM errors. If you must return a result set in a trigger, then you shouldn't use EM to update.

If you have questions about the code in the trigger, please post the trigger so we can review it and answer your questions.

Thanks, Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[awebb]

THANK YOU!! The key was 'return result sets'. I didn't understand that the default behavior (if that's the right term) of SELECT is to return a result set. The trigger needed a count of rows, but didn't need to return the result set. I changed to select into a temp table and everything works great - EM and QA behave the same.

Here's the code that works. Thanks again.

ALTER TRIGGER Contact_Phone
ON Contact_Phone_T
FOR INSERT, UPDATE

AS

IF @@ROWCOUNT = 0 RETURN

DECLARE @Rcnt INTEGER

SET NOCOUNT ON

SELECT
I.Contact_Phone_ID
INTO #Chk
FROM INSERTED AS I
INNER JOIN DELETED AS D
ON I.Contact_Phone_ID = D.Contact_Phone_ID
WHERE
I.Contact_ID <> D.Contact_ID
OR
I.Phone_Type_ID <> D.Phone_Type_ID

SET @RCnt = @@ROWCOUNT

DROP TABLE #Chk

IF @RCnt > 1
BEGIN
RAISERROR ('Multiple record Inserts and Updates which affect Contact and/or Phone Type are not allowed. No action taken.',16,1)
ROLLBACK TRAN
RETURN
END

 

[tlbroadbent]

You can count the records in inserted without a temp table. Use the follwoing syntax.

Select @RCnt = count(*) From inserted Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[awebb]

Even better. Got so wrapped up in the differences between QA and EM, I guess I lost site of the obvious. Thanks again.