Oracle Month function

SELECT LAST_NAME, FIRST_NAME, ORIG_HIRE_DT, BIRTHDATE, extract(month from BIRTHDATE)
from ps_pwc_employees
where pwc_mktcls_descr = 'Philadelphia Metro'
and  extract(month from BIRTHDATE) =5

Clustered Index syntax

USE [DWHCBI]
GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

GO

/****** Object:  Index [ndx_unique]    Script Date: 4/17/2014 9:40:23 AM ******/
CREATE UNIQUE CLUSTERED INDEX [ndx_unique] ON [dbo].[Transform_Rolling_12_Months]
(
[Month_ID] ASC,
[Rolling12Month] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) 
ON [Primary]
GO

Schemabinding

CREATE VIEW [dbo].[Transform_Rolling_12_Months]
with schemabinding
AS
SELECT A.Month_ID, B.Month_ID AS Rolling12Month
FROM dbo.Dim_Month AS A LEFT OUTER JOIN dbo.Dim_Month AS B ON B.Month_ID BETWEEN A.Month_ID - 11 AND A.Month_ID
WHERE (A.Month_ID > 11)

GO

Make comma separates list from database table

select distinct se.guid, spt.respid
into #tempSUBLOS
from dbo.Static_Pivot_Table spt inner join dbo.tblSaratogaExtract se on spt.respid=se.respid
where spt.sublos is null


DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + se.[GUID]
from #tempSUBLOS se
SELECT @listStr

drop table #tempSUBLOS
GO

List all Database Constraints

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

where Constraint_Name like '%Service%'
GO

MS-Access: Suppress System Messages

MS ACCESS: SUPPRESS SYSTEM MESSAGES (QUERY CONFIRMATIONS) IN ACCESS 2003/XP/2000/97

Question: In Microsoft Access 2003/XP/2000/97, how can I suppress the system messages when I run queries? For example, when a delete query is run, Access will ask you to confirm the number of deletions. How can I suppress these kinds of messages?

Answer: To suppress system messages in Access, you will need to use the "Docmd.SetWarnings" command. You could use the following code.

DoCmd.SetWarnings False

{...run queries...}
   
DoCmd.SetWarnings True

For example, you could create a button on your form and place the following code on the Click event:

Private Sub Command1_Click()

   'Turn system messages off
   DoCmd.SetWarnings False
   
   DoCmd.OpenQuery "Delete all entries"
   DoCmd.OpenQuery "Populate with new entries"
   
   'Turn system messages back on
   DoCmd.SetWarnings True
   
End Sub

In this example, we have a button called Command1. When this button is clicked, the system messages will be turned off. Then two queries are run - one called "Delete all entries" and second query called "Populate with new entries".

After the two queries are run, the system messages are turned back on.

The purpose of turning off the system messages is to hide the following kinds of messages from the users:

DateTime into Date

, CONVERT(char(10), DateOfHire,126)


turns DateTime in to Date

Determine Currently Running SQL Server Processes

1.1.1       Determine Currently Running SQL Server Processes

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

If you need to see the SQL running for a given spid from the results, use something like this:

Declare @spid int,   @stmt_start int,   @stmt_end int,   @sql_handle binary(20) set @spid = XXX -- 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 endfrom    master.dbo.sysprocesseswhere   spid = @spidorder 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)