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

Getting "severe error" when referencing a table that doesn't exist Expand / Collapse
Author
Message
Posted Friday, December 14, 2012 1:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:48 AM
Points: 403, Visits: 295
This is the error (all databases):

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

This is example code that receives the error:

SELECT * 
INTO #Announcements
FROM Announcements

SELECT *
FROM Announcements A1
INNER JOIN #Announcements A2
ON A1.AnnouncementID = A2.AnnouncementID

This is example code that does NOT receive the error:

SELECT * 
INTO #Announcements
FROM Announcements
GO

SELECT *
FROM Announcements A1
INNER JOIN #Announcements A2
ON A1.AnnouncementID = A2.AnnouncementID

Other things that still/also get the error:
- Even if I check for existence of table before a SELECT INTO
- CREATE TABLE instead of SELECT INTO
- Attempting to select from a non-existent table (intentionally misspelled one, as a test)
- Opening Activity Monitor (instant error after the AM pane opens, but before any data is retrieved)

Also, this error is occurring in procs that create temp tables and use them in select statements. And since I can't put a GO in the middle of a proc creation script, this is problematic. I have restarted the service but can not yet reboot the server (just for availability purposes).

My @@VERSION info:
- Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (Intel X86) Feb 25 2011 14:22:23 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
Post #1396802
Posted Friday, December 14, 2012 1:20 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 1,616, Visits: 2,119
You haven't indicated why you chose to create a copy of a table just to join it to the original. Unless you're going to join in a way that causes a performance problem, creating a SELF JOIN isn't an inherently bad thing. So, how about:

SELECT * 
FROM Announcements A1
INNER JOIN Announcements A2
ON A1.AnnouncementID = A2.AnnouncementID

Of course, with this query, you'll have two copies of every field in the original table showing up in the result set, and I'm not sure why you would want that, so you might want to elaborate on what your objective is.


Steve
(aka sgmunson)

Internet ATM Machine
Post #1396805
Posted Friday, December 14, 2012 1:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
It works fine for me both ways...I dont get the error, seems to me you have something configured differently within your SSMS?


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1396812
Posted Friday, December 14, 2012 2:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
There seems to be something a little odd going on. I agree that from what you have posted creating a temp table as a copy seems like complete overkill. Have you tried actually indicating that the insert is done by adding the semicolon to the end of the statement?

SELECT * 
INTO #Announcements
FROM Announcements;

SELECT *
FROM Announcements A1
INNER JOIN #Announcements A2
ON A1.AnnouncementID = A2.AnnouncementID



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1396813
Posted Friday, December 14, 2012 3:21 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 1,616, Visits: 2,119
Sean,

I wonder if the OP has a case-sensitive collation in place and just miscapitalized something?


Steve
(aka sgmunson)

Internet ATM Machine
Post #1396825
Posted Friday, December 14, 2012 3:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
sgmunson (12/14/2012)
Sean,

I wonder if the OP has a case-sensitive collation in place and just miscapitalized something?


That could be...didn't even think about being a case-sensitive collation.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1396827
Posted Friday, December 14, 2012 3:43 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 1,616, Visits: 2,119
And I wouldn't have either, except for having just yesterday copied some code out of a post that wasn't in case agreement and pasting into a query window in SSMS connected to my local case-sensitive instance, and had I not just had to spend the extra time correcting case, I wouldn't have even thought about it. Funny - I've always made my own instance case sensitive, but never thought twice about this kind of problem connecting to that potential.



Steve
(aka sgmunson)

Internet ATM Machine
Post #1396830
Posted Friday, December 14, 2012 7:21 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:48 AM
Points: 403, Visits: 295
Okay, it was foolish for me to have posted confusing code.

This is not any actual business logic that exists, but rather is a simplified example that causes the error. I had to leave urgently and was describing all of the things I could think of to test. All of my real life statements causing the issue had joins with the temp table, and I hadn't tested a straight select from the newly created object. I've now done a little more testing...

