Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-15 : 08:50:25
|
| BILLY writes "I'm tring to do a simple INSERT, UPDATE trigger that runs on server 1 and inserts new or updated records to a duplicate table on server 2. When the trigger fires I get the following error: Native Error: 7405Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.: First PassI've checked the ANSI_NULLS and ANSI_WARNINGS on the database properties connection tab but still get the error. I've SET ANSI_NULLS, ANSI_WARNINGS, ON and OFF in the trigger code but still get the same error. Please help. I'm totally stuck. Thank you in advance." |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-02-15 : 11:02:43
|
| Try this - 1. Copy the entire trigger statement (CREATE TRIGGER ...) onto the clipboard2. Paste it into Query Analyzer3. Delete the original trigger4. Run the Query Analyzer to recreate the triggerNot sure why it works, but it does for stored procedures. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-15 : 12:00:40
|
According to Ken Henderson'sThe Guru's Guide to SQL Server Stored Procedures, XML, and HTML  ...ANSI settings only affect a stored procedure at the time it is created; changing the settings within the procedure has no effect. I'm pretty sure the same would apply for a trigger. That's why Scott's solution works: you're creating the trigger with the proper ANSI settings. I had the same problem you did, unfortunately I bought Ken's book after I needed to know this little fact! |
|
|
|
|
|
|
Distributed Queries -- Error 7405