 |
|
8000XXXX Errors ASP.NET 2.0 Classic ASP 1.0
Databases Access DB & ADO General SQL Server & Access Articles MySQL Other Articles Schema Tutorials Sql Server 2000 Sql Server 2005 General Concepts Search Engine Optimization (SEO)Search
ASP FAQ Tutorials :: Databases :: Sql Server 2000 :: How do I know which version of SQL Server I'm running?
How do I know which version of SQL Server I'm running?
For SQL Server 7.0, 2000, and 2005, running the following will extract ONLY the version information.
And the following query will work on SQL Server 2000 and up:
SQL Server 2000 / 2005
If you are running SQL Server 2000, please see the new and dynamic SQL Server 2000 Build Chart.
If you are running SQL Server 2005, please see the following article: What version of SQL Server 2005 do I hav...
Want to find out who installed a specific HotFix? The following registry key will have a sub-key for each build number (e.g. there will be a key called "0818" for 8.00.818) with values for DateInstalled, InstalledBy, and KBArticle.
MSDE
If you are using MSDE, you can use osql (other products might tell you with less effort; see Article #2442).
You may need to determine whether MSDE is running as a default instance or a named instance. Look in the Services applet and find services starting with MSSQLSERVER. For example, if you have a service named MSSQLSERVER$MYSERVER, you have a named instance called MYSERVER. So, you can use the following to return version information at a command prompt:
SQL Server 7.0 / MSDE 1.0
7.00.1150 = SP4 + KB #891116 (Updated 2005-02-22)
7.00.1149 = SP4 + KB #867763 (Updated 2004-08-23)
7.00.1143 = SP4 + KB #829015 (Updated 2004-03-18)
7.00.1097 = SP4 + KB #822756 (Updated 2003-08-21)
7.00.1094 = SP4 + KB #821279 (Updated 2003-09-09)
7.00.1092 = SP4 + KB #820788 (Updated 2003-10-09)
7.00.1090 = SP4 + KB #819773 (Updated 2004-02-04)
7.00.1078 = SP4 + KB #327068 (Updated 2003-09-16)
7.00.1077 = SP4 + KB #327068 (superceded)
7.00.1076 = SP4 + KB #327068 (superceded)
7.00.1063 = SP4
7.00.1030 = SP3 + KB #318268 (Updated 2003-05-13)
7.00.1004 = SP3 + KB #304851 (Updated 2003-04-21)
7.00.978 = SP3 + KB #285870 (Updated 2003-05-20)
7.00.977 = SP3 + KB #284351 (Updated 2002-04-25)
7.00.970 = SP3 + KB #282243 (Updated 2003-05-20)
7.00.961 = SP3
7.00.921 = SP2 + KB #283837
7.00.843 = SP2 + Security Bulletin MS00-092
7.00.842 = SP2
7.00.835 = SP2 Beta
7.00.699 = SP1
7.00.689 = SP1 Beta
7.00.677 = MSDE 1.0 (shipped with Office 2000 Developer Edition)
7.00.623 = SQL Server 7.0 RTM
7.00.583 = SQL Server 7.0 RC1
7.00.517 = SQL Server 7.0 Beta 3
If you are wary about using the string parsing provided (either for forward- or backward-compatibility), or are worried your server might have another build of SQL Server (e.g. a beta of a service pack), you can roll your own parsing simply by working with the results of the following:
These results are a little superfluous, IMHO... but may contain any extra information you need. For example, they also tell you which version of NT / Windows 2000 (including service pack level), the actual type of SQL Server installed (for example, Enterprise Edition), and whether the system is Intel or Alpha. Finally, if you want even more exhaustive information, you can use the following (undocumented) extended stored procedure:
See KB #321185 for official Microsoft techniques.
Do I have SP3 or SP3a?
A lot of people have asked how to tell if they have SP3 or SP3a. Here is the text from the SP3a readme:
Analysis Services
There isn't a way, that I know of, to programmatically determine the version of Analysis Services. For the product that ships with SQL Server 2000, the SP3a readme tells us this:
You can also check the version of msmdsrv.exe, for example there have been some hotfixes available for Analysis Services, and you might have a different build number from above:
8.0.999.0 = SP3 + KB #891575 (Updated 2005-03-03)
8.0.989.0 = SP3 + KB #889524 (Updated 2005-01-19)
8.0.986.0 = SP3 + KB #888504 (Updated 2005-02-23)
8.0.979.0 = SP3 + KB #885928 (Updated 2005-02-10)
8.0.976.0 = SP3 + KB #885421 (Updated 2004-12-02)
8.0.968.0 = SP3 + KB #883485 (Updated 2004-09-02)
8.0.966.0 = SP3 + KB #872934 (Updated 2005-01-14)
8.0.960.0 = SP3 + KB #872783 (Updated 2004-09-21)
8.0.950.0 = SP3 + KB #843262 (Updated 2004-07-30)
8.0.947.0 = SP3 + KB #842859 (Updated 2004-07-09)
8.0.940.0 = SP3 + KB #841856 (Updated 2004-06-11)
8.0.931.0 = SP3 + KB #834844 (Updated 2004-05-11)
8.0.924.0 = SP3 + KB #838840 (Updated 2004-05-03)
8.0.921.0 = SP3 + KB #838004 (Updated 2004-03-11)
8.0.918.0 = SP3 + KB #837724 (Updated 2004-05-07)
8.0.909.0 = SP3 + KB #834419 (Updated 2004-03-09)
8.0.885.0 = SP3 + KB #833162 (Updated 2003-12-09)
8.0.875.0 = SP3 + KB #831655 (Updated 2003-12-10)
8.0.874.0 = SP3 + KB #831652 (Updated 2003-12-08)
8.0.872.0 = SP3 + KB #831045 (Updated 2003-11-24)
8.0.868.0 = SP3 + KB #830393 (Updated 2003-10-15)
8.0.864.0 = SP3 + KB #829897 (Updated 2003-10-09)
8.0.860.0 = SP3 + KB #829051 (Updated 2003-10-17)
8.0.853.0 = SP3 + KB #827899 (Updated 2003-10-17)
8.0.849.0 = SP3 + KB #826524 (Updated 2003-10-17)
8.0.843.0 = SP3 + KB #823373 (Updated 2005-02-22)
8.0.836.0 = SP3 + KB #823474 (Updated 2003-08-15)
8.0.817.0 = SP3 + KB #820358 (Updated 2003-08-08)
8.0.813.0 = SP3 + KB #819757 (Updated 2003-08-08)
8.0.812.0 = SP3 + KB #819609 (Updated 2004-04-21)
8.0.808.0 = SP3 + KB #819606 (Updated 2003-08-08)
8.0.783.0 = SP3 + KB #817285 (Updated 2003-08-26)
8.0.773.0 = SP3 + KB #816480 (Updated 2003-06-21)
8.0.770.0 = SP3 + KB #814956 (Updated 2003-06-20)
8.0.768.0 = SP3 + KB #813487 (Updated 2003-06-20)
8.0.760.0 = SQL Server 2000 SP3 / SP3a 1 (Updated 2003-08-27)
8.0.727.0 = SP2 + KB #813487 (Updated 2003-06-20)
8.0.708.0 = SP2 + KB #811628 (Updated 2003-07-01)
8.0.694.0 = SP2 + KB #331610 (Updated 2003-06-20)
8.0.683.0 = SP2 + KB #328876 (Updated 2003-12-18)
8.0.681.0 = SP2 + KB #328873 (Updated 2003-12-18)
8.0.671.0 = SP2 + KB #329479 (Updated 2003-09-24)
8.0.657.0 = SP2 + KB #326827 (Updated 2002-12-30)
8.0.639.0 = SP2 + KB #324859 (Updated 2002-12-18)
8.0.637.0 = SP2 + KB #325289 (Updated 2002-01-09)
8.0.616.0 = SP2 + KB #322911 (Updated 2002-12-30)
8.0.601.0 = SP2 + KB #320730 (Updated 2004-03-19)
8.0.534.0 = SP2
8.0.470.0 = SP1 + KB #313279 (Updated 2005-02-21)
8.0.448.0 = SP1 + KB #309181 (Updated 2002-10-16)
8.0.425.0 = SP1 + KB #309385 (Updated 2002-10-16)
8.0.417.0 = SP1 + KB #303897 (Updated 2003-04-23)
8.0.382.0 = SP1
8.0.02.80 = RTM + KB #294993 (Updated 2003-04-23)
8.0.02.65 = RTM + KB #293195 (Updated 2002-11-11)
8.0.022.6 = RTM + KB #278239 (Updated 2002-11-11)
8.0.194.0 = RTM
And if you are running the 64-bit version of Analysis Services:
8.0.989.0 = SP3 + KB #885435 (Updated 2005-01-12)
8.0.960.0 = SP3 + KB #872783 (Updated 2004-09-21)
8.0.931.0 = SP3 + KB #839854 (Updated 2004-05-06)
8.0.872.0 = SP3 + KB #825804 (Updated 2004-03-02)
See KB #825804 for the list of issues addressed by this hotfix.
8.0.861.0 = SP3 + KB #829582 (Updated 2003-10-27)
8.0.843.0 = SP3 + KB #819606 (Updated 2003-08-08)
8.0.826.0 = SP3 + KB #824219 (Updated 2003-08-25)
8.0.810.0 = SP3 + KB #822017 (Updated 2003-08-01)
| SELECT LTRIM(RIGHT(LEFT(@@VERSION,38),9)) |
And the following query will work on SQL Server 2000 and up:
| SELECT 'SQL Server ' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' (' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')' |
SQL Server 2000 / 2005
If you are running SQL Server 2000, please see the new and dynamic SQL Server 2000 Build Chart.
If you are running SQL Server 2005, please see the following article: What version of SQL Server 2005 do I hav...
Want to find out who installed a specific HotFix? The following registry key will have a sub-key for each build number (e.g. there will be a key called "0818" for 8.00.818) with values for DateInstalled, InstalledBy, and KBArticle.
| HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\HotFixes\ |
MSDE
If you are using MSDE, you can use osql (other products might tell you with less effort; see Article #2442).
| osql -E -q"SELECT @@VERSION" |
You may need to determine whether MSDE is running as a default instance or a named instance. Look in the Services applet and find services starting with MSSQLSERVER. For example, if you have a service named MSSQLSERVER$MYSERVER, you have a named instance called MYSERVER. So, you can use the following to return version information at a command prompt:
| osql -S".\MYSERVER" -q"SELECT @@VERSION" |
SQL Server 7.0 / MSDE 1.0
7.00.1150 = SP4 + KB #891116 (Updated 2005-02-22)
7.00.1149 = SP4 + KB #867763 (Updated 2004-08-23)
7.00.1143 = SP4 + KB #829015 (Updated 2004-03-18)
7.00.1097 = SP4 + KB #822756 (Updated 2003-08-21)
7.00.1094 = SP4 + KB #821279 (Updated 2003-09-09)
7.00.1092 = SP4 + KB #820788 (Updated 2003-10-09)
7.00.1090 = SP4 + KB #819773 (Updated 2004-02-04)
7.00.1078 = SP4 + KB #327068 (Updated 2003-09-16)
7.00.1077 = SP4 + KB #327068 (superceded)
7.00.1076 = SP4 + KB #327068 (superceded)
7.00.1063 = SP4
7.00.1030 = SP3 + KB #318268 (Updated 2003-05-13)
7.00.1004 = SP3 + KB #304851 (Updated 2003-04-21)
7.00.978 = SP3 + KB #285870 (Updated 2003-05-20)
7.00.977 = SP3 + KB #284351 (Updated 2002-04-25)
7.00.970 = SP3 + KB #282243 (Updated 2003-05-20)
7.00.961 = SP3
7.00.921 = SP2 + KB #283837
7.00.843 = SP2 + Security Bulletin MS00-092
7.00.842 = SP2
7.00.835 = SP2 Beta
7.00.699 = SP1
7.00.689 = SP1 Beta
7.00.677 = MSDE 1.0 (shipped with Office 2000 Developer Edition)
7.00.623 = SQL Server 7.0 RTM
7.00.583 = SQL Server 7.0 RC1
7.00.517 = SQL Server 7.0 Beta 3
If you are wary about using the string parsing provided (either for forward- or backward-compatibility), or are worried your server might have another build of SQL Server (e.g. a beta of a service pack), you can roll your own parsing simply by working with the results of the following:
| SELECT @@VERSION |
These results are a little superfluous, IMHO... but may contain any extra information you need. For example, they also tell you which version of NT / Windows 2000 (including service pack level), the actual type of SQL Server installed (for example, Enterprise Edition), and whether the system is Intel or Alpha. Finally, if you want even more exhaustive information, you can use the following (undocumented) extended stored procedure:
| EXEC master..xp_msver --or EXEC sp_server_info |
See KB #321185 for official Microsoft techniques.
Do I have SP3 or SP3a?
A lot of people have asked how to tell if they have SP3 or SP3a. Here is the text from the SP3a readme:
- "To determine whether you have SP3 or SP3a installed, look at the version number of the Net-Library file, Ssnetlib.dll. If the version number of this file is 2000.80.760.0, you have SP3; if the version number of this file is 2000.80.766.0, you have SP3a."
Analysis Services
There isn't a way, that I know of, to programmatically determine the version of Analysis Services. For the product that ships with SQL Server 2000, the SP3a readme tells us this:
- From the Start menu, point to Program Files, point to SQL Server 2000, point to Analysis Services, and then click Analysis Manager.
- In the Analysis Manager tree, right-click the Analysis Servers node, and then click About Analysis Services.
- Use the following table to determine which version of Analysis Services you have.
Build number = Product version 8.0.194 RTM 8.0.382 SP1 8.0.534 SP2 8.0.760 SP3/SP3a
You can also check the version of msmdsrv.exe, for example there have been some hotfixes available for Analysis Services, and you might have a different build number from above:
8.0.999.0 = SP3 + KB #891575 (Updated 2005-03-03)
8.0.989.0 = SP3 + KB #889524 (Updated 2005-01-19)
8.0.986.0 = SP3 + KB #888504 (Updated 2005-02-23)
8.0.979.0 = SP3 + KB #885928 (Updated 2005-02-10)
8.0.976.0 = SP3 + KB #885421 (Updated 2004-12-02)
8.0.968.0 = SP3 + KB #883485 (Updated 2004-09-02)
8.0.966.0 = SP3 + KB #872934 (Updated 2005-01-14)
8.0.960.0 = SP3 + KB #872783 (Updated 2004-09-21)
8.0.950.0 = SP3 + KB #843262 (Updated 2004-07-30)
8.0.947.0 = SP3 + KB #842859 (Updated 2004-07-09)
8.0.940.0 = SP3 + KB #841856 (Updated 2004-06-11)
8.0.931.0 = SP3 + KB #834844 (Updated 2004-05-11)
8.0.924.0 = SP3 + KB #838840 (Updated 2004-05-03)
8.0.921.0 = SP3 + KB #838004 (Updated 2004-03-11)
8.0.918.0 = SP3 + KB #837724 (Updated 2004-05-07)
8.0.909.0 = SP3 + KB #834419 (Updated 2004-03-09)
8.0.885.0 = SP3 + KB #833162 (Updated 2003-12-09)
8.0.875.0 = SP3 + KB #831655 (Updated 2003-12-10)
8.0.874.0 = SP3 + KB #831652 (Updated 2003-12-08)
8.0.872.0 = SP3 + KB #831045 (Updated 2003-11-24)
8.0.868.0 = SP3 + KB #830393 (Updated 2003-10-15)
8.0.864.0 = SP3 + KB #829897 (Updated 2003-10-09)
8.0.860.0 = SP3 + KB #829051 (Updated 2003-10-17)
8.0.853.0 = SP3 + KB #827899 (Updated 2003-10-17)
8.0.849.0 = SP3 + KB #826524 (Updated 2003-10-17)
8.0.843.0 = SP3 + KB #823373 (Updated 2005-02-22)
8.0.836.0 = SP3 + KB #823474 (Updated 2003-08-15)
8.0.817.0 = SP3 + KB #820358 (Updated 2003-08-08)
8.0.813.0 = SP3 + KB #819757 (Updated 2003-08-08)
8.0.812.0 = SP3 + KB #819609 (Updated 2004-04-21)
8.0.808.0 = SP3 + KB #819606 (Updated 2003-08-08)
8.0.783.0 = SP3 + KB #817285 (Updated 2003-08-26)
8.0.773.0 = SP3 + KB #816480 (Updated 2003-06-21)
8.0.770.0 = SP3 + KB #814956 (Updated 2003-06-20)
8.0.768.0 = SP3 + KB #813487 (Updated 2003-06-20)
8.0.760.0 = SQL Server 2000 SP3 / SP3a 1 (Updated 2003-08-27)
8.0.727.0 = SP2 + KB #813487 (Updated 2003-06-20)
8.0.708.0 = SP2 + KB #811628 (Updated 2003-07-01)
8.0.694.0 = SP2 + KB #331610 (Updated 2003-06-20)
8.0.683.0 = SP2 + KB #328876 (Updated 2003-12-18)
8.0.681.0 = SP2 + KB #328873 (Updated 2003-12-18)
8.0.671.0 = SP2 + KB #329479 (Updated 2003-09-24)
8.0.657.0 = SP2 + KB #326827 (Updated 2002-12-30)
8.0.639.0 = SP2 + KB #324859 (Updated 2002-12-18)
8.0.637.0 = SP2 + KB #325289 (Updated 2002-01-09)
8.0.616.0 = SP2 + KB #322911 (Updated 2002-12-30)
8.0.601.0 = SP2 + KB #320730 (Updated 2004-03-19)
8.0.534.0 = SP2
8.0.470.0 = SP1 + KB #313279 (Updated 2005-02-21)
8.0.448.0 = SP1 + KB #309181 (Updated 2002-10-16)
8.0.425.0 = SP1 + KB #309385 (Updated 2002-10-16)
8.0.417.0 = SP1 + KB #303897 (Updated 2003-04-23)
8.0.382.0 = SP1
8.0.02.80 = RTM + KB #294993 (Updated 2003-04-23)
8.0.02.65 = RTM + KB #293195 (Updated 2002-11-11)
8.0.022.6 = RTM + KB #278239 (Updated 2002-11-11)
8.0.194.0 = RTM
And if you are running the 64-bit version of Analysis Services:
8.0.989.0 = SP3 + KB #885435 (Updated 2005-01-12)
8.0.960.0 = SP3 + KB #872783 (Updated 2004-09-21)
8.0.931.0 = SP3 + KB #839854 (Updated 2004-05-06)
8.0.872.0 = SP3 + KB #825804 (Updated 2004-03-02)
See KB #825804 for the list of issues addressed by this hotfix.
8.0.861.0 = SP3 + KB #829582 (Updated 2003-10-27)
8.0.843.0 = SP3 + KB #819606 (Updated 2003-08-08)
8.0.826.0 = SP3 + KB #824219 (Updated 2003-08-25)
8.0.810.0 = SP3 + KB #822017 (Updated 2003-08-01)
Related Articles
Are there tools available for auditing changes to SQL Server data? Can I create an index on a BIT column? Can I have optional parameters to my stored procedures? Can I implement an input mask in SQL Server? Can I make SQL Server format dates and times for me? Can I start IDENTITY values at a new seed? Can SQL Server tell me which row was inserted most recently? How can I learn more about undocumented SQL Server stored procedures? How can I make my SQL queries case sensitive? How do I audit changes to SQL Server data? How do I connect to a non-default instance of SQL Server? How do I connect to SQL Server on a port other than 1433? How do I create a cross-tab (or "pivot") query? How do I determine if a table exists in a SQL Server database? How do I drop a SQL Server database? How do I find all the available SQL Servers on my network? How do I get a list of SQL Server tables and their row counts? How do I get rid of Named Pipes / DBNMPNTW errors? How do I get the correct date/time from the msdb.sysjob* tables? How do I get the nth row in a SQL Server table? How do I get the result of dynamic SQL into a variable? How do I handle REPLACE() within an NTEXT column in SQL Server? How do I hide system tables in SQL Server's Enterprise Manager? How do I limit the number of rows returned in my resultset? How do I load text or csv file data into SQL Server? How do I manage changes in SQL Server objects? How do I monitor SQL Server performance? How do I prevent linked server errors? How do I reclaim space in SQL Server? How do I recover data from SQL Server's log files? How do I search for special characters (e.g. %) in SQL Server? How do I start SQL Server Agent from ASP? How do I time my T-SQL code? How do I upsize from Access to SQL Server? How do I upsize my MSDE database to full-blown SQL Server 2000? How do I use a variable in a TOP clause in SQL Server? How do I use GETDATE() within a User-Defined Function (UDF)? How should I store an IP address in SQL Server? Schema: How do I find all the foreign keys in a database? SQL Server & MSDE What are reserved Access, ODBC and SQL Server keywords? What are the capacities of Access, SQL Server, and MSDE? What are the main differences between Access and SQL Server? What do I need to know about SQL Server 2000 SP4? Where else can I learn about SQL Server? Where is SP4 for SQL Server 2000? Why am I having problems with SQL Server 2000 SP3 / SP3a? Why can't I install SQL Server on Windows Server 2003? Why can't I install SQL Server on Windows XP? Why can't I use LIKE '%datepart%' queries? Why do I get "Login failed for user '\'."? Why do I get 'object could not be found' or 'invalid object name'? Why do I get errors about master..spt_values? Why do I get script errors in Enterprise Manager's 'taskpad' view? Why do I get SQLSetConnectAttr Failed errors? Why do I have problems with views after altering the base table? Why does EM crash when I get an error in a stored procedure? Why does Enterprise Manager return 'Invalid cursor state'? Why does my DELETE query not work? Why does sp_spaceused return inaccurate values? Why is Enterprise Manager slow at expanding my database list? Why is my app slow after upgrading from SQL Server 7 to 2000? Why is tempdb full, and how can I prevent this from happening? Why should I consider using an auxiliary calendar table? Why should I consider using an auxiliary numbers table?