December 14, 2012 at 1:10 pm
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)
December 14, 2012 at 1:20 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 14, 2012 at 1:57 pm
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; Theyll drag you down to their level and beat you with experience
December 14, 2012 at 2:11 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 14, 2012 at 3:21 pm
Sean,
I wonder if the OP has a case-sensitive collation in place and just miscapitalized something?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 14, 2012 at 3:31 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 14, 2012 at 3:43 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 14, 2012 at 7:21 pm
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.
December 14, 2012 at 10:19 pm
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; Theyll drag you down to their level and beat you with experience
December 15, 2012 at 7:42 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply