Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2130
  • Last Modified:

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".
0
INHOUSERES
Asked:
INHOUSERES
  • 8
  • 4
  • 2
1 Solution
 
ProjectChampionCommented:
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>
0
 
INHOUSERESAuthor Commented:
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.
0
 
ProjectChampionCommented:
>>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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
INHOUSERESAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
Simply put, the job does not have the appropriate permissions to run.
0
 
INHOUSERESAuthor Commented:
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.
0
 
ProjectChampionCommented:
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...
0
 
INHOUSERESAuthor Commented:
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

0
 
ProjectChampionCommented:
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.
0
 
Anthony PerkinsCommented:
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
0
 
INHOUSERESAuthor Commented:
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.
0
 
INHOUSERESAuthor Commented:
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?
0
 
INHOUSERESAuthor Commented:
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.
0
 
INHOUSERESAuthor Commented:
Didn't work properly last night.
I'm going to post another question about it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now