Type an Ascii character in Windows, Alt Codes, Useful Links

If you're like me, you have a fairly limitted selection of special characters that you want to insert into your documents or messages on a regular basis.

There are several ways to do this, as outlined here:
http://www.timeatlas.com/5_minute_tips/general/making_cents_of_special_characters#.UV7QyFeS8hV

I've taken to using alt-codes for the ones I use most frequently.  Memorizing a few of these can be a big time-saver.  Good list here:
http://www.alt-codes.net/

Some of the ones I find most useful:
ALT-3        ♥
ALT- 14     ♫
ALT - 126   ~
ALT - 236   ∞
ALT - 241   ±
ALT - 242   ≥
ALT - 243   ≤
ALT - 0162 ¢  (Not in the linked page - look here: http://symbolcodes.tlt.psu.edu/accents/codealt.html)
ALT - 0169 © (Not in the linked page - look here: http://symbolcodes.tlt.psu.edu/accents/codealt.html)
ALT - 0174 ® (Not in the linked page - look here: http://symbolcodes.tlt.psu.edu/accents/codealt.html)

Those who write Spanish will find these useful:
¿  ALT-168
¡  ALT-173

There are also some accented letters you can check into, found here:
http://symbolcodes.tlt.psu.edu/bylanguage/spanish.html

Finding large files and directories on Windows

One of the most useful tools I've found for troubleshooting full drive issues is WinDirStat.

Since it seems that it's being bundled with all sorts of yuck-ware by all sorts of people, I thought I'd post the sourceforge link to the software.

http://sourceforge.net/projects/windirstat/files/windirstat/1.1.2%20installer%20re-release%20%28more%20languages%21%29/windirstat1_1_2_setup.exe/download?use_mirror=iweb

It gives a nice sort of heat map of where your large files and directories reside.  Very good tool, and much faster than the jdiskreport that I previously recommended from JGoodies, although I will say that is also a very worthy tool.  We have some systems that don't have java installed, though, and this tool is faster and doesn't have the java requirement.

Detecting Transaction Isolation Level through Profiler Trace and other means on MS SQL Server 2005

I doubt that this comes up often, but I find myself in a bit of an argument about what isolation level will be used for transactions in our SQL Server database when the transaction is initiated by WebSphere.  There are various documents that indicate how WebSphere can over ride this value.

I decided to dig into this a bit further.

TRACE:

In doing so, I found that I could monitor our SQL Server DB to determine this.

I have only found two places where the transaction isolation level is appearing:
1 - Sessions:Existing Connection.Text Data
2 - In a deadlock trace as part of the XML output.

If anyone can find it elsewhere, I'd love to hear from you.  However, this makes sense, since this is more-or-less controlled at the session level.

QUERY:
If found that 

DBCC USEROPTIONS

would give me the *default* TIL for the db, but that if that TIL was altered by the session, it would not reflect that.

To get the TIL for *your session*, you can run the following query:

SELECT CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'Readcomitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL FROM sys.dm_exec_sessions

Credit to StackOverflow question and answer here: http://stackoverflow.com/questions/1038113/how-to-find-current-transaction-level 

Observations on translating stored procedures from Oracle <-> DB2 <-> MS SQL Server

I've spent the vast majority of the last 3-4 weeks translating stored procedures from Oracle to DB2, Oracle to SQL Server, and back again, etc. etc. etc. and so on and so forth ;-)

I have a couple of observations:
1 - Oracle PL/SQL is a very clean and easy to read language.
2 - MS SQL Server's T/SQL is powerful and modern and easy
3 - DB2's PL/SQL is, well, my grandmother always taught me that if I didn't have anything nice to say I should just not say anything at all.

I'll post some more useful notes in the coming days.  For now, I wanted to say, very clearly, that I have spent an inordinate amount of my time on the DB2 portions of this.  Sort of sad.  I fear IBM is neglecting this flagship product ...

Altering schema for all tables in MS SQL Server 2005

I ran into a situation today where I needed to alter all tables for an MS SQL Server 2005 database.
A quick Google revealed this post from Ruslan Trifonov.

exec sp_MSforeachtable "ALTER SCHEMA new_schema TRANSFER ? PRINT '? modified' "

Excellent :-)  That was easy ...

Reference to ALTER SCHEMA command is here.
In it's simplest form, the command appears to take this shape: ALTER SCHEMA HumanResources TRANSFER Person.Address;

For those who aren't familiar with the '?' in T/SQL, it's represents a parameter, so this statement is parameterized.  Essentially, sp_MSforeachtable is going to pass the name of each table to statement/query.

Great stuff. Big thanks to Ruslan. That was easy :-)

Email your SQL Server Administrators regarding the last backups for databases

The following script will send a notification that tells when the last database backup was performed.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Sqlalerts',
@recipients = 'first.last@somoecompany.com',
@copy_recipients ='first.last@somoecompany.com',
@query ='exec msdb.dbo.uspMonitorbackups',
@query_result_separator=' ',
@subject = 'DB's w/o backups for last 10 days on SS Instance Name',
@attach_query_result_as_file = 0,
@body= 'Database without backup from last 10 days',
@body_format='text',
@query_result_width = 1000,
@append_query_error = 1,
@query_result_no_padding = 1;

Most of these parameters are self-explanatory.  I will say that profile_name = the profile FROM which the email will be sent.  This ends up in the "FROM" line of the email.

sp_send_dbmail documentation can be found here: http://msdn.microsoft.com/en-us/library/ms190307.aspx 

The contents of uspMonitorbackups is as follows:

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[uspMonitorbackups]    Script Date: 03/19/2013 16:51:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  Create procedure [dbo].[uspMonitorbackups]
  as
   SELECT  sd.name,
        bs.TYPE,
        bs.database_name,
        max(bs.backup_start_date) as last_backup,
        CONVERT(varchar(30),DATABASEPROPERTYEX(sd.name,'Status')) as [Database Status]
FROM    master..sysdatabases sd
        Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
        left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE     bs.type = 'D'
Group by sd.name,
        bs.TYPE,
        bs.database_name
HAVING (MAX(bs.backup_start_date) < DATEADD(dd,-10,GETDATE()))

Issues with AIX newline characters on KSH files developed on Windows

To fix issues with the ^M from developing a .KSH file on windows and then uploaded to AIX:

1 - edit the file in vi (extremly useful vi cheatsheet found here: Vi Cheatsheet from LagMonster)

2 - do a substitution using the following keystrokes: :%s/[ctrlkey+v and ctrl-key+M]//g

This will appear as ...  :%s/^M//g … when you type it.

3 - save with :x

Reference: http://thedaneshproject.com/posts/how-to-remove-m-character-with-vi/

However, note that the command does not look like he says it does.  He reports the ctrl-v as printing this: :%s/^V^M//g.  For me, it did this: :%s/^M//g.