INSERT from SAS 9.2 to SQL Server 2005 fails when table has a trigger

Question

I connect to an SQL Server 2005 DB from SAS 9.2 via SAS/Access ODBC Driver. In the DB, I have a table my_table, which has an INSERT-trigger that copies all the inserted rows into another table in the database. In the SQL Server environment, the trigger works normally. However, SAS Proc SQL doesn't seem to get on well with a table with trigger. The following statement

proc sql ;
    INSERT INTO my_db.my_table
    SELECT a, b, c
    FROM my_db.my_test_table ;
quit ;

runs without complaint w/o the trigger, but when I enable the trigger, it hangs!

Could anyone shed a light to this?

Answer 1

Try to make sure that the triggers start with this line of code:

SET NOCOUNT ON 

This prevents the sql server from returning the number of records affected by an operation.

Answer 2

Did you try to send your query using "SQL passthrough" ?