Surface Pro 3 for a DBA a ture laptop/Desktop/iPad replacement

285_surface

SQL PASS 285 Tech Tattoo on Surface Pro 3

IMG_2211

Microsoft Store in Lenox Square Mall in Atlanta

Setting up in August 2014

Setting up in August 2014

Back in August 2014 I decided to get a Microsoft Surface Pro 3 and mother was in need of a new laptop as well so we went together to investigate at the Microsoft Store in the Lenox Square Mall in Atlanta. Needless to say we pulled the trigger and made the purchases. Thanks Mom! ūüôā We both¬†went with the Surface Pro 3¬†– 256GB / Intel i5 to get the¬†version with 8GB RAM. I felt like I wanted the extra RAM for SQL Server and I’m glad I did. Over the past months this machine has been my laptop and tablet; I have still been using the iPhone for phone/email. I have added a few links below to help with the over experience be easier for other DBAs. I had no problem the first evening installing¬†Microsoft SSMS 2014 and Visual studio 2012 from my ISO files. I also went ahead and added¬†Red-Gate developer tools and SSMS Tools Pack. The installations went super fast with the SSD onboard. If you are hoping that the Surface is going to satisfy your need for apps; think again. Microsoft is lacking the apps in the marketstore. The big ones are there but I use the IE web for mostly everything and if you can install your everyday software.

At home I had an exisiting KVM IOGear Extreme 4 port KVM which got me the keyboard and mice working right away.

Before SQL PASS Summit 2014 I had to purchase the Plugable UD-3000 Universal Docking Station USB 3.0 to get the NIC and docking station feature for my PreCon on Big Data. In December had a small issue with the Intel drivers and the Plugable 3.0 to resolve check out: Plugable Blog

3

Surface Pro 3 and Plugable UD-3000 Universal Docking Station USB 3.0

2

IO Gear KVM and Second Monitor to Surface Pro 3

I also had a small issue with the WiFi not working automatically connecting after I enable the Hyper-V.¬†Hyper-V functionality in Windows 8.1 doesn’t play nice with Surface Pro 3’s Connected Standby.¬†Article:¬†Surface Pro 3 Tip: Hyper-V vs. Connected Standby. So I have some saved batch files to enable and disable WiFi and¬†Hyper-V.

Surafce SSMS

Magnets in this Cover connect with magnets in Surface Pro 3 to add stability when you’re typing on your lap. Like Type Cover 2, this Cover has backlit keys, with keys on the left side of the top row of keys to control keyboard brightness.

Screenshot

SSMS Screenshot from Surface Pro 3

Some useful links I used to make my purchase decision:

The Surface Pro 3 is a very good Windows 8.1 device I would recommend it to any DBA for home and business use. I’m also excited to see it working with Windows 10¬†Tech Preview.

Advertisements

SQL PASS Summit 2014 Checklist

This year we are headed back to Seattle, WA. First time for me. New job and new goals to be focused on at this year SQL Summit. I’m so ready to be with the #sqlfamily and discover the coastal seaport city of Seattle. This year my wife Stacy will be my guest and attend the evening events. After the conference her and I¬†are getting some R&R at a B&B for our¬†anniversary. A few extra days on the pacific coast will be good for us.

Be ready to be physical and mentally drained and mind blow at the same time. Its the best way I can describe the summit. I enjoyed it some much last year; I’m going back. I didn’t giving my employers an option. Told everyone at the office this is a conference for me and is hands down the best event for SQL DBAs.

So what do I need to do before SQL PASS Summit 2014:

Registration

  • Pre-Con
  • Full Summit
  • 1 Day

Travel plans

  1. Transport / Accommodation
  2. It would also be worthwhile to start planning on how you’d want to get to Washington State Convention Center, i.e. by car or public transport, etc.
  3. Weather in November

Review Sessions

  1. Use the schedule builder to put together a list of sessions to go to
  2. Guidebook App

Review Local Attractions

  1. Trip Advisor

Check out the networking events ,#sqlrun, #sqlwalk and SQL Karaoke.

Stay Connected on Social Media (What to know whats going on? Get on Twitter!)

  1. #sqlpass
  2. #summit14
  3. #SQLFamily

