SQL

Duplicate Id in SQL server Database
select * from t_Company where id in ( select id from
t_Company group by id having Count(*) > 1)

Backup of SQL Server Data through batch file
BackUp: In Backup.bat file, type ==>
c:
del alamPlus.dat
sqlcmd -i c:\backup\backup.sql

In the location <C:\Backup> make a sql file <backup.sql>In this type >>>
EXEC sp_addumpdevice ‘disk’, ‘alamPlusDump’, ‘c:\alamPlus’
BACKUP database alamPlus TO alamPlusDump
GO

SQL Warning
The table ‘t_info’ has been created but its maximum row size (128081) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Procedure-Creating procedure
==========================
create procedure CutRprt
@m_finyr varchar(21),
@m_godcd varchar(10),
@m_docdt datetime
as
select x.finyr,x.itemcd, y.descr as Item_name,z.sorderno,sum(abs(z.qty))as Qty_Order,sum(abs(x.qty)) as Qty_Prod_Tday,x.uom,0.00 as Qty_Prod_MtoDate,
0.00 as Qty_Recv_Tday,0.00 as Qty_Recv_MtoDate,0.00 as Qty_Iss_Tday,0.00 as Qty_Iss_MtoDate,
0.00 as Tday_Recv_Fab,0.00 as MtoDates_Recv_Fab
into cutt_prod from tfa_it x, m_item y, tfa_wodtl z
where x.finyr=@m_finyr and x.docdt=@m_docdt
and x.tt=’GPROD’ and x.godcd=@m_godcd and x.itemcd=y.itemcd
group by x.finyr,x.itemcd,y.descr,z.sorderno,x.uom
union all
select x.finyr,x.itemcd,y.descr as Item_name,z.sorderno,0.00 as Qty_Order,0.00 as Qty_Prod_Tday,x.uom,sum(abs(x.qty))as Qty_Prod_MtoDate,
0.00 as Qty_Recv_Tday,0.00 as Qty_Recv_MtoDate,0.00 as Qty_Iss_Tday,0.00 as Qty_Iss_MtoDate,
0.00 as Tday_Recv_Fab,0.00 as MtoDates_Recv_Fab
from tfa_it x, m_item y, tfa_wodtl z
where x.finyr=@m_finyr and x.docdt <= @m_docdt and datepart(mm,x.docdt)= datepart(mm,@m_docdt)
and x.tt = ‘GPROD’ and x.godcd = @m_godcd and x.itemcd = y.itemcd and
x.itemcd+x.orderno = z.itemcd+z.wono
group by x.finyr,x.itemcd,y.descr,z.sorderno,x.uom
union all
select x.finyr,x.itemcd,y.descr as Item_name,z.sorderno,0.00 as Qty_Order,0.00 as Qty_Prod_Tday,x.uom,0.00 as Qty_Prod_MtoDate,
0.00 as Qty_Recv_Tday,0.00 as Qty_Recv_MtoDate,sum(abs(x.qty)) as Qty_Iss_Tday,0.00 as Qty_Iss_MtoDate,
0.00 as Tday_Recv_Fab, 0.00 as MtoDates_Recv_Fab
from tfa_it x, m_item y, tfa_wodtl z
where x.finyr=@m_finyr and x.docdt= @m_docdt
and x.tt=’ITRAN’ and x.godcd=@m_godcd and x.itemcd=y.itemcd and
x.itemcd+x.orderno=z.itemcd+z.wono
group by x.finyr,x.itemcd,y.descr,z.sorderno,x.uom

union all
select x.finyr,x.itemcd,y.descr as Item_name,z.sorderno,0.00 as Qty_Order,0.00 as Qty_Prod_Tday,x.uom,0.00 as Qty_Prod_MtoDate,
0.00 as Qty_Recv_Tday,0.00 as Qty_Recv_MtoDate,0.00 as Qty_Iss_Tday,sum(abs(x.qty)) as  Qty_Iss_MtoDate,
0.00 as Tday_Recv_Fab,0.00 as MtoDates_Recv_Fab
from tfa_it x, m_item y, tfa_wodtl z
where x.finyr=@m_finyr and x.docdt<=@m_docdt and datepart(mm,x.docdt)=datepart(mm,@m_docdt)
and x.tt=’ITRAN’ and x.godcd=@m_godcd and x.itemcd=y.itemcd and
x.itemcd+x.orderno=z.itemcd+z.wono
group by x.finyr,x.itemcd,y.descr,z.sorderno,x.uom

execute CutRprt ’01-07-2007-30-06-2008′,’CTPF’,’2007-07-01 00:00:00.000′

drop table cutt_prod
drop procedure CutRprt
select * from cutt_prod

SQL function for accessing database name, availalable table, procedures
sp_tables
sp_stored_procedures
sp_helpdb

SQL Server;Database in SQL server after fresh installation
Master,model,msdb,Northwind,pubs,tempdb.
 Trigger
create  trigger connect_restriction
after logon on database
when (user = ‘mady’)
declare
v_username number;
Begin
select count(*)
into v_username
from v$session
where username=’mady’
and status=’ACTIVE’
and upper(program) like ‘SQLPLUS%’;
if v_username > 0 then
raise_application_error(-20002,’no sqlplus please’);
end if;
end ;
create table logon_tbl (who varchar2(30),machine varchar2(60),when date);

create or replace trigger trg_logon_db
after logon on database
begin
declare
machine varchar2(60);

machine=select machine from v$session where username = (select user from dual)
insert into logon_tbl (who,machine, when) values (user,machine,sysdate);
end;

select machine from v$session where username = (select user from dual)

IN ORDER TO CLEAR YOUR SCREEN IN SQL, JUST TYPE clear screen.=>sohail<=

Facebook page-From the Heart of Bangladesh