SQL Server Performance Forum – Threads Archive

Create DB error – Newcomer asks…

Hi, I have just started to learn SQL Server 2005, and am currently using the MCTS SQL Server 2005 Self-Paced Training Kit. I am literally at the very start, and already seem to have hit a snag and can’t figure it out. From browsing here in the past I recall people here seemed quite clued up, and I was hoping someone might be able to assist. I am using SQL Server 2005 Ent. Edtn. (trial) on Win XP Pro (SP2). I am basically trying to run a sample script copied from book to create a database and primary, secondary and log files. Whilst I have the theory down, and thought I understood the SQL (have used other DBMSs in the past, MySQL/ACCESS etc.) I am getting an error when trying to run the following code when connected to the default instance on my PC. The code I am trying to run is: create database Sales ON primary (name = SalesPrimary, filename = ‘E<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />ales_DataSalesPrimary.mdf’, size = 50mb, MAXSIZE = 200, filegrowth = 20), Filegroup SalesFG ( name = SaleData1, filename = ‘F:Sales_DataSalesData1.ndf’, size = 200mb, maxsize = 800, filegrowth = 100), ( name = SalesData2, Filename = ‘F:Sales_DataSalesData2.ndf’, Size = 400mb, Maxsize = 1200, Filegrowth = 300), Filegroup SalesHistoryFG ( name = SalesHistory1, filename = ‘F:Sales_DataSalesHistory1.ndf’, size = 100mb, maxsize = 500, filegrowth = 50) LOG ON (name = Archlog1, Filename = ‘G:Sales_DataSalesLog.ldf’, size = 300mb, maxsize = 800, filegrowth = 100) Straightforward enough I’d think. But I get the following error when I try to run it: Msg 5105, Level 16, State 2, Line 1 A file activation error occurred. The physical file name ‘E<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />ales_DataSalesPrimary.mdf’ may be incorrect. Diagnose and correct additional errors, and retry the operation. Msg 1802, Level 16, State 1, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors. The E:, F: and G: drives are all setup with the Sales_Date folders created, all SQL services are up and running, but for some reason it won’t execute the above statement <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> Does anyone have any ideas. I have scoured a few news groups/MS Forums/BOL and Googled but to no avail. The closest I came was an MS knowledge base article which pertained to SQL Server version 7/2000 but wasn’t of relevance to my specific issue. I think it’s something simple, but right now at my curve of learning not exactly sure what… Any help would be greatly appreciated. Thanks, Will

Hi, For : create database Sales ON primary (name = SalesPrimary, filename = ‘E<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />ales_DataSalesPrimary.mdf’, Read : create database Sales ON primary (name = SalesPrimary, filename = ‘E:Sales_DataSalesPrimary.mdf’, the catch is instead of "E<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />ales" it should be E:Sales Madhu

Madhu,
Thank you for your really quick response! On removing the ‘ ‘ marks, the query started to run, however it again choked with the message: Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ‘E’.
Msg 132, Level 15, State 1, Line 16
The label ‘F’ has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 22
The label ‘F’ has already been declared. Label names must be unique within a query batch or stored procedure. The only change to this and above code was the removal of the ‘ ‘ marks, but completely different errors now. Normally I’d just plow on to next Chapter, as I understand what the SQL code is ‘supposed’ to be doing, but really would like some insight into the error messages if you, or anyone else, had any idea? Thanks,
Will [:0]

Hi, — Thank you for your really quick response! On removing the ‘ ‘ marks, the query started to run, however it again choked with the message: — What have u removed ??????? If you have all the drives (E,F and G) and Sales_Data folder in each drive this will work. the actual problem was in the 5th line of Create Database statement filename = ‘E<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />ales_DataSalesPrimary.mdf’ replace 5th line with the following line filename = ‘E:Sales_DataSalesPrimary.mdf’ Madhu

Ah, spotted the missing "". My clumsy coding — will be extra careful in future! Thanks for pointing that out <twice even! <gulp>. I inserted "", and then ran the query. It is still not working however, error message from before gone, but now receiving the following: Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:Sales_DataSalesPrimary.mdf" failed with the operating system error 3(error not found).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. The code in full, as it is executed: create database Sales
ON
primary
(name = SalesPrimary,
filename = ‘E:Sales_DataSalesPrimary.mdf’,
size = 50mb,
MAXSIZE = 200,
filegrowth = 20),
Filegroup SalesFG
( name = SaleData1,
filename = ‘F:Sales_DataSalesData1.ndf’,
size = 200mb,
maxsize = 800,
filegrowth = 100),
( name = SalesData2,
Filename = ‘F:Sales_DataSalesData2.ndf’,
Size = 400mb,
Maxsize = 1200,
Filegrowth = 300),
Filegroup SalesHistoryFG
( name = SalesHistory1,
filename = ‘F:Sales_DataSalesHistory1.ndf’,
size = 100mb,
maxsize = 500,
filegrowth = 50)
LOG ON
(name = Archlog1,
Filename = ‘G:Sales_DataSalesLog.ldf’,
size = 300mb,
maxsize = 800,
filegrowth = 100) The folders and drives are there as mentioned, but from the error message it seems that SQL is not recognising them? Thanks,
Will

just remove all E,F and G drive with C and also remove the Sales_Data folder. just keep file path as C:<FileName> then try. Madhu

the operating system error 3(error not found
Mean the CREATE database unable to find the specifed path, make sure you have referred files on that path. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

This reminds me that when you input OS path in SQL Server, don’t copy-&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />aste, just browser OS directory to make sure it does exist. —————— Bug explorer/finder/seeker/locator ——————

Thanks to everyone who replied. You were extremely helpful. The problem was that the E:, F: and G: drives I created were volumes mapped to shared folders (avail to everyone). For some reason SQL Server didn’t like those. When I created just equivalent folders on C: it went through no problems. Thanks again.

quote:Originally posted by bluepinecone Thanks to everyone who replied. You were extremely helpful. The problem was that the E:, F: and G: drives I created were volumes mapped to shared folders (avail to everyone). For some reason SQL Server didn’t like those. When I created just equivalent folders on C: it went through no problems. Thanks again.

what BOL Says is ‘ os_file_name ‘
Is the path and file name used by the operating system when you create the file. The file must reside on one of the following devices: the local server on which SQL Server is installed, a Storage Area Network [SAN], or an iSCSI-based network. The specified path must exist before executing the CREATE DATABASE statement. For more information, see "Database Files and Filegroups" in the Remarks section. so the physical file name has to be on the same machine. Madhu

True and you must install the SQL databases on the local folders of the server and if you want to create on a shared folder (other machinE) then you have to use SAN technology. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Enable trace flag 1807, which will allow you to create dbs using UNC path but it is not recommended and I am not sure it works in 2005…
Trace flag 1807 works in 7.0 and 2000.
Mohammed U.

Yes it works in SQL 2005 too and BOL has additional information as:

quote:We recommend that you use a Storage Area Network (SAN), iSCSI-based network, or locally attached disk for the storage of your SQL Server database files, because this configuration optimizes SQL Server performance and reliability. By default, using network database files (stored on a networked server or network-attached storage) is not enabled for SQL Server. However, you can create a database that has network-based database files by using trace flag 1807. For information about this trace flag and important performance and maintenance considerations, see this Microsoft Web site.

KBAhttp://support.microsoft.com/kb/304261 fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

]]>