April 8, 2015 at 7:14 am
Some question - wrong answer
Msg 2760, Level 16, State 1, Line 1
The specified schema name "tmp" either does not exist or you do not have permission to use it.
April 8, 2015 at 8:20 am
free_mascot (4/8/2015)
Carlo Romagnano (4/8/2015)
MyDoggieJessie (4/7/2015)
This assumes that you have a schema in the database named "tmp" otherwise it fails because the schema does not exist.+1
Ahi, ahi, ahi, ahi, ahi, ahi!!!!!!!!!!
:w00t:
+1
Yeah, me too.
April 8, 2015 at 8:42 am
Sean of the Lynchmob (4/8/2015)
I got this when I ran it:Msg 2760, Level 16, State 1, Line 1
The specified schema name "tmp" either does not exist or you do not have permission to use it.
.....and no table created. So I have to disagree with the "correct" answer.....
+1
April 8, 2015 at 9:57 am
It was not clearly mentioned about the schema!!
I hope that the question should be loud and clear , rather for users to guess around
Thanks.
April 8, 2015 at 10:52 am
This is an interesting question. While I haven't run across this specific case before, I have had developers create tables with things like double-quotes or even square brackets in them, and had to retool some of my procedures to handle such cases.
April 8, 2015 at 11:42 am
My question is given I have a schema named tmp why would I want to create a table named like that in the real world, other than to prove a point?
April 8, 2015 at 12:26 pm
For People posting questions for QoTD, Please post questions assuming default schema.
April 8, 2015 at 12:52 pm
Steve, did anyone look at this QotD before it was posted?
April 8, 2015 at 2:02 pm
To address the point of the question, it is interesting/important to note that the same rules apply to database, schema and column names so you could in fact have a whole database with no visible names. I wonder if there are some security applications for this information.
April 8, 2015 at 2:50 pm
Lon-860191 (4/8/2015)
My question is given I have a schema named tmp why would I want to create a table named like that in the real world, other than to prove a point?
You are assuming it was done on purpose. I've seen some very odd table names created by accident, often via tools or applications.
April 8, 2015 at 4:46 pm
Richard Warr (4/8/2015)
It should have been possible to attempt an answer without just running the code. And, given the title of the question, it should have been clear that the "tmp" error was just an oversight and not really relevant.
Totally agree. My playpen database has a schema called temp (for sticking short term junk in) so although I noticed tmp wouldn't work for me I was happy with the idea that there might be a schema called tmp (whch would presumably have roughly the same function).
Although I think it's a good question, I think it's probably a bad thing that quoted identifiers allow us to use such names for tables. A name consisting of the unit separator character all on its own is surely not useful (except perhaps for code obscurity competitions).
And it would have been a better question if either it had either used a schema which exists by default or specified that the schema it used existed.
Tom
April 8, 2015 at 5:38 pm
DBA From The Cold (4/8/2015)
MyDoggieJessie (4/7/2015)
This assumes that you have a schema in the database named "tmp" otherwise it fails because the schema does not exist.Yep:-
Msg 2760, Level 16, State 1, Line 1
The specified schema name "tmp" either does not exist or you do not have permission to use it.
+1
April 8, 2015 at 7:08 pm
prvmine (4/8/2015)
DBA From The Cold (4/8/2015)
MyDoggieJessie (4/7/2015)
This assumes that you have a schema in the database named "tmp" otherwise it fails because the schema does not exist.Yep:-
Msg 2760, Level 16, State 1, Line 1
The specified schema name "tmp" either does not exist or you do not have permission to use it.
+1
+1, unfortunately, doesn't even begin to describe that. 1 <> n where n -> ?
April 9, 2015 at 1:40 am
Sorry people for the confusion. As many have pointed out, I made a mistake with the tmp schema. In my databases I always have a tmp schema, which is emptied very often (at least daily), and is therefor well suited for test queries. Obviously the use of the tmp schema was just an oversight, and not the core of the question. I didn't want to make a trick question. At work we needed to know what table names are valid and which are illegal. Turns out, everything is legal, including keywords, brackets, non-printable characters and even carriage returns.
This was my first QotD, next time I'll remember to use the dbo schema.
April 9, 2015 at 7:24 am
MyDoggieJessie (4/7/2015)
This assumes that you have a schema in the database named "tmp" otherwise it fails because the schema does not exist.
Yes! Not to be too literal, but simply running the script returned this for me:
Msg 2760, Level 16, State 1, Line 1
The specified schema name "tmp" either does not exist or you do not have permission to use it.
Good question, but the bit about the tmp schema should have been clearly stated.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply