Today we are going to have very simple and interesting question.
Run following T-SQL Code in SSMS. There are total of five lines. Three T-SQL statements separated by two horizontal lines.
SELECT MAX(OBJECT_ID) FROM sys.objects
______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects
______________________________________
SELECT COUNT(OBJECT_ID) FROM sys.objects
Now when you execute individual lines only it will give you error as
Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure '______________________________________'.
However, when you executed all the five statement together it will give you following resultset.
What is the reason of the same?
Please leave your comment as answer. I will discuss the answer of this question on tomorrow’s office hours.
One random correct answer will win my SQL Wait Stats [Amazon] | [Flipkart] | [Kindle] copy – anywhere in the world.
Reference: Pinal Dave (https://blog.sqlauthority.com)
82 Comments. Leave new
you are aliasing sys.objects table with ______________________________________
this line (or what’s in it) is irrelevant so long as it is not a reserved keyword.
It was as posted. When I replaced with other characters, then it didn’t work. So, is it another comment character similar to ‘-‘
I have executed the statements both the way and there is no error.
But try to executed the statements with line it get give error.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘______________________________________’.
Is it version specific ?
The ___________ is treated as a table alias.
-rojipt
Sir , I am able to run this even individually as well as all together in sql server 2008 R2.
Regards,
sanjeev Kumar
Hi Pinal,
I have executed the statements both the way and there is no error.
Is it version specific ?
If i am selecting the “______________________________________” and executing then its giving this error:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘______________________________________’.
Same here ..on 2k8 it does not give any error..
Ohh… Come on Pinal.
This doesn’t look like SQL test but rather brain teaser.
To specify that a command continues on the next line, we use a hyphen (continuation character) after all the text on the line to be continued.
So in this case when we are executing all together, its working fine because its treating all the lines it continuation but executing single line giving error because its not getting the next executable statement.
Hi all,
I try to run that code in both scenario. After that, i add a new line at the top of that existing code
______________________________________
SELECT MAX(OBJECT_ID) FROM sys.objects
______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects
______________________________________
SELECT COUNT(OBJECT_ID) FROM sys.objects
It displays the Messages tab as
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘______________________________________’.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
But in Results tab displays correct answer.
I think even if run the sproc no need to add prefix Exec or Execute keyword.
Like that we try to run a single ___________________. (or any text)
It says :
Could not find stored procedure ‘______________________________________’.
so, It does not consider any lines or text below the Select statement. If the line or text above the Select statement returns error message and result also.
Good one! Table aliasing with ___
no errors Sir
hi pinal…
i executed the above statement individually and it is working fine…!
why should there be any error ?
Thanks and Regards
Akiii
Hi pinal,
i have executed in both ways.
No errors.
and i am using ssms express edition.
When we execute this line ______________________________________, we get the error, because it doesnot belong to anyone, but if we execute all of them “______________________________________” behave like alias.
Sir, I am able to run both individually and combined…Thanks for your info..I expect more questions from you like this…
This ______________________________________ line treated as sys.object alias name.
We cannot execute any column name or dabase object name without any command like SELECT,INSERT,UPDATE,DROP
SQL server is unable to recognize the line ______________________________________, if this line is before the SQL Statement SQL Server is treating this line as a Stored Procedure and searching for this object but its does not exists, that is the reason it is throwing the error, it is also treating the line and select query are different parts. when we are executing all the statements at a time, the line is treating as an alias of the column. That is the reason it is not throwing any error.
i executed the query both way in SQL 2008 R2 working fine
if we assign some alias deliberately at the last. Following statements wont work.
SELECT MAX(OBJECT_ID) FROM sys.objects T1
______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects T1
______________________________________
SELECT COUNT(OBJECT_ID) FROM sys.objects T1
But in the following way, its considering the line as alias.
SELECT MAX(OBJECT_ID) FROM sys.objects
______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects
______________________________________
SELECT COUNT(OBJECT_ID) FROM sys.objects
Reason behind giving the following message:
Could not find stored procedure ‘___________’.
‘_’ is part of T-SQL Identifiers.
Here you can find its explanation:
Regards,
F. Ahmed
Hi pinal,
This can can happen in SQL Server, and can be due to a number of reasons. For example:
1. The user you are connecting as does not have SELECT, UPDATE, INSERT, DELETE, EXEC permissions on the object;
2.You are not logging in / connecting as the user you expect;
3.You are referencing the object without an owner name prefix (or with the wrong owner name);
4.You are connected to the wrong database;
5.You are, in fact, spelling the object’s name incorrectly.
When executing that long line without SQL STATEMENTS, it appears to the SQL SERVER as a stored procedure, which however is nowhere to be found.
So it returns the error.
SQL Server considers ‘______________________________________’ as unicode.
SQL server does not take inputs as unicode unless specified explicitly with -u.