Question about Set Ansi Nulls on and off 2

[jfield817]

We are trying to create a stored proc which uses a linked server of dbf files.&nbsp;&nbsp;&nbsp;Setting ANSI_NULLS ON and SET ANSI_Warnings ON&nbsp;&nbsp;still results in msg that those settings must be set on.<br><br>Any ideas as to why the settings set to ON ..prior to running the CREATE PROC ...are not being recognized<br>any ideas would be appreciated<br>thank you<br>john

 

[Christine0701]

Did you ever find a solution to this problem?

 

[cdukes]

The state of ANSI_NULLS ON when running a stored procedure takes the values of the state when the stored procedure was actually installed.

Thus Adding Set ANSI_NULLS on in the stored procedure actually has no effect at all. When you install the stored procedure you should preceed the stored procedure with the commands
SET ANSI_NULLS OFF
SET ANSI_WARNINGS ON
GO

Make sure that you also have the go statement, as this ensures that the settings are activated before you install the stored procedure.

Hope this helps,

Chris Dukes

 

[cdukes]

From books on line:

For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed

Chris

 

[Christine0701]

I did set ansi_nulls and ansi_warnings on at the creation time of the stored procedure and I still get the error message.

EX:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
Create Procedure pr_MyStoredProcedure
...

Any other ideas?
Thanks!

 

[cdukes]

What is the stored procedure running on SQLServer 6.5/7.0 ?
Are you also using a linked server ?
If you are using a linked server, is the linked server driver( ODBC or other ) configured for ANSI NULLS ?
When do you get an error message ?
Where are you runnng the stored procedure from ?

Chris

 

[soundarya]

Hi Christine0701,
I found the same problem even after these ANSI settings before the procedure compilation.
But I have got a workaround.
I just turned on these ANSI settings (ANSI Nulls and ANSI warnings) for the whole database using sp_dboption. Now the SQL job is working fine with linked server procedure.
Please try this and let me know.

Soundarya

 

[boogin]

Create the stored procedure via query analyzer.

As long as you have the linked server ANSI settings correct - this works.

Just did it an hour ago. . . had the same exact problem.

 

[ctwilliams]

This worked for me...

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