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