Friday, March 22, 2013

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 ...

Thursday, March 21, 2013

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 :-)

Tuesday, March 19, 2013

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

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.

DBT-50000 when using DBCA.bat on Windows (Oracle 19.11)

I’ve been having some trouble getting DBCA to run in order to create databases. Thought I’d share it with you, and thus document it for la...