SET ANSI_NULLS error???

[princessk1]

I am trying to access tables from differnet databases in a stored procedure and I am getting error 7405:

Heterogenous queries require the ANSI_NULLS & ANSI_WARNINGS options to be set for the connection.

I have tried going into the control panel under the ODBC connection and selecting the options there but I still get the error. I have also tried setting them in the code:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

This also does not work.
Any ideas????

The line in my code that is causing the error is:

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

If I take out the .dbo the error goes away but I get an invalid database error when I try to run the stored procedure.
Thanks.

 

[JayKusch]

you are on the right tack ....

you need to get back in your store procedure (sp) and enter the two lines of code before the main "Create Procedure" text as such ...


SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

CREATE PROCEDURE 'YourProcedureName AS
..... your code
..... go


when you save the sp, it enters those lines of code even though you will not see them when you open up the sp again.

hope this helps

 

[ctwilliams]

Please help! I am having this same problem. My stored procedure is accessing tables from 2 different (linked) servers (one server has SQL 2000, the other has SQL 7.0).

The procedure works great when I run it from within Query Analyzer, but when I "Verify Database" from within Crystal Reports it says "Heterogenous queries require the ANSI_NULLS & ANSI_WARNINGS options to be set for the connection."

I did like the post above said -- I set ANSI_NULLS and ANSI_WARNINGS to ON before creating the procedure. But then I got this error message: "Incorrect syntax near keyword 'Proc'"

 

[JayKusch]

try w/ a go in between ... let me know if that flies for you!

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

GO

CREATE PROCEDURE 'YourProcedureName AS
..... your code
..... go

Thanks

J. Kusch

 

[ctwilliams]

Thanks, but no dice. I already had a GO in between.

I have also tried setting ANSI_NULLS and ANSI_WARNINGS both outside (before) the CREATE PROCEDURE statement and inside, but I still have the same problem. Do you think it's because the two servers are running different versions of SQL Server? I'm also having this problem when trying to implement heterogeneous triggers.

 

[adam0101]

I'm having the same problem with linked servers, except I'm using views instead of procedures. I tried putting the
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
above the CREATE VIEW. I can get a result set just fine in Query Analyzer, but I get the error when I try to add the view to Crystal Reports. Any ideas are welcome. Adam

http://www.adventex.com

 

[alfredp77]

Try to set the default connection options for the server through enterprise manager (right click on the server, properties, connections, and you can see a lot of checkboxes, two of them are ANSI WARNING and ANSI NULLS). This works for me.

Specifying SET ANSI NULLS and SET ANSI WARNINGS ON for the storproc won't help since it will detect the connection settings and not the storproc settings.
And somehow the options in ODBC setup in control panel does not affect these 2 options.

Hope this information helps.

 

[ctwilliams]

This worked for me:

From within Query Analyzer, I ran the following to create the stored procedure...

SET ANSI_NULLS ON
GO

CREATE PROCEDURE MyRemoteProc AS

SET ANSI_WARNINGS ON

DECLARE @SQL nvarchar(500)
SET @SQL = 'SELECT * FROM [RemoteServer].DB1.dbo.Table1'
EXEC sp_executesql @SQL

GO