Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2782
  • Last Modified:

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_dat.mdf',
   SIZE = 10MB,
   MAXSIZE = 50MB,
   FILEGROWTH = 5MB )
LOG ON
( NAME = 'SMC_log',
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL$DB\Data\SMC_log.ldf',
   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_dat.mdf' 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.

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?


0
alcratin_42
Asked:
alcratin_42
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
either the path is not correct, or the file exists already and is used by some other (existing) database...
0
 
alcratin_42Author Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
alcratin_42Author Commented:
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!
0
 
alcratin_42Author Commented:
what does a named instance provide when creating databases then?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>what does a named instance provide when creating databases then?
the question like that has no sense.

you have to know that each time you install sql server, you actually install 1 instance.
by default, it has no name (default instance), but you can give each instance a name, hence install several instances of sql server on the same box (each having a different version/service pack).
check out next time you install sql server, there is 1 screen when you can specify the instance name, ie have the checkbox to say "default instance".

in each instance, you can create databases. usually, each instance has it's own folder to create database files in, but nothing would prevent you to put all files from several instances into the same folder...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now