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.