Solved

Why does the attached code fail in Task Scheduler?

Posted on 2011-03-15
13
1,404 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
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…

708 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

14 Experts available now in Live!

Get 1:1 Help Now