Hidden Subreport Executing Queries

I'm currently developing a report in which the customer has requested a
drill-down type of interface that starts at a regional level to a
district level to a territory level and finally to the individual
customers within the territory. The drill-down interface was easy to
create by using Groups in conjunction with the ToggleItem and
Visibility.Hidden properties at the different Grouping levels.
The problem I have run into is that there are around 1 Million
customers across the country and the report was attempting to retrieve
all of the records at all levels at once. This turns out to be
unworkable from a performance standpoint.
To try to work around this I proposed using drill-through at the
territory level to open a new report with that territories customer
list. The customer was unwilling to accept that approach since they
wanted to keep the flexibility of navigating through the tree without
the disruption of opening a separate report.
Next I tried to work around the problem by implementing a subreport at
the customer level thinking that the subreport would only execute once
the user drilled down into the territory level. Following is a general
overview of this solution:
+ North East
- South East
- Georgia
- Atlanta
- subreport displays here with territory customer list
+ Savannah
+ Athens
+ Florida
+ Kentucky
- Midwest
+ Kansas
+ Iowa
+ Minnesota
+ West
By using SQL Profiler I found that the report was actually executing
the query for each individual territory's corresponding subreport
even though those levels were hidden. This was essentially the same
scenario that I had with my initial attempt.
To try to work around this I created a parameter within the subreport
called "show_subreport" and if that parameter is set to 1 the dataset
will return the customer list and if it is set to 0 the dataset will
not return any rows. By manually setting the parameter to 0 through
the parameter mapping in the parent report this worked well. However,
I have not been able to figure out how to dynamically change that
parameter by using an expression. I have been trying to set the
parameter expression to something like:
= IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
However, I have not been able to get to that property or any other
property which would be relevant to the "show_subreport" parameter.
This seems like it would be a very common type of report request so I
assume there is a way to do this. Any help would be appreciated.I've got the same problem.
--
/Mattias
"fred_mumble@.yahoo.com" wrote:
> I'm currently developing a report in which the customer has requested a
> drill-down type of interface that starts at a regional level to a
> district level to a territory level and finally to the individual
> customers within the territory. The drill-down interface was easy to
> create by using Groups in conjunction with the ToggleItem and
> Visibility.Hidden properties at the different Grouping levels.
> The problem I have run into is that there are around 1 Million
> customers across the country and the report was attempting to retrieve
> all of the records at all levels at once. This turns out to be
> unworkable from a performance standpoint.
> To try to work around this I proposed using drill-through at the
> territory level to open a new report with that territories customer
> list. The customer was unwilling to accept that approach since they
> wanted to keep the flexibility of navigating through the tree without
> the disruption of opening a separate report.
> Next I tried to work around the problem by implementing a subreport at
> the customer level thinking that the subreport would only execute once
> the user drilled down into the territory level. Following is a general
> overview of this solution:
> + North East
> - South East
> - Georgia
> - Atlanta
> - subreport displays here with territory customer list
> + Savannah
> + Athens
> + Florida
> + Kentucky
> - Midwest
> + Kansas
> + Iowa
> + Minnesota
> + West
> By using SQL Profiler I found that the report was actually executing
> the query for each individual territory's corresponding subreport
> even though those levels were hidden. This was essentially the same
> scenario that I had with my initial attempt.
> To try to work around this I created a parameter within the subreport
> called "show_subreport" and if that parameter is set to 1 the dataset
> will return the customer list and if it is set to 0 the dataset will
> not return any rows. By manually setting the parameter to 0 through
> the parameter mapping in the parent report this worked well. However,
> I have not been able to figure out how to dynamically change that
> parameter by using an expression. I have been trying to set the
> parameter expression to something like:
> = IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
> However, I have not been able to get to that property or any other
> property which would be relevant to the "show_subreport" parameter.
> This seems like it would be a very common type of report request so I
> assume there is a way to do this. Any help would be appreciated.
>sql

Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS

I have stored procedure:

EXEC sp_addlinkedsrvlogin @.FailedRegionServerName, 'false', NULL, 'sa', 'pass'

DECLARE @.a varchar(100)
SET @.a = @.FailedRegionServerName + '.Ithalat.dbo.Product'

DECLARE @.s varchar(100)
SET @.s = ' SELECT * FROM ' + @.a
EXEC ( @.s )

