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"
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.
My knowledge of sqlcmd stretches to "I know how to use it to run something". But not "how to debug an issue".