June 23, 2021 at 12:22 pm
We have inherited a SQL 2012 installation and when I attempt to open any SQL Agent jobs I get the below error. The jobs are running as scheduled just fine. Doing searching I cannot pinpoint what this is or a resolution. Has anyone ever come across this or can point me in the direction of what is wrong and a fix?
When I attempt to open up and SQL Agent jobs I get this error:
Retrieving the COM class factory for component with CLSID {8357986F-76E9-4E0A-A780-FB0541E91ECC} failed due to the following error: 8007007e The specified module could not be found. (Exception from HRESULT: 0x8007007E). (Microsoft.SqlServer.ManagedDTS)
June 23, 2021 at 2:19 pm
That error usually means a COM object is not registered correctly. I am not sure what COM object corresponds to that CLSID, but if you can determine that, you MAY be able to manually register the DLL.
I think the first thing to determine though is the scope of the problem. Does the problem happen on all workstations or just some or just one? I expect it is happening on a single workstation. If my suspicions are correct, you can probably correct it by reinstalling SSMS on that workstation. USUALLY COM related errors are workstation specific and are NOT server side errors in my experience. I am not saying they are NOT server side problems, but in my experience they have always been client side.
I'd install or reinstall SSMS on the client machine, and I'd recommend installing SSMS 2012 for connecting to a SQL Server 2012 instance. I've had strange errors when I use mismatched versions such as SSMS 18 for SQL Server 2012 before.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 23, 2021 at 2:45 pm
Great. Thanks for your help with this. The SSMS is actually installed on the DB server, sorry, I forgot to mention that specifically.
June 23, 2021 at 3:09 pm
I generally don't install SSMS on the server. To me it feels like extra overhead for little benefit. I have SSMS installed on my workstation and I connect to the SQL instances.
BUT if using it on the server is the option you need to go with, it sounds like a COM object on the server is unregistered. So you will need to determine which DLL needs to be registered OR do a repair/fresh install of SSMS. Doing the repair or fresh install of SSMS is the easier option, but MAY result in a server reboot.
Alternately, you could download SSMS 18 to connect to it which SHOULD work, but there may be little hiccups along the way. If you use SSIS, I would NOT install the wrong SSMS as pushing an SSIS package from a different SSMS version than the server will cause problems. Most other things work fine with sometimes an annoyance or 2 with intellisense where it tells you a keyword that doesn't work in that version of SQL Server.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 23, 2021 at 3:23 pm
Thanks for the info. I spoke with the guy that has maintained that server and he said from his workstation it works, just on the server it is not. He said that specific version of SSMS is needed due to some debug feature in later versions of it Microsoft has removed. I will look into this further.
June 24, 2021 at 2:34 pm
I think that that is best practice anyway. My understanding is that on a server, you want as little overhead as possible which means not installing additional tools like SSMS. I try to keep my servers as light as possible for the live ones, test ones sometimes get extra tools while I am testing things, but I clean up when I am done with my testing.
As for specific SSMS version having the debug feature removed, I think that SSMS 18 removed the debug feature. I know I have never had good luck with the debug feature in SSMS, so I've always found workarounds. Personally, I have never liked the debug feature in SSMS. In Visual Studio as a general tool, I like the debug feature as it is nice being able to step through the code and observe variable values as I go. Inside SSMS, I have not found the need to use a debugger as I find that a simple PRINT or SELECT statement can handle any debugging that I am needing to do inside SSMS. This may not be true for all use cases as I have seen some complicated stored procedures, and if it is too complex, a debugger may be helpful. In general though, once I find that the stored procedure is getting too complex, I try to simplify it. This MAY be by doing a redesign of the stored procedure OR it may be by breaking the one stored procedure into 2.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 24, 2021 at 4:27 pm
Yea. He is running the show, I was just asked to look at everything and give my recommendations from a true DBA prospective. He 'wants' that debug feature for whatever reason. I gave him all my recommendations and info on it and he will run with it I guess now going forward.
June 24, 2021 at 5:42 pm
Some people really like the debug feature. My thoughts on it are that it was a pain to get set up and it seemed to be very inconsistent on my systems. When I saw Microsoft removed it, I thought it probably was a feature they didn't want to support going forward.
Since it is removed in SSMS 18, I would not be surprised if support for it is removed in future versions of SQL Server.
I also have some suspicions on it (unconfirmed by me, but guesses about how it works in the back end) in that if the debugger is running, it is going to hold onto locks while it is run, kind of like if you do a BEGIN TRANSACTION without a COMMIT or ROLLBACK. So it could be VERY easy to cause blocking and deadlocks and other headaches. That is another good use case for the PRINT/SELECT as debugging tools - you are less likely to lock the objects for long periods of time. Can still happen, but less likely.
I know when I am debugging code in C# and get interrupted, it is EASY for me to forget I left it running in a paused state and get sucked into a new task for a few hours. This would only impact me though. If I did this on a SQL query I was debugging, I may lock up a test system for hours and would impact other developers.
That being said, I don't think the use of the debugger is a good hill to die on. If he wants to use it, I'd say let him, but I personally would look at other options. I imagine there are other more pressing issues to address than if you should use a version of SSMS that has a debugger built in or not.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply