sql solace: May 2006

Monday, 29 May 2006

Stored Procedures - Output Parameters

Demonstrates the use of the OUTPUT parameter from a stored procedure -

CREATE PROCEDURE dbo.GetCountByLastName (
@DepartmentName NVARCHAR(50),
@DepartmentCount INT OUTPUT )
AS
select @DepartmentCount = COUNT(*) from HumanResources.EmployeeDepartmentHistory h
inner join HumanResources.Department d
on h.DepartmentID = d.DepartmentID
where d.Name = @DepartmentName and EndDate IS NULL
group by d.Name

Call the procedure like this to fetch the output -

declare @intDepartmentCount int

exec dbo.GetCountByLastName
@DepartmentName = 'Marketing',
@DepartmentCount = @intDepartmentCount OUTPUT

select @intDepartmentCount  as DepartmentCount

Sunday, 28 May 2006

Occasional SQL User?

SQL Server runs as a service. Brilliant on a server, not so brilliant if you just wasn't to do the occasional bit of development on a laptop!

This is where placing a couple of batch files (remember them?) on your desktop can help.
Create two files, say StartSQL.bat and StopSQL.bat and place the following lines in them respectively.

net start mssqlserver

net stop mssqlserver

Then go to Services (located in Control Panel > Administrative Tools) and set SQL Server (MSSQLSERVER) to Manual startup.

Now, SQL wont start on reboot and you turn it on only when you need to.

Wednesday, 24 May 2006

Generic Cursor Template

Generic CURSOR Template -

DECLARE @Variable VARCHAR(200)
DECLARE CURSORNAME CURSOR FOR
SELECT ColumnName FROM Table         
OPEN CURSORNAME
FETCH NEXT FROM CURSORNAME INTO @Variable

WHILE @@FETCH_STATUS = 0
BEGIN

-- DO STUFF HERE !!

FETCH NEXT FROM CURSORNAME INTO @Variable
END

CLOSE CURSORNAME
DEALLOCATE CURSORNAME

Tuesday, 23 May 2006

SQL 2000 : Sending Mail WITHOUT Sql Mail

Creating the procedure -

create procedure [dbo].[CdoSysEMail] 
 (
 @email_from    varchar(200), 
 @email_to      varchar(200), 
 @email_bcc     varchar(200) = '', 
 @email_subject varchar(200) = '', 
 @email_body    varchar(7000) =''
 )
as 

begin
 -- declare variables
 declare @obj_message_id int 
 declare @output int 
 declare @error_output varchar(5000) 
 declare @error_source varchar(255) 
 declare @error_description varchar(1000) 
  
 -- create CDO.Message object 
 exec @output = sp_OACreate 'CDO.Message', @obj_message_id OUT 

 -- set server properties in the object
 exec @output = sp_OASetProperty @obj_message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' 
 exec @output = sp_OASetProperty @obj_message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value','25'
 exec @output = sp_OASetProperty @obj_message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','0'
 exec @output = sp_OASetProperty @obj_message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '193.19.88.36'

 -- save those properties
 exec @output = sp_OAMethod @obj_message_id, 'Configuration.Fields.Update', Null
 
 -- pass the procedure parameters through to the e-mail
 exec @output = sp_OASetProperty @obj_message_id, 'To', @email_to
 exec @output = sp_OASetProperty @obj_message_id, 'Bcc', @email_bcc
 exec @output = sp_OASetProperty @obj_message_id, 'From', @email_from
 exec @output = sp_OASetProperty @obj_message_id, 'Subject', @email_subject
 exec @output = sp_OASetProperty @obj_message_id, 'TextBody', @email_body

 -- send the email
 exec @output = sp_OAMethod @obj_message_id, 'Send', NULL
 
 -- error handling 
 if @output <> 0 
 begin
  declare @No Int
  Set @No=@output
  Select @No
      exec @output = sp_OAGetErrorInfo NULL, @error_source OUT, @error_description OUT
  if @output = 0
   begin
   Set @error_output = 'Error : ' + cast(@No As varchar) + ', Source: ' + @error_source + ', Description: ' + @error_description
   Raiserror(@error_output,16,1)
   end
  else
   begin
      Raiserror('could not determine error (sp_OAGetErrorInfo failed)', 16,1)
      end
 end
 
 -- clean up
 exec @output = sp_OADestroy @obj_message_id
