Can't Update Build on SQL 2000 SP4

  • mstoffa

    SSC Veteran

    Points: 259

    We have a handful of servers running SQL 2000 on Windows 2000 SP4. Just recently we installed SP4 on the SQL Servers and are now running build 8.00.2050. However we ran into an issue with one of the applications that appears to have the issue fixed with build SQL Server 2000 build 8.00.2145 (KB 901212).

    FIX: You receive an error message if you use the sp_addalias or sp_dropalias procedures when the IMPLICIT_TRANSACTIONS option is set to ON in SQL Server 2000 Service Pack 4

    I have downloaded the hotfix from Microsoft that is to address this issue. However it does not appear that I can apply this update because it thinks it has a newer build on it. When I try to install the hotfix I am unable to select any updates and have the following messages:

    File Name: SQL2000-KB903086-v8.00.2148-x86x64-ENU

    Messages: This SQL Server instance you are currently running already contains the update you are trying to install. You do not need to install the update. A Hotfix of build equal to or greater than the following should be downloaded: 2273

    I am not a SQL admin so I am trying to learn this as I go along. Any advice on how to patch the server to at least build 2148 would be greatly appreciated.

  • mstoffa

    SSC Veteran

    Points: 259

    I think I have found the problem...at least I can recreate it. I took a VM and installed SQL 2000 and ran SP4. I am running version 2039 as expected. So I was trying to figure out where 2050 was coming from and I figured that it was being pushed from our internal WSUS box. Sure enough I can install Hotfix build 2148 ontop of SP4 without any issues. I then tried to remove Hotfix 2148, reboot the server, and then apply Hotfix build 2050 manually. Then I tried to apply Hotfix update 2148 and I have the same issue as I described above. It looks like the update that is being pushed from our WSUS server KB948110 Hotfix update 2050 to any SQL 2000 server is causing an issue. Has anyone had this problem before?

  • george sibbald

    SSC Guru

    Points: 104200

    I have not come across the problem but think I can take an educated guess at the cause and way round it. The update being applied by WSUS (god bless it) is security hotfix MS08-040, see this link

    http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx

    and in particular the FAQ section.

    Hotfix 2148 does not include this security patch that for you has been directly applied to SP4, so you need to apply the QFE update of MS08-040 post patch level 2050, which will bring you up to to 2273 and include the 2148 hotfixes you require. So you need to download the contents of KB948111 from

    http://www.microsoft.com/downloads/details.aspx?familyid=8316BC5E-8C2D-4710-8ACC-B815CCC81CD4&displaylang=en

    Thats my take on it anyway and I hope I explained that clearly!

    ---------------------------------------------------------------------

  • mstoffa

    SSC Veteran

    Points: 259

    I downloaded the update suggested above and applied it to my VM. It was able to update. We are going to put this on a production box later today and this should resovle our problem. Thank you for all your help.

  • george sibbald

    SSC Guru

    Points: 104200

    thats good to hear, were you able to confirm it resolves your problem on the VM box?

    ---------------------------------------------------------------------

  • mstoffa

    SSC Veteran

    Points: 259

    The issue still does not appear to be resovled. After doing additional research I want to see if I understand this correct:

    KB948111 which is the patch I applied is only going to install updates 8.00.2148-8.00.2272. (I have gathered this from reading another post and what is stated in the FAQ on for KB948111)

    http://www.microsoft.com/technet/security/Bulletin/MS08-040.mspx

    I believe the Hotfix that I need is for SQL Server 2000 build 8.00.2145 (901212 (http://support.microsoft.com/kb/901212/ ) FIX: You receive an error message if you use the sp_addalias or sp_dropalias procedures when the IMPLICIT_TRANSACTIONS option is set to ON in SQL Server 2000 Service Pack 4

    http://support.microsoft.com/kb/894905/

    KB948110 had installed by WSUS which patched it to build 8.00.2250 but it would not allow me to install the HOTFIX for build 8.00.2145 when the update was already installed.

    So do I need to remove the HOTFIX for KB948111 which should take it back to 8.00.2050. Then reboot the server. Next unstall HOTFIX taht will take it to build 8.00.2145 (KB901212). Reboot the server if needed and then install KB948111 which should take it back to build 8.00.2272 but will include the fixes in 8.00.2145.

    I hope this makes sense.

  • mstoffa

    SSC Veteran

    Points: 259

    I noticed I may need a correction on this:

    So do I need to remove the HOTFIX for KB948111 which should take it back to 8.00.2050. Then reboot the server. Next unstall HOTFIX that was applied from WSUS and it will take it to build 8.00.2039 which should be straight SP4. Then install the update to take it to build 8.00.2145. Reboot the server if needed and then install KB948111 which should take it back to build 8.00.2272 but will include the fixes in 8.00.2145.

    I hope this makes sense.

  • george sibbald

    SSC Guru

    Points: 104200

    I would need to test this to confirm which will take a while, so please do your uninstalls etc on your test box first!

    One clarification, once you are at straight SP4 (2039), apply the cumulative hotfix rollup 2187 next, this definitely will contain previous HFs like 2145, then apply security patch to bring you up to 2273.

    I wish microsoft would not muddy the waters like this, you used to be able to rely on hotfixs being cumulative, now there are different routes to various patch numbers and this happens.

    ---------------------------------------------------------------------

  • george sibbald

    SSC Guru

    Points: 104200

    mstoffa,

    I went through the process as described , applying hf2187 and then 2273, but still get an error running an addalias.

    have you proved that adding the hotfix for 2145 only fixes this problem?

    ---------------------------------------------------------------------

  • mstoffa

    SSC Veteran

    Points: 259

    I am currently working with our Dataprocessing Department on this with real data in my VM and have additional testing to do but have come up with the following so far:

    1: Load SP4 and it brakes the application

    2: Don't install the Update from WSUS and install the Hotfix that is to correct the issue. I had them test the application and it worked

    3: Pulled all curent updates from WSUS which put it at build 2273: Broke the application again.

  • mstoffa

    SSC Veteran

    Points: 259

    Here is some interesting things I found out with our testing.

    I built another VM today that is very similar to our production box (OS: Windows 2000 Server SP4 and all security updates). I then installed SQL 2000 and added SP2 which is what it was running at and as far as I know no additional hotfixes. I made sure the server was had all updates applied. We pulled a backup copy of one of our databases and restored it in the test environment (that way I was working with valid data). After this was setup I took a snapshot of the VM (W2K3SP4 - SQL2KSP2). I had them test our application and as expected no issues. I then installed SP4 for SQL2000 and took another snapshot of the VM (W2k3SP4 - SQL2KSP4). I had them test the application again and failure. I then went and installed the HOTFIX that I downloaded from Microsoft for the issue (KB901212). I installed this hotfix which took it to build 2184 and then took another SNAP (W2K3SP4 - SQL2KSP4 W/ Hotfix : Build 2184). I had them test it and the application was working. So then I wanted to see what happened if I let WSUS apply any additional updates after that and as expected it applied (KB948111 - since I was running build 8.00.2148) and I took another SNAP (W2K3SP4 - SQL2KSP4 W/ Hotfix and KB9481111 : Build 2272). I had them test it again and it failed. I then went back to my SNAP (W2K3SP4 - SQL2KSP4 W/ Hotfix : Build 2184) and had them test it again to check if the issue was fixed. This works.

    If I install update KB9481110 then I can not apply the Hotfix for KB901212 because the application thinks it is already installed but the application is broken.

    If I install SP4 and then install the Hotfix directly for KB901212 which takes it to build 2184 the issue is fixed. However as soon as I apply KB9481111 from WSUS the appliction is broken again.

    Any ideas?

  • george sibbald

    SSC Guru

    Points: 104200

    it would not be the first time a hotfix has reintroduced a problem fixed by an earlier hotfix.

    you do want to be at least at SP4 so If I was you I would apply the level that works for you (SP4 plus hotfix to 2148) and leave it at that. It leaves you at very near the highest level you can go to on SQL 2000. I would also turn off WSUS on your SQL servers, you need more control over patches that are applied to SQL.

    The security hotfix MS08-040 is 'important' rather than 'critical' so if your server is behind a firewall and not open to the world you should be ok. As this is a known bug I would also report it to MS, there should be no charge.

    If you can work up a code workaround I would for future proofing. Seems strange that an app adds or drops an alias?

    Good luck and thanks, that was fun. 🙂

    ---------------------------------------------------------------------

  • Henrik Schütze

    Ten Centuries

    Points: 1004

    Hello,

    did you find the answer?

    SQL Server 2000 8.00.2250 is a GDR Release

    SQL Server 2000 8.00.2273 is a QFE Release

    For the Difference Between GDR and QFE look:

    http://blogs.msdn.com/gauravagg/archive/2007/04/27/jargons-gdr-and-qfe-release.aspx

    GDR:

    http://support.microsoft.com/?scid=kb%3Ben-us%3B935897&x=10&y=13

    QFE:

    http://blogs.msdn.com/gauravagg/archive/2007/04/27/jargons-gdr-and-qfe-release.aspx

    If you have GDR installed this must include all patches from this line and all the build numbers like 2145, 2187 and 2249.

    But shure, a problem can come back.

    But is the problem still there if you use the QFE release 2273 ???

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply