GRANT EXECUTE

exec [dbo].[GSPrimeExcel]

GRANT EXECUTE ON [dbo].[GSPrimeExcel] TO GSPrimeExcelUser;

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

USE DB1;
GRANT SELECT ON OBJECT:: View1 TO JohnDoe;
GO

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

ALTER Table Primary Key

If a primary key already doesn't already exists. then you want to do this

ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);

If a primary key already exists then you want to do this

drop primary key
ALTER TABLE Table1DROP CONSTRAINT PK_Table1_Col1GO


ALTER TABLE [dbo].[tbl_Excel_Tier1Tier2IDs]
   drop constraint [PK_tbl_Excel_Tier1Tier2IDs]
GO


 ALTER TABLE [dbo].[tbl_Excel_Tier1Tier2IDs]
   ADD PRIMARY KEY(Agent_Name, PwC_GUID, Effective_Date);


ALTER TABLE dbo.tblComment_Review
ADD FOREIGN KEY (Record_ID)
REFERENCES [dbo].[tblSurvey_Response_2](Record_ID)

Create List of Calendar Dates

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Holidays_Weekends](
CalendarDate datetime NOT NULL,
descr [varchar](20) NULL,
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


WITH sample AS (
 SELECT CAST('2015-04-01' AS DATETIME) AS dt
 UNION ALL
 SELECT DATEADD(dd, 1, dt)
FROM sample s
  WHERE DATEADD(dd, 1, dt) <= CAST('2015-06-30' AS DATETIME))

INSERT INTO [dbo].[Holidays_Weekends]
SELECT *, '' FROM sample

Find is_ansi_padded status of columns

SELECT b.name as 'schema_name', a.name as 'table_name', a.type_desc

       , c.name, c.column_id

       , d.name as [type_name], c.max_length, c.precision, c.scale, c.is_nullable, c.is_ansi_padded, is_identity

FROM sys.types d INNER JOIN (sys.columns c INNER JOIN (sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id)

              ON c.object_id = a.object_id)

              ON d.user_type_id = c.user_type_id

WHERE b.name like '%snapshot%'

       and is_ansi_padded = 0 and d.name NOT IN ('int', 'datetime', 'date', 'numeric', 'float', 'bigint', 'smallint')

ORDER BY a.[type], b.name, a.name, c.column_id

To fix

TrimTrailingBlanks "no" is the same as ANSI_PADDING ON.  So it looks like those columns were (correctly) created with SET ANSI_PADDING ON.

If you have only a few columns with TrimTrailingBlanks "yes", you can create a new column, migrate data and rename like the example below, keeping in mind that you will need do drop constraints and indexes on the column(s) and recreate afterward.  You might find it easier to recreate the table if you have many columns.

SET ANSI_PADDING ON;
GO
ALTER TABLE dbo.MyTable
    ADD MyColumn_New varchar(100);
GO
UPDATE dbo.MyTable
    SET MyColumn_New = MyColumn;
GO
ALTER TABLE dbo.MyTable
    DROP COLUMN MyColumn;
GO
EXEC sp_rename 'dbo.MyTable.MyColumn_New', 'MyColumn', 'COLUMN';
GO

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

RowVersion

Create Table Orders (

    OrderID int not null identity(1,1)

        Constraint PK_Orders Primary Key

    , ProductName varchar(50) not null

    , Quantity int not null

    , RowRevision rowversion

)

Insert Into Orders(ProductName, Quantity)

Values ('Widget-Dongles', 13)

Insert Into Orders(ProductName, Quantity)

Values ('test 2', 15)

select OrderID, ProductName, Quantity, RowRevision, cast(RowRevision as int)

from orders order by RowRevision desc

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

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

Excel: remove #VALUE formula result

If a cell shows #VALUE it is because it cannot evaluate what you asked for.
eg If A1 has a value of 5, and A2 has "No", then =SUM(A1:A5) will give #VALUE because it cannot add a number and text together. 

=IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))

So you can get around this by querying if there is an error: =IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))
Thus if there is an error, the formula returns "No Data", otherwise you get the sum.

Similarly, for #DIV/0!, you can (should?) test to see if the denominator is 0. If it is then put "", else the result of the division.

Hope this helps

MS-Access: How to refer to subform control in SQL

Forms![main form name]![subform control name].Form![control name]


To refer to a control on a subform, use the following syntax: 

   Forms![main form name]![subform control name].Form![control name]
    

To refer to a control on a subreport, use the following syntax: 

   Reports![main report name]![subreport control name].Report![control
   name]
    

NOTE: Only subforms are discussed in the rest of this article, but all the information applies to both subforms and subreports. 

It is important to note that you cannot refer to controls on a subform with the following syntax: 

   Forms![subform name]![control name]
    

Private Sub Command3_Click()
    On Error GoTo err_Command3_Click

    Dim intHRGroupID As Integer
    Dim sql As String
    
        Debug.Print "############  begin   ##################'"
    
    Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form![HR Manager Group ID].SetFocus
    HRGroupID = Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form![HR Manager Group ID].Text
    
    sql = "UPDATE [HR_TLV_HR_Staff_DB] z INNER JOIN ([HR Group to HR Mgr Relationships] x INNER JOIN [HR Manager Group] y ON x.[HR Manager Group ID] = y.[HR Manager Group Code] " & vbCrLf & _
          "    ) ON z.[ID] = x.[HR Manager Emplid] " & vbCrLf & _
          "SET x.[Included in Group] = 1 " & vbCrLf & _
          "WHERE y.[HR Manager Group Code] = " & HRGroupID & vbCrLf & _
          "  AND " & Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form.Filter & " AND 1 = 1"
    
    Debug.Print sql
        
    sql = Replace(sql, "[HR Mgr to Group Update]", "z")
    
    Debug.Print "############  revised   ##################'" & vbCrLf
    Debug.Print sql
    
    DoCmd.RunSQL sql

exit_Command3_Click:
    Exit Sub
err_Command3_Click:
    MsgBox Err.Number & " " & Err.Description
    GoTo exit_Command3_Click
End Sub