November 29, 2012 at 8:17 am
How is your environment set up?
For instance, in one environment I worked on, we had a SQL Server, a Citrix Terminal Server, and an application / file server. I worked with people who also didn't really have a server, per say, but used Access applications / dbs on an XP workgroup.
What does your environment look like?
Is this the only Access application you have, or are there others?
Are there other non-Access applications that you're running from the same location as the Access app?
And do you use Citrix? (Citrix and SQL don't always play well together).
November 29, 2012 at 8:17 am
Randy Doub (11/29/2012)
How about the data in the Express db? Does it fail trying to return the same record every time?
No, it varies. It's roughly a minute, and sometimes chokes in the same place, but not always.
November 29, 2012 at 8:30 am
Brandie Tarvin (11/29/2012)
How is your environment set up?For instance, in one environment I worked on, we had a SQL Server, a Citrix Terminal Server, and an application / file server. I worked with people who also didn't really have a server, per say, but used Access applications / dbs on an XP workgroup.
What does your environment look like?
SQL Server Express 2008 R2, running on 64-bit Windows Server Standard 2008, dual core Intel Xeon E5420 CPU, 6GB RAM, Active Directory enabled. 32GB free space on C:, 2.62 TB free space on D:. Database files on D:.
Dump from SSMSE is:
Microsoft SQL Server Management Studio10.50.1617.0
Microsoft Data Access Components (MDAC)6.0.6002.18005
Microsoft MSXML3.0 5.0 6.0
Microsoft Internet Explorer9.0.8112.16421
Microsoft .NET Framework2.0.50727.4234
Operating System6.0.6002
Is this the only Access application you have, or are there others?
Nothing else, and when I was doing these tests, there was only one instance of the app running. Everyone else has enough sense to go home in the evening, and I was here by myself. The app is set up to only run from in-house - the server is not even visible from outside our network, so there is no possibility that some else was on it.
Are there other non-Access applications that you're running from the same location as the Access app?
Nothing that should impact this. The usual antivirus stuff and such in the background, and I may have had Notepad open.
And do you use Citrix? (Citrix and SQL don't always play well together).
No, not even installed on this machine, ever.
November 29, 2012 at 9:17 am
pdanes (11/29/2012)
Neither of these keys exists anywhere on my server. The path you list is there, but those keys are not, there or anywhere else.
I carelessly said "On the server, not on the client" when I meant "On the client, not on the server". But if these values have never been changed from the default, the registry entries for them will be absent anyway - the registry entries are overrides for the defaults. The default for MaxUserPort (which is a DWORD value) is 5000, which permits something under 4000 user sockets in simultaneous use. Try creating the value in HKLM/\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters and setting it to 17000, which allows about 4 times as many simultaneous user sockets (of course ODBC may insist on a narrow range of client port numbers so that this would have no effect, but I don't think it does), or to any value up to 65535 (NOT 65535 or 65536 or higher). Requires a reboot to take effect.
The default for TcpTimedWaitDelay, which is also a DWORD, is 240 (! - I believe that means your sockets can hang about marked as in use for up to 4 minutes after you close the connection). Overriding this default with a new value 30 (the minimum value allowed) might do some good (I think my previous post suggested increasing it - I was thinking upside down at the time). This too requires a reboot to take effect.
This is all documented at http://msdn.microsoft.com/en-us/library/aa560610.aspx - which also suggests a couple of other things, messing about with DNETLIB. Something with the same error apparently common with Biztalk back then.
I can't discount the possibility, but I don't see error the #10048 that you mention anywhere, in hex or decimal.
It's in your first post in this topic:
pdanes (11/27/2012)
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.
Tom
November 29, 2012 at 9:23 am
Tom, if it's a client setting, then that'd mean configuring all clients that runs the front-end software. Wouldn't it also potentially mess with other softwares? I'd prefer that it was limited to the application or was server-side (assuming the server is private and not hosted or shared in some way)
November 29, 2012 at 9:38 am
Error ID 10048 occurs when the port you are connecting to is being used by some other application.
In your case, it may be the same application, but at nearly the same time you are killing the connection from the previous loop, you try to re-establish a connection to same port - there may be that very small time lag between sending command to server to kill connection whilst your client already moves on and tries to reconnect to that same port (complete speculation ofc).
So, as you suggested in an earlier post, you could add a wee wait command to allow the server to catch up.
B
November 29, 2012 at 10:14 am
L' Eomot Inversé (11/29/2012)
pdanes (11/29/2012)
Neither of these keys exists anywhere on my server. The path you list is there, but those keys are not, there or anywhere else.I carelessly said "On the server, not on the client" when I meant "On the client, not on the server".
And I carelessly checked the client, not the server, initially. :hehe: But they're not on either machine.
But if these values have never been changed from the default, the registry entries for them will be absent anyway - the registry entries are overrides for the defaults. The default for MaxUserPort (which is a DWORD value) is 5000, which permits something under 4000 user sockets in simultaneous use. Try creating the value in HKLM/\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters and setting it to 17000, which allows about 4 times as many simultaneous user sockets (of course ODBC may insist on a narrow range of client port numbers so that this would have no effect, but I don't think it does), or to any value up to 65535 (NOT 65535 or 65536 or higher). Requires a reboot to take effect.
The default for TcpTimedWaitDelay, which is also a DWORD, is 240 (! - I believe that means your sockets can hang about marked as in use for up to 4 minutes after you close the connection). Overriding this default with a new value 30 (the minimum value allowed) might do some good (I think my previous post suggested increasing it - I was thinking upside down at the time). This too requires a reboot to take effect.
This is all documented at http://msdn.microsoft.com/en-us/library/aa560610.aspx - which also suggests a couple of other things, messing about with DNETLIB. Something with the same error apparently common with Biztalk back then.
Thanks, I'll have a look at that.
I can't discount the possibility, but I don't see error the #10048 that you mention anywhere, in hex or decimal.
It's in your first post in this topic:
pdanes (11/27/2012)
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.
Oops. I saw the two state numbers and the second error, 17, but completely spun over the first one. Sorry about that.
In the meantime, I've tried some other things. First was to increase the size of the text file being read in. Just open it in Notepad, presses Ctrl/A, Ctrl/C, then Ctrl/V about a half dozen times. Generates lots of duplicates, but that's not a problem - one of the things that the verification routine checks for is duplicates in the input. Sure enough, the error repeats at roughly one minute intervals.
I then tried changing the timeout in the SQL Server instance connection query timeout property from 600 to 0, for unlimited time. No change, still bombs. Restarted SQL Server service, no change, still bombs. The restart was noted in the error log, though, so at least I have another confirmation that I'm looking in the correct place for server activity reports.
November 29, 2012 at 11:16 am
Well, apparently it's some sort of a timing or saturation issue. I put a 100 millisecond pause with DoEvents after every call to SQL Server, and it works. Where it used to bomb around every 1000 records, it now sucked in almost 10,000 without a hitch. Took a while to run, but it didn't crash. I don't get it, though, a load like this should be a walk in the park for SQL Server, even the Express version. I'm fairly well convinced it's somewhere in the communication protocol, not the actual server engine.
However, this module doesn't get used all that much, and I'm slowly building a better way to do the entire task, so I think this will be be good enough for now. I'll continue to experiment and post back if I discover anything significant, like a real solution that works properly, but this workaround has moved the problem considerably down in my list of priorities.
Many thanks to everyone for all the help.
November 29, 2012 at 11:44 am
It's good to hear that you're making progress. Let us know if you need anything else.
November 29, 2012 at 12:52 pm
Banana-823045 (11/29/2012)
Tom, if it's a client setting, then that'd mean configuring all clients that runs the front-end software. Wouldn't it also potentially mess with other softwares? I'd prefer that it was limited to the application or was server-side (assuming the server is private and not hosted or shared in some way)
By "client" I mean the machine running the app, which is a client of the machine running SQL Server - at least that's what I think the issue is about, connection between app machine and database machine. If it isn't, I'm looking at the wrong things - but the error message seems to me to clearly indicate a data comms issue between the app machine (what I call the client) and the SQL machine (what I call the server).
Tom
November 29, 2012 at 1:29 pm
Tom, we're talking at different granularity. My concern is that registry edits you're suggesting is a machine-wide setting and I'm saying it's preferable that it's either an application-wide setting OR server-side setting but not client machine settings. The concern is based on the fact that they may have other software installed that uses the same protocols (after all, ODBC is very common, no?) so that tweaking the machine-level setting may negatively impact other applications, causing more problems in long run.
If I can, I try to tweak the application's settings rather than the registry's settings, and do it server side over client side.
November 29, 2012 at 1:33 pm
Also, to clarify --
L' Eomot Inversé (11/29/2012)
connection between app machine and database machine. If it isn't, I'm looking at the wrong things - but the error message seems to me to clearly indicate a data comms issue between the app machine (what I call the client) and the SQL machine (what I call the server).
For OP, the client is Access so this is not same setup as a web server working with a database; Access is running at the end users' endpoints and connecting to SQL Server directly without a mid-tier layer. If it were a web server or something that, then I'd agree that such tweaking is more viable since we have more control over the application server and database server. Not so for the end user's workstations.
November 29, 2012 at 3:05 pm
Yes, this is Access talking over an ODBC link to a server running SQL Server. There are many individual machines running this app, and more may be added any time. Having to manually tweak setting on a per-client-machine basis is something to be avoided if at all possible.
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply