SQLCMD Scheduled task

Posted on 2014-04-29
Last Modified: 2014-05-09
Hi Experts,

I'm a little stumped by something.

I need something run nightly that compiles data from many databases into a single table in another database. I'm happy the query I've written is working and I've saved that into a .sql file.
The query wipes ALL data in the table then populates it again.

I then have a .bat file which runs:
sqlcmd -U USER -P PASS -S SERVER -d Master -i "D:\Scripts\DailyScheduleSQL.sql"

Open in new window

I setup a scheduled task to run this .bat file every night at 9pm.

If I run the query in SSMS, it works fine 100% of the time.
If I run the .bat file, it works fine 100% of the time.
If I run the scheduled task manually, it DOESN'T ALWAYS WORK.
The schedule task running itself seems to ALWAYS fail.

I know it's failing "half way through", because the destination table that the query populates has data in it, but not all of it, meaning it managed to delete it but not fully populate.

I'm stumped.

My knowledge of sqlcmd stretches to "I know how to use it to run something". But not "how to debug an issue".
Question by:INHOUSERES
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
  • 8
  • 4
  • 2

Expert Comment

ID: 40029039
Instead of scheduling a job that calls SQLCMD to run a script, try storing your script as a storedproc and create a job with a T-SQL step that run the stored proc using something like:
EXEC <mydb>..<myproc>

Author Comment

ID: 40029106
Hi ProjectChampion,

I forgot to mention that I'm using SQL express. Sorry. No "jobs" available to me.

I've saved it as a SP, and changed the saved .sql file to only "EXEC Compile.dbo.DailySQL".

Initial test seems to show it's more reliable.
I'll set the scheduled task to run hourly and check results.

Frustrating if a query runs better as an SP than normally.

Accepted Solution

ProjectChampion earned 500 total points
ID: 40029117
>>I forgot to mention that I'm using SQL express.
That explains, you're right in the absence of SQL Server Agent, Windows schedules are the next best choice.

>>...query runs better as an SP than normally.
There are several advantages in replacing ad hoc SQL scripts with stored proc; reliability, security and better performance (due to compiled and stored execution plans) are just a few. It's also better to have as much as possible of your business logic organised and stored in one place in SQL Server rather than in various places in the file system.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Author Comment

ID: 40029145
I find .sql files easier to edit and test, I do use SP's a lot for other things though.
Perhaps it's what you get used to.

I didn't realise there was such a difference, let alone such an advantage.

First test ran at 11:00 (UTC+1) ran fine.
12:00 ran fine.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40029320
Simply put, the job does not have the appropriate permissions to run.

Author Comment

ID: 40029377
3 tests in a row without fail.

The defining test will be if it runs properly at the original scheduled time of 9pm. If it fails then there's obviously a different issue.

I'll post results tomorrow.

Thanks for the help. It seems likely that it's the SP that's making it reliable.

Expert Comment

ID: 40029393
Are there any scheduled jobs starting at 2100 or earlier that may be clashing with the your INSERT?
It may also be prudent to modify the proc to do what it does inside a transaction block, e.g.

      ... do stuff....

This way you can ensure for instance either both delete and insert operations will be completed or neither will go through.
Also in the CATCH Block you can use RAISEERROR to insert the error to log what happened and if needed send a notification alert...

Author Comment

ID: 40029644
There are other tasks that run hourly but not exclusively, therefore if there was a clash then in theory it would have been caught from my tests today.

The query, to go into more detail, deletes all data in a table call "Response", then runs 'sp_MSForEachDB' to go through all databases, checks their name (all the ones I want data from are named in the same way) and retrieve all records from a table also called "Response".
So it's pretty much

DELETE FROM Compile.dbo.Response

EXEC sp_MSForEachDB 

'IF ''?'' Like ''xyz'' BEGIN
  INSERT INTO compile.dbo.response (dbname, col 1, col2...) select ''?'' as dbname, col 1 col2... from ?.dbo.Response

Open in new window


Expert Comment

ID: 40029681
Ah I see. It might be prudent add a "(NoLock)" at the end, e.g.: ''?'' as dbname, col 1 col2... from ?.dbo.Response (NoLock)  just to be on the safe side and avoid unnecessary blockage. I presume you won't mind "read uncommitted" in this scenario.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40029904
You should know that sp_MSForEachDB is undocumented and unsupported and as such there are reports where it simply fails for no apparent reason.  See Aaron's blog on the subject A more reliable and more flexible sp_MSforeachdb

Author Comment

ID: 40034226
Cheers all. Seems to be working consistently now.

So far, I've had no issues at all with sp_MSForEachDB. This is mainly due to the fact that none of the databases that I need to run through are created by a user - they are created by a piece of software.
They all have similar names, (e.g. "Database_123", "Database_9008" etc).
They have identical table names and structures.

It could be that my initial issues was because I was running it via a .sql file rather than an SP. Therefore perhaps sp_MSForEachDB will only run reliably when it's in an SP rather than occasional "quick queries"

I agree that it's not the best SP in the world. But for what I need it for, it's reliable and gets the job done.

Author Comment

ID: 40044660
Hi Again,

This isn't working properly. I'm not sure if it's because of the "Known issues with sp_MSForEachDB", or whether there is something wrong somewhere else.

I thought I'd try Aaron Bertrand's query but I'm having an issue with that now!

Here's my code. If I try to run it I get an error (below) that repeats for each db

EXEC sp_foreachdb
       @command = N'INSERT INTO Compile.dbo.Response (dbname, List, of, columns) SELECT ''?''  dbName, List, of, columns FROM ?.dbo.Response;',
       @name_pattern = N'Project_%';

Open in new window

Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.

Open in new window

Any ideas?

Author Comment

ID: 40044688
Nevermind. Key violations that I was too stupid to recognise!!!

I'll post results after running this with Aaron's query for a few days.

Author Comment

ID: 40053648
Didn't work properly last night.
I'm going to post another question about it.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Only Wanting One Record 8 54
SQL Lag Function DateDiff 2 23
Use SSRS to email customers? 4 21
invoke-sqlcmd help 5 22
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

733 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