Color Coding Status Bar in Microsoft SQL Server Management Studio

This is one of those things you learn about when you make a change on the wrong server, well at least that is how I learned about it and why I share it when someone else makes the same mistake.

You can change the color of the status bar in Microsoft SQL Server Management Studio for every connection you make to help identify the server you are on.

The first step is to launch the Connect To Server option.

Then click on the "Options >>" button.

Then check the "Use custom color" check box then the "Select" button to choose a color.

I usually use Green for Development, Blue for Test and Red for Production.

Hope this helps someone before they make a mistake on the wrong server, but sometimes we have to learn the hard way.

SQL and XML

I was trying to figure out an issue with the new project I took over, and found some ColdFusion code that was throwing some errors trying to parse XML that came from a Microsoft SQL Server stored procedure. 

The stored procedure was using the FOR XML RAW to output XML instead of the query output that is normally returned. I have not used this before so I am unaware of any limitations that may be involved. The error was being caused by the result set being truncated and failing the reading of the XML result.

Here is an example of the SQL I got from Microsoft TechNet

USE AdventureWorks2012 

GO 

SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status 

FROM Sales.Customer Cust 

INNER JOIN Sales.SalesOrderHeader OrderHeader ON Cust.CustomerID = OrderHeader.CustomerID 

FOR XML RAW

The results that were being returned was rather large but I am unsure why that would be an issue. I did read about some truncation but that was blamed on the SQL Server Management Studio and its limitations on the size of the result set it could return.

The error I was receiving was being generated by ColdFusion so the SQL Server Management Studio limitations should not apply. I did solve my issue by using the XML data type that is available in Microsoft SQL Server 2005.

USE AdventureWorks2012 

GO 

DECLARE @xmlResult XML;

SET @xmlResult = (

SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status 

FROM Sales.Customer Cust 

INNER JOIN Sales.SalesOrderHeader OrderHeader ON Cust.CustomerID = OrderHeader.CustomerID 

FOR XML RAW)

SELECT @xmlResult

I have not run the above query as I am using a different table but the idea is there. I understand I could probably do something else but my knowledge of SQL is limited to the basic stuff, this works for me so I am using it until I discover a better solution.

Starting Over

I have neglected this blog for so long, things that seemed important or interesting at the time change as we grow older.

The one constant is my job I am a ColdFusion Developer and have been for a long time. So I decided to turn this blog into a place to share some interesting things I figure out as a developer that can maybe help out someone else and to help me keep track of them.