Things to take with:

  1. Mobile devices (Chargers)
  2. Bag to carry brochures and etc.
  3. Walking, Running, and Drinking shoes
  4. Have your business cards ready
  5. Water Bottle

Work covered- Out of Office enabled on email. Noting the time differences for reply.

See if you know any one else coming

Spread about SQL PASS Summit

First Timers events

1 Goal

One of the ways to get the most of the PASS Summit is to have at least one goal on what you want to achieve, even if it was just to ‚Äúhang out‚ÄĚ with #SQLFamily. HAVE FUN!

 

 

Update dbase blank columns before published in SQL Server

dbase_SSIS When I was designing a SSIS package to move dbase (DBF) files to SQL Server tables. I noticed I was going have to clean up the VARCHARs and DATETIMEs in the database before I could publish the data for BI Team. In my SSIS Package these are the last two tasks. The two queries below dynamically get the columns data types and names and does update statements on the columns to correct them.

Update DateTimes to NULL

declare @tab table (id int identity(1,1) primary key,SQLtable varchar(50),SQLcolumn VARCHAR(50))
insert into @tab(SQLtable,SQLcolumn)

select
so.name table_name
,sc.name column_name
--,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE')
declare @sql varchar(8000), @i int,@count int, @sqltable varchar(50), @sqlcolumn varchar(50)

set @i=1
select @count= count(*) from @tab
while @i <= @count

begin
select @SQLtable=SQLtable from @tab where id=@i
select @sqlcolumn =sqlcolumn from @tab where id=@i

set @sql = '
UPDATE '+@SQLtable+'
SET '+@SQLcolumn+' = CASE WHEN '+@SQLcolumn+' = ''1899-12-30''
THEN NULL
ELSE '+@SQLcolumn+'
END
'
--To print in the Result/Message box
print @sql
--To EXECUTE
--EXEC (@sql)

set @i=@i+1
end

Update Varchars to NULL

declare @tab table (id int identity(1,1) primary key,SQLtable varchar(50),SQLcolumn VARCHAR(50))
insert into @tab(SQLtable,SQLcolumn)

 select
 so.name table_name
 ,sc.name column_name
 --,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('nvarchar', 'varchar', 'char', 'nchar')
--Added because of Error
AND sc.name <> 'group'

declare @sql varchar(8000), @i int,@count int, @sqltable varchar(50), @sqlcolumn varchar(50) 

set @i=1
select @count= count(*) from @tab
while @i <= @count

begin
select @SQLtable=SQLtable from @tab where id=@i
select @sqlcolumn =sqlcolumn from @tab where id=@i 

set @sql = '
 UPDATE '+@SQLtable+'
 SET '+@SQLcolumn+' = CASE WHEN LEN('+@SQLcolumn+') = 0
 THEN NULL
 ELSE '+@SQLcolumn+'
 END
'
--To print in the Result/Message box
print @sql
--To EXECUTE
--EXEC (@sql)
set @i=@i+1
end

Turn Unusable Date into usable SQL DateTime

We had situation where we had an application that feed a varchar date into a column that was not usable to our BI report writers. The column had 3 months letters and a space and the year which was numeric.

Analysis_Date

 

To create the sample Table:

SELECT * INTO [Table]
FROM (
SELECT N'Apr 2014' AS [Analysis_Date] UNION ALL
SELECT N'Feb 2012' AS [Analysis_Date] UNION ALL
SELECT N'Jan 2014' AS [Analysis_Date] UNION ALL
SELECT N'Jul 2014' AS [Analysis_Date] UNION ALL
SELECT N'Jun 2014' AS [Analysis_Date] UNION ALL
SELECT N'Mar 2014' AS [Analysis_Date] UNION ALL
SELECT N'May 2014' AS [Analysis_Date] UNION ALL
SELECT N'Apr 2013' AS [Analysis_Date]
) t;
SELECT [Analysis_Date]
FROM [Table]
--DROP TABLE [Table]
GO

 

First I broke the data into two columns as you can see below with a SUBSTRING.

SELECT DISTINCT
DDA_Hist.Analysis_Date ,
SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] ,
LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month]
FROM Table

This produces:

Analysis_Date1

 

Then I took the two substrings and mashed them to recreate the beginning of the month datetime. The magic happens with the SUBSTRING and CHARINDEX.

‘XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC’

this string lets me count the months and get a number instead of the months first three letters.

SELECT DISTINCT
DDA_Hist.Analysis_Date ,
SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] ,
LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month],
 CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4))
 + RIGHT('0'
 + CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3),
 'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
 / 3 AS VARCHAR(2)), 2) + RIGHT('0'
 + CAST('01' AS VARCHAR(2)),
 2) AS DATETIME) AS Analysis_Datetime_BeginMonth
FROM Table

This produces:

Analysis_Date2

Click to enlarge

Taking that same logic we can also get the end of that month datetime as well.

SELECT DISTINCT
DDA_Hist.Analysis_Date ,
SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] ,
LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month],
CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4))
+ RIGHT('0'
+ CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3),
'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
/ 3 AS VARCHAR(2)), 2) + RIGHT('0'
+ CAST('01' AS VARCHAR(2)),
2) AS DATETIME) AS Analysis_Datetime_BeginMonth</pre>
<pre> DATEADD(DAY,
-DAY(DATEADD(MONTH, 1,
CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date,
5, 9) AS VARCHAR(4))
+ RIGHT('0'
+ CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date,
3),
'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
/ 3 AS VARCHAR(2)), 2) + RIGHT('0'
+ CAST('01' AS VARCHAR(2)),
2) AS DATETIME))),
DATEADD(MONTH, 1,
CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5,
9) AS VARCHAR(4)) + RIGHT('0'
+ CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date,
3),
'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
/ 3 AS VARCHAR(2)),
2) + RIGHT('0'
+ CAST('01' AS VARCHAR(2)),
2) AS DATETIME))) AS Analysis_Datetime_MonthEnd
FROM Table

This produces:

Analysis_Date3

Click to enlarge

Have Fun!

Flattening a Parent Child Hierarchy with EnumeratedPath

HFM_Hierarchy

This is for companies with HFM repository on MS SQL or users wanting to flattening a parent-child hierarchy. If you are administratoring HFM and have users who would like to have that HFM Account Hierarchy in SQL. These scripts will help you with flattening a parent-child hierarchy for internal reporting. They are designed and can be very useful for both SQL and HFM. By using a recursive CTE we can enumerate the path from the leaf to the root/roof (Bottom to the top). See the first image to envision the HFM parent child hierarchy tree.

First I created a temp table to work with one item’s parent-child hierarchy with 14 levels. The -1 is the top of the house ¬†itemid in HFM; other applications/ examples may have NULL for the top parentid. Once you have this working you will want to change¬†the first SQL view below to point to the¬†hfm.dbo.Company_Account_Layout table.

SELECT * INTO tmp_GridResults_1
FROM (
SELECT N'860' AS [ItemID], N'861' AS [ParentID] UNION ALL
SELECT N'861' AS [ItemID], N'868' AS [ParentID] UNION ALL
SELECT N'868' AS [ItemID], N'908' AS [ParentID] UNION ALL
SELECT N'908' AS [ItemID], N'1056' AS [ParentID] UNION ALL
SELECT N'1056' AS [ItemID], N'1704' AS [ParentID] UNION ALL
SELECT N'1704' AS [ItemID], N'1706' AS [ParentID] UNION ALL
SELECT N'1706' AS [ItemID], N'1371' AS [ParentID] UNION ALL
SELECT N'1371' AS [ItemID], N'1708' AS [ParentID] UNION ALL
SELECT N'1708' AS [ItemID], N'1709' AS [ParentID] UNION ALL
SELECT N'1709' AS [ItemID], N'1711' AS [ParentID] UNION ALL
SELECT N'1711' AS [ItemID], N'1903' AS [ParentID] UNION ALL
SELECT N'1903' AS [ItemID], N'2104' AS [ParentID] UNION ALL
SELECT N'2104' AS [ItemID], N'-1' AS [ParentID] ) t;
SELECT [ItemID], [ParentID]
FROM tmp_GridResults_1

This produces:

HFM1

Click to enlarge

In this cte I’m going to build the tree Path, ItemID, ParentID and the Level. Remember change the¬†hfm.dbo.Company_Account_Layout table after you have the temp table working.

CREATE VIEW [dbo].[vw_HFM_CTE] AS

WITH cte([ItemID] ,[Path],[Level], [ParentID])
AS
(
--Parents
SELECT e.[ItemID],
Path=CAST(CAST(e.[ItemID] AS VARCHAR(500)) + ',' AS VARCHAR(1000)),
0 AS [Level],
[ParentID]
FROM [dbo].[tmp_GridResults_1]AS e
--FROM hfm.dbo.Company_Account_Layout AS e
WHERE e.[ParentID] = -1
UNION ALL
--Levels up
SELECT
l.[ItemID],
Path1=CAST(CAST(cte.Path AS VARCHAR(500)) + CAST(l.[ItemID] AS VARCHAR(500)) + ',' AS VARCHAR(1000)),
[Level] + 1,
l.[ParentID] AS [ParentID]
FROM [dbo].[tmp_GridResults_1] AS l
--FROM hfm.dbo.Company_Account_Layout AS l
INNER JOIN cte
ON l.[ParentID] = cte.[ItemID]
)
SELECT *
FROM cte
GO

This produces:

HFM2

Click to enlarge

Then to keep things easy and clear for me I break into another view. I built a SQL view to flattening a parent-child hierarchy path with another cte that converts the Path column to XML and then cross apply to unpivot the data then I pivot the data back on the levels.

I’m including HFM’s Label and Description from the COMPANY_ACCOUNT_ITEM and COMPANY_ACCOUNT_DESC tables.

The three case statements are separating the label, description, and child item. I also replace brackets around the work [NONE] in our labels.

CREATE VIEW [dbo].[vw_HFM_CTE_Results] AS
 WITH cte1 AS (

 SELECT
 t2.ItemID,
 t3.split,
 ROW_NUMBER() OVER (PARTITION BY t2.ItemID ORDER BY t2.ItemID) AS num
 FROM
 (
 SELECT *,
 CAST('<X>'+REPLACE(t.path,',','</X><X>')+'</X>' AS XML) AS xmlfilter
 FROM [HFM].[dbo].[vw_HFM_CTE] t
 WHERE [Path] NOT LIKE '%,2101,%'
 ) t2
 CROSS APPLY
 (
 SELECT col1data.D.value('.','varchar(50)') AS split
 FROM t2.xmlfilter.nodes('X') AS col1data(D)) t3

 )

