tag:blogger.com,1999:blog-42249811549516437832024-02-20T13:46:39.747-08:00Tricks and Info on SQL Server and .NET Technologies...! -By HibsBy:Chandrakant Hibare- Software Engineer,Working with Spain Based Company,Bangalore****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.comBlogger33125tag:blogger.com,1999:blog-4224981154951643783.post-41525569607026874292014-12-05T11:55:00.001-08:002014-12-05T11:55:47.594-08:00.NET Interview questions for experiences by another blogger Pavan<br />
http://pawantechit.blogspot.in/2013/03/interview-question-for-3-6-year.html****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com8tag:blogger.com,1999:blog-4224981154951643783.post-91210106932693730562014-11-12T00:43:00.003-08:002014-11-12T00:43:25.052-08:00Generate PDF file in MVC using itextsharp dll<br />
<br />
//pdf file Generate Code <br /> Document pdfDoc = new Document(PageSize.A4, 25, 10, 10, 10);<br /> PdfWriter pdfWriter = PdfWriter.GetInstance(pdfDoc, Response.OutputStream);<br /> PdfDestination pdfDest = new PdfDestination(PdfDestination.XYZ, 0, pdfDoc.PageSize.Height, 0.99f);<br /> pdfDoc.Open();<br /><br /> <br /> Font font11 = FontFactory.GetFont("ARIAL", 11);<br /> Font font12 = FontFactory.GetFont("ARIAL", 11,Font.UNDERLINE, iTextSharp.text.BaseColor.RED);<br /> Font font20 = FontFactory.GetFont("ARIAL", 20);<br /><br /> iTextSharp.text.Image jpg = iTextSharp.text.Image.GetInstance(Server.MapPath("~/Images/BBClogo.jpg"));<br /> jpg.ScaleToFit(121F, 131F);<br /> jpg.Alignment = Image.ALIGN_RIGHT;<br /> pdfDoc.Add(jpg);<br /><br /> Paragraph text = new Paragraph(20f, "Commercial Invoice", font20);<br /> text.Alignment = Element.ALIGN_CENTER;<br /> pdfDoc.Add(text);<br /><br /> PdfPTable pdftable1 = new PdfPTable(4);<br /><br /> float[] widthstab1 = new float[] { 1f, 2f, 1f, 2f };<br /> pdftable1.SetWidths(widthstab1);<br /> pdftable1.HorizontalAlignment = 1;<br /> pdftable1.SpacingBefore = 20f;<br /> pdftable1.SpacingAfter = 10f;<br /><br /><br /> PdfPCell cell = new PdfPCell();<br /> pdftable1.AddCell("Sender’s name:");<br /> pdftable1.AddCell(objInternationalBookModels.senderName);<br /> pdftable1.AddCell("Invoice No:");<br /> pdftable1.AddCell(" ");<br /> pdftable1.AddCell("Sender’s Address:");<br /><br /><br /> var pCollAdd = new Paragraph();<br /><br /> if (objInternationalBookModels.collectionCompany != "" && objInternationalBookModels.collectionCompany != null)<br /> {<br /> pCollAdd.Add(objInternationalBookModels.collectionCompany + "," + "\n");<br /> }<br /> if (objInternationalBookModels.collectionaddress1 != "" && objInternationalBookModels.collectionaddress1 != null)<br /> {<br /> pCollAdd.Add(objInternationalBookModels.collectionaddress1 + "," + "\n");<br /> }<br /> if (objInternationalBookModels.collectionaddress2 != "" && objInternationalBookModels.collectionaddress2 != null)<br /> {<br /> pCollAdd.Add(objInternationalBookModels.collectionaddress2 + "," + "\n");<br /> }<br /> if (objInternationalBookModels.collectiontown != "" && objInternationalBookModels.collectiontown != null)<br /> {<br /> pCollAdd.Add(objInternationalBookModels.collectiontown + "," + "\n");<br /> }<br /> if (objInternationalBookModels.collectionpostcode != "" && objInternationalBookModels.collectionpostcode != null)<br /> {<br /> pCollAdd.Add(objInternationalBookModels.collectionpostcode + "," + "\n");<br /> }<br /> if (objInternationalBookModels.CountryName != "" && objInternationalBookModels.CountryName != null)<br /> {<br /> pCollAdd.Add(objInternationalBookModels.CountryName);<br /> }<br /><br /> PdfPCell celsendadd = new PdfPCell(pCollAdd);<br /> celsendadd.Colspan = 2;<br /> pdftable1.AddCell(celsendadd);<br /><br /> PdfPTable nested = new PdfPTable(2);<br /> nested.AddCell("HAWB:");<br /> nested.AddCell(objInternationalBookModels.HAWBNumber);<br /> nested.AddCell("Date:");<br /> nested.AddCell(objInternationalBookModels.collectionDate);<br /> nested.AddCell("Pieces:");<br /> nested.AddCell(objInternationalBookModels.totalNoofpieces.ToString());<br /> nested.AddCell("Weight:");<br /> nested.AddCell(objInternationalBookModels.totalweight.ToString());<br /> nested.AddCell("Currency:");<br /> nested.AddCell("GBP");<br /> PdfPCell nesthousing = new PdfPCell(nested);<br /> nesthousing.Padding = 0f;<br /> pdftable1.AddCell(nesthousing);<br /><br /> pdftable1.AddCell("Consignee Name:");<br /> PdfPCell celDelConname = new PdfPCell(new Phrase(objInternationalBookModels.deliveryContactname));<br /> celDelConname.Colspan = 3;<br /> pdftable1.AddCell(celDelConname);<br /><br /> pdftable1.AddCell("Delivery Address:");<br /><br /> var pDelAdd = new Paragraph();<br /> if (objInternationalBookModels.deliveryCompany != "" && objInternationalBookModels.deliveryCompany != null)<br /> {<br /> pDelAdd.Add(objInternationalBookModels.deliveryCompany + "," + "\n");<br /> }<br /> if (objInternationalBookModels.deliveryaddress1 != "" && objInternationalBookModels.deliveryaddress1 != null)<br /> {<br /> pDelAdd.Add(objInternationalBookModels.deliveryaddress1 + "," + "\n");<br /> }<br /> if (objInternationalBookModels.deliveryaddress2 != "" && objInternationalBookModels.deliveryaddress2 != null)<br /> {<br /> pDelAdd.Add(objInternationalBookModels.deliveryaddress2 + "," + "\n");<br /> }<br /> if (objInternationalBookModels.deliverytown != "" && objInternationalBookModels.deliverytown != null)<br /> {<br /> pDelAdd.Add(objInternationalBookModels.deliverytown + "," + "\n");<br /> }<br /> if (objInternationalBookModels.deliverypostcode != "" && objInternationalBookModels.deliverypostcode != null)<br /> {<br /> pDelAdd.Add(objInternationalBookModels.deliverypostcode + "," + "\n");<br /> }<br /> if (objInternationalBookModels.deliveryCountryName != "" && objInternationalBookModels.deliveryCountryName != null)<br /> {<br /> pDelAdd.Add(objInternationalBookModels.deliveryCountryName);<br /> }<br /><br /> PdfPCell celdeladd = new PdfPCell(pDelAdd);<br /> celdeladd.Colspan = 3;<br /> pdftable1.AddCell(celdeladd);<br /><br /> pdftable1.AddCell("Telephone:");<br /> pdftable1.AddCell(objInternationalBookModels.deliveryContactNo);<br /> pdftable1.AddCell("Shipped via:");<br /> pdftable1.AddCell(objInternationalBookModels.SupplierName);<br /> pdfDoc.Add(pdftable1);<br /><br /> if (objInternationalBookModels.lstContentdetails != null && objInternationalBookModels.lstContentdetails.Count > 0)<br /> {<br /> PdfPTable pdftable2 = new PdfPTable(4);<br /> float[] widthstab2 = new float[] { 1f, 2f, 1f, 2f };<br /> pdftable2.SetWidths(widthstab2);<br /> pdftable2.HorizontalAlignment = 1;<br /> pdftable2.SpacingBefore = 20f;<br /> // pdftable2.SpacingAfter = 20f;<br /> Decimal totalShipmentValue = 0;<br /> pdftable2.AddCell("Quantity");<br /> pdftable2.AddCell("Contents");<br /> pdftable2.AddCell("Item Value");<br /> pdftable2.AddCell("Total Value");<br /><br /><br /> for (int i = 0; i < objInternationalBookModels.lstContentdetails.Count; i++)<br /> {<br /> pdftable2.AddCell(objInternationalBookModels.lstContentdetails[i].quantity.ToString());<br /> pdftable2.AddCell(objInternationalBookModels.lstContentdetails[i].content.ToString());<br /> pdftable2.AddCell(objInternationalBookModels.lstContentdetails[i].totalvalue.ToString());<br /> Decimal TotalXValue = objInternationalBookModels.lstContentdetails[i].quantity * objInternationalBookModels.lstContentdetails[i].totalvalue;<br /> pdftable2.AddCell(TotalXValue.ToString());<br /><br /> totalShipmentValue = totalShipmentValue + TotalXValue;<br /> }<br /> pdfDoc.Add(pdftable2);<br /><br /><br /><br /> PdfPTable pdfTotalValue = new PdfPTable(4);<br /> float[] widthsT = new float[] { 1f, 2f, 1f, 2f };<br /> pdfTotalValue.SetWidths(widthsT);<br /> pdfTotalValue.HorizontalAlignment = 1;<br /><br /> BaseColor basecol = new BaseColor(System.Drawing.Color.Red);<br /> Font fontTotalvalue = FontFactory.GetFont("ARIAL", 12, basecol);<br /> PdfPCell cellTotalValue = new PdfPCell(new Phrase("Total Shipment Value", fontTotalvalue));<br /> cellTotalValue.Colspan = 3;<br /> cellTotalValue.HorizontalAlignment = Element.ALIGN_RIGHT;<br /> pdfTotalValue.AddCell(cellTotalValue);<br /> pdfTotalValue.AddCell(totalShipmentValue.ToString());<br /><br /> pdfDoc.Add(pdfTotalValue);<br /> }<br /><br /> if (objInternationalBookModels.producttype != null && objInternationalBookModels.producttype == "Document")<br /> {<br /> PdfPTable pdftable3 = new PdfPTable(4);<br /> float[] widths = new float[] { 1f, 2f, 1f, 2f };<br /> pdftable3.SetWidths(widths);<br /> pdftable3.HorizontalAlignment = 1;<br /> pdftable3.SpacingBefore = 20f;<br /> //pdftable3.SpacingAfter = 20f;<br /><br /> pdftable3.AddCell("Quantity");<br /> pdftable3.AddCell("Contents");<br /> pdftable3.AddCell("Item Value");<br /> pdftable3.AddCell("Total Value");<br /> pdftable3.AddCell(" ");<br /> pdftable3.AddCell(" ");<br /> pdftable3.AddCell(" ");<br /> pdftable3.AddCell(" ");<br /><br /> pdftable3.AddCell(" ");<br /> pdftable3.AddCell(" ");<br /> pdftable3.AddCell(" ");<br /> pdftable3.AddCell(" ");<br /><br /> pdfDoc.Add(pdftable3);<br /><br /><br /> PdfPTable pdfTotalValueDoc = new PdfPTable(4);<br /> float[] widthsT = new float[] { 1f, 2f, 1f, 2f };<br /> pdfTotalValueDoc.SetWidths(widthsT);<br /> pdfTotalValueDoc.HorizontalAlignment = 1;<br /><br /> BaseColor basecol = new BaseColor(System.Drawing.Color.Red);<br /> Font fontTotalvalue = FontFactory.GetFont("ARIAL", 12, basecol);<br /> PdfPCell cellTotalValue = new PdfPCell(new Phrase("Total Shipment Value", fontTotalvalue));<br /> cellTotalValue.Colspan = 3;<br /> cellTotalValue.HorizontalAlignment = Element.ALIGN_RIGHT;<br /> pdfTotalValueDoc.AddCell(cellTotalValue);<br /> pdfTotalValueDoc.AddCell(" ");<br /> pdfDoc.Add(pdfTotalValueDoc);<br /><br /> }<br /><br /> PdfPTable pdftable4 = new PdfPTable(2);<br /> float[] widthstab4 = new float[] { 1f, 2f };<br /> pdftable4.SetWidths(widthstab4);<br /> pdftable4.HorizontalAlignment = 1;<br /><br /> pdftable4.SpacingBefore = 20f;<br /> pdftable4.SpacingAfter = 30f;<br /> pdftable4.AddCell("Senders Vat No:");<br /> pdftable4.AddCell(" ");<br /> pdftable4.AddCell("Reason For Export:");<br /> pdftable4.AddCell(" ");<br /> pdftable4.AddCell("ECO Licence");<br /> pdftable4.AddCell(" ");<br /> pdftable4.AddCell("Terms of Delivery:");<br /> pdftable4.AddCell(" ");<br /> pdfDoc.Add(pdftable4);<br /><br /><br /> //Chunk chnk = new Chunk("I declare the above information is true and correct to the best of my knowledge.\n", font11);<br /> //chnk.SetUnderline(0.1f, -2f);<br /> Paragraph text2 = new Paragraph("I declare the above information is true and correct to the best of my knowledge.\n", font11);<br /> pdfDoc.Add(text2);<br /><br /> Paragraph text5 = new Paragraph("Signed for on behalf of the shipper.\n", font11);<br /> pdfDoc.Add(text5);<br /> Paragraph text3 = new Paragraph("Name (in print):\n\n\n", font11);<br /> pdfDoc.Add(text3);<br /> Paragraph text4 = new Paragraph("Signature:", font11);<br /> pdfDoc.Add(text4);<br /><br /> PdfAction pdfAct = PdfAction.GotoLocalPage(1, pdfDest, pdfWriter);<br /> pdfWriter.SetOpenAction(pdfAct);<br /><br /> pdfWriter.CloseStream = false;<br /> pdfDoc.Close();<br /> Response.Buffer = true;<br /> Response.ContentType = "Application/pdf";<br /> Response.AddHeader("content-disposition", "attachment;filename=Commercial_Invoice_template_2014.pdf");<br /> Response.Cache.SetCacheability(HttpCacheability.NoCache);<br /> Response.Write(pdfDoc);<br /> Response.End();<br />
<br />****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-64196895231148398922010-03-29T03:14:00.000-07:002010-03-29T03:14:44.915-07:00Blogger Buzz: Blogger integrates with Amazon Associates<a href="http://buzz.blogger.com/2009/12/blogger-integrates-with-amazon.html">Blogger Buzz: Blogger integrates with Amazon Associates</a>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com1tag:blogger.com,1999:blog-4224981154951643783.post-50514065759223728162010-01-28T23:30:00.000-08:002010-01-29T00:16:08.132-08:00Replication in SQL Server<span style="color: rgb(51, 51, 255); font-weight: bold;"><br />Replication </span><br /><span style="color: rgb(51, 51, 255);">- process of distributing data from one database to another on the same server or servers connected through LAN or the internet.</span><br /><span style="color: rgb(51, 51, 255);">- synchronize data between databases that may be at remote location.</span><br /><span style="color: rgb(51, 51, 255);">- maintain copies of database at more than one locations.</span><br /><span style="color: rgb(51, 51, 255);">- It can be used for load balancing of server by maintaining area specific data independently and replicate later to have single version of data</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Types of Replication</span><br /><br /><span style="color: rgb(51, 51, 255);">1. Snapshot Replication</span><br /><span style="color: rgb(51, 51, 255);">2. Merge Replication</span><br /><span style="color: rgb(51, 51, 255);">3. Transactional Replication</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Snapshot Replication</span><br /><span style="color: rgb(51, 51, 255);">- Snapshot of entire data is copied from publisher to the subscriber's database on regular </span><br /><span style="color: rgb(51, 51, 255);"> interval.</span><br /><span style="color: rgb(51, 51, 255);">- This kind of replication is used for replicating data that doesn’t change frequently.</span><br /><span style="color: rgb(51, 51, 255);">- It is used where the source can manage with out of date data.</span><br /><span style="color: rgb(51, 51, 255);">- This replication is considered when amount of data to be replicated is small.</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Merge Replication</span><br /><span style="color: rgb(51, 51, 255);">- This allows both publisher and subscriber to work independently, online or offline and merges </span><br /><span style="color: rgb(51, 51, 255);"> the changes later.</span><br /><span style="color: rgb(51, 51, 255);">- Here changes are track on both publisher and subscriber and then merged.</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Transactional Replication</span><br /><span style="color: rgb(51, 51, 255);">- This kind is used when changes are frequent.</span><br /><span style="color: rgb(51, 51, 255);">- The replication agent monitors the changes at the publisher and distributes the changes to the </span><br /><span style="color: rgb(51, 51, 255);"> subscribers.</span><br /><span style="color: rgb(51, 51, 255);">- Its required where up to date data is required.</span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-35517179716860412832010-01-28T23:13:00.000-08:002010-01-29T01:50:00.848-08:00Cursors in SQL Server<span style="color: rgb(51, 51, 255); font-weight: bold;"><br />Cursors </span><br /><span style="color: rgb(51, 51, 255);">-It is a special programming construct that allows data to be manipulated on a row-by-row basis.</span><br /><span style="color: rgb(51, 51, 255);">-They point to a certain location within a record set and allow the operator to move forward (and sometimes backward, depending upon the cursor type) through the results one record at a time.</span><br /><span style="color: rgb(51, 51, 255);">-A cursor is used to access the result set stored in the memory on execution of a query.</span><br /><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Steps to use Transact-SQL Cursor.</span><br /><br /><span style="color: rgb(51, 51, 255);">Declare the cursor,</span><br /><span style="color: rgb(51, 51, 255);">Open the cursor,</span><br /><span style="color: rgb(51, 51, 255);">Fetch record row by row,</span><br /><span style="color: rgb(51, 51, 255);">Close cursor,</span><br /><span style="color: rgb(51, 51, 255);">Deallocate cursor.</span><br /><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Example of a cursor</span><br /><br /><span style="color: rgb(51, 51, 255);">Declare @EmpId int</span><br /><span style="color: rgb(51, 51, 255);">Declare curEmp CURSOR READ_ONLY FOR SELECT EmpId FROM Employee</span><br /><span style="color: rgb(51, 51, 255);">Open curEmp</span><br /><span style="color: rgb(51, 51, 255);">Fetch next from curEmp into @EmpId</span><br /><span style="color: rgb(51, 51, 255);">While @@FETCH_STATUS = 0</span><br /><span style="color: rgb(51, 51, 255);">Begin</span><br /><span style="color: rgb(51, 51, 255);"> Print @EmpId </span><br /><span style="color: rgb(51, 51, 255);"> Fetch next from curEmp into @EmpId</span><br /><span style="color: rgb(51, 51, 255);">End</span><br /><span style="color: rgb(51, 51, 255);">Close curEmp</span><br /><span style="color: rgb(51, 51, 255);">Deallocate curEmp</span><br /><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Different types of cursors</span><br /><br /><span style="color: rgb(51, 51, 255);">1. DYNAMIC: It reflects changes happened on the table while scrolling through the row.</span><br /><br /><span style="color: rgb(51, 51, 255);">2. STATIC: It works on snapshot of record set and disconnects from the server. This kind </span><br /><span style="color: rgb(51, 51, 255);"> doesn’t reflects changes happened on the table while scrolling through the row.</span><br /><br /><span style="color: rgb(51, 51, 255);">3.KEYSET: In this kind, new record is not reflected, but data modification can be seen.</span><br /><span style="color: rgb(51, 51, 255);"> Define the cursor lock types.</span><br /><br /><span style="color: rgb(51, 51, 255);">SQL Server supports three types of cursor namely Transact-SQL server cursor, API server cursor, and client cursor.</span><br /><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Disadvantages/limitation of the cursor</span><br /><span style="color: rgb(51, 51, 255);">- consume network resources.</span><br /><span style="color: rgb(51, 51, 255);">- Close cursor when it is not required.</span><br /><span style="color: rgb(51, 51, 255);">- You shouldn’t forget to deallocate cursor after closing it.</span><br /><span style="color: rgb(51, 51, 255);">- Cursor requires a network roundtrip each time it fetches a record.</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Avoid cursors</span><br /><span style="color: rgb(51, 51, 255);">- Using the SQL while loop</span><br /><span style="color: rgb(51, 51, 255);">- User defined functions</span><br /><span style="color: rgb(51, 51, 255);">- Using CTE(Common Table Expression) Using WITH Clause.</span><br /><span style="color: rgb(51, 51, 255);"> -Temporary table</span><br /><span style="color: rgb(51, 51, 255);">- Table variable</span><br /><span style="color: rgb(51, 51, 255);">- You should fetch least number of records.</span><br /><span style="color: rgb(51, 51, 255);">- You should use FORWARD ONLY option when there is no need to update rows.</span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com1tag:blogger.com,1999:blog-4224981154951643783.post-73622183625525002092010-01-18T04:27:00.000-08:002010-01-18T04:41:24.418-08:00Differences between User Defined Functions and Stored Procedures<span id="ctl00_ContentPlaceHolder1_lblDescription" style="color:DarkBlue;"> <span style="font-size:180%;"><span style="font-weight: bold;"><br />User Defined Functions</span></span><br /><br /><span style="font-size:100%;">1 UDF are simpler to invoke than Stored Procedures from inside another SQL statement.<br />2 SP's are Complex to Invoke.<br />3 UDF's must be prefaced with the owner name, DBO in this case. Not mandatory.<br />4 When call function then the parameter must transmit full.<br />5 A UDF is always used to return a value or a table object.<br />6 Function return type could be scalar or table or table values(SQL Server).<br /></span></span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >7 Function returns only one value at a time</span><span style="font-size:100%;"><br /></span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >8 </span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >We can call the functions in SQL statements (select max(sal) from emp)<br />9 </span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >Function do not return the images, text.<br />10 </span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >Functions are used for computations.<br />11 </span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >Functions MUST return a value.</span><span style="font-size:100%;"><br /></span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >12 </span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >Function parameters are always IN.</span><span style="font-size:100%;"><br />13 </span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >UDF can be used in the SQL Statements anywhere in the WHERE/HAVING/SELECT section.<br />14 </span><span id="ctl00_ContentPlaceHolder1_lblDescription" style=";font-size:100%;color:DarkBlue;" >UDF's only can have input parameters.</span><br /><br /><br /><span id="ctl00_ContentPlaceHolder1_lblDescription" style="color:DarkBlue;"> <span style="font-weight: bold;font-size:180%;" > Stored Procedures</span></span><br /><br /><span id="ctl00_ContentPlaceHolder1_lblDescription" style="color:DarkBlue;">1 </span><span id="ctl00_ContentPlaceHolder1_lblDescription" style="color:DarkBlue;">Stored Procedure is pre compiled execution plan Functions are not precompiled.</span><br /><span id="ctl00_ContentPlaceHolder1_lblDescription" style="color:DarkBlue;">2 Stored procedure returns always integer value by default zero.<br />3 Stored Procedure returns more than one value at a time.<br />4 We cannot call Stored Procedures in SQL Statements.<br />5 Function do not return the images, text. Stored Procedure returns all.<br />6 Procedures can be used for performing business logic.<br />7 Functions MUST return a value. Procedures need not be.<br />8 Function parameters are always IN, no OUT is possible Stored procedures can have input and output parameters.<br />9 </span><span id="ctl00_ContentPlaceHolder1_lblDescription" style="color:DarkBlue;">Stored procedures cannot be used</span><span id="ctl00_ContentPlaceHolder1_lblDescription" style="color:DarkBlue;"> WHERE/HAVING/SELECT section .<br /><br /></span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-25085373189363134402009-11-30T02:56:00.000-08:002009-11-30T03:25:03.981-08:00Normalization and DeNormalization,fulltext definition functions,find index, non index tables in sql server<span style="color: rgb(51, 51, 255); font-weight: bold;"><br />Normalization</span><br /><br /><span style="color: rgb(51, 51, 255);">In Relational database design,the process of organizing data to minimize redundancy is called Normalization.</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">DeNormalization</span><br /><br /><span style="color: rgb(51, 51, 255);">It is the process of optimize the performance of a database by adding redundant data.</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Different Normalization Forms</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">1 NF</span><br /><span style="color: rgb(51, 51, 255);"> </span><br /><span style="color: rgb(51, 51, 255);">Eliminate Repeating groups</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">2NF</span><br /><br /><span style="color: rgb(51, 51, 255);">Eliminate Redundant Data</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">3NF</span><br /><br /><span style="color: rgb(51, 51, 255);">Eliminate Columns which are not dependent on key column</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">BCNF</span><br /><br /><span style="color: rgb(51, 51, 255);">Eliminate non-trivial dependencies in candidate key attribute then separate it</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">4NF</span><br /><br /><span style="color: rgb(51, 51, 255);">Isolate independent multiple relationship</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">5NF</span><br /><br /><span style="color: rgb(51, 51, 255);">Isolate semantically related multiple relationship(Separate Many-Many Relationship)</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">ONF</span><br /><br /><span style="color: rgb(51, 51, 255);">A Model is limited as express object role model</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">DKNF</span><br /><br /><span style="color: rgb(51, 51, 255);">A model free from all modification anomalies</span><br /><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Trick to find all index tables in a database</span><br /><br /><span style="color: rgb(51, 51, 255);">SELECT NAME FROM SYS.TABLES WHERE OBJECTPROPERTY(OBJECT_ID,'ISINDEXED')=1</span><br /><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Trick to find all non-index tables in a database</span><br /><br /><br /><span style="color: rgb(51, 51, 255);"> SELECT NAME FROM SYS.TABLES WHERE OBJECTPROPERTY(OBJECT_ID,'ISINDEXED')=0</span><br /><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">Fulltext definition functions with example</span><br /><br /><br /><span style="color: rgb(51, 51, 255);">SELECT EMP_NAME FROM EMPLOYEE , CONTAINSTABLE(EMPLOYEE,EMP_NAME,'CHANDR') AS TabName</span><br /><br /><br /><span style="color: rgb(51, 51, 255);">SELECT EMP_NAME FROM EMPLOYEE WHERE FREETEXT(EMP_NAME,'CHAN')</span><br /><br /><span style="color: rgb(51, 51, 255);">SELECT EMP_NAME FROM EMPLOYEE WHERE CONTAINS(EMP_NAME,'CHAN')</span><br /><br /><span style="color: rgb(51, 51, 255);">SELECT RANK()OVER(ORDER BY Description),Description FROM EMPLOYEE</span><br /><br /><span style="color: rgb(51, 51, 255); font-weight: bold;">How to find current version of the sql server</span><br /><br /><span style="color: rgb(51, 51, 255);">SELECT @@VERSION</span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-40063784556491594682009-11-24T01:54:00.000-08:002009-12-06T21:53:47.580-08:00Remove duplicate records from table in sql server<span style="color: rgb(0, 0, 153);font-family:verdana;" ></span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >Query shows how to select duplicate records with SL No.</span><br /><span style="color: rgb(0, 0, 153);font-family:Verdana;" ></span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" ></span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >SELECT ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_NAME) SLNO,EMP_NAME FROM EMPLOYEE</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" ></span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" ></span><br /><span style="color: rgb(0, 0, 153);font-family:Verdana;" ></span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >Query for remove duplicate records from table</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" ></span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" ></span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >WITH REMDUPREC</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >AS</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >(</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >SELECT ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_NAME) SLNO,EMP_NAME FROM EMPLOYEE</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >)</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" ></span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >DELETE FROM REMDUPREC WHERE SLNO>1</span><br /><br /><br /><br /><pre style="font-family: verdana; font-weight: bold; color: rgb(51, 51, 255);"><span style="font-size:100%;">delete from <i>MyTable</i><br />where <i>uniqueField </i>not in<br />(select min(<i>uniqueField</i>) from <i>MyTable</i> T2<br />where T2.<i>dupField</i>=<i>MyTable</i>.<i>dupField</i>)</span></pre><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" ></span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com1tag:blogger.com,1999:blog-4224981154951643783.post-60407786051729342009-11-11T04:10:00.000-08:002009-11-11T04:17:06.513-08:00OPENQUERY AND OPENROWSET IN SQL SERVER<p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-weight: bold;font-size:100%;" ><br /></span></p><p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-weight: bold;font-size:100%;" >OPENQUERY:</span><span style="font-size:100%;"> Used to execute the query passed on the linked server.</span></p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:100%;">Syntax: OPENQUERY (Linked_server_identifier, query).<br /></span></p><p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:100%;">It can also be refernced from a FROM clause of selecte query. </span></p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:100%;">e.g. Select * from OPENQUERY (Oracleserver, ‘select fname, FROM Employee);</span></p><p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:100%;"><br /></span></p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-weight: bold;font-size:100%;" >OPENROWSET:</span><span style="font-size:100%;"> Used to access tables in a linked server. It can be used one time for accessing remote data by using OLE DB. It includes all the information required to access the remote data. </span></p> <span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" >Syntax:</span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" > OPENROWSET </span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" > ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' </span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" > | 'provider_string' } </span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" > , { [ catalog. ] [ schema. ] object </span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" > | 'query' </span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" > }</span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" > | BULK 'data_file' , </span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" > { FORMATFILE = 'format_file_path' [ ]</span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" > | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }</span><span style="color: rgb(0, 0, 153);font-size:100%;" ><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" >} ) </span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-58672538695047852272009-11-08T21:18:00.000-08:002009-11-08T21:53:18.616-08:00Advantages and disadvantages of views in sql server<span style="color: rgb(0, 0, 153);"><br /><span style="font-weight: bold;">Advantages of view</span></span><br /><br /><span style="color: rgb(0, 0, 153);">1. Data Security</span><br /><span style="color: rgb(0, 0, 153);"><br />2. Simplicity</span><br /><span style="color: rgb(0, 0, 153);"><br />3. No Space</span><br /><span style="color: rgb(0, 0, 153);"><br />4. Hide data and columns from base tables</span><br /><span style="color: rgb(0, 0, 153);"><br />5. You can easily use a view to create new information out of existing data,</span><span style="color: rgb(0, 0, 153);"> without changing the (legacy) database structure</span><br /><span style="color: rgb(0, 0, 153);"><br />6. Complex queries can be stored in the from of view</span><br /><br /><span style="color: rgb(0, 0, 153); font-weight: bold;">Disadvantages of view</span><br /><br /><span style="color: rgb(0, 0, 153);">1. </span><span style="color: rgb(0, 0, 153);" id="sort1"><span class="tdvamseel"><span arial="" helvetica="" serif="">Querying from view takes more time than directly querying from the table<br /><br />2. I</span></span></span><span style="color: rgb(0, 0, 153);">t will have to do the query a new, every time it is opened</span><br /><br /><span style="color: rgb(0, 0, 153);">3. </span><span style="color: rgb(0, 0, 153);" id="sort1"><span class="tdvamseel">when table is not there view will not work or view become inactive<br /><br />4. View can be create only if there is SELECT Permission on its base table<br /><br />5. views can not derived the data from temp tables<br /><br />6.Query defining view can not include ORDER BY,COMPUTE,INTO Keyword<br /><br />7.You can not define full text index definitions on views</span></span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-68987985832268692972009-10-29T00:22:00.000-07:002009-10-29T01:54:46.744-07:00INSERT Record into TABLE with DEFAULT VALUES and other SQL tricks..!<span style="font-weight: bold; color: rgb(0, 0, 153);font-family:verdana;" ><br /><br /><span style="color: rgb(0, 0, 153);font-size:130%;" >If you want INSERT record INTO TABLE with DEFAULT VALUES without specifying all the values, then here is the trick...</span></span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >SYNTAX:</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >INSERT INTO TABLENAME DEFAULT VALUES</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >EX:</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >INSERT INTO EMPLOYEE DEFAULT VALUES</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >-It will insert all default values specified for the columns in a table.</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >OTHER EX:</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >INSERT INTO TABLENAME SELECT 'VALUE1','VALUE2',...AND SO ON</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >-This will insert values into table without having specify all column names of table,but here in select statement you have specify all values or default values.</span><br /><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >Solution to insert or update record using views,when view holding two joined tables.</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >Ex:</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >-</span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:85%;" >CREATE VIEW</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >CREATE VIEW EUView</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >AS</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >SELECT coalesce(EMPLOYEE.ID,[USER].UID) TABLENAMEID,EMPLOYEE.NAME,[USER].UNAME </span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >FROM EMPLOYEE FULL OUTER JOIN [USER] ON EMPLOYEE.ID=[USER].UID</span><br /><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >-</span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:85%;" >CREATE A TRIGGER ON VIEW</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >CREATE TRIGGER EUTRIGGER</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >ON EUVIEW</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >INSTEAD OF INSERT</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >AS</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >BEGIN</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >INSERT INTO EMPLOYEE(ID,NAME)SELECT TABLENAMEID,NAME FROM INSERTED</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >INSERT INTO [USER](UID,UNAME)SELECT TABLENAMEID,UNAME FROM INSERTED</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >END</span><br /><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >-</span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:85%;" >FOLLOWING SHOWS INSERT A RECORD</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >INSERT INTO EUVIEW SELECT 3,'Test2','Chandu'</span><br /><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:180%;" ><br /><span style="font-weight: bold;font-size:78%;" >TRICK TO FIND DUPLICATE RECORD IN A TABLE</span></span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" >SELECT EMP_NAME,COUNT(EMP_NAME) FROM EMPLOYEE<br />GROUP BY EMP_NAME<br />HAVING COUNT(EMP_NAME)>1<br /><br /><br /></span><span style="color: rgb(0, 0, 153); font-weight: bold;font-family:verdana;font-size:130%;" >TRIGGER FIRING ORDER IN SQL Server</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:verdana;" >Following shows creation of triggers and setting order of trigger firing.</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:100%;" ><br /></span><table style="color: rgb(0, 0, 153);font-family:verdana;" id="table5" width="590" border="1" cellpadding="3" cellspacing="1"><tbody><tr><td><span style="font-size:130%;">set nocount on<br />go<br />create table dbo.customer (customerid int identity primary key)<br />go</span></td></tr> <tr> <td bg="" style="color: rgb(192, 192, 192);"> <p align="center"><b><span style="font-size:85%;">Create trigger 1</span></b></p></td></tr> <tr> <td><span style="font-size:130%;">create trigger dbo.tr1 on dbo.customer<br />for insert<br />as<br />set nocount on<br />print 'firing original trigger 1'<br />go</span></td></tr> <tr> <td bg="" style="color: rgb(192, 192, 192);"> <p align="center"><b><span style="font-size:85%;">Create trigger 2</span></b></p></td></tr> <tr> <td><span style="font-size:130%;">create trigger dbo.tr2 on dbo.customer<br />for insert<br />as<br />set nocount on<br />print 'firing original trigger 2'<br />go</span></td></tr> <tr> <td bg="" style="color: rgb(192, 192, 192);"> <p align="center"><b><span style="font-size:85%;">Sample insert statement</span></b></p></td></tr> <tr> <td><span style="font-size:130%;">insert into dbo.customer default values<br />go<br /><br /><br /></span><span style="font-size:85%;"><span style="font-size:130%;">exec sp_settriggerorder @triggername = 'tr1',<br />@order = 'first',<br />@stmttype = 'insert',<br />@namespace = null<br /><br />exec sp_settriggerorder @triggername = 'tr2',<br />@order = 'last',<br />@stmttype = 'insert',<br />@namespace = null<br />go<br /></span><br /></span></td></tr></tbody></table><br /><br /><span style="color: rgb(0, 0, 153);font-family:Arial;font-size:85%;" ><br /><br /></span><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:180%;" ><span style="font-weight: bold;font-size:85%;" >TRICK TO FIND nth MAX SALARY</span><br /><br /><span style="font-size:78%;">1.<br /><br /></span><span style="font-size:85%;"><span style="font-size:78%;">SELECT top 1 * FROM (SELECT TOP 3 * FROM SALARY ORDER BY SAL_MODE DESC)A ORDER BY SAL_MODE<br /><br />2.<br /><br />SELECT SAL_ID ,SAL_MODE FROM (SELECT row_number() over(order by sal_mode DESC) 'RN', SALARY.SAL_MODE,SALARY.SAL_ID FROM SALARY )A<br />WHERE A.RN=1 ORDER BY SAL_MODE</span><br /><br /></span><br /></span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-10053283835141818732009-10-28T21:34:00.000-07:002009-10-28T21:41:54.763-07:00Languages used in SQL Server<div style="color: rgb(0, 0, 153);" class="post-body"> <div> <p style="text-align: justify;font-family:verdana;"><span style=""><span style="font-weight: bold; font-style: italic;">The Data Definition Language (DDL) includes, </span><o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b><span style="">CREATE TABLE</span></b><span style=""> - creates new database table <o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b><span style="">ALTER TABLE </span></b><span style="">- alters or changes the database table <o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b><span style="">DROP TABLE</span></b><span style=""> - deletes the database table <o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b><span style="">CREATE INDEX</span></b><span style=""> - creates an index or used as a search key <o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b><span style="">DROP INDEX</span></b><span style=""> - deletes an index<br /></span></p><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><br /></p><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><br /><span style=""><o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;"><span style=""><span style="font-weight: bold; font-style: italic;">The Data Manipulation Language (DML) includes,</span><o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b><span style="">SELECT</span></b><span style=""> - extracts data from the database <o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b><span style="">UPDATE</span></b><span style=""> - updates data in the database <o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b><span style="">DELETE</span></b><span style=""> - deletes data from the database <o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> <u1:p></u1:p> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b><span style="">INSERT INTO</span></b><span style=""> - inserts new data into the database</span></p><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><br /></p><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><br /><span style=""><o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><span style=""><span style="font-weight: bold; font-style: italic;">The Data Control Language (DCL) includes,</span><o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b style=""><span style="">GRANT</span></b><span style=""> – gives access privileges to users for database<o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> </div><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><b style=""><span style="">REVOKE</span></b><span style=""> – withdraws access privileges to users for database</span></p><p style="text-align: justify;font-family:verdana;" class="MsoNormal"><br /></p><p face="verdana" style="text-align: justify;" class="MsoNormal"><br /><span style=""><o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> </div><p face="verdana" style="text-align: justify;" class="MsoNormal"><span style=""><span style="font-weight: bold; font-style: italic;">The Transaction Control (TCL) includes,</span><o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> </div><p face="verdana" style="text-align: justify;" class="MsoNormal"><b style=""><span style="">COMMIT</span></b><span style=""> – saves the work done <o:p></o:p></span></p><div style="text-align: justify; font-family: verdana;"> </div><p style="text-align: justify;" class="MsoNormal"><b style="font-family:verdana;"><span style="">ROLLBACK</span></b><span style="color: rgb(0, 0, 0);font-family:verdana;" > - restore the database to original since the last COMMIT</span><span style="font-family:Verdana;"><o:p></o:p></span></p> <p style="text-align: justify;" class="MsoNormal"><span style="font-family:Verdana;"><span style="color: rgb(255, 255, 255);font-family:verdana;font-size:85%;" ></span> <o:p></o:p></span></p></div></div> <!-- End #footer --> <script type="text/javascript"><!-- google_ad_client = "pub-0483585725255531"; google_ad_host = "pub-1556223355139109"; /* 728x15, created 8/12/08 */ google_ad_slot = "7608722114"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-86607278303524618042009-10-28T01:06:00.000-07:002009-10-28T01:09:33.816-07:00Locking in Microsoft SQL Server<h2 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;">Introduction</span></h2> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >In this article, I want to tell you about SQL Server 7.0/2000 Transaction Isolation Levels, what kinds of Transaction Isolation Levels exist, and how you can set the appropriate Transaction Isolation Level, about Lock types and Locking optimizer hints, about deadlocks, and about how you can view locks by using the sp_lock stored procedure.<br /><br /> </span><h2 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;"><a name="part_2"></a>Transaction Isolation Levels</span></h2> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >There are four isolation levels:<br /><br /> <li>READ UNCOMMITTED</li> <li>READ COMMITTED</li> <li>REPEATABLE READ</li> <li>SERIALIZABLE</li><br /><br /> Microsoft SQL Server supports all of these Transaction Isolation Levels and can separate REPEATABLE READ and SERIALIZABLE.<br /><br /> Let me to describe each isolation level.<br /><br /> </span><h3 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;">READ UNCOMMITTED</span></h3> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >When it's used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read.<br /><br /> </span><h3 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;">READ COMMITTED</span></h3> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >This is the default isolation level in SQL Server. When it's used, SQL Server will use shared locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it not ensures that the data will not be changed before the end of the transaction.<br /><br /> </span><h3 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;">REPEATABLE READ</span></h3> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >When it's used, the dirty reads and nonrepeatable reads cannot occur. It means that locks will be placed on all data that is used in a query, and another transactions cannot update the data.<br /><br /> This is the definition of nonrepeatable read from SQL Server Books Online:<br /><br /> <table width="100%"><tbody><tr bgcolor="#dcdcdc"><td><pre>nonrepeatable read<br />When a transaction reads the same row more than one time, and between the<br />two (or more) reads, a separate transaction modifies that row. Because the<br />row was modified between reads within the same transaction, each read<br />produces different values, which introduces inconsistency.<br /></pre></td></tr></tbody></table> </span><h3 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;">SERIALIZABLE</span></h3> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >Most restrictive isolation level. When it's used, the phantom values cannot occur. It prevents other users from updating or inserting rows into the data set until the transaction will be completed.<br /><br /> This is the definition of phantom from SQL Server Books Online:<br /><br /> <table width="100%"><tbody><tr bgcolor="#dcdcdc"><td><pre>phantom<br />Phantom behavior occurs when a transaction attempts to select a row that<br />does not exist and a second transaction inserts the row before the first<br />transaction finishes. If the row is inserted, the row appears as a phantom<br />to the first transaction, inconsistently appearing and disappearing.<br /></pre></td></tr></tbody></table> You can set the appropriate isolation level for an entire SQL Server session by using the SET TRANSACTION ISOLATION LEVEL statement.<br />This is the syntax from SQL Server Books Online:<br /><br /> <table width="100%"><tbody><tr bgcolor="#dcdcdc"><td><pre>SET TRANSACTION ISOLATION LEVEL<br /> {<br /> READ COMMITTED<br /> | READ UNCOMMITTED<br /> | REPEATABLE READ<br /> | SERIALIZABLE<br /> }<br /></pre></td></tr></tbody></table> You can use the DBCC USEROPTIONS statement to determine the Transaction Isolation Level currently set. This command returns the set options that are active for the current connection. This is the example:<br /><br /> <table width="100%"><tbody><tr bgcolor="#dcdcdc"><td><pre>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED<br />GO<br />DBCC USEROPTIONS<br />GO<br /></pre></td></tr></tbody></table> </span><h2 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;"><a name="part_3"></a>Lock types</span></h2> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >There are three main types of locks that SQL Server 7.0/2000 uses:<br /><br /> <li>Shared locks</li> <li>Update locks</li> <li>Exclusive locks</li><br /><br /> Shared locks are used for operations that do not change or update data, such as a SELECT statement.<br /><br /> Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.<br /><br /> Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.<br /><br /> Shared locks are compatible with other Shared locks or Update locks.<br /><br /> Update locks are compatible with Shared locks only.<br /><br /> Exclusive locks are not compatible with other lock types.<br /><br /> Let me to describe it on the real example. There are four processes, which attempt to lock the same page of the same table. These processes start one after another, so Process1 is the first process, Process2 is the second process and so on.<br /><br /> Process1 : SELECT<br />Process2 : SELECT<br />Process3 : UPDATE<br />Process4 : SELECT<br /><br /> Process1 sets the Shared lock on the page, because there are no another locks on this page.<br />Process2 sets the Shared lock on the page, because Shared locks are compatible with other Shared locks.<br />Process3 wants to modify data and wants to set Exclusive lock, but it cannot make it before Process1 and Process2 will be finished, because Exclusive lock is not compatible with other lock types. So, Process3 sets Update lock.<br />Process4 cannot set Shared lock on the page before Process3 will be finished. So, there is no Lock starvation. Lock starvation occurs when read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely. So, Process4 waits before Process3 will be finished.<br />After Process1 and Process2 were finished, Process3 transfer Update lock into Exclusive lock to modify data. After Process3 was finished, Process4 sets the Shared lock on the page to select data.<br /><br /> </span><h2 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;"><a name="part_4"></a>Locking optimizer hints</span></h2> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >SQL Server 7.0/2000 supports the following Locking optimizer hints:<br /><br /> <li>NOLOCK</li> <li>HOLDLOCK</li> <li>UPDLOCK</li> <li>TABLOCK</li> <li>PAGLOCK</li> <li>TABLOCKX</li> <li>READCOMMITTED</li> <li>READUNCOMMITTED</li> <li>REPEATABLEREAD</li> <li>SERIALIZABLE</li> <li>READPAST</li> <li>ROWLOCK</li><br /><br /> NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.<br /><br /> HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used. You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.<br /><br /> UPDLOCK instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the end of the command or transaction.<br /><br /> TABLOCK takes a shared lock on the table that is held until the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.<br /><br /> PAGLOCK is used by default. Directs SQL Server to use shared page locks.<br /><br /> TABLOCKX takes an exclusive lock on the table that is held until the end of the command or transaction.<br /><br /> READCOMMITTED<br />Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.<br /><br /> READUNCOMMITTED<br />Equivalent to NOLOCK.<br /><br /> REPEATABLEREAD<br />Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.<br /><br /> SERIALIZABLE<br />Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.<br /><br /> READPAST<br />Skip locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.<br />You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.<br /><br /> ROWLOCK<br />Use row-level locks rather than use the coarser-grained page- and table-level locks.<br /><br /> You can specify one of these locking options in a SELECT statement.<br />This is the example:<br /><br /> SELECT au_fname FROM pubs..authors (holdlock)<br /><br /> </span><h2 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;"><a name="part_5"></a>Deadlocks</span></h2> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. For example, User1 has a lock on object "A" and wants a lock on object "B" and User2 has a lock on object "B" and wants a lock on object "A". In this case, SQL Server ends a deadlock by choosing the user, who will be a deadlock victim. After that, SQL Server rolls back the breaking user's transaction, sends message number 1205 to notify the user's application about breaking, and then allows the nonbreaking user's process to continue.<br /><br /> You can decide which connection will be the candidate for deadlock victim by using SET DEADLOCK_PRIORITY. In other case, SQL Server selects the deadlock victim by choosing the process that completes the circular chain of locks.<br /><br /> So, in a multiuser situation, your application should check the error 1205 to indicate that the transaction was rolled back, and if it's so, restart the transaction.<br /><br /> Note. To reduce the chance of a deadlock, you should minimize the size of transactions and transaction times.<br /><br /> </span><h2 style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);"><span style="font-size:85%;"><a name="part_6"></a>View locks (sp_lock)</span></h2> <span style="font-family: verdana; font-weight: bold; font-style: italic; color: rgb(0, 0, 153);font-family:Verdana;font-size:85%;" >Sometimes you need a reference to information about locks. Microsoft recommends using the sp_lock system stored procedure to report locks information. This very useful procedure returns the information about SQL Server process ID, which lock the data, about locked database, about locked table ID, about locked page and about type of locking (locktype column).<br /><br /> This is the example of using the sp_lock system stored procedure:<br /><br /> <table width="100%"><tbody><tr bgcolor="#dcdcdc"><td><pre>spid locktype table_id page dbname<br />------ ----------------------------------- ----------- ----------- ---------------<br />11 Sh_intent 688005482 0 master<br />11 Ex_extent 0 336 tempdb<br /></pre></td></tr></tbody></table> The information, returned by sp_lock system stored procedure needs in some clarification, because it's difficult to understand database name, object name and index name by their ID numbers.<br /><br /> Check the link below if you need to get user name, host name, database name, index name object name and object owner instead of their ID numbers:<br /><a href="http://www.mssqlcity.com/Articles/Adm/LockView.htm"><br /></a></span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-60179510975857014422009-10-27T22:19:00.000-07:002009-10-27T22:44:49.370-07:00Arrays in VB.NET<p style="color: rgb(0, 0, 153);font-family:verdana;"> <span class="heading" style="font-size:130%;">Arrays </span> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:130%;"> Arrays are programming constructs that store data and allow us to access them by numeric </span><span style="font-size:130%;">index </span><span style="font-size:130%;">or </span><span style="font-size:130%;">subscript</span><span style="font-size:130%;">. Arrays helps us create shorter and simpler code in many situations. Arrays in Visual Basic .NET inherit from the </span><span style="font-size:130%;">Array</span><span style="font-size:130%;"> class in the System namespace. All arrays in VB as </span><span style="font-size:130%;">zero based</span><span style="font-size:130%;">, meaning, the index of the first element is zero and they are numbered sequentially. You must specify the number of array elements by indicating the upper bound of the array. The upper bound is the numder that specifies the index of the last element of the array. Arrays are declared using Dim, ReDim, Static, Private, Public and Protected keywords. An array can have one dimension (liinear arrays) or more than one (multidimensional arrays). The dimensionality of an array refers to the number of subscripts used to identify an individual element. In Visual Basic we can specify up to 32 dimensions. Arrays do not have fixed size in Visual Basic. </span></p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:130%;"> The following code demonstrates arrays.</span><span style="font-size:130%;">Imports System.Console<br /> Module Module1<br /><br /> Sub Main()<br /> Dim sport(5) As String<br /> 'declaring an array<br /> sport(0) = "Soccer"<br /> sport(1) = "Cricket"<br /> sport(2) = "Rugby"<br /> sport(3) = "Aussie Rules"<br /> sport(4) = "BasketBall"<br /> sport(5) = "Hockey"<br /> 'storing values in the array<br /> WriteLine("Name of the Sport in the third location" & " " & sport(2))<br /> 'displaying value from array<br /> End Sub<br /><br /> End Module</span></p><p style="color: rgb(0, 0, 153);font-family:verdana;"><table style="width: 6px; height: 1px;" id="Table1" title="Arrays" bg="" color="Linen" border="0"><tbody><tr><td style="vertical-align: top;"><br /></td></tr><tr><td><br /></td></tr></tbody></table></p><p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:130%;"><strong>Understanding the Code</strong></span> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:130%;"> The above code declared a sport array of type string like this: <strong>Dim sport(5) as String</strong>. This sport array has 6 elements starting from sport(0) to sport(5). The first element of an array is always referred by zero index. The image below displays output from above code.<br /><br /><br /></span> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:130%;"> You can also declare an array without specifying the number of elements on one line, you must provide values for each element when initializing the array. The following lines demonstrate that: </span></p> <p style="color: rgb(0, 0, 153);font-family:verdana;"> <table id="Table3" title="Arrays" bg="" width="80%" border="0" style="color:Linen;"> <tbody><tr> <td><span style="font-size:130%;"> Dim Test() as Integer<br /> 'declaring a Test array<br />Test=New Integer(){1,3,5,7,9,} </span></td> </tr> </tbody></table> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"> <span style="font-size:130%;"><strong>Reinitializing Arrays</strong></span> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:130%;"> We can change the size of an array after creating them. The </span><span style="font-size:130%;">ReDim </span><span style="font-size:130%;">statement assigns a completely new array object to the specified array variable. You use ReDim statement to change the number of elements in an array. The following lines of code demonstrate that. This code reinitializes the Test array declared above. </span></p> <p style="color: rgb(0, 0, 153);font-family:verdana;"> <table id="Table4" title="ReDim Statement" bg="" width="80%" border="0" style="color:Linen;"> <tbody><tr> <td><span style="font-size:130%;"> Dim Test(10) as Integer<br /> ReDim Test(25) as Integer<br /> 'Reinitializing the array </span></td> </tr> </tbody></table> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:130%;"> When using the Redim statement all the data contained in the array is lost. If you want to preserve existing data when reinitializing an array then you should use the </span><span style="font-size:130%;">Preserve</span><span style="font-size:130%;"> keyword which looks like this: </span></p> <p style="color: rgb(0, 0, 153);font-family:verdana;"> <table id="Table5" title="ReDim with Preserve Keyword" bg="" width="80%" border="0" style="color:Linen;"> <tbody><tr> <td><span style="font-size:130%;"> Dim Test() as Integer={1,3,5}<br /> 'declares an array an initializes it with three members<br /> ReDim Preserve Test(25)<br /> 'resizes the array and retains the the data in elements 0 to 2 </span></td> </tr> </tbody></table> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"> <span style="font-size:130%;"><strong><u>Multidimensional Arrays</u></strong></span> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:130%;"> All arrays which were mentioned above are one dimensional or linear arrays. There are two kinds of multidimensional arrays supported by the .NET framework: </span><span style="font-size:130%;">Rectangular arrays</span><span style="font-size:130%;"> and </span><span style="font-size:130%;">Jagged arrays</span><span style="font-size:130%;">. </span></p> <p style="color: rgb(0, 0, 153);font-family:verdana;"> <span style="font-size:130%;"><strong>Rectangular arrays</strong></span> </p> <p style="color: rgb(0, 0, 153);font-family:verdana;"><span style="font-size:130%;"> Rectangular arrays are arrays in which each member of each dimension is extended in each other dimension by the same length. We declare a rectangular array by specifying additional dimensions at declaration. The following lines of code demonstrate the declaration of a multidimensional array. </span></p> <p face="verdana" style="color: rgb(0, 0, 153);"> <table id="Table6" title="Rectangular Arrays" bg="" width="80%" border="0" style="color:Linen;"> <tbody><tr> <td><span style="font-size:130%;"> Dim rectArray(4, 2) As Integer<br /> 'declares an array of 5 by 3 members which is a 15 member array<br /> Dim rectArray(,) As Integer = {{1, 2, 3}, {12, 13, 14}, {11, 10, 9}}<br /> 'setting initial values </span></td> </tr> </tbody></table> </p> <p face="verdana" style="color: rgb(0, 0, 153);"> <span style="font-size:130%;"><strong>Jagged Arrays</strong></span> </p> <p style="font-family: verdana; color: rgb(0, 0, 153);"><span style="font-size:130%;"> Another type of multidimensional array, Jagged Array, is an array of arrays in which the length of each array can differ. Example where this array can be used is to create a table in which the number of columns differ in each row. Say, if row1 has 3 columns, row2 has 3 columns then row3 can have 4 columns, row4 can have 5 columns and so on. The following code demonstrates jagged arrays. </span></p> <span style="color: rgb(0, 0, 153);font-size:130%;" ><span style="font-family:verdana;"> Dim colors(2)() as String</span><br /><span style="font-family:verdana;"> 'declaring an array of 3 arrays</span><br /><span style="font-family:verdana;"> colors(0)=New String(){"Red","blue","Green"}</span><br /><span style="font-family:verdana;"> initializing the first array to 3 members and setting values</span><br /><span style="font-family:verdana;"> colors(1)=New String(){"Yellow","Purple","Green","Violet"}</span><br /><span style="font-family:verdana;"> initializing the second array to 4 members and setting values</span><br /><span style="font-family:verdana;"> colors(2)=New String(){"Red","Black","White","Grey","Aqua"}</span><br /><span style="font-family:verdana;"> initializing the third array to 5 members and setting values </span></span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-72525565471059785212009-10-23T04:22:00.000-07:002010-03-29T03:12:08.170-07:00Delete duplicate records and delete all records from database<span style="color: rgb(0, 0, 153);font-size:130%;" ><span style="font-family:verdana;"><br />WITH CTE (SecondCol,ThirdCol, DuplicateCount)</span><br /><span style="font-family:verdana;">AS</span><br /><span style="font-family:verdana;">(</span><br /><span style="font-family:verdana;">SELECT SecondCol,ThirdCol,</span><br /><span style="font-family:verdana;">ROW_NUMBER()</span><br /><span style="font-family:verdana;">OVER(PARTITION BY SecondCol,ThirdCol ORDER BY secondCol)</span><br /><span style="font-family:verdana;">AS DuplicateCount</span><br /><span style="font-family:verdana;">FROM testtable</span><br /><span style="font-family:verdana;">)</span><br /><span style="font-family:verdana;">DELETE</span><br /><span style="font-family:verdana;">FROM CTE</span><br /><span style="font-family:verdana;">WHERE DuplicateCount > 1</span><br /></span><span style="font-family:verdana;"><span style="color: rgb(0, 0, 153);font-size:130%;" ><span style="font-family:verdana;">GO</span></span><br /><br /><br /><br /></span><h3 style="color: rgb(0, 0, 153);" class="post-title entry-title"> <span style="font-size:100%;"><a href="http://venkateshkoolla.blogspot.com/2009/02/how-to-delete-all-data-from-database.html">How to Delete all the data from the Database with simple Query- Sql Server</a></span></h3><br /><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:85%;" >use practice</span><br /><span style="color: rgb(0, 0, 153);font-family:verdana;font-size:85%;" >use practice<br />EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'<br />GO<br />EXEC sp_MSForEachTable '<br />IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1<br />DELETE FROM ?<br />else<br />TRUNCATE TABLE ?<br />'<br />GO<br />EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'<br />GO<br /><br /><br /><br /><span style="font-weight: bold;">SELECT ALTERNATIVE ROWS</span><br /><br /><span style="font-weight: bold;">WITH shital</span><br /><span style="font-weight: bold;">AS</span><br /><span style="font-weight: bold;">(</span><br /><span style="font-weight: bold;"> SELECT ROW_NUMBER()OVER(ORDER BY IDA)RN,* FROM EMPLOYEE</span><br /><span style="font-weight: bold;">)</span><br /><span style="font-weight: bold;">SELECT *FROM shital WHERE RN%2=0</span><br /></span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com1tag:blogger.com,1999:blog-4224981154951643783.post-7268610566905607912009-10-23T03:13:00.000-07:002009-10-23T03:33:17.798-07:00Triggers in SQL Server<span style="color: rgb(0, 0, 153);font-size:180%;" ><span style="font-weight: bold;">Triggers</span></span><br /><p face="verdana" style="color: rgb(0, 0, 153);">Creates a DML, DDL, or logon trigger. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.</p><p style="font-family: verdana; color: rgb(0, 0, 153);"><br /></p><h3 style="color: rgb(0, 0, 153); font-family: verdana;" class="subHeading"><span style="font-size:130%;">DML Triggers</span></h3><div style="color: rgb(0, 0, 153); font-family: verdana;" class="subSection"> <p>DML triggers are frequently used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the ALTER TABLE and CREATE TABLE statements. However, DRI does not provide cross-database referential integrity. Referential integrity refers to the rules about the relationships between the primary and foreign keys of tables. To enforce referential integrity, use the PRIMARY KEY and FOREIGN KEY constraints in ALTER TABLE and CREATE TABLE. If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.</p></div><p style="font-family: verdana; color: rgb(0, 0, 153);"><br /></p><h3 style="color: rgb(0, 0, 153); font-family: verdana;" class="subHeading">DDL Triggers</h3><div style="color: rgb(0, 0, 153); font-family: verdana;" class="subSection"><p style="font-family: verdana; color: rgb(0, 0, 153);">In your workplace does more than one person has access to databases that are vital to the smooth operation of your organisation? Do these users have rights to alter the database structure by using Data Definition Language (DDL) statements such as CREATE TABLE..., DROP TABLE etc?</p> <p style="font-family: verdana; color: rgb(0, 0, 153);">If the answer to the above questions are yes then are mechanisms in place to help monitor/audit who is making what changes to the database? Who deleted that table? Who changed that columns data type? What code was in the previous version of that procedure that now isn't working? This type of auditing is being asked for more and more as organisations become more and more dependent on their databases for helping in all aspects of day to day work.</p> <p style="font-family: verdana; color: rgb(0, 0, 153);">DDL Triggers (introduced in SQL Server 2005) provide you with the capability of auditing the creation, deletion and modification of database objects as well as other capabilities such as checking that DDL code conforms to your business rules before executing it.</p> <h3 style="font-family: verdana; color: rgb(0, 0, 153);">How Triggers work</h3> <p style="font-family: verdana; color: rgb(0, 0, 153);">A Trigger is a block of T-SQL code that is executed or 'triggered' as a result of another statement that is sent to the database. Before SQL Server 2005 a trigger could be 'triggered' by either INSERT, UPDATE or DELETE (Data Manipulation Language - DML) statements. SQL Server 2005 introduced DML Triggers that can be set to fire on your chosen DDL events such as CREATE_TABLE, ALTER_TABLE, DROP_TABLE, ALTER_DATABASE, CREATE_LOGIN etc. </p> <p style="font-family: verdana; color: rgb(0, 0, 153);">DDL Triggers can be set with either a <em>Server</em> scope or <em>database</em> scope. Triggers created with Server scope must target server DDL events such as CREATE_DATABASE or CREATE_LOGIN whilst triggers created with database scope must target database level events such as CREATE_TABLE or ALTER_PROC. See the full <a href="http://msdn2.microsoft.com/en-us/library/ms189871.aspx">list of SQL Server DDL Trigger Events</a> (including their scope).</p> <p style="font-family: verdana; color: rgb(0, 0, 153);">DDL triggers can only fire <strong>after</strong> the DDL statement has occurred. This is different from DML triggers which can fire before the triggering statement. </p> <h3 style="font-family: verdana; color: rgb(0, 0, 153);">Syntax of a DDL trigger</h3> <pre style="font-family: verdana; color: rgb(0, 0, 153);" class="Syn">CREATE TRIGGER [TriggerName]<br />ON [Scope (Server|Database)]<br />FOR [EventName...],<br />AS<br />-- code for your trigger response here<br /></pre> <h3 style="font-family: verdana; color: rgb(0, 0, 153);">The EventData function</h3> <p style="font-family: verdana; color: rgb(0, 0, 153);">If you want to audit changes to your database schemas you need to be able to access the triggering events in your DDL trigger so that you can record what changes are being made. To access the triggering event we can use the EventData function in our DDL trigger. The EventData function returns an xml value.</p> <p style="font-family: verdana; color: rgb(0, 0, 153);">The EventData xml value includes the triggering SQL statement, the event time, the type of event and depending on what type of event was called, extra information such as the database name. The following example shows how EventData can be used to record all statements that changed the table or stored proc schemas into a table called <em>DDLAudit</em>. </p> <pre style="font-family: verdana; color: rgb(0, 0, 153);" class="Code">CREATE TRIGGER AuditProcChanges<br />ON DATABASE<br />FOR CREATE_PROC, ALTER_PROC, DROP_PROC, CREATE_TABLE, ALTER_TABLE, DROP_TABLE<br />AS<br /><br />DECLARE @ed XML<br />SET @ed = EVENTDATA()<br /><br />INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL, Login)<br />VALUES<br />(<br />GetDate(),<br />@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),<br />@ed.value('(/EVENT_INSTANCE/EventType)[1]', nvarchar(100)'),<br />@ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),<br />@ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),<br />@ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')<br />)<br /></pre> <p style="font-family: verdana; color: rgb(0, 0, 153);">The EventData function returns an xml value and is assigned to a variable called @ed which is of an xml data type. The xquery function <em>value(Xquery, sqltype)</em> returns the specified values from the xml variable. For more information on EventData see <a href="http://msdn2.microsoft.com/en-us/library/ms187909.aspx">MSDN</a>. </p> <p style="font-family: verdana; color: rgb(0, 0, 153);">This DDLAudit table could reside in the individual database or you could create a seperate ApplicationAudit database and use a 3 part name to record the audit in this ApplicationAudit database, i.e. INSERT INTO ApplicationAudit.dbo.DDLAudit ....</p> <h3 style="font-family: verdana; color: rgb(0, 0, 153);">Covering all databases and events</h3> <p style="font-family: verdana; color: rgb(0, 0, 153);">If you wanted to audit the DDL events for all your databases you would need to create this trigger in each database. The above trigger only monitors DDL events that affect Tables and Stored Procedures. You can use the handy event name of <strong>DDL_DATABASE_LEVEL_EVENTS</strong> to make sure your trigger covers all DDL events that have database scope as follows:</p> <pre style="font-family: verdana; color: rgb(0, 0, 153);" class="Code">CREATE TRIGGER AuditDBScopeDDLChanges<br />ON DATABASE<br />FOR DDL_DATABASE_LEVEL_EVENTS<br />AS<br />-- trigger code here...<br /></pre> <h3 style="font-family: verdana; color: rgb(0, 0, 153);">Preventing DDL actions using triggers</h3> <p style="font-family: verdana; color: rgb(0, 0, 153);">Sometimes you may want to prevent the alteration of a schema, because the triggering statement and trigger are joined in one transaction we can call ROLLBACK in our trigger to rollback the DDL statement that caused the trigger to fire:</p> <pre style="font-family: verdana; color: rgb(0, 0, 153);" class="Code">CREATE TRIGGER PreventDropTable<br />ON DATABASE<br />FOR DROP_TABLE<br />AS<br />PRINT 'Tables cannot be dropped'<br />ROLLBACK<br /></pre> <p style="font-family: verdana; color: rgb(0, 0, 153);">What happens if you then want to drop a table in the database with the above trigger? You can disable the trigger, drop the table and then re-enable the trigger:</p> <pre style="font-family: verdana; color: rgb(0, 0, 153);" class="Code">DISABLE TRIGGER PreventDropTable<br />ON DATABASE<br />GO<br />DROP TABLE MyTable<br />GO<br />ENABLE TRIGGER PreventDropTable<br />ON DATABASE<br />GO<br /></pre> <p style="font-family: verdana; color: rgb(0, 0, 153);">You could also if you wished extend use this method of schema change prevention for server scope events to prevent the dropping of databases:</p> <pre style="font-family: verdana; color: rgb(0, 0, 153);" class="Code">CREATE TRIGGER PreventDropDatabaseServerWide<br />ON ALL SERVER<br />FOR DROP_DATABASE<br />AS<br />PRINT 'Cannot drop tables, DDL Trigger will rollback'<br />ROLLBACK<br /></pre> <h3 style="font-family: verdana; color: rgb(0, 0, 153);">Finding DDL triggers using system tables</h3> <p style="font-family: verdana; color: rgb(0, 0, 153);">To view the DDL triggers in your databases of database scope you can query the sys.triggers table. To view the DDL triggers with server scope you need to query the sys.server_triggers table in the master database.</p> <h3 style="font-family: verdana; color: rgb(0, 0, 153);">Conclusion</h3> <p style="font-family: verdana; color: rgb(0, 0, 153);">When designing your DDL trigger you will probably be performing one of more of the following actions:</p> <ul style="font-family: verdana; color: rgb(0, 0, 153);"><li>Recording changes made to the database schema</li><li>Stopping certain types of changes being made to the database schema</li><li>Fire another action in the database in response to the schema change</li></ul> <p style="font-family: verdana; color: rgb(0, 0, 153);">We have seen how DDL triggers can be used to a) audit and b) control schema changes using the EventData function and the ROLLBACK command respectively.</p> <p><span><h3 style="color: rgb(0, 0, 153);" class="subHeading"><span style="font-size:130%;">Logon Triggers</span></h3><div class="subSection"> <p>Logon triggers fire in response to the LOGON event. This event is raised when a user sessions is being established. For more information.</p><h4 class="subHeading"><span style="font-size:130%;">Multiple Triggers</span></h4><div class="subSection"> <p> SQL Server allows for multiple triggers to be created for each DML, DDL, or LOGON event. For example, if CREATE TRIGGER FOR UPDATE is executed for a table that already has an UPDATE trigger, an additional update trigger is created. In earlier versions of SQL Server, only one trigger for each INSERT, UPDATE, or DELETE data modification event is allowed for each table.</p><h4 class="subHeading"><span style="font-size:130%;">Recursive Triggers</span></h4><div class="subSection"> <p> SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.</p><h4 class="subHeading"><span style="font-size:130%;">Nested Triggers</span></h4><div class="subSection"> <p>Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable nested triggers, set the <strong>nested triggers</strong> option of <strong>sp_configure </strong>to 0 (off). The default configuration allows for nested triggers. If nested triggers is off, recursive triggers is also disabled, regardless of the RECURSIVE_TRIGGERS setting set by using ALTER DATABASE.</p></div></div></div><p></p> </div></span></p></div>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com2tag:blogger.com,1999:blog-4224981154951643783.post-88249596234347794502009-10-20T02:25:00.000-07:002009-10-20T03:19:55.209-07:00Drop all objects in a database on SQL Server<span style="color: rgb(0, 0, 153);">Solution for Drop all objects from database at once!<br /><br /><span style="color: rgb(51, 51, 51);">Following procedure run on any database (SQL Server)</span><br /><br /></span>\***********************************************/<br /><br /><span style="color: rgb(0, 0, 153);"><br />CREATE PROCEDURE dbo.DropAllDBObjects<br />@Action VARCHAR(20)=NULL<br />As<br />BEGIN<br /><br />DECLARE @Name VARCHAR(500)<br /><br />IF @Action='Table'<br />BEGIN<br /> DECLARE DROPALL CURSOR<br /> FOR SELECT [NAME] FROM sys.TABLES<br /><br /> OPEN DROPALL<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> WHILE @@FETCH_STATUS=0<br /> BEGIN<br /> EXEC('DROP TABLE ' + @Name)<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> END<br /> CLOSE DROPALL<br /> DEALLOCATE DROPALL<br /><br />END<br /><br />ELSE IF @Action='PROCEDURE'<br />BEGIN<br /> DECLARE DROPALL CURSOR<br /> FOR SELECT [NAME] FROM sys.objects WHERE TYPE='P'<br /><br /> OPEN DROPALL<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> WHILE @@FETCH_STATUS=0<br /> BEGIN<br /> IF @Name <> 'DropAllDBObjects'<br /> EXEC('DROP PROCEDURE ' + @Name)<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> END<br /> CLOSE DROPALL<br /> DEALLOCATE DROPALL<br /><br />END<br />ELSE IF @Action='Function'<br />BEGIN<br /> DECLARE DROPALL CURSOR<br /> FOR SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'<br /><br /> OPEN DROPALL<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> WHILE @@FETCH_STATUS=0<br /> BEGIN<br /> EXEC('DROP FUNCTION ' + @Name)<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> END<br /> CLOSE DROPALL<br /> DEALLOCATE DROPALL<br /><br />END<br />ELSE IF @Action='View'<br />BEGIN<br /> DECLARE DROPALL CURSOR<br /> FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS<br /><br /> OPEN DROPALL<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> WHILE @@FETCH_STATUS=0<br /> BEGIN<br /> EXEC('DROP VIEW ' + @Name)<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> END<br /> CLOSE DROPALL<br /> DEALLOCATE DROPALL<br /><br />END<br />IF @Action is null<br />BEGIN<br /> --Table<br /> DECLARE DROPALL CURSOR<br /> FOR SELECT [NAME] FROM sys.TABLES<br /><br /> OPEN DROPALL<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> WHILE @@FETCH_STATUS=0<br /> BEGIN<br /> EXEC('DROP TABLE ' + @Name)<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> END<br /> CLOSE DROPALL<br /> DEALLOCATE DROPALL<br /><br /> --Views<br /> DECLARE DROPALL CURSOR<br /> FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS<br /><br /> OPEN DROPALL<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> WHILE @@FETCH_STATUS=0<br /> BEGIN<br /> EXEC('DROP VIEW ' + @Name)<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> END<br /> CLOSE DROPALL<br /> DEALLOCATE DROPALL<br /><br /> --Functions<br /> DECLARE DROPALL CURSOR<br /> FOR SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'<br /><br /> OPEN DROPALL<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> WHILE @@FETCH_STATUS=0<br /> BEGIN<br /> EXEC('DROP FUNCTION ' + @Name)<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> END<br /> CLOSE DROPALL<br /> DEALLOCATE DROPALL<br /><br /> --Procedure<br /> DECLARE DROPALL CURSOR<br /> FOR SELECT [NAME] FROM sys.objects WHERE TYPE='P'<br /><br /> OPEN DROPALL<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> WHILE @@FETCH_STATUS=0<br /> BEGIN<br /> IF @Name <> 'DropAllDBObjects'<br /> EXEC('DROP PROCEDURE ' + @Name)<br /> FETCH NEXT FROM DROPALL INTO @Name<br /> END<br /> CLOSE DROPALL<br /> DEALLOCATE DROPALL<br />END<br />END<br />GO<br /></span><br />\***********************************************/<br /><br />To execute Procedure:<br /><br />a)To Drop all objects from Database,execute following<br /><br /><span style="color: rgb(0, 0, 153);">EXEC DropAllDBObjects </span><br /><br />b)To Drop all objects from Database,based on different object at time.<br /><br /><span style="color: rgb(0, 0, 153);">EXEC DropAllDBObjects 'table'</span><br /><br />or<br /><br /><span style="color: rgb(0, 0, 153);">EXEC DropAllDBObjects 'View'</span><br /><br />or<br /><br /><span style="color: rgb(0, 0, 153);">EXEC DropAllDBObjects 'Function'</span><br /><br />or<br /><br /><span style="color: rgb(0, 0, 153);">EXEC DropAllDBObjects 'Procedure'</span><br /><br /><br />To View all objects,use following<br /><br /><span style="color: rgb(0, 0, 153);">SELECT [NAME] FROM sys.TABLES</span><br /><span style="color: rgb(0, 0, 153);">SELECT [NAME] FROM sys.objects WHERE TYPE='P'</span><br /><span style="color: rgb(0, 0, 153);">SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'</span><br /><span style="color: rgb(0, 0, 153);">SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS</span><br /><br />After All work done,you can drop following procedure<br /><br /><span style="color: rgb(0, 0, 153);">DROP PROC DropAllDBObjects</span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-18605686078637474292009-10-15T21:34:00.000-07:002009-10-15T21:35:40.546-07:00Differences between varchar and nvarchar in SQL Server<span style="font-size:100%;"><span style="color: rgb(0, 0, 153);font-family:verdana;" class="a3" >The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions are <b>VARCHAR</b> and <b>NVARCHAR</b>: what's the difference between the two, and how important is the difference? <p>VARCHAR is an abbreviation for <b>variable-length character string</b>. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.</p> <p>The "N" in NVARCHAR means <b>uNicode</b>. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.</p> <p>The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.</p> <p>Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.</p></span></span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-47176268304473812972009-10-14T23:48:00.000-07:002009-11-02T03:12:00.426-08:00Windows Vista Error Fix Desktop Icons And Taskbar Fails To Load At Startup<span style="color: rgb(0, 0, 153);"> There are really a lot of reasons why all of your icons and your Windows Vista desktop fails, all of a sudden, to load at startup. Some times the taskbar also refuses to load at Windows Vista startup.Anyway, most of the times this issue is due to a little key in the registry called</span><strong style="color: rgb(0, 0, 153);"> explorer.exe</strong><span style="color: rgb(0, 0, 153);"> which refuses to load or it is just missing or it doesn’t exist at all.This </span><a style="color: rgb(0, 0, 153);" title="Windows Vista Error Fix" href="http://www.tomstricks.com/category/windows-vista/vista-error-fix/">Issue in Windows Vista</a><span style="color: rgb(0, 0, 153);"> is easily fixed by using one of these 4 fixes:</span><br /><br /><span style="font-size:180%;">The following method i have checked out and its worked for my laptop inspiron 1545 windows vista home edition.<br /></span><br /><p style="text-align: justify; color: rgb(0, 0, 153);"><span style="text-decoration: underline;"><strong>Method1:</strong></span></p> <p style="text-align: justify; color: rgb(0, 0, 153);">Copy and paste the following piece of code in your notepad:</p> <p style="color: rgb(0, 0, 153);"><strong>Windows Registry Editor Version 5.00</strong></p> <p style="color: rgb(0, 0, 153);"><strong>[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon]<br />“Userinit”=”C:\\WINDOWS\\SYSTEM32\\userinit.exe,”</strong></p> <p style="color: rgb(0, 0, 153);">Now save the file as <strong>Userinit.reg</strong>. Double-click on it to merge it in the Windows Vista registry. or Change registry key value and close registry editor and restart.</p> <p style="text-align: justify; color: rgb(0, 0, 153);"><span style="text-decoration: underline;"><strong>Method 2:</strong></span></p> <p style="text-align: justify; color: rgb(0, 0, 153);">The key<strong> Explorer.exe</strong> in your registry is not correct.To fix this:</p> <ul style="text-align: justify; color: rgb(0, 0, 153);"><li>Click on Windows Vista Start.</li><li>In the start menu Search Box type <strong>“regedit</strong>” and hit Enter.</li><li>In Windows registry, locate the following key: <strong>[HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\Windows NT\CurrentVersion\ Image File Execution Options]</strong></li><li>Look for <strong>Explorer.exe</strong>.</li><li>Delete it.</li><li>or Change registry key value to <strong>Explorer.exe</strong> to the above location and close registry editor then restart.</li></ul> <p style="text-align: justify; color: rgb(0, 0, 153);"><span style="text-decoration: underline;"><strong>Method 3:</strong></span></p> <p style="text-align: justify; color: rgb(0, 0, 153);">Restore <strong>Explorer.exe</strong> which is missing in your Windows Vista registry.To do this:</p> <ul style="text-align: justify; color: rgb(0, 0, 153);"><li>Copy this piece of code in your notepad editor.</li></ul> <p style="text-align: justify; color: rgb(0, 0, 153);"><strong> Windows Registry Editor Version 5.00</strong></p> <p style="color: rgb(0, 0, 153);"><strong>[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon]<br />“Shell”=”explorer.exe”</strong></p> <ul style="text-align: justify; color: rgb(0, 0, 153);"><li>Save the file as <strong>Logon.reg</strong>.</li><li>Double-click on it to merge it in the Windows Vista registry(Sometimes UAC prompt appears and click “Continue”).</li><li>or Change registry key value to <strong>Explorer.exe</strong> to the above location and close registry editor then restart.</li></ul> <p style="text-align: justify; color: rgb(0, 0, 153);"><span style="text-decoration: underline;"><strong>Method 4:</strong></span></p> <p style="color: rgb(0, 0, 153);">If <strong>explorer.exe</strong> is not in C:\WINDOWS\ then insert your Windows CD and, using the ‘<strong>Task Manager</strong>’ (File -> New Task), run the ‘<strong>System File Checker</strong>’ utility with this command:</p> <p style="color: rgb(0, 0, 153);"><strong>sfc /scannow</strong></p> <p style="color: rgb(0, 0, 153);">If the ‘System File Checker’ didn’t replace it, boot to the Recovery Console with the Windows CD and copy <strong>explorer.exe</strong> manuallyFor this:</p> <ul style="text-align: justify; color: rgb(0, 0, 153);"><li>Insert your Windows Vista DVD.</li><li>When the setup screen window appears, choose the Repair or Recover option by pressing “R”.</li><li>In the Command Prompt, type the following command:<strong> expand G:\i386 /F:EXPLORER.EX_ C: \WINDOWS\explorer.exe /y</strong> (don’t forget that G: is the letter of your DVD drive, while C: is the partition where Windows Vista is installed, change them if they are different in your PC)</li></ul>original link from:<br /><br />http://www.tomstricks.com/windows-vista-error-fixdesktop-icons-and-taskbar-fails-to-load-at-startup/****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-88824586375263649332009-08-21T03:19:00.000-07:002009-08-21T03:21:58.910-07:00Identify last statement run for a specific SQL Server session<code style="font-size:12px;"><span style="color:blue;">SELECT </span><span style="color:black;">DEST.</span><span style="color:blue;">TEXT<br />FROM </span><span style="color:black;">sys.[dm_exec_connections] SDEC<br /></span><span style="color:gray;">CROSS </span><span style="color:black;">APPLY sys.[dm_exec_sql_text]</span><span style="color:gray;">(</span><span style="color:black;">SDEC.[most_recent_sql_handle]</span><span style="color:gray;">) </span><span style="color:blue;">AS </span><span style="color:black;">DEST<br /></span><span style="color:blue;">WHERE </span><span style="color:black;">SDEC.[most_recent_session_id] </span><span style="color:blue;">= </span><span style="color:black;">(SELECT @@SPID)</span></code>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-1540721838375387092009-07-27T23:17:00.000-07:002009-07-27T23:33:41.035-07:00RESEED All Identity Column in all tables of a Database<span style="color: rgb(0, 0, 153);">IF EXISTS(SELECT *FROM SYS.PROCEDURES WHERE NAME='SP_RESEEDIdentityColumn')</span><br /><span style="color: rgb(0, 0, 153);">BEGIN</span><br /><span style="color: rgb(0, 0, 153);"> DROP PROC SP_RESEEDIdentityColumn</span><br /><span style="color: rgb(0, 0, 153);">END</span><br /><span style="color: rgb(0, 0, 153);">GO</span><br /><span style="color: rgb(0, 0, 153);">CREATE PROC SP_RESEEDIdentityColumn</span><br /><span style="color: rgb(0, 0, 153);">@RESEEDVALUE INT=0</span><br /><span style="color: rgb(0, 0, 153);">AS</span><br /><span style="color: rgb(0, 0, 153);">BEGIN</span><br /><span style="color: rgb(0, 0, 153);"> DECLARE CURRESEED CURSOR</span><br /><span style="color: rgb(0, 0, 153);"> FOR </span><br /><span style="color: rgb(0, 0, 153);"> SELECT NAME FROM SYS.TABLES</span><br /><br /><span style="color: rgb(0, 0, 153);"> DECLARE @TABNAME VARCHAR(225),@SQL NVARCHAR(225)</span><br /><span style="color: rgb(0, 0, 153);"> </span><br /><span style="color: rgb(0, 0, 153);"> OPEN CURRESEED</span><br /><span style="color: rgb(0, 0, 153);"> FETCH NEXT FROM CURRESEED INTO @TABNAME</span><br /><span style="color: rgb(0, 0, 153);"> WHILE @@FETCH_STATUS=0</span><br /><span style="color: rgb(0, 0, 153);"> BEGIN</span><br /><span style="color: rgb(0, 0, 153);"> SET @SQL ='DBCC CHECKIDENT('+@TABNAME+',RESEED,'+@RESEEDVALUE+')'</span><br /><span style="color: rgb(0, 0, 153);"> EXEC(@SQL)</span><br /><span style="color: rgb(0, 0, 153);"> FETCH NEXT FROM CURRESEED INTO @TABNAME</span><br /><span style="color: rgb(0, 0, 153);"> END</span><br /><span style="color: rgb(0, 0, 153);">END</span><br /><br /><span style="color: rgb(0, 0, 153);">CLOSE CURRESEED</span><br /><span style="color: rgb(0, 0, 153);">DEALLOCATE CURRESEED</span><br /><span style="color: rgb(0, 0, 153);">GO</span><br /><br /><br /><span style="color: rgb(0, 0, 153);">--Here if you are not specified param value it reseed from 0</span><br /><span style="color: rgb(0, 0, 153);">EXECUTE SP_RESEEDIdentityColumn </span><br /><br /><span style="color: rgb(0, 0, 153);">--Here if you are not specified param value it reseed from 1</span><br /><span style="color: rgb(0, 0, 153);">EXECUTE SP_RESEEDIdentityColumn 1</span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-66346177252461017442009-07-27T04:52:00.000-07:002009-07-27T04:57:59.033-07:00Delete the data from all tables In Database-Simple Sql Server Query!<span style="color: rgb(0, 0, 153);">USE TEST</span><br /><span style="color: rgb(0, 0, 153);">EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'</span><br /><span style="color: rgb(0, 0, 153);">GO</span><br /><span style="color: rgb(0, 0, 153);">EXEC sp_MSForEachTable '</span><br /><span style="color: rgb(0, 0, 153);">IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1</span><br /><span style="color: rgb(0, 0, 153);">DELETE FROM ?</span><br /><span style="color: rgb(0, 0, 153);">else</span><br /><span style="color: rgb(0, 0, 153);">TRUNCATE TABLE ?</span><br /><span style="color: rgb(0, 0, 153);">'</span><br /><span style="color: rgb(0, 0, 153);">GO</span><br /><span style="color: rgb(0, 0, 153);">EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'</span><br /><span style="color: rgb(0, 0, 153);">GO</span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-24548707465898228582009-06-30T23:48:00.001-07:002009-06-30T23:51:45.915-07:00Different String Function in SQL Server 2005<span class="Apple-style-span" style=" ;font-family:arial;font-size:13px;"><div><h2><span class="Apple-style-span" style="font-size: 48px; font-weight: normal; "><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 0); "><b><span style="color: rgb(0, 51, 102); "><span style="text-decoration: underline; ">ASCII</span></span></b></span></span></span> <span style="text-decoration: underline; "><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><span style=" ;font-family:'comic sans ms', sans-serif;"><b>function in SQLServer</b></span></span></span></span></span></h2></div><div><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 0); "><span style="color: rgb(0, 51, 102); ">Returns the ASCII code value of Leftmost charector in a string<br />Syntax : ASCII ('char Expression')<br /><br /><span style="color: rgb(0, 51, 102); "><span style="text-decoration: underline; "><b>NCHAR</b></span></span></span></span></span></span> <span style="text-decoration: underline; "><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><span style=" ;font-family:'comic sans ms', sans-serif;"><b>function in SQLServer</b></span></span></span></span><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 0); "><span style="color: rgb(0, 51, 102); "><br /></span><span style="color: rgb(0, 51, 102); ">Returns the Unicode Charector with specified Integer code, as defined be Unicode standard<br />Syntax :NCHAR(Integer Expression)<br /><br /><span style="color: rgb(0, 51, 102); "><span style="text-decoration: underline; "><b>SOUNDEX</b></span></span></span></span></span></span> <span style="text-decoration: underline; "><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><span style=" ;font-family:'comic sans ms', sans-serif;"><b>function in SQLServer</b></span></span></span></span><br /><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 0); "><span style="color: rgb(0, 51, 102); ">Returns a four-charector code to evalute the similarity of two strings<br />Syntax : SOUNDEX('char Expression')<br /><em>Ex:</em><br />Select SOUNDEX('smith'),SOUNDEX('<wbr>smythe')<br /><em>Result:</em><br />S530 S530<br /><br /><span style="text-decoration: underline; "><span style="color: rgb(0, 51, 102); "><b>CHAR</b></span></span></span></span></span></span> <span style="text-decoration: underline; "><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><span style=" ;font-family:'comic sans ms', sans-serif;"><b>function in SQLServer</b></span></span></span></span><br /><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 0); "><span style="color: rgb(0, 51, 102); ">Convert an Int ASCII code to a Charector<br />Syntax : CHAR( Integer Expression )<br />Note : Is an integer from 0 through 255, NULL is return if the integer expression not in the Range<br /><br /><span style="color: rgb(0, 51, 102); "><span style="text-decoration: underline; "><b>PATINDEX</b></span></span></span></span></span></span> <span style="text-decoration: underline; "><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><span style=" ;font-family:'comic sans ms', sans-serif;"><b>function in SQLServer</b></span></span></span></span><br /><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 0); "><span style="color: rgb(0, 51, 102); ">Returns the starting position of the first occurance of a pattren in the specied expression, or Zero if the pattren is nor found the specified expression, on all valid text and charector data types.<br />Syntax : PATINDEX('%pattren%',<wbr>expression)<br /><br /><span style="color: rgb(0, 51, 102); "><span style="text-decoration: underline; "><b>SPACE</b></span></span></span></span></span></span> <span style="text-decoration: underline; "><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><span style=" ;font-family:'comic sans ms', sans-serif;"><b>function in SQLServer</b></span></span></span></span><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 0); "><span style="color: rgb(0, 51, 102); "><br />Returns a string of Repeated Spaces<br />Syntax : SPACE (Integer Expression)<br /><em>Ex:</em><br />Select 'Myname' + Space(2) + 'Lastname'<br /><em>Result:</em><br />Myname Lastname<br /><span style="color: rgb(0, 51, 102); "><br /></span></span></span><span style="text-decoration: underline; "><span style="color: rgb(0, 51, 102); "><b>QUOTENAME</b></span></span></span></span> <span style="text-decoration: underline; "><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><span style=" ;font-family:'comic sans ms', sans-serif;"><b>function in SQLServer</b></span></span></span></span><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><br />Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier<br />Syntax: QUOTENAME ( 'character_string' [ , 'quote_character' ] )<br /></span></span></span></div><div><dl><dt><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><b>'</b> <em>character_string</em> <b>'</b></span></span></span></dt><dd><p><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); ">Is a string of Unicode character data. <em>character_string</em> is <b>sysname</b> and is limited to 128 characters. Inputs greater than 128 characters return NULL.<br /></span></span></span></p></dd></dl></div><dl><dt><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><b>'</b> <em>quote_character</em> <b>'</b></span></span></span></dt><dd><p><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); ">Is a one-character string to use as the delimiter. Can be a single quotation mark ( <b>'</b> ), a left or right bracket ( <b>[</b><b>]</b> ), or a double quotation mark ( <b>"</b> ). If <em>quote_character</em> is not specified, brackets are used.</span></span></span></p></dd></dl><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><em>Ex:<br /></em>Select QUOTENAME('abc[]def')<em><br />Result :<br /></em>[abc[]]def<br /><br /><span style="text-decoration: underline; "><span style="color: rgb(0, 51, 102); "><b>STR</b></span></span></span></span></span> <span style="text-decoration: underline; "><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><span style=" ;font-family:'comic sans ms', sans-serif;"><b>function in SQLServer</b></span></span></span></span><br /><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); ">Returns characters data converted from numaric data<br />Syntax : STR (float Expression [,length,[[decima]])<br /><em>Ex;</em><br />Select STR(123.45,6,1)<br /><em>Result</em><br /></span></span></span><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 0); ">123.4<span style="color: rgb(0, 51, 102); "><br /><b><br /><span style="text-decoration: underline; "><span style="color: rgb(0, 51, 102); ">DIFFERENCE</span></span></b></span></span></span></span> <span style="text-decoration: underline; "><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 102); "><span style=" ;font-family:'comic sans ms', sans-serif;"><b>function in SQLServer</b></span></span></span></span><span style=" ;font-family:'comic sans ms', sans-serif;"><span style=" ;font-size:10pt;"><span style="color: rgb(0, 51, 0); "><span style="color: rgb(0, 51, 102); "><br />Retruns interger value that indicates the difference between SOUNDEX value of two character expressions<br />Syntax : DIFFERENCE (firat expression, second Expression)<br /><br /><span style="text-decoration: underline; "><b>REPLACE function in SQLServer</b></span><br />Replaces all occurences of Specified string value with another string value<br />Syntax : Replace (car expression,old string,new string)<br /><em>EX:</em><br />Print REPLACE('abcdefghcd','c','x')<br /><em>Result:</em><br />abxdefghxd<br /><br /><span style="text-decoration: underline; "><b>STUFF function in SQLServer</b></span><br />Deletes a specied length of characters and insert another set of characters at specified starting point<br />Syntax : STUFF(char expression,start,length,char expression)<br /><em>Ex :</em><br />select STUFF('abcdef',2,3,'ijklmn')<br /><em>Result:</em><br />aijklmnef<br /><br /><br /><span style="text-decoration: underline; "><b>LEFT function in sqlserver<br /></b></span>Return the left part of the char expression with specied number of characters<br />Syntax : LEFT(char expression,length)<br /><em>Ex:</em><br />print LEFT('abcdef',2)<br /><em>Result:</em><br />ab<br /><br /><span style="text-decoration: underline; "><b>REPLICATE function in SQLServer<br /></b></span>Replicate a string value in specified numver of times<br />Syntax : REPLICATE(char expression, interger expression)<br /><em>Ex:</em><br />Print REPLICATE('0',4) + 'S'<br /><em>Result:</em><br />0000S<br /><br /><span style="text-decoration: underline; "><b>SUBSTRING function in SQL Server<br /></b></span>Returns a part of charecter, byte, text or Image expression<br />Syntax : SUBSTRING(char expression,start,length)<br /><em>Ex:</em><br />select SUBSTRING('myname',1,2)<br /><em>Result:</em><br />my<br /><br /><span style="text-decoration: underline; "><b>LEN function in SQL Server</b></span><br />Returns length of specified char expression, excluding trailing blank spaces<br />Syntax : LEN(char expression)<br /><em>Ex :</em><br />print LEN(' my Name')<br /><em>Result :</em><br />7<br /><br /><br /><span style="text-decoration: underline; "><b>REVERSE function in SQL Server<br /></b></span>Returns reverse of char expression<br />Syntax : REVERSE(char expression)</span></span></span></span></span>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-25474399756920706482009-06-30T22:31:00.000-07:002009-06-30T22:36:17.249-07:00Create your own key in Registry using Microsoft.Win32.RegistryKey in c#.Net<div><br /></div><div><b><span class="Apple-style-span" style="color:#000099;">//Check for key exists in Registry before creating New key and if not exists then create new key</span></b></div><div><br /></div><div>if (((Microsoft.Win32.RegistryKey)(Microsoft.Win32.Registry.CurrentUser.OpenSubKey("MyChandruKey123"))) == null)</div><div> {</div><div> Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.CurrentUser.CreateSubKey("MyChandruKey123");</div><div> key.SetValue("MyConnStrChandru123","");</div><div> }</div><div> </div><div><b><span class="Apple-style-span" style="color:#000099;">//Get the Key value based on key name</span></b></div><div>string conns = ((Microsoft.Win32.RegistryKey)(Microsoft.Win32.Registry.CurrentUser.OpenSubKey("MyChandruKey123"))).GetValue("MyConnStrChandru123").ToString();</div>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0tag:blogger.com,1999:blog-4224981154951643783.post-54335046471089073322009-06-30T22:23:00.000-07:002009-06-30T22:30:21.184-07:00Encrypt and Decrypt the string using RSACryptoServiceProvider in c#.Net within Single Line of Code<div><span class="Apple-style-span" style="color:#000099;"><b>//Call the functions</b></span></div><div><span class="Apple-style-span" style="color:#000099;"><b><div><br /></div><div><span class="Apple-style-span" style="font-weight: normal;"><span class="Apple-style-span" style="color:#000000;">string EncryptedString= GetEncryptedText("Chandru");</span></span></div><div><span class="Apple-style-span" style="font-weight: normal;"><span class="Apple-style-span" style="color:#000000;">string DecryptedString = GetDecryptedText(EncryptedString);</span></span></div></b></span></div><div><span class="Apple-style-span" style="color:#000099;"><b><br /></b></span></div><div><span class="Apple-style-span" style="color:#000099;"><b><br /></b></span></div><div><b><span class="Apple-style-span" style="color:#000099;">//Create an object for RSACryptoServiceProvider</span></b></div><div> RSACryptoServiceProvider RSAEncrypt = new RSACryptoServiceProvider();</div><div> </div><div><b><span class="Apple-style-span" style="color:#000099;">//Encrypt the String Provided</span></b></div><div> public string GetEncryptedText(string PlainStringToEncrypt)</div><div> {</div><div> return Encoding.Default.GetString(RSAEncrypt.Encrypt(Encoding.Default.GetBytes(PlainStringToEncrypt), false));</div><div><br /></div><div> }</div><div><br /></div><div><b><span class="Apple-style-span" style="color:#000099;">//Decrypt The String Provided</span></b></div><div> public string GetDecryptedText(string EncryptedStringToDecrypt)</div><div> {</div><div> return Encoding.Default.GetString(RSAEncrypt.Decrypt(Encoding.Default.GetBytes(EncryptedStringToDecrypt), false));</div><div> }</div>****Hib's*** ChandrUhttp://www.blogger.com/profile/14644672189274974877noreply@blogger.com0