Permission issue or query issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Permission issue or query issue

Permission issue or query issue

Rate Topic

Display Mode

Topic Options

Author

Message

saidapurs

Posted Tuesday, September 11, 2012 12:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:46 AM
Points: 167, Visits: 413

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

Post #1357149

saidapurs

Posted Tuesday, September 11, 2012 12:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:46 AM
Points: 167, Visits: 413

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

Post #1357153

Sean Pearce

Posted Tuesday, September 11, 2012 2:42 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 350, Visits: 1,340

Increase the size of your variables to use a longer path

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

http://thesqlguy.blogspot.com/

Post #1357196

saidapurs

Posted Tuesday, September 11, 2012 6:24 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:46 AM
Points: 167, Visits: 413

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

Thanks
Satish

Post #1357355

« Prev Topic | Next Topic »

Permissions