Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Connection to SQL Server dropping Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 3:28 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
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.
Post #1389436
Posted Wednesday, November 28, 2012 2:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:14 AM
Points: 167, Visits: 682
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
Post #1389589
Posted Wednesday, November 28, 2012 5:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:35 AM
Points: 7,197, Visits: 6,342
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1389673
Posted Wednesday, November 28, 2012 7:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
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?
Post #1389761
Posted Wednesday, November 28, 2012 7:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
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.
Post #1389775
Posted Wednesday, November 28, 2012 7:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:14 AM
Points: 167, Visits: 682
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

Post #1389788
Posted Wednesday, November 28, 2012 8:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
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.
Post #1389819
Posted Wednesday, November 28, 2012 8:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 12,910, Visits: 32,029
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1389829
Posted Wednesday, November 28, 2012 8:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1389868
Posted Wednesday, November 28, 2012 9:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
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.
Post #1389924
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse