ODBC--update on a linked table table_name failed

Hi all,

Hope someone can help me with this one. Thanks in advance.

We have several Access 97 .mdb front-end applications running against an SQL Server 7 database on NT 4.0. I know, welcome to the way back machine. Any way, all was well and running great. Then the user attempted to run the application, it had been successfully run several times earlier in the day, and received the following error: "ODBC--update on linked table 'table_name' failed (Error 3157)". Everything I know suggests that this would be an MDAC version issue except for the abrupt change. From everything I can tell nothing was altered on the hosting server and we are running MDAC 2.5. I have performed the following tests using several different client systems, 98 and 2K, to no avail.

1. Cycled the power on the client and server systems.

2. Opened the linked table in data view mode and attempted to update a single column within a single row.

3. Created new DSN and re-linked tables.

4. Reloaded MDAC 2.5 on both client and server systems.

This only affects Access 97 clients though. If I create an Access 2K database and link to the same server/database/table the updates work just fine.

Any suggestions would be greatly appreciated.

Thanks,
RickHave you tried to update it to Mdac 2.7. This might resolve the issue.|||Originally posted by oj_n
Have you tried to update it to Mdac 2.7. This might resolve the issue.

I'm in the process of testing with the latest MDAC and ODBC drivers now. What I'm realy curious about though is the fact that this was running fine and then just quit. I have suspicions that something was altered on the server but I can't discover what it was and can't get anyone to admit to it. {;^>}

I know when I talk about Access 97, SQL 7 and MDAC 2.5 that it's all history. It's just the nature of the company I'm doing the work for. I'm just a contractor here. We have just migrated their, near 1 GB, Access 97 application/database to SQL server 7, 7 due to licensing issues, and Access 2000. However some front-end apps are still in Access 97. So they are pretty far behind the technology curve.

Thanks for the suggestion; I'll let you know how it turns out.

Thanks,
Rick|||Just in case anyone has the same problem described in my original post:

I discovered the cause and, as I suspected, it was from someone making a change to the SQL Server configuration. The User Option option had been modified to turn the NOCOUNT attribute on. Once I found this I was able to duplicate or fix the problem on a test SQL Server instance. I assume it has to do with Access 97 requireing a row count be returned from SQL Server through ODBC. Don't know exactly why.... yet.

Thanks,
Rick|||you can have better peformance, better flexibility, easier development

and a solution that will work for the next 10 years

if you just throw out Access 97 and convert everything to Access 2002 ADP Format.

Its a thin client to SQL Server.

and it ROCKS!!!

odbc-timeout error on a linked table from Access2k

Hi.
I have the problem that some records in a ms sqlserver table is unable
to update from Access.
I get the error message odbc-time out error in linked table.....
I tried to copy this table to another database, where none but me was
aktive.
And then it worked quit ok when I try to save the record.

I am thinking about if there is some trigger och restraint that I
don't know about, but I don't know how to se all of this in the
database.

I have changed the odbc-timeout settings in Access but that doesn't
seed to make any difference
So what's the problem?
Does anyone have an idea?
/regards BigOlle(olle@.ylm.se) writes:

Quote:

Originally Posted by

I have the problem that some records in a ms sqlserver table is unable
to update from Access.
I get the error message odbc-time out error in linked table.....
I tried to copy this table to another database, where none but me was
aktive.
And then it worked quit ok when I try to save the record.
>
I am thinking about if there is some trigger och restraint that I
don't know about, but I don't know how to se all of this in the
database.
>
I have changed the odbc-timeout settings in Access but that doesn't
seed to make any difference

Could be a blocking issue. You can check this in Query Analyzer by
running sp_who2. If there is a non-zero value in the Blk column,
that spid is blocking the spid on that line. With the rest of the
information from sp_who2, you may be able to identify the blocker.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

ODBC Update on a linked table failed

I created a form in Access 2003 who's recordsource is an ODBC linked table from
SQL Server 2000. The form has a subform that lists all the records in that
table. The form also has all the fields from that table as separate controls.
I have a command button to add new records, which works without any
problems. When the user clicks on any record in the subform, I fill in the
controls on the form, and this works fine. But If I try to edit anything in
the record, when it tries to update the record, I get this message , ODBC
Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL Server
Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
appears. If I remove the subform or if I use a table thats not linked for the
recordsource, it works fine. What am I doing wrong?
You probably have some locking or blocking issues based on
how the form and recordsources are designed. You can view
whatever SQL statement are being executed by running a trace
or running Profiler. In terms of optimizing your form
itself, you would probably want to post that on one of the
Access newsgroups. Try one of these:
microsoft.public.access.formscoding
microsoft.public.access.odbcclientsvr
-Sue
On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:

