asked on

SQLCMD Scheduled task

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".

ProjectChampion

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>

INHOUSERES

ASKER

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.

ASKER CERTIFIED SOLUTION

ProjectChampion

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

INHOUSERES

ASKER

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.

Anthony Perkins

Simply put, the job does not have the appropriate permissions to run.

INHOUSERES

ASKER

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.

ProjectChampion

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.

BEGIN TRY
BEGIN TRAN
... do stuff....
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH

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...

INHOUSERES

ASKER

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
END'

Open in new window

ProjectChampion

Ah I see. It might be prudent add a "(NoLock)" at the end, e.g.:
...select ''?'' 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.

Anthony Perkins

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

INHOUSERES

ASKER

Cheers all. Seems to be working consistently now.

Anthony:
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.

INHOUSERES

ASKER

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?

INHOUSERES

ASKER

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.

INHOUSERES

ASKER

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