ANSI_NULL errors after restoring database

[solomon...@live.com]

Hello,

This question is with regards to Solomon 6.5.

Every few months we "update" our demo database based on the production
database by making a backup of the production user database and
SIVSystem and then restore these in the demo SQL server environment.
For the first time we have a problem after doing this. The demo
environment now gives the following error in many screen ( PO,
Shipper, etc) :

" 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." It looks
like we get this error when a stored proc is called. This occurs even
if I set the ANSI NULL/WARNINGS to ON at start of stored proc and to
OFF at end of stored proc.

In other words this seems to be a generalised problem for us and we
don't know how to fix. We urgently need help with this as we have
someone who needs this environment this morning. Any suggestions are
greatly appreciated.

Thanks in advance.

[solomon...@live.com]

The problem has been solved. The problem was that triggers were added
to the production DB that inserted data into another DB using a linked
server. We deactivated these unnecessary triggers for our demo
environment using a script similar to the one below:

declare @TriggerName sysname, @TableName sysnamedeclare

cTr cursor local for
select tr.name as TriggerName, object_name(tr.parent_id) as TableName
from sys.triggers tr where tr.name like '%some_common_pattern%'

open cTrfetch next from cTr into @TriggerName, @TableNamewhile
@@fetch_status = 0
begin
exec ('alter table ' + @TableName + ' disable trigger ' +
@TriggerName)
fetch next from cTr into @TriggerName, @TableNameend
close cTr
deallocate cTr