alcratin_42
asked on
SQL Server 2000 - Query Error - Device activation error. The physical file name may be incorrect.
I'm trying to figure out how to set up a database using TSQL commands.
My syntax is as follows:
CREATE DATABASE SMC
ON
( NAME = 'SMC_dat',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL$DB\Data\SMC_d
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = 'SMC_log',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL$DB\Data\SMC_l
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
Instead of a successful creation, I get these errors instead.
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$DB\Data\SMC_d
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
I'm using SQL2000 demo with SP4 installed. Query Analyzer is used to enter query. Server is on a local machine using a local login account.
The device activation error seems to be a common error associated with retrieving data across different MS apps. So I'm lost when trying to reference this elsewhere. Any ideas?
My syntax is as follows:
CREATE DATABASE SMC
ON
( NAME = 'SMC_dat',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL$DB\Data\SMC_d
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = 'SMC_log',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL$DB\Data\SMC_l
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
Instead of a successful creation, I get these errors instead.
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$DB\Data\SMC_d
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
I'm using SQL2000 demo with SP4 installed. Query Analyzer is used to enter query. Server is on a local machine using a local login account.
The device activation error seems to be a common error associated with retrieving data across different MS apps. So I'm lost when trying to reference this elsewhere. Any ideas?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
either the path is not correct, or the file exists already and is used by some other (existing) database...
ASKER
What in the path would be incorrect?
The only thing that might be out of order would be the MSSQL$DB, what is the $ doing for me? Is that a wildcard here?
The only thing that might be out of order would be the MSSQL$DB, what is the $ doing for me? Is that a wildcard here?
the $ indicates that you are probably on a named instance called DB. this is nothing special as such.
the path (folder) has to exist when you run the statement, sql will NOT create the folder for you.
the path (folder) has to exist when you run the statement, sql will NOT create the folder for you.
ASKER
I deleted the $DB and then it worked in both statements.
That last bit about that SQL will not create the folder for me was key.
Thanks!
That last bit about that SQL will not create the folder for me was key.
Thanks!
ASKER
what does a named instance provide when creating databases then?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.