• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1526
  • Last Modified:

Why does the attached code fail in Task Scheduler?

I want to set up SQL backups in Task Scheduler. I added the attached code as an action.
Tested SQLContent.sql and it runs the SQL backup. Why does this action fail when I attempt to run in task scheduler? Thanks

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\InstanceName -i "C:\SQLContent.sql"

Open in new window

0
PDSWSS
Asked:
PDSWSS
1 Solution
 
waleedaCommented:
what is the error you are getting?
0
 
PDSWSSAuthor Commented:
Failed in Task Scheduler. Where would I find the error log?
0
 
BrandonGalderisiCommented:
I assume this is SQL Express?  Otherwise, no reason to use windows backup jobs.

Are you "running with highest privileges" when you set up the job?  Are you running as an account that has permissions?  I see you are missing the -E (windows auth) or -U & -P (user & password) switches.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
shjacks55Commented:
does it run on the command line?
0
 
PDSWSSAuthor Commented:
Will run  on cmd line and check highest privileges and see what happens. Thanks
0
 
PDSWSSAuthor Commented:
Runs on cmd line. Chose highest privileges and still will not work in task scheduler.
0
 
PDSWSSAuthor Commented:
Says failed in Task Scheduler window but no error is logged in the events window.
0
 
PDSWSSAuthor Commented:
waleeda:

Here is the error message:The filename, directory name or volume label syntax is incorrect. (0x8007007B)

Strange since all these must be correct since I can run from a batch file.  Thanks
0
 
PDSWSSAuthor Commented:
BrandonGalderisi:



"Are you running as an account that has permissions?  I see you are missing the -E (windows auth) or -U & -P (user & password) switches."

Do special permissions for Task Scheduler and do I need extra switches in the Task scheduler Action code?  Thanks
0
 
PDSWSSAuthor Commented:
I solved the issue by putting the action that wouldn't work in the task scheduler into a batch file and running the task scheduler with an action that ran the batch file.
0
 
shjacks55Commented:
Cool as long as your backups are actually getting made. Rem that usually you need to include an errorlevel  line to intercept .cmd file errors.

See http://msdn.microsoft.com/en-us/library/ms162773.aspx command line and batch differ.
"When sqlcmd is run from the command line, sqlcmd uses the OLE DB provider. Because different default options may apply, you might see different behavior when you execute the same query in SQL Server Management Studio in SQLCMD Mode and in the sqlcmd utility."

Of course the variables and stdout are handled differently within a batch file, example the period after -S is passed "current location" as a value by the command shell but not by windows. Can use %1 %2 in a batch file but not when called by windows.

Not?  "Warning 0x8007007b: Data Flow Task: The filename, directory name, or volume label syntax is incorrect. (SQL Server Import and Export Wizard)"

What I found for 0x8007007b was (other than incorrect syntax for destination file) was WMI restore or backup seems to reserve additional disk space for automated actions and people sending data from sqlcmd to IIS page (extra" \) and data sent to network locations by dial-up (time outs) and web storage.

It would be interesting to see the sqlcontent.sql file.

0
 
PDSWSSAuthor Commented:
shjacks55:  Thanks for your input. I was not aware of that.

The backup was successfully created in the correct location without any errors.
I generated the  sqlcontent.sql file   by   running the same back up 1X in Studio Manager and saving the script that was created to run the backup.

Should I still be concerned?
0
 
PDSWSSAuthor Commented:
None of the experts provided a solution that solved my issue and I ended up solving it myself as described in my last post.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now