Re: Heterogeneous queries error

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

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 07:55:30 -0600

> So where do I set the ANSI NULLS ?

You can create your stored procedure from Query Analyzer to set the options
as desired. For example:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE MyProcedure
AS
...
GO

These SET options are remembered and will be used when executing the
procedure regardless of the connection settings. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"kriste" <kriste_l@hotmail.com> wrote in message
news:uQre0Ki9DHA.1392@tk2msftngp13.phx.gbl...
> So where do I set the ANSI NULLS ?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> message news:%23y9JeSh9DHA.3900@tk2msftngp13.phx.gbl...
> > 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
    ... You need to set ANSI_NULLS when you *create* the proc, ... I've a dynamic query that works on linked server but it keep giving me this ... 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? ... This ensures consistent query ... > DECLARE @sqlString NVARCHAR ... > -- exist in remote but missing in HQ ...
    (microsoft.public.sqlserver.programming)
  • stored proc ansi_nulls help
    ... Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. ... Enable these options and then reissue your query. ... I put the ansi nulls and ansi warnings in when building the proc but am still recieving the error. ...
    (microsoft.public.sqlserver.programming)
  • Re: Microsoft SQL-DMO error 7405 on linked server query
    ... > I am attempting to execute a query on a linked oracle server. ... Hetergenous queries require the ANSI NULLS and ANSI ... stored procedure using query analyiser and all is well. ...
    (microsoft.public.sqlserver.connect)
  • 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)