I need to solve a problem with sql server 7.
We have a procedure that perform a database export in a local db called
dbage and another procedure that move the files to a network hard disk.
The "move" doesn't work.

this is the sp:

===============================================
CREATE PROCEDURE [dbo].[copia_offline_dbage] AS

exec sp_dboption dbage , 'single user', true
exec sp_dboption dbage, 'offline', true



DECLARE
@STR_DOS1 AS VARCHAR(8000) ,
@STR_DOS2 AS VARCHAR(8000) ,
@FILEDATI AS VARCHAR(8000) ,
@FILELOG AS VARCHAR(8000) ,
@STR_DOS_FILEDATI_ARCH1 AS VARCHAR(8000),
@STR_DOS_FILELOG_ARCH1 AS VARCHAR(8000),
@RC AS INTEGER

SET @FILEDATI = 'd:\mssql7\data\dbage_data.mdf'
SET @FILELOG = 'd:\mssql7\data\dbage_log.ldf'


SET @STR_DOS_FILEDATI_ARCH1 = 'del X:\dbage_data.mdf'
SET @STR_DOS_FILELOG_ARCH1 = 'del X:\dbage_log.ldf'

PRINT @STR_DOS_FILEDATI_ARCH1
PRINT @STR_DOS_FILELOG_ARCH1

EXECUTE @RC = master.dbo.xp_cmdshell @STR_DOS_FILEDATI_ARCH1
EXECUTE @RC = master.dbo.xp_cmdshell @STR_DOS_FILELOG_ARCH1

SET @STR_DOS1 = 'copy ' + @FILEDATI + ' X:\'
PRINT @STR_DOS1
EXECUTE @RC = master.dbo.xp_cmdshell @STR_DOS1


SET @STR_DOS2 = 'copy ' + @FILELOG + ' X:\'


PRINT @STR_DOS2
EXECUTE @RC = master.dbo.xp_cmdshell @STR_DOS2

exec sp_dboption dbage, 'offline', false
exec sp_dboption dbage , 'single user', false

================================================== ==

Everything is all right but the copy to X: whic is a network
disk.

I think something deals with user permission.
Can you help me?

Thanks,

Fransis

Fransis,

It looks as X is a mapped drive to a network share. You are trying to use
this resource under assumption that the drive is already mapped, but it may
not be the case. xp_cmdshell runs under MS SQLServer service account unless
you make an exception by establishing a proxy account. Unless you logged on
to the server console under one of those accounts and created the mapping
there, xp_cmdshell would not recognize it. If that is the case you have to
create that mapping on the fly by NET USE command:

SET @STR_DOS1 = 'NET USE .......
copy ' + @FILEDATI + ' X:\'

or use UNC notation:

SET @STR_DOS1 = 'copy ' + @FILEDATI + ' \\<server>\<share>\'

Ilya

"Fransis il mulo parlante" <francogozzi (AT) tiscali (DOT) it> wrote

Ilya Margolin wrote:
Thanks a lot!!!