Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error 7405 - Heterogeneous queries require the ANSI_NULLS

Status
Not open for further replies.
dariopowell

dariopowell

Programmer
Jun 15, 2004
14
US

I am trying to write and save a stored procedure but I keep getting the folling error.

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 then reissue your query.

The ANSI_NULLS and ANSI_WARNINGS are turned on in the properties on the connections tab for the server in question.

The code does a simple create table and then populates that table from other existing tables. The queary never needs to leave the Server in question and access another server.

NOTE: The query runs fine in Query Analyzer.

Any ideas would be greatly appreciated.




Code: 
CREATE PROCEDURE [dbo].[spEDSClienteleFiles]  @Client varchar(5),  @Pub varchar(3), @Server varchar(30), @DataBase varchar(30), @flag varchar(3)
AS


if exists (select * from dbo.sysobjects 
	where name = ('EdSlottMastercat'))
	begin
	drop table EdSlottMastercat
	end


CREATE TABLE [EdSlottMastercat] (
	[acronym] [char] (3)   NOT NULL ,
	[acctno] [char] (8)   NOT NULL ,
	[dtcat] [datetime] NULL ,
	[cdcat] [varchar] (3)   NULL ,
	[source] [varchar] (10)   NULL ,
	[tramount] [smallmoney] NULL ,
	[checkno] [varchar] (16)   NULL ,
	[purchase_card] [varchar] (20)   NULL ,
	[cc_exp] [varchar] (6)   NULL ,
	[mult_pay] [varchar] (1)   NULL ,
	[operator] [varchar] (3)   NULL ,
	[batch] [varchar] (4)   NULL ,
	[curr_status] [varchar] (1)   NULL ,
	[curr_atype] [varchar] (2)   NULL ,
	[former_status] [varchar] (1)   NULL ,
	[former_type] [varchar] (2)   NULL ,
	[slen] [smallint] NULL ,
	[copies_before] [smallint] NULL ,
	[copies_after] [smallint] NULL ,
	[cash_chng] [smallmoney] NULL ,
	[ar_chng] [smallmoney] NULL ,
	[unrn_chg] [smallmoney] NULL ,
	[overundr] [smallmoney] NULL ,
	[yistart] [varchar] (6)   NULL ,
	[yiexpire] [varchar] (6)   NULL ,
	[old_yiex] [varchar] (6)   NULL ,
	[yimail] [varchar] (6)   NULL ,
	[yicycle] [varchar] (6)   NULL ,
	[issuerec] [smallint] NULL ,
	[inv_send_pd] [smallint] NULL ,
	[numtimren] [smallint] NULL ,
	[numren] [smallint] NULL ,
	[rencode] [varchar] (2)   NULL ,
	[onoff] [varchar] (1)   NULL ,
	[single_code] [varchar] (6)   NULL ,
	[dacctno] [varchar] (8)   NULL ,
	[po_num] [varchar] (20)   NULL ,
	[qual_sub] [varchar] (1)   NULL ,
	[flag] [varchar] (3)   NULL ,
	[filler] [char] (1)   NULL ,
	[client_deposit] [char] (1)   NULL ,
	[credit_decline] [char] (1)   NULL ,
	[mult_ind] [char] (1)   NULL ,
	[fix_code] [varchar] (3)   NULL ,
	[process_flag] [char] (1)   NULL ,
	[lockbox#] [varchar] (8)   NULL ,
	[lockbox_datebatch] [varchar] (10)   NULL ,
	[tax_amt] [smallmoney] NULL ,
	[Shipping_Amt] [smallmoney] NULL ,
	[Cancel_Reason] [varchar] (3)   NULL ,
	[repcode] [varchar] (10)   NULL,
	[Product] [char] (1)   NULL 
) ON [PRIMARY]

-----------------------------------------------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects 
	where name = ('EdSlottMastersubinfo'))
	begin
	drop table EdSlottMastersubinfo
	end

