Re: Heterogeneous queries error

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 12:44:49 +0100

You need to set ANSI_NULLS when you *create* the proc, not inside the proc
code. ANSI NULLS is handled in a special way and setting it inside the proc
code is a dummy operation, it doesn't do anything. 

-- 
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"kriste" <kriste_l@hotmail.com> wrote in message
news:eIDSbDh9DHA.1268@TK2MSFTNGP12.phx.gbl...
Hi,
I've a dynamic query that works on linked server but it keep giving me this
error "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've set all the ANSI_DEFAULT on but still doesn't work. Can someone help me
out?
Thx.
----------------------------------------------------------------------------
----
CREATE PROCEDURE a_sp_check_record
@serverDB_name VARCHAR(100),
@storeID       VARCHAR(2),
@cutoff_date   VARCHAR(30)
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET ANSI_DEFAULTS ON
DECLARE @sqlString  NVARCHAR(4000)
DECLARE @recCount  INT
-- exist in remote but missing in HQ
SET @sqlString ='SELECT DISTINCT po_number, invoice_number, received_date,
upc '
                +'FROM  ' + @serverDB_name +
'.dbo.receive_order_transactions REMO '
                +'WHERE  received_date>''' + @cutoff_date + ''' AND '
                + 'store_id=' + @storeID + ' '
                + 'AND NOT EXISTS (SELECT * FROM
[tm341].dbo.receive_order_transactions HQ '
                +   'WHERE  HQ.po_number=REMO.po_number AND
HQ.invoice_number=REMO.invoice_number AND '
                +          'HQ.received_date=REMO.received_date AND
HQ.store_id=REMO.store_id AND '
                +          'HQ.store_id=' + @storeID + ' AND REMO.store_id='
+ @storeID +')'
EXEC SP_EXECUTESQL @sqlString
IF @@rowCOUNT = 0
 print 'remote have all HQ rcv'
ELSE
    print 'remote have missing HQ rcv'
GO

Relevant Pages

  • Re: Heterogeneous queries error
    ... So where do I set the ANSI NULLS? ... This ensures consistent query ... > DECLARE @sqlString NVARCHAR ... > -- exist in remote but missing in HQ ...
    (microsoft.public.sqlserver.programming)
  • Re: Heterogeneous queries error
    ... > So where do I set the ANSI NULLS? ... You can create your stored procedure from Query Analyzer to set the options ... >> You need to set ANSI_NULLS when you *create* the proc, ... >> -- exist in remote but missing in HQ ...
    (microsoft.public.sqlserver.programming)
  • EXCEPTION_ACCESS_VIOLATION
    ... update a remote table from a web application. ... run from query analyser it works fine. ... But executing both a proc and ... inline query from a c# sharp code results in the ...
    (microsoft.public.sqlserver.security)
  • Re: Expect - memory leak issue
    ... we try to use expect to automatic ftp pull files ... -A pull remote files in alphanumeric order (exclusive ... -D n set debug trace level to n ... proc debug { ...
    (comp.lang.tcl)
  • RE: Using query values for field name in table
    ... "Proc" is a caption given to acalculated field in the query ... should fldName be defined as a string variable which I can then ... >> Dim dbsGeneralThoracic As Database ...
    (microsoft.public.access.modulesdaovba)