Monday, February 25, 2008
Milliseconds to Detail
I use this function for testing. It takes in milliseconds and spits out the whole number value in various other rulers (seconds, minutes, etc).
USE MaasSql


USE [MaasSql]
/****** Object:  UserDefinedFunction [cCode].[DateTime_GetUtcOffset]    Script Date: 02/25/2008 07:01:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cCode].[MilliSeconds_ToDetail]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
        DROP FUNCTION cCode.MilliSeconds_ToDetail


CREATE FUNCTION cCode.MilliSeconds_ToDetail ( @Milliseconds BIGINT )
                 PRIMARY KEY
    , milliseconds BIGINT
    , Seconds REAL
    , Minutes REAL
    , Hours REAL
    , Days REAL
    , Weeks REAL

    DECLARE @MilliSecondsREAL AS REAL ;

    SET @MilliSecondsREAL = @Milliseconds ;

    DECLARE @SecondsDivisor AS REAL ;

    SET @SecondsDivisor = 1000 ;

    DECLARE @MinutesDivisor AS REAL ;

    SET @MinutesDivisor = @SecondsDivisor * 60 ;

    DECLARE @HoursDivisor AS REAL ;

    SET @HoursDivisor = @MinutesDivisor * 60 ;

    DECLARE @DaysDivisor AS REAL ;

    SET @DaysDivisor = @HoursDivisor * 24 ;

    DECLARE @WeeksDivisor AS REAL ;

    SET @WeeksDivisor = @DaysDivisor * 7 ;

        , Seconds
        , Minutes
        , Hours
        , Days
        , Weeks

        ,   @MilliSecondsREAL / @SecondsDivisor seconds
        ,   @MilliSecondsREAL / @MinutesDivisor minutes
        ,   @MilliSecondsREAL / @HoursDivisor hours
        ,   @MilliSecondsREAL / @DaysDivisor days
        ,   @MilliSecondsREAL / @WeeksDivisor weeks



posted @ Monday, February 25, 2008 1:03 PM | Feedback (0)
DTS Script out - SQL Server 2000

My preferred method of persisting a DTS package is by saving it to the server and backing up MSDB. At one of my employers, I found many filepaths and other should be variables hardcoded in. Not one week passed before someone said, what’s making this file? To find the creator of the file, I needed a way to GREP their DTS packages. I found this neat article on SQL Server Central.

I run it as a .vbs file, and start an instance of it for every server, using a quick and easy batch file such as:
“\\Fileserver-4\DTS\Script Out DTS Packages-CmdArgs.vbs” /server:SQLSVR2 /backupfolder:”\\Fileserver\DTS”

“\\Fileserver-4\DTS\Script Out DTS Packages-CmdArgs.vbs” /server:SQLSVR2 /backupfolder:”\\Fileserver\DTS”

One possible use is for easy change control via a text file diffing.


posted @ Monday, February 25, 2008 12:55 PM | Feedback (0)
Cleaning a Phone Number with a Function

Not much more to say other than, taking the resources - CPU, Memory, Time to loop through the characters in a phone number and retrieving all #’s is better than crap. Don’t blame me, blame the designer who allowed a phone number to be stored as varchar with no constraints.

Oh, and maybe since we’re talking varchar, you can’t get crazy and replace | with 1 and Oo with 0 and all that other stuff we DBA’s LOVE to do.  Crap is crap.

When I looked on the web, I found this post and liked the last posted script the best:

posted @ Monday, February 25, 2008 12:54 PM | Feedback (2)
DTS Logging - SQL Server 2000

It’s been a while since I used DTS.  I was trying to remember the right way to do logging in DTS.  First, I just said - hey, I’ll do a File System Object File.  Then, I said to myself, no way, that’s bogus, there has got to be an easier way……  So I found this:DTSPackageLog.  What’s funny is that the DTSPackageLog may or may not be available.  You have to test it for null or nothing prior to attempting to use it.  I soo hate that crap.  It makes no sense to me.  I used to feel bad about not understanding these sorts of ideas, until a good friend told me to take it easy on myself.  I think what he said was - “It’s not you, it just doesn’t make sense.”   Long story short, DTSPackageLog = sounds neat, but long term plan = fso writing to a reliable ( existing ) temp folder.

posted @ Monday, February 25, 2008 12:54 PM | Feedback (0)
Sql Server Management Studio Slow

I really dug this explanation of the “slowness” in Management Studio.

This is the “official” kb article:

posted @ Monday, February 25, 2008 12:53 PM | Feedback (0)