CREATE TABLE [EdSlottMastersubinfo] (
	[acronym] [char] (3)   NOT NULL ,
	[acctno] [char] (8)   NOT NULL ,
	[name] [varchar] (32)   NULL ,
	[zip] [varchar] (10)   NULL ,
	[country_code] [varchar] (3)   NOT NULL ,
	[pstclass] [varchar] (1)   NULL ,
	[curr_status] [varchar] (1)   NOT NULL ,
	[curr_type] [varchar] (2)   NOT NULL ,
	[former_status] [varchar] (1)   NULL ,
	[former_type] [varchar] (2)   NULL ,
	[orig_source] [varchar] (10)   NULL ,
	[curr_source] [varchar] (10)   NULL ,
	[dtenter] [datetime] NULL ,
	[dtcat] [datetime] NULL ,
	[yifirst] [varchar] (6)   NULL ,
	[yistart] [varchar] (6)   NULL ,
	[yimail] [varchar] (6)   NULL ,
	[yiexpire] [varchar] (6)   NULL ,
	[copies] [smallint] NULL ,
	[slen] [smallint] NULL ,
	[undel] [smallint] NULL ,
	[issuerec] [smallint] NULL ,
	[paid] [money] NULL ,
	[due] [money] NULL ,
	[unearn] [money] NULL ,
	[nxtype] [varchar] (2)   NULL ,
	[nxlen] [smallint] NULL ,
	[nxpaid] [money] NULL ,
	[nxdue] [money] NULL ,
	[nxcopies] [smallint] NULL ,
	[nxpstcla] [varchar] (1)   NULL ,
	[nxsource] [varchar] (10)   NULL ,
	[yicycle] [varchar] (6)   NULL ,
	[numinv] [smallint] NULL ,
	[billcode] [varchar] (2)   NULL ,
	[numren] [smallint] NULL ,
	[numtimren] [smallint] NULL ,
	[rensrc] [varchar] (10)   NULL ,
	[rencode] [varchar] (2)   NULL ,
	[renuniv] [varchar] (6)   NULL ,
	[rqsrc] [varchar] (10)   NULL ,
	[dacctno] [char] (8)   NULL ,
	[qual_sub] [varchar] (1)   NULL ,
	[bcode] [varchar] (2)   NULL ,
	[btitle] [varchar] (2)   NULL ,
	[qual_src] [varchar] (1)   NULL ,
	[addbreak] [varchar] (1)   NULL ,
	[qualdate] [varchar] (6)   NULL ,
	[verifydate] [varchar] (6)   NULL ,
	[ponum] [varchar] (20)   NULL ,
	[lockbox#] [varchar] (8)   NULL ,
	[lockbox_datebatch] [varchar] (10)   NULL ,
	[repcode] [varchar] (10)   NULL ,
	[delivery_code] [char] (5)   NULL ,
	[route_code] [char] (8)   NULL,
	[Product] [varchar] (1)   NULL 
) ON [PRIMARY]

------------------------------------------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects 
	where name = ('EdSlottMasternameaddr'))
	begin
	drop table EdSlottMasternameaddr
	end

CREATE TABLE [EdSlottMasternameaddr] (
	[acctno] [char] (8)   NOT NULL ,
	[f_name] [varchar] (20)   NULL ,
	[l_name] [varchar] (20)   NULL ,
	[title] [varchar] (38)   NULL ,
	[company] [varchar] (38)   NULL ,
	[addr2] [varchar] (38)   NULL ,
	[street] [varchar] (38)   NULL ,
	[city] [varchar] (23)   NULL ,
	[state] [varchar] (3)   NULL ,
	[zip] [varchar] (10)   NULL ,
	[country_code] [varchar] (3)   NOT NULL ,
	[phone_code] [varchar] (3)   NULL ,
	[phone] [varchar] (10)   NULL ,
	[phone_ext] [varchar] (5)   NULL ,
	[fax_code] [varchar] (3)   NULL ,
	[fax] [varchar] (10)   NULL ,
	[e_mail] [varchar] (50)   NULL ,
	[prefix] [varchar] (2)   NULL ,
	[gender] [varchar] (1)   NULL ,
	[cr_rt] [varchar] (4)   NULL ,
	[county] [varchar] (26)   NULL ,
	[dp_barcode] [varchar] (2)   NULL ,
	[tax_exempt] [varchar] (1)   NULL ,
	[tax_id] [varchar] (10)   NULL ,
	[lineof_travel] [varchar] (4)   NULL ,
	[travel_sort] [varchar] (1)   NULL ,
	[email_rent] [char] (1)   NULL ,
	[email_solicit] [char] (1)   NULL ,
	[mail_rent] [char] (1)   NULL ,
	[mail_solicit] [char] (1)   NULL ,
	[fax_rent] [char] (1)   NULL ,
	[fax_solicit] [char] (1)   NULL ,
	[phone_rent] [char] (1)   NULL ,
	[phone_solicit] [char] (1)   NULL 
) ON [PRIMARY]

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

insert into EdSlottMasternameaddr
(
acctno, f_name, l_name, title, company, addr2, street, city, state, zip, country_code, phone_code, phone, phone_ext, 
fax_code, fax, e_mail, prefix, gender, cr_rt, county, dp_barcode, tax_exempt, tax_id, lineof_travel, travel_sort, email_rent, 
email_solicit, mail_rent, mail_solicit, fax_rent, fax_solicit, phone_rent, phone_solicit
)

select 
acctno, f_name, l_name, title, company, addr2, street, city, state, zip, country_code, phone_code, phone, phone_ext, 
fax_code, fax, e_mail, prefix, gender, cr_rt, county, dp_barcode, tax_exempt, tax_id, lineof_travel, travel_sort, email_rent, 
email_solicit, mail_rent, mail_solicit, fax_rent, fax_solicit, phone_rent, phone_solicit
From Jupiter.edsclient.dbo.edsnameaddr

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

