If you are ever trying to work out what hotfix a SQL Server 2000 build equates to, the Build Chart on the ASPFAQ website will certainly come in very handy. Not only does it list all the build numbers and the related hotfixes but also provided is a link to the associated KB Article. http://www.aspfaq.com/sql2000builds.asp.
The May edition of the WARDY IT SQL Server Newsletter has been e-mailed to everyone on the distribution list. For those not on the distribution list you can view the newsletter using the following URL http://www.wardyit.com/newsletters/may2005.htm.
To subscribe to the newsletter send an email to email@example.com.
Many of you may be aware that the Login Mode for SQL Server is stored in the Registry key HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer (another good reason to ensure that the Registry is secured).
Where 1 = Windows Only Authentication and 2 = SQL Server and Windows Authentication.
If you have sufficient privileges in SQL Server but not in the registry then the Authentication mode is easy to change in the Properties of the server in Enterprise Manager, but when you have MSDE and no client tools how do you change the Authentication mode from Windows Only to SQL and Windows…Using osql (command line query analyser). The command below can be executed from the command line to change the Authentication mode on a SQL Server instance if you have permission to execute the Extended Stored Procedure xp_instance_regwrite (by default only sysadmin).
osql -E -Q”xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\MSSQLServer’, ‘LoginMode’, N’REG_DWORD’, 2″
Below is a VBScript code sample illustrating how to use SQLDMO to generate a SQL script of all of the operators on a SQL Server instance to assist in migrating from one instance to another:
Const SQLDMOScript_Drops = 1
Const SQLDMOScript_Default = 4
Const SCRIPT_FILE = “c:\operators.txt”
Const ForWriting = 2
Set oSQLServer = CreateObject(“SQLDMO.SQLServer”)
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
Set oFSOWrite = oFSO.OpenTextFile(SCRIPT_FILE, ForWriting, True)
oSQLServer.LoginSecure = True
For each op in oSQLServer.JobServer.Operators
txt = op.Script(SQLDMOScript_Drops + SQLDMOScript_Default)
Set oFSOWrite = Nothing
Set oSQLServer = Nothing
Recently a client of ours had the need to run a DTS Package on a scheduled basis to read an Outlook Mail Profile and perform some transformations on the mail messages before inserting them into a SQL Server table. Below is a VBScript code sample illustrating of how to read unread messages from an Outlook Mail Profile.
Set oSession = CreateObject(“MAPI.Session”)
oSession.Logon “WARDY IT Solutions”, , True, True
Set oFolder = oSession.Inbox
Set oMessages = oFolder.Messages
Set oMsg = oMessages.GetFirst
While Not oMsg Is Nothing
If oMsg.Unread = True Then
strEmailFrom = oMsg.Sender.Address
strEmailBody = Trim(Replace(oMsg.Text, “‘”, “”””))
strEmailSubject = Trim(Replace(oMsg.Subject, “‘”, “””))
EmailRecivedDate = oMsg.TimeReceived
Set oMsg = oMessages.GetNext
Set oSession = Nothing
For those in Australia who are not aware Itzik Ben-Gan from Solid Quality Learning is in Australia promoting the launch of Solid Quality Learning’s training services in Australia. If you are interested in attending any of Itzik’s one day or User Group sessions then you still have time. Registration information can be found at http://www.sqlserver.org.au/itzik.aspx. If Itzik’s name sounds familiar then it is probably because you have read one of his many articles including his regular column in SQL Server Magazine on solving complex SQL problems using logic to produce efficient set-based queries.
Microsoft has found an issue with the final build of SQL Server 2000 SP4 that impacts customers who are running SQL Server 2000 with Address Windowing Extensions (AWE) support enabled. If you are utilising this configuration, you should not install SP4 as the amount of memory will be limited to less then 4GB regardless of how much physical memory is installed.
SQL Junkies has a interesting article by Rob Garrison regarding the performance difference between a script run on SQL Server 2000 and 2005 using identical hardware http://www.sqljunkies.com/Tutorial/077C7BEB-EB31-4A07-923D-BE309F59D0F8.scuk