SELECT ItemID
 ,[1] AS [1_Path]
 ,CASE WHEN LEN([1]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([1],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([1],'[',''),']',''))) END AS [1]
 ,CASE WHEN LEN([1]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([1],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([1],'[',''),']',''))+1,CHARINDEX('|',REVERSE(REPLACE(REPLACE([1],'[',''),']','')))-1)
 END AS [1_Label]
 ,CASE WHEN LEN([1]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([1],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([1],'[',''),']','')))))
 END AS [1_Description]
 ------
 ,[2] AS [2_Path]
 ,CASE WHEN LEN([2]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([2],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([2],'[',''),']',''))) END AS [2]
 ,CASE WHEN LEN([2]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([2],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([2],'[',''),']',''))+1,LEN([2])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([2],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([2],'[',''),']','')))
 END AS [2_Label]
 ,CASE WHEN LEN([2]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([2],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([2],'[',''),']','')))))
 END AS [2_Description]
 ------
 ,[3] AS [3_Path]
 ,CASE WHEN LEN([3]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([3],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([3],'[',''),']',''))) END AS [3]
 ,CASE WHEN LEN([3]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([3],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([3],'[',''),']',''))+1,LEN([3])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([3],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([3],'[',''),']','')))
 END AS [3_Label]
 ,CASE WHEN LEN([3]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([3],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([3],'[',''),']','')))))
 END AS [3_Description]
 --------
 ,[4] AS [4_Path]
 ,CASE WHEN LEN([4]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([4],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([4],'[',''),']',''))) END AS [4]
 ,CASE WHEN LEN([4]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([4],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([4],'[',''),']',''))+1,LEN([4])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([4],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([4],'[',''),']','')))
 END AS [4_Label]
 ,CASE WHEN LEN([4]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([4],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([4],'[',''),']','')))))
 END AS [4_Description]
 -------
 ,[5] AS [5_Path]
 ,CASE WHEN LEN([5]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([5],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([5],'[',''),']',''))) END AS [5]
 ,CASE WHEN LEN([5]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([5],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([5],'[',''),']',''))+1,LEN([5])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([5],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([5],'[',''),']','')))
 END AS [5_Label]
 ,CASE WHEN LEN([5]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([5],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([5],'[',''),']','')))))
 END AS [5_Description]
 -------
 ,[6] AS [6_Path]
 ,CASE WHEN LEN([6]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([6],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([6],'[',''),']',''))) END AS [6]
 ,CASE WHEN LEN([6]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([6],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([6],'[',''),']',''))+1,LEN([6])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([6],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([6],'[',''),']','')))
 END AS [6_Label]
 ,CASE WHEN LEN([6]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([6],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([6],'[',''),']','')))))
 END AS [6_Description]
 -------
 ,[7] AS [7_Path]
 ,CASE WHEN LEN([7]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([7],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([7],'[',''),']',''))) END AS [7]
 ,CASE WHEN LEN([7]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([7],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([7],'[',''),']',''))+1,LEN([7])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([7],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([7],'[',''),']','')))
 END AS [7_Label]
 ,CASE WHEN LEN([7]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([7],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([7],'[',''),']','')))))
 END AS [7_Description]
 -------
 ,[8] AS [8_Path]
 ,CASE WHEN LEN([8]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([8],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([8],'[',''),']',''))) END AS [8]
 ,CASE WHEN LEN([8]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([8],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([8],'[',''),']',''))+1,LEN([8])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([8],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([8],'[',''),']','')))
 END AS [8_Label]
 ,CASE WHEN LEN([8]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([8],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([8],'[',''),']','')))))
 END AS [8_Description]
 -------
 ,[9] AS [9_Path]
 ,CASE WHEN LEN([9]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([9],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([9],'[',''),']',''))) END AS [9]
 ,CASE WHEN LEN([9]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([9],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([9],'[',''),']',''))+1,LEN([9])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([9],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([9],'[',''),']','')))
 END AS [9_Label]
 ,CASE WHEN LEN([9]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([9],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([9],'[',''),']','')))))
 END AS [9_Description]
 -------
 ,[10] AS [10_Path]
 ,CASE WHEN LEN([10]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([10],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([10],'[',''),']',''))) END AS [10]
 ,CASE WHEN LEN([10]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([10],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([10],'[',''),']',''))+1,LEN([10])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([10],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([10],'[',''),']','')))
 END AS [10_Label]
 ,CASE WHEN LEN([10]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([10],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([10],'[',''),']','')))))
 END AS [10_Description]
 -------
 ,[11] AS [11_Path]
 ,CASE WHEN LEN([11]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([11],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([11],'[',''),']',''))) END AS [11]
 ,CASE WHEN LEN([11]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([11],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([11],'[',''),']',''))+1,LEN([11])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([11],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([11],'[',''),']','')))
 END AS [11_Label]
 ,CASE WHEN LEN([11]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([11],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([11],'[',''),']','')))))
 END AS [11_Description]
 -------
 ,[12] AS [12_Path]
 ,CASE WHEN LEN([12]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([12],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([12],'[',''),']',''))) END AS [12]
 ,CASE WHEN LEN([12]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([12],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([12],'[',''),']',''))+1,LEN([12])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([12],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([12],'[',''),']','')))
 END AS [12_Label]
 ,CASE WHEN LEN([12]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([12],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([12],'[',''),']','')))))
 END AS [12_Description]
 -------
 ,[13] AS [13_Path]
 ,CASE WHEN LEN([13]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([13],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([13],'[',''),']',''))) END AS [13]
 ,CASE WHEN LEN([13]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([13],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([13],'[',''),']',''))+1,LEN([13])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([13],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([13],'[',''),']','')))
 END AS [13_Label]
 ,CASE WHEN LEN([13]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([13],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([13],'[',''),']','')))))
 END AS [13_Description]
 -------
 ,[14] AS [14_Path]
 ,CASE WHEN LEN([14]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([14],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([14],'[',''),']',''))) END AS [14]
 ,CASE WHEN LEN([14]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([14],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([14],'[',''),']',''))+1,LEN([14])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([14],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([14],'[',''),']','')))
 END AS [14_Label]
 ,CASE WHEN LEN([14]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([14],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([14],'[',''),']','')))))
 END AS [14_Description]
 -------