insert into EdSlottMastersubinfo
(
acronym, acctno, name, zip, country_code, pstclass, curr_status, curr_type, former_status, former_type, orig_source, 
curr_source, dtenter, dtcat, yifirst, yistart, yimail, yiexpire, copies, slen, undel, issuerec, paid, 
due, unearn, nxtype, nxlen, nxpaid, nxdue, nxcopies, nxpstcla, nxsource, yicycle, numinv, billcode, numren, 
numtimren, rensrc, rencode, renuniv, rqsrc, dacctno, qual_sub, bcode, btitle, qual_src, addbreak, qualdate, 
verifydate, ponum, lockbox#, lockbox_datebatch, repcode, delivery_code, route_code, product
)

select  
acronym, acctno, name, zip, country_code, pstclass, curr_status, curr_type, former_status, former_type, orig_source, 
curr_source, dtenter, dtcat, yifirst, yistart, yimail, yiexpire, copies, slen, undel, issuerec, paid, 
due, unearn, nxtype, nxlen, nxpaid, nxdue, nxcopies, nxpstcla, nxsource, yicycle, numinv, billcode, numren, 
numtimren, rensrc, rencode, renuniv, rqsrc, dacctno, qual_sub, bcode, btitle, qual_src, addbreak, qualdate, 
verifydate, ponum, lockbox#, lockbox_datebatch, repcode, '', '', product
From  Jupiter.edsclient.dbo.iraSubinfo

------------------------------------------

insert into EdSlottMastercat
(
acronym, acctno, dtcat, cdcat, source, tramount, checkno, purchase_card, cc_exp, mult_pay, operator, batch, 
curr_status, curr_atype, former_status, former_type, slen, copies_before, copies_after, cash_chng, ar_chng, 
unrn_chg, overundr, yistart, yiexpire, old_yiex, yimail, yicycle, issuerec, inv_send_pd, numtimren, numren, 
rencode, onoff, single_code, dacctno, po_num, qual_sub, flag, filler, client_deposit, credit_decline, mult_ind, 
fix_code, process_flag, lockbox#, lockbox_datebatch, tax_amt, Shipping_Amt, Cancel_Reason, repcode, product
)

select  
acronym, acctno, dtcat, cdcat, source, tramount, checkno, purchase_card, cc_exp, mult_pay, operator, batch, 
curr_status, curr_atype, former_status, former_type, slen, copies_before, copies_after, cash_chng, ar_chng, 
unrn_chg, overundr, yistart, yiexpire, old_yiex, yimail, yicycle, issuerec, inv_send_pd, numtimren, numren, 
rencode, onoff, single_code, dacctno, po_num, qual_sub, flag, filler, client_deposit, credit_decline, mult_ind, 
fix_code, process_flag, lockbox#, lockbox_datebatch, tax_amt, Shipping_Amt, Cancel_Reason, repcode, product
From  Jupiter.edsclient.dbo.iracat

update EdSlottMastercat

	Set CheckNo =  
	Case 
	When cc_exp is not null and cc_exp <> '' and Substring(CheckNo,1,1) = '4' Then 'VISA****' +  right(Checkno,4)
	When cc_exp is not null and cc_exp <> '' and  Substring(CheckNo,1,1) = '5' Then 'MC****' + right(Checkno,4)
	When cc_exp is not null and cc_exp <> '' and  Substring(CheckNo,1,2) = '37' Then 'AMEX****' + right(Checkno,4)
	When cc_exp is not null and cc_exp <> '' and  Substring(CheckNo,1,2) = '36' Then 'DIN****' + right(Checkno,4)
	When cc_exp is not null and cc_exp <> '' and  Substring(CheckNo,1,2) = '38' Then 'DIN****' + right(Checkno,4)
	When cc_exp is not null and cc_exp <> '' and  Substring(CheckNo,1,2) = '60' Then 'DIS****' + right(Checkno,4)
	Else CheckNo 
	End
---------------------------------------------

GO
 
Sort by date Sort by votes
Don't you need a GO after DROP TABLE before you do an INSERT?

And for laughs' sake have you tried putting

Code: 
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

at the top? Or whatever settings suit you?
 
Upvote 0 Downvote
I lied about the GO thing. Ignorance rears its ugly head! What I was thinking of only applies to create default, function, procedure, rule, trigger, and view.
 
Upvote 0 Downvote
ESquared - Putting

SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

was my first remdy but it didnt help.

Vongrunt -

Jupiter is the server I am dealing with with this error.
 
Upvote 0 Downvote
If it isn't remote/linked server, remove it from sproc code.

As far as I know, setting ANSI_NULLS inside sproc has no effect. SQL Server remembers this setting when a stored procedure is created or altered.

ANSI_WARNINGS is runtime value - set it inside SP, preferably immediately after "AS".
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
596
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
398
Andrzejek
Andrzejek
Virendra Yaduvanshi
  • Locked
  • Question
SSRS - Subscription page error
Replies
0
Views
171
Virendra Yaduvanshi
Virendra Yaduvanshi
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
215
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
342
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top