Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Why does the attached code fail in Task Scheduler?

Posted on 2011-03-15
13
Medium Priority
?
1,493 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Microsoft will be releasing the Windows 10 Creators Update in just a matter of weeks. Are you prepared? Follow these steps to ensure everything goes smoothly and you don't lose valuable data on your PC.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Viewers will learn how the fundamental information of how to create a table.

704 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