Set SQL Server Login Mode

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″

Script Operators using SQLDMO

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

Dim oSQLServer
Dim oFSOWrite
Dim oFSO
Dim txt
Dim op

Set oSQLServer = CreateObject(“SQLDMO.SQLServer”)
Set oFSO = CreateObject(“Scripting.FileSystemObject”)

Set oFSOWrite = oFSO.OpenTextFile(SCRIPT_FILE, ForWriting, True)

oSQLServer.LoginSecure = True
oSQLServer.Connect “(local)”

For each op in oSQLServer.JobServer.Operators
txt = op.Script(SQLDMOScript_Drops + SQLDMOScript_Default)
oFSOWrite.Write txt
Next

oFSOWrite.Close
oSQLServer.DisConnect

Set oFSOWrite = Nothing
Set oSQLServer = Nothing

Read E-mail from Outlook Mail Profile

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.

Dim oSession
Dim oFolder
Dim oMessages
Dim oMsg
Dim oAttachments

Dim strEmailFrom
Dim strEmailBody
Dim strEmailSubject
Dim EmailRecivedDate

Set oSession = CreateObject(“MAPI.Session”)
oSession.Logon “WARDY IT Solutions”, , True, True

Set oFolder = oSession.Inbox
Set oMessages = oFolder.Messages
Set oMsg = oMessages.GetFirst

oSession.DeliverNow

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

MsgBox strEmailFrom
MsgBox strEmailBody
End If
Set oMsg = oMessages.GetNext
Wend

oSession.Logoff
Set oSession = Nothing

Itzik Ben-Gan Australian Sessions

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.

SQL Server SP4 and AWE

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.