Posts
10
Comments
14
Trackbacks
0
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

GO

USE [MaasSql]
GO
/****** 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'))
    BEGIN
        DROP FUNCTION cCode.MilliSeconds_ToDetail
    END

GO

CREATE FUNCTION cCode.MilliSeconds_ToDetail ( @Milliseconds BIGINT )
RETURNS @Results TABLE
    (
      ID INTEGER IDENTITY(1 , 1)
                 PRIMARY KEY
    , milliseconds BIGINT
    , Seconds REAL
    , Minutes REAL
    , Hours REAL
    , Days REAL
    , Weeks REAL
    )
AS 
BEGIN

    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 ;

    INSERT INTO
        @Results
        (
          milliseconds
        , Seconds
        , Minutes
        , Hours
        , Days
        , Weeks

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

    RETURN

END

GO
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. http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1613.

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: http://sqljunkies.com/Forums/ShowPost.aspx?PostID=11222

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.

http://msdn2.microsoft.com/en-US/library/aa936769(SQL.80).aspx

http://www.ureader.com/message/1200664.aspx

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. http://blogs.msdn.com/euanga/archive/2006/07/11/662053.aspx

This is the “official” kb article:

http://support.microsoft.com/kb/555686

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