Thanks, it worked for me.

sys.sql_modules does not work here because USER needs to load view script from TOAD and TOAD uses Sys.syscomments.

yes it is sys.syscomments.

It says" grant or revoke VIEW DEFINATION is not compatitable wiht the object(error:4606)

IIS reset??? Does all the application will have IIS set. I know MOSS has IIS but don't know about the other application have IIS setup that is using this global...

jacek0 is right. application that uses global temp table is not the same application that exec sp_reset_connection. And they arenot using same databases since they are two different application. Moss...

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...

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...

Old hand,

"So if there are multiple applications accessing the server and one of them is using a global temp table and another calls sp_reset_connection could that effectively mess up the...

i was able to get the object ID. I will run your script next time when application hangs to find out what deleted the temp table. I have even run...

This is the third party application. I am just DBA administoring the application

There is two different connection since it is two different application i am talkign about. There are 2 application connected to one sql server. Sp_reset_connections is executed by one application...

what event i have to start to collect information regarding network issues?

I hope this is not sql server isssues or port issue.

I have set the profiler for error event.

I am planning to run profiler until erorr occurs again and application stops sending updates. Is that OK?

I set the profiler to audit fail login, login, sql:batchstarting, sql:batchcompleted.

Do i need to collect some other events?

How can i verify what is going on the server? I checked the error log, there is nothing there.

Should I turn on the profiler? What event should i capture...