Heterogeneous queries - SQL Server Forums

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

Transact-SQL (2000)

Heterogeneous queries

Author

Topic

mufasa
Yak Posting Veteran

62 Posts

Posted - 2005-10-20 : 11:50:54

Hi there

I am pulling data from a linked server in a query, but everytime I try to run it I get the following error;

Server: Msg 7405, Level 16, State 1, Line 4
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 syntax is the following;
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

truncate table IPTEmployee
insert INTO IPTEmployee

SELECT dbo.EmployeValid.Dept AS Store, dbo.StoreNew.[group 3] AS Type, dbo.EmployeValid.Title, dbo.Rep_Calendar.[Year],
dbo.Rep_Calendar.Week, SUM(DBSFIL.dbs_tot_sal) - SUM(ABS(DBSFIL.dbs_tot_ref)) AS Sales, SUM(DBSFIL.dbs_itm_sal) - SUM(DBSFIL.dbs_itm_ref)
AS Qty, SUM(DBSFIL.dbs_bil_sal) + SUM(DBSFIL.dbs_bil_ref) AS Trans,
dbo.EmployeValid.[First Name] + N' ' + dbo.EmployeValid.[Last Name] AS Name, CONVERT(decimal(38, 6), SUM(DBSFIL.dbs_itm_sal)
- SUM(DBSFIL.dbs_itm_ref)) / (SUM(DBSFIL.dbs_bil_sal) + SUM(DBSFIL.dbs_bil_ref)) AS IPT, (SUM(DBSFIL.dbs_tot_sal)
- SUM(ABS(DBSFIL.dbs_tot_ref))) / dbo.EmployHours.TTLHours AS SPH, dbo.EmployHours.TTLHours AS Hours, DBSFIL.dbs_emp_code AS Employ,
SUM(DBSFIL.dbs_bil_sal) - SUM(DBSFIL.dbs_bil_ref) AS NetTrans, SUM(DBSFIL.dbs_itm_sal) - SUM(DBSFIL.dbs_itm_ref) AS NetQty

FROM CANMTLSQL02.YZZA.dbo.DBSFIL as DBSFIL INNER JOIN
dbo.Rep_Calendar ON DBSFIL.dbs_date = dbo.Rep_Calendar.CivilDate INNER JOIN
dbo.[This Year Last Year1] ON dbo.Rep_Calendar.[Year] >= dbo.[This Year Last Year1].LY INNER JOIN
dbo.StoreNew ON DBSFIL.dbs_store = dbo.StoreNew.StoreA INNER JOIN
dbo.EmployeValid ON DBSFIL.dbs_emp_code = dbo.EmployeValid.[EE num] INNER JOIN
dbo.EmployHours ON dbo.Rep_Calendar.[Year] = dbo.EmployHours.[Year] AND dbo.Rep_Calendar.Week = dbo.EmployHours.Week AND
dbo.EmployeValid.[EE num] = dbo.EmployHours.EmployeeId
GROUP BY dbo.Rep_Calendar.[Year], dbo.Rep_Calendar.Week, dbo.StoreNew.[group 3], dbo.EmployeValid.Dept, DBSFIL.dbs_emp_code,
dbo.EmployeValid.Title, dbo.EmployeValid.[First Name] + N' ' + dbo.EmployeValid.[Last Name], dbo.EmployHours.TTLHours
GO

I don't much understand what the error is telling me, because I have other queries using this linked server without problems.

When it tells me to

quote:
Enable these options and then reissue your query.

Where am I supposed to enable these?

Thanks for any help

Mufasa

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-20 : 13:18:25

You can find what you want in SQL's Books On-Line (BOL)

quote:
Syntax
SET ANSI_NULLS {ON | OFF}

Remarks
The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

mufasa
Yak Posting Veteran

62 Posts

Posted - 2005-10-20 : 15:56:52

I checked BOL first, but could not find the answer.

I use the
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
Because I sometimes have divide by zero's

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources