Custom Sorting in Power Pivot (Tabular Models)

http://dhanumjay999.blogspot.com/2012/06/custom-sorting-slicers-in-power-pivot.html

Table Create Date

SELECT col.* from sys.objects obj 
inner join sys.columns col 
on obj.object_Id=col.object_Id 
and obj.Name=@tableName


SELECT * FROM sys.objects WHERE Name='tblHeadcount'

SELECT sys.objects.name, sys.schemas.name AS schema_name, create_date, modify_date
FROM    sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.name = 'tblHeadCount'



SELECT sys.objects.name, sys.schemas.name AS schema_name, create_date, modify_date
FROM    sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.name like '%sp_T2_Daily%'

ORDER BY create_date

Disable double-click on Pivot Table

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rng As Range

    '~~> This is your pivot table range. Change as applicable
    Set rng = Range("B43:E52")

    '~~> Check if the double click happend in the Pivot
    If Not Intersect(Target, rng) Is Nothing Then
        '~~> Cancel Double click
        Cancel = True
    End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rng As Range
    Dim wks As Worksheet
    Dim pt As PivotTable

    Set wks = Target.Worksheet
    For Each pt In wks.PivotTables()
        Set rng = Range(pt.TableRange1.Address)
        If Not Intersect(Target, rng) Is Nothing Then
            Cancel = True
        End If
    Next
End Sub

Application.Wait (Now + #12:00:05 AM#)

--######################################
--this only works in debug mode...sometimes.
-- below gives 5 second delay.
--######################################

Public Sub Workbook_NewSheet(ByVal Sh As Object)
    On Error GoTo err_NewWS
    
    'Debug.Print "test"
    'Dim oWS As Excel.Worksheet
    'Set oWS = Application.Worksheets(ActiveSheet.Name)
    Sh.Activate
    
    Application.Wait (Now + #12:00:05 AM#)
    If Left(Sh.Name, 5) = "Sheet" Then
    
        If Sh.Range("D3").Value = "[$tblSnapshot].[SNAPSHOT_STATUS]" Then
            
            Sh.Range("A3:CK3").AutoFilter Field:=4, Criteria1:="ACKNOWLEDGED"
             Debug.Print "executed " & Sh.Name & " "; Now
        End If
        
    Else
        Debug.Print "DID NOT WORK " & Sh.Name & " "; Now
    End If
    
    'Set oWS = Nothing
exit_NewWS:
    Exit Sub
err_NewWS:
    MsgBox Err.Number & " " & Err.Description
    GoTo exit_NewWS
End Sub

ORACLE concatenate values

SELECT emplid, EFFDT, Last_name || ', ' || First_Name as Name2
FROM [table] where emplid = '00000001'

Identify SQL Server Connections

sp_who

SELECT p.spid
,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
order by batch_duration desc

--kill 60

Declare @spid int
,   @stmt_start int
,   @stmt_end int
,   @sql_handle binary(20)

set @spid = 60 -- Fill this in

select  top 1
    @sql_handle = sql_handle
,   @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
,   @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from    master.dbo.sysprocesses
where   spid = @spid
order by ecid

SELECT
    SUBSTRING( text,
    COALESCE(NULLIF(@stmt_start, 0), 1),
    CASE @stmt_end
    WHEN -1
    THEN DATALENGTH(text)
    ELSE
    (@stmt_end - @stmt_start)
    END
    )
FROM ::fn_get_sql(@sql_handle)

/*
Executed as user: NAM\SQLAdmin100. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5058.0 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  5:01:39 AM  Error: 2014-09-02 05:01:41.52  
Code: 0x00000000     Source: Daily_tblSnapshot_Insert      Description: Invalid column name 'ASSESSEE_HR_EMAIL'.  End Error  Error: 2014-09-02 05:01:41.52  
Code: 0x00000000     Source: Daily_tblSnapshot_Insert      Description: Invalid column name 'ASSESSEE_HR_MANAGER_EMPLID'.  End Error  Error: 2014-09-02 05:01:41.58  
Code: 0xC002F210     Source: Daily_tblSnapshot_Insert Execute SQL Task     Description: Executing the query "EXEC [snapshot].Daily_tblSnapshot_Insert;" failed with the following error: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  5:01:39 AM  Finished: 5:01:41 AM  Elapsed:  2.594 seconds.  The package execution failed.  The step failed.
*/

SQL JOIN to find non-matching rows

SELECT t1.ID 
FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID 
WHERE t2.ID IS NULL