Tuesday, June 19, 2012

2012 MS SQL TSQL Functions

-- Format very useful in formatting results


          FORMAT(1.0, 'C'),

          FORMAT(42, '###,##0.00')

-- turn text into a particular data type, if it can’t be converted then it fails

select parse('05/15/1968' AS datetime2),

              parse('$123.45' AS money)

-- same as parse but if it doesn’t convert it returns null

SELECT try_parse('05/15/1968' AS datetime2),

              try_parse('99/15/1968' AS datetime2)

-- evalues the first parameter, then returns the second if true, or the third if false.

SELECT IIF(1=1, 'true', 'false'),

       IIF(try_parse('99/15/1968' AS datetime2) is NULL, 'Was Null', 'Not Null')

-- indexed based, conversion if 1 then ‘one’, if 2 then ‘two’ etc.

SELECT CHOOSE(1, 'One', 'Two', 'Three')

-- concat function, not sure of usefulness yet maybe in conjunction with format to make some nice strings, but sadly no aggregate version

-- there are a couple funky rules about size of strings so read up before assuming.

SELECT CONCAT('first string', ' Second String')

-- return the end of month calendar date for any given date, can also use an offset, with offset it adds X months to the date then calculates the end of month

SELECT EOMONTH('5/15/1968'), EOMONTH('5/15/1968', 3)

-- create a date object from the individual values.


              DATETIME2FROMPARTS(1968, 5, 15, 4, 0, 30, 0, 0)

Monday, June 11, 2012

SQL 2012 SSRS Sharepoint 2012 Integration Corrupt Data Driven Subscriptions

      We recently upgraded from SQL SSRS 2008 R2 Sharepoint 2010 Integration Mode to  SQL SSRS 2010 Sharepoint 2010 Integration Mode. After making this upgrade we were no longer able to edit any of the Data Driven subscriptions. The reason for this is that SSRS 2010 Data Driven subscriptions require a few additional nodes than 2008 R2. If you edit the subscriptions in the ReportServer.dbo.Subscriptions table you will be able to edit via the SharePoint/ReportServer UI. 

F    Follow the below steps and script.

1.       Find the SubscriptionID
2.       Update the SubscriptionID variable
3.       Run the Subscription update
4.       Edit the Subscription in SharePoint to confirm

Due to formatting I had to upload the script in a file: