May 19, 2005 at 9:22 am
Greetings,
I am trying to write a stored procedure to run a query against a linked server. In trying to save/close the proc, Error 7405 "...Must set ANSI_NULLS... appears. I modified the proc to:
Create MyProc AS
Set ANSI_WARNINGS ON
SET ANSI_NULLS ON
SELECT * FROM Server2.Database2.dbo.MyTable
but this did not solve the problem. No problem running a query in Server 1's QA against Server2, but no go with the proc.
How do I set the ANSI nulls to satisfy the connection issue?
Elliott
May 19, 2005 at 10:17 am
I think the SETs have to come prior to the CREATE and must be sep. by GO??
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 19, 2005 at 10:25 am
Tried that but error message says nothing can be written before the "Create Procedure" statement.
Elliott
May 19, 2005 at 12:54 pm
Here is an explanation of CREATE PROCEDURE Books Online topic.
"SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure."
Why don't you set ANSI_NULLS in Query Analyzer in Query ->Current Connection Options and then run CREATE PROCEDURE in this query window ?
Yelena
Regards,Yelena Varsha
May 19, 2005 at 4:34 pm
Yelena, Thanks for your tip. Building the stored procedure right from QA works great and is very efficient.
Elliott
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply