ANSI defaults, Linked Server

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Gerard (Gerard_at_discussions.microsoft.com)
Date: 02/11/05 

Date: Fri, 11 Feb 2005 09:03:08 -0800

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 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 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 = '357')
AND (PCCW..Trans.Status = 'C') AND (PCCW..Trans.[Date] = '2/7/05'))

GO

Relevant Pages

  • Re: Operator in SQL like "Like"
    ... I have an Access database with a table called "product". ... The SQL that Access gives me for the query I am trying ... All of the operators used in Microsoft Jet SQL ... So it may be that the Like operator for Access SQL cannot be used in Delphi. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Running a Query
    ... in your "code" (it's called a SQL statement, ... query in design view. ... the SQL is presented in the query grid. ... >> is the Members table in the Access database where you're building the ...
    (microsoft.public.access.queries)
  • Pass-Through, Default DSN?
    ... I am converting my companies Access database to SQL ... as a front end to connect to SQL Server. ... Through query so it connects to the ODBC to run the query, ...
    (microsoft.public.access.queries)
  • RE: How to simple retrieve autonumber valuees or identity from data so
    ... you can write the equivalent of a stored procedure in Access. ... Simply write your SQL in a query and save it. ... > I use the access database in my simple application. ...
    (microsoft.public.dotnet.framework.adonet)
  • Querying a Access database as a linked server
    ... The query should be like this: ... >I created a linked server to an MS Access database. ... However, I don't knwo ...
    (microsoft.public.sqlserver.security)