Solved

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

Posted on 2006-11-27
8
2,534 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now