FROM
(
 SELECT cte1.ItemID, num, CAST(split+'|'+CAST(LTRIM(RTRIM(t4.Label))AS VARCHAR(20))+'|'+LTRIM(RTRIM(t5.[Description]))AS VARCHAR(8000)) AS split
 FROM CTE1
 INNER JOIN hfm.dbo.COMPANY_ACCOUNT_ITEM t4
 ON cte1.split = t4.ItemID

 INNER JOIN hfm.dbo.COMPANY_ACCOUNT_DESC t5</pre>
<pre> ON cte1.split = t5.ItemID
) AS sourcetable
PIVOT
(MAX(split) FOR num IN ([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12],[13],[14])
) AS pivottable
GO

This produces:

HFM3

Click to enlarge

Then I have a 4th view to filter alternate hierarchy paths and comment out the paths.

CREATE VIEW [dbo].[vw_HFM_Hierarchy]
AS
SELECT cte.[ItemID]
,t1.Label
 --,[1_Path]
 ,[1]
 ,[1_Label]
 ,[1_Description]
 --,[2_Path]
 ,[2]
 ,[2_Label]
 ,[2_Description]
 --,[3_Path]
 ,[3]
 ,[3_Label]
 ,[3_Description]
 --,[4_Path]
 ,[4]
 ,[4_Label]
 ,[4_Description]
 --,[5_Path]
 ,[5]
 ,[5_Label]
 ,[5_Description]
 -- ,[6_Path]
 ,[6]
 ,[6_Label]
 ,[6_Description]
 --,[7_Path]
 ,[7]
 ,[7_Label]
 ,[7_Description]
 -- ,[8_Path]
 ,[8]
 ,[8_Label]
 ,[8_Description]
 --,[9_Path]
 ,[9]
 ,[9_Label]
 ,[9_Description]
 -- ,[10_Path]
 ,[10]
 ,[10_Label]
 ,[10_Description]
 --,[11_Path]
 ,[11]
 ,[11_Label]
 ,[11_Description]
 -- ,[12_Path]
 ,[12]
 ,[12_Label]
 ,[12_Description]
 -- ,[13_Path]
 ,[13]
 ,[13_Label]
 ,[13_Description]
 --,[14_Path]
 ,[14]
 ,[14_Label]
 ,[14_Description]
 FROM [HFM].[dbo].[vw_HFM_CTE_Results] cte

 LEFT JOIN [hfm].[dbo].[COMPANY_ACCOUNT_ITEM] t1
 ON t1.ItemID = cte.ItemID

WHERE [FirstChildID] = -1
AND LEN([Label]) = 4
GO

This produces:

HFM4

Click to enlarge

The obvious downside with method is the static nature of the levels.

Happy Coding and Have Fun!

SQL Server Dynamic PIVOT Query

Let say you have a table or view that has customers credit scores from every quarter for a few years back and you want to pivot the data out but new data is coming every quarter and you want the query to continue to work.
We need a dynamic pivot query:


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

 --Reporting_Date is the move column.
select @cols = STUFF((SELECT  DISTINCT ',' + QUOTENAME(ED.Reporting_Date)
                    FROM [Database].[dbo].[vw_Data_Reporting] AS ED

            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
		set @query
		--/*This is the Pivot, we are inserting the @cols in two spots.
		 --  One in the Select from the Pivot
		 --  Two in the FOR IN */
      = 'SELECT pvt.[ID_Number],
	  pvt.[Phone_Number],
	  pvt.[SSN],
    ' +    @cols +'
         FROM
    (SELECT [Reporting_Date]
      ,[ID_Number]
      ,[Phone_Number]
      ,[SSN]
      ,[Credit_Score]
	  FROM [Database].[dbo].[vw_Data_Reporting]
	 )
    AS EQ
PIVOT
(
MAX([Credit_Score])
FOR [Reporting_Date]  IN
( '+
@cols +')
) AS pvt
		 '
		 --You have to pick PRINT or Execute but only one.

		-- 1. Uncomment to see the Printed Text for what you are about to run.
		--PRINT @query

		-- 2. Uncomment when you want to run it.
		--execute(@query)

Enjoy and Happy Coding.

Update SQL Jobs to Databases on Netapp Drive

I had to create job to add non naming conversational database names to my existing NetApp snap manager for SQL job.

If a database was not in the LUN I email the users and alerts them to move it.

In this case the databases sit in the R: Drive for .mdf and S: Drive for .ldf

Create SQL Agent Job

then insert Step 1 РTransact-SQL script

USE msdb

GO

SET NOCOUNT ON
 DECLARE @FileName varchar(255)
 DECLARE @File_Exists int
 DECLARE @DBCount int
 DECLARE @DBCounter int
 DECLARE @DBName varchar(50)
 SELECT @DBCount = count(*) from master.dbo.sysdatabases sd
 INNER JOIN sys.master_files mf
 ON mf.database_id = sd.dbid
 WHERE sd.name NOT IN ('master','msdb','model','tempdb')
 AND physical_name NOT LIKE '%.ldf%'
 AND physical_name LIKE '%R:%'
 AND CONVERT(sysname,databasepropertyex(sd.name,'status')) ='ONLINE'
 AND CONVERT(sysname,databasepropertyex(sd.name,'updateability')) ='READ_WRITE'
 --PRINT @DBCount
 BEGIN
 SET @DBCounter = 1
 DECLARE @BackupCmd varchar(500)
 SET @BackupCmd = '"C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe" new-backup -svr ''' + @@SERVERNAME + ''' -d ''' + @@SERVERNAME + ''', ''' + cast(@DBCount as varchar(3)) + ''','
 DECLARE strategy_cursor CURSOR FOR
 SELECT sd.name from master.dbo.sysdatabases sd
 INNER JOIN sys.master_files mf
 ON mf.database_id = sd.dbid
 WHERE sd.name NOT IN ('master','msdb','model','tempdb')
 AND physical_name NOT LIKE '%.ldf%'
 AND physical_name LIKE '%R:%'
 AND CONVERT(sysname,databasepropertyex(sd.name,'status')) ='ONLINE'
 AND CONVERT(sysname,databasepropertyex(sd.name,'updateability')) ='READ_WRITE'
 ORDER BY name
 OPEN strategy_cursor
 FETCH NEXT FROM strategy_cursor INTO @DBName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 IF @DBCounter < @DBCount SET @BackupCmd = @BackupCmd + '''' + @DBName + ''','
 IF @DBCounter = @DBCount SET @BackupCmd = @BackupCmd + '''' + @DBName + ''' -ver ‚ÄďverInst ''DB110'' -mp ‚Äďmpdir ''C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint'' -RetainBackups 5 -lb -bksif -RetainSnapofSnapInfo 8 -trlog -noutm -gen ‚Äďmgmt daily'
 SET @DBCounter = @DBCounter + 1
 --PRINT @BackupCmd
 --PRINT @DBCounter
 FETCH NEXT FROM strategy_cursor INTO @DBName
 END
 CLOSE strategy_cursor
 DEALLOCATE strategy_cursor
 END
 --PRINT @BackupCmd
 --DECLARE @rc int
 --EXEC @rc = master.dbo.xp_cmdshell @BackupCmd

EXEC dbo.sp_update_jobstep
 @job_name = N'BACKUP 22:20 DBs',
 @step_id = 1,
 @command =@BackupCmd
 GO

-- Start T-SQL
 USE msdb
 EXEC sp_send_dbmail
 @profile_name = 'DB.sqlserver',
 @recipients = 'jason@test.com',
 @copy_recipients = 'jason@test.com',
 @subject = 'Databases not in NetApp Nightly Snaps',
 @body = 'The Databases below need to be move in R: & S: Drives; if you want them snapped.',
 @execute_query_database = 'msdb',
 @query = 'SELECT sd.name from master.dbo.sysdatabases sd
 INNER JOIN sys.master_files mf
 ON mf.database_id = sd.dbid
 WHERE sd.name NOT IN (''master'',''msdb'',''model'',''tempdb'')
 AND physical_name NOT LIKE ''%.ldf%''
 AND physical_name NOT LIKE ''%R:%'''
 -- End T-SQL --
 GO