Solved

Why does the attached code fail in Task Scheduler?

Posted on 2011-03-15
13
1,474 Views
Last Modified: 2012-08-13
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
Comment
Question by:PDSWSS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 7

Expert Comment

by:waleeda
ID: 35144745
what is the error you are getting?
0
 

Author Comment

by:PDSWSS
ID: 35144752
Failed in Task Scheduler. Where would I find the error log?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35144855
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 3

Expert Comment

by:shjacks55
ID: 35144877
does it run on the command line?
0
 

Author Comment

by:PDSWSS
ID: 35148569
Will run  on cmd line and check highest privileges and see what happens. Thanks
0
 

Author Comment

by:PDSWSS
ID: 35149980
Runs on cmd line. Chose highest privileges and still will not work in task scheduler.
0
 

Author Comment

by:PDSWSS
ID: 35150022
Says failed in Task Scheduler window but no error is logged in the events window.
0
 

Author Comment

by:PDSWSS
ID: 35152359
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
 

Author Comment

by:PDSWSS
ID: 35152377
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
 

Accepted Solution

by:
PDSWSS earned 0 total points
ID: 35154005
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
 
LVL 3

Expert Comment

by:shjacks55
ID: 35166857
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
 

Author Comment

by:PDSWSS
ID: 35168119
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
 

Author Closing Comment

by:PDSWSS
ID: 35178888
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question