1. 

   SteveD, Member

   When I try to save the following in the report editor I get the error message Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection and it wont save.

   IF object_id('sp_DBR_FindGRNLocation','P') IS NOT NULL DROP PROCEDURE [sp_DBR_FindGRNLocation] GO CREATE PROCEDURE sp_DBR_FindGRNLocation @iGRN int, @iItem int AS BEGIN /* EXEC ABS.dbo.qselAllGRNItemLocationsForLocationGridByGRNNoAndItemNo @iGRN,@iItem */ set ansi_nulls on set ansi_warnings on SELECT * INTO #MyTempTable FROM OPENROWSET( 'SQLNCLI','Server=localhost;TRUSTED_CONNECTION=YES', 'SET FMTONLY OFF; SET NOCOUNT ON; EXEC ABS.dbo.qselAllGRNItemLocationsForLocationGridByGRNNoAndItemNo 1234,0') SELECT * FROM #MyTempTable drop table #MyTempTable

   END GO

   If I move the set ansi_nulls and the set ansi_warnings statements to before the start of creating the procedure as shown below then the report editor lets me save the report OK, but when I attempt to run the report I get the same Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection error message.

   IF object_id('sp_DBR_FindGRNLocation','P') IS NOT NULL DROP PROCEDURE [sp_DBR_FindGRNLocation] set ansi_nulls on set ansi_warnings on GO CREATE PROCEDURE sp_DBR_FindGRNLocation @iGRN int, @iItem int AS BEGIN /* EXEC ABS.dbo.qselAllGRNItemLocationsForLocationGridByGRNNoAndItemNo @iGRN,@iItem */ SELECT * INTO #MyTempTable FROM OPENROWSET( 'SQLNCLI','Server=localhost;TRUSTED_CONNECTION=YES', 'SET FMTONLY OFF; SET NOCOUNT ON; EXEC ABS.dbo.qselAllGRNItemLocationsForLocationGridByGRNNoAndItemNo 1234,0') SELECT * FROM #MyTempTable drop table #MyTempTable

   END GO

   Even if I have the set ansi comments in both places (prior to the procedure create statement and after it) I still get the Heterogeneous error. Any advice of where else to put them? The database is SQL Server 2005 btw.

   Thanks
2. 

   myDBR Team, Key Master

   Was there a reason for using the OPENROWSET? You have commented out the direct call to the procedure?

   You can call the procedure from another procedure which sets the ANSI_NULLS and ANSI_WARNINGS on. You can see what your settings are by using the @@options variable.

   select 'ANSI_WARNINGS' as 'Setting', case when ( (8 & @@OPTIONS) = 8 ) then 'ON' else 'OFF' end as 'Value'
   
   union
   
   select 'ANSI_NULLS', case when ( (32 & @@OPTIONS) = 32 ) then 'ON' else 'OFF' end
   
   

   --
   myDBR Team
3. 

   SteveD, Member

   I need the results in a temporary table rather than just as is as I need to process them.

   I know that this is probably just myself being dense by the way, but your included code is pretty much meaningless to me - Where would my call to OPENROWSET go in connection to your example?
4. 

   myDBR Team, Key Master

   You can put the result of a procedure into a temporary table using "insert into #table exec proc". No need to use the OPENROWSET.

   --
   myDBR Team
5. 

   SteveD, Member

   Wouldn't I need to create and define the fields of the temp table in order to do that though? Using OPENROWSET does away with that need.

   Even if I use the "insert into #table exec proc" where does that fit in with your example
   select 'ANSI_WARNINGS' as 'Setting', case when ( (8 & @@OPTIONS) = 8 ) then 'ON' else 'OFF' end as 'Value' union select 'ANSI_NULLS', case when ( (32 & @@OPTIONS) = 32 ) then 'ON' else 'OFF' end
6. 

   myDBR Team, Key Master

   You have two options:

   1) Use the procedure directly and create the temp table first.
   2) Use OPENROWSET and call your procedure from another procedure which set the ANSI_NULLS and ANSI_WARNINGS on.

   We'd recommend the first option.

   --
   myDBR Team

Reply

You must log in to post.