>I created a form in Access 2003 who's recordsource is an ODBC linked table from
>SQL Server 2000. The form has a subform that lists all the records in that
>table. The form also has all the fields from that table as separate controls.
>I have a command button to add new records, which works without any
>problems. When the user clicks on any record in the subform, I fill in the
>controls on the form, and this works fine. But If I try to edit anything in
>the record, when it tries to update the record, I get this message , ODBC
>Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL Server
>Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
>appears. If I remove the subform or if I use a table thats not linked for the
>recordsource, it works fine. What am I doing wrong?
|||Sorry, I'm not familiar with these. How do you run a trace or Profiler?
It is definitely a locking issue, because I tried splitting the form into
two separate forms, clearing out the subform list before opening up the new
form for editing, and it works fine. I just don't understand why the subform
that has the record list is locking the record. The subform properties are
RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
RecordsetType=Snapshot, RecordLocks=NoLocks.
"Sue Hoegemeier" wrote:

> You probably have some locking or blocking issues based on
> how the form and recordsources are designed. You can view
> whatever SQL statement are being executed by running a trace
> or running Profiler. In terms of optimizing your form
> itself, you would probably want to post that on one of the
> Access newsgroups. Try one of these:
> microsoft.public.access.formscoding
> microsoft.public.access.odbcclientsvr
> -Sue
> On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>
|||The subform design, properties, etc would probably be better addressed
in a Microsoft Access newsgroup.
To run profiler, from the start button go to the SQL Server program
group and you will find profiler. You can find more information on
using the tool in Books Online (the SQL Server help file).
-Sue
On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sorry, I'm not familiar with these. How do you run a trace or Profiler?
>It is definitely a locking issue, because I tried splitting the form into
>two separate forms, clearing out the subform list before opening up the new
>form for editing, and it works fine. I just don't understand why the subform
>that has the record list is locking the record. The subform properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks.
>
>"Sue Hoegemeier" wrote:
|||I actually posted it in both newsgroups. I wasn't sure which was causing the
problem, SQL or Access. You were the first to respond. Thanks so much for
your help.
"Sue Hoegemeier" wrote:

> The subform design, properties, etc would probably be better addressed
> in a Microsoft Access newsgroup.
> To run profiler, from the start button go to the SQL Server program
> group and you will find profiler. You can find more information on
> using the tool in Books Online (the SQL Server help file).
> -Sue
> On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>
|||Yeah...it's not as cut and dry as it might seem. It's timing
out due to locking, blocking type of issues in SQL Server
but then again that would be related to how the form and
subform is designed. What you can do is use profiler or even
just execute sp_who2, sp_lock, query sysprocesses when you
hit the issue. As long as it's timing out, you should be
able to capture it with those (but Profiler would be
better). From there, you would want to determine what is
being executed, what part of what action on the form,
subform is leading to the problem. And then from there...you
can look at the design. Most of it will be related to how
the forms are populated, what kind of binding and that type
of thing. I can't remember enough Access off the top of my
head to give you enough direction on how you may want to
rethink the form, subform design.
-Sue
On Tue, 21 Nov 2006 09:19:01 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I actually posted it in both newsgroups. I wasn't sure which was causing the
>problem, SQL or Access. You were the first to respond. Thanks so much for
>your help.
>"Sue Hoegemeier" wrote:

ODBC Update on a linked table failed

I created a form in Access 2003 who's recordsource is an ODBC linked table f
rom
SQL Server 2000. The form has a subform that lists all the records in that
table. The form also has all the fields from that table as separate controls
.
I have a command button to add new records, which works without any
problems. When the user clicks on any record in the subform, I fill in the
controls on the form, and this works fine. But If I try to edit anything in
the record, when it tries to update the record, I get this message , ODBC
Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL
Server
Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
appears. If I remove the subform or if I use a table thats not linked for th
e
recordsource, it works fine. What am I doing wrong?You probably have some locking or blocking issues based on
how the form and recordsources are designed. You can view
whatever SQL statement are being executed by running a trace
or running Profiler. In terms of optimizing your form
itself, you would probably want to post that on one of the
Access newsgroups. Try one of these:
microsoft.public.access.formscoding
microsoft.public.access.odbcclientsvr
-Sue
On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:

>I created a form in Access 2003 who's recordsource is an ODBC linked table
from
>SQL Server 2000. The form has a subform that lists all the records in that
>table. The form also has all the fields from that table as separate control
s.
>I have a command button to add new records, which works without any
>problems. When the user clicks on any record in the subform, I fill in the
>controls on the form, and this works fine. But If I try to edit anything in
>the record, when it tries to update the record, I get this message , ODBC
>Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL
Server
>Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
>appears. If I remove the subform or if I use a table thats not linked for t
he
>recordsource, it works fine. What am I doing wrong?|||Sorry, I'm not familiar with these. How do you run a trace or Profiler?
It is definitely a locking issue, because I tried splitting the form into
two separate forms, clearing out the subform list before opening up the new
form for editing, and it works fine. I just don't understand why the subform
that has the record list is locking the record. The subform properties are
RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
RecordsetType=Snapshot, RecordLocks=NoLocks.
"Sue Hoegemeier" wrote:

> You probably have some locking or blocking issues based on
> how the form and recordsources are designed. You can view
> whatever SQL statement are being executed by running a trace
> or running Profiler. In terms of optimizing your form
> itself, you would probably want to post that on one of the
> Access newsgroups. Try one of these:
> microsoft.public.access.formscoding
> microsoft.public.access.odbcclientsvr
> -Sue
> On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>|||The subform design, properties, etc would probably be better addressed
in a Microsoft Access newsgroup.
To run profiler, from the start button go to the SQL Server program
group and you will find profiler. You can find more information on
using the tool in Books Online (the SQL Server help file).
-Sue
On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sorry, I'm not familiar with these. How do you run a trace or Profiler?
>It is definitely a locking issue, because I tried splitting the form into
>two separate forms, clearing out the subform list before opening up the new
>form for editing, and it works fine. I just don't understand why the subfor
m
>that has the record list is locking the record. The subform properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks.
>
>"Sue Hoegemeier" wrote:
>|||I actually posted it in both newsgroups. I wasn't sure which was causing the
problem, SQL or Access. You were the first to respond. Thanks so much for
your help.
"Sue Hoegemeier" wrote:

> The subform design, properties, etc would probably be better addressed
> in a Microsoft Access newsgroup.
> To run profiler, from the start button go to the SQL Server program
> group and you will find profiler. You can find more information on
> using the tool in Books Online (the SQL Server help file).
> -Sue
> On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>|||Yeah...it's not as cut and dry as it might seem. It's timing
out due to locking, blocking type of issues in SQL Server
but then again that would be related to how the form and
subform is designed. What you can do is use profiler or even
just execute sp_who2, sp_lock, query sysprocesses when you
hit the issue. As long as it's timing out, you should be
able to capture it with those (but Profiler would be
better). From there, you would want to determine what is
being executed, what part of what action on the form,
subform is leading to the problem. And then from there...you
can look at the design. Most of it will be related to how
the forms are populated, what kind of binding and that type
of thing. I can't remember enough Access off the top of my
head to give you enough direction on how you may want to
rethink the form, subform design.
-Sue
On Tue, 21 Nov 2006 09:19:01 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I actually posted it in both newsgroups. I wasn't sure which was causing th
e
>problem, SQL or Access. You were the first to respond. Thanks so much for
>your help.
>"Sue Hoegemeier" wrote:
>

ODBC to linked server

I have created a linked server using an OLE DB service in a SQL Server 2000
database running on Windows NT 4.
I can see all the tables and views of the linked server in Enterprise
manager, but when I go to a client machine and try to setup a DSN to the
linked server using an SQL Server driver all I can see for a default
database is the example Microsoft databases (Northwind, Pub, etc.). How do I
get the ODBC driver to use the linked server as a default database?
Any help would be greatly appreciated.ODBC cannot "see" linked servers. You can issue queries using the fully qua
lified server.db.dbo.tablename to be able to select from the table using a p
XXX-through query

ODBC Timeout Linked to Optimizer?

I have been struggling with an MSSQL problem for a couple of weeks and
hope this is something someone else has already solved.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the central table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much random fashion -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
"feature" of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable and random waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred
Try updating your statistics, preferably with the FULLSCAN option. You may
also need to tune your indexes.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||ITFred wrote:
> *
> So I am still looking for a "proper" solution from one of you out
> there, or
> at least an acknowledgment that this is a behavioral issue with
> SQL2K that needs to be addressed.
> - ITFred *
I've been running into the exact same issue recently with my database.
My configuration is a MS Access/VBA front end with MSSQL as the
backend, and I see similar behavior when I allow ODBC to pick the
indices to use.
One workaround I've found for this problem is to encapsulate the query
into a stored procedure, then call the stored procedure via ODBC in
pass-through mode with something like this:
EXECUTE sp_my_query
This takes ODBC out of the picture as far as indexing goes, and seems
to resolve the random index botching which frequently occurs.
Drawbacks to this workaround are that it can't be easily used with
queries which are dynamically generated, and it also places queries in
a second location outside of your client code where you might have a
debugging error.
Its not the most elegant solution, and may not work for you at all if
you are constructing the SQL string dynamically in VB, but maybe it can
give you some inspiration on what else you can try (i.e. dynamically
call CREATE and DROP PROCEDURE statements to construct a customized
stored procedure, run it, and kill it when you're done, et. al.).
Hope this helps.
Jim
Jim Tran
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message163387.html
|||I ran into the same problem recently, and the reason was the way the SQL
queries were written.
In order to have the optimzier process a query correctly, the where-clause
has to follow a few rules:
1. dont use OR to link conditions
2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
3. dont use conditions that begin with wildcards (like %)
4. dont use string functions like "substring()"
If you have any of these in the where-clause, the optimizer cant use
indexes and has to perform a table scan. This works as long as a table is
small enough, but once it has too many rows you run into timeouts.
hope this helps..
A.
|||"Cindy Gross (MSFT)" <cgross@.online.microsoft.com> wrote in message
news:bgpaEVoFEHA.3688@.cpmsftngxa06.phx.gbl...
> Try updating your statistics, preferably with the FULLSCAN option. You may
> also need to tune your indexes.
>
Because this application is mission-critical, we have been doing index
regeneration during slow times on weekends as a part of the standard
database maintenance setup. As a double-check I went in with STATS_DATE and
verified that the stats were in fact getting rebuilt at that time, and it
looks OK. I know it is not doing a FULLSCAN during these times, but
manually executing an update with FULLSCAN in the past does not seem to have
been of any help especially as the failure is intermittent: executing the
same query 20 times over the same ODBC link from the same machine might
produce one or two timeouts, and the rest are normal timings.
Working with the Execution Plan tool does not seem to address the problem,
since the optimizer always seems to behave itself when I run a query
(cut-and-pasted from the app) in the Query Analyzer. It always picks
suitable indices.
The same seems to apply to the Index Tuning Wizard -- I would assume that,
since it watches primarily for the occurrence of table scans, and those
scans only occur a percentage of the time for a given query, and since there
is already a suitable index in place which should have been used but wasn't,
it cannot make any suggestion. After all, the suggestion would be to create
the index that already exists!
Still very frustrating......
|||"A. Finkler" <finklerNO_SPAM@.gmx.de> wrote in message
news:94BE566F4finklerNOSPAMgmxde@.192.168.10.250...
> I ran into the same problem recently, and the reason was the way the SQL
> queries were written.
> In order to have the optimzier process a query correctly, the where-clause
> has to follow a few rules:
> 1. dont use OR to link conditions
> 2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
> 3. dont use conditions that begin with wildcards (like %)
> 4. dont use string functions like "substring()"
> If you have any of these in the where-clause, the optimizer cant use
> indexes and has to perform a table scan. This works as long as a table is
> small enough, but once it has too many rows you run into timeouts.
> hope this helps..
> A.
>
Useful suggestions to be sure. But I have spent a lot of time with the
Execution Plan tool trying to avoid such issues. Of course it is not always
possible to follow all of these rules (#1 and #2 are typically mutually
exclusive for certain queries) but I would still expect the optimizer to
behave consistently whether the query originates in the Query Analyzer or
over an ODBC connection. For that matter, it should be consistent if the
identical query is issued multiple times. As I mentioned, I have had some
success by using hints, but hints specifically cannot be used where
column-OR'ing is being used, and besides I consider it really bad form to
have the wording of the query defined by the availability and
characteristics of the indices -- makes for some serious code maintenance
issues.
Nevertheless I will continue to explore this... thank you!

ODBC Timeout Linked to Optimizer?

I have been struggling with an MSSQL problem for a couple of weeks and
hope this is something someone else has already solved.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the central table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much random fashion -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
"feature" of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable and random waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFredTry updating your statistics, preferably with the FULLSCAN option. You may
also need to tune your indexes.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||

quote:

---

Originally posted by ITFred

So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred

---

I've been running into the exact same issue recently with my database. My c
onfiguration is a MS Access/VBA front end with MSSQL as the backend, and I s
ee similar behavior when I allow ODBC to pick the indices to use.
One workaround I've found for this problem is to encapsulate the query into
a stored procedure, then call the stored procedure via ODBC in pass-through
mode with something like this:
EXECUTE sp_my_query
This takes ODBC out of the picture as far as indexing goes, and seems to res
olve the random index botching which frequently occurs.
Drawbacks to this workaround are that it can't be easily used with queries w
hich are dynamically generated, and it also places queries in a second locat
ion outside of your client code where you might have a debugging error.
Its not the most elegant solution, and may not work for you at all if you ar
e constructing the SQL string dynamically in VB, but maybe it can give you s
ome inspiration on what else you can try (i.e. dynamically call CREATE and D
ROP PROCEDURE statements to construct a customized stored procedure, run it,
and kill it when you're done, et. al.).
Hope this helps.
Jim|||I ran into the same problem recently, and the reason was the way the SQL
queries were written.
In order to have the optimzier process a query correctly, the where-clause
has to follow a few rules:
1. dont use OR to link conditions
2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
3. dont use conditions that begin with wildcards (like %)
4. dont use string functions like "substring()"
If you have any of these in the where-clause, the optimizer cant use
indexes and has to perform a table scan. This works as long as a table is
small enough, but once it has too many rows you run into timeouts.
hope this helps..
A.|||With respect to the issue brought up by A. Finkler above, that's actually a
separate cause of index failure through ODBC (though still noteworthy).
In my most recent situation, my query had the following construction:
UPDATE [Table A]
SET [Table A].[Column 1] = X
FROM
[Table A] INNER JOIN
([Table B] INNER JOIN
[Table C] ON [Table B].[Column G] = [Table C].[Column H])
ON [Table A].[Column P1] = [Table B].[Column P2] AND
[Table A].[Column Q1] = [Table B].[Column Q2] AND
[Table A].[Column R1] = [Table B].[Column R2] AND
[Table A].[Column S1] = [Table C].[Column S2]
Running this query through the Query Analyzer always produced an appropriate
indexing scheme that resulted in fast performance, but launching the exact
same query string through ODBC would randomly cause a table scan.
Jim|||"Cindy Gross (MSFT)" <cgross@.online.microsoft.com> wrote in message
news:bgpaEVoFEHA.3688@.cpmsftngxa06.phx.gbl...
> Try updating your statistics, preferably with the FULLSCAN option. You may
> also need to tune your indexes.
>
Because this application is mission-critical, we have been doing index
regeneration during slow times on weekends as a part of the standard
database maintenance setup. As a double-check I went in with STATS_DATE and
verified that the stats were in fact getting rebuilt at that time, and it
looks OK. I know it is not doing a FULLSCAN during these times, but
manually executing an update with FULLSCAN in the past does not seem to have
been of any help especially as the failure is intermittent: executing the
same query 20 times over the same ODBC link from the same machine might
produce one or two timeouts, and the rest are normal timings.
Working with the Execution Plan tool does not seem to address the problem,
since the optimizer always seems to behave itself when I run a query
(cut-and-pasted from the app) in the Query Analyzer. It always picks
suitable indices.
The same seems to apply to the Index Tuning Wizard -- I would assume that,
since it watches primarily for the occurrence of table scans, and those
scans only occur a percentage of the time for a given query, and since there
is already a suitable index in place which should have been used but wasn't,
it cannot make any suggestion. After all, the suggestion would be to create
the index that already exists!
Still very frustrating......|||"A. Finkler" <finklerNO_SPAM@.gmx.de> wrote in message
news:94BE566F4finklerNOSPAMgmxde@.192.168.10.250...
> I ran into the same problem recently, and the reason was the way the SQL
> queries were written.
> In order to have the optimzier process a query correctly, the where-clause
> has to follow a few rules:
> 1. dont use OR to link conditions
> 2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
> 3. dont use conditions that begin with wildcards (like %)
> 4. dont use string functions like "substring()"
> If you have any of these in the where-clause, the optimizer cant use
> indexes and has to perform a table scan. This works as long as a table is
> small enough, but once it has too many rows you run into timeouts.
> hope this helps..
> A.
>
Useful suggestions to be sure. But I have spent a lot of time with the
Execution Plan tool trying to avoid such issues. Of course it is not always
possible to follow all of these rules (#1 and #2 are typically mutually
exclusive for certain queries) but I would still expect the optimizer to
behave consistently whether the query originates in the Query Analyzer or
over an ODBC connection. For that matter, it should be consistent if the
identical query is issued multiple times. As I mentioned, I have had some
success by using hints, but hints specifically cannot be used where
column-OR'ing is being used, and besides I consider it really bad form to
have the wording of the query defined by the availability and
characteristics of the indices -- makes for some serious code maintenance
issues.
Nevertheless I will continue to explore this... thank you!

ODBC SQL Driver Timeout Expired from MS Access

Hi

I'm running a machine with windows xp pro sp2. I have a MS Access 2000 database on that machine. The database contains 1 odbc linked table
and 1 Access query.

The odbc linked table is connected to a MS SQL Server 2000 database with a ODBC SQL Server System DSN.

The table on SQL Server to which the table in Access is linked has about
3 million + rows.

When I open the ODBC linked table in Access, it opens without a problem.

However, if I use even so much as one criterion such as "where company = ABC" for example, then it "thinks" for a while and returns the dreaded:
[Microsoft][ODBC SQL Driver] Timeout Expired (#0).

I have to use MS Access 2000 as the front end for this SQL Database table.

How do I get around the timeout issue. Is there a setting in SQL?

Please keep in mind that
I can't write the queries as Store procs because the requirement of the client is that there be one Linked Access table that the users can use
to write various access queries. No user are not techies.

please helpread this post:
http://www.mcse.ms/message1582144.html

I don't test.
Tell me.
Bye.

ODBC security problem

Hi, I've a Access 2003 program with linked tables to a sqlexpress configured
with mixed mode security.
From clients XP no problem.
From clients 2000 MSACCESS don't get the right configuration from ODBC
source, because try to conect with Windows Authentication Mode security, but
the source is configurated with mixed mode. If I refresh the linked tables
and change the password, works fine, but when I close MSACCESS and open
again, fail.
Anybody knows where's the bug? In ODBC driver? MSACCESS?You could be hitting an MDAC bug where the authentication
mode setting is ignored and windows authentication is always
used. Refer to:
FIX: SQL Server ODBC Driver Ignores Authentication Setting
http://support.microsoft.com/?id=279526
You can use component checker to verify the version and MDAC
installation on any of the clients. You can download
component checker and MDAC versions from this site:
http://msdn.microsoft.com/data/ref/mdac/downloads/
-Sue
On Fri, 18 Aug 2006 11:27:01 -0700, Negri
<Negri@.discussions.microsoft.com> wrote:

>Hi, I've a Access 2003 program with linked tables to a sqlexpress configure
d
>with mixed mode security.
>From clients XP no problem.
>From clients 2000 MSACCESS don't get the right configuration from ODBC
>source, because try to conect with Windows Authentication Mode security, bu
t
>the source is configurated with mixed mode. If I refresh the linked tables
>and change the password, works fine, but when I close MSACCESS and open
>again, fail.
>Anybody knows where's the bug? In ODBC driver? MSACCESS?|||Thanks Sue, I'll try it next monday and tell you.
"Sue Hoegemeier" wrote:

> You could be hitting an MDAC bug where the authentication
> mode setting is ignored and windows authentication is always
> used. Refer to:
> FIX: SQL Server ODBC Driver Ignores Authentication Setting
> http://support.microsoft.com/?id=279526
> You can use component checker to verify the version and MDAC
> installation on any of the clients. You can download
> component checker and MDAC versions from this site:
> http://msdn.microsoft.com/data/ref/mdac/downloads/
> -Sue
> On Fri, 18 Aug 2006 11:27:01 -0700, Negri
> <Negri@.discussions.microsoft.com> wrote:
>
>

ODBC readonly (viewonly) linked tables....

Hi,

I have a prod database (main bread & bread DB) and have a small access
database that sales team is using... I created a login inside SQL 2000
and gave db_read permission and SELECT permissions... and public.
Role of public is EXEC store procs and view some systables...
I linked those tables that I gave permissions through ODBC link into
access db with the user that I created inside SQL as readonly user...
but to my surprise when I ran a insert query from access on SQL
tables... I was able to update and insert data... if my ODBC link is
only for readonly.. why was I able to unpdate & insert data into SQL
table?
I don't want to give write/update/insert permissions for SQL tables to
sales team... or anyone outside SQL 2000 server DB.
Is there any easy way to create viewonly users inside SQL?
I created users like this: security -logins -new login...
select none serverrole.. db access (bread & butter DB)

Thank you,
hj

******
Pardon my English...Hitesh Joshi (hitesh287@.gmail.com) writes:

Quote:

Originally Posted by

I have a prod database (main bread & bread DB) and have a small access
database that sales team is using... I created a login inside SQL 2000
and gave db_read permission and SELECT permissions... and public.
Role of public is EXEC store procs and view some systables...
I linked those tables that I gave permissions through ODBC link into
access db with the user that I created inside SQL as readonly user...
but to my surprise when I ran a insert query from access on SQL
tables... I was able to update and insert data... if my ODBC link is
only for readonly.. why was I able to unpdate & insert data into SQL
table?

Exactly how does the connection information for the ODBC link look like?
(Minus any password of course).

Exactly how did you set up this user as a readonly user?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

odbc problems with Linked server in sql 2005

Hi guys,

I've a Linked server en sql 2000 with oledb provider for odbc drivers (Progress software) to do some queries, and works fine, I m trying to do the same in sql 2005, i set up exactly as i do in sql 2000 but when I try to view the tables: there are nothing the list is missing and obviosly when i try to query, I got errors:

OLE DB provider "MSDASQL" for linked server "test" returned message "[DataDirect-Technologies][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed.".

OLE DB provider "MSDASQL" for linked server "test" returned message "[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Access denied(Authorisation failed)".

OLE DB provider "MSDASQL" for linked server "test" returned message "[DataDirect-Technologies][ODBC PROGRESS

ex:

works perfectly in sql 2000, not in 2005

SELECT * FROM OPENQUERY (MY_LINKED_SERVER, 'SELECT * FROM AFN_cotizaseguro_rmt')

some body knows if i have to set up additional stuff in sql 2005?

Hi

This seems to be a discussion on ProgressTalk that refers to your problem:

http://www.progresstalk.com/showthread.php?t=103352

If the connection works in the ODBC administrator Test Connection then they seem to suggest checking the security context setting in the security page of the linked servers settings in SQL Management studio.

I hope this is some use to you.

|||

Thank u Dhericean, you really help me, now I 'm working with my linked servers in sql 2005, it was just a click in the security, awesome !!!

Regards

ODBC linked table support in SQL SERVER 2005

Hi,

We still have some legacy applications that use ODBC linked tables to access SQL SERVER 2000.

Questions: Will these legacy applications still function with SQL SERVER 2005 / SQL SERVER 2005 Express? In order to relink the tables within the Access front-end do we need to install the new SQL Native Client that contains the new ODBC driver for SQL SERVER 2005?

Any input would be appreciated.

ThanksThe existing SQL Server 2000 ODBC driver can be used with SQL Server 2005, there is no need to update existing clients just because you upgraded the server. In general you only have to upgrade to SQL Native Client when you change application code to take advantage of new features in SQL Server 2005.|||What version of the SQL Server 2000 ODBC Driver is supported for accessing SQL Server 2005 databases? Thanks.|||

2.5 and later are supported with SQL Server 2005.

In general, since MDAC is now part of Windows, if you're up to date on Windows service packs you should have no problems. For Windows 2000 and later this means you'll have 2.81 installed.

ODBC linked table support in SQL SERVER 2005

Hi,

We still have some legacy applications that use ODBC linked tables to access SQL SERVER 2000.

Questions: Will these legacy applications still function with SQL SERVER 2005 / SQL SERVER 2005 Express? In order to relink the tables within the Access front-end do we need to install the new SQL Native Client that contains the new ODBC driver for SQL SERVER 2005?

Any input would be appreciated.

ThanksThe existing SQL Server 2000 ODBC driver can be used with SQL Server 2005, there is no need to update existing clients just because you upgraded the server. In general you only have to upgrade to SQL Native Client when you change application code to take advantage of new features in SQL Server 2005.

|||What version of the SQL Server 2000 ODBC Driver is supported for accessing SQL Server 2005 databases? Thanks.|||

2.5 and later are supported with SQL Server 2005.

In general, since MDAC is now part of Windows, if you're up to date on Windows service packs you should have no problems. For Windows 2000 and later this means you'll have 2.81 installed.

ODBC Linked Server Security Problem - Anybody?

Hi all,
I'll provide as much detail as I can. I have read many messages
regarding this topic, but I just can't seem to follow them in my
specific case. Sorry I'm so dumb...
Here goes:
1) I have a Microsoft SQL Server 2005 installed in mixed mode (NT
security plus standard SQL security). I don't know much about NT
security as I've never used it in connection with SQL Server.
2) I have set up a linked server called Max which uses the provider
OLEDB for ODBC.
3) I have a system DSN set up on the SQL Server to point to my ODBC
data source (A Maximizer CRM ODBC Driver, but I don't think this is
important)
4) On the security tab of the linked server, I have selected: For a
login not defined in the list above, connections will be made using
this security context. Below that, I specified the user ID and
password. The top box (server login mappings) is empty. Basically, I
want any user who runs a query to the linked server to use the user ID
and password I specified in the box below. No restrictions (at least
until I understand the security model a lot better than I do now)
So far, so good. I log in using a query tool using the standard SQL
Security ID I want to be using for this task in production later and
execute my stored procedure. Everything works. My stored procedure
connects to my remote (linked) server and does exactly what it should
do. No security issues at all...
So, I set up a "job" to run that stored procedure at certain times of
the day. That job fails every time. The error is:
"Executed as user: REPL Access to the remote server is denied because
the current securty context is not trusted.[SQLSTATE 42000] ERROR:
15274 The step failed."
By the way, REPL is the standard SQL Server security user ID I want
this job to run as. I know I must be very close to getting this to
work, but as I read the articles from Microsoft, my eyes just glaze
over and I start looking for coffee...
In your reply, please be very specific. If I need to do something to
my NT Security or something like that, please try to provide the
steps...
Also, I should tell you that I do not have a Windows 2000 or Windows
Server 2003 domain. I do have a PDC (Primary domain controller)
running on a Windows NT 4.0 Server. (On a side note, I would be happy
to upgrade my PDC to my Windows 2003 R2 server, but I don't know how to
do that either)
Anyone want to help out a dummy?
Thanks in advance!
-Rick
datamovr at yahoo dot comOne thing I forgot to mention, the job fails at exactly the point in
the stored procedure where it tries to insert a row into a linked
server table. This is the only line in the code that connects to the
linked server. The error I'm getting is certainly related to the
linked server.

ODBC Linked Server & DSN

Hi,
Short version of question:
For linked servers to ODBC DSN data sources, does the DSN have to be setup
on the same windows server as the sql server 2005 instance? Or is only
requirment that the DSN is setup from the workstaing that is luanching SSMS?
Longer Background for question:
I'm attempting to setup a linked server in SQL Server 2005 to an ODBC
datasource which is Centura SQLbase database.
On my workstation, I have a DSN setup with the odbc drivers to connect to
the SQLbase db. The driver was "Centura SQLBase 3.60 32-bit Driver -NT &
Win95"
I can successfully import that tables from SQLBase using Access.
I'm using the SQL Server Managment Studio from my desktop to connect to the
SQL 2005 Server on a Windows 2003 Server.
When testing the Linked server by using a query, I use the following syntax
in a query in SSMS:
SELECT LastName, FirstName FROM LINK2SQLBASE.COMPANYDB.dbo.tblEmployee
I receive an Error:
*OLE DB provider "MSDASQL" for linked server... returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no
default
driver specified".
*Msg 7303, Level 16, State 1, Line 1
*Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server...
Again, i'm using the SSMS from my workstation that has the DSN setup and
works fine when importing from access. I do NOT have the DSN or drivers on
the actual windwows 2003 Server where the SQL Sever 2005 instance is on.
Is that my promblem? Do I need the DSN on the actual windows 2003 server, or
should I be albe to use the DSN from my workstation?
Thanks in advanced!On Fri, 12 Jan 2007 14:11:04 -0800,
labsRcoolcommunitynospan@.discussions.microsoft.com wrote:
You will need to setup the driver and DSN on the SQL Server machine.
When you issue a query to a linked server, the client machine only knows
that it is talking to a SQL Server. The SQL Server then makes the request
to the remote data source. So the SQL Server has to have all the required
drivers, DSNs and privileges required to access the remote data source.
Darren Gosbell
SQL Server MVP

> Hi,
> Short version of question:
> For linked servers to ODBC DSN data sources, does the DSN have to be
setup
> on the same windows server as the sql server 2005 instance? Or is only
> requirment that the DSN is setup from the workstaing that is luanching
SSMS?
>
> Longer Background for question:
> I'm attempting to setup a linked server in SQL Server 2005 to an ODBC
> datasource which is Centura SQLbase database.
> On my workstation, I have a DSN setup with the odbc drivers to connect to
> the SQLbase db. The driver was "Centura SQLBase 3.60 32-bit Driver -NT &
> Win95"
> I can successfully import that tables from SQLBase using Access.
> I'm using the SQL Server Managment Studio from my desktop to connect to
the
> SQL 2005 Server on a Windows 2003 Server.
> When testing the Linked server by using a query, I use the following
syntax
> in a query in SSMS:
> SELECT LastName, FirstName FROM LINK2SQLBASE.COMPANYDB.dbo.tblEmployee
> I receive an Error:
> *OLE DB provider "MSDASQL" for linked server... returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and no[/vbcol
]
default[vbcol=seagreen]
> driver specified".
> *Msg 7303, Level 16, State 1, Line 1
> *Cannot initialize the data source object of OLE DB provider "MSDASQL"
for
> linked server...
> Again, i'm using the SSMS from my workstation that has the DSN setup and
> works fine when importing from access. I do NOT have the DSN or drivers
on
> the actual windwows 2003 Server where the SQL Sever 2005 instance is on.
> Is that my promblem? Do I need the DSN on the actual windows 2003 server,
or
> should I be albe to use the DSN from my workstation?
> Thanks in advanced!

