February 6, 2012 at 8:06 pm
I wonder if anyone else ever encountered the below error or something similar. Mostly the error happens at second update when the data is huge in the table.:angry:
" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"Environment: SSIS executed on application server(4 CPU/8GB RAM). Database hosted on a separate server(16 CPU/18GB RAM on the box). Error occurs on Execute SQL Task that has update statements(Example given below).
No. of rows in the table - between 40 and 50 millions
UPDATE CustTable SET Flag1 = 1,Flag2 = 2 WHERE CustType = X AND CustAge = Y AND CustHeight IN ('A', 'B')
GO
UPDATE CustTable SET Flag1 = 1,Flag2 = 2 WHERE CustType = X AND CustAge = Y AND CustHeight IN ('C', 'D','E')
GO
Tried wrapping update statements in transaction but to no benefit.
Any ideas about what could be the reason? Anyone?
Thanks all for your time.
________________________________________________________________
"The greatest ignorance is being proud of your learning"
February 7, 2012 at 12:36 am
Does the task fail immediately or after some time?
Maybe you got a time-out or something?
Do you have any parameter mapping or resultset configured?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 7, 2012 at 9:33 am
Koen Verbeeck (2/7/2012)
Does the task fail immediately or after some time?Maybe you got a time-out or something?
Do you have any parameter mapping or resultset configured?
1) Mostly the first update completes and as soon as it goes for executing the second, it errors out. So, it fails after some time. (Under progress tab I can see 10% done which is what the first update statement constitute of total)
2) There are no parameter mappings or result sets configured. Just a bunch of update statements.
Also, there is no error when the the number of rows in the table is relatively small. I am testing now by removing the Transactions (Begin Tran/End Tran) for individual update statements. Next, I will try to execute the SQL Statements through SSMS as a single batch. Let's see what i find.
Thanks Koen!
________________________________________________________________
"The greatest ignorance is being proud of your learning"
February 7, 2012 at 9:48 am
Have you checked that this is the only error you're getting?
I've discovered that it's quite common to get this error AFTER you've had a real SQL error (e.g. constraint violation), so if you look a bit further up there's something more informative.
The other thing I've found helpful is to run a trace while you're running the package, looking for User Error Message (under Errors and Warnings) - that can often show you what the original error was.
Hope this helps,
Rachel.
February 7, 2012 at 1:53 pm
Rachel Byford (2/7/2012)
Have you checked that this is the only error you're getting?I've discovered that it's quite common to get this error AFTER you've had a real SQL error (e.g. constraint violation), so if you look a bit further up there's something more informative.
The other thing I've found helpful is to run a trace while you're running the package, looking for User Error Message (under Errors and Warnings) - that can often show you what the original error was.
Hope this helps,
Rachel.
I would seek in the direction that Rachel provides.
It's very strange that the Execute SQL Task would crash on an update statement concerning millions of rows, as it only has to send the data to the database engine and wait for results.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 7, 2012 at 5:06 pm
Koen Verbeeck (2/7/2012)
Rachel Byford (2/7/2012)
Have you checked that this is the only error you're getting?I've discovered that it's quite common to get this error AFTER you've had a real SQL error (e.g. constraint violation), so if you look a bit further up there's something more informative.
The other thing I've found helpful is to run a trace while you're running the package, looking for User Error Message (under Errors and Warnings) - that can often show you what the original error was.
Hope this helps,
Rachel.
I would seek in the direction that Rachel provides.
It's very strange that the Execute SQL Task would crash on an update statement concerning millions of rows, as it only has to send the data to the database engine and wait for results.
Thanks Rachel,Koen.
If only I could run the trace. pfft! Lack of privilages will certainly keep that from happening. I think it could be because of the way the queries are written. Here's why.
I ran the update statememts in SSMS as a single batch and got the below output.
(0 row(s) affected)
The statement has been terminated.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.(0 row(s) affected)
(0 row(s) affected)
The statement has been terminated.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(15495324 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(4628086 row(s) affected)
As we can see the 2nd and 5th statement returned error. Now, 1st and the 2nd statement has similar filter conditions and same is the case with the 4th and the 5th update statement.
Could it be the case that out of any two consecutive update statements with same filter conditions the first one gets executed and the second errors out?
Such as:
--Consecutive update statements with similar filter conditions updating different columns
UPDATE dbo.MYTABLE
SET COL1 = 0
WHERE COL3 IN ('X','Y','Z')
GO
UPDATE dbo.MYTABLE
SET COL2 = 0
WHERE COL3 IN ('X','Y','Z') AND COL4 = 'Some Value'
GO
If I run the satements one by one then I get no error.
What do you guys make of this? Thanks.
________________________________________________________________
"The greatest ignorance is being proud of your learning"
February 8, 2012 at 2:53 am
Run a DBCC CheckDB on the database. If it's bombing out with a severe error, it could well be database corruption.
February 8, 2012 at 3:02 am
Also, what exact version of SQL Server are you running? There are a few bugs related to this error. You need to get your DBA's involved (if you have them!) or get some permissions and check the SQL error log file and see if there are any dump files in the log directory
February 8, 2012 at 3:39 am
Please run the following and post the full and complete results
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
This is an indication that the connection has been terminated at the server. The most common reasons for that are tripping over a high-severity error (though that should also return the error message of said error) or of someone executing KILL on your connection.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2012 at 11:14 am
Thank you HowardW.
HowardW (2/8/2012)
Also, what exact version of SQL Server are you running? There are a few bugs related to this error. You need to get your DBA's involved (if you have them!) or get some permissions and check the SQL error log file and see if there are any dump files in the log directory
PRODUCT_VERSION PRODUCT_LEVELEDITION
9.00.4053.00 SP3 Enterprise Edition (64-bit)
I am trying to get my hands on the error log file. Hopefully, will get it soon and then i will be able to discover what's in there.
🙂
________________________________________________________________
"The greatest ignorance is being proud of your learning"
February 8, 2012 at 11:56 am
GilaMonster (2/8/2012)
Please run the following and post the full and complete results
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
This is an indication that the connection has been terminated at the server. The most common reasons for that are tripping over a high-severity error (though that should also return the error message of said error) or of someone executing KILL on your connection.
Thanks Gail Shaw.
Would CHECKTABLE be sufficient instead of CHECK DB as the latter would just take too much time? Please note that the error is encountered while updating certain data in a staging table. Once data is processed into next table the staging table is truncated.
I checked and found that there's no error logged when SQL server threw this severe error. Moreover, the error is seen even if different environments(identical db but different server) are used. As i mentioned earlier, running one statement at a time results no problem whatsoever which is very strange.
-0-
________________________________________________________________
"The greatest ignorance is being proud of your learning"
February 8, 2012 at 12:03 pm
I'd prefer to see a full checkDB, or just get the results when the next scheduled integrity check runs.
It may not be that, it's just that the disconnect can be a symptom of corruption, so best to get that checked.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2012 at 2:35 pm
CHECKDB came out clear. It ran successfully and didn't return any error.
I guess, I will have to perform some rigorous testing in order to get to the root of this weird error.
Thanks,
SF
________________________________________________________________
"The greatest ignorance is being proud of your learning"
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply