SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connection to SQL Server dropping


Connection to SQL Server dropping

Author
Message
pdanes
pdanes
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 1354
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.
bleroy
bleroy
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 739
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
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14765 Visits: 9000
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
pdanes
pdanes
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 1354
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?
pdanes
pdanes
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 1354
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.
bleroy
bleroy
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 739
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
pdanes
pdanes
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 1354
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.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28417 Visits: 39963
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5300 Visits: 4076
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;-)
pdanes
pdanes
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 1354
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search