Solved

SQLCMD Scheduled task

Posted on 2014-04-29
14
1,279 Views
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".
0
Comment
Question by:INHOUSERES
  • 8
  • 4
  • 2
14 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
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>
0
 
LVL 5

Author Comment

by:INHOUSERES
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.
0
 
LVL 8

Accepted Solution

by:
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.
0
 
LVL 5

Author Comment

by:INHOUSERES
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.
0
 
LVL 75

Expert Comment

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

Author Comment

by:INHOUSERES
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.
0
 
LVL 8

Expert Comment

by:ProjectChampion
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.

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 5

Author Comment

by:INHOUSERES
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
END'

Open in new window

0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40029681
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
 
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
0
 
LVL 5

Author Comment

by:INHOUSERES
ID: 40034226
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
 
LVL 5

Author Comment

by:INHOUSERES
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?
0
 
LVL 5

Author Comment

by:INHOUSERES
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.
0
 
LVL 5

Author Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

11 Experts available now in Live!

Get 1:1 Help Now