When I execute it I get the 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.

Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

Then I tried:

DECLARE @.s varchar(300)
SET @.s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @.a
EXEC ( @.s )

I still got the error.

WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

You will have to create the stored procedures with those ANSI Settings:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

CREATE PROCEDURE Name (...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Well, your suggestion had been tried and didn't work

All I need is to select some data from another server and insert into local server

|||

In Query Analyzer

Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;

Execute and then remove these two lines of code.

You can now write your create procedure code in Analyzer

SQL server will remember these ansi settings evertime your procedure is subsequently called

|||

hi,

did u get the solution for ur problem? i am facing the same error..

|||

Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....

-Sue

Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS

I have stored procedure:

EXEC sp_addlinkedsrvlogin @.FailedRegionServerName, 'false', NULL, 'sa', 'pass'

DECLARE @.a varchar(100)
SET @.a = @.FailedRegionServerName + '.Ithalat.dbo.Product'

DECLARE @.s varchar(100)
SET @.s = ' SELECT * FROM ' + @.a
EXEC ( @.s )

When I execute it I get the 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.

Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

Then I tried:

DECLARE @.s varchar(300)
SET @.s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @.a
EXEC ( @.s )

I still got the error.

WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

You will have to create the stored procedures with those ANSI Settings:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

CREATE PROCEDURE Name (...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Well, your suggestion had been tried and didn't work

All I need is to select some data from another server and insert into local server

|||

In Query Analyzer

Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;

Execute and then remove these two lines of code.

You can now write your create procedure code in Analyzer

SQL server will remember these ansi settings evertime your procedure is subsequently called

|||

hi,

did u get the solution for ur problem? i am facing the same error..

|||

Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....

-Sue

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

I have a SQL200 stored proc that gives me the 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." when I try to execute it through the query analyzer.

I was able to create the stored proc fine but when I try to execute it through the query analyzer it gives me the above error. I do have Link Server select inside the stored proc. I have to turn of warnings inside the stored proc in order for it to not crash my vb6 recordset by putting in the SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF
or else my vb6 recordset crashes.

When I created the sproc, I did what every one was telling me to do in the forums by putting in the

SET ANSI_WARNINGS ON
Go
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure usp_SprocName

AS
SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF

Can someone help me?this is a confirmed bug of sql2k. do u read this link?
http://support.microsoft.com/kb/296769/en-us|||I am not using the Enterprise Manager like the artical says. I am using the Query Analyser. I don't have a problem creating the stored proc. I only get the error when I try to execute the stored proc.|||I assume this to be the code that you are using to create the stored procedure.

SET ANSI_WARNINGS ON
Go
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure usp_SprocName

AS
SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF


OK. But why haven't you done what was mentioned in the Microsoft Support Article? That would seem like the only logical step to me.

The error occurs because, in order to execute this type of query, the stored procedure definition must contain the definitions mentioned in the article. Your stored procedure sets the relative option to 'ON' but then immediately afterwards sets it to OFF. Try reading your stored procedure definition again...

Regards,|||The reason for turning the warnings off immediately is because the stored proc. is executed in VB6 to populate a record set object. If after the creation of sproc, NSI_WARNINGS, NOCOUNT, ANSI_NULLS are on, then the recordset object crashes. The article you sent me, talks about how to fix a problem creating a stored sproc. After the stored proc is created, the warnings, nocount and ANSI_NULLS can be turned off. I have other sprcos that work this way with different linked servers. For some reason this one does not.

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

Hi,

I have a problem with linked servers.

I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.

So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.

But when I try to access to the aplication database, I hav the following 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 searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.

If anyone can help me, I'd be veri greatfull

Best regards, ArielAriel,
I had a similar problem with SQL 2000 when I added a stored procedure to access the data on a linked server. I had created the stored proc through Query Analyzer and the work around was to create the stored proc through Enterprise Manager. Once I created within Enterprise Manager it worked.
A google search of this problem will provide results.|||The problem is the application have a lot of SPs, tables and views I have created the synonims.

The problem is only with the SPs?

If that, I can remove the synonims and create SPs that access the original ones.

Thanks, Ariel

Heterogeneous queries error

I'am attempting to modify an SMS 2003 web report to include data from a
linked SQL server. Running the query (report) in Query Analyzer works fine,
however, running the report via the web returns the 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.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 7405
Any ideas how to would fix this?
Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> I'am attempting to modify an SMS 2003 web report to include data from a
> linked SQL server. Running the query (report) in Query Analyzer works fine,
> however, running the report via the web returns the 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.
> Error Number: -2147217900
> Source: Microsoft OLE DB Provider for SQL Server
> Native Error: 7405
> Any ideas how to would fix this?
|||I'm not using a stored procedure. SMS 2003 stores the SQL query in the
database as a text field, not as a stored procedure.
"Tibor Karaszi" wrote:

> Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
>
>
|||OK, let me re-phrase:
Preceding any other statement in your code, execute that SET command...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:00AB7DD9-148F-4602-9E46-63C34CA3F576@.microsoft.com...[vbcol=seagreen]
> I'm not using a stored procedure. SMS 2003 stores the SQL query in the
> database as a text field, not as a stored procedure.
> "Tibor Karaszi" wrote:

Heterogeneous queries error

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 option
s to be set for the connection. This ensures consistent query semantics. Ena
ble 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 ='SELECT DISTINCT po_number, invoice_number, received_date, u
pc '
+'FROM ' + @.serverDB_name + '.dbo.receive_order_transactions REMO '
+'WHERE received_date>''' + @.cutoff_date + ''' AND '
+ 'store_id=' + @.storeID + ' '
+ 'AND NOT EXISTS (SELECT * FROM [tm341].dbo.receive_order_transactions HQ '
+ 'WHERE HQ.po_number=REMO.po_number AND HQ.invoice_number=REMO.invoice_n
umber AND '
+ 'HQ.received_date=REMO.received_date AND HQ.store_id=REMO.store_i
d AND '
+ 'HQ.store_id=' + @.storeID + ' AND REMO.store_id=' + @.storeID +')'
EXEC SP_EXECUTESQL @.sqlString
IF @.@.rowCOUNT = 0
print 'remote have all HQ rcv'
ELSE
print 'remote have missing HQ rcv'
GOYou 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

Heterogeneous queries error

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

Heterogeneous queries error

I'am attempting to modify an SMS 2003 web report to include data from a
linked SQL server. Running the query (report) in Query Analyzer works fine,
however, running the report via the web returns the 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.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 7405
---
Any ideas how to would fix this?Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> I'am attempting to modify an SMS 2003 web report to include data from a
> linked SQL server. Running the query (report) in Query Analyzer works fine,
> however, running the report via the web returns the 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.
> Error Number: -2147217900
> Source: Microsoft OLE DB Provider for SQL Server
> Native Error: 7405
> ---
> Any ideas how to would fix this?|||I'm not using a stored procedure. SMS 2003 stores the SQL query in the
database as a text field, not as a stored procedure.
"Tibor Karaszi" wrote:
> Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> > I'am attempting to modify an SMS 2003 web report to include data from a
> > linked SQL server. Running the query (report) in Query Analyzer works fine,
> > however, running the report via the web returns the 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.
> >
> > Error Number: -2147217900
> > Source: Microsoft OLE DB Provider for SQL Server
> > Native Error: 7405
> > ---
> >
> > Any ideas how to would fix this?
>
>|||OK, let me re-phrase:
Preceding any other statement in your code, execute that SET command...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:00AB7DD9-148F-4602-9E46-63C34CA3F576@.microsoft.com...
> I'm not using a stored procedure. SMS 2003 stores the SQL query in the
> database as a text field, not as a stored procedure.
> "Tibor Karaszi" wrote:
> > Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Chad" <Chad@.discussions.microsoft.com> wrote in message
> > news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> > > I'am attempting to modify an SMS 2003 web report to include data from a
> > > linked SQL server. Running the query (report) in Query Analyzer works fine,
> > > however, running the report via the web returns the 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.
> > >
> > > Error Number: -2147217900
> > > Source: Microsoft OLE DB Provider for SQL Server
> > > Native Error: 7405
> > > ---
> > >
> > > Any ideas how to would fix this?
> >
> >
> >

Heterogeneous queries and use of OLEDB providers are not supported

Hi all
I am trying to create linked server on 2005 64bit server for 2000 32 bit.
I ran instcat.sql script as microsoft article sugested.
Still, I am having problems.
I can browse db objects using management studio.
But when i try to run sql like:
select count(*) from botserver.master.dbo.sysobjects
I am getting:
Heterogeneous queries and use of OLEDB providers are not supported in fiber
mode.
I can neither run any stored procs.
My linked server is defined as server type: sql server
Any ideas?
Why are you running in fiber mode in the first place? Unless you have a
very good reason for turning it on you should turn it back off and solve two
problems.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> Hi all
> I am trying to create linked server on 2005 64bit server for 2000 32 bit.
> I ran instcat.sql script as microsoft article sugested.
> Still, I am having problems.
> I can browse db objects using management studio.
> But when i try to run sql like:
> select count(*) from botserver.master.dbo.sysobjects
> I am getting:
> Heterogeneous queries and use of OLEDB providers are not supported in
> fiber
> mode.
> I can neither run any stored procs.
> My linked server is defined as server type: sql server
> Any ideas?
|||Hi Kelly
I have 8 cpu host and content switching is pretty high.
I read that these are indications to use light weight pooling.
If you can please educate me on this point too.
Thank you, Gene.
"Andrew J. Kelly" wrote:

> Why are you running in fiber mode in the first place? Unless you have a
> very good reason for turning it on you should turn it back off and solve two
> problems.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
>
|||Try to use alias name as qualifier instead of full qualifier (complete schema).
Hope this will solve your problem.

Heterogeneous queries and use of OLEDB providers are not supported

Hi all
I am trying to create linked server on 2005 64bit server for 2000 32 bit.
I ran instcat.sql script as microsoft article sugested.
Still, I am having problems.
I can browse db objects using management studio.
But when i try to run sql like:
select count(*) from botserver.master.dbo.sysobjects
I am getting:
Heterogeneous queries and use of OLEDB providers are not supported in fiber
mode.
I can neither run any stored procs.
My linked server is defined as server type: sql server
Any ideas?Why are you running in fiber mode in the first place? Unless you have a
very good reason for turning it on you should turn it back off and solve two
problems.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> Hi all
> I am trying to create linked server on 2005 64bit server for 2000 32 bit.
> I ran instcat.sql script as microsoft article sugested.
> Still, I am having problems.
> I can browse db objects using management studio.
> But when i try to run sql like:
> select count(*) from botserver.master.dbo.sysobjects
> I am getting:
> Heterogeneous queries and use of OLEDB providers are not supported in
> fiber
> mode.
> I can neither run any stored procs.
> My linked server is defined as server type: sql server
> Any ideas?|||Hi Kelly
I have 8 cpu host and content switching is pretty high.
I read that these are indications to use light weight pooling.
If you can please educate me on this point too.
Thank you, Gene.
"Andrew J. Kelly" wrote:
> Why are you running in fiber mode in the first place? Unless you have a
> very good reason for turning it on you should turn it back off and solve two
> problems.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> > Hi all
> > I am trying to create linked server on 2005 64bit server for 2000 32 bit.
> > I ran instcat.sql script as microsoft article sugested.
> > Still, I am having problems.
> > I can browse db objects using management studio.
> > But when i try to run sql like:
> > select count(*) from botserver.master.dbo.sysobjects
> >
> > I am getting:
> > Heterogeneous queries and use of OLEDB providers are not supported in
> > fiber
> > mode.
> >
> > I can neither run any stored procs.
> > My linked server is defined as server type: sql server
> >
> > Any ideas?
>|||Context switching being high is relative but is also usually caused by other
factors that can be addressed to ease this issue. Things like poorly
optimized queries, lack of proper indexes, poorly configured I/O subsystems,
too little memory, excessive blocking etc. Even other apps running on the
same server can cause excessive context switching. Switching to fiber mode
without addressing the root cause will only cause more problems in most
cases. And fiber mode is usually only really effective on systems with more
than 8 CPU's that are running at or near 100% utilization. I suggest you
concentrate on finding the real bottle necks and think seriously about
turning fiber mode off.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:13BBB927-0B8C-4479-8B01-6A9050DA9BF8@.microsoft.com...
> Hi Kelly
> I have 8 cpu host and content switching is pretty high.
> I read that these are indications to use light weight pooling.
> If you can please educate me on this point too.
> Thank you, Gene.
> "Andrew J. Kelly" wrote:
>> Why are you running in fiber mode in the first place? Unless you have a
>> very good reason for turning it on you should turn it back off and solve
>> two
>> problems.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
>> > Hi all
>> > I am trying to create linked server on 2005 64bit server for 2000 32
>> > bit.
>> > I ran instcat.sql script as microsoft article sugested.
>> > Still, I am having problems.
>> > I can browse db objects using management studio.
>> > But when i try to run sql like:
>> > select count(*) from botserver.master.dbo.sysobjects
>> >
>> > I am getting:
>> > Heterogeneous queries and use of OLEDB providers are not supported in
>> > fiber
>> > mode.
>> >
>> > I can neither run any stored procs.
>> > My linked server is defined as server type: sql server
>> >
>> > Any ideas?
>>|||Hi Andrew
Thank you for your information.
Here I have few questions if you would be interested to answer:
1. Do you think that fiber pooling is the real reason for linked server
problem
2. I have 2 similar servers: 8 cpu, 8gb of memory running 64 bit 2003
advanced server. They both do not run anything but 64 bit 2005 sql server.
One is very busy but cpu is close to 3-5% - plenty of queries but all
optimized and lightweight - mostly inserts and single value indexed selects.
cashe is 99%, no paging. Many hundreds processes access it.
Another server is just idling.
Both have 800 - 5000 contest switching all the time. Which i think is too
high.
Do you think it's normal or typical for given platform?
Thank you, Gene.
"Andrew J. Kelly" wrote:
> Context switching being high is relative but is also usually caused by other
> factors that can be addressed to ease this issue. Things like poorly
> optimized queries, lack of proper indexes, poorly configured I/O subsystems,
> too little memory, excessive blocking etc. Even other apps running on the
> same server can cause excessive context switching. Switching to fiber mode
> without addressing the root cause will only cause more problems in most
> cases. And fiber mode is usually only really effective on systems with more
> than 8 CPU's that are running at or near 100% utilization. I suggest you
> concentrate on finding the real bottle necks and think seriously about
> turning fiber mode off.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:13BBB927-0B8C-4479-8B01-6A9050DA9BF8@.microsoft.com...
> > Hi Kelly
> >
> > I have 8 cpu host and content switching is pretty high.
> > I read that these are indications to use light weight pooling.
> > If you can please educate me on this point too.
> >
> > Thank you, Gene.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Why are you running in fiber mode in the first place? Unless you have a
> >> very good reason for turning it on you should turn it back off and solve
> >> two
> >> problems.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> >> > Hi all
> >> > I am trying to create linked server on 2005 64bit server for 2000 32
> >> > bit.
> >> > I ran instcat.sql script as microsoft article sugested.
> >> > Still, I am having problems.
> >> > I can browse db objects using management studio.
> >> > But when i try to run sql like:
> >> > select count(*) from botserver.master.dbo.sysobjects
> >> >
> >> > I am getting:
> >> > Heterogeneous queries and use of OLEDB providers are not supported in
> >> > fiber
> >> > mode.
> >> >
> >> > I can neither run any stored procs.
> >> > My linked server is defined as server type: sql server
> >> >
> >> > Any ideas?
> >>
> >>
>|||1. Yes Fiber mode has many restrictions and I am pretty sure that the
linked server issues you have are one of them.
2. 800 - 5000 context switching for an 8 processor server is not that much.
Especially if you have lots happening. That is no where even close to the
range that should be a consideration for fiber mode usage. Turn it off and
you will be much happier.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:4C7A457E-4176-49EC-8660-D7A95D689A04@.microsoft.com...
> Hi Andrew
> Thank you for your information.
> Here I have few questions if you would be interested to answer:
> 1. Do you think that fiber pooling is the real reason for linked server
> problem
> 2. I have 2 similar servers: 8 cpu, 8gb of memory running 64 bit 2003
> advanced server. They both do not run anything but 64 bit 2005 sql server.
> One is very busy but cpu is close to 3-5% - plenty of queries but all
> optimized and lightweight - mostly inserts and single value indexed
> selects.
> cashe is 99%, no paging. Many hundreds processes access it.
> Another server is just idling.
> Both have 800 - 5000 contest switching all the time. Which i think is too
> high.
> Do you think it's normal or typical for given platform?
> Thank you, Gene.
>
> "Andrew J. Kelly" wrote:
>> Context switching being high is relative but is also usually caused by
>> other
>> factors that can be addressed to ease this issue. Things like poorly
>> optimized queries, lack of proper indexes, poorly configured I/O
>> subsystems,
>> too little memory, excessive blocking etc. Even other apps running on
>> the
>> same server can cause excessive context switching. Switching to fiber
>> mode
>> without addressing the root cause will only cause more problems in most
>> cases. And fiber mode is usually only really effective on systems with
>> more
>> than 8 CPU's that are running at or near 100% utilization. I suggest you
>> concentrate on finding the real bottle necks and think seriously about
>> turning fiber mode off.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> news:13BBB927-0B8C-4479-8B01-6A9050DA9BF8@.microsoft.com...
>> > Hi Kelly
>> >
>> > I have 8 cpu host and content switching is pretty high.
>> > I read that these are indications to use light weight pooling.
>> > If you can please educate me on this point too.
>> >
>> > Thank you, Gene.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Why are you running in fiber mode in the first place? Unless you have
>> >> a
>> >> very good reason for turning it on you should turn it back off and
>> >> solve
>> >> two
>> >> problems.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> >> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
>> >> > Hi all
>> >> > I am trying to create linked server on 2005 64bit server for 2000 32
>> >> > bit.
>> >> > I ran instcat.sql script as microsoft article sugested.
>> >> > Still, I am having problems.
>> >> > I can browse db objects using management studio.
>> >> > But when i try to run sql like:
>> >> > select count(*) from botserver.master.dbo.sysobjects
>> >> >
>> >> > I am getting:
>> >> > Heterogeneous queries and use of OLEDB providers are not supported
>> >> > in
>> >> > fiber
>> >> > mode.
>> >> >
>> >> > I can neither run any stored procs.
>> >> > My linked server is defined as server type: sql server
>> >> >
>> >> > Any ideas?
>> >>
>> >>
>>|||Thank you Andrew, your advises were really helpfull.
"Andrew J. Kelly" wrote:
> 1. Yes Fiber mode has many restrictions and I am pretty sure that the
> linked server issues you have are one of them.
> 2. 800 - 5000 context switching for an 8 processor server is not that much.
> Especially if you have lots happening. That is no where even close to the
> range that should be a consideration for fiber mode usage. Turn it off and
> you will be much happier.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:4C7A457E-4176-49EC-8660-D7A95D689A04@.microsoft.com...
> > Hi Andrew
> >
> > Thank you for your information.
> > Here I have few questions if you would be interested to answer:
> > 1. Do you think that fiber pooling is the real reason for linked server
> > problem
> > 2. I have 2 similar servers: 8 cpu, 8gb of memory running 64 bit 2003
> > advanced server. They both do not run anything but 64 bit 2005 sql server.
> > One is very busy but cpu is close to 3-5% - plenty of queries but all
> > optimized and lightweight - mostly inserts and single value indexed
> > selects.
> > cashe is 99%, no paging. Many hundreds processes access it.
> > Another server is just idling.
> > Both have 800 - 5000 contest switching all the time. Which i think is too
> > high.
> > Do you think it's normal or typical for given platform?
> >
> > Thank you, Gene.
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Context switching being high is relative but is also usually caused by
> >> other
> >> factors that can be addressed to ease this issue. Things like poorly
> >> optimized queries, lack of proper indexes, poorly configured I/O
> >> subsystems,
> >> too little memory, excessive blocking etc. Even other apps running on
> >> the
> >> same server can cause excessive context switching. Switching to fiber
> >> mode
> >> without addressing the root cause will only cause more problems in most
> >> cases. And fiber mode is usually only really effective on systems with
> >> more
> >> than 8 CPU's that are running at or near 100% utilization. I suggest you
> >> concentrate on finding the real bottle necks and think seriously about
> >> turning fiber mode off.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> news:13BBB927-0B8C-4479-8B01-6A9050DA9BF8@.microsoft.com...
> >> > Hi Kelly
> >> >
> >> > I have 8 cpu host and content switching is pretty high.
> >> > I read that these are indications to use light weight pooling.
> >> > If you can please educate me on this point too.
> >> >
> >> > Thank you, Gene.
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Why are you running in fiber mode in the first place? Unless you have
> >> >> a
> >> >> very good reason for turning it on you should turn it back off and
> >> >> solve
> >> >> two
> >> >> problems.
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >> Solid Quality Mentors
> >> >>
> >> >>
> >> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> >> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> >> >> > Hi all
> >> >> > I am trying to create linked server on 2005 64bit server for 2000 32
> >> >> > bit.
> >> >> > I ran instcat.sql script as microsoft article sugested.
> >> >> > Still, I am having problems.
> >> >> > I can browse db objects using management studio.
> >> >> > But when i try to run sql like:
> >> >> > select count(*) from botserver.master.dbo.sysobjects
> >> >> >
> >> >> > I am getting:
> >> >> > Heterogeneous queries and use of OLEDB providers are not supported
> >> >> > in
> >> >> > fiber
> >> >> > mode.
> >> >> >
> >> >> > I can neither run any stored procs.
> >> >> > My linked server is defined as server type: sql server
> >> >> >
> >> >> > Any ideas?
> >> >>
> >> >>
> >>
> >>
>

Heterogeneous queries and use of OLEDB providers are not supported

Hi all
I am trying to create linked server on 2005 64bit server for 2000 32 bit.
I ran instcat.sql script as microsoft article sugested.
Still, I am having problems.
I can browse db objects using management studio.
But when i try to run sql like:
select count(*) from botserver.master.dbo.sysobjects
I am getting:
Heterogeneous queries and use of OLEDB providers are not supported in fiber
mode.
I can neither run any stored procs.
My linked server is defined as server type: sql server
Any ideas?Why are you running in fiber mode in the first place? Unless you have a
very good reason for turning it on you should turn it back off and solve two
problems.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> Hi all
> I am trying to create linked server on 2005 64bit server for 2000 32 bit.
> I ran instcat.sql script as microsoft article sugested.
> Still, I am having problems.
> I can browse db objects using management studio.
> But when i try to run sql like:
> select count(*) from botserver.master.dbo.sysobjects
> I am getting:
> Heterogeneous queries and use of OLEDB providers are not supported in
> fiber
> mode.
> I can neither run any stored procs.
> My linked server is defined as server type: sql server
> Any ideas?

Heterogeneous queries

Hi Guys,

I have one stored procedure on SQL 6.5 and retrieving data from SQL
2000. I have defined a linked server on SQL 6.5. But
I am getting following 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 dropped and re-create the stored procedure after adding the SET
ANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still getting
this error. I have created this sp on ISQL/W not in Enterprise Manager.

Can anyone please let me know, how to fix this problem.

Thanks
AdnanAdnan (adnanjamil58@.yahoo.ca) writes:
> I have one stored procedure on SQL 6.5 and retrieving data from SQL
> 2000. I have defined a linked server on SQL 6.5. But
> I am getting following 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 dropped and re-create the stored procedure after adding the SET
> ANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still getting
> this error. I have created this sp on ISQL/W not in Enterprise Manager.
> Can anyone please let me know, how to fix this problem.

The setting of ANSI_NULLS is saved with the procedure, why it does
not help setting ANSI_NULLS within the procedure.

ANSI_NULLS is on by default with most interfaces - but not if you use
DB-Library, and the 6.5 tools uses DB-Library. If you insist on using
6.5 tools, be sure to always include this:

SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSACTIONS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF

then you get the same settings as in the SQL 2000 tools.

Certainly far more easier is to use Query Analyzer. (You mentioned
Enterprise Manager. If you mean the 6.5 tool, it has the same issue
as ISQL/W. It you mean EM 2000, this is a poor tool for maintaining
stored procedures.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> The setting of ANSI_NULLS is saved with the procedure, why it does
> not help setting ANSI_NULLS within the procedure.

A clarification: the setting of ANSI_NULLS that applies for a stored
procedure, is the setting that was in effect when you created the procedure.
The rest below applies as before:

> ANSI_NULLS is on by default with most interfaces - but not if you use
> DB-Library, and the 6.5 tools uses DB-Library. If you insist on using
> 6.5 tools, be sure to always include this:
> SET ANSI_DEFAULTS ON
> SET IMPLICIT_TRANSACTIONS OFF
> SET CURSOR_CLOSE_ON_COMMIT OFF
> then you get the same settings as in the SQL 2000 tools.
> Certainly far more easier is to use Query Analyzer. (You mentioned
> Enterprise Manager. If you mean the 6.5 tool, it has the same issue
> as ISQL/W. It you mean EM 2000, this is a poor tool for maintaining
> stored procedures.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp