Re: repetitive query to sysobjects in an ADP

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Well, 2:45 is a long time! Particularly when the same job on SQL2000 take
*only* 1 minute.

I don't have any comparaison point, so I cannot tell you more about this but
if you can do, you should open a technical incident with the technical
support at Microsoft.

It's possible that your servers are badly configured in some way but it's
also possible that this is normal. In the later case, your only option
would be to remove the relationships (and use triggers if you need some DRI)
or replace ADP with something else like .NET.

There is also Office 2007 that should be soon out but for any amelioration
of ADP under Office 2007, I wouln't hold my breath too long. (But we will
see in some months).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Dee_J" <DeeJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8F966521-066E-4A2D-A1C9-47671E7E0804@xxxxxxxxxxxxxxxx
Apparently the error "Error enumerating data servers" was caused due to a
missing dll from system32. The dll was dbnetlib.dll.

I made a few speed tests on two different servers running Win2003 sp1,
SQL2005 sp1:
The tests were:
1.ececuted localy and remotely
2.with tcp/ip and with named pipes
3.on the same db and on the same table
4.on two different servers of came cpu/ram (one is domain server)
5.using windows nt security as well as SQL server security

All tests gave the same result:
Time to open first table 2:45 !

I don't know what else to try.


"Sylvain Lafontaine" wrote:

You don't need the browser service; simply write down the name of the
server.

Named pipes are often troublesome, particularly over a LAN, so instead of
using the name of the server, you can also use its internal TCP/IP
address
to make use of the tcp/ip protocol instead of named pipes. Even on a
local
machine, I would go with TCP/IP instead of named pipes.

In your case, it's also possible than an alias has been defined but with
the
wrong protocol. Take a look at the SQL-Server Network Client Utility
(not
sure of the translation here).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Dee_J" <DeeJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BEB41F9E-1AF8-418E-9B9C-5D4899A0C5D7@xxxxxxxxxxxxxxxx
I tried opening the adp on the server and I get this error from Access:
"Unable to load communication module. Driver has not been correctly
installed"

I also tried making a new adp and when I hit the combo to select the
server
to connect to, I get this error: "Error enumerating data servers.
Enumerator
reports 'unspecified error'.

I haven't found the source of this problem yet. I was suspecting the
SQL
server browser service, but it appears it is running properly. I will
try
your other 3 suggestions as soon as I find out whats going on with the
first
one.

Thanks for your help

"Sylvain Lafontaine" wrote:

600 tables and 1000 relationships queried in one minute? I don't know
because I don't have any comparaison point but it's possible that
maybe
this
is normal in this range.

One way of testing this would be to open the ADP application directly
on
one
of the server. You could also try with an SQL-Server account instead
of
a
domain account.

You could also duplicate the queries yourself using another client
than
ADP
and see if this is an appropriate timing response.

Also, you've said that there is a difference for the first table but
is
there also a difference for the first client to open the ADP project
after a
reboot of the servers or a flushing of the buffers? Something in the
line
of:

use MyDataBase
go
checkpoint
go
dbcc dropcleanbuffers
dbcc freeproccache -- Not sure of this one here for this kind of
test.

....Then open the ADP application.

Of course, you must also check your servers for anything that could
lead
to
some performance problems.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Dee_J" <DeeJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3696370E-38B2-44DC-9AB3-157D051BC7EB@xxxxxxxxxxxxxxxx
Thanks Sylvain.
The servers are accessed through 100mbit LAN. The network setup is 1
domain
with two 2003 servers, and about 15 XP clients.

I ve seen somewhere on the net a similar question, where it was
discussed
that the querying of the sysobjects during the first opening of a
table,
is a
by design "fault" of ms access. Perhaps the 1 minute for SQL 2000 is
normal
considering that the database has about 600 tables and about 1000
relationships?


"Sylvain Lafontaine" wrote:

I don't explain why it's taking 3-4 minutes on SQL-Server 2005 but
only 1
minute with the 2000 version.

However, the fact that it takes 1 minute with SQL-Server 2000 is
already
symptomatic of some kind of network problem. Unless the schema of
your
database is incredibly huge, it shouldn't take any longer than one
or
two
seconds.

A second possibility would be some kind of permission problems, for
example
on the sysobjects table; with some internal time-outs as the result
(hence
the waiting time, ADP would still work after that because the
information
it tries to query is not really necessary if all of your code is
based
on
SP/Views/UDF) but the fact that the problem is also present on
SQL-Server
2005 make it hard to believe that this could be the source of your
problem.

Are you accessing these SQL-Servers through a VPN?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Dee_J" <DeeJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8FA75A4E-2CAF-459B-8B4D-410893289BA3@xxxxxxxxxxxxxxxx
When I open an SQL 2005 table from within the ADP, it takes 3-4
minutes
to
open it. This happens only once, and only for the first table
that
will
be
opened.
Looking in the profiler I see 100's of statements like this:

select object_name(sotblfk.id), user_name(sotblfk.uid),
object_name(sotblrk.id), user_name(sotblrk.uid) from
sysreferences
srfk,
sysobjects sofk, sysobjects sotblfk, sysobjects
sotblrk where srfk.constid = sofk.id and srfk.fkeyid = sotblfk.id
and
srfk.rkeyid = sotblrk.id and user_name(sofk.uid) = N'dbo' and
object_name(sofk.id) = N'FK_Purchase Order
Analysis_Purchase Order'

Apparently access is querying the system table for every single
relationship
in the database.
Does anybody have a solution for this?

By the way the same thing happens to the same db but on SQL 2000.
However
for some reason the querying is much faster in SQL2000 and the
first
table
opens up after about 1 minute.

Thanks
Dimitris











.

Relevant Pages

  • Re: repetitive query to sysobjects in an ADP
    ... Long live ADP. ... Sylvain Lafontaine ... It's possible that your servers are badly configured in some way but it's ... opens up after about 1 minute. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: repetitive query to sysobjects in an ADP
    ... Sylvain Lafontaine ... It's possible that your servers are badly configured in some way but it's ... DRI) or replace ADP with something else like .NET. ... opens up after about 1 minute. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: repetitive query to sysobjects in an ADP
    ... Another possible solution would be to connect instead to an empty database ... Sylvain Lafontaine, ing. ... It's possible that your servers are badly configured in some way but it's ... DRI) or replace ADP with something else like .NET. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: repetitive query to sysobjects in an ADP
    ... I tried opening the adp on the server and I get this error from Access: ... I get this error: "Error enumerating data servers. ... that the querying of the sysobjects during the first opening of a table, ... opens up after about 1 minute. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: repetitive query to sysobjects in an ADP
    ... You could also duplicate the queries yourself using another client than ADP ... you must also check your servers for anything that could lead to ... that the querying of the sysobjects during the first opening of a table, ... opens up after about 1 minute. ...
    (microsoft.public.access.adp.sqlserver)