This still receives the error:
SELECT * 
INTO #Announcements
FROM Announcements;

SELECT *
FROM #Announcements;

I get the error ANY time I create a new table and try to reference that table in the same transaction. Semicolons don't fix it, but GO statements do. Whether I use temp or permanent tables doesn't matter.

I found it odd (and possibly very revealing for those who know what happens behind the scenes) that Activity Monitor immediately produces the same error, as though it's opening generates a table it reads from.

Anyway, I regretfully didn't post a question at all with my original post but left it implied. What I may need here is DBA type assistance, whereas I only know T-SQL development and have no DBA resource available.

I did a CHECKDB, but considering I get the behavior on all databases, it's no surprise that it didn't help. I don't know what other kind of script/commands to do to test why this is not working, when it has been working for years, literally.

I restarted the services with no success. I will be rebooting tonight, but other than that, I'm kind of lost.
Post #1396850
Posted Friday, December 14, 2012 10:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
Interesting...

Please post your full code causing the errors, including DDL for all the tables needed, and the scripts needed to populate at least a few rows into each table. I can't think of any reason why you cannot insert data into a temp table and then immediately JOIN to it unless you are using table variables, or you have something running outside the scope if the particular spid running the SELECT <<>> INTO.

I get the error ANY time I create a new table and try to reference that table in the same transaction. Semicolons don't fix it, but GO statements do. Whether I use temp or permanent tables doesn't matter.
Are you instantiating an actual transaction somewhere?

I found it odd (and possibly very revealing for those who know what happens behind the scenes) that Activity Monitor immediately produces the same error, as though it's opening generates a table it reads from.
It is weird that you would notice any error in the Activity Monitor as a result of this...

I cannot reproduce this...unless someone has come across this (which I still think is a SQL configuration issue) we will need DDL and some sample data to test...


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1396876
Posted Saturday, December 15, 2012 7:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 1,616, Visits: 2,119
jeffem (12/14/2012)
Okay, it was foolish for me to have posted confusing code.

This is not any actual business logic that exists, but rather is a simplified example that causes the error. I had to leave urgently and was describing all of the things I could think of to test. All of my real life statements causing the issue had joins with the temp table, and I hadn't tested a straight select from the newly created object. I've now done a little more testing...

This still receives the error:
SELECT * 
INTO #Announcements
FROM Announcements;

SELECT *
FROM #Announcements;

I get the error ANY time I create a new table and try to reference that table in the same transaction. Semicolons don't fix it, but GO statements do. Whether I use temp or permanent tables doesn't matter.

I found it odd (and possibly very revealing for those who know what happens behind the scenes) that Activity Monitor immediately produces the same error, as though it's opening generates a table it reads from.

Anyway, I regretfully didn't post a question at all with my original post but left it implied. What I may need here is DBA type assistance, whereas I only know T-SQL development and have no DBA resource available.

I did a CHECKDB, but considering I get the behavior on all databases, it's no surprise that it didn't help. I don't know what other kind of script/commands to do to test why this is not working, when it has been working for years, literally.

I restarted the services with no success. I will be rebooting tonight, but other than that, I'm kind of lost.


Did you check to see if the instance you are using is case-sensitive? You just need to look at the collation, and if it has CI in the name, that means it's NOT, whereas if it has CS in it, then it IS case sensitive. The reason I asked this in an earlier post is that if you don't know the instance's default collation is case sensitive, all you have to do is create a table with all lower case letters and then try to reference it with just 1 of those letters capitalized, and you'll get an error (probably the same one you are getting now), because from SQL Server's perspective, the table you asked for, doesn't exist. When case sensitivity is the default collation, you have to spell table names EXACTLY as they were spelled when created, and the luxury of not worrying about capitalization ceases to exist. Thus I wonder if someone changed the default collation ? Just something to check to be sure this isn't the cause, before getting too crazy searching for some kind of bug.


Steve
(aka sgmunson)

Internet ATM Machine
Post #1396895
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse