Error when creating Stored Procedure

I am trying to create a new stored procedure:
****************************************
***************
CREATE PROCEDURE XXX_GetInfo AS
select cast(info.id as varchar) refno, info.begindt, enddt, info.company,
info.type, info.weight
from info info
union
select cast(test.id as varchar) refno, test.begindt, enddt, test.company,
test.type, info.weight
from link1.test.dbo.test test
order by begindt,enddt
****************************************
***************
link1 is a linked server
When I check syntax, sql comes back and says that syntax is correct.
When I try to create the stored procedure, I get an:
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 7405: 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.
I have checked that ANSI_NULLS, ANSI_WARNINGS, and ANSI Padding is
configured as on for both servers.
When Setting up the linked server :
General Tab
Linked server - link1
Other data source - Provider Name - Microsoft OLE DB Provider for
SQL Server
Product name -
Data source - ip address of the server
Provider string -
catalog - test
Security Tab
Be made using this security context - sa of the remote machine
Any help or insight as to why it will not allow me to create the stored
procedure will be greatly appreciated.
Thanks in Advance
VinceServer settings are overridden by tool settings which are overriden by
explicit SET settings. So just issue the appropriate SET statements in the
same query window where you are executing the create proc statement.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Vince,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this
issue.
I think Cindy has pointed out the root cause that the server settings are ov
eridden. To explicit
that settings in either Enterprise Manager or Query Analyzer, you should iss
ue the SET within
the CREATE PROCEDURE so that these setting will not be affected by SQL state
ments in
anohter Client Tools . Otherwise, the life span of these settings ends with
the batch finishes
and they may be OFF before you create your procedure.
Please look through the following script and see if it works on your side. I
f there is anything
more we can do to assist you, please feel free to post it in the group.
---
CREATE PROCEDURE XXX_GetInfo
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
select cast(info.id as varchar) refno, info.begindt, enddt, info.company,
info.type, info.weight
from info info
union
select cast(test.id as varchar) refno, test.begindt, enddt, test.company,
test.type, info.weight
from link1.test.dbo.test test
order by begindt,enddt
GO
---
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.