end
go

Testing the procedure -

execute CdoSysEMail 
 @email_from ='sqladmin@mydomain.net',
 @email_to = 'recipient@mydomain.net', 
 @email_subject = 'test, please ignore!', 
 @email_body = 'test message body'

Friday, 19 May 2006

TSQL : Swap Databases

Swap databases code.

Useful for quickly switching content, e.g for websites.

ALTER DATABASE Database1  SET SINGLE_USER with rollback immediate
ALTER DATABASE Database2  SET SINGLE_USER with rollback immediate

EXEC sp_renamedb 'Database1', 'Database3'
EXEC sp_renamedb 'Database2', 'Database1'
EXEC sp_renamedb 'Database3', 'Database2'

ALTER DATABASE Database1  SET MULTI_USER
ALTER DATABASE Database2  SET MULTI_USER

Monday, 15 May 2006

TSQL : Restoring a database to a point in time (Litespeed version)

Sql to restore LiteSpeed backups.

-- restore main bak first

exec master.dbo.xp_restore_database
@database = 'Accounts' ,
@filename = '\\FILESTORE\SQL$\Accounts\Accounts_20060502001507_Friday_LS.bak' ,
@filenumber = 1,
@with = 'NORECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10',
@with = 'REPLACE'
go

-- restore latest diff

exec master.dbo.xp_restore_database
@database = 'Accounts',
@filename = '\\FILESTORE\SQL$\Accounts\Accounts_20060502014913_Thursday.diff',
@filenumber = 1,
@with = 'NORECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10',
@with = 'REPLACE'
go

-- restore transaction log backups
-- repeat for each one, changing NORECOVERY to RECOVERY for the last one.

exec master.dbo.xp_restore_database
@database = 'Accounts',
@filename = '\\FILESTORE\SQL$\Accounts\Accounts_20060502060107_Thursday.trn',
@filenumber = 1,
@with = 'NORECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10',
@with = 'REPLACE'
go

Saturday, 13 May 2006

TSQL : Restoring a database to a point in time

Sql to use once you have identified which backup files need to be restored...

-- restore last full backup
-- use NORECOVERY to state you want to add further backup files

RESTORE DATABASE Sales
FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060423180002_Wednesday_MS.bak'
WITH NORECOVERY

-- add last differential backup
-- use NORECOVERY to state you want to add further backup files

RESTORE DATABASE Sales
FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425010002_Friday.diff'
WITH NORECOVERY
go

-- apply transaction log backups , up until the point you wish to restore to
-- use NORECOVERY to state you want to add further backup files
-- on the final restore, omit WITH NORECOVERY or specify WITH RECOVERY so the db is readable.

RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425060000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425070000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425080000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425090000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425100000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425110000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425120000_Friday_MS.trn'
go

Friday, 12 May 2006

SQL 2005 - Overview

SQL 2005 Management Studio

'Management Studio' is the primary interface for managing SQL 2005 servers and is backwards compatible with older servers.

It acts as the replacement for the main SQL 2000 tools, namely 'Enterprise Manager' , 'Query Analyser' and 'Analysis Manager'.

Management Studio Overview

http://www.yukonxml.com/chapters/apress/ss2005revealed/mgmtstudio/

SSIS - Sql Server Integration Services

In Sql Server 2005, The DTS (Data Transformation Services) functionality has been replaced with SSIS - Sql Server Integration Services.

SSIS is an installable component and provides powerful ETL (extraction, transformation & loading) capability.

SSIS supports automation in the same was that DTS packages could be executed and monitored from within other languages.

To design 'Integration Services' projects you use the Business Intelligence Studio Interface (also used for Analysis Services & Reporting Services components). If you already have Visual Studio 2005 installed, SQL 2005 projects fall within the 'Business Intelligence Projects' project type.

To execute 'Integration Services' projects you use 'SQL Server Management Studio'.

Integration Services Resources

http://www.sqlis.com/

http://www.databasejournal.com/features/mssql/article.php/3503996

Analysis Services 2005

Analysis Services was present on Sql Server 2000.

Like its predecessor, Analysis Services 2005 allows you to create and manage OLAP (Online Analytical Processing) cubes.

Unlike its predecessor, Analysis Services 2005 makes the task a lot easier by combining old Enterprise Manager, Query Analyser and DTS functionality.

