CASE statement in UPDATE query

UPDATE a
SET a.Date_Added = (CASE 
                    WHEN a.Date_Requested > b.ADmodifyTimeStamp THEN getdate() 
                    ELSE b.ADmodifyTimeStamp END)
FROM [dbo].[tableA] a INNER JOIN [dbo].[tableA] b 
     ON a.[col1] = b.[col1] AND a.[col2] = b.[col2]
WHERE a.[col3] IS NULL

Configure SQL Agent Operator role

http://msdn.microsoft.com/en-us/library/ms187901.aspx

Using SQL Server Management Studio

To add a SQL login or msdb role to a SQL Server Agent fixed database role

1. In Object Explorer, expand a server.
2. Expand Security, and then expand Logins.
3. Right-click the login you wish to add to a SQL Server Agent fixed database role, and select Properties.
4. On the User Mapping page of the Login Properties dialog box, select the row containing msdb.
5. Under Database role membership for: msdb, check the appropriate SQL Server Agent fixed database role.

[Top]

To configure a proxy account to create and manage SQL Server Agent job steps

1. In Object Explorer, expand a server.
2. Expand SQL Server Agent.
3. Right-click Proxies and select New Proxy.
4. On the General page of the New Proxy Account dialog, specify the proxy name, credential name, and description for the new proxy. Note that you must create a credential first before creating a SQL Server Agent proxy. For more information about creating a credential, see Create a Credential and CREATE CREDENTIAL (Transact-SQL).
5. Check the appropriate subsystems for this proxy.
6. On the Principals page, add or remove logins or roles to grant or remove access to the proxy account.

MAX updated row per User record

use AssessmentApp

declare @LatestDateTime as datetime

SET @LatestDateTime = '09/12/2014 04:03:15.000'

-- this will capture EVERY record.

/*

SELECT ASSESSMENT_ID, AUDIT_DATE_TIME, AUDIT_BY_PPID, AUDIT_BY_NAME, NEW_ASSESSMENT_STATUS, ASSESSMENT_STATUS, USER_AGENT_TYPE, USER_AGENT_VERSION

FROM [dbo].[table]

WHERE AUDIT_DATE_TIME <= @LatestDateTime

ORDER BY AUDIT_DATE_TIME DESC

*/

-- this will capture the LATEST record for each Assessment_ID.

SELECT a.ASSESSMENT_ID, a.AUDIT_DATE_TIME, AUDIT_BY_PPID, AUDIT_BY_NAME, NEW_ASSESSMENT_STATUS, ASSESSMENT_STATUS, USER_AGENT_TYPE, USER_AGENT_VERSION

FROM [dbo].[table] a

WHERE AUDIT_DATE_TIME IN (

                          SELECT MAX(aa.AUDIT_DATE_TIME)

                          FROM [dbo].[ASSESSMENT_AUDIT] aa

                          WHERE aa.AUDIT_DATE_TIME <= @LatestDateTime

                             and aa.Assessment_ID = a.ASSESSMENT_ID                                                    

                            GROUP BY aa.ASSESSMENT_ID

                                         )

       AND AUDIT_DATE_TIME <= @LatestDateTime

--     AND assessment_id = 88205

ORDER BY a.Audit_Date_Time

ALTER SCHEMA Ownership

Syntax ::

USE [dbname]
GO
ALTER AUTHORIZATION ON SCHEMA::[schema_name] TO [login]

USE [sandbox]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [dbo]



SELECT s.name, u.name AS owner
   FROM sys.schemas s, sys.database_principals u
   WHERE s.principal_id = u.principal_id
and u.name like '%chao%';

All objects in a schema

SELECT b.name as 'schema_name', a.name, a.object_id, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
FROM sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id
WHERE object_id IN (select object_ID from sys.columns where name like 'snapshot%')
ORDER BY a.[type], b.name, a.name


--1760985600
SELECT b.name as 'schema_name', a.name, a.object_id, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
FROM sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id
WHERE object_id IN (select object_ID from sys.columns where name like 'snapshot%')
and b.name = 'snapshot'
and a.name = 'T2_Flatfile_snapshot_level'
ORDER BY a.[type], b.name, a.name

--823934257
SELECT b.name as 'schema_name', a.name, a.object_id, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
FROM sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id
WHERE object_id IN (select object_ID from sys.columns where name like 'snapshot%')
and b.name = 'dbo'
and a.name = 'Flatfile_snapshot'
ORDER BY a.[type], b.name, a.name


SELECT name, column_id FROM sys.columns where object_ID = 109503719
order by 2
union all
SELECT object_id, name, column_id FROM sys.columns where object_ID = 823934257
order by 2

ORA-00907 Missing Paranthesis error

PROBLEM:  SSIS package fails  with ORA-00907 error even though the query can be successfully parsed in the data flow task.

POSSIBLE SOLUTION:  Remove all of the comments from the query.

----------------------------------------------------------------------------------------

Sound stupid, I know, but try it anyway.

Transaction Count

http://stackoverflow.com/questions/21930156/transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm

Custom Sorting in Power Pivot (Tabular Models)

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

Determine SQL Server 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