I have a single step job which truncates a SQL Server table and then re-populates it with updated data using OPENQUERY from a cloud based system set up as a linked server.
When I run the code in Studio Manager it populates currently a little over 6,000 records, but when the job is run it only populates 5,867.
It is always the same 5,867 records. As the source system gets updated there are more and more records not being pulled in causing me to have to manually run the code instead of just being able to schedule the job.
Some LIMIT when the job runs as a scheduled task somehow active somewhere?
e.g.
SET ROWCOUNT nnnn
5,867 is not a very "round" number though!!!
Perhaps "spy" on the job with SQL Profiler and see if anything comes through - e.g. use "Find" and look for "5867"
Thank you, it comes up with the same results scheduled or if I "start job at step" if that matters. There are no limiting factors (that I can find) for a max row count. Key word - that I can find; not exactly sure where to look.
Is the SCHEDULE JOB connected to a different database than the one you are using in SSMS? Perhaps a Test Database?
I would still SPY on it using SQL Profiler and see exactly that SQL commands it sends to the server - that, and the other properties (such as Database Name) may be revealing.
No - the job is set to run against the database the table is in that it truncates and populates.with data from the linked server.
As soon as the system is free I am going to try the trace and see what (if anything) shows up differently between the job running the code or my manually running it.