Permission issue or query issue

  • Hi friends,

    I have small doubt in below query. This query is use for zip the file and when i use thought command prompt its working 100% but when i use that query in SQL analyze getting error. some one advice me issue with security. Will pls advice me how i get output of below query..

    DECLARE @MyFileName varchar(50), @COMMAND VARCHAR(100)

    SELECT @MyFileName = (SELECT 'C:\DBBACKUP\Weekly_Full_Backup\store\' + convert(varchar(50),GetDate(),112) + '.bak')

    -- BACKUP

    BACKUP DATABASE store TO DISK = @MyFileName

    -- COMPAC ZIP

    SET @COMMAND = 'C:\7-Zip\7z.exe A ' + @MyFileName + '.ZIP ' + @MyFileName

    EXEC XP_CMDSHELL @COMMAND

    Errors are:-

    1. C:\DBBACKUP\Weekly_Full_Back: WARNING: The system cannot find

    the file specified.

    2. Creating archive C:\DBBACKUP\Weekly_Full_Backup\store

    \20120910.bak.ZIP

    3. WARNINGS for files

    4. C:\DBBACKUP\Weekly_Full_Back : The system cannot find the file

    specified.

    5. WARNING: Cannot find 1 file

    NOTE :- Destination path is correct ..

    Thanks in advance...

    Satish

  • Hi Friends,

    I am getting output of above query when i change the path like

    DECLARE @MyFileName varchar(50), @COMMAND VARCHAR(100)

    SELECT @MyFileName = (SELECT 'C:\DBBACKUP\Full\Store\' + convert(varchar(50),GetDate(),112) + '.bak')

    -- BACKUP

    BACKUP DATABASE store TO DISK = @MyFileName

    -- COMPAC ZIP

    SET @COMMAND = 'C:\7-Zip\7z.exe A ' + @MyFileName + '.ZIP ' + @MyFileName

    EXEC XP_CMDSHELL @COMMAND

    in last query path was long now i reduce the path. Please advice me if i want to use longer path what shall i change...

    Thanks

    Satish

  • Increase the size of your variables to use a longer path

    DECLARE @MyFileName varchar(255), @COMMAND VARCHAR(255)

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Thanks Arto,

    I change it into NVARCHAR(1000), then its working..

    Thanks

    Satish

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply