Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Permission issue or query issue Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 12:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, Visits: 414
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
Posted Tuesday, September 11, 2012 12:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, Visits: 414
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
Posted Tuesday, September 11, 2012 2:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 815, Visits: 2,643
Increase the size of your variables to use a longer path

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





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1357196
Posted Tuesday, September 11, 2012 6:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, Visits: 414
Thanks Arto,
I change it into NVARCHAR(1000), then its working..

Thanks
Satish
Post #1357355
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse