How to know Which SP is installed in SQL Server

Question

Post reply

How to know Which SP is installed in SQL Server

Manish Joisar

Ten Centuries

Points: 1173
More actions
August 5, 2005 at 12:06 am

#186879

I have problem related XML when i include ( & , ') & use it thru
SP_XML_PREPAREDOCUMENT it give XML Parsing Error
I found that for this error i have to download latest SP from Microsoft
OS is Windows 2000 Professional so Desktop SP i have to install ??

Viewing 8 posts - 1 through 7 (of 7 total)

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

Chrislis SSChasing Mays Points: 622 More actions · Answer 1

Chrislis

SSChasing Mays

Points: 622

August 5, 2005 at 2:57 am

#579936

select @@version

Manish Joisar Ten Centuries Points: 1173 More actions · Answer 2

I want to know which SP is installed on my machine

if i put

Select @@Version

it comes like this

what is SP Info in this

One More Quest

How to Install SP4 on Desktop Machine

If i install with Setup.exe it showing me 'instance name is not valid' because i have already install MSDE Setup

If remove that Setup it show

Strong Password is required for Security Purpose Please use SAPWD ......

Now what to do for this

I am updating SP4 thru SQL2000.MSDE-KB884525-SP4-x86-ENU.EXE file

Phil Parkin SSC Guru Points: 245813 More actions · Answer 3

Try this for SP level:

SELECT SERVERPROPERTY ('productlevel')

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Manish Joisar Ten Centuries Points: 1173 More actions · Answer 4

Manish Joisar

Ten Centuries

Points: 1173

August 5, 2005 at 5:53 am

#579956

It gives RTM

what it means

SQLBill SSC Guru Points: 51440 More actions · Answer 5

The 760 of 8.00.760 identifies the 'build'. 760 is SQL Server service pack 3 (SP3).

You can find this somewhere on Microsoft's website, but you can also find it at:

http://www.sqlsecurity.com/DesktopDefault.aspx

Then go to FAQ/Docs and choose SQL Server Version Database.

From SP3a and on, you need to have a strong password for the SA account. Run this to change the password: (in a command prompt window)

osql -U sa

at the password prompt enter the SA password or press enter if the password is blank.

If you are using Windows Authentication only, type:

osql -E

then type:

sp_password @old = <old password or NULL>, @new = '<strong password>', @loginame = 'sa'

GO

Make sure the GO is on a separate line. Replace the information in the <>'s (and the <>'s) with the correct information. For example:

sp_password @old = NULL, @new = 'jafyUU7JDL', @loginame = 'sa'

Also, note that @loginame only has one n. And if you use a password for @old, it must be in single quotes unless it's the word NULL.

-SQLBill

Manish Joisar Ten Centuries Points: 1173 More actions · Answer 6

My Actual Problem is

I am passing XML Document To Stored Procedure which

use OPENXML Method to get data from XML & Update to database

It works fine, but when user enter (' , &amp in data i get the XML Parsing Error

as white spaces are not allowed

For this one of my friend told to get Latest SP for SQL Server

I have already downloaded SP4

So the solution is to install SP4 or Anything else

Thanks

Jamie Christian SSC Enthusiast Points: 125 More actions · Answer 7

I realize that I am a day late and a dollar short, but if you want to know Version, SQL Install Path, Default Database Path, and then some you could run this in Query Analyzer.................

--===[ Script Begin ]===--

-- ===============================================================================================

-- Object : SQL_Server_FullDocumentation.txt

-- Description : Exports SQL Server Settings and Configurations.

--

-- Usage : SQL_Server_FullDocumentation

--

-- Returns : a Flat Text File of Settings and Configurations.

--

-- Created by : Jamie 'MudFace' Christian

-- ===============================================================================================

-- Data Collected :

-- ===============================================================================================

-- Server Name

-- Instance Name

-- Number of Processors

-- Installed Memory

-- Product Version

-- Product Level

-- Product Edition

-- Engine Edition

-- License Type

-- Number of Licenses

-- Install Path

-- Default Database Directory

-- Default Log File Directory

-- Default Backup Directory

-- Default SQL Tools Directory

-- ===============================================================================================

-- NOTE: I know there are easier ways to collect this information,

-- I wanted to collect the data into Temp Tables on purpose so that later I can

-- come back and re-write this script to outout its information to HMTL.

-- ===============================================================================================

-- Created: 05/11/2005

-- Modtime: 05/14/2005

-- ===============================================================================================

SET NOCOUNT ON

SET DATEFORMAT dmy

USE master

GO

PRINT ''

PRINT '+-----------------------------------------------------------------------------+'

PRINT '¦ Database and Operational System versions.'

PRINT '+-----------------------------------------------------------------------------+'

PRINT ''

-- ===================================================================================

-- ===[ SQL Server Basic Information ]================================================

-- ===================================================================================

PRINT '+-----------------------------------------------------------------------------+'

PRINT '¦ SQL Server Details]'

PRINT '+-----------------------------------------------------------------------------+'

-- ====================================

-- SQL Server Install Details

-- ====================================

-- Install Path

DECLARE @rc INT

DECLARE @dir NVARCHAR(4000)

DECLARE @datadir NVARCHAR(4000)

DECLARE @logdir NVARCHAR(4000)

DECLARE @backdir NVARCHAR(4000)

DECLARE @installedmemory VARCHAR(20)

DECLARE @Server_Name SYSNAME

DECLARE @ToolsPath NVARCHAR(512)

DECLARE @RegKeyProcs VARCHAR(50)

DECLARE @RegKeyMem VARCHAR(50)

DECLARE @RegMem VARCHAR(50)

DECLARE @RegValueProcs VARCHAR(50)

DECLARE @RegValueMem VARCHAR(50)

DECLARE @server SYSNAME

DECLARE @NumProcs VARCHAR(60)

SET @RegKeyMem = 'NumberOfProcessors'

DECLARE @srvobject INT -- Server object

DECLARE @object INT -- Work variable for instantiating COM objects

DECLARE @hr INT -- Contains HRESULT returned by COM

-- ====================================

-- Default Install Directory

-- ====================================

EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLPath', @dir OUTPUT, 'no_output'

-- ====================================

-- Default Data Directory

-- ====================================

EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @datadir OUTPUT, 'no_output'

IF (@datadir IS NULL)

BEGIN

EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @datadir OUTPUT, 'no_output'

SELECT @datadir = @datadir + N'\Data'

END

-- ====================================

-- Default Data Log Directory

-- ====================================

EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @logdir OUTPUT, 'no_output'

IF (@logdir IS NULL)

BEGIN

EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @logdir OUTPUT, 'no_output'

SELECT @logdir = @logdir + N'\Data'

END

-- ====================================

-- Default Backup Directory

-- ====================================

EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @Backdir OUTPUT, 'no_output'

-- ====================================

-- Read the Registry for Number of Processors & Memory

-- ====================================

SET @RegKeyProcs = 'NumberOfProcessors'

SET @RegKeyMem = 'PhysicalMemory'

-- Create a SQLServer object

EXEC @hr=sp_OACreate 'SQLDMO.SQLServer', @srvobject OUTPUT

-- Connect to the server

EXEC @hr = sp_OASetProperty @srvobject, 'LoginSecure', 1

EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @@ServerName

-- Get a pointer to the SQLServer object's Registry object

EXEC @hr = sp_OAGetProperty @srvobject, 'Registry', @object OUT

-- Get the registry value

EXEC @hr = sp_OAGetProperty @object, @RegKeyProcs, @RegValueProcs OUT

EXEC @hr = sp_OAGetProperty @object, @RegKeyMem, @RegValueMem OUT

-- PRINT @RegValueProcs

-- PRINT @RegValueMem

EXEC sp_OADestroy @srvobject -- Dispose of the server object

SET @Server_Name = @@ServerName

EXECUTE dbo.sp_MSgettools_path @ToolsPath OUT;

-- ====================================

DECLARE @MachineName VARCHAR(60)

DECLARE @ProductVersion VARCHAR(60)

DECLARE @ProductLevel VARCHAR(60)

DECLARE @Edition VARCHAR(60)

DECLARE @EngineEdition VARCHAR(60)

DECLARE @LicenseType VARCHAR(60)

DECLARE @NumLicenses VARCHAR(60)

DECLARE @Instance VARCHAR(50)

DECLARE @InstallPath VARCHAR(80)

SELECT @Instance = CONVERT(VARCHAR(60), SERVERPROPERTY( 'InstanceName' ))

SELECT @NumLicenses = CONVERT(VARCHAR(60), SERVERPROPERTY( 'NumLicenses' ))

SELECT @MachineName = CONVERT(VARCHAR(60), SERVERPROPERTY( 'MachineName'))

SELECT @Instance = CONVERT(VARCHAR(60), SERVERPROPERTY( 'InstanceName' ))

SELECT @ProductVersion = CONVERT(VARCHAR(60), SERVERPROPERTY( 'ProductVersion' ))

SELECT @ProductLevel = CONVERT(VARCHAR(60), SERVERPROPERTY( 'ProductLevel' ))

SELECT @Edition = CONVERT(VARCHAR(60), SERVERPROPERTY( 'Edition' ))

SELECT @EngineEdition = (CASE SERVERPROPERTY( 'EngineEdition')

WHEN 1 THEN 'Personal or Desktop'

WHEN 2 THEN 'Standard'

WHEN 3 THEN 'Enterprise'

END )

SELECT @LicenseType = CONVERT(VARCHAR(60), SERVERPROPERTY( 'LicenseType' ))

SELECT @NumLicenses = CONVERT(VARCHAR(60), SERVERPROPERTY( 'NumLicenses' ))

-- ====================================

-- ===================================================================================

-- ===[ Begin here ]==================================================================

-- ===================================================================================

PRINT 'Server Name................: ' + @MachineName

PRINT 'Instance...................: ' + CONVERT(VARCHAR(30),@@SERVICENAME)

PRINT 'Number of Processors.......: ' + @RegValueProcs

PRINT 'Installed Memory...........: ' + @RegValueMem + ' MB'

PRINT ''

PRINT 'Product Version............: ' + @ProductVersion

PRINT 'Product Level..............: ' + @ProductLevel

PRINT 'Edition....................: ' + @Edition

PRINT 'Engine Edition.............: ' + @EngineEdition

PRINT 'License Type...............: ' + @LicenseType

PRINT 'Number of Licenses.........: ' + ISNULL(@NumLicenses, 'NULL')

PRINT ''

PRINT 'Install Path...............: ' + @dir

PRINT 'Default Database Directory.: ' + @datadir

PRINT 'Default Log File Directory.: ' + @logdir

PRINT 'Default Backup Directory...: ' + @backdir

PRINT 'Default Tools Directory....: ' + @ToolsPath

PRINT ''

PRINT 'Current Date Time..........: ' + CONVERT(VARCHAR(30),GETDATE(),100)

PRINT 'User.......................: ' + USER_NAME()

PRINT ''

GO

-- ====================================

-- EXEC master..xp_msver

-- GO

-- ====================================

PRINT ''

PRINT '+-----------------------------------------------------------------------------+'

--===[ Script End ]===--

--======================================================

This will output:

+-----------------------------------------------------------------------------+

¦ Database and Operational System versions.

+-----------------------------------------------------------------------------+

¦ SQL Server Details]

+-----------------------------------------------------------------------------+

Server Name................: DELL

Instance...................: MSSQLSERVER

Number of Processors.......: 2

Installed Memory...........: 2047 MB

Product Version............: 8.00.760

Product Level..............: SP3

Edition....................: Personal Edition

Engine Edition.............: Personal or Desktop

License Type...............: DISABLED

Number of Licenses.........: NULL

Install Path...............: C:\Program Files\Microsoft SQL Server\MSSQL

Default Database Directory.: C:\HelpDeskDatabase\

Default Log File Directory.: C:\HelpDeskDatabase\

Default Backup Directory...: C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP

Default Tools Directory....: C:\Program Files\Microsoft SQL Server\80\Tools

Current Date Time..........: Aug 8 2005 1:57AM

User.......................: dbo

+-----------------------------------------------------------------------------+

Sorry I could not help with the XML