Solved

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

Posted on 2006-11-27
8
2,643 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

687 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