SQL SERVER Date Conversion Format Codes

Ever wonder what the format for all of the date conversion codes is? Where you convert, for instance GETDATE() into a VARCHAR(10). Like if you wanted to display GETDATE() as 20150610 how do you go about it?

Well, here’s some code to find out which code you need to use:

DECLARE @DateFormat TABLE
(
[DateFormat] VARCHAR(50),
FormatNumberUsed INT
)

DECLARE @Type INT = 1;

WHILE @Type < 200
BEGIN

  BEGIN TRY

  INSERT INTO @DateFormat SELECT CONVERT(VARCHAR(50),GETDATE(),@Type), @Type

  END TRY
  BEGIN CATCH

  INSERT INTO @DateFormat SELECT 'NOT VALID', @Type

  END CATCH

  SET @Type = @Type + 1;

END

SELECT TOP 100 *
FROM @DateFormat
WHERE [DateFormat] <> 'NOT VALID'

Obviously you can change the WHERE clause if you want to see all of the invalid codes.

XML FOR PATH with CDATA

Recently I had to put together a result set in XML format. There was only one root node called Products and each product sat under that node. Easy. Let’s do it:

Create some dummy data

DECLARE @xmltable TABLE
(
ProductId INTEGER IDENTITY(1,1),
Name VARCHAR(100) NOT NULL,
[Description] VARCHAR(100) NOT NULL
)

INSERT INTO @xmltable VALUES ('Intoxotil','Best synthetic alcohol there is.'),
('Somnnolax','The only sleep laxative on the market.'),
('Carajax','To help you get the car you want, fast.'),
('Pisciscent','Attractant for partners heavily into fishing'),
('Keithomil','Helping non-Keiths change to Keiths since 1956')


SELECT *
FROM @xmltable

Which returns

Standard stuff agreed. Now let’s return in in XML format:

SELECT *
FROM @xmltable
FOR XML PATH('Product'), ROOT('Products'), ELEMENTS

Which returns the following XML:

<Products>
  <Product>
    <ProductId>1</ProductId>
    <Name>Intoxotil</Name>
    <Description>Best synthetic alcohol there Is.</Description>
  </Product>
  <Product>
    <ProductId>2</ProductId>
    <Name>Somnnolax</Name>
    <Description>The only sleep laxative On the market.</Description>
  </Product>
  <Product>
    <ProductId>3</ProductId>
    <Name>Carajax</Name>
    <Description>To help you Get the car you want, fast.</Description>
  </Product>
  <Product>
    <ProductId>4</ProductId>
    <Name>Pisciscent</Name>
    <Description>Attractant For partners heavily into fishing</Description>
  </Product>
  <Product>
    <ProductId>5</ProductId>
    <Name>Keithomil</Name>
    <Description>Helping non-Keiths change To Keiths since 1956</Description>
  </Product>
</Products>

Cool story bro, right?

The problem was that we needed to return the Description in CDATA tags. Which is where things get more interesting. Basically because (as far as I’m aware) FOR XML PATH doesn’t allow the inclusion of CDATA tags.

The way to resolve this is by using FOR XML EXPLICIT. And the way to do it is below:

SELECT 1       [Tag],
       NULL    [Parent],
       NULL    [Products!1!productid],
       NULL    [Product!2!productid],
       NULL    [Product!2!description!cdata]

UNION ALL

SELECT 2,
       1,
       x.ProductId,
       x.ProductId,
       x.[Description]
FROM @xmltable x
FOR XML EXPLICIT

Which returns us the values we were after including the CDATA tags.

<Products>
  <Product productid="1">
    <description><![CDATA[Best synthetic alcohol there is.]]></description>
  </Product>
  <Product productid="2">
    <description><![CDATA[The only sleep laxative on the market.]]></description>
  </Product>
  <Product productid="3">
    <description><![CDATA[To help you get the car you want, fast.]]></description>
  </Product>
  <Product productid="4">
    <description><![CDATA[Attractant for partners heavily into fishing]]></description>
  </Product>
  <Product productid="5">
    <description><![CDATA[Helping non-Keiths change to Keiths since 1956]]></description>
  </Product>
</Products>

Yes, it’s a bit of a pain having to work around, but still it’s manageable. And I probably spend more time writing this blog post than I did working around the problem…

Connecting to Analysis Services through Excel

Just a quick one with some pretty pictures..

Step 1: Select a nice blank cell in Excel (B4 for instance)

Step 2: From the Data tab on the ribbon, select From Other Sources > From Analysis Services

Step 3: Enter the name of the server you want to connect to

Step 4: Click Next and then select the name of the Cube to which you want to connect from the dropdown

Step 5: Select the location of your new pivot table

Step 6: click OK and Robert’s your Mother’s brother.

Reversing Transactions in MDS

Recently we needed to reverse some transactions in MDS. We found the transactions by going to Version Management > Transactions from the MDS main page. Then we added filters to show the entity that was changed and the member code as per below:

I then thought to myself that MDS has some decent stored procedures (while the rest of it can be a bit of a mess and hard to use, like seriously have you ever tried to edit three tiered models? It’s a nightmare), so why not see if there’s one I could use.. And indeed there was. First go and find the ID of the transaction you want to reverse by querying the mdm.tblTransaction table:

SELECT TOP 100 *
FROM mdm.tblTransaction T
WHERE NewCode = 'De-Activated'

Then query the mdm.tblUsers table to get your ID:

SELECT id, DisplayName
FROM mdm.tblUser
WHERE DisplayName LIKE '%Jim%'

Now you’re ready to run the stored procedure

EXEC [mdm].[udpTransactionReverse]  @User_ID = 40, @Transaction_ID = 7445864

Then when you refresh the Transactions page again you’ll notice that the transaction has been reversed under your username. Enjoy 🙂

Schema Compare not updating target in Visual Studio

This has been a bit of a pain over the last little while until I found a post at the bottom of this thread on Stack Overflow:

http://stackoverflow.com/questions/16968902/cant-update-with-schema-compare

The issue I was having was that I wasn’t including the schema in the update, which was causing tables to not be updated in the target. Obviously once I started including the schemas the updates started coming through! w007.