'Analysis Services' projects are designed within the Business Intelligence Studio Interface (also used for Integration Services & Reporting Services components). If you already have Visual Studio 2005 installed, SQL 2005 projects fall within the 'Business Intelligence Projects' project type.

Analysis Services Resources

http://www.databasejournal.com/features/mssql/article.php/1429671

SSRS - Sql Server Reporting Services

Originally an add-on to SQL 2000, Sql Server Reporting Services is included in SQL 2005 for free.

It consists of 2 components, 'Report Designer' and 'Report Builder'

Report Designer features a 'drag and drop' interface and will be familiar to developers who have used Crystal Reports or Access.

Report Builder is an adhoc reporting tool which allows users to specify

A unique feature of SSRS (at this time) is that it allows users to subscribe to reports and features the functionality to have these reports emailed out periodically. Licencing for SSRS is free, which should accelerate its take up in the market place.

Reporting Services Resources

http://www.databasejournal.com/features/mssql/article.php/3573361

Thursday, 11 May 2006

ASCII Values Chart

System Characters
decimal	code	meaning

0	NUL	NULL
1	SOH	Start Of Heading
2	STX	Start Of Text
3	ETX	End Of Text
4	EOT	End Of Transmission
5	ENQ	Enquiry,
6	ACK	Acknowledge
7	BEL	Bell
8	BS	Backspace
9	HT	Horizontal Tabulation
10	LF	Line Feed
11	VT	Vertical Tabulation
12	FF	Form Feed
13	CR	Carriage Return
14	SO	Shift Out
15	SI	Shift In
16	DLE	Data Link Escape
17	DC1	Device Control 1,
18	DC2	Device Control 2
19	DC3	Device Control 3,
20	DC4	Device Control 4
21	NAK	Negative Acknowledge
22	SYN	Sychronous Idle
23	ETB	End of Transmission Block
24	CAN	Cancel
25	EM	End of Medium
26	SUB	Substitute
27	ESC	Escape
28	FS	File Separator
29	GS	Group Separator
30	RS	Record Separator
31	US	Unit Separator

Printable Characters

decimal	character	hex	oct