ODBC In Stored Procedure

Hey guys,
I'm trying to get the column names from a table to which I'm connected
through OBDC. I don't want to use linked server. I get my data with
Openrowset.
I found the SQLDescribeCol and SQLColumns ODBC functions.
Can anybody tell me how to use those functions in a SQL Server stored
procedure?
Thanks a lotHi Pumkin,
should work as follows
SELECT colId, name,
FROM dbo.sysObjects
WHERE Id = OBJECT_ID('YourTableName')
ORDER BY ColId
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm|||Hey,
I should explain myself better... I need to get the column names of a
table given as parameter. There is COLUMN_NAME from INFORMATION_SCHEMA
for SQL Server. The problem is that my table is not on my server and is
not a SQL Server table either. I need to access it through ODBC.
Can anybody help? Thanks|||Pumkin wrote:
> Hey,
> I should explain myself better... I need to get the column names of a
> table given as parameter. There is COLUMN_NAME from INFORMATION_SCHEMA
> for SQL Server. The problem is that my table is not on my server and
> is not a SQL Server table either. I need to access it through ODBC.
> Can anybody help? Thanks
It is not possible to use the ODBC functions you mentioned from a stored
procedure which can only run queries via openrowset.
If the table is in a relational database, then it should be possible to run
a query against that database to get the column names. Without more specific
information, it is impossible to go into details. Maybe you should try a
newsgroup devoted to the rdbms you are linking to.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hy Bob,
Thanks for the reply. The database that I am connecting to in not
relational. But I found something now...
It works if I do "select * from table where 1=2"
But I was looking for something more dynamically build.|||Pumkin wrote:
> Hy Bob,
> Thanks for the reply. The database that I am connecting to in not
> relational. But I found something now...
> It works if I do "select * from table where 1=2"
> But I was looking for something more dynamically build.
I'm not sure how that helps you: your t-sql code is not going to be able to
read those column names. I was thinking that you needed your code to somehow
use those column names for something.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

ODBC in Stored Procedure

Hey guys,

I'm trying to get the column names from a table to which I'm connected
through OBDC. I don't want to use linked server. I get my data with
Openrowset.
I found the SQLDescribeCol and SQLColumns ODBC functions.
Can anybody tell me how to use those functions in a SQL Server stored
procedure?

Thanks a lotPumkin (PopClaudia@.gmail.com) writes:
> I'm trying to get the column names from a table to which I'm connected
> through OBDC. I don't want to use linked server. I get my data with
> Openrowset.
> I found the SQLDescribeCol and SQLColumns ODBC functions.
> Can anybody tell me how to use those functions in a SQL Server stored
> procedure?

You can't. Well, you could write an extended stored procedure or an
OLE object that you could call from a stored procedure, and that would
use these functions.

However, it would be more straight forward to query the system catalog
on the remote server directly.

What exactly are you trying to achieve?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx