ANSI defaults, Linked Server

Hey all,
I have a linked server on our SQL 2000 box, which points to PCCW.mdb; An
Access database that our Credit Card processing software uses. I have
written a SP to grab some data from this Linked Server, and from my local SQ
L
database. The Syntax check on the SP goes fine, but when I try to save the
SP, I get the warning:
"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 the reissue your query"
In the SP I am issuing the commands for ANSI_NULLS and ANSI_WARNINGS (will
post below), and when I run the SELECT statement contained in the SP through
Query Analyzer, it works fine. Any help is always appreciated, and thanks i
n
advance.
Gerard - SP posted below
CREATE PROCEDURE spBasSunLyncInBadCredit
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SELECT tblCreditCardControl.CID, PCCW..Trans.[Date], PCCW..Trans.[Time],
PCCW..Trans.Card, PCCW..Trans.TID, PCCW..Trans.Issuer, PCCW..Trans.Member,
PCCW..Trans.ExpDate, PCCW..Trans.Amount, PCCW..Trans.Status,
PCCW..Trans.Auth, PCCW..Trans.BatchNumber, PCCW..Trans.Ticket FROM
PCCW...Trans INNER JOIN tblCreditCardControl ON PCCW..Trans.TID =
tblCreditCardControl.MerchantNumber WHERE ((tblCreditCardControl.CID = '357'
)
AND (PCCW..Trans.Status = 'C') AND (PCCW..Trans.[Date] = '2/7/05'))
GOWhere are you creating the sp in EM?, Try using QA.
BUG: Can't Use SQL Enterprise Manager to Create Stored Procedures Containing
Linked Server Objects
http://support.microsoft.com/defaul...kb;en-us;296769
AMB
"Gerard" wrote:

> Hey all,
> I have a linked server on our SQL 2000 box, which points to PCCW.mdb;
An
> Access database that our Credit Card processing software uses. I have
> written a SP to grab some data from this Linked Server, and from my local
SQL
> database. The Syntax check on the SP goes fine, but when I try to save th
e
> SP, I get the warning:
> "Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_Warning
s
> options to be set for the connection. This ensures consistent query
> semantics. Enable these options and the reissue your query"
> In the SP I am issuing the commands for ANSI_NULLS and ANSI_WARNINGS (will
> post below), and when I run the SELECT statement contained in the SP throu
gh
> Query Analyzer, it works fine. Any help is always appreciated, and thanks
in
> advance.
> Gerard - SP posted below
> CREATE PROCEDURE spBasSunLyncInBadCredit
> AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SELECT tblCreditCardControl.CID, PCCW..Trans.[Date], PCCW..Trans.[Time],
> PCCW..Trans.Card, PCCW..Trans.TID, PCCW..Trans.Issuer, PCCW..Trans.Member,
> PCCW..Trans.ExpDate, PCCW..Trans.Amount, PCCW..Trans.Status,
> PCCW..Trans.Auth, PCCW..Trans.BatchNumber, PCCW..Trans.Ticket FROM
> PCCW...Trans INNER JOIN tblCreditCardControl ON PCCW..Trans.TID =
> tblCreditCardControl.MerchantNumber WHERE ((tblCreditCardControl.CID = '35
7')
> AND (PCCW..Trans.Status = 'C') AND (PCCW..Trans.[Date] = '2/7/05'))
> GO