Friday, June 29, 2007

Guest Tips 3: SQL Server reindex

And finally..

Tip #3

This neat litle bit of code rebuilds all indexes on all tables.
sp_MSforeachtable is a System Stored Procedure (as you can see from the sp_ prefix) that performs an action for every user table in the database.

exec sp_MSforeachtable 'dbcc dbreindex (''?'')'

Guest Tips 2:SQL Server row count

Tip #2

Here's a little bit of script to count all rows on all tables.
This uses two very useful features of SQL Server sp_MSforeachtable and temporary tables (designated with a #).
sp_MSforeachtable is a System Stored Procedure (as you can see from the sp_ prefix) that performs an action for every user table in the database.
Any table whose name begins with # is local and temporary. This means it is only visible to the current session and will be dropped when the session ends.

create table #temp_tables
nr_of_rows numeric (15,4),
table_name varchar(40),
exec sp_MSforeachtable 'insert into #temp_tables select count(*) as nr_of_rows, ''?'' as table_name from ?'
select * from #temp_tables
drop #temp_tables

Guest Tips 1: Outlook Extension blocking

A couple of Guest tips. 3 actually, these from a friend of mine, whose
can be found here.

So without further Ado
Tip #1
Here is a little registry hack to allow .exe and .zip attachments in Outlook
(if you really have to accept them, which the folks at Microsoft think
you don't...)

Please make sure you have backed up your registry first.
If you don't know how - please stop now!

Using regedit or regedit32, or some other tool to edit the registry, browse to
the following key:

Create a new String value called Level1remove and set the value = "exe;zip"

The reason this works:

Microsoft have built in two levels of attachment "protection" in Outlook.
Level 1 (these files cannot be received) which consists of a whole load of
dangerous file types; and Level 2 (these files must be downloaded to disk
before opening) which has no extensions by default and is user configurable.

Quite sensibly someone in Microsoft decided that to allow users to remove items
from Level 1 would be silly. Obviously users are "clickers" and want to be able
to receive everything and click on the virus payload if they so wish!

So, failsafes were built in:
Extensions on the Level 1 list will always be considered a threat
(no matter what action is taken by user or administrator).

Level 1 cannot be modified through the front end.

A system administrator can (if (s)he knows how) demote Level 1 "threats" to
Level 2 using the above registry key. This means that users will then be able to
receive the attachments but will still not be able to just "click" them open. The
file must be saved to disk first. Any user who will go to the trouble of saving
the attachment and opening it deserves what they get if it really is a threat.

You can include any extension in this string
(as long as it is on Microsoft's Level 1 restrict list obviously).

Wednesday, June 13, 2007

Gmail tip

Like most people I subscribe to a bunch of sites which send me email updates fairly regularly. For example I have several google alerts keyword searches and each one produces a separate email everyday. This is what I like to think of as 'background' mail, I don't NEED to check any of it.

So when I went on holidays for a couple of weeks recently, I had a lot of this 'background mail' built up of say 100 emails there is more than half of it I'm not interested in trawling through. I just want to mark them as read so only the items I really need to pay attention to are marked unread. "Why don't you just ignore them" you say? , leaving unread emails in my inbox would not sit well with me.

But all is not lost with a bit of forethought its easy to quickly mark all those messages as read.
Enter Gmail's filter functionality, you've probably seen it , its the filters tab under the settings menu, so without further ado, the details.

Create a new filter.

In the to: field enter the address from which the emails you wish to ignore orginate from. if you have more than one address you wish to ignore you need to use the OR operator (many useful gmail operators here)

you can also use partial addresses (if for example you wish to capture all emails from a particular domain just use @domainname)

a complete example here:

you can then hit "test search" to see what messages in your Inbox this pattern matches.
once your happy click next for the filter action

Filter Rule
A simple solution is to choose the filter so these messages skip the Inbox ( you can find them under 'All Mail' or by the label if it is given one, the messages are still marked as unread.

This isn't ideal for me; most of the time I do want to see those messages as they arrive in my inbox, so instead of applying this I simply apply a label such as 'Background Mail' (see image)

Cleaning Your Inbox

So now when I return from holidays and I see 100 emails. Instead of manually selecting the individual messages I'm not interested in reading (right now) and choosing the 'mark as read action'

I simply click the 'Background Mail' label from the labels list which shows all messages with that label (and only messages with the label).

I choose 'ALL' from the Select options .
Then in the Action drop down list I click mark as read.

now when I return to my inbox I can see exactly what needs reading!