November 27, 2012 at 3:28 pm
I'm running a MsAccess app connected to SQL Server through an ODBC link. It works fine in almost all cases, but I have one module where the connection drops, and I haven't been able to figure out why. (I'm using DAO recordsets, they are opened and immediately closed again, even the recordset object is set to nothing and a DoEvents right after, for the VBA freaks here.)
I thought it seemed like a timeout issue, but the elapsed time before crash is not consistent and making the default connection time in the ODBC link to ten times its default had no effect - it still bombs at approximately the same place, although still not consistently (that is, it bombs consistently - always, just not after the same amount of elapsed time).
This is the first part of a mass import, but I'm not changing any data yet. I look up things in SQL Server, based on data I read in from a text file, and store the results locally. If examination of the text file concludes with no errors, I enable a "Do Import" button, which does a set-based import from the local temp table. The mass import works fine, when it gets that far, but the numerous lookups during examination of the input text file keep crashing. The individual queries that I execute during these lookups all run essentially instantaneously, and there is nothing else on the machine.
This is the error message I get, but all my hunting around the net has produced solutions to this problem on initial hookup. That's not my case - I connect just fine and run for a while - around a minute, which is why I thought it was a timeout issue. No error trapping code in the application has let me catch the exact line where this happens - the error trap never fires. The code runs for a while, then this dialog appears. All I do is click OK to this and another dialog offering to reconnect, using the trusted connection. Sometimes I have click OK several times in these two dialogs (they alternate) but it always eventually takes off again and finishes with no problem.
I don't know what would happen if the text file was significantly longer, whether it would bomb again after a while - should I try that? Would knowing yes or no to that give any further indications of the problem? Is there anything else that I can provide that would help troubleshoot this? I'm not exactly an amateur at Access, but this is something new.
Connection Failed:
SQL State: '01000'
SQL Server: Error: 10048
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect())
Connection Failed:
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
November 28, 2012 at 2:29 am
Hi,
Do you have access to the machine hosting the SQL Server DB? if so, can you check on there for the windows log, and sql serror logs?
See if there is any information in there as to why the remote server/db closes the connection.
B
November 28, 2012 at 5:41 am
Do you have Auto_Close enabled on your SQL Server properties?
That's the first thing I would check. If not, then I would get a network sniffer running while you try to establish the connection again. See if that tells you anything.
Other options are to do a tracert and a ping from the location of this particular Access db to see if that machine can even hit the SQL server.
EDIT: It's also giving an "Access Denied" in that error, so I'd check permissions too.
November 28, 2012 at 7:15 am
bleroy (11/28/2012)
Hi,Do you have access to the machine hosting the SQL Server DB? if so, can you check on there for the windows log, and sql serror logs?
See if there is any information in there as to why the remote server/db closes the connection.
B
I am the only show in town. I have full access to everything on the machine, except AD admin rights. But my domain account has full local admin privileges, so I can look at anything I need to.
I assume you mean right-click, manage compter and diagnostics? But there's an awful lot of stuff there, and I don't know my way around it that well. What should I be looking at?
November 28, 2012 at 7:33 am
Brandie Tarvin (11/28/2012)
Do you have Auto_Close enabled on your SQL Server properties?
I do not. I'm looking at it right now. AutoCreate and AutoUpdate Statistics are both true, AutoClose, AutoShrink and Asynchronous Update Stats are all false.
That's the first thing I would check. If not, then I would get a network sniffer running while you try to establish the connection again. See if that tells you anything.
I don't have one at hand. Is there a downloadable freeware or trial version of something that you would recommend? I've never used one, so I have no idea what would do an adequate job for this.
Other options are to do a tracert and a ping from the location of this particular Access db to see if that machine can even hit the SQL server.
EDIT: It's also giving an "Access Denied" in that error, so I'd check permissions too.
What would that tell me? The app does run for a while, and not just warming up - it communicates with the server quite nicely, sending requests for data and getting them back in milliseconds, for around a minute, then something jacks and the connection gets dropped/broken/refused/something. And the reconnect dialog hooks it back together, often on the first try.
November 28, 2012 at 7:49 am
Not knowing what OS we are talking about, I will assume that the machine runs with Windows Server 2003+.
Have a look in the windows event log (http://support.microsoft.com/kb/308427), a number of categories there, check them all out (although Application log should be the most relevant), the right panel will give a list of events - sometimes a little cryptic, but generally using a combination of event time + related application would allow you to identify the relevant event (if any!) - simply scroll through the lists and check out the details related to the event of interest.
Also have a look at the SQL server event logs themselves,
**** from http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=242 by Buck Woody ****
the Error Logs in SQL Server are actually text files. They are normally found at:
Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n
Replace the āNā with the version of SQL Server and the error log number. But most folks use graphical tools to read them.
****
HTH,
B
November 28, 2012 at 8:13 am
bleroy (11/28/2012)
Not knowing what OS we are talking about, I will assume that the machine runs with Windows Server 2003+.
My apologies - Alzheimer's working overtime today. It's Windows Server Standard, Copyright 2007 (= Server 2008?), SP2, Active Directory enabled, running "Microsoft SQL Server Express Edition with Advanced Services (64-bit)" (= 2008 R2?)
Several TB free disk space and practically no other tasks running.
Looking at the rest of the stuff you mention now - back in a while.
November 28, 2012 at 8:24 am
just reading up and want you to check the basics.
search the code and see if there is a {MyConnection}.Close, where {MyConnection} is the variable name for your Connection...it might be being closed when you don't expect it to...
especially if this is vb/vba...are there any On Error Resume Next commands? it might be that an error gets suppressed that prevents subsequent code from executing correctly.
Lowell
November 28, 2012 at 8:54 am
WHat if we use SSIS to do this stuff more robust more tracable .. more comfortable to handle heavy volume data. i will welcome suggest on this suggest š
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 28, 2012 at 9:46 am
bleroy (11/28/2012)
Have a look in the windows event log (http://support.microsoft.com/kb/308427), a number of categories there, check them all out (although Application log should be the most relevant), the right panel will give a list of events - sometimes a little cryptic, but generally using a combination of event time + related application would allow you to identify the relevant event (if any!) - simply scroll through the lists and check out the details related to the event of interest.
I get an error when attempting to expand the SQL Server Logs node in SSMSE:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Also have a look at the SQL server event logs themselves,
**** from http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=242 by Buck Woody ****
the Error Logs in SQL Server are actually text files. They are normally found at:
Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n
Just checked that - there is absolutely nothing from the period yesterday evening when I was testing and experiencing those lockouts. There are some failed login attempts from several hours earlier, when I was messing with something else, and an automatic full backup task about 30 minutes later, but nada from the time period in question.
November 28, 2012 at 10:04 am
Lowell (11/28/2012)
search the code and see if there is a {MyConnection}.Close, where {MyConnection} is the variable name for your Connection...it might be being closed when you don't expect it to...
Nope. I use Set WORM_cdb = CurrentDb on startup, then refer to that whenever I create DAO recordsets, like this:Set rstP = WORM_cdb.OpenRecordset("SELECT TOP 1 * FROM vwPodrobnostiPal2 WHERE PodrobnostiAutoID = " & CStr(DrEvidenceStatus.GoToPodrobnostiAutoID), dbOpenSnapshot)One such recordset was opened in this module for communication with SQL Server, then continually re-opened for every query cycle (several dozen times per second). I added code to specifically close it after every cycle, then code to set the recordset variable to Nothing after closing, then a DoEvents. No change - it still bombs.
especially if this is vb/vba...are there any On Error Resume Next commands? it might be that an error gets suppressed that prevents subsequent code from executing correctly.
Nothing like that. I completely removed all error trapping in the module, trying to track down exactly where it was bombing. There is nothing in the module that should cause an error anyway. It reads in text, parses individual lines, executes some stored procedures to examine and verify input texts, but all processes and tests return some sort of definite result. My general coding preference, in all languages, is to test for conditions and act on the results of the test, rather than attempting something, letting the attempt fail and then recovering. Not always possible, of course, sometimes error handling is the only possible route, but I avoid it whenever I can.
November 28, 2012 at 10:11 am
Bhuvnesh (11/28/2012)
WHat if we use SSIS to do this stuff more robust more tracable .. more comfortable to handle heavy volume data. i will welcome suggest on this suggest š
I don't know how SSIS would help here. I'm simply sending individual requests like "Select DepozitarAutoID FROM vwPodrobnostiPal2 WHERE PodrobnostiAutoID = 123654" to see whether a number does or does not exist. These queries use indexes and return their results instantly - zero elapsed time in SSMS, and as fast as the host code can execute when communicating with the app. It just stops doing it after a while. But if you have a suggestion on how SSIS might help with this, I'm certainly willing to listen.
November 28, 2012 at 10:47 am
pdanes (11/28/2012)
Other options are to do a tracert and a ping from the location of this particular Access db to see if that machine can even hit the SQL server.
EDIT: It's also giving an "Access Denied" in that error, so I'd check permissions too.
What would that tell me? The app does run for a while, and not just warming up - it communicates with the server quite nicely, sending requests for data and getting them back in milliseconds, for around a minute, then something jacks and the connection gets dropped/broken/refused/something. And the reconnect dialog hooks it back together, often on the first try.
The error you posted doesn't look like a timed out connection, just a failed connection. That tells me that no connection was actually made. However, I'm not looking at everything you can see, therefore, all I could do was diagnose on what I could see.
When you say the app runs for a while, are you sure it's not timing out the connection? This would be one thing to look for in the logs. The text would be something along the lines of "Connection Timeout". Also, look for any warnings or errors around the time you had this problem (ignore the informational messages) to see if any of them directly relate to the database or to the application in question. Check for network issues as well. Your best bets are the Application & System logs in Event Viewer and the SQL Server logs (EDIT: Which I just read the post where you said you don't have them. Do any SQL logs exist for today? If not, you might have logging turned off or something else is going on with your server.).
I'm not sure how a network sniffer is set up. I know they exist and that network admin I know set them up all the time, but I don't know if they're using PerfMon or some other tool to do the sniff. I do know the sniff watches all the traffic and logs it, but that's about the extent of my knowledge.
November 28, 2012 at 10:50 am
Bhuvnesh (11/28/2012)
WHat if we use SSIS to do this stuff more robust more tracable .. more comfortable to handle heavy volume data. i will welcome suggest on this suggest š
If he's doing what I think he's doing (my workplace has a similar setup), SSIS doesn't work. This isn't a transfer of data from one system to another. It's an Access client application that uses SQL Server as its backend database.
November 28, 2012 at 10:52 am
pdanes, have you checked your connection pooling properties both in the app and on the server to make sure you can reuse existing connections and that the pool isn't throttled so far back that it's causing the bump & dump?
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply