Heterogeneous queries require ANSI_NULLS and warnings be set

[mebenz]

I have a stored proc that contains a query to a linked SQL server. I can execute the stored proc in query analyzer without error and get all the results. However, when accessing the report with Crystal Reports, which accesses the server and sp via ODBC, I receive the error "Heterogeneous queries require ANSI_NULLS and warnings be set for the connection."

I have added the set ANSI_NULLS and WARNINGS to ON within the stored proc, but Crystal (version 9) still give me an error, so it seems to be something in Crystal or the ODBC Connection itself. If I mark the ansi settings on the ODBC, I get more errors.

 

[beltmanjr]

I found useful information in:

http://www.mcse.ms/archive/index.php/t-549409.html

And kind of conclude that before you create your procedures you need to use

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.

and that in your ODBC settings you need to enable

Use ANSI nulls, paddings and warnings

Although it reads from your post that you have done both (except perhaps set these options before you created the procedure) I suggest you double check your ODBC settings.

Also please display the additional errors you got.

 

[mebenz]

I have these settings before the CREATE proc.

When I check the "use ANSI nulls, paddings and warnings" on my ODBC setup I get a new error "Login failed for user '(null)'. Reason. NOt associated with a trusted sql server connection."

Only reports that use this sp get this error. All other reports, which use the same ODBC, work fine.

The sp runs fine in query analyzer.

 

[beltmanjr]

Ok, but that is encouraging I would say.
Which version of MSSQL are we talking about?
What is the authentication mode you are using and what does the server accept?

And do you need to set authentication separately perhaps for your stored procedures?