January 28, 2008 at 3:13 pm
Recently we went live using SQL Server 2005 for our MS Dynamics Great Plains application. The app had been working fine on SQL Server 2000. On SQL Server 2005 the Dynamics users are loosing connection to SQL SERVER 2005 and the application doesn't know it and the user doesn't know if. The same spid number is then reused by the server resulting in the app thinking one login has the spid and SQL Server 2005 having the spid assigned to a different login. This is playing havoc with the temp tables used by the app..
Does anyone know what could cause this to happen?
We are NOT using dynamic ports for SQL Server. It is configured with the default static port of 1433. The TCP/IP KeepAlive is set to the default of 30000 millisecs.
Any insight you might have will be greatly appreciated.
Thank you,
Ellen
January 28, 2008 at 3:31 pm
Please don't cross-post.
Do you by chance have connection pooling turned on in your ODBC connection? Mine look to be on by default. That may be causing your issue, since it's going to persist connections after the client drops off (the idea was to speed up reconnect, but it can keep some objects around for substantially longer than they need to be.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 4:15 pm
The client ODBC connections do have connection pooling turned on by default but I think that would just apply to their own connection. So if the server drops the connection, the pooling on the client side would not make a difference to the app. At least that is what my assumption is. It seems like there is a problem on the server side. The default keep alive is in effect on the server network TCP configuration.
January 28, 2008 at 7:32 pm
All due respect, but the connection pooling when dealing with SQL server CAN cause SQL server to "hang on" to various things. especially if it's detecting dropped connections. Meaning - it's expecting the connection to come back online.
These guys explain it better than I can... It's a good read:
http://www.sql-server-performance.com/tips/odbc_oledb_p1.aspx
Keep in mind - this would help deal with the SPID's getting reused and giving you objects you didn't created. It's NOT going to fix the root cause (i.e whatever is dropping the connections).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply