SQL Server 2008: Scrip Run only on a Server

In cases you will have to implement from a development to a user environment and do not want to run the scrip on the wrong server environment by using the below script the system will only run on the server you specified. the only draw back is that you cannot use the GO in the script then. but it is pretty useful to ensure you don't run on the wrong environment.



DECLARE @RightServerRun VARCHAR(20) = 'local host'

/***************************************************************************************************************************
CHECKS IF THE SCRIPT IS ON THE RIGHT SERVER, IF SO IT EXECUTES THE WHILE BEGIN...
***************************************************************************************************************************/
IF (SELECT ServerCheck.srvname FROM master.dbo.sysservers ServerCheck WHERE ServerCheck.srvid = 0) != @RightServerRun  
PRINT 'This Script is not running on the Right Server change to ' + @RightServerRun
ELSE
WHILE (SELECT ServerCheck.srvname FROM master.dbo.sysservers ServerCheck WHERE ServerCheck.srvid = 0) = @RightServerRun
 BEGIN
IF (SELECT ServerCheck.srvname FROM master.dbo.sysservers ServerCheck WHERE ServerCheck.srvid = 0) != @RightServerRun  
BREAK
PRINT '------------------------------------------------------------------------------------------------------------';
PRINT 'START:';
/***************************************************************************************************************************
START THE SCRIP TO IMPLEMENT BELOW:
***************************************************************************************************************************/


/***************************************************************************************************************************
ENDS THE WHILE STATEMENT AND RUN
***************************************************************************************************************************/
PRINT '------------------------------------------------------------------------------------------------------------';
PRINT 'END of Script';
IF (SELECT ServerCheck.srvname FROM master.dbo.sysservers ServerCheck WHERE ServerCheck.srvid = 0) = @RightServerRun
SET @RightServerRun = 'END'
END

if you run the SQL statement on the wrong server you will get the following results:

This Script is not running on the Right Server change to local host

Else if it is the right server the results will be as follows:

------------------------------------------------------------------------------------------------------------

START:

------------------------------------------------------------------------------------------------------------

END of Script

SQL Server 2008: Sentence Case Strings or Word Case

In SQL Server there is no function that allows the user to Word or Sentence Case Strings. I came accross this useful code that does that:

Word Case:


CREATE FUNCTION [dbo].[WordCase] ( @InputString varchar(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END
RETURN @OutputString
END
GO

Input:

dbo.WordCase('this is a lower case sentence')

Output:

This Is A Lower Case Sentence

This is very handy when it comes to display for BI applications.

SQL Server 2008: Change Color of Status Bar

When working in big organisation you will run across that you will need to work in different environments (Development / User Acceptance Testing / Production) this leaves you with a problem that sometimes you will not know from one easy look which environment you are working on. This can be solved by the following:

Once you reach the database login click on the options >>

Then Click on the Connection Properties Tab, and then the check box Use custom color, then click the Select... button

Then on the color pallet screen select the desired color to use:

The Recommended Colors are:

Production : RED

UAT:           ORANGE

DEV:           GREEN

Click OK and then CONNECT.

Now when you run a New Query on SQL Server the display will be as follows:

Now you can from one easy look see on what environment you run your queries.