Hide an ODBC connection?

We've purchased a 3rd party app that uses an Access db linked to SQL Server. Unfortunately, we've come to discover that a user of ours seems to know access and has decided to take report writing into their own hands.
We have the app and the access db on a server and a mapped a drive for each user.

Unfortunately, I can't lock down the ODBC connection because that would lock it down in the app. Someone suggested using a File DSN and removing the System DSN's off all users machines. I'm not too familiar with File DSN's and wanted to know if this would prevent users from creating their own Access mdb's linked to my server or not.

Any idea's are greatly appreciated.Dude,

Bad news :( - unless you can stop the end user from hacking the registry and prevent access to the odbcad32 (which you really can't lock out and have the machine access the server) there isn't much you can do AFAIK.

You didn't mention what OS's are in play. If you are a Win2k/WinXP shop you may be able to build a policy that limits the user from adding a new DSN, but I doubt even a file DSN will really stop him. If you go look in C:\Program Files\Common Files\ODBC\Data Sources All they really do is point to the system DSN. And he'll be able to pick it in Access.|||I figured the answer would go something like that.
Thank you anyways.

Hi

Hi,

I have setup a linked server to a DB2 database using "Microsoft OLE DB Provider for ODBC Drivers",

the connections works fine i am able to run select queries.

but the number of rows returned from the two following statements are different.

select * from linkedserver...tablename

select count(*) from linkedserver...tablename

e.g. The First statement returns 116 rows, whereas the second query returns count as 144....

I am a bit confused ?

could anybody think of any possible reasons..

Cheers

ARAW012

Hi,

which SQL Server version are you using ? There was an issue with SQL Server 2000. Are you able to trace the queries that are actually executed at the DB2 database ? Did you already try to use the OPENQUERY method to pass the query (without any refactoring through the driver stack) through to the DB2 instance ?

HTH, Jens K. Suessmeyer.

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

i am using SQL server 2005 , and i am using the OPENQUERY method.

I only have read access to the DB2 database.

Thanks

|||The openquery and the "normal" Select behave different, because the OPENQUERY will issue a pass-through to the server instead of doing the interpreted Select query through the driver. So the results can differ according to the changes the driver will do in the middle. Perhaps someone with additional DB2 knowledge can help you here.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Heterogeneous Subscriber Oracle

Publisher /distributor - same server SQL Server 7.0 SP 4
Subscriber - Oracle 9.2.0.3.0
After configuring Oracle subscriber whether as a linked server or a system
DSN, I run the distribution agent and get the following error message:
"Distributor must be running in per seat (named user) licensing mode to use
this replication feature"
SQL Server is set up in per server licensing mode. Now where in al the
documentation does it say that there is this restriction, I really don't
want to change my licensing mode just to work around this one issue. Is
there something I can change in my config to make this work?
Thanks in advance
Tony.
DTS and replication are two components that require the per seat option for
the desktop version of SQL Server.
http://support.microsoft.com/default...b;en-us;207809
Exactly which version of SQL Server are you running?
"TonyL" <anonymous@.discussions.microsoft.com> wrote in message
news:uUkqsDxIEHA.3572@.TK2MSFTNGP10.phx.gbl...
> Publisher /distributor - same server SQL Server 7.0 SP 4
> Subscriber - Oracle 9.2.0.3.0
> After configuring Oracle subscriber whether as a linked server or a system
> DSN, I run the distribution agent and get the following error message:
> "Distributor must be running in per seat (named user) licensing mode to
use
> this replication feature"
> SQL Server is set up in per server licensing mode. Now where in al the
> documentation does it say that there is this restriction, I really don't
> want to change my licensing mode just to work around this one issue. Is
> there something I can change in my config to make this work?
> Thanks in advance
> Tony.
>
|||It's standard edition. This SQL server has been happily replicating data to
other SQL Servers since I set it up 4 1/2 years ago. Ths week was when I
had the first requirement to replicate to an Oracle subscriber.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u9fHO0xIEHA.940@.tk2msftngp13.phx.gbl...
> DTS and replication are two components that require the per seat option
for[vbcol=seagreen]
> the desktop version of SQL Server.
> http://support.microsoft.com/default...b;en-us;207809
> Exactly which version of SQL Server are you running?
>
> "TonyL" <anonymous@.discussions.microsoft.com> wrote in message
> news:uUkqsDxIEHA.3572@.TK2MSFTNGP10.phx.gbl...
system
> use
>
|||I have run into cases where it seems that the licensing info gets scrambled
on SQL 7. I think these occured when we upgraded from nt 4 to win2k.
Can you run a test where you set up a new publication and replicate locally
to perhaps the pubs or northwind database to see what happens?
"TonyL" <anonymous@.discussions.microsoft.com> wrote in message
news:uQI4r7xIEHA.1528@.TK2MSFTNGP09.phx.gbl...
> It's standard edition. This SQL server has been happily replicating data
to[vbcol=seagreen]
> other SQL Servers since I set it up 4 1/2 years ago. Ths week was when I
> had the first requirement to replicate to an Oracle subscriber.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u9fHO0xIEHA.940@.tk2msftngp13.phx.gbl...
> for
> system
to[vbcol=seagreen]
don't[vbcol=seagreen]
Is
>
|||Hilary,
I add SQL Server subscribers to publications on this server regularly, I
don't think this test will identify anything as the issue is with adding an
Oracle subscriber. I also set up a brand new publication on another SQL
Server and added an Oracle subscriber with the same result.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ufGquUyIEHA.2908@.TK2MSFTNGP09.phx.gbl...
> I have run into cases where it seems that the licensing info gets
scrambled
> on SQL 7. I think these occured when we upgraded from nt 4 to win2k.
> Can you run a test where you set up a new publication and replicate
locally[vbcol=seagreen]
> to perhaps the pubs or northwind database to see what happens?
> "TonyL" <anonymous@.discussions.microsoft.com> wrote in message
> news:uQI4r7xIEHA.1528@.TK2MSFTNGP09.phx.gbl...
> to
I[vbcol=seagreen]
option[vbcol=seagreen]
message:[vbcol=seagreen]
> to
the
> don't
> Is
>
|||Hi Tony,
This isn't right. Please call in and open a case with PSS and keep me in the loop so that I can track the case.
Matt
Program Manager
Microsoft SQL Server Replication
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||OK, after talking to PSS here is an extract from the resolution email:
"In the article Q207809, if you set up replication with configuring a SQL
7.0 (using Per Server mode ) as distributor and the subscriber is SQL 7.0
desktop edition, then you will receive the error due to license restriction.
We researched and confirmed that it is by-design that any non-SQL server
subscriber will be considered as equivalent to SQL 7.0 desktop edition
license situation. This means the server that the subscriber communicates
with must evaluate connections based on the Per-Seat licensing model. "
My point is that this is not mentioned in any documentation anywhere.
Tony.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:e1spcu$IEHA.3304@.TK2MSFTNGP10.phx.gbl...
> Hi Tony,
> This isn't right. Please call in and open a case with PSS and keep me in
the loop so that I can track the case.
> Matt
> Program Manager
> Microsoft SQL Server Replication
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Heterogeneous Query Internals

when executing a query joining a local table to a linked server's table, or when querying the linked server's table directly through a local session, does the engine "pass through" to the linked engine the query portion that can be addressed by that server?

for example. . .

select local.master, linked.detail
from mydb.dbo.mytable local inner join linked.yourdb.dbo.yourtable linked
on local.id = linked.id
where linked.lastname = 'smith'

is the engine smart enough pass the " linked.lastname = 'smith' " to the linked engine before doing the join?

or

select linked.*
from linked.yourdb.dbo.yourtable linked
where linked.lastname = 'smith'

does the engine pass the " linked.lastname = 'smith' " to the linked server for it to process?

any reference links would be helpful.

Blair,

Sometimes yes, sometimes no. You should be able to see the remote query by generating the estimated execution plan in SQL Server Management Studio, then hovering the mouse cursor over the remote query operator. The query processor certainly makes some attempt to remote the predicates, but for string equality, the processor can only do so if the remote server has a compatible collation. The query needs to evaluate = 'smith' according to the local collation, and the remote server may or may not be able to do so.

Steve Kass
Drew University
www.stevekass.com

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

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

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

Help:how can i use sql server 2000 in Windows 2003

Windows 2003,sql server
How can i connect sql server 2000?
Unable to get a linked server working.
Please help me,thanks
gouki-- gouki wrote: --
Windows 2003,sql server
How can i connect sql server 2000?
Unable to get a linked server working.
Please help me,thanks
gouki
You should explain it in details, how did you installed it and how did you s
et it up?
How are we supposed to answer you if we don't have details?|||MS SQL 2000 with Service Pack 3 is a minimum requirement to run on
Win2003.
Further problem, please explain it in detail
kennethgoh
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message735124.html

Help:how can i use sql server 2000 in Windows 2003

Windows 2003,sql server
How can i connect sql server 2000?
Unable to get a linked server working.
Please help me,thanks
gouki
-- gouki wrote: --
Windows 2003,sql server
How can i connect sql server 2000?
Unable to get a linked server working.
Please help me,thanks
gouki
You should explain it in details, how did you installed it and how did you set it up?
How are we supposed to answer you if we don't have details?
|||MS SQL 2000 with Service Pack 3 is a minimum requirement to run on Win2003.
Further problem, please explain it in detail

HELP: Linked SQL servers issue - cannot call ServerName.Database.dbo.TableName

We recently upgraded our production SQL Server to SQL Server 64-bit
Yukon on Windows 2K3 however our development SQL server is still SQL
Server v7.
We have linked servers.
The problem is all the stored procedures fail as well as T-SQL
commands in SQL Query Analyzer because we may no longer call the
server with the 4-part naming convention (server.database.dbo.table)
while OPENQUERY still works such as:
FAILURE:
SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
FROM SERVER2.DATABASE2.dbo.TABLE2
WHERE LRSN=34534534
OPENQUERY WORKS:
SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
FROM
OPENQUERY(SERVER2, 'SELECT * FROM DATABASE2.dbo.TABLE2 WHERE
LRSN=34534534')
The failure message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not
give any information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IUnknown::QueryInterface returned 0x80004005: The provider did not
give any information about the error.].
Any help is appreciated!On your new server make sure the remote account is the
same and the password is he same. Also check your remote
query time out length
>--Original Message--
>
>We recently upgraded our production SQL Server to SQL
Server 64-bit
>Yukon on Windows 2K3 however our development SQL server
is still SQL
>Server v7.
>We have linked servers.
>The problem is all the stored procedures fail as well as
T-SQL
>commands in SQL Query Analyzer because we may no longer
call the
>server with the 4-part naming convention
(server.database.dbo.table)
>while OPENQUERY still works such as:
>
>FAILURE:
>SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
>FROM SERVER2.DATABASE2.dbo.TABLE2
>WHERE LRSN=34534534
>
>OPENQUERY WORKS:
>SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
>FROM
>OPENQUERY(SERVER2, 'SELECT * FROM DATABASE2.dbo.TABLE2
WHERE
>LRSN=34534534')
>
>The failure message is:
>Server: Msg 7399, Level 16, State 1, Line 1
>OLE DB provider 'SQLOLEDB' reported an error. The
provider did not
>give any information about the error.
>OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>IUnknown::QueryInterface returned 0x80004005: The
provider did not
>give any information about the error.].
>
>Any help is appreciated!
>.
>