Solved

Why does the attached code fail in Task Scheduler?

Posted on 2011-03-15
13
1,417 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
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
 
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
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.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now