SQL Server Performance Forum – Threads Archive

Heterogeneous queries require the ANSI_NULLS

Hi, When try to insert DATA from ORACLE to MSSQL i got the Error: Insert into APPL_DOC_SERNO (APPLID,KPTNO,DOCTYPE,DOCSERNO,GSCBUPDFLAG,TIMESTAMP,BRANCHCODE,USERID,EXPIRYDATE) Select * From Openquery(GSC,’Select APPLID,KPTNO,DOCTYPE,DOCSERNO,GSCBUPDFLAG,TIMESTAMP,BRANCHCODE,USERID,EXPIRYDATE From APPL_DOC_SERNO’)
Server: Msg 7405, Level 16, State 1, Line 1
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. If u do like this i dont have any error Why: Insert into APPL_DOC_SERNO Select * From Openquery(GSC,’Select * From APPL_DOC_SERNO’)
Regards
Srini

Run "sp_dboption <your_database_name>, ‘warnings’" and if it’s OFF re-run the statement in this form: sp_dboption <your_database_name>, ‘warnings’, true"

By default, SQL Server sets ANSI_NULLS to OFF before running code from a SP. To overcome explicitly use ‘Set ANSI_NULLS ON’. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

if you are using isql in command mode add:
isql -Q "sp_dboption <dbname>,’warnings’,true; <rest of code>" …
to set the option

]]>