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 12»»

Question of the Day for 22 Mar 2006 Expand / Collapse
Author
Message
Posted Wednesday, March 15, 2006 6:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the Question of the Day for 22 Mar 2006 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=735.
Post #266088
Posted Wednesday, March 22, 2006 1:40 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32

Knowing nothing about how schemas in SQL 2K, I guessed you would get a syntax error, wrongly as it turned out. But when I tried the script, it turned out that answer C. seems to be right after all, I get the error

Server: Msg 195, Level 15, State 10, Line 1
'SCHEMA_NAME' is not a recognized function name.

Is SCHEMA_NAME a SQL Server 2005 function?


David



If it ain't broke, don't fix it...
Post #267480
Posted Wednesday, March 22, 2006 2:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870

Category : SQL Server 2005 - TSQL
Question - Worth 2 Point(s):
What will be the result of the last SELECT statement if you execute the T-SQL script below as a sysadmin.

Question category was SQL 2005. There's no such thing as schemas in SQL 2000




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #267484
Posted Wednesday, March 22, 2006 7:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32

There are schemas in a limited form in SQL Server 2000. Seem to be a way to create objects and grant permissions in a single logical transaction...

BOL says

"CREATE SCHEMA provides a way to create tables and views and to grant permissions for objects with a single statement. If errors occur when creating any objects or granting any permissions specified in a CREATE SCHEMA statement, none of the objects are created.

The created objects do not have to appear in logical order, except for views that reference other views. For example, a GRANT statement can grant permission for an object before the object itself is created, or a CREATE VIEW statement can appear before the CREATE TABLE statements creating the tables referenced by the view. Also, CREATE TABLE statements can declare foreign keys to tables specified later. The exception is that if the select from one view references another view, the referenced view must be specified before the view that references it."

When I tried the script in SQL Server 2000 everything worked up to the point where I tried to create the second table.

David



If it ain't broke, don't fix it...
Post #267553
Posted Wednesday, March 22, 2006 8:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 20, 2011 10:51 AM
Points: 272, Visits: 45

Using 2005, I received the following message.

SELECT SCHEMA_NAME(schema_id), name FROM sys.objects

WHERE name = 'Table1'

Msg 195, Level 15, State 10, Line 1

'SCHEMA_NAME' is not a recognized function name.

Originally, I had expected an invalid object message ( sys.objects ), and that' just what I got when I tried.

SELECT name FROM sys.objects

WHERE name = 'Table1'

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.objects'.

Post #267614
Posted Wednesday, March 22, 2006 9:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 16, 2013 8:08 AM
Points: 1,987, Visits: 210
I was hoping for the synthax [sic] error, never seen one of those before 
Post #267626
Posted Wednesday, March 22, 2006 9:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 16, 2013 6:59 PM
Points: 168, Visits: 350

I selected A as the answer:

1 (A).

Schema1    Table1dbo        Table1
My coworker suggested B as the answer but I know he was wrong.
Having read and reviewed scheme, it was all good. Then I saw
Answer C, that almost seem right to me because I haven't encounter
sys.objects before and just last night, I got the information I want
from sysobjects ... I was sooo leaning towards C but select B to claim my coworker down.
What do you know, my answer was right.  He was still questioning the concept so we ran the code
in sql 2005 and boom, A, was the correct answer.


-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool :.
Post #267629
Posted Wednesday, March 22, 2006 9:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 2,351, Visits: 2,696
The synthax error seemed to me to be an archaic form of SQL error, perhaps dating from the Middle Ages. I imagine a difference in the SQL keywords, too:

SELECTETH *
FROMME TableTheFirst
ORDERETH BY ColumnTheFirst



-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #267648
Posted Wednesday, March 22, 2006 11:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 9, 2007 1:24 PM
Points: 507, Visits: 1
The correct answer to this question all depends on what version of SQL Server you are using. SCHEMA_NAME() is only a recognized function in SQL Server 2005, not in SQL Server 2000 and since the version is not stated in the question there are 2 different answers to this misleading question..


Post #267683
Posted Wednesday, March 22, 2006 10:42 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870

The question does state the version. Look at the category

Category : SQL Server 2005 - TSQL




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #267810
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse