Access Project (adp) with Sql Server 2000 - First Connection Latency

[Gerhard]

Hi folks,

after searching and searching (and sometimes just trying to foget the
problem) im really stuck...

I deployed an adp-application to a customer network (win2k-server with
Sql Server 2000/ win2k-workstations with mdac 2.8, Access 2000 Sp3) .
Everything works fine - only the first time I wait for accessing data
lasts for about 15 seconds or if the user leaves the application
untouched for serveral minutes - the same delay occures. After this
time of "waiting" the application works fine and performant.

It has nothing to do with too much data or missing server filters,
because the same actions perform pretty well after this delays. I watch
the performance monitors of server and workstations , but there is no
significant high usage rate.

The most astonishing thing is, that when I connect to this network with
my laptop and use the same ade and the same sql server - I don´t get
these latency on login/ after being idle.
Difference from my lappy to their workstations are - Win XP, Enterprise
Manager and local Sql Server (for development) installed.

Knows somebody something about this problem? Is there maybe some
service pack for Win2k Connectivity/ Sql Server or Access?

Any help´s appreciatet

Thanx a lot in advance,

Gerhard Mack

 

[Sylvain Lafontaine]

What kind of authorisation are you using in your connection string?

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


Hi folks,

after searching and searching (and sometimes just trying to foget the
problem) im really stuck...

I deployed an adp-application to a customer network (win2k-server with
Sql Server 2000/ win2k-workstations with mdac 2.8, Access 2000 Sp3) .
Everything works fine - only the first time I wait for accessing data
lasts for about 15 seconds or if the user leaves the application
untouched for serveral minutes - the same delay occures. After this
time of "waiting" the application works fine and performant.

It has nothing to do with too much data or missing server filters,
because the same actions perform pretty well after this delays. I watch
the performance monitors of server and workstations , but there is no
significant high usage rate.

The most astonishing thing is, that when I connect to this network with
my laptop and use the same ade and the same sql server - I don´t get
these latency on login/ after being idle.
Difference from my lappy to their workstations are - Win XP, Enterprise
Manager and local Sql Server (for development) installed.

Knows somebody something about this problem? Is there maybe some
service pack for Win2k Connectivity/ Sql Server or Access?

Any help´s appreciatet

Thanx a lot in advance,

Gerhard Mack

 

[Gerhard]

Hallo Sylvain,

I´m using Sql Server Authentication (User Id/ Password).

Gerhard

 

[Vadim Rapp]

Hello Gerhard,
You wrote in conference microsoft.public.access.adp.sqlserver on 1 Feb 2006
07:45:55 -0800:

G> I deployed an adp-application to a customer network (win2k-server with
G> Sql Server 2000/ win2k-workstations with mdac 2.8, Access 2000 Sp3) .
G> Everything works fine - only the first time I wait for accessing data
G> lasts for about 15 seconds or if the user leaves the application
G> untouched for serveral minutes - the same delay occures. After this
G> time of "waiting" the application works fine and performant.

The most likely reason for this is auto-closing of the database on sql
server. Check in enterprise manager/selected database/properties/options.

Vadim Rapp

 

[Gerhard]

Hi Vadim,

I checked the property "autoclose", but it is not enabled.
As I already said - with my laptop in the customers network everything
works fine.

I suspect a missing service pack or special connectivity tool that is
used by
the enterprise manager (I have installed on my laptop and which is not
on all
the network workstations).
The adp is used on 15 workstations, but every workstation has this
problem -
really good performance unless the user lets the app untouched for
several minutes.

I really have no more ideas left

Thanx for your suggestion anyway... got another hint?

Gerhard

 

[Vadim Rapp]

G> Thanx for your suggestion anyway... got another hint?

Yes. Run profiler on sql server and watch for the connection as you make it
from the problematic client. Then you will see if it's (1) delay in the
connection coming to sql server, or (2) delay already within sql server in
handling the request. If the former, look for network problems, especially
DNS; also try changing the protocol in the client tool. If the latter, look
for sql server problems, especially authentication - for example, change nt
auth to sql server auth and see if there's a difference.

Vadim

 

[Gerhard]

Hi Vadim,
last friday I was on the MS "Rock The Launch" event of VS 2005 and Sql
Server 2005. So I took the chance to ask "the expert" direktly. He gave
me the hint to explicitly name the network library in the
connectionstring. So I tried "network library = dbnetlib" and "network
library = dbmssocn". Both didn´t work out to solve the problem.

Also I used the ip address of the server to eliminate dns-problems.
Didn´t work.

So I profiled:
- Before I open the form I check the securityrole for the logged in
user (own security implementation) and these checks are done
immediately.
- Afterwards there is an 5-second-gap where nothing is written to the
protocol
- After the gap everything works fine
- while doing those aktions without leaving the app untouched for 5
minutes not gap occurs

So after all I think it is the adp trying to gain another connection to
sqlserver when opening a form - don´t know how to explain the adp to
not drop the connecton after 5 seconds or to connect faster.

I will just try to install the client configuration utility from sql
server to check if it works afterwards.

Thank you for your suggestions.

Gerhard

 

[Gerhard]

By the way, the gap seems to occur two times:

....starting the application action and working immediately until the
next statement:
SQL:BatchCompleted Developer 83 2006-02-14 12:38:56.450
....first gap of 4 sec
SQLTransaction sa 9 2006-02-14 12:39:00.780
TransactionLog sa 9 2006-02-14 12:39:00.780
TransactionLog sa 9 2006-02-14 12:39:00.780
SQLTransaction sa 0 9 2006-02-14 12:39:00.780
TransactionLog sa 9 2006-02-14 12:39:00.780
SQLTransaction sa 9 2006-02-14 12:39:00.780
SQLTransaction sa 0 9 2006-02-14 12:39:00.780
SQLTransaction sa 9 2006-02-14 12:39:00.780
SQLTransaction sa 0 9 2006-02-14 12:39:00.780
SQLTransaction sa 9 2006-02-14 12:39:00.780
SQLTransaction sa 0 9 2006-02-14 12:39:00.780
... second gap of 8 sec
Audit Login -- network protocol: TCP/IP Developer 104 2006-02-14
12:39:08.483
"set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language Deutsch
set dateformat dmy
set datefirst 1"

If anybody understands what is happening - please tell me

Gerhard

 

[Vadim Rapp]

G> So I profiled:
G> - Before I open the form I check the securityrole for the logged in
G> user (own security implementation) and these checks are done
G> immediately.
G> - Afterwards there is an 5-second-gap where nothing is written to the
G> protocol

I would try to narrow down the problem by simplifying your project. Create
brand new project with one form, connected to the same db. Will the gap
occur? if not, then keep adding components approaching your real adp and
watch when the gap reappears. Specifically, remove "own security
implementation".

Vadim Rapp

 

[Gerhard]

Hi Vadim,

has taken me a while to find the time to test the whole mess again.
I created a new adp and connected it to the sql-database.

I close the adp, wait for 1 minute, open it again and it takes 15 sec.
I close it again, don´t wait, open it again and it opens immediately.

I installed Client Connectivity Tools for Sql Server and made shure the
connection
takes place over tcp/ip. I updated the tcp/ip networklibrary.
Nothing changed.

Afterwards I tried the same thing on another machine in the customers
network, where I had access installed. It was Terminal Server 2003 (?)
and Access XP. Created new ADP and the Connection opens always
immediately - even if I wait more than 1 minute.

My conclusions are either
- Problem with Access 2000 or
- Problem with Windows 2000.

Customer will try to install Access XP/ 2003 on a machine with the
latency-problem...

Thanks for your help - I will write down the solution as soon as found


Gerhard

 

[Vadim Rapp]

Hello Gerhard:

Try it with complied project, i.e. compile adp into ade. For production,
it's good idea anyways.

Vadim Rapp



You wrote on 8 Mar 2006 11:14:04 -0800:

G> Hi Vadim,

G> has taken me a while to find the time to test the whole mess again.
G> I created a new adp and connected it to the sql-database.

G> I close the adp, wait for 1 minute, open it again and it takes 15 sec.
G> I close it again, don´t wait, open it again and it opens immediately.

G> I installed Client Connectivity Tools for Sql Server and made shure the
G> connection
G> takes place over tcp/ip. I updated the tcp/ip networklibrary.
G> Nothing changed.

G> Afterwards I tried the same thing on another machine in the customers
G> network, where I had access installed. It was Terminal Server 2003 (?)
G> and Access XP. Created new ADP and the Connection opens always
G> immediately - even if I wait more than 1 minute.

G> My conclusions are either
G> - Problem with Access 2000 or
G> - Problem with Windows 2000.

G> Customer will try to install Access XP/ 2003 on a machine with the
G> latency-problem...

G> Thanks for your help - I will write down the solution as soon as found
G>

G> Gerhard

With best regards, Vadim Rapp. E-mail: (e-mail address removed)

 

[aaron.kempf]

are you using MSDE?

oh wait a second; you're actually trying to use SQL 2000 with Access
2000?

those idiots in redmond made those products NOT COMPATABLE.

i would reccomend upgrading to Access 2002 or 2003.

 

[Vadim Rapp]

ak> those idiots in redmond made those products NOT COMPATABLE.

Aaron, when you decide to apply for the Vice-President of Microsoft Office
position, drop a post here, and we will all send a collective letter to
Microsoft that this is their only and last chance to get on the right track.

Vadim Rapp

 

[aaron.kempf]

Vadim;

Seriously; do you tink that it is RATIONAL to sell Access 2000 and SQL
2000 as being incompatable?

Is it RATIONAL to sell SQL 2005 without having an ACCESS DATA PROJECT
that will work with SQL 2005?

ACCESS DATA PROJECTS rock and Microsoft is a bunch of drunk lazy fat
idiots.

In my honest opinion; I am the only person that could WHIP THAT COMPANY
INTO SHAPE.
I would love a management position at Microsoft-- but it's not going to
happen.

BECAUSE I DONT ACCEPT GROUPTHINK.

I DONT ACCEPT 'CLOSE ENOUGH'.

AND I DONT ACCEPT THE LONG HELD PREMISE THAT ALL SOFTWARE **MUST** HAVE
BUGS.

But as it is; those assholes fired me TWICE for complaining about SQL
authentication.

They put our soldiers at risk and somehow it's MY PROBLEM for
complaining about it?

Screw Microsoft

Bunch of drunk kids can't even figure out a decent strategy; they
change direction every 4 months.

ACCESS DATA PROJECTS ARE A VERY STRATEGIC PIECE OF THE PUZZLE AND
MICROSOFT JUST DOESNT TAKE THEM SERIOUSLY.

THAT IS JUST ONE REASON THAT MICROSOFT SQL SERVER WILL NEVER BE TAKEN
SERIOUSLY.

MY COMPANY STILL HAS DOZENS OF RPG DEVELOPERS: MAKING SIMPLE DATA ENTRY
FORMS FOR THE AS/400.

DO YOU KNOW WHY?

BECAUSE MSFT WONT TAKE ADP SERIOUSLY.