Create Database

Question

Post reply

Create Database

Viewing 15 posts - 16 through 30 (of 55 total)

You must be logged in to reply to this topic. Login to reply

Ernie Schlangen SSCrazy Points: 2382 More actions · Answer 1

Ernie Schlangen

SSCrazy

Points: 2382

May 11, 2009 at 7:23 am

#992094

Ditto and...

Ditto to the edit, as well.

pkupsovsky SSC Journeyman Points: 78 More actions · Answer 2

Running the risk of sounding like a smartar.. 🙂 Your example is as bad as the 'tested' code I get from my users. You know SQL server can create files but not directories. Nice point though regarding mdf, ldf, ndf

perhaps include:

exec xp_cmdshell 'MD c:\mssql'

go

cheers

Chad Crawford SSChampion Points: 11610 More actions · Answer 3

I agree that it is difficult to test when part of the question depends on the environment and configuration. I did think it was pretty easy to tell what the question was trying to point out though. I never thought of putting an .mp3 extension on my database - gave me quite a chuckle!

peter.marley SSC Enthusiast Points: 120 More actions · Answer 4

peter.marley

SSC Enthusiast

Points: 120

May 11, 2009 at 8:16 am

#992151

Syntax incorrect on 2005

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 5

DOH! :w00t:

A wee bit vague... I guess ultimately it caught me out since i made assumptions as to the permissions for SQL Server access to the stated directory...

Raamamohan SSC Eights! Points: 839 More actions · Answer 6

Database wasn't create:

I have executed the query but the results are:

Server: Msg 5105, Level 16, State 2, Line 1

Device activation error. The physical file name 'C:\mssql\MYTEST.doc' may be incorrect.

Server: Msg 1802, Level 16, State 1, Line 1

CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 7

The question is phrased very poorly as it is anybody's guess what does it try to ask:[p]

that no it is not possible to create a database if the folder for db and/or log files does not exist or permissions are not set
that yes you can create db and log files with whatever extension you want.

[/p][p]At least this should not be a true/false question.[/p][p]Oleg[/p]

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 8

OK, I have edited the question to say "Can" instead. However I don't agree with a lot of the comments here.

First, if the paths don't exist on your system, or you don't have permissions, that isn't something we can, or are going to qualify. If you see a path, you need to assume that path exists on the test system. And that permissions are appropriate unless something about permissions is mentioned.

If I were to add those qualifications, we'd be writing a set of paragraphs for each question, and that isn't the intent. If permissions are part of the question, they should be mentioned. If they aren't, then permissions aren't a part of things.

Points have been awarded back.

Toreador SSChampion Points: 11368 More actions · Answer 9

Steve Jones - Editor (5/11/2009)
If you see a path, you need to assume that path exists on the test system.

Why? The question could be about whether or not SQLServer requires a path and/or file to already exist, or if it will auto-create it for you.

I got it wrong anyway, but for the wrong reason - I assumed that the path would be automatically created but that it would then object to the file types.

purplebirky SSC-Addicted Points: 461 More actions · Answer 10

I tried it also and it didn't work. Here's my msg:

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "C:\mssql\MYTEST.doc" failed with the operating system error 2(The system cannot find the file specified.).

Msg 1802, Level 16, State 1, Line 1

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

No need to award back to me, I answered before i tested so i would have gotten it wrong anyway.

[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]

Bob Griffin SSCertifiable Points: 5982 More actions · Answer 11

from BOL "Create Database":

The specified path must exist before executing the CREATE DATABASE statement. For more information, see "Database Files and Filegroups" in the Remarks section.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 12

I'm not sure how we would word a question about creating paths, but in this case, the question was simple. Will this work.

It will (with some settings) or it won't in any case. The answer is that simple. So just because it doesn't work on your system, doesn't mean it won't work. That's the litmus test. Will it ever work.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 13

I thought it was a good question.

Those of you saying the syntax is not correct, where are you getting a syntax error? As long as those paths exist, it should run fine.

The Redneck DBA

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 14

It is a good question, but it is just strange that it is a question about extensions. Extensions are rather obviously not ever a concern in Windows. If one were to create a Word document with XYZ extension by, for example, renaming existing document to [file_name].XYZ, it can be done of course, and the Word will still happily open the document afterwards whether via open with or via File – Open feature once MS Word is already open. This is a normal expected behavior of any program designed to work with particular kind of files (MS SQL Server included). Any such program relies on reading the information from the headers rather than on file extension. Even if you rename MP3 file to AVI, the Media Player still knows it is MP3 it is playing. Same is true for SQL Server. If the record in the system table says that the database file is MyTest.mp3 so let it be, why should this be a question?[p]The extensions are not even an issue here, and therefore, one reading the question definition will inevitably revert to thinking about something else the question is possibly asking. I for example, thought about where the gotcha would happen if someone were to actually run the script? Extensions are not an issue, but the gotcha could be that the engine will not create the path if it does not already exists, so I assumed that the question was about whether people know the behavior of the engine regarding path creation for storage, which is a very good question, and the answer to it is Yes, but only if path already exists or No otherwise.[/p][p]Oleg[/p]

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 15

Jason Shadonix (5/11/2009)
Those of you saying the syntax is not correct, where are you getting a syntax error? As long as those paths exist, it should run fine.

Hi Jason,

It turns out it was a formatting issue in the email, combined with a lack of careful review by those (like me) copy->pasting directly from the email.

the code came out as follows:

Create Database Mytestdbon

( name ='Mytest_1', Filename= 'C:\MyTEST.mp3', size = 5000 KB

) log on(

name ='MyTest_2', Filename= 'C:\mssql\MYTEST.doc', size= 512 kb)

I did, amusingly, think "Mytestdbon" was a strange name to give a test database! 🙂

It looks like Steve has corrected the question's formatting since.

Tao

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.