SQL Server Agent running DTExec 10.5 instead of 11
-
I was hoping someone else might have run across this. We did an in place upgrade of SQL Server 2008 R2 to SQL 2012 patch 4 over the weekend. We're having problems running jobs because it seems it runs the wrong DTexec. The agent itself is pointed to ...\MSSQ11.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE" -i MSSQLSERVER
Executed as user: (Our active directory account for SQL). Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 7:37:25 AM Error: 2018-03-27 07:37:25.44 Code: 0xC001700A Source: Description: The version number in the package is not valid. The version number cannot be greater than current version number. End Error Error: 2018-03-27 07:37:25.44 Code: 0xC0016020 Source: Description: Package migration from version 6 to version 3 failed with error 0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.". End Error Error: 2018-03-27 07:37:25.44 Code: 0xC0010018 Source: Description: Error loading value "<DTS:Property xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:Name="PackageFormatVersion">6</DTS:Property>" from node "DTS:Property". End Error Could not load package "Maintenance Plans\BackupBob" because of error 0xC0010014. Description: One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors. Source: Started: 7:37:25 AM Finished: 7:37:25 AM Elapsed: 0.312 seconds. The package could not be loaded. The step failed.
-
But the question is exactly how some SSIS packages are run. SQL 2012 has a different folder structure for where that DTEXEC program is located, so any SQL Agent Jobs that just execute a program or a batch file might well be referencing the old path, which was probably NOT removed during the in-place upgrade. My guess is that you have hard-coded paths in some jobs to the old folder, but hard to know without more detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
An example of what the command line looks like for one of the jobs:
/FILE "\"C:\LowIntegration\LowAXDump\LOW\bin\LowAXDump.dtsx\"" /CHECKPOINTING OFF /REPORTING EWe tried doing a repair again without any change in behavior. I found the registry entry for dtexec and modified to point it to the new path, but the agent wouldn't start. After changing it back the agent started again. We wrote the backup scripts by hand instead of using the wizard and they work. Some of the other jobs we're creating batch files to run them via Task Scheduler instead of the agent until we can get things figured out. After the number of hours we've spent trying to figure it out, we're thinking our best bet will be to setup a new server and then do backup/restores for each database.
- Bob Thiele - Thursday, March 29, 2018 12:20 PM
The command line you show there doesn't disclose the location of the DTEXEC.exe file, but the problem could be that the Agent Service is still 2008 R2, Perhaps the upgrade didn't address upgrading the Agent Service, or the 2008 R2 Agent Service was left alone and still runs? No way I can know... However, one could try to disable the SQL 2008 R2 Agent Service and see if you still have a SQL Agent service, as an experiment. If you don't, then perhaps run an install of the 2012 Agent Service. You'd then need to re-apply any patches, CUs or Service Packs to SQL 2012... Alternatively, your idea to start fresh with another server would probably be the ideal solution. I hate in-place upgrades... they always seem to leave something in the way of a bad taste in my mouth.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
That's the weird part. The binary shown in SQL Configuration Manager for the agent points to version 11 instead of 10.52. Thanks for your input. We're going ahead with building a new server over the weekend.
- Bob Thiele - Friday, March 30, 2018 11:20 AM
So have you determined that the SQL 2008 R2 Agent Service is NOT still in the category of "alive and kickin' " ? If you can run SSMS 2008 R2, you might see what it thinks the Agent Service is using SQL Server 2008 R2 Configuration Manager... (again, if it's still around)...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I don't think it has anything to do with Agent. That whole mess can happen when you have two dtsexec on the server. You can try using the full path the dtsexec - along the lines of:
DriveLetter\Program Files\Microsoft SQL Server\110\DTS\Binn\DTEXEC.exe /F "C:\LowIntegration\LowAXDump\LOW\bin\LowAXDump.dtsx"My guess is that some of this happens as environment path variables get messed up. You would want to make sure the path for the current version is before the old version if both exist.
Sue
- Sue_H - Friday, March 30, 2018 3:03 PM
Sue is referring to the PATH environment variable, which can really mess you up. Check that first, as it seems likely that the old path to the old executable is probably still in existence and still in the PATH environment variable in a position prior to the path to the current version. You could also just re-name that executable to dtsexec.exe.old, but why continue to force your OS to search that folder when it's out of date software?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks for everyone's input, but we couldn't get it working. Over the weekend we spun up a new server, installed 2012 and did a backup/restore of all the databases to get it working correctly.
The 2008 environment variables were ahead of the 2012 in the path. We removed the 2008 references the first night and rebooted without any success. Below is the path we used thereafter:
C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\2008 is still on the server. But the default instance, which we are having problems with, is pointed to 2012
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply