Solved

SQL Server 2000 - Query Error - Device activation error. The physical file name may be incorrect.

Posted on 2006-11-27
8
2,583 Views
Last Modified: 2008-01-09
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
Comment
Question by:alcratin_42
  • 3
  • 3
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18017872
either the path is not correct, or the file exists already and is used by some other (existing) database...
0
 

Author Comment

by:alcratin_42
ID: 18018049
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18018067
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:alcratin_42
ID: 18018192
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
 

Author Comment

by:alcratin_42
ID: 18018203
what does a named instance provide when creating databases then?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 18018247
>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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question