0

I created several views on SQL Server whose task was to retrieve data from an Excel file (1 view from a different data source/Excel file) whose purpose was to synchronize the data in the database with the data in Excel and later synchronize this data which will be displayed in the weekly report, but sometimes the process is locked by a wait type "OLEDB".

I can't find the root use, but my biggest problem is that I can't kill the process using:

KILL <spid>

Then, I also tried using:

KILL <spid> WITH STATUSONLY

I got:

"SPID <spid>: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds."

The process is still running with the query status running with a long execution time and this makes me dizzy when I read an article in Microsoft in the Detailed blocking scenarios section which states:

An actively running query has acquired a lock, and the lock is not released (affected by transaction level isolation), so another session will wait until they are released.

And that's right, all the queries I do on the database are deadlocked. I've tried the Resolution they embedded and it still doesn't work.

Improve this question
2
  • 4
    Be aware that using the ACE driver in a server environment (non UI) is not supported. It works sort of, but lots of issues, so you won't get any help from Microsoft. Use a client tool such as Import/Export Wizard instead. Commented Jun 6, 2024 at 11:12
  • Just a nitpick but a deadlock in sql server is resolved by killing some process, so you're probably just having a long block. I'm guessing either someone have the excel file opened or some other COM juju is being stuck somewhere while working with excel file. You should restructure your processing, the best would be to avoid excel altogether, but if you must, use some SSIS task or python / powershell / whatever-language-youre-comfortable-with script to read data into sql server and back, thus avoiding the blockage Commented Jun 7, 2024 at 7:42
Related questions
4347
UPDATE from a SELECT
3327
How to add a column with a default value to an existing table in SQL Server?
2114
LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.