schema and database in sql server

Question

As this link say the schema and database are can be as ;

schema : database : table :: floor plan : house : room.

But it is suppose to be like this;

database : schema : table :: house : floor plan: room.

Which one is correct? I used like the second option as;

exec xp_cmdshell 'bcp "select * from test2.dbo.test" queryout I:\File\mytest.xlsx -c -t -T -S YAMUNA\SQLEXPRESS'

Here database: test2; schema:dbo; and table:test

The concepts are slightly different between Sql Server and (Oracle/MySQL). You need to specify a more specific RDMBS. — granadaCoder, Sep 20 '13 at 13:25
That link is really not talking about sqlserver syntax. It is even not talking about schema in the way sqlserver uses it. It is just not relevant at all. Replace schema with database model and it should be clear as `database model : database : table` — Hogan, Sep 30 '13 at 17:48

score 2 · Answer 1 · answered Sep 20 '13 at 13:29

Ignore that link, it's not talking about the SQL Server object addressing conventions. Plus, they are talking about the traditional/formal meaning of "Schema" which is "any relational data design", whereas in SQL Server "Schema" is usually short for "Owner-Schema" which is a specific type of security object.

Rather, it's making an analogy about the levels or elements of a relational design. The ":" symbol in this case is the relative relationship of one concept to another, and "::" is the Analogy symbol.

So schema:database:table :: floor plan:house:room just means "the relationship between schema, databases and tables is analogous to the relationship between floor plans, houses and rooms". (There is a more common and succinct way to say this in English, but it tends to be confusing to non-native speakers)

Note that the specific order of these concepts does not matter, so long as their corresponding elements (schema-floor plan, database-house, room-table) has the same relative location. So schema:database:table :: floor plan:house:room is the same as database:schema:table :: house:floor plan:room.

score 1 · Answer 2 · answered Sep 20 '13 at 13:36

The proper answer is schema : database : table :: floor plan : house : room. A house (database) can contain many floors and subsequently, many floor plans (schema). Within each floor, there can be multiple rooms (tables) that are unique to that floor.

DBO is special in the fact that it is the database owner's schema. Other schema can be added to that database.

score 1 · Answer 3 · answered Sep 20 '13 at 14:00

The code below is from a blog article I wrote on use BCP to dump data.

Here are some things to note that are different from what you have.

1 - The path should be supplied to bcp since it might not be found.

2 - I noticed that you put a *.xlsx extension even though it is a comma separated value (csv) format. I suggest using *.csv extensions.

3 - Also, you need to supply a character after '-t,' delimiter switch. Below uses a hex value. The example here uses a comma.

4 - Last but not least, I saw you are missing " " around the file name.

Please fix these issues and try again. If still no dice. Use my code that creates a string. Use the PRINT command.

Try the call from the command line. If it does not work there, it will not work in SQL server.

Good luck.

John Miner

www.craftydba.com

PS: If still have issues, post the output of the command execution.

PPS: The schema should already be fixed for an existing database. Just ask you DBA for the syntax, unless you are the accidental DBA?

-- BCP - Export query, pipe delimited format, trusted security, character format
DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @exe_path4 VARCHAR(200) = 
    ' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & ';
SET @bcp_cmd4 =  @exe_path4 + 
    ' BCP.EXE "SELECT FirstName, LastName FROM AdventureWorks2008R2.Sales.vSalesPerson" queryout ' +
    ' "C:\TEST\PEOPLE.TXT" -T -c -q -t0x7c -r\n';
PRINT @bcp_cmd4;
EXEC master..xp_cmdshell @bcp_cmd4;
GO

schema and database in sql server

3 Answers3