SQL Server Service Pack 3

Published: February 26, 2003

Please note:Portions of this transcript have been edited for clarity

Hosts:

Moderator: Stephen (Microsoft)

Welcome to today's SQL Server Service Pack 3 chat. I will ask the hosts to introduce themselves.

Host: Kurt (PASS)

Hi! I'm Kurt Windisch, director of program development for PASS.

Host: David (Microsoft)

I'm David Botzenhart a Technical Lead with Microsoft PSS SQL Server Analysis Services

Host: Bruce (Microsoft)

Hi, I'm Bruce Prang. I'm a development manager in SQL Server.

Host: Ted (Microsoft)

Hi, I'm Ted Weinberg. I was the release manager on the SQL 2000 SP3 project. Glad to meet you all!

Host: Rashid (Microsoft)

Hi, I am Rashid Jean-Baptiste, and I am a Technical Lead with Microsoft SQL Server Support.

Host: Erika (Microsoft)

Hi! I am Erika Christensen. I am a support professional with Microsoft SQL Server Support.

Moderator: Stephen (Microsoft)

And... I am Stephen Dybing, SQL Server Communities PM. Glad you all could make it today!

Let's get started! Fire away with your questions for our hosts.

Moderator: Stephen (Microsoft)

Q: PaulZ: Do I need to install SP2 before installing SP3 - a magazine article said I did.

A: No, SQL Server service packs are cumulative. You don't need to install SP2 before installing SP3. See http://www.microsoft.com/downloads/details.aspx?familyid=90DCD52C-0488-4E46-AFBF-ACACE5369FA3&displaylang=en.

Host: Bruce (Microsoft)

Q: Heather: Why were permissions denied for the TargetServerRole within MSDB in SP3...

A: What do you mean denied?

Host: Bruce (Microsoft)

Q: Heather: for example sp_start_job was denied permissions

A: Understand. The TargetServerRole is used for Target servers to be able to see jobs on a Master server. It was a potential security issue to allow members of that role to be able to execute jobs, so it was tightened down.

Host: Ted (Microsoft)

Q: PaulZ: I had some issues when installing the fix for SQL SLAMMER when I had both the MSDE and the SQL Server engines around. The fix to SQL Server never took. Is this a known issue?

A: Hmm. Are you saying SQL Server's copy of the ssnetlib.dll is still pre-311?

Host: David (Microsoft)

Q: Nout: Are there any updates available on MDAC2.7, SP1? Or are there any coming soon?

A: There currently isn't anything after SP1, keep checking < http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860> for updates

Host: Bruce (Microsoft)

Q: IZ: When can we have a SQL Update service (like Windows update) for fixes distribution?

A: We are reviewing our options in this regard. Nothing to announce at this point.

Moderator: Stephen (Microsoft)

Q: CoreyPud: How does SP3 affect/change SQL Security?

A: Perhaps one of the biggest issues is that it turns off cross-database ownership chaining by default. Take a good look at the readme file. You view it at http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp.

Host: Ted (Microsoft)

Q: raghu: Any known issues we should be concerned about after installing SP3?

A: The best references are the Readme and Readme Addendum. The URL for the Readme is: http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp. The URL for the Readme Addendum is: http://support.microsoft.com/default.aspx?scid=kb;en-us;330022&sd=tech

Host: Erika (Microsoft)

Q: PaulZ: When will I have a method to back out a SP from an instance -- or can I do this now?

A: Currently, the only method to back out or remove a service pack from an instance is through a reinstallation. This process is documented in article 314823 HOW TO: Remove a SQL Server Service Pack << http://support.microsoft.com/default.aspx?scid=kb;en-us;314823&sd=tech >>

Host: David (Microsoft)

Q: rcook: The chat announcement mentioned "Watson for SQL" but I don't see a reference in the README. Can you elaborate?

A: Basically Watson allows crash reports to be sent to MS when they occur. MS can use these to improve the product quickly and follow trends. A KB article that explains a little about the watson is http://support.microsoft.com/default.aspx?scid=kb;en-us;328446&sd=tech

Host: Ted (Microsoft)

Q: Grue: Is there any reason for CPU consumption should go up with SP3 in transaction replication?

A: Not that we know of. If you're running into problems, please post them on the newsgroup.

Host: Rashid (Microsoft)

Q: Nout: Can you tell me anything about the KB article 814410? This should handle the performance issues after installing SP3?

A: This KB article is currently unpublished, and there is very little information internally on it. Are you running into any performance issues after upgrading to SP3?

Host: Rashid (Microsoft)

Q: Scarlet: I'm having a problem with sqlsrvr not releasing memory back to the os when Oracle needs it (for linked server/SQLNet); since sp3, causes fatal exception. Should I call techsupport on this or is this already documented as an issue?

A: As far as I know this is not a known issue, therefore, my recommendation would be to contact Microsoft SQL Server Support for assistance.

Host: Erika (Microsoft)

Q: Alex: Let's say we already applied SP3. What would be your recommendations regarding SQL Scan, SQLCheck, and SQL Critical Update?

A: If you have already applied SQL Server 2000 Service Pack 3, you may want to utilize SQLCheck, SQLScan, or SQL Critical Update to verify that your server(s) are no longer vulnerable. These utilities can also help identify any new installations that may have been installed and are not yet patched.

Host: Bruce (Microsoft)

Q: michprath: I have several different servers that I have applied SP3 on. A couple of them have had scheduled DBCC jobs fail. These tasks were created with the Maintenance wizard. If I create the tasks manually, they work fine. Is this a known issue?

A: This is not a known issue. You should either contact PSS or post in the newsgroup.

Host: Michiel (Microsoft)

Q: Vera: Any improvements in SP3 with regards to using SQL-DMO and DTS from .NET, for example the fact that in order to change a string GlobalVariable in DTS I need to delete and add the GlobalVariable if a value exist. Other big problem is the eventing bugs.

A: There were no changes regarding use of SQL-DMO/DTS from .NET. The eventing problems are known and being looked at.

Host: Michiel (Microsoft)

Q: Vera: Michiel: When will MSFT deliver official PIA's for DMO and DTS?

A: We are looking at this currently, but cannot tell yet it's going to come. I'm only speaking on behalf of DMO.

Host: Ted (Microsoft)

Q: pavan: Hi all good morning, I build an installer with vb applications, and latest msde sp3 merge modules. It installs and works fine only for Administrators. If Normal user tries to use application installer is displaying Error message "Fatal Error during Inst...

A: Could you post the details on the newsgroup for us, please?

Host: Michiel (Microsoft)

Q: Pat94: I am not familiar with SQLCheck, SQLScan & SQL Critical Update. What are they and where can I get them?

A: See http://www.microsoft.com/security/malwareremove/default.mspx for more info in SQLScan etc.

Host: David (Microsoft)

Q: Vera: How can I test if SQL Agent Watson support works?

A: There really isn't a way to test it until a Crash occurs in SQL Agent. We tested it internally with a too that injected a crash into the process.

Host: Bruce (Microsoft)

Q: Vera: Is SQL Mail supported on a cluster in SP3?

A: SQLMail support on a cluster is the same as SP2.

Host: Bruce (Microsoft)

Q: Tammy: Then, how would you recommend allowing users to start jobs?

A: Owners can start their own jobs. SQL 2000 does not have the ability to grant execute rights for jobs to non-owners.

Host: Erika (Microsoft)

Q: Vera: Does fn_getsql work pre SP3, I saw a hotfix for build 65? which is on top of SP2 correct?

A: Fn_get_sql is a system table-valued function that returns the SQL text for the specified SQLHANDLE. There is a hotfix available for post SQL Server 2000 Service Pack 2 servers. SQL Server build 8.00.652 or higher contains the fix.

Host: Rashid (Microsoft)

Q: keith: What is the cause of the SP3 installation error that people (including myself) have regarding "unable to validate the logged on user". Is there a KB that explains? Does MS plan to roll out a new version of SP3 that can properly deal with the issue?

A: There can be different causes to this error. There is currently no kb which explains this error in regards to SQL Server 2000. I am not aware of any version of SP3 that will be rolled out to address this issue.

Host: Dave (Microsoft)

Q: bhallnerd: The Documentation says that all instances of SQL must be updated to SP3 that coexist on the same computer. How do you do this when one of the instances is a MSDE and the service pack does not work with MSDE?

A: Which documentation are you seeing the statement in?

Host: Farooq (Microsoft)

Q: simas: I had some issues in cluster environment basically not finding the virtual server after the service shutdown and later restart in single user mode. Fixing HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\COnnectto helped. Had to change it to DBNETLIB. Otherwise everything works.

A: Please post this issue to the newsgroup along with details on what the previous value of the ConnectTo value was. Additional background over the reason why the service was shutdown and started in single user mode would be helpful. Also look at the Event Logs for any SQL Server related error messages during this time.

Host: David (Microsoft)

Q: michpath: I noticed SP3 installs MDAC 2.7 as well. MDAC 2.7 isn't compatible with Windows 95 clients. Is there a way to either remove the MDAC update in the SP or uninstall MDAC 2.7/revert back to 2.6?

A: MDAC 2.7 is compatible with Windows 95, but to install requires DCOM95 installed prior to the setup. Look at article <http://support.microsoft.com/?id=191094> for more information.

Moderator: Stephen (Microsoft)

Q: Andy_Ritting: I've had someone from ms support say to me that SP3 is almost a complete version level change in terms of query processing logic, is this accurate, and what has changed to increase query performance?

A: Hmm, I don't think that big of a change is going to happen in a service pack, there's too much of a chance to create new bugs. Unfortunately, we don't have anybody here from the query processor team to say for sure. You might want to follow up with that support professional.

Host: Ted (Microsoft)

Q: Vera: When can we have one service pack for both SQL and MSDE?

A: For SQL Server 2000, SQL and MSDE need to be separate downloads because they use different installer technologies. We are working on resolving this issue in future SQL releases.

Host: Rashid (Microsoft)

Q: Nout: Rashid: how many days / weeks will a KB article (in this case 814410) to be released normally then? Thanks for your answers!

A: Hello Nout. The time to release varies for each kb article, and the issue discussed in the kb article - 814410 - is still being investigated, so I am not sure when it will be released.

Host: David (Microsoft)

Q: Andy_Ritting: When I upgraded my datawarehouse database from SP2 to SP3, the analysis server selection statement went from 25minutes to 12 hours, running UPDATE STATS WITH FULLSCAN fixed the problem, why did out of date statistics have such an effect on SP3 performance?

A: I remember the case. SP3 did make changes made to the query processor. With these changes, updating the Statistics was required in your case to create a optimal query plan.

Moderator: Stephen (Microsoft)

Q: LightheartAl: You've advised to 'post in the newsgroup' multiple times; which newsgroup specifically?

A: That depends on the question. :-) There are 22 newsgroups under microsoft.public.sqlserver... Take a look at that hierarchy and post where it fits best. We monitor all of them but often other community members will answer the question before we even see them. If it's a hot issue that requires a Microsoft response, an MVP will usually point it out to me.

Host: Ted (Microsoft)

Q: LightheartAl: We've encountered this issue re the slammer patch; is this an issue with SP3? Post installation of Hotfix Update 8.00.0679 (for the slammer worm) we've encountered this error on our production server "2003-02-13 09:54:17.14 server Error: 17882, Severity: State: 1 2003-02-13 09:54:17.14 server Error accepting connection request via Net-Library 'SSNETLIB'. Execution continuing..".

A: I think this is another one for the newsgroup, where there's more time to go into detail.

Host: Bruce (Microsoft)

Q: michprath: I have several servers registered in EM. Since applying the July or October hotfix and even now after having applied SP3, I receive an error a lot of times when I first try to connect to a server. The error is the typical "not known to be running” err.

A: This basically means we could not detect the state of the service (which is done via normal Service Control APIs). You can check Tools/Options and see if polling is on.

Host: David (Microsoft)

Q: Vera: I had it execute a COM component that causes an AV and nothing happens.

A: Did you check in the Event viewer to see if an event was created that a bucket ID was created? If the AV is still occurring make sure you call in to PSS and we will work with you.

Host: Erika (Microsoft)

Q: Mich: Trying to run fn_get_sql with the example from Books Online, but no SQL code is returned; but I can get SQL with dbcc inputbuffer. Why is that?

A: fn_get_sql will return the inputbuffer based on the SQL_handle column in Master..Sysprocesses. If the spid is not performing work at the time it is queried the handle will be 0 by default and you will be unable to return the inputbuffer. DBCC INPUTBUFFER will return the inputbuffer regardless of whether work is currently being performed or not.

Moderator: Stephen (Microsoft)

Q: Kelly: When we upgrade our severs to SP3, is it a requirement that the developers and users also upgrade their SQL Client Utilities to SP3? Is it required, recommended, not necesssary, ... to upgrade the SQL Client to SP3?

A: It's recommended, but not required.

Host: Erika (Microsoft)

Q: Vera: When will there be a public fix for the SQL Manager AV introduced in SP3

A: The fix is publicly available by contacting Microsoft SQL Server Product Support for free. The details regarding this Access Violation are available in the following article: 814064 BUG: SQLBrowseConnect May Cause an Access Violation in the Client http://support.microsoft.com/?id=814064.

Host: Erika (Microsoft)

Q: Adrian: What changes to a replicated environment does SP3 bring?

A: Check out sp3readme.htm#_replication_enhancements.

Host: Erika (Microsoft)

Q: Adrian: In a replicated environment, is there a certain order to apply the SP's (i.e. distributor, publisher, subscriber)?

A: Take a look at section 3.11 of the sp3readme. It lists the order for servers participating in replication.

Host: Erika (Microsoft)

Q: Pat: I've heard mentions of a program that will analyze a trace from SQL Profiler to check for security problems that would arise from application of sp3. I've never seen any official source for it. Does this exist, and if so, how can I get a copy of it?

A: Sqlcheck is a non-Microsoft product that you can get more information about at http://www.netiq.com/sqlcheck/default.asp.

Host: Rajesh (Microsoft)

Q: Vera: How does Watson work when the box is not connected to the Internet? Anyway I can relay this stuff it is trying to send? My SQL boxes are not on the internet at all.

A: A minidump file is generated and stored in the Log directory of SQL Server. If your computer is not connected to the Internet, you can install a Corporate Error Reporting (CER) server to collect information and relay this to Microsoft. For more information about CER, please refer to the following web pages:

http://www.microsoft.com/office/ork/xp/two/adma05.htm

http://watson.microsoft.com/dw/1033/dcp.asp

Host: Farooq (Microsoft)

Q: Michael: I need to install SQL 2000 SP in a Win2K/SQL2K environment and the documentation talks about cluster resource options being changed or overlayed by the SP3 install. Can you elaborate on any details related to installing SP3 in a clustered environment?

A: The SQL Server 2000 Service Pack 3 readme refers to article 811168 which mentions that the default properties will be set for all resources in the SQL Server group as part of the install process. These resources will also be restarted as stated in article 811168. Therefore it is important to note the properties of these resources prior to Service Pack 3 installation and then set them back to their original values when the installation is done. In addition to article 811168 please refer to article 254321 for additional recommendations regarding service pack installation on a cluster.

Host: Bruce (Microsoft)

Q: Kelly: We found that the developers couldn't debug stored procedures after applying SP3 on the server - until they applied SP3 on their development PC.

A: Yes this is the case and is documented in the readme.htm file included with SP3.

Moderator: Stephen (Microsoft)

Thanks for joining us today! See you in the newsgroups

Host: Bruce (Microsoft)

Thanks for joining us, some great questions.

Host: David (Microsoft)

Thank you for attending!

Host: Kurt (PASS)

Thanks for the excellent questions! We will get to as many of the unanswered questions as possible in the transcript.

Host: Ted (Microsoft)

It's been a pleasure chatting with you all. Thanks for using SQL Server!

For further information on this topic or about SQL Server, please visit the following:

Newsgroups: SQL Server Newsgroups

SQL Transcripts: Read the archive of past SQL chats.

Website: Visit the Microsoft SQL Website