November 12, 2007 at 11:08 am
We’ve come across a problem with using SQL Server to communicate with an Oracle instance. We have several STPs that do something sillimilar to the following:
TRUNCATE TABLE TBL_BK
INSERT INTO TBL_BK (FIELDS)
SELECT FIELDS
FROM OPENROWSET(‘OracleDB’,’user’,pwd’,’SELECT FIELDS FROM TBL_ORA_BK’) T1
This works fine until Oracle times out or returns a ORA error. When looking at the processes in SQL Server the status for the process that fails is still executing. SQL Server will keep this process open. If we try to KILL the process, it will remain open, in sp_who2 you can see the process flagged as KILLED/ROLLBACK. As such when we try to rerun this STP there is a lock on our table (TBL_BK). The only way to free the table is to either stop and start SQL Server, or as we’ve found kill the TCP connection (we’ve used TCPView from sysinternals.com) by searching for the remote IP of the Oracle server and kill the connection. SQL Server must detect a message from the TCP area that the connection has closed and then sees the Kill message and terminates (or the error in connection is enough for the STP to error).
To prevent this from occurring on our Live server we have written our Application Service to kill all remote IP connections to the oracle instance if an error occurs. This way we can ensure that the next load we won’t encounter an issue.
Is this the best solution? We're being asked to move to a consolidated environment where will not have this level of control.
Stop start SQL instance – major impact: loss of database
Hotfix (is there one)
Recode service to import data using VB.NET – very slow, this will add another layer of processing. SQL Server communicating directly to Oracle is far quicker.
Kill the connections via a command shell – may still need an exe on the server to accept these commands?
November 16, 2007 at 2:38 am
what is your sql server service pack level?
November 16, 2007 at 9:26 am
SQL Server 2000 SP4
Since writing I believe I now know when the process locks.
If I have 2 STP that use a openrowset query, similar to above, to the same Oracle environment but different views. if one of these STPs gets an error from Oracle (ORA-03113) the other STP will not error, but remain stuck on SQL Server.
November 18, 2007 at 9:05 pm
My service pack SP 4 hotfix and the build number is 8.00.2187
I remember when I update to SP4 (8.00.2039) i face this problem before.. kill the process but the spid always in kill/rollback until restart sqlserver
This is for my SQL server environment..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply