SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting "severe error" when referencing a table that doesn't exist


Getting "severe error" when referencing a table that doesn't exist

Author
Message
jeffem
jeffem
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 346
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)
sgmunson
sgmunson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6836 Visits: 4361
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)
Smile Smile Smile
Health & Nutrition
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6638 Visits: 7391
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" ;-)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25831 Visits: 17509
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 Modens 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)
sgmunson
sgmunson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6836 Visits: 4361
Sean,

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

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25831 Visits: 17509
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 Modens 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)
sgmunson
sgmunson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6836 Visits: 4361
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)
Smile Smile Smile
Health & Nutrition
jeffem
jeffem
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 346
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.
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6638 Visits: 7391
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" ;-)
sgmunson
sgmunson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6836 Visits: 4361
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)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search