Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-11-27
8
Medium Priority
?
2,726 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 143

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 143

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
Independent Software Vendors: 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!

 

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

609 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