Heterogeneous queries... Help me.

Question

Heterogeneous queries... Help me.

Vu Doan Tu

Grasshopper

Points: 18
More actions
November 14, 2005 at 10:11 pm

#89839

I met a proplem.
This is the stored procedure i created:
-----------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[sp_ReceiDistributing]
@Transdate datetime
AS
DECLARE @BrID varchar(5)
DECLARE @RDB varchar(50)
DECLARE @sql varchar(3000)
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
DECLARE BrDB Cursor For
SELECT BrID,ReceiDB FROM BranchReceis
OPEN BrDB
FETCH NEXT FROM BrDB INTO @BrID,@RDB
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @sql=N'SET ANSI_NULLS ON; SET ANSI_WARNINGS ON; '
SELECT @sql=@SQL+N'INSERT INTO '+LTRIM(RTRIM(@RDB))+N'.dbo.[Tranday]([Branch_ID],[TransDate],[SCT],[Amount],[Descript],[Trans_code],[UserCreated],[PostFrom],[PostTo],[Cust_ID],[Cust_name],[Cust_Add], [Status],[Cust_Cert],[Ccy],[Verified],[TransTime],[UserCreated0],[ReceivedTime],[SubBranch_ID],[Exec_Branch],[ReVerified],[PushTime] ) '
SELECT @sql=@SQL+N'SELECT [Branch_ID],[TransDate],[SCT],[Amount],[Descript],[Trans_code],[UserCreated],[PostFrom],[PostTo], [Cust_ID],[Cust_name],[Cust_Add], [Status],[Cust_Cert],[Ccy],[Verified],[TransTime],[UserCreated0], [ReceivedTime],[SubBranch_ID],[Exec_Branch],[ReVerified],[PushTime] FROM [Tranday]'
SELECT @sql=@SQL+N' WHERE (Transdate='''+cast(@Transdate as varchar(12))+N''') AND (SubBranch_ID='''+@BrID+N''')'
EXEC(@SQL)
--Print @sql
SELECT @sql=N'SET ANSI_NULLS ON; SET ANSI_WARNINGS ON; '
SELECT @sql=@SQL+N'INSERT INTO '+LTRIM(RTRIM(@RDB))+N'.dbo.[TrandayDetail]([Branch_ID], [TransDate], [SCT], [Account_ID], [db_Amount], [Cr_Amount], [SEQ], [Medium], [MasterAcc], [Ccy], [OpenBalance], [ReceivedTime], [SubBranch_ID], [Exec_Branch], [PushTime]) '
SELECT @sql=@SQL+N'SELECT [Branch_ID], [TransDate], [SCT], [Account_ID], [db_Amount], [Cr_Amount], [SEQ], [Medium], [MasterAcc], [Ccy], [OpenBalance], [ReceivedTime], [SubBranch_ID], [Exec_Branch], [PushTime] FROM [TrandayDetail] '
SELECT @sql=@SQL+N' WHERE (Transdate='''+cast(@Transdate as varchar(12))+N''') AND (SubBranch_ID='''+@BrID+N''')'
EXEC(@SQL)
--Print @sql
FETCH NEXT FROM BrDB INTO @BrID,@RDB
END
CLOSE BrDB
DEALLOCATE BrDB
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------
It is all Ok but when I run it in the following statement
exec dbo.sp_ReceiDistributing 'Nov 14 2005'
I got the message:
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.
Anyone can help me. Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

You don't say if you enabled this or not...did you try with ANSI_NULLS and ANSI_WARNINGS ON ?!?! Did you get a different error message ?!

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 2

I think your error is at creation time!

try:

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

SET ANSI_WARNINGS ON

GO

CREATE PROCEDURE [dbo].[sp_ReceiDistributing]

@Transdate datetime

AS

...

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Hi noel - thanks for being "translator & interpreter" of my post...

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 4

Well, a little bit of clarification was needed because if you look closely, the poster did enable the settings in the code but didn't at creation time

I didn't see this as a trivial "interpretation" of your anwser but I could be wrong

* Noel

Vu Doan Tu Grasshopper Points: 18 More actions · Answer 5

Thanks 4 all. I found the answer.

http://support.microsoft.com/default.aspx?scid=kb;en-us;296769