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

  1. I love looking through and I think this website got some truly useful stuff on it!

    Like

  2. I was reading through some of your blog posts on this site and I think this internet site is rattling informative! Continue posting.

    Like

  3. I like this weblog very much, Its a real nice place to read and obtain information. “The absence of war is not peace.” by Harry S Truman.

    Like

  4. As I website possessor I think the subject matter here is rattling superb , regards for your efforts.

    Like

  5. I carry on listening to the news broadcast lecture about getting boundless online grant applications so I have been looking around for the best site to get one. Could you tell me please, where could i get some?

    Like

  6. Some genuinely prize blog posts on this internet site , saved to favorites .

    Like

  7. I gotta bookmark this site it seems extremely helpful invaluable

    Like

  8. I really enjoy looking through on this internet site , it holds fantastic content .

    Like

  9. Great work! That is the type of information that are supposed to be shared around the web. Shame on the search engines for not positioning this post higher! Come on over and visit my website . Thanks =)

    Like

  10. Hello, i believe that i saw you visited my blog thus i came to “go back the want”.I’m attempting to find things to improve my web site!I suppose its good enough to make use of a few of your ideas!!

    Like

  11. Within YouTube video embed script you can also stipulate parameters according to your desire like width, height or even border colors.

    Like

  12. Hi all, here every one is sharing such experience, thus it’s nice to read this webpage, and I used to pay a quick visit this blog every day.

    Like

  13. I like this site very much so much excellent info.

    Like

  14. Thank you for the auspicious writeup. It in fact used to be a enjoyment account it. Glance advanced to far added agreeable from you! However, how can we communicate?

    Like

  15. You are my aspiration , I possess few web logs and very sporadically run out from to post .

    Like

  16. Yo! Interesting post! I’m also a ordinary website visitor (far like addict :P) on your website unfortunately I had a is sue. I am only not really certain that whether it is the right place to ask, but you have no spam comments. I receive comments often. Possibly can you assist me? Thanks for the tips!

    Like

  17. Yay google is my queen aided me to find this great site! . “The worst sin – perhaps the only sin – passion can commit, is to be joyless.” by Dorothy L. Sayers.

    Like

  18. Some really fantastic posts on this internet site , thanks for contribution.

    Like

  19. I was looking through some of your blog posts on this internet site and I think this internet site is rattling informative ! Keep posting .

    Like

  20. Somebody necessarily lend a hand to make seriously posts I would state. That is the first time I frequented your website page and up to now? I amazed with the research you made to create this actual submit amazing. Magnificent process!

    Like

  21. I do accept as true with all of the concepts you have introduced to your post. They are very convincing and can definitely work. Still, the posts are very quick for newbies. Could you please lengthen them a little from next time? Thank you for the post.

    Like

  22. Thank you, I have just been looking for information approximately this topic for a long time and yours is the greatest I’ve discovered so far. But, what about the conclusion? Are you sure about the source?

    Like

  23. I absolutely love your blog and find nearly all of your post’s to be just what I’m looking for. Would you offer guest writers to write content for you? I wouldn’t mind creating a post or elaborating on a lot of the subjects you write in relation to here. Again, awesome website!

    Like

  24. Lovely website! I am loving it!! Will come back again. I am taking your feeds also

    Like

  25. Really great info can be found on web site.

    Like

  26. Keep up the good work , I read few articles on this site and I conceive that your weblog is really interesting and contains bands of wonderful information.

    Like

  27. I love the efforts you have put in this, thanks for all the great content.

    Like

  28. As I website owner I believe the subject material here is very superb , regards for your efforts.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: