select 
substring(login_id, 1, patindex('% %', login_id)) as firstname,
substring(login_id, patindex('% %', login_id), len(login_id)) as surname

from c_contact
and login_id <> ''

// count users with filter
select Count(surname+firstname) as duplicates, surname, firstname
from c_contact
where show_record=1
group by surname, firstname
having Count(surname+firstname) > 1
order by duplicates, surname, firstname

// original code
SELECT column,COUNT(*) c 
FROM table
GROUP BY column 
HAVING COUNT(*) > 1

//update code
update c_contact
set duplicates = dbo.func_get_duplicates(surname, firstname)

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use [?]
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for
        select fileid from sysfiles where  status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'

exec sp_msforeachdb @ssql

CREATE GENERATOR testgen;

SET TERM $$ ;
CREATE TRIGGER trg_gen_test FOR test_table
ACTIVE
BEFORE INSERT AS
BEGIN
  new.identity_column = gen_id(testgen, 1);
END $$
SET TERM ; $$


SET TERM $$ ;

CREATE PROCEDURE proc_insert ( val1 VARCHAR(20), val2 VARCHAR(20) ) RETURNS 
(generated_id INTEGER)
AS
BEGIN
  generated_id = gen_id(testgen, 1);
  INSERT INTO test_table (identity_column, col1, col2) VALUES (:generated_id, :val1, :val2);
  SUSPEND;
END $$

SET TERM ; $$

/* call the above procedure like this from code (PHP):
SELECT generated_id FROM proc_insert('a', 'b');
of course you can prepare and use placeholders/bind params for the proc parameters
*/

#!/bin/sh

# This file will run a backup of your desired MySQL database and
# remove any backups older than 7 days.
#
# If youOd like to preserve backups for longer than a week, like say 
# 2 weeks, then set the '-mtime' value from '+7' to '+14'.
#

TIME_STAMP=`date "+%Y-%m-%d"`
echo "starting "$0" on" `date`
for db in db1 db2 db3
do
  DB_STAMP=${db}_${TIME_STAMP}
  echo ${DB_STAMP}
  /opt/csw/mysql5/bin/mysqldump --opt --skip-add-locks --user=username --password=password ${db} | gzip > /domains/backups/mysql/${DB_STAMP}.gz
  /opt/csw/bin/mutt -s "mysql  ${TIME_STAMP}"  -a /domains/backups/mysql/${DB_STAMP}.gz someuser@somedomain.com </dev/null
done

cd /domains/backups/mysql/

/usr/bin/find *.gz -mtime +14 -exec rm {} \;
echo "finished "$0" on" `date`

DBCC CHECKIDENT (CustomerContactType, RESEED, XXX)

update orders set statusid = 1,currentqueueid = 1, ntuser = null,checkedout = null where orderid = 19111067

select
	convert(varchar(6),n.notedate) as [date],count(*) as cnt
into
	#temp
from
	orders o inner join	
	orderdetail od on o.orderid = od.orderid inner join
	ordernotes n on o.orderid = n.orderid
where
	od.jurisdictionid = 3152 and
	od.serviceid in (3,4,12,13,15,18) and
	(n.noteid = 18 and n.notedate between '1/1/2006 12:01 AM' and '12/31/2006 11:59 PM')
	--(n.noteid = 129 and n.notedate between '1/1/2006 12:01 AM' and '12/31/2006 11:59 PM')
group by
	convert(varchar(6),n.notedate)
order by
	convert(varchar(6),n.notedate)

select * from #temp
compute sum(cnt)
drop table #temp

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name,syscolumns.colid

SELECT column,COUNT(*) c FROM table
GROUP BY column HAVING COUNT(*) > 1