I am trying to access tables from d

[princessk1]

I am trying to access tables from different databases and I am getting the following error...

Error 7405: Heterogenous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection.

I have gone into the control panel and made sure the options were selected for that ODBC connection -- this didn't do anything. I have also tried SET ANSI_NULLS ON in the code and this also does nothing.

I have accessed this table in another stored procedure using the same code and do not get the error there. I have no idea why it gives me the error in my new stored procedure. Any ideas???

This is the piece of the code that causes the error:

DECLARE store_cursor CURSOR FOR SELECT StoreID,StoreNo FROM LWIDWDRV.LWISales.dbo.Store WHERE EndDate = '1900-01-01'

 

[jhall156]

Set ANSI_NULLS ON will work but you have to end the batch first by telling it to GO. The connection ansi settings will not change otherwise.

At the very top of your script/stored procedure
SET ANSI_NULLS ON
GO

JHall