Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

A severe error occurred on the current command.The results, if any, should be discarded. Expand / Collapse
Author
Message
Posted Friday, April 29, 2011 8:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 29, 2011 8:23 AM
Points: 2, Visits: 0
Guys, I guess there are several reasons which can cause this error. In my case it happend because i was starting transaction and without commiting, my SP was returing. When I commit before return statement it solved my issue. Probably these are unwanted execution flow which if some one follow then we will end-up with such generic error.

Post #1100890
Posted Friday, April 29, 2011 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 29, 2011 8:23 AM
Points: 2, Visits: 0
Guys, I guess there are several reasons which can cause this error. In my case it happend because i was starting transaction and without commiting, my SP was returing. When I commit before return statement it solved my issue. Probably these are unwanted execution flow which if some one follow then we will end-up with such generic error.

Post #1100892
Posted Monday, July 11, 2011 9:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
I just encountered this error.

The steps were
Begin Tran
TRUNCATE Table1

INSERT INTO Table1(Columns...)
SELECT Columns From Table1Backup

This would give the error at the INSERT statement

Changing the Truncate Table to DELETE FROM Table1 allowed the process to work. I would guess that the way Truncate works is preventing inserting into the table (due to locking?) until the Truncate has been committed.

Perhaps other situations are similar with an attempt to update or insert into a table that is locked within the transaction.
--
JimFive
Post #1139834
Posted Tuesday, July 12, 2011 9:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 26, 2011 3:34 PM
Points: 27, Visits: 75
I get the same error when run the following query:

FETCH name_cur INTO @dbName, @tableName
WHILE @@Fetch_Status = 0
BEGIN
SET @sql -....
insert into [dbo].[Table1] (obj_name, obj_type)
EXEC (@sql)
UPDATE [dbo].[Table1] SET db_name = @dbName ,table_name = @tableName WHERE db_name IS NULL
FETCH name_cur INTO @dbName, @tableName
END
CLOSE name_cur
DEALLOCATE name_cur

Can anybody help, why the error comes in the above query?
Post #1140432
Posted Tuesday, July 12, 2011 1:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
This is a guess based on the Truncate example.

Your Cursor is based on the same table that is being updated in the INSERT and Update statements. The Cursor is holding a lock on that table. Try Declaring your Cursor as READ_ONLY.

Also, instead of:
INSERT INTO Table1(obj_name, Obj_type)
EXEC (@SQL)

UPDATE Table1 Set ...

You could put the @dbName and @tableName in the SQL Statement and do it as one statement.

Even Better: Find a way to do this without a cursor. There doesn't seem to be anything in there that requires working on one row at a time.
--
JimFive
Post #1140605
Posted Tuesday, July 12, 2011 2:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 26, 2011 3:34 PM
Points: 27, Visits: 75
Thank you James.
But my cursor depends on different table suppose (main_table). and I want to find dependency of table read from main_table. And I am storing it into another table. I will try to do UPDATE in one statement and making cursor READ_ONLY. But i do not think its because of the lock on cursor.
Post #1140669
Posted Sunday, September 4, 2011 9:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:33 PM
Points: 109, Visits: 419
Hi there,

I have seen the same issue on my production box.
The problems looks like SQL Server works incorrectly with several sub-queries using <> on NOT IN expressions.
There is a workaround. You can use JOINs instead of sub-queries in order to overcome this issue.
Post #1169825
Posted Thursday, October 6, 2011 8:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 8:14 PM
Points: 3, Visits: 50
I have the same error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

When I execute:

select top 10 * from sysindexes

I have SQL SERVER 2005 service pack 4
Any idea???
Post #1186900
Posted Friday, October 7, 2011 12:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
Please run the following and post the full and complete results

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1186941
Posted Monday, October 10, 2011 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 8:14 PM
Points: 3, Visits: 50
The result of
DBCC CHECKDB ('DatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS

is:
Command(s) completed successfully.
Post #1187993
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse