0

I have two instances on a single server. Instances are named TEST and LIVE for simplicity. Both are running SQL Server 2019. Only the LIVE instance has SQL Agent jobs running (Basic backup jobs via maintenance plans). SSIS is not installed on this environment.

I have just upgraded the TEST instance to SQL Server 2022. All was quite straight forward except for the ODBC and OLEDB components having to be uninstalled for it to be able to upgrade correctly. All looks okay now on the TEST instance.

However there is an issue with the LIVE instance which is still running SQL Server 2019. The backup jobs are failing with:

Could not load package "Maintenance Plans\Backup" because of error 0x80070002.
Description: The system cannot find the file specified.

I have determined that the issue is with the DTExec component by running the backup code, from the SQLAgent job, via the command prompt. If I attempt to run it with the 150\DTS\Binn version (i.e. The SQL Server 2019 version) it fails. However when I run it with the 160\DTS\Binn (i.e. the newly installed SQL Server 2022 version) it works.

I would assumed that if anything it would be the other way around.

I cannot see any changes to files within the SQL Server 2019 folders. I am guessing some shared component has altered and is causing this but I cannot track it down.

I am quite sure that when I upgrade the LIVE instance then this problem will be resolved but I would like to know why this upgrade has caused an issue. If there were multiple instance on a server then this could be much more of a problem.

I have tried all the obvious sources of information (Including ChatGPT) for help but nothing is telling me why this is happening.

Does anyone have an idea?

Additional information: Reinstalling the old drivers makes no difference. I get the same results.

Improve this question
2
  • "All was quite straight forward except for the ODBC and OLEDB components having to be uninstalled for it to be able to upgrade correctly." - DTS for 2019 is probably referencing these OLE DB/ODBC components, which fails since they've been uninstalled, but DTS for 2022 is using the new components, hence why it works when you manually execute using this DTS. Try reinstalling the components you removed and see if the maint plans start working
    – HandyD
    Commented Oct 24 at 1:25
  • I did try your suggestion and reloaded the drivers from the SQL Server 2019 installation disk. Unfortunately it made no difference. I get exactly the same results (Even after a reboot). It was worth a try. Strangely enough I was at another office yesterday attempting to upgrade a SQL Server 2014 instance to 2019 and got the same driver issue. All the other instances on that box were 2017 so I cancelled the 2019 upgrade and upgraded to 2017 instead with no issues.
    – StuKay
    Commented Oct 25 at 14:36

1 Answer 1

Reset to default
0

I used a DependancyWalker to analyse the DTExec (150\2019 version) executable and it highlighted there were a lot of missing components. Too complicated and time consuming for me to spend time on.

So I ran the SQL Server 2019 installation and used the Maintenance page Repair option against the LIVE instance. It reported everything succeeded.

This has fixed my issue. SQLAgent jobs are now running okay.

Also both versions of DTExec (160/2022 for TEST and 150/2019 for LIVE) look to be okay. Ran successful command line tests with both.

Both the 2022 and 2019 instances look to be okay as far as I can tell.

Hopefully this will useful to those encountering a similar situation.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.