ODBC is an old technology. It is still around, as some database vendors are
still using it. It is the LCD approach to database access and is extremely
generic. Unfortunately, when you try to be one size fits all it turns out to
be "not really fitting anyone at all."
Microsoft is not interested in supporting ODBC any more, and would have
likely dumped it long ago, if not for a few database vendors who refuse to
create OleDb providers. I do agree that you have likely found a bug in the
ODBC, but the fact that fewer and fewer people use it means fewer resources
to iron out all the loose ends.
Glad you got resolution.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Wolfgang Kaml" <ms@no-spam.kaml.com> wrote in message
news:unTKMJU4DHA.2384@TK2MSFTNGP10.phx.gbl...[color=blue]
> Gregory,
>
> THANK YOU so much for the hint. I have thought about using OleDb before,[/color]
but[color=blue]
> wouldn't have given it a try for a long time because I never thought that
> Microsoft would have in fact a buggy ODBC setup on Windows 2003 Server.
>
> Everybody using ODBC on Windows 2003 Server with a MS Access DB - and
> specially _Microsoft_ - listen up:
>
> With this example, I confirmed the ODBC data connection to MS Access on
> Windows 2003 Server to have a bug.
>
> Have a good look at this code example:
>
> Example 1 - using OleDb - takes 1 second on Windows 2003 Server with MS
> Access, on _every_ call
>[/color]
'---------------------------------------------------------------------------[color=blue]
> ------------------------------------
> Dim objCounterDBConnection As OleDbConnection
> Dim objCounterDbRdr As OleDbDataReader
> Dim objCounterDBCommand As OleDbCommand
>
> objCounterDBConnection = New
> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data[/color]
Source=C:\mydb.mdb")[color=blue]
> objCounterDBCommand = New OleDbCommand("select count(*) from [COUNTER][/color]
where[color=blue]
> CNTPAGE = " & Me.CounterID, objCounterDBConnection)
>
> Try
> objCounterDBConnection.Open()
> objCounterDbRdr =
> objCounterDBCommand.ExecuteReader(CommandBehavior. SingleRow)
> While objCounterDbRdr.Read
> If Not IsDBNull(objCounterDbRdr.Item(0)) Then strReturnValue =
> objCounterDbRdr.Item(0)
> End While
> objCounterDbRdr.Close()
> objCounterDBConnection.Close()
> Catch ex As Exception
> End Try
>
> objCounterDBCommand.Dispose
> objCounterDBConnection.Dispose
>
> Example 2 - using ODBC - takes 1 second on Windows 2003 Server with MS
> Access on the 1st call, and 8.9 seconds on every subsequent call.
>[/color]
'---------------------------------------------------------------------------[color=blue]
> ------------------------------------
> Dim objCounterDBConnection As OdbcDbConnection
> Dim objCounterDbRdr As OdbcDbDataReader
> Dim objCounterDBCommand As OdbcDbCommand
>
> objCounterDBConnection = New OdbcDbConnection("PageTimeout=5;FIL=MS
> Access;MaxBufferSize=2048;DSN=MyDSN;UID=admin;Driv erId=25")
> objCounterDBCommand = New OdbcDbCommand("select count(*) from [COUNTER]
> where CNTPAGE = " & Me.CounterID, objCounterDBConnection)
>
> Try
> objCounterDBConnection.Open()
> objCounterDbRdr =
> objCounterDBCommand.ExecuteReader(CommandBehavior. SingleRow)
> While objCounterDbRdr.Read
> If Not IsDBNull(objCounterDbRdr.Item(0)) Then strReturnValue =
> objCounterDbRdr.Item(0)
> End While
> objCounterDbRdr.Close()
> objCounterDBConnection.Close()
> Catch ex As Exception
> End Try
>
> objCounterDBCommand.Dispose
> objCounterDBConnection.Dispose
>
>[/color]
'---------------------------------------------------------------------------[color=blue]
> ------------------------------------
>
> Dear MS folks,
> That's what I call a real bug! Please check it and fix it, or drop ODBC
> support on Windows 2003 server, because it is a hassle.
>
> Best Regards,
> Wolfgang
>
>
>
>
> "Wolfgang Kaml" <ms@no-spam.kaml.com> wrote in message
> news:eT7LKdT4DHA.1868@TK2MSFTNGP10.phx.gbl...[color=green]
> > Hey Cowboy, ;-))
> >
> > I really do appreciate your feedback.
> >
> > A few comments:
> > - The component I have is a most simplistic WebCounter, so there is not[/color]
> much[color=green]
> > of an architecture required. I know that this would be a requirement for[/color][/color]
a[color=blue][color=green]
> > more sophisticated application. My component has only a very few lines[/color][/color]
of[color=blue][color=green]
> > code and therefore could be an exreme good example for MS to figure out[/color][/color]
an[color=blue][color=green]
> > eventual ODBC problem on Windows 2003 Server with MS Access.
> > - Also, the DB is very small and using SQL Server or MSDE would be an
> > overkill. I know of the limitations of MS Access though and I am far[/color][/color]
from[color=blue][color=green]
> > reaching that. That's why it has been an even bigger puzzle to my, why
> > Windows 2003 Server would 'hang' for 8-9 seconds over such an easy[/color][/color]
issue.[color=blue][color=green]
> >
> > I will follow your suggestion and modify the app to use OleDb and see if[/color]
> it[color=green]
> > makes a difference. If it does, then that would be the ultimate proof[/color][/color]
that[color=blue][color=green]
> > MS has an issue with the Access ODBC driver on Windows 2003 server.
> >
> > Now, I'd like to know the difference on Odbc versus OleDb and tried
> > searching MSDN briefly after reading your article. Somebody responded in[/color][/color]
a[color=blue][color=green]
> > different thread on a newsgroup here, the OldDb sits on top of Odbc,[/color][/color]
which[color=blue][color=green]
> > after reading the article
> >[/color]
>[/color]
http://msdn.microsoft.com/library/de.../html/daag.asp I[color=blue][color=green]
> > know is not true.
> > So, OleDb is a different way to access a MS Access DB, but are the[/color]
> different[color=green]
> > ways explained somewhere? Do you have an article handy somewhere - just[/color][/color]
in[color=blue][color=green]
> > case? Don't spend time on searching, I can try that too.
> >
> > I will let you know how things go after tossing Odbc.
> >
> > Thanks,
> > Wolfgang
> >
> >
> >
> > "Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote[/color][/color]
in[color=blue][color=green]
> > message news:%23xWcv6S4DHA.2428@tk2msftngp13.phx.gbl...[color=darkred]
> > > I have not read everything, but can get you to a point that might[/color][/color][/color]
help.[color=blue][color=green][color=darkred]
> > >
> > > 1. Get rid of ODBC. It is God-awful slow. Use OleDb instead with a
> > > connection string and avoid the whole DSN and/or ODBC methodology.
> > > 2. If you have tons of code in your ASP.NET project, re-architect and[/color][/color]
> move[color=green][color=darkred]
> > > business code to business layer components. This will reduce heavy JIT
> > > loads.
> > >
> > > Another option is "you have outgrown Access." If you database is in[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > multiple megs (over 25MB in some cases, more in others), you are[/color][/color][/color]
ending[color=blue]
> up[color=green][color=darkred]
> > > with an inefficient database. Access starts to really puke when it[/color][/color]
> grows,[color=green][color=darkred]
> > > esp. with web apps.
> > >
> > > Note that Windows Server 2003 can reduce overhead, if you have apps in[/color][/color]
> the[color=green][color=darkred]
> > > proper pools. Since you do not mention multiple apps, this is unlikely[/color][/color]
> the[color=green][color=darkred]
> > > problem. I mention largely because improper setup of apps can cause[/color]
> > Windows[color=darkred]
> > > Server 2003 to take its own sweet time.
> > >
> > > NOTE: In Whidbey, there is a precompilation option (finally).[/color]
> > Unfortunately,[color=darkred]
> > > this does not help the immediate problem. I would focus on getting rid[/color][/color]
> of[color=green][color=darkred]
> > > ODBC and using OleDb and figuring out what code is taking a long time[/color][/color][/color]
to[color=blue][color=green]
> > JIT[color=darkred]
> > > compile. Moving some code to business components may reduce load on[/color][/color][/color]
the[color=blue][color=green]
> > app[color=darkred]
> > > JITting. This will force some refactoring of code, but it will pay off
> > > rather quickly as the app can JIT business components as needed,[/color][/color][/color]
rather[color=blue][color=green]
> > than[color=darkred]
> > > focusing on the entire ASP.NET app.
> > >
> > > --
> > > Gregory A. Beamer
> > > MVP; MCP: +I, SE, SD, DBA
> > >
> > > ************************************************** ********************
> > > Think Outside the Box!
> > > ************************************************** ********************
> > > "Wolfgang Kaml" <ms@no-spam.kaml.com> wrote in message
> > > news:eEpBTwR4DHA.2304@TK2MSFTNGP10.phx.gbl...
> > > > Hello All,
> > > >
> > > > I have been working on this for almost a week now and I haven't[/color][/color]
> anything[color=green][color=darkred]
> > > up
> > > > my sleeves anymore that I could test in addition or change....
> > > > Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC
> > > issue,
> > > > I am posting this on all of the three newsgroups.
> > > >
> > > > That's the setup:
> > > > Windows 2003 Server with IIS and ASP.NET actiavted
> > > > Access 2002 mdb file (and yes, proper rights are set on TMP paths[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > path,
> > > > where mdb file is located)
> > > > ASP.Net web server component that will query and update the Access[/color][/color][/color]
DB[color=blue]
> in[color=green][color=darkred]
> > > the
> > > > functionality of a web counter
> > > >
> > > > The problem is, that after the first call of the web site with the[/color][/color][/color]
web[color=blue][color=green][color=darkred]
> > > > counter component, the refresh will take about 8-9 seconds. Doesn't[/color]
> > matter[color=darkred]
> > > > if I close the IE or just hit the refresh.
> > > >
> > > > From what I can observe is, that after the start of the IIS web[/color][/color][/color]
server[color=blue][color=green]
> > no[color=darkred]
> > > > w3wp.exe process is running. As soon as I open the aspx web site[/color][/color][/color]
with[color=blue][color=green]
> > the[color=darkred]
> > > > .Net built counter component, a w3wp.exe process is being[/color][/color][/color]
instantiated[color=blue][color=green]
> > and[color=darkred]
> > > > the calls to the Access DB are made. Since the w3wp.exe process has[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > > start, the inital display of the web page takes about 1-2 seconds[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > > everything works fine. I can see a .tmp file being created in the[/color][/color][/color]
TMP[color=blue][color=green][color=darkred]
> > > > directory and a .ldb file being created in the path of the .mdb file[/color][/color]
> for[color=green]
> > a[color=darkred]
> > > > brief moment. Then they disappear. The w3wp.exe process stays. Then,[/color][/color]
> if[color=green]
> > I[color=darkred]
> > > > hit refresh or close and reopen the web site, nothing seems to[/color][/color][/color]
happen[color=blue][color=green]
> > for[color=darkred]
> > > > 8-9 seconds and then the page displays without any errors. The[/color]
> > difference[color=darkred]
> > > > is, that the .tmp file and the .ldb file are also sitting in the
> > > > corresponding paths for a while longer, but both disappear again.
> > > >
> > > > Since I have been doing quiet a bit of trouble shooting already,[/color][/color][/color]
here[color=blue]
> is[color=green][color=darkred]
> > > > some detailed info as of what happens during the inital call of the[/color][/color]
> web[color=green][color=darkred]
> > > > site, and during subsequent calls:
> > > >
> > > > Output of current second and millisecond of the machine during[/color][/color][/color]
initial[color=blue][color=green][color=darkred]
> > > call,
> > > > when no w3wp.exe process existed yet:
> > > > 58:343 objCounterDBConnection = New OdbcConnection
> > > > 58:359 objCounterDBCommand = New OdbcCommand
> > > > 58:359 objCounterDBConnection.Open()
> > > > 58:390 objCounterDbRdr = objCounterDBCommand.ExecuteReader
> > > > 58:562 objCounterDbRdr.Read
> > > > 58:562 objCounterDbRdr.Close()
> > > > 58:578 objCounterDBConnection.Close()
> > > > 58:625 preparing insert
> > > > 58:640 objCounterDBConnection.Open()
> > > > 58:640 objCounterDBCommand.ExecuteNonQuery()
> > > > 58:640 objCounterDBConnection.Close()
> > > > 58:687 objCounterDBConnection & objCounterDBCommand.Dispose()
> > > > 58:687 finished
> > > >
> > > > Output of current second and millisecond of the machine during[/color]
> > subsequent[color=darkred]
> > > > call, when existing w3wp.exe process apparently take the job:
> > > > 10:359 objCounterDBConnection = New OdbcConnection
> > > > 10:359 objCounterDBCommand = New OdbcCommand
> > > > 10:359 objCounterDBConnection.Open()
> > > > 10:375 objCounterDbRdr = objCounterDBCommand.ExecuteReader
> > > > 19:140 objCounterDbRdr.Read
> > > > 19:140 objCounterDbRdr.Close()
> > > > 19:140 objCounterDBConnection.Close()
> > > > 19:187 preparing insert
> > > > 19:187 objCounterDBConnection.Open()
> > > > 19:187 objCounterDBCommand.ExecuteNonQuery()
> > > > 19:187 objCounterDBConnection.Close()
> > > > 19:234 objCounterDBConnection & objCounterDBCommand.Dispose()
> > > > 19:234 finished
> > > >
> > > > As you can see, during a subsequent call, the ExecuteReader takes[/color][/color]
> close[color=green]
> > to[color=darkred]
> > > 9
> > > > seconds before it returns the OdbcDataReader object.
> > > >
> > > > WHY????
> > > >
> > > > This is not an update and hence has nothing to do with lazy[/color][/color]
> propagation[color=green][color=darkred]
> > > > issues as far as I can tell what I have seen on different posts.
> > > > Also, this problem occurs only on the Windows 2003 Server, not the[/color]
> > Windows[color=darkred]
> > > > 2000 Server.
> > > > What is the issue here? - the initial w3wp.exe process is being run[/color]
> > under[color=darkred]
> > > > the NETWORK SERVICE account and I have given that account proper
> > > permissions
> > > > to perform the job. That is being verified in my opinion by the[/color][/color][/color]
fact,[color=blue][color=green]
> > that[color=darkred]
> > > > the first run of the component works fine.
> > > > To me this is an absolute issue of Windows 2003 Server and/or ODBC[/color][/color][/color]
on[color=blue][color=green][color=darkred]
> > > > Windows 2003 Server.
> > > >
> > > > Is it ODBC, is it the Windows 2003 server? I have no clue.
> > > >
> > > > Your help on that would be really appreciated and I can post the[/color][/color]
> sources[color=green][color=darkred]
> > > if
> > > > necessary if somebody else would like to try that out. I'm almost[/color][/color]
> 99.99%[color=green][color=darkred]
> > > > certain that this is a true Windows 2003 Server or ODBC issue that I[/color][/color]
> am[color=green][color=darkred]
> > > just
> > > > about ready to open a call with Microsoft on that.
> > > > The source is clean as it could be and I have double checked with[/color][/color]
> quiet[color=green]
> > a[color=darkred]
> > > > few examples on MSDN and other Tech articels on that.
> > > >
> > > > Yesterday I had the IIS and application part completly removed from[/color][/color]
> the[color=green][color=darkred]
> > > > server, everything cleaned up and started all over with the install.[/color]
> > Exact[color=darkred]
> > > > same results. No other pocesses are running on that machine at all.[/color][/color]
> It's[color=green][color=darkred]
> > > > clean as it could be. So I am really stumbling on that.
> > > >
> > > > Please help!
> > > >
> > > > Thank you,
> > > > Wolfgang
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