32	(space)	20	40
33	!	21	41
34	"	22	42
34	"	22	42
35	#	23	43
36	$	24	44
37	%	25	45
38	&	26	46
38	&	26	46
39	'	27	47
40	(	28	50
41	)	29	51
42	*	2A	52
43	+	2B	53
44	,	2C	54
45	-	2D	55
46	.	2E	56
47	/	2F	57
48	0	30	60
49	1	31	61
50	2	32	62
51	3	33	63
52	4	34	64
53	5	35	65
54	6	36	66
55	7	37	67
56	8	38	70
57	9	39	71
58	:	3A	72
59	;	3B	73
60	<	3C	74
60	<	3C	74
61	=	3D	75
62	>	3E	76
62	>	3E	76
63	?	3F	77
63	?	3F	77
63	?	3F	77
64	@	40	100
65	A	41	101
66	B	42	102
67	C	43	103
68	D	44	104
69	E	45	105
70	F	46	106
71	G	47	107
72	H	48	110
73	I	49	111
74	J	4A	112
75	K	4B	113
76	L	4C	114
77	M	4D	115
78	N	4E	116
79	O	4F	117
80	P	50	120
81	Q	51	121
82	R	52	122
83	S	53	123
84	T	54	124
85	U	55	125
86	V	56	126
87	W	57	127
88	X	58	130
89	Y	59	131
90	Z	5A	132
91	[	5B	133
92	\	5C	134
93	]	5D	135
94	^	5E	136
95	_	5F	137
96	`	60	140
97	a	61	141
98	b	62	142
99	c	63	143
100	d	64	144
101	e	65	145
102	f	66	146
103	g	67	147
104	h	68	150
105	i	69	151
106	j	6A	152
107	k	6B	153
108	l	6C	154
109	m	6D	155
110	n	6E	156
111	o	6F	157
112	p	70	160
113	q	71	161
114	r	72	162
115	s	73	163
116	t	74	164
117	u	75	165
118	v	76	166
119	w	77	167
120	x	78	170
121	y	79	171
122	z	7A	172
123	{	7B	173
124	\|	7C	174
125	}	7D	175
126	~	7E	176
128	€	80	200

Extended Characters

129		81	201
130	‚	82	202
131	ƒ	83	203
132	„	84	204
133	…	85	205
134	†	86	206
135	‡	87	207
136	ˆ	88	210
137	‰	89	211
138	Š	8A	212
139	‹	8B	213
140	Œ	8C	214
141		8D	215
142	Ž	8E	216
143		8F	217
144		90	220
145	‘	91	221
146	’	92	222
147	“	93	223
148	”	94	224
149	•	95	225
150	–	96	226
151	—	97	227
152	˜	98	230
153	™	99	231
154	š	9A	232
155	›	9B	233
156	œ	9C	234
157		9D	235
158	ž	9E	236
159	Ÿ	9F	237
161	¡	A1	241
162	¢	A2	242
163	£	A3	243
164	¤	A4	244
165	¥	A5	245
166	¦	A6	246
167	§	A7	247
168	¨	A8	250
169	©	A9	251
169	©	A9	251
170	ª	AA	252
171	«	AB	253
172	¬	AC	254
173		AD	255
174	®	AE	256
174	®	AE	256
175	¯	AF	257
176	°	B0	260
177	±	B1	261
178	²	B2	262
179	³	B3	263
180	´	B4	264
181	µ	B5	265
182	¶	B6	266
183	·	B7	267
184	¸	B8	270
185	¹	B9	271
186	º	BA	272
187	»	BB	273
188	¼	BC	274
189	½	BD	275
190	¾	BE	276
191	¿	BF	277
192	À	C0	300
192	À	C0	300
193	Á	C1	301
193	Á	C1	301
194	Â	C2	302
194	Â	C2	302
195	Ã	C3	303
195	Ã	C3	303
196	Ä	C4	304
196	Ä	C4	304
197	Å	C5	305
197	Å	C5	305
198	Æ	C6	306
198	Æ	C6	306
199	Ç	C7	307
199	Ç	C7	307
200	È	C8	310
200	È	C8	310
201	É	C9	311
201	É	C9	311
202	Ê	CA	312
203	Ë	CB	313
203	Ë	CB	313
204	Ì	CC	314
204	Ì	CC	314
205	Í	CD	315
205	Í	CD	315
206	Î	CE	316
206	Î	CE	316
207	Ï	CF	317
207	Ï	CF	317
208	Ð	D0	320
208	Ð	D0	320
209	Ñ	D1	321
209	Ñ	D1	321
210	Ò	D2	322
211	Ó	D3	323
212	Ô	D4	324
213	Õ	D5	325
213	Õ	D5	325
214	Ö	D6	326
214	Ö	D6	326
215	×	D7	327
216	Ø	D8	330
216	Ø	D8	330
217	Ù	D9	331
217	Ù	D9	331
218	Ú	DA	332
218	Ú	DA	332
219	Û	DB	333
219	Û	DB	333
220	Ü	DC	334
220	Ü	DC	334
221	Ý	DD	335
221	Ý	DD	335
222	Þ	DE	336
222	Þ	DE	336
223	ß	DF	337
223	ß	DF	337
224	à	E0	340
224	à	E0	340
225	á	E1	341
225	á	E1	341
226	â	E2	342
227	ã	E3	343
228	ä	E4	344
229	å	E5	345
229	å	E5	345
230	æ	E6	346
230	æ	E6	346
231	ç	E7	347
231	ç	E7	347
232	è	E8	350
232	è	E8	350
233	é	E9	351
233	é	E9	351
234	ê	EA	352
234	ê	EA	352
235	ë	EB	353
235	ë	EB	353
236	ì	EC	354
236	ì	EC	354
237	í	ED	355
237	í	ED	355
238	î	EE	356
238	î	EE	356
239	ï	EF	357
239	ï	EF	357
240	ð	F0	360
240	ð	F0	360
241	ñ	F1	361
241	ñ	F1	361
242	ò	F2	362
242	ò	F2	362
243	ó	F3	363
243	ó	F3	363
244	ô	F4	364
244	ô	F4	364
245	õ	F5	365
245	õ	F5	365
246	ö	F6	366
246	ö	F6	366
247	÷	F7	367
248	ø	F8	370
248	ø	F8	370
249	ù	F9	371
249	ù	F9	371
250	ú	FA	372
250	ú	FA	372
251	û	FB	373
251	û	FB	373
252	ü	FC	374
253	ý	FD	375
253	ý	FD	375
254	þ	FE	376
254	þ	FE	376
255	ÿ	FF	377

Backup Stored Procedures

Script from a colleague.
This could be scheduled to backup code.
Particularly useful in development environments -


CREATE PROCEDURE dbo.StoredProcBackup_sp

@DatabaseName as varchar(200) = 'DATABASENAME IN HERE',
@DestinationDir as varchar(200) = 'D:\SQLDATA\MSSQL\BACKUP\StoredProcedures\AND THE REST OF YOUR PATH',
@User as varchar(20)= 'USER',
@Password as varchar(20) = 'PASSWORD'
AS

set nocount on

declare @dircreatecmd as varchar(210)
declare @Error int
declare @RootDir varchar(210)
declare @RootDay varchar(215)
declare @CheckDircmd varchar(300)

set @CheckDircmd = 'dir ' + @DestinationDir
EXEC @Error =  master..XP_CMDSHELL @CheckDircmd
If @Error = 1
 Begin
 --Directory does not exist so create it
 set @RootDir =  @DestinationDir
 set @dircreatecmd = 'mkdir ' + @RootDir
 EXEC master..XP_CMDSHELL @dircreatecmd
 End 
Declare @Day varchar(2)
Declare @Month varchar(2)
set @Day = convert(varchar(2),DATEPART(dd,getDate()))
set @Month = convert(varchar(2),DATEPART(mm,getDate()))

set @RootDay = @DestinationDir +'\'+ @Month + '\' + @Day + '\' 
declare @CheckRootDaycmd varchar(320)

set @CheckRootDaycmd = 'Dir ' + @RootDay
EXEC @Error =  master..XP_CMDSHELL @CheckRootDaycmd
If @Error = 1
 Begin
 --Directory does not exist so create it
 set @RootDir =  @RootDay
 set @dircreatecmd = 'mkdir ' + @RootDir
 EXEC master..XP_CMDSHELL @dircreatecmd
 End 

Declare @ProcName varchar(100)
Declare @ProcText varchar(8000)
Declare @filename varchar(104)
Declare @FilePath varchar(310)
DECLARE @cmd varchar (8000), @var varchar (8000)

-- backup all stored procs into single file.
EXEC master..xp_cmdshell 'osql -U?? -P?????? -Q"SELECT rtrim(ltrim(text))  FROM DATABASENAME.dbo.sysobjects so join DATABASENAME.dbo.syscomments sc on so.id = sc.id where type = ''p''" -dDATABASE -oD:\SQLDATA\MSSQL\BACKUP\StoredProcedures\REST OF YOUR PATH\AllStoredProcs_CURRENT.txt'


Declare BackupTables_cur cursor for

-- select all the stored procedures from the given database

SELECT name, text FROM DATABASENAME.dbo.sysobjects  so,  DATABASENAME.dbo.syscomments sc
where so.id = sc.id
and type = 'p'

Open BackupTables_cur

Fetch Next from BackupTables_cur into @ProcName, @ProcText
While @@Fetch_Status = 0
Begin
 Print @ProcName
 Print  @ProcText
 Print @Day
 Set @filename = @ProcName + '.txt'
 set @FilePath = @DestinationDir +'\'+ @Month + '\' + @Day + '\' + @filename

  
 DECLARE @FS int, @OLEResult int, @FileID int
 
 
 EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
 IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
 
 --Open a file
 execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FilePath, 8, 1
 IF @OLEResult <> 0 PRINT 'OpenTextFile'
 
 --Write Text1
 execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @ProcText
 IF @OLEResult <> 0 PRINT 'WriteLine'
 
 EXECUTE @OLEResult = sp_OADestroy @FileID
 EXECUTE @OLEResult = sp_OADestroy @FS

 Fetch Next from BackupTables_cur into @ProcName, @ProcText
End
Close BackupTables_cur
Deallocate BackupTables_cur

set nocount off
GO

Wednesday, 10 May 2006

UltraEdit Macro - Delete all blank lines

Ultraedit

Top
TrimTrailingSpaces
Loop
Find "^p^p"
Replace All "^p"
IfNotFound
ExitLoop
EndIf
EndLoop

Tuesday, 9 May 2006

UltraEdit Macro - Delete after comma

Macro for UltraEdit
Deletes everything after the final comma ',' on all lines

InsertMode
ColumnModeOff
Key HOME
HexOff
UnixReOff
Loop 
IfEof
ExitMacro
Else
Key END
Find Up ","
StartSelect
Key END
Key BACKSPACE
EndSelect
Key DOWN ARROW
EndLoop

Sunday, 7 May 2006

Backup Batch File

Schedule weekly to hold 5 weeks of history of important folders >

rd /s/q u:\backup\5

ren u:\backup\4 5
ren u:\backup\3 4
ren u:\backup\2 3
ren u:\backup\1 2
md u:\backup\1
xcopy c:\my documents\*.* u:\backup\1\*.* /r/v/y/s

Thursday, 4 May 2006

Exchange 2003 : Exmerge Permissions Problem

Solution that worked for me (14/03/03) >

You should have to give your account permission to enter all messages in all data stores.

The access to user mailboxes is controlled by two permissions Send As / Receive As , you can grant your account those rights in the Exchange manager then you will be able to access all mailboxes.

To do it open the Exchange system manager locate you server on the permission tab remove the inherited permission select copy and remove the deny Send As / Receive As from the groups you are member of.
These are domain admins and enterprise admins grant your account those two permissions.

Or give the permission at the organization level >

Add the following registry key

HKEY_CURRENT_USER\Software\Microsoft\Exchange\EXAdmin New dword 'ShowSecurityPage' value=00000001
After you add this key you will find the permission page on all objects.
In the level you want (you can do it for all the organization)
Remove the deny from the send as and receive as permissions from the groups you are member of the default is deny to the domain admins and enterprise admins )
Grant yourself those two permissions.

Exchange 2003 : Compressing Mailbox Store

Overview :

Over time, the mailbox store grows and also becomes fragmented with the constant addition and deletion of mailboxes and messages. After clearing out old user mailboxes and deleting large volumes of messages, the data store file needs some housekeeping i.e. compacting and reordering.

Notes :

Perform the following steps when no users are using the mail server (preferably out of hours).

Solution :

1 Ensure you have up to date backups of mail data.

2 Disable the NIC (network interface card) by right clicking it's icon in the task bar (labelled Local Area Connection') and selecting 'disable'.

3 Stop all the Exchange services. These are easily identified as they all start with 'Microsoft Exchange'. To control services, go to 'Control Panel' , 'Administrative Tools' and then 'Services'.

4 Start a DOS session by selecting Start > Run , then typing 'cmd' .

5 Change to drive c: by typing 'c:'

6 Change the working directory to the exchange application directory by typing 'cd program files\exchsrvr\bin' .

7 Assuming the location of the message store files has not changed, type the following to compact the mailstore.
eseutil /d /o 'c:\program files\exchsrvr\mdbdata\priv1.edb'

This task may take a while depending on the volume of work it has to do.
The following test is taken from a screenshot when the task completes.

C:\Program Files\Exchsrvr\BIN>eseutil /d /o 'c:\program files\exchsrvr\mdbdata\priv1.edb'
Initiating DEFRAGMENTATION mode...
Database: c:\program files\exchsrvr\mdbdata\priv1.edb
Streaming File: c:\program files\exchsrvr\mdbdata\priv1.STM
Temp. Database: TEMPDFRG5160.EDB
Temp. Streaming File: TEMPDFRG5160.STM
Defragmentation Status (% complete) 0 10 20 30 40 50 60 70 80 90 100
|----|----|----|----|----|----|----|----|----|----| ...................................................
Moving 'TEMPDFRG5160.EDB' to 'c:\program files\exchsrvr\mdbdata\priv1.edb'... DONE!
Moving 'TEMPDFRG5160.STM' to 'c:\program files\exchsrvr\mdbdata\priv1.stm'... DONE!

Note:
It is recommended that you immediately perform a full backup of this database.
If you restore a backup made before the defragmentation, the database will be rolled back to the state it was in at the time of that backup.
Operation completed successfully in 369.938 seconds.

C:\Program Files\Exchsrvr\BIN>

To complete the task, restart the server. This will re-establish all the exchange services on startup.
The server should be rebooted periodically anyway hence this is an ideal opportunity.

Tuesday, 2 May 2006

SQL 101 : Network Debugging

Testing Connectivity	ping / tracert / pathping
Establish session on Port number	telnet
Server Name Lookup	NSLookup
View / Configure IP Address	ipconfig