[Last Call] Learn how to a build a cloud-first strategyRegister Now


Can I close DataReader using T-SQL commands in SQL Server Management Studio 2005

Posted on 2011-10-12
Medium Priority
Last Modified: 2013-04-21
I have a job that runs every night that executes SQL commands to import data from a flat text file to a table in my database (after truncating the file).  For some reason, it appeared to get stuck last night.  I went to the Job Activity Monitor and stopped the job (which I probably shouldn't have done).  I want to try to import the data again, but it appears the table is "locked."  If I try to open the table, I get an error message that says "Lock request time out period exceeded." If I look at the table properties using Mgmt Studio, it takes a while to bring them up, then several of the properties say "There is already an open DataReader associated with this Command that must be closed first."  Is there a way to close the datareader?  Do I have to just delete the table and start over?
Question by:EDCTECH
  • 3
  • 2
LVL 28

Expert Comment

ID: 36957192
Have you tried killing all connections and/or restarting the server?

Author Comment

ID: 36957474
I don't want to restart the server because there are other applications running on it.  
I found the session id for the task that had frozen up.  I tried to Kill that session, and the following message came up: SPID 57: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
The session still showing as suspended.  Any other ideas?
LVL 28

Expert Comment

ID: 36957829
If that is the case, I would say refer to this: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/37b67e29-9107-4837-a8fe-734fe83d09f9/

You can restart the SQL service which will have a shorter impact than a full server restart.

Accepted Solution

EDCTECH earned 0 total points
ID: 36958501
Thank you very much for your assistance.  While I was researching how to restart the SQL Server Instance service, the rollback must have completed and the session was gone.  I was able to open the table to verify that there were no records in in, and I was able to import the data into the table manually using the Import/Export wizard.  Hopefully it will run okay tonight.
Thanks again.

Author Closing Comment

ID: 39098116
The problem resolved itself without intervention

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question