Error 7405 - heterogeneous queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error 7405 - heterogeneous queries

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-28 : 05:41:22

Heterogeneous queries (meaning fetching from remote servers, I think?) require ANSI_NULLS and ANSI_WARNINGS options to be set for the connection, the error tells me.

So while preparing an SP which isn't accepted I add

SET ANSI_NULLS on
GO
SET ANSI_WARNINGS off
GO

to the start of it, then the script afterwards - 90% of it. Then the error is gone and the SP seems to be accepted and stored now but when I open it to check it, all the text is gone! Only the SET ANSI_NULLS part remains! What a mess!! What happened here?!?!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 05:44:10

Did you generated script of SP? or just used sp_helptext?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-28 : 06:02:03

The script in the SP runs just fine in Query Analyzer, and I think it would be fine in an SP if it was running on the home server. The script is:

insert into mobile_solution_process (msp_id, work_order, received, departed, arrived, completed)
select newid(), left(cid, 10), received, departed, arrived, completed
from [ken-sql-002].hettonhosttestdatabase.dbo.job
where cid not in ('1','2','3','6')
and left(completed, 10) like left(getdate(), 10)

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-28 : 06:03:02

What is sp_helptext ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 06:10:17

sp_helptext is system stored proc which shows text of SP, UDF, view, trigger etc.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-28 : 06:20:46

so in your 'create proc' script you put...
set ansi_nulls on
go

??

in that case surely the create proc script 'terminated' at the first GO. so your proc is only 'set ansi_nulls on'

Em

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-28 : 09:03:23

Well I set things up like this:

CREATE procedure populate_msp_2 AS

SET ANSI_WARNINGS OFF

SET ANSI_NULLS ON

insert into mobile_solution_process (msp_id, work_order, received, departed, arrived, completed)
select newid(), left(cid, 10), received, departed, arrived, completed
from [ken-sql-002].hettonhosttestdatabase.dbo.job
where cid not in ('1','2','3','6')
and left(completed, 10) like left(getdate(), 10)

GO

It's there. When I run it, I still gives the usual error and terminates!

Server: Msg 7405, Level 16, State 1, Procedure populate_msp_2, Line 8
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.

It's all to do with the remote server. If I comment out that and use a local source everything works, so is there a way to improve on the linked server?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources