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:Sales_Data\SalesPrimary.mdf,
size = 50mb,
MAXSIZE = 200,
filegrowth = 20),
Filegroup SalesFG
( name = SalesData1,
filename = F:\Sales_Data\SalesData1.ndf,
size = 200mb,
maxsize = 800,
filegrowth = 100),
( name = SalesData2,
Filename = F:\Sales_Data\SalesData2.ndf,
Size = 400mb,
Maxsize = 1200,
Filegrowth = 300),
Filegroup SalesHistoryFG
( name = SalesHistory1,
filename = F:\Sales_Data\SalesHistory1.ndf,
size = 100mb,
maxsize = 500,
filegrowth = 50)
LOG ON
(name = Archlog1,
Filename = G:\Sales_Data\SalesLog.ldf,
size = 300mb,
maxsize = 800,
filegrowth = 100)
I find I get the following error 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.
Anyone have any ideas? Your help is greatly appreciated!
Thanks,
Will
Someone found the problem with the above SQL query, and it was line 5 missing the \ after E: E:\Sales_Data\SalesPrimary.mdf
I have corrected that, however, now getting the message:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\Sales_Data\SalesPrimary.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.
Anyone have any ideas?
|||
Hi,
that will work if and only if you have drives E , F and G
and you have created the folder (subdirectory) in each drive needed by the create
database command
for more information visit books online search for "create database"
that's a nice book anyway.
create database Sales
ON
primary
(name = SalesPrimary,
filename = E:\Sales_Data\SalesPrimary.mdf, < this is the physical location of the data
thats drive E: on forlder sales_data - SalesPrimary.mdf, is the physicla filename
you can change that to
C:\mydata1\salesprimary.mdf if you dont have drive E: make sure that "mydata1" folder exist in the drive. use explorer to create it or dos command c:\md mydata1
size = 50mb,
MAXSIZE = 200,
filegrowth = 20),
to simulate the exercise use
c:\mydata1; c:\mydata2; c:\mydata3... and c:\mylogs just make sure the folder exisit in drive c:
thanks,
joey
|||
Hi Joey,
Thanks for your response. I did have those drives available (e,f,g) and the subfolders required. However, I created them as shared folders mapped to respective volumes. I think the problem there is the way the SQL Server permissions. I tried as you suggested, with just the C: drive and it worked no problem. I don't know why SQL can't find the volumes though... will have to investigate that further.
Thanks,
Will
|||hi,
It should be physical drives or a SAN. you can't create those things on
shared folders or mapped network drives
please see these links:
http://msdn2.microsoft.com/en-us/library/ms176061.aspx
http://msdn2.microsoft.com/en-us/library/ms190257.aspx
regards,
joey
|||Hi,
you *could* create them on network drives, but this is not recommended, see this thread for more details:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1029560&SiteID=1&mode=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||now i know. trace flag 1807. still not it is not recommended
thanks jens
