May 19, 2005 at 12:26 pm
Greetings
I am trying to write a stored procedure on Server 1 to run a query against linked Server 2. Error 7405 appears when I try to save the new procedure, demanding that ansi nulls be set. I tried the following syntax but no luck:
Create Procedure MyProc AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SELECT * FROM SERVER2.CUSTOMERS.DBO.TABLE1
GO
So far every variation of the above still produces the error upon attempting to close the proc. The same query runs fine in Query Analyzer. Any ideas?
May 19, 2005 at 12:35 pm
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
Create Procedure MyProc AS
SELECT * FROM SERVER2.CUSTOMERS.DBO.TABLE1
GO
May 19, 2005 at 12:36 pm
Actually I've always put goes in between the set statements, can you try without 'em and tell me if it works??
May 19, 2005 at 1:01 pm
Thanks for your help. I was unable to put any commands preceding the "Create Proc" command when writing the proc. UNTIL I used Query Analyzer to edit the proc. Then the (system default?) SET ANSI_NULLS command was displayed and I was able to set it to "ON". Once the proc was edited in QA everything worked fine.
This seems like a long way round, but at least the proc's can be written to run cross-server queries.
Elliott
May 20, 2005 at 10:39 am
cross-server queries are considered heterogenous queries so you have to set ansi_nulls/warnings "on" with the "GO"s
then you have to create the procedure in QA (because you cant put three seperate statements in a "create sp window" in Enterprise Mangler. Once it is created you can alter it in Enterprise Mangler.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply