Device activation error 1

[Warnie]

Hello,

I had a DB in SQL7.0 and now I have migrated the same to SQL2K. My project demands that I make a copy of the DB. I tried using the SP_ATTACH_DB stored procedure of making copies of the MASTER DB and attaching the same. When I try to attach the DB, I am getting an error as "Device activation error. The physical file name 'C:\Mssql7\data\S213_Data.mdf' may be incorrect" On further interrogation, I came to know from

http://mipsupport.mip.com/npsdocs/InstallConfig/AdvProIssues/DeviceActErr.htm

that I will have to change the folder structure for this. But I am not allowed to do that. There is a suggestion from the said URL that WITH MOVE can be used. I am in the dark now on how to proceed. Any pointers are most welcome....

Warnie

 

[tlbroadbent]

Use backup to create a full backup of teh database. Then you can restore it using any new database name you choose. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Warnie]

tlbroadbent,

I am afraid I could not understand your reply. I have explained my problem again:

1. I have a DB in SQL2000.
2. I want to make a copy of the same. For this, I am using the sp_attach_db stored procedure. But I am unable to create a copy since I am getting the "Device Activation Error". Please note all this is happening from Visual Basic. I can't afford to take a backup and restore. I am not using the Console for any of this. I am doing it through VB. Can you please now help me out? Is it possible to use the MOVE syntax?

Thanks,
Warnie

 

[tlbroadbent]

Move applies to RESTORE. The message you referenced has nothing to do with sp_attach_db. You must be using an incorrect file name when executing sp_attach_db. If you want help debugging the statement, please post the code.

You can issue backup and restore commands from VB as easily as detach and attach. Backup and restore may take longer but the database is online during the operation. If you use backup and restore, you must use the MOVE option on the restore statement.

Another option avilable in VB is to use SQL Distributed management Objects or SQL-DMO. SQL-DMO is a collection of objects encapsulating SQL Server database and replication management. You can perform backup and restore or detach attach with SQL-DMO.

SQL BOL contains complete VB code examples for backup and restore.

Backup:

http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_ex02_9uwh.asp

Restore:

http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_ex02_8j8x.asp

If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Warnie]

tlbroadbent,

I understand the implication with respect to BACKUP and RESTORE. As you have suggested, I am giving the code below.

This is the stored procedure code:

CREATE PROCEDURE stp_NewProject
@DatabaseName varchar(100),
@LocalPath nvarchar(200),
@QueryRequested varchar(50),
@LocalPathLDF varchar (100)=NULL
AS
if @QueryRequested='NewProject'
BEGIN
EXECUTE SP_ATTACH_DB @Databasename, @Filename1=@LocalPath,@Filename2=@LocalPathLDF
RETURN
END

I am invoking this SP from a VB screen. I tried to run the SP from the query analyser and got this message:

Execute stp_NewProject @QueryRequested='NewProject', @DatabaseName= 'S213',
@LocalPath = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\S213_Data.mdf',
@LocalPathLDF = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\S213_log.Ldf'

Error:
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\S213_Data.mdf' may be incorrect.

This error is explained in:

http://mipsupport.mip.com/npsdocs/InstallConfig/AdvProIssues/DeviceActErr.htm

It is to be noted that the mdf and ldf specified above are actually copies of a MASTER DB. The fact is that this SP was working well in SQL7.0 where the folder location of the MDF,LDF files was "C:\Mssql7\data".

I hope I have given sufficient info. I need a workaround code for this so that the DB is created in the default Microsoft SQL Server\MSSQL\data directory.Kindly help....

 

[tlbroadbent]

Warnie,

I repeat, the link that you posted points to an article about an error that occurs when using RESTORE. Your process does not use RESTORE. It uses sp_attach_db. Therefore, the article doesn't have anything to do with the error you have.

There is no problem with the code you posted that I can see. I just created the code and it work just fine on SQL Server 2000 running on Windows 2000 Server. Does the file exist in the location specified? If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLBill]

Warnie,

Where are you getting the name "S213_Data.mdf"? You say that you are "...making copies of the MASTER DB..." but the name of the MASTER database files are MASTER_DATA.mdf. Have you renamed the mdf file somehow? I'm really not sure that you can rename the MASTER database.

-SQLBill

 

[tlbroadbent]

SQLBill, I have assumed that Warnie is referring to a MASTER database for the system rather than the SQL Server master DB. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Warnie]

tlbroadbent,SQLBill

Sorry for coming back late. Both of you are right that there is no problem in the code I have posted. I did a thorough analysis of my code and found that it is not a problem with attaching the DB. The situation is that I have a TEMPLATE DB which I am trying to make a copy of. I inferred that there is a problem in making a copy which is finally giving me a "DEVICE ACTIVATION ERROR".

Now there are 2 cases for this: One a success and another a failure.

1. I am able to make a copy of the TEMPLATE DB when the datafiles are present on say "C:\MSSQL\DATA".
2. I am unable to make a copy of the TEMPLATE DB when the datafiles are present in the default installation directory that is "C:\Program Files\Microsoft SQL Server\MSSQL\Data".

I am pasting the code I am using for copying the datafiles.

************************************************************
if @QueryRequested='CopyFile'

BEGIN

DECLARE ProcessKill CURSOR FOR

select spid from master.dbo.sysprocesses where dbid =(select dbid from master.dbo.sysdatabases where name = @DatabaseName)

OPEN ProcessKill

FETCH NEXT FROM ProcessKill INTO @ProcessID

select @str = 'kill ' + convert(varchar,@processid)
IF @@FETCH_STATUS = 0
begin
execute (@str)
end

WHILE @@FETCH_STATUS = 0

BEGIN
FETCH NEXT FROM ProcessKill
INTO @ProcessID
select @str = 'kill ' + convert(varchar,@processid)
IF @@FETCH_STATUS = 0
begin
execute (@str)
END
END

CLOSE ProcessKill

DEALLOCATE ProcessKill

SET @sqlselect = 'select top 1 filename from ' + @DatabaseName + '.dbo.sysfiles'
print @sqlselect

BEGIN

SET @strcursor ='Declare select_cursor cursor for ' + @sqlselect
EXEC sp_executesql @strcursor

OPEN select_cursor

FETCH FROM select_cursor INTO @path
CLOSE select_cursor
DEALLOCATE select_cursor

END


SET @sqlselect = 'select filename from ' + @DatabaseName + '.dbo.sysfiles where fileid=2'
print @sqlselect

BEGIN

SET @strcursor ='Declare selectLDF_cursor cursor for ' + @sqlselect
print @strcursor
EXEC sp_executesql @strcursor

OPEN selectLDF_cursor

FETCH FROM selectLDF_cursor INTO @pathLDF
CLOSE selectLDF_cursor
DEALLOCATE selectLDF_cursor

END

EXECUTE SP_DETACH_DB @Databasename

set @sqlselect = ' COPY ' + rtrim(@path) + ' ' + rtrim(@localpath)
print @sqlselect
execute master..xp_cmdshell @sqlselect

set @sqlselect = ' COPY ' + rtrim(@pathLDF) + ' ' + rtrim(@LocalPathLDF)
print @sqlselect
execute master..xp_cmdshell @sqlselect

EXECUTE SP_ATTACH_DB @Databasename, @Filename1=@Path

RETURN
END
************************************************************

Now when I saw the microsoft documentation about xp_cmdshell, I got the below information:

"When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.".

This made me give all permissions to my sql login.
Also Microsoft lists this as a bug which has been taken care by the latest Service Pack for SQL2000. I installed the Service Pack as well. Still the TEMPLATE DB is not being copied when the SQL Server is installed in its default directory?

Questions:

Is there a problem with xp_cmdshell when the path is "C:\Program Files\Microsoft SQL Server\MSSQL\Data"?Should I be giving the DOS path like "C:\Progr~1\Micro~1\MSSQL\Data"?
Please advise.

Thanks,
Warnie

 

[tlbroadbent]

You can overcome the problem by using double quotes around the file names.

set @sqlselect = ' COPY "' + rtrim(@path) + '" "' + rtrim(@localpath) + '"'
If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm