ANSI_NULLS error message 2

[RobertT687]

I have the following in a stored procedure on a SQL 7.0 server for accessing data from a SQL 6.5 server:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
select *
into #temp_SmartStreamOpenDocuments
FROM HOOD.DBSrrcv.dbo.recv_doc
where recv_entity_id in ('412','418') AND
recv_doc_status_code <> 'C'

When I try to compile the procedure I get the error message:
&quot;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&quot;

How do I set these options for the procedure to avoid this error?

 

[osjohnm]

Hi Robert

This is an Enterprise Manager Bug with stored procedures which contain linked objects.

To work around this execute your create procedure statement in query analyser like so..


SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE yourproc
AS

select *
into #temp_SmartStreamOpenDocuments
FROM HOOD.DBSrrcv.dbo.recv_doc
where recv_entity_id in ('412','418') AND
recv_doc_status_code <> 'C'

GO

The stored proc should then be created successfully. You have to set the ANSI options first before issuing the create statement. Once the proc is created sql server will store the ansi settings so you can then edit it in EM and save the changes there.

There is an article at Microsft about this but for the life of me I can't locate it. If I can find it, I'll post the link for you.

Hope this helps

John

 

[harryrich]

I can't speak authoritatively, but I seem to recall from somewhere that the procedure runs with the settings at the time of compilation. In other words, you might try running the two SET statements, compiling the procedure, and then setting the variables back to the values you want to default to.

Best of luck.

 

[osjohnm]

Hi Robert

Here is the link that I mentioned:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q296769

John