April 15, 2009 at 1:36 pm
JacekO (4/15/2009)
Lynn Pettis (4/15/2009)
If sys.sp_reset_connection being executed by one application is causing your global temporary table to be dropped for another applicationThis the big IF. This sp is a strange beast. I tried to exeute it directly in query window to test it's impact on the temp tables but the server claims it does not exist. So who the heck knows what it does....
I agree, as I tried testing it as well. But if this extended stored procedure is causing the problem, then they need to isolate these applications from each other and having them use separate instances will accomplish that isolation.
April 15, 2009 at 1:51 pm
I believe that sp_reset_connection is only available via ODBC/ADO/ADO.NET to manage connection pooling.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2009 at 1:58 pm
That is correct check below for more about the stored procedure and the poster does not understand ANSI SQL requirements because the connection context of transaction was changed in SQL Server 2008 because ANSI SQL changed the transaction connection context. So it is get on the phone with vendor.
Kind regards,
Gift Peddie
April 15, 2009 at 3:38 pm
Is moving the application to another server is the only solution?
Can two application ever use the same connection?Global temporary tables are doesn't dropped as long as session is open so how can sp_reset_connections from another application can drop global temp table?
April 15, 2009 at 5:02 pm
Is moving the application to another server is the only solution?
No it can be fixed by changing the connection pooling code and making sure the connection object is disposed correctly. Call the vendor.
Can two application ever use the same connection?
In 2005 no however if you are using the Enterprise library for data access both SSRS and your Winform application could use a shared connection.
Global temporary tables are doesn't dropped as long as session is open so how can sp_reset_connections from another application can drop global temp table?
That is the question for your vendor and it is easy to see in the application code which you don't have. It is not complicated problem but code needs to be modified in Data Access of .NET or Java.
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
Kind regards,
Gift Peddie
April 16, 2009 at 12:06 am
JacekO (4/15/2009)
...I tried to exeute it directly in query window to test it's impact on the temp tables but the server claims it does not exist. So who the heck knows what it does....
Some jdbc drivers generate temporary stored procedures for every sql statement they execute.
I've seen this behaviour can be modified.... but cannot recall the parameter name to provide.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 16, 2009 at 7:18 am
ruchika thapa (4/15/2009)
Global temporary tables are doesn't dropped as long as session is open so how can sp_reset_connections from another application can drop global temp table?
Hold a second on this one.
I do not think anyone here said explicitly that sp_reset_connections drops the global temp tables. It was just a speculation that no one here could prove or disprove so far. There is no Microsoft provided documentation I could found that will specify what sp_reset_connections does internally.
It is quite possible that your application with the problems is poorly written and it is droping the temp table itself. You have few options.
1. Use the tracing to find out what is going on.
2. If you have a different SQL box available you might want to split the apps between the servers and see if this fixes the problem.
3. Call the vendor and have them fix the problem.
The options are not numbered in any particular order and I am not suggesting which one to use. You have to make the call yourself.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 16, 2009 at 7:33 am
Just so everyone following this thread knows, ruchicka has opened a new thread about the behavior of sp_reset_connection.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 16, 2009 at 7:45 am
The question is posted in the wrong section and the actual question is wrong because connection is not per server but per database. The only way another application will close a connection is if both are using the same database.
Kind regards,
Gift Peddie
April 16, 2009 at 1:21 pm
Makes sense. everyone is suggesting to talk to vendor but vendor is saying there is some issue with SQL SERVER. Anyway i have run the profiler so hopefully will find out how and who is dropping this global temporary table. Is it the the application itself or some other application in the server and let you all know....
April 16, 2009 at 1:36 pm
That is nonsense ask them to send you the connection pooling code and how the connection object is dropped. You should also ask them if they are using a DataSet or a DataReader ADO.NET object.
Kind regards,
Gift Peddie
April 16, 2009 at 1:42 pm
Gift Peddie,
In the original post it was indicated that the application that uses the global temp table is not the same application that uses the connection pooling (sp_reset_connection). At least I read it that way. So if I am not mistaken the connection pooling code will not help in this case.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 16, 2009 at 1:50 pm
There is no way for an application to close another connection if both are not using the same database, the reason all SQL Server connection string include a catalog which is the database name. So the only way one could be closing the other is if both are using the same database with same data access.
Kind regards,
Gift Peddie
April 16, 2009 at 1:56 pm
Connection pooling can not span multiple processes. So even if the other application is using the same method accessing the database and the same database they each will have a different, independent pool.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 16, 2009 at 2:01 pm
That is assuming the code is clean and what is using the global temp table is not some process that is propagated into SQL Server from another layer of the application.
One more thing I have cleaned up many connection pooling related errors at the MSDN forums most are related to the connection object not disposed because the programmer thinks the system will clean it up.
Kind regards,
Gift Peddie
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply