Re: SQL Job question

Okay, I tried to run this again, logging into the db as domain\administrator
and got the following error when trying to run the script:

Server: Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'xp_cmdshell'.

Looking at the docs, it appears they are geared towards running the script
through a sql job in Enterprise Mgr.

Any other suggestions?

"Chris Hall" <someone@xxxxxxxxxxxxx> wrote in message
news:Oe6ca3VbHHA.1240@xxxxxxxxxxxxxxxxxxxxxxx
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: Enterprise Manager Newbie Question
    ... a SQL backup is not a simple copy of the database files. ... Is it possible to write a script that one could run from a workstation and ...
    (microsoft.public.sqlserver.tools)
  • Re: Enterprise Manager Newbie Question
    ... and have a script run from the workstation so that we ... SSMS does work with SQL 2000 and SQL ... why don't you write a short backup script using the SQLCMD utility ... Senior Database Administrator ...
    (microsoft.public.sqlserver.tools)
  • Re: Enterprise Manager Newbie Question
    ... Tibor Karaszi, SQL Server MVP ... and have a script run from the workstation so that we ... why don't you write a short backup script using the SQLCMD utility ... Senior Database Administrator ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Job question
    ... SA and with Windows authentication. ... 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
    ... EXEC master..xp_cmdshell @str ... Andrew J. Kelly SQL MVP ... Backup', STATS = 10, DESCRIPTION = N'WinFM DB Full Backup' ...
    (microsoft.public.sqlserver.setup)