This is a multi-part message in MIME format.
--=_NextPart_000_002E_01C3F654.828ED3B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 =3D'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=3D' + @.storeID + ' '
+ 'AND NOT EXISTS (SELECT * FROM = [tm341].dbo.receive_order_transactions HQ '
+ 'WHERE HQ.po_number=3DREMO.po_number AND = HQ.invoice_number=3DREMO.invoice_number AND '
+ 'HQ.received_date=3DREMO.received_date AND = HQ.store_id=3DREMO.store_id AND ' + 'HQ.store_id=3D' + @.storeID + ' AND = REMO.store_id=3D' + @.storeID +')'
EXEC SP_EXECUTESQL @.sqlString
IF @.@.rowCOUNT =3D 0
print 'remote have all HQ rcv'
ELSE
print 'remote have missing HQ rcv'
GO
--=_NextPart_000_002E_01C3F654.828ED3B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 &= nbsp; VARCHAR(2),@.cutoff_date VARCHAR(30)
AS
SET ANSI_NULLS ONSET ANSI_WARNINGS ONSET = ANSI_DEFAULTS ON
DECLARE @.sqlString NVARCHAR(4000)DECLARE @.recCount INT
-- exist in remote but missing in = HQSET @.sqlString =3D'SELECT DISTINCT po_number, invoice_number, = received_date, upc ' +'FROM ' + @.serverDB_name + '.dbo.receive_order_transactions REMO ' &n= bsp; +'WHERE received_date>''' + @.cutoff_date + ''' AND ' &n= bsp; + 'store_id=3D' + @.storeID + ' ' &n= bsp; + 'AND NOT EXISTS (SELECT * FROM [tm341].dbo.receive_order_transactions HQ ' &n= bsp; + 'WHERE HQ.po_number=3DREMO.po_number AND = HQ.invoice_number=3DREMO.invoice_number AND ' &n= bsp; + &n= bsp; 'HQ.received_date=3DREMO.received_date AND HQ.store_id=3DREMO.store_id = AND ' &n= bsp; + &n= bsp; 'HQ.store_id=3D' + @.storeID + ' AND REMO.store_id=3D' + @.storeID = +')'
EXEC SP_EXECUTESQL @.sqlStringIF = @.@.rowCOUNT =3D 0 print 'remote have all HQ rcv'ELSE = print 'remote have missing HQ rcv'GO
--=_NextPart_000_002E_01C3F654.828ED3B0--You the ANSI_NULLS & ANSI_WARNINGS settings within the stored procedure.
Instead apply these settings while creating the procedure itself.
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE usp_test(...
GO
--
Anith
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment