Re: SQL Job question

Andrew,

Thanks for your response. I tried this a couple of ways...logged into QA as
SA and with Windows authentication. No luck either way. QA just seems to
hang ("Executing qfuery batch..."). I will review the articles you supplied
below and post back if this still doesn't work.

"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message
news:%23A3$D4UbHHA.1300@xxxxxxxxxxxxxxxxxxxxxxx
Chris,

Can you execute this from QA and have it work?

SET @str = '"C:\Program Files\7-Zip\7zFM.exe"
E:\Data\MSSQL\BACKUP\zip\PPSSQL.zip E:\Data\MSSQL\BACKUP\PPSSQLBU.bak'
EXEC xp_cmdshell @str


If so then my guess would be the account that SQL Agent or the proxy
account
is using does not have proper permissions to execute that command or
rights
to the folders. Is the job owned and executed by someone in the sa role or
other?

http://www.support.microsoft.com/?id=269074 How to Run a DTS Package as
a
Scheduled Job
http://www.support.microsoft.com/?id=890775 How to enable no sa's to
run
xp_cmdshell

--
Andrew J. Kelly SQL MVP

"Chris Hall" <someone@xxxxxxxxxxxxx> wrote in message
news:uP7j8aMbHHA.704@xxxxxxxxxxxxxxxxxxxxxxx
Greetings,

I'm a complete nub with SQL and have a question. I haven't been able to
find
any resoures on this....We have an application that uses SQL 2000 on the
backend. I'm trying to setup an automated way to backup a database, then
zip
that database up. The final step would be to set a scheduled task
(windows
task mgr) to ftp the zipped file to an ftp server. The server is running
Windows Server 2003, the SQL version is 2000. Here's the transact-sql
code
I'm using to:


DECLARE @bkFile varchar (75)
DECLARE @str nvarchar (300)
SET @bkFile='E:\Data\MSSQL\BACKUP\PPSSQLBU.bak'
BACKUP DATABASE [WinFMSQL] TO DISK = @bkFile WITH INIT, NAME = N'WinFM
Full
Backup', STATS = 10, DESCRIPTION = N'WinFM DB Full Backup'
SET @str = '"C:\Program Files\7-Zip\7zFM.exe"
E:\Data\MSSQL\BACKUP\zip\PPSSQL.zip E:\Data\MSSQL\BACKUP\PPSSQLBU.bak'
EXEC xp_cmdshell @str


The backup kicks off and complete's fine, however, the file doesn't get
zipped. The log file and event viewer offers little information.

Thanks for your suggestions,
Chris






.

Relevant Pages

  • Re: SQL Job question
    ... EXEC master..xp_cmdshell @str ... Andrew J. Kelly SQL MVP ... Backup', STATS = 10, DESCRIPTION = N'WinFM DB Full Backup' ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Job question
    ... Can you execute this from QA and have it work? ... Andrew J. Kelly SQL MVP ... I'm trying to setup an automated way to backup a database, ... DECLARE @str nvarchar ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Job question
    ... it appears they are geared towards running the script ... If so then my guess would be the account that SQL Agent or the proxy ... DECLARE @str nvarchar ... Backup', STATS = 10, DESCRIPTION = N'WinFM DB Full Backup' ...
    (microsoft.public.sqlserver.setup)
  • Password does not work on SQL Server 2000
    ... The sa password no longer works on my SQL 2000 server. ... Windows authentication does not work either. ... to backup or update my data. ...
    (microsoft.public.sqlserver.security)
  • Re: Backup erros
    ... You may have a SQL ... Error 800423f4 appears in the backup log file when you back up a volume by ... > Reason: The process cannot access the file because it is being used by> another process. ...
    (microsoft.public.windows.server.sbs)
Loading