tag:blogger.com,1999:blog-72969405950160426542024-02-18T18:00:12.830-08:00Tharindu Dhaneenja's blog....Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-7296940595016042654.post-80223017673858446852010-12-07T04:28:00.000-08:002010-12-08T02:54:25.880-08:00T-SQL Debugging – Error<div style="text-align: center; font-weight: bold;"><span style="font-size:130%;">SQL Server Debugging/Microsoft SQL Server Management Studio – Error</span></div> <p class="MsoNormal"><span style="font-size:85%;">Last two month I have spent most of time to Debugging T-SQL code. Some certain situation I have noticed T-SQL Debugging given errors. When I’m working only my own SQL server instance, it’s (T-SQL Debugging) working without any problem. But when I’m connected different server and execute procedures with T-SQL Debugging then it will gives me following error,</span></p> <p style="font-weight: bold; color: rgb(153, 0, 0);" class="MsoNormal"><span style="font-size:85%;">Unable to start T-SQL debugging. <span style=""> </span>Could not attach to SQL Server process on ’YOUR SERVER’ .click help for more information.</span></p><p class="MsoNormal"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyQiSFHNHe8s3j15klBpIa33Azn-wNFKtK6awF3MKhVdKZVQ_SeoeXyqYvoXx8z_SDCIBtXyBbJomPHgG01mTi7xjAtWTJ1yfY1jNOvUfdX22kNzSmRkJRjd3InLpIeEtq8K-HtVSejkc/s1600/sql+dubbging+tharindu+dhaneenja.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 320px; height: 108px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyQiSFHNHe8s3j15klBpIa33Azn-wNFKtK6awF3MKhVdKZVQ_SeoeXyqYvoXx8z_SDCIBtXyBbJomPHgG01mTi7xjAtWTJ1yfY1jNOvUfdX22kNzSmRkJRjd3InLpIeEtq8K-HtVSejkc/s320/sql+dubbging+tharindu+dhaneenja.png" alt="" id="BLOGGER_PHOTO_ID_5547917102119455298" border="0" /></a></p><p class="MsoNormal"><!--[if gte mso 9]><xml> <o:officedocumentsettings> <o:allowpng/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:worddocument> <w:view>Normal</w:View> <w:zoom>0</w:Zoom> <w:trackmoves/> <w:trackformatting/> <w:punctuationkerning/> <w:validateagainstschemas/> <w:saveifxmlinvalid>false</w:SaveIfXMLInvalid> <w:ignoremixedcontent>false</w:IgnoreMixedContent> <w:alwaysshowplaceholdertext>false</w:AlwaysShowPlaceholderText> <w:donotpromoteqf/> <w:lidthemeother>EN-US</w:LidThemeOther> <w:lidthemeasian>X-NONE</w:LidThemeAsian> <w:lidthemecomplexscript>X-NONE</w:LidThemeComplexScript> <w:compatibility> <w:breakwrappedtables/> <w:snaptogridincell/> <w:wraptextwithpunct/> <w:useasianbreakrules/> <w:dontgrowautofit/> <w:splitpgbreakandparamark/> <w:enableopentypekerning/> <w:dontflipmirrorindents/> <w:overridetablestylehps/> </w:Compatibility> <m:mathpr> <m:mathfont val="Cambria Math"> <m:brkbin val="before"> <m:brkbinsub val="--"> <m:smallfrac val="off"> <m:dispdef/> <m:lmargin val="0"> <m:rmargin val="0"> <m:defjc val="centerGroup"> <m:wrapindent val="1440"> <m:intlim val="subSup"> <m:narylim val="undOvr"> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:latentstyles deflockedstate="false" defunhidewhenused="true" defsemihidden="true" defqformat="false" defpriority="99" latentstylecount="267"> <w:lsdexception locked="false" priority="0" semihidden="false" unhidewhenused="false" qformat="true" name="Normal"> <w:lsdexception locked="false" priority="9" semihidden="false" unhidewhenused="false" qformat="true" name="heading 1"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 2"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 3"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 4"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 5"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 6"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 7"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 8"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 9"> <w:lsdexception locked="false" priority="39" name="toc 1"> <w:lsdexception locked="false" priority="39" name="toc 2"> <w:lsdexception locked="false" priority="39" name="toc 3"> <w:lsdexception locked="false" priority="39" name="toc 4"> <w:lsdexception locked="false" priority="39" name="toc 5"> <w:lsdexception locked="false" priority="39" name="toc 6"> <w:lsdexception locked="false" priority="39" name="toc 7"> <w:lsdexception locked="false" priority="39" name="toc 8"> <w:lsdexception locked="false" priority="39" name="toc 9"> <w:lsdexception locked="false" priority="35" qformat="true" name="caption"> <w:lsdexception locked="false" priority="10" semihidden="false" unhidewhenused="false" qformat="true" name="Title"> <w:lsdexception locked="false" priority="1" name="Default Paragraph Font"> <w:lsdexception locked="false" priority="11" semihidden="false" unhidewhenused="false" qformat="true" name="Subtitle"> <w:lsdexception locked="false" priority="22" semihidden="false" unhidewhenused="false" qformat="true" name="Strong"> <w:lsdexception locked="false" priority="20" semihidden="false" unhidewhenused="false" qformat="true" name="Emphasis"> <w:lsdexception locked="false" priority="59" semihidden="false" unhidewhenused="false" name="Table Grid"> <w:lsdexception locked="false" unhidewhenused="false" name="Placeholder Text"> <w:lsdexception locked="false" priority="1" semihidden="false" unhidewhenused="false" qformat="true" name="No Spacing"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 1"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 1"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 1"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 1"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 1"> <w:lsdexception locked="false" unhidewhenused="false" name="Revision"> <w:lsdexception locked="false" priority="34" semihidden="false" unhidewhenused="false" qformat="true" name="List Paragraph"> <w:lsdexception locked="false" priority="29" semihidden="false" unhidewhenused="false" qformat="true" name="Quote"> <w:lsdexception locked="false" priority="30" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Quote"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 1"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 1"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 1"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 1"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 1"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 1"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 1"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 2"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 2"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 2"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 2"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 2"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 2"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 2"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 2"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 2"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 2"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 2"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 3"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 3"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 3"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 3"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 3"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 3"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 3"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 3"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 3"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 3"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 3"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 3"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 3"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 4"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 4"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 4"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 4"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 4"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 4"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 4"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 4"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 4"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 4"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 4"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 4"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 4"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 4"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 5"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 5"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 5"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 5"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 5"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 5"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 5"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 5"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 5"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 5"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 5"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 5"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 5"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 5"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 6"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 6"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 6"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 6"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 6"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 6"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 6"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 6"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 6"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 6"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 6"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 6"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 6"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 6"> <w:lsdexception locked="false" priority="19" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Emphasis"> <w:lsdexception locked="false" priority="21" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Emphasis"> <w:lsdexception locked="false" priority="31" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Reference"> <w:lsdexception locked="false" priority="32" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Reference"> <w:lsdexception locked="false" priority="33" semihidden="false" unhidewhenused="false" qformat="true" name="Book Title"> <w:lsdexception locked="false" priority="37" name="Bibliography"> <w:lsdexception locked="false" priority="39" qformat="true" name="TOC Heading"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} </style> <![endif]--> </p><!--[if gte mso 9]><xml> <o:officedocumentsettings> <o:allowpng/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:worddocument> <w:view>Normal</w:View> <w:zoom>0</w:Zoom> <w:trackmoves/> <w:trackformatting/> <w:punctuationkerning/> <w:validateagainstschemas/> <w:saveifxmlinvalid>false</w:SaveIfXMLInvalid> <w:ignoremixedcontent>false</w:IgnoreMixedContent> <w:alwaysshowplaceholdertext>false</w:AlwaysShowPlaceholderText> <w:donotpromoteqf/> <w:lidthemeother>EN-US</w:LidThemeOther> <w:lidthemeasian>X-NONE</w:LidThemeAsian> <w:lidthemecomplexscript>X-NONE</w:LidThemeComplexScript> <w:compatibility> <w:breakwrappedtables/> <w:snaptogridincell/> <w:wraptextwithpunct/> <w:useasianbreakrules/> <w:dontgrowautofit/> <w:splitpgbreakandparamark/> <w:enableopentypekerning/> <w:dontflipmirrorindents/> <w:overridetablestylehps/> </w:Compatibility> <m:mathpr> <m:mathfont val="Cambria Math"> <m:brkbin val="before"> <m:brkbinsub val="--"> <m:smallfrac val="off"> <m:dispdef/> <m:lmargin val="0"> <m:rmargin val="0"> <m:defjc val="centerGroup"> <m:wrapindent val="1440"> <m:intlim val="subSup"> <m:narylim val="undOvr"> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:latentstyles deflockedstate="false" defunhidewhenused="true" defsemihidden="true" defqformat="false" defpriority="99" latentstylecount="267"> <w:lsdexception locked="false" priority="0" semihidden="false" unhidewhenused="false" qformat="true" name="Normal"> <w:lsdexception locked="false" priority="9" semihidden="false" unhidewhenused="false" qformat="true" name="heading 1"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 2"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 3"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 4"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 5"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 6"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 7"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 8"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 9"> <w:lsdexception locked="false" priority="39" name="toc 1"> <w:lsdexception locked="false" priority="39" name="toc 2"> <w:lsdexception locked="false" priority="39" name="toc 3"> <w:lsdexception locked="false" priority="39" name="toc 4"> <w:lsdexception locked="false" priority="39" name="toc 5"> <w:lsdexception locked="false" priority="39" name="toc 6"> <w:lsdexception locked="false" priority="39" name="toc 7"> <w:lsdexception locked="false" priority="39" name="toc 8"> <w:lsdexception locked="false" priority="39" name="toc 9"> <w:lsdexception locked="false" priority="35" qformat="true" name="caption"> <w:lsdexception locked="false" priority="10" semihidden="false" unhidewhenused="false" qformat="true" name="Title"> <w:lsdexception locked="false" priority="1" name="Default Paragraph Font"> <w:lsdexception locked="false" priority="11" semihidden="false" unhidewhenused="false" qformat="true" name="Subtitle"> <w:lsdexception locked="false" priority="22" semihidden="false" unhidewhenused="false" qformat="true" name="Strong"> <w:lsdexception locked="false" priority="20" semihidden="false" unhidewhenused="false" qformat="true" name="Emphasis"> <w:lsdexception locked="false" priority="59" semihidden="false" unhidewhenused="false" name="Table Grid"> <w:lsdexception locked="false" unhidewhenused="false" name="Placeholder Text"> <w:lsdexception locked="false" priority="1" semihidden="false" unhidewhenused="false" qformat="true" name="No Spacing"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 1"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 1"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 1"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 1"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 1"> <w:lsdexception locked="false" unhidewhenused="false" name="Revision"> <w:lsdexception locked="false" priority="34" semihidden="false" unhidewhenused="false" qformat="true" name="List Paragraph"> <w:lsdexception locked="false" priority="29" semihidden="false" unhidewhenused="false" qformat="true" name="Quote"> <w:lsdexception locked="false" priority="30" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Quote"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 1"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 1"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 1"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 1"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 1"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 1"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 1"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 2"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 2"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 2"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 2"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 2"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 2"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 2"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 2"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 2"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 2"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 2"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 3"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 3"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 3"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 3"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 3"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 3"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 3"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 3"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 3"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 3"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 3"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 3"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 3"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 4"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 4"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 4"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 4"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 4"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 4"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 4"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 4"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 4"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 4"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 4"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 4"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 4"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 4"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 5"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 5"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 5"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 5"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 5"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 5"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 5"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 5"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 5"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 5"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 5"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 5"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 5"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 5"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 6"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 6"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 6"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 6"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 6"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 6"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 6"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 6"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 6"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 6"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 6"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 6"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 6"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 6"> <w:lsdexception locked="false" priority="19" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Emphasis"> <w:lsdexception locked="false" priority="21" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Emphasis"> <w:lsdexception locked="false" priority="31" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Reference"> <w:lsdexception locked="false" priority="32" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Reference"> <w:lsdexception locked="false" priority="33" semihidden="false" unhidewhenused="false" qformat="true" name="Book Title"> <w:lsdexception locked="false" priority="37" name="Bibliography"> <w:lsdexception locked="false" priority="39" qformat="true" name="TOC Heading"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} </style> <![endif]--> <p class="MsoNormal"><span style="font-size:85%;">I have work with T-SQL debugging many times but first time I have notice above error. I try to find some information about this error, but I could not found good relative information. Finally I have decided compare my server configuration with error given server instance configuration. Microsoft categorizes most of above errors under [configuration problem]/ [security problem]. So my security comparison gives me the answer for above error. <span style=""> </span></span></p> <p class="MsoNormal"><span style="font-size:85%;">The problem is I have working under domain account. I connected in above error given server instance using “SA“ user, <span style=""> </span>the problem is “SA” user cannot execute above server T-SQL debugger. In that case I have created my domain account login to above error given SQL server instance. </span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10pt;color:blue;" ></span></p><blockquote><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10pt;color:blue;" >CREATE</span><span style=";font-family:";font-size:10pt;" > <span style="color:blue;">LOGIN</span> [DOMAIN\login name] <span style="color:blue;">FROM</span> <span style="color:blue;">WINDOWS</span> <span style="color:blue;">WITH</span> <span style="color:blue;">DEFAULT_DATABASE</span><span style="color:gray;">=</span>[master]<span style="color:gray;">,</span> <span style="color:blue;">DEFAULT_LANGUAGE</span><span style="color:gray;">=</span>[us_english]</span></p> <p class="MsoNormal"><span style="line-height: 115%;font-family:";font-size:10pt;color:blue;" >GO</span> <span style=""> </span><span style=""> </span></p></blockquote><p class="MsoNormal"><span style=""></span></p> <p class="MsoNormal"><span style="font-size:85%;">Great now it’s working. If anyone have above T-SQL Debugging error hope this post will help you!!!</span></p> <p class="MsoNormal"><img src="file:///C:/Users/THARIN%7E1.PER/AppData/Local/Temp/moz-screenshot.png" alt="" /></p>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com0tag:blogger.com,1999:blog-7296940595016042654.post-66438708040018978042010-11-12T04:04:00.000-08:002010-11-12T04:19:33.852-08:00ISDATE Function doesn’t validate “date” and “datetime2” data typesLast week I have done some R&D stuff reading SQL Server and as well as Oracle. Recently I have come up with issue how to validate Date column. Answer is very simple; Every SQL Server guy’s Answer is ISDATE function and my answer was same. But after few second, again have a complained we can’t use this function it gives error. I got surprised and ask him to send that error with table format,<br /><ul><li>Column data type is – <span style="font-weight: bold; color: rgb(51, 51, 255);font-family:courier new;" >date </span>(SQL Server 2008)</li><li>Error,</li></ul><span style="color: rgb(204, 0, 0);font-family:courier new;" > Msg 8116, Level 16, State 1, Line 3</span><br /><span style="color: rgb(204, 0, 0);font-family:courier new;" > Argument data type date is invalid for argument 1 of isdate function.</span><br /><br />Then I have created some sample T-SQL scripts and check the issue.<br /><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">USE </span>tempdb</span><br /><span style="font-family:courier new;">GO</span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">DECLARE </span>@smalldatetime <span style="color: rgb(51, 51, 255);">smalldatetime </span></span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">SELECT </span>@smalldatetime=<span style="color: rgb(204, 0, 0);">'01/01/2010'</span></span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">SELECT </span><span style="color: rgb(204, 51, 204);">ISDATE</span>(@smalldatetime);</span><br /><span style="font-family:courier new;">GO</span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">DECLARE </span>@date <span style="color: rgb(51, 51, 255);">date</span></span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">SELECT </span>@date=<span style="color: rgb(204, 0, 0);">'01/01/2010'</span></span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">SELECT </span><span style="color: rgb(204, 51, 204);">ISDATE</span>(@date); <span style="color: rgb(0, 153, 0);">--Error</span></span><br /><span style="font-family:courier new;">GO</span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">DECLARE </span>@datetime <span style="color: rgb(51, 51, 255);">datetime</span></span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">SELECT </span>@datetime=<span style="color: rgb(204, 0, 0);">'01/01/2010'</span></span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">SELECT </span><span style="color: rgb(204, 51, 204);">ISDATE</span>(@datetime);</span><br /><span style="font-family:courier new;">GO</span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">DECLARE </span>@datetime2 <span style="color: rgb(51, 51, 255);">datetime2</span></span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">SELECT </span>@datetime2=<span style="color: rgb(204, 0, 0);">'01/01/2010'</span></span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">SELECT </span><span style="color: rgb(204, 51, 204);">ISDATE</span>(@datetime2); <span style="color: rgb(0, 153, 0);">--Error</span></span><br /><span style="font-family:courier new;">GO</span><br /><br />Finally I realize the problem <a href="http://msdn.microsoft.com/en-us/library/ms186724.aspx">ISDATE </a>function, only working datetime, smalldatetime data types. Funny thing is Microsoft did not update the <a href="http://msdn.microsoft.com/en-us/library/ms187347.aspx">ISDATE ()</a> function to support this data types. Even it’s not supported SQL SERVER 2008 R2. Simply it doesn’t validate “<span style="font-family: courier new; color: rgb(51, 51, 255); font-weight: bold;">date</span>” data type and “<span style="font-family: courier new; font-weight: bold; color: rgb(51, 51, 255);">datetime2</span>” data type. After checking details for this issue <a href="https://connect.microsoft.com/sql/feedback/ViewFeedback.aspx?FeedbackID=327335&wa=wsignin1.0">Microsoft promised to consider this</a> in there next release (I didn’t check this issue using SQL Server new <a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9">CTP1 release (”Denali”)</a>)Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com0tag:blogger.com,1999:blog-7296940595016042654.post-87846031635251156352010-10-16T07:51:00.000-07:002010-10-16T08:10:02.777-07:00Some doubt about integer (INT) data type columns<div style="text-align: center; font-weight: bold;"><span style="font-size:130%;">Some doubt about integer (INT) data type columns<br /></span></div><br /><span style="font-family:verdana;">Recently I have some doubt about integer (INT) data type column. Let me explain this doubtful problem, if you insert or update blank records to integer column it will update as 0.But if it is decimal data type situation bit different. Check following example,</span><br /><br /><span style="font-family:courier new;"><span style="color: rgb(51, 102, 255);">use </span>tempdb</span><br /><span style="font-family:courier new;">go</span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">drop table</span> T1</span><br /><span style="font-family:courier new;">go</span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">create table</span> T1(col1 <span style="color: rgb(51, 51, 255);">int primary key </span></span><br /><span style="font-family:courier new;"> ,col2 <span style="color: rgb(51, 51, 255);">tinyint</span></span><br /><span style="font-family:courier new;"> ,col3 <span style="color: rgb(51, 51, 255);">smallint</span></span><br /><span style="font-family:courier new;"> ,col4 <span style="color: rgb(51, 51, 255);">int</span></span><br /><span style="font-family:courier new;"> ,col5 <span style="color: rgb(51, 51, 255);">bigint</span></span><br /><span style="font-family:courier new;"> ,col6 <span style="color: rgb(51, 51, 255);">decimal</span>(9,1)</span><br /><span style="font-family:courier new;"> ,col7 <span style="color: rgb(51, 51, 255);">varchar</span>(5));</span><br /><span style="font-family:courier new;">go</span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">select</span> * <span style="color: rgb(51, 51, 255);">from</span> T1</span><br /><span style="font-family:courier new;">go</span><br /><span style="color: rgb(51, 51, 255);font-family:courier new;" >begin tran</span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">insert into</span> T1 <span style="color: rgb(51, 51, 255);">select </span>1,11,111,1111,11111,111111,<span style="color: rgb(255, 102, 102);">'R1'</span></span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">insert into</span> T1 <span style="color: rgb(51, 51, 255);">select </span><span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">''</span>,-1,<span style="color: rgb(255, 102, 102);">''</span></span><br /><span style="color: rgb(51, 51, 255);font-family:courier new;" >commit tran</span><br /><span style="font-family:courier new;">go</span><br /><span style="font-family:courier new;"><span style="color: rgb(51, 51, 255);">select </span>* <span style="color: rgb(51, 51, 255);">from </span>T1</span><br /><span style="font-family:courier new;">go<br /><br /><span style="font-family:verdana;">If you try following example then you may come up with this error,<br /><br /><span style="color: rgb(51, 51, 255);">begin tran</span><br /><span style="color: rgb(51, 51, 255);">insert into</span> T1 <span style="color: rgb(51, 51, 255);">select </span>1,11,111,1111,11111,111111,<span style="color: rgb(255, 102, 102);">'R1'</span><br /><span style="color: rgb(51, 51, 255);">insert into</span> T1 <span style="color: rgb(51, 51, 255);">select </span><span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">''</span>,<span style="color: rgb(255, 102, 102);">'' <span style="color: rgb(51, 204, 0);">--error</span></span><br /></span></span><span style="font-family:courier new;"><span style="font-family:verdana;"><span style="color: rgb(51, 51, 255);">insert into</span> T1 <span style="color: rgb(51, 51, 255);">select </span><span style="color: rgb(255, 102, 102);">' '</span>,<span style="color: rgb(255, 102, 102);">' '</span>,<span style="color: rgb(255, 102, 102);">' '</span>,<span style="color: rgb(255, 102, 102);">' '</span>,<span style="color: rgb(255, 102, 102);">' '</span>,<span style="color: rgb(255, 102, 102);">' '</span>,<span style="color: rgb(255, 102, 102);">' ' <span style="color: rgb(51, 204, 0);">--error</span></span></span></span><br /><span style="font-family:courier new;"><span style="font-family:verdana;"><span style="color: rgb(51, 102, 255);">commit tran</span><br /></span><br /><span style="font-family:verdana;">Result,</span><br /><span style="color: rgb(255, 102, 102);">Server: Msg 8114, Level 16, State 5, Line 1</span><br />Error converting data type varchar to numeric.<br /><br /><span style="font-family:verdana;">Finally I come up with some conclusion but I have some doubt when we dealing with decimal data type. MSSQL consider blank value as 0 if you use only integer data type but if you dealing with string data type(varchar) it will remain as same. I will try to find more information about decimal data type and update it in future </span></span><span style="font-family:courier new;"><span style="font-family:verdana;">post</span></span><span style="font-family:courier new;"><span style="font-family:verdana;">.</span><br /></span>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com6tag:blogger.com,1999:blog-7296940595016042654.post-32607983777675453292010-02-13T05:41:00.000-08:002010-02-14T02:18:05.525-08:00How to add WHERE clause after execute procedure returns values?<span style=";font-family:trebuchet ms;font-size:100%;" >Let me start like this “Do we have any mechanism to add WHERE clause after execute procedure returns values?” Answer is <span style="font-weight: bold;">YES </span>but we need to spend bit time. But recently I found smart Answer for above Question.</span><span style=";font-family:trebuchet ms;font-size:100%;" >i will explain here my old concept and new concept,</span><br /><br /><span style="font-weight: bold;">Old concept,</span><br /><br /><span style="font-size:85%;"><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">USE </span>AdventureWorks2008</span><br /><span style="font-family:courier new;">GO</span><br /><span style="color: rgb(0, 153, 0);font-family:courier new;" >--create table</span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">CREATE TABLE</span> MyRecords</span><br /><span style="font-family:courier new;">(col1 <span style="color: rgb(0, 0, 153);">int</span>,</span><br /><span style="font-family:courier new;"> col2 <span style="color: rgb(0, 0, 153);">varchar</span>(25)</span><br /><span style="font-family:courier new;">);</span><br /><span style="font-family:courier new;">GO</span><br /><br /><span style="color: rgb(0, 153, 0);font-family:courier new;" >--insert Records</span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">INSERT INTO</span> MyRecords</span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">SELECT TOP </span>15 [object_id],[name] </span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">FROM </span><span style="color: rgb(0, 153, 0);">sys.objects</span></span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">WHERE </span>(<span style="color: rgb(204, 51, 204);">LEN</span>([name]) <span style="color: rgb(102, 102, 102);">BETWEEN </span>0 AND 25)</span></span><span style="font-size:85%;"><span style="font-family:courier new;"><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);"><span style="color: rgb(0, 153, 0);">/* cannot add less-then sign because of html validation */</span><br />ORDER BY</span> [object_id];</span><br /><span style="font-family:courier new;">GO</span><br /><br /><span style="color: rgb(0, 153, 0);font-family:courier new;" >--create procedure</span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">CREATE PROCEDURE</span> proc_get_records</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >AS</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >SET NOCOUNT ON</span><br /><span style="font-family:courier new;"> <span style="color: rgb(0, 0, 153);">SELECT </span>col1 [object_id], col2 [name] <span style="color: rgb(0, 0, 153);">FROM </span>MyRecords;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >SET NOCOUNT OFF</span><br /><span style="font-family:courier new;">GO</span><br /><br /><span style="color: rgb(0, 153, 0);font-family:courier new;" >--execute sp</span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">EXEC </span>proc_get_records; <span style="color: rgb(0, 153, 0);">--this will returns 15 rows</span></span><br /><span style="font-family:courier new;">GO</span><br /><br /><span style=";font-family:trebuchet ms;font-size:100%;" >If you need to check your procedure returns values contain some specific value, then you have to do following mechanism.</span></span></span><span style="font-size:85%;"><span style="font-family:courier new;"><span style=";font-family:trebuchet ms;font-size:100%;" ></span><br /><br /><span style="color: rgb(0, 153, 0);font-family:courier new;" >--if we need to check procedure returns value contain object id 7 </span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">CREATE TABLE</span> #tempTable</span><br /><span style="font-family:courier new;">(col1 <span style="color: rgb(0, 0, 153);">int</span>,</span><br /><span style="font-family:courier new;"> col2 <span style="color: rgb(0, 0, 153);">varchar</span>(25)</span><br /><span style="font-family:courier new;">);</span><br /><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">INSERT INTO</span> #tempTable</span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">EXEC </span>proc_get_records;</span><br /><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">SELECT </span>* <span style="color: rgb(0, 0, 153);">FROM </span>#tempTable <span style="color: rgb(0, 0, 153);">WHERE </span>(col1=7);</span><br /><span style="font-family:courier new;">GO<br /><br /></span></span></span><span style="font-weight: bold;">New concept,</span><br /><br /><span style="font-size:100%;"><span style="font-family:trebuchet ms;">Now you can realize above method very effective but time consuming work.</span></span><span style="font-size:85%;"><span style="font-family:courier new;"><span style=";font-family:trebuchet ms;font-size:100%;" >Let’s say your procedure contains many columns with different data types and finally it will </span></span></span><span style="font-size:100%;"><span style="font-family:courier new;"><span style="font-family:trebuchet ms;">returns more than thousand rows</span></span></span><span style="font-size:85%;"><span style="font-family:courier new;"><span style=";font-family:trebuchet ms;font-size:100%;" >. Then no doubt this will be, bit time consuming processes. And sometimes your Production Database environment does not allow you to create temp tables or you might be haven’t any permission to create tables. Then this will be painful problem.This is how you can sort-out above Problem,</span></span></span><span style="font-size:100%;"><span style="font-family:trebuchet ms;">This is very smart concept I found recently when I’m go through <span style="font-weight: bold;">OPENDATASOURCE </span>and <span style="font-weight: bold;">OPENROWSET </span>functions.And pls Notice,i`m using same table formats I have created earlier to explain this,</span></span><br /><br /><span style=";font-family:courier new;font-size:85%;" ><span style="color: rgb(0, 0, 153);">USE </span>AdventureWorks2008<br />GO<br /><span style="color: rgb(0, 0, 153);">EXEC </span>proc_get_records;<br />GO<br /><span style="color: rgb(0, 0, 153);">SELECT </span>*<br /><span style="color: rgb(0, 0, 153);">FROM OPENROWSET</span>(<span style="color: rgb(204, 0, 0);">'SQLOLEDB'</span>,<br /> <span style="color: rgb(204, 0, 0);">'Server=THARINDU\SQLDEV2008;Trusted_Connection=yes;'</span>,<br /> <span style="color: rgb(204, 0, 0);">'EXEC AdventureWorks2008.dbo.proc_get_records'</span>)<br /><span style="color: rgb(0, 0, 153);">WHERE </span>([object_id] <span style="color: rgb(0, 0, 153);">IN</span>(5,3,7))<br /><span style="color: rgb(0, 0, 153);">ORDER BY</span> [object_id];</span><br /><br /><span style="font-size:100%;"><span style="font-family:trebuchet ms;">Still I couldn’t found good mechanism to do same concept using oracle. Any way I’ll try and Update my blog in future post. </span></span>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com1tag:blogger.com,1999:blog-7296940595016042654.post-44149599393484836182009-12-30T07:27:00.000-08:002010-01-24T10:32:47.097-08:00Move Data one table to another table.<span style="font-size:85%;"><span style="font-family:verdana;">When we comes to Table designing and the table is</span></span><span style="font-size:85%;"><span style="font-family:verdana;"> very large table, then</span></span><span style="font-size:85%;"><span style="font-family:verdana;"> we can consider Table-Partitioning is most important factor. using above concept </span></span><span style="font-size:85%;"><span style="font-family:verdana;">we can gain huge</span></span><span style="font-size:85%;"><span style="font-family:verdana;"> Performance and very easy to manage table containing records. In this topic I’m not going to discuss all about Table-Partitioning. But this is more related Table-Partitioning. If we want to move one particular range of data or all records of data one table to another table then Table-Partitioning come and play big role. This is less time consuming process.</span></span><br /><br /><span style="color: rgb(0, 102, 0);font-family:courier new;" >/* Formatted on 2009/12/30 20:36 (Formatter Plus v4.8.7) */</span><br /><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">CREATE TABLE</span> sourceTable(</span><br /><span style="font-family:courier new;">column_num <span style="color: rgb(102, 0, 0);">NUMBER</span>(18,0),</span><br /><span style="font-family:courier new;">column_chr <span style="color: rgb(102, 0, 0);">VARCHAR2</span>(2)</span><br /><span style="font-family:courier new;">)</span><br /><span style="font-family:courier new;"> <span style="color: rgb(0, 0, 153);">PARTITION BY RANGE</span> (column_num)</span><br /><span style="font-family:courier new;"> (</span><br /><span style="font-family:courier new;"> <span style="color: rgb(0, 0, 153);">PARTITION </span>tab_h1 <span style="color: rgb(0, 0, 153);">VALUES </span>LESS THAN</span><br /><span style="font-family:courier new;"> (11),</span><br /><span style="font-family:courier new;"> <span style="color: rgb(0, 0, 153);">PARTITION </span>tab_h2 <span style="color: rgb(0, 0, 153);">VALUES </span>LESS THAN</span><br /><span style="font-family:courier new;"> (21)</span><br /><span style="font-family:courier new;"> );</span><br /><br /><span style="color: rgb(0, 102, 0);">--Insert test Records</span><br /><span style="color: rgb(0, 0, 153);">INSERT INTO</span> sourceTable<br /><span style="color: rgb(0, 0, 153);">SELECT ROWNUM</span>, somecode<br /><span style="color: rgb(0, 0, 153);">FROM </span>(<span style="color: rgb(0, 0, 153);">SELECT ROWNUM </span>noofrows,<span style="color: rgb(0, 102, 0);">'TD'</span> somecode<br /> <span style="color: rgb(0, 0, 153);">FROM </span>dba_objects)<br /><span style="color: rgb(0, 0, 153);">WHERE </span>noofrows < 21;<br /><br /><span style="color: rgb(0, 0, 153);">COMMIT </span>;<br /><br /><span style="color: rgb(0, 102, 0);font-family:courier new;" >--Check no of records in Table </span><br /><span style="color: rgb(0, 0, 153);">SELECT </span><span style="font-weight: bold;">count</span>(*)<br /><span style="color: rgb(0, 0, 153);">FROM </span>sourceTable;<br /><br /><span style="color: rgb(0, 0, 153);">SELECT </span><span style="font-weight: bold;"></span>*<br /><span style="color: rgb(0, 0, 153);">FROM </span>sourceTable;<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPOD9-H7x1iSLX0gP_HAefQ39lFRZKWeTd51fRQUjFFfGpck0-wqARAYt58BN8AMo2tHKzBC6xO_bxlyOClGesUncGxOS0TRwNKCgno46MtxCa3kjmjUqxnI-1nV41r14ADXONDA3yZOg/s1600-h/select_Dhaneenja.JPG"><img style="cursor: pointer; width: 212px; height: 320px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPOD9-H7x1iSLX0gP_HAefQ39lFRZKWeTd51fRQUjFFfGpck0-wqARAYt58BN8AMo2tHKzBC6xO_bxlyOClGesUncGxOS0TRwNKCgno46MtxCa3kjmjUqxnI-1nV41r14ADXONDA3yZOg/s320/select_Dhaneenja.JPG" alt="" id="BLOGGER_PHOTO_ID_5421058357427387922" border="0" /></a><br /><br /><span style="color: rgb(0, 102, 0);font-family:courier new;" >--Create test Table 2</span><br /><span style="color: rgb(0, 0, 153);">CREATE TABLE</span> DestinationTable(<br />column_num <span style="color: rgb(102, 0, 0);">NUMBER</span>(18,0),<br />column_chr <span style="color: rgb(102, 0, 0);">VARCHAR2</span>(2)<br />);<br /><br />--check step 1<br /><span style="color: rgb(0, 0, 153);">ALTER TABLE</span> sourceTable<br /><span style="color: rgb(0, 0, 153);">EXCHANGE PARTITION</span> tab_h1<br /><span style="color: rgb(0, 0, 153);">WITH TABLE</span> DestinationTable;<br /><br />--or check step 2<br /><span style="color: rgb(0, 0, 153);">ALTER TABLE</span> sourceTable<br /><span style="color: rgb(0, 0, 153);"> EXCHANGE PARTITION</span> tab_h2<br /><span style="color: rgb(0, 0, 153);">WITH TABLE</span> DestinationTable;<br /><br /><br /><span style="color: rgb(0, 0, 153);">SELECT </span>*<br /><span style="color: rgb(0, 0, 153);"> FROM</span> sourcetable;<br /><br /><span style="color: rgb(0, 0, 153);">SELECT</span> *<br />FROM DestinationTable;<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF3fbzUOuneMv5jAUCctbTEV6nC5Ad1NCp_dIQQr02uBadYE0oLndf0Mm4nXmfM5XoIQK6e_vQy5jLswyusbKLI2hufzqy43xnpEQ8_yK2pwfJ05Sc3iytmjSHWP091Ur7NTTPbi7jGTM/s1600-h/select_data_Dhaneenja.JPG"><img style="cursor: pointer; width: 212px; height: 320px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF3fbzUOuneMv5jAUCctbTEV6nC5Ad1NCp_dIQQr02uBadYE0oLndf0Mm4nXmfM5XoIQK6e_vQy5jLswyusbKLI2hufzqy43xnpEQ8_yK2pwfJ05Sc3iytmjSHWP091Ur7NTTPbi7jGTM/s320/select_data_Dhaneenja.JPG" alt="" id="BLOGGER_PHOTO_ID_5421058359583052018" border="0" /></a><br /><br /><span style="color: rgb(0, 102, 0);">--drop test tables</span><br />DROP TABLE sourceTable;<br />DROP TABLE destinationTable;<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtpTFFrgdDhUzcmYlu9d5LXU-H2LmfQ8P2mDIsK3IJanC4YjmsqeYrxXiylTZYakM9a71prmHHBh0VdlHLLRZ5-qNEy4YbEkbj8ujHC1RPzgidUcyljn21gjU7c81FeEK_w05si7MuTUc/s1600-h/TABLE_PARTITION_Dhaneenja.JPG"><img style="cursor: pointer; width: 278px; height: 320px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtpTFFrgdDhUzcmYlu9d5LXU-H2LmfQ8P2mDIsK3IJanC4YjmsqeYrxXiylTZYakM9a71prmHHBh0VdlHLLRZ5-qNEy4YbEkbj8ujHC1RPzgidUcyljn21gjU7c81FeEK_w05si7MuTUc/s320/TABLE_PARTITION_Dhaneenja.JPG" alt="" id="BLOGGER_PHOTO_ID_5421058348069870786" border="0" /></a><br /><br />Many time we have done “<span style="font-family:courier new;">create table </span><new><span style="font-family:courier new;"> as select …“(PL/SQL), “Inseet into </span><new><span style="font-family:courier new;"> select…</span>”(T/SQL) commands to move data one table to new table or given table. But above methods high costly and time consuming operations (EXCHANGE PARTITION command just take 1 ms to move millions of records). Good news is <a href="http://jahaines.blogspot.com/2009/12/sql-server-2005-how-to-move-10-millions.html">SQL Server also support above PARTITION</a> method but only SQL Server 2005 and 2008 versions only.</new></new>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com4tag:blogger.com,1999:blog-7296940595016042654.post-42129809654350434522009-12-28T12:24:00.000-08:002010-12-07T04:35:56.861-08:00Unfair limitations of Oracle Database 10G<div style="text-align: center; font-weight: bold;font-family:verdana;"><span style="font-size:85%;">Unfair limitations of Oracle Database 10G<br /></span></div><span style="font-size:85%;"><span style="font-family:verdana;">Oracle one of great database Product and it takes more than 50% of market share. But I’m honestly saying Oracle has very unfair limitations. Few of them are,</span><br /></span><ul style="font-family:verdana;"><li><span style="font-size:85%;">Database name length (no of Digits) – 8</span></li></ul><ul style="font-family:verdana;"><li><span style="font-size:85%;">Table, Procedure...Etc name length (no of Digits) – 30</span></li></ul><span style="font-size:85%;"><span style="font-family:verdana;">But recently I was come across Oracle Installation Problem, </span></span><br /><ol><li><span style="font-size:85%;"><span style="font-weight: bold;font-family:verdana;" >“Invalid command line argument. Execute VSJITDebuggar /? for Help”. Javaw.exe has encountered a Problem and need to close. </span></span></li><li><span style="font-size:85%;"><span style="font-family:verdana;"><span style="font-weight: bold;">“An unhandled win32 exception occurred in Javaw.exe [5676] “</span></span></span></li></ol><span style="font-size:85%;"><span style="font-family:verdana;">I was nearly spent more than 1 week but I couldn’t found any solution. Unfortunately there haven’t any blog or article related this. Luckily <a href="http://www.facebook.com/people/Buddhika-Karunatilake/752240874">Buddy (Buddhika Karunatilake)</a> find out the solution. The problem was my <span style="font-weight: bold;">Oracle installation contain folder path too long. (“C:\Documents and Settings\Administrator\Desktop\Oracle\ Oracle-10.2.0.1.0-WinNT-Base\database”)</span> I found few oracle articles about oracle limitation,</span><br /></span><ol><li><span style="font-size:85%;"><span style="font-family:verdana;"><a href="http://www.oracle.com/technology/products/ias/bpel/htdocs/soa_training_10133_installation_instructions.html">http://www.oracle.com/technology/products/ias/bpel/htdocs/soa_training_10133_installation_instructions.html</a> - (Check 2. Installing the software)</span></span></li><li><a href="http://download.oracle.com/docs/cd/B19188_01/doc/B15917/limitations.htm"><span style="font-size:85%;"><span style="font-family:verdana;"><span></span></span></span></a><span style="font-size:85%;"><span style="font-family:verdana;"><a href="http://download.oracle.com/docs/cd/B19188_01/doc/B15917/limitations.htm">http://download.oracle.com/docs/cd/B19188_01/doc/B15917/limitations.htm</a></span></span></li><li><a href="http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm"><span style="font-size:85%;"><span style="font-family:verdana;"><span></span></span></span></a><span style="font-size:85%;"><span style="font-family:verdana;"><a href="http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm">http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm</a></span></span></li><li><a href="http://www.gc.maricopa.edu/business/oracle/docs/Oracle8iDocs/server.815/a67790/ch4.htm"><span style="font-size:85%;"><span style="font-family:verdana;"><span></span></span></span></a><span style="font-size:85%;"><span style="font-family:verdana;"><a href="http://www.gc.maricopa.edu/business/oracle/docs/Oracle8iDocs/server.815/a67790/ch4.htm">http://www.gc.maricopa.edu/business/oracle/docs/Oracle8iDocs/server.815/a67790/ch4.htm</a></span></span></li></ol><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwlD4PHqbKeCeZSEl-xQzp7vQxcdpaNAOGBKYQNcRNiBBm8L7DVTLEO5LyfuFINAidrk4k9UFtuQtfsAgUqwkymfdhKSm-WalpllD5-Wy0PR4V6Z8VRcvx3VUUPddMZ9PJBFYHdYtZl4w/s1600-h/oracle_10g_javaw.exe+error-+Tharindu+Dhaneenja.JPG"><img style="cursor: pointer; width: 320px; height: 250px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwlD4PHqbKeCeZSEl-xQzp7vQxcdpaNAOGBKYQNcRNiBBm8L7DVTLEO5LyfuFINAidrk4k9UFtuQtfsAgUqwkymfdhKSm-WalpllD5-Wy0PR4V6Z8VRcvx3VUUPddMZ9PJBFYHdYtZl4w/s320/oracle_10g_javaw.exe+error-+Tharindu+Dhaneenja.JPG" alt="" id="BLOGGER_PHOTO_ID_5420386206653156322" border="0" /></a><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjk16CJBl74QHOpMXsNXoHv_Z8O-pbX069YNoX61JZd9RcHKb05Miuhvono_11TlshzHoDQEh5yIp9sa_UkANK2osjGx2A9-tzW7SfPbTTT3E3E9hm-lKERKdNLqDYj7nANl9upoLeH96E/s1600-h/javaw.exe_error-+Tharindu+Dhaneenja.JPG"><img style="cursor: pointer; width: 320px; height: 226px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjk16CJBl74QHOpMXsNXoHv_Z8O-pbX069YNoX61JZd9RcHKb05Miuhvono_11TlshzHoDQEh5yIp9sa_UkANK2osjGx2A9-tzW7SfPbTTT3E3E9hm-lKERKdNLqDYj7nANl9upoLeH96E/s320/javaw.exe_error-+Tharindu+Dhaneenja.JPG" alt="" id="BLOGGER_PHOTO_ID_5420386204293660306" border="0" /></a><br /><a href="http://linkpartners.dotnetforum.lk/default.aspx"><br /></a>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com4tag:blogger.com,1999:blog-7296940595016042654.post-65757208394983489162009-12-26T10:34:00.000-08:002009-12-26T10:53:03.755-08:00SQL Server 2008 Books Online Has Small mistake.Recently I was start to complete my <a href="http://www.sql-server-performance.com/articles/dba/Understanding_the_Basic_of_Triggers_p1.aspx">Understanding the Basic of Triggers</a> Article Part II hope to include all topics related Triggers in that case I want to explain <a href="http://msdn.microsoft.com/en-us/library/ms186329.aspx">COLUMNS_UPDATED (Transact-SQL) Syntax</a> because everybody know it`s very useful when we working with triggers . Hope everybody familiar with <a href="http://msdn.microsoft.com/en-us/library/ms186329.aspx">COLUMNS_UPDATED (Transact-SQL) Syntax</a> so I’m not going to explain it this time (after complete my Article then you can get clear idea so pls wait for while). I want to say there is small mistake in SQL Server 2008 Books Online (November 2009) under <a href="http://msdn.microsoft.com/en-us/library/ms186329.aspx">COLUMNS_UPDATED (Transact-SQL)</a> category, example of <a href="http://msdn.microsoft.com/en-us/library/ms186329.aspx">“B. Using </a><a href="http://msdn.microsoft.com/en-us/library/ms186329.aspx">COLUMNS_UPDATED</a><a href="http://msdn.microsoft.com/en-us/library/ms186329.aspx"> to test more than eight columns”</a> under this headline. I’m honestly saying this is not big mistake but my view is Microsoft Technical writing and QA team needs to get responsibility of this mistake. So when we comes to this mistake,<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;"><span style="color: rgb(0, 0, 153);">USE </span>AdventureWorks;</span> <span style="font-family:courier new;"><br />GO</span> <span style="font-family:courier new;"><br />IF <span style="color: rgb(204, 51, 204);">OBJECT_ID </span>(N<span style="color: rgb(255, 0, 0);">'uContact2</span>', N<span style="color: rgb(255, 0, 0);">'TR'</span>) IS NOT NULL</span> <span style="font-family:courier new;"><br />DROP TRIGGER Person.</span><span style="font-weight: bold;font-family:courier new;" >tr1</span><span style="font-family:courier new;">; </span></span><span style="font-weight: bold; color: rgb(204, 0, 0);font-family:courier new;font-size:100%;" ><---mistake</span><span style="font-size:85%;"> <span style="font-family:courier new;"><br />GO</span> <span style="font-family:courier new;"><br /><span style="color: rgb(0, 0, 153);">CREATE TRIGGER</span> uContact2 ON Person.Contact</span> <span style="font-family:courier new;"><br /><span style="color: rgb(0, 0, 153);">AFTER UPDATE AS</span></span> <span style="font-family:courier new;"><br />IF ( (<span style="color: rgb(204, 51, 204);">SUBSTRING</span>(<span style="color: rgb(204, 51, 204);">COLUMNS_UPDATED</span>(),1,1) & 20 = 20)</span> <span style="font-family:courier new;"> <br /> <span style="color: rgb(102, 102, 102);">AND </span>(<span style="color: rgb(204, 51, 204);">SUBSTRING</span>(<span style="color: rgb(204, 51, 204);">COLUMNS_UPDATED</span>(),2,1) & 1 = 1) )</span> <span style="font-family:courier new;"><br /> <span style="color: rgb(0, 0, 153);">PRINT </span><span style="color: rgb(255, 0, 0);">'Columns 3, 5 and 9 updated'</span>;</span> <span style="font-family:courier new;"><br />GO</span> <span style="font-family:courier new;"><br /><br /><span style="color: rgb(0, 0, 153);">UPDATE </span>Person.Contact</span> <span style="font-family:courier new;"><br /><span style="color: rgb(0, 0, 153);">SET </span>Title=Title,</span> <span style="font-family:courier new;"> MiddleName=MiddleName,</span> <span style="font-family:courier new;"> <br /> EmailPromotion=EmailPromotion;</span><br /><span style="font-family:courier new;">GO</span></span><br /><br />If you run above SQL statement in first time your AdventureWorks database its runs successfully but if you run it again then it will gives you following error.<br /><br /><span style="font-size:85%;"><span style="color: rgb(255, 0, 0);font-family:courier new;" >Msg 2714, Level 16, State 2, Procedure uContact2, Line 5</span> <span style="color: rgb(255, 0, 0);font-family:courier new;" >There is already an object named 'uContact2' in the database.</span></span><br /><br />Hope now everybody will understand the mistake and it’s not big mistake either it’s not good for company like Microsoft.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9EXSLJhp3RzxP1-G5KmYntkNufhTdQgSfZ7Zb8g-rApfI-ix8czuVy0CxgSSdDvt8AEikL86M7pq6h_ln8rJElT8IaHjcfS2ctQCyBngRTgoyD3Dqz-IEBUbQ8dak-2eBYpgRzKnAoNg/s1600-h/COLUMNS_UPDATED+%28Transact-SQL%29-+Tharindu+Dhaneenja.JPG"><img style="cursor: pointer; width: 320px; height: 200px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9EXSLJhp3RzxP1-G5KmYntkNufhTdQgSfZ7Zb8g-rApfI-ix8czuVy0CxgSSdDvt8AEikL86M7pq6h_ln8rJElT8IaHjcfS2ctQCyBngRTgoyD3Dqz-IEBUbQ8dak-2eBYpgRzKnAoNg/s320/COLUMNS_UPDATED+%28Transact-SQL%29-+Tharindu+Dhaneenja.JPG" alt="" id="BLOGGER_PHOTO_ID_5419616636336738866" border="0" /></a>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com0tag:blogger.com,1999:blog-7296940595016042654.post-83130156683390861832008-07-29T02:25:00.000-07:002009-05-13T00:10:54.937-07:00small bug in SQL server 2005 developer edition with SP 2<span style="font-size:85%;"><span style="font-family:arial;">Recently SQL server 2008 also release but SQL server 2005 I notice there has small bug but it’s not the big issue, but SQL server QA team need to find out those things. This error I notice SQL server Developer edition when you create a backup database using graphical view (Not the T-SQL) after finish database backup it show Executing (0 %) I see this error twice normally I’m taking backups 5 or 6 time per day. And I was install SQL server 2005 developer edition with SP 2,<br /><br /></span></span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9HNY4t4TzbjGvc_T_o-FLYpY1HiuGTX3HvCf9oe0Oh7m52bweBD0BRPaHOOR6LUQZct0HKN9kSV5usc2-Go_ggGQmO1OJvxmbHDQJJzqKfnqfWl7hP7nIk0KpvOOYS-3_lv9X84bEjpA/s1600-h/Errors.JPG"><img style="cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9HNY4t4TzbjGvc_T_o-FLYpY1HiuGTX3HvCf9oe0Oh7m52bweBD0BRPaHOOR6LUQZct0HKN9kSV5usc2-Go_ggGQmO1OJvxmbHDQJJzqKfnqfWl7hP7nIk0KpvOOYS-3_lv9X84bEjpA/s320/Errors.JPG" alt="" id="BLOGGER_PHOTO_ID_5228365838816742882" border="0" /></a>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com1tag:blogger.com,1999:blog-7296940595016042654.post-39880987301507677492008-06-24T03:35:00.000-07:002009-05-13T00:11:12.836-07:00Minimum year value in SQL server 2008(date data type)<h4 style="margin-bottom: 0px;" id="subjcns!52063E89E23A5AAB!453">Minimum year value in SQL server 2008(date data type)</h4><span style="font-size:85%;"><span style="font-family:Verdana;">SQL server 2008 comes with lots of new features and they separated SQL server datetime data type as Date and Time (datetime data type also available).SQL server previous versions (SQL 2000, SQL 2005) there is limit for min date (year-1753) and max date (year-9999). But when you notice SQL server 2008 date data type min date is </span></span><b style=""><span style="font-size:85%;"><span style="font-family:Verdana;">0001.<br /><br /></span></span></b><table style="width: 100%;" width="100%" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="color: rgb(0, 176, 80);"><td style="border-style: none none solid; border-width: medium medium 1pt; padding: 0in 3.75pt; background: rgb(216, 216, 216) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><br /></td><td style="border-style: none none solid; border-width: medium medium 1pt; padding: 0in 3.75pt; background: rgb(216, 216, 216) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><br /></td></tr><tr style="font-family:Courier New;"><td colspan="2" style="border-style: solid none none; border-color: rgb(216, 216, 216) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3.75pt 3.75pt 0in; background: rgb(204, 204, 204) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><span style="font-size:85%;"><span style="color: rgb(0, 176, 80);">/*</span><br /><span style="color: rgb(0, 176, 80);">Date 24/06/2008</span><br /><span style="color: rgb(0, 176, 80);">SQL SERVER 2000 and 2005</span><br /><span style="color: rgb(0, 176, 80);">*/</span><br /><span style="color: rgb(23, 54, 93);">DROP TABLE </span>dateCheck;<br /><span style="color: rgb(23, 54, 93);">CREATE TABLE</span> dateCheck(dateCol <span style="color: rgb(23, 54, 93);">datetime</span>);<br />GO<br /><span style="color: rgb(0, 176, 80);">--can</span><br /><span style="color: rgb(23, 54, 93);">INSERT INTO</span> dateCheck(dateCol) VALUES (<span style="color: rgb(255, 0, 0);">'12/31/1753'</span>)<br />GO<br /><span style="color: rgb(0, 176, 80);">--can't</span><br /><span style="color: rgb(23, 54, 93);">INSERT INTO</span> dateCheck(dateCol) VALUES (<span style="color: rgb(255, 0, 0);">'01/01/1752'</span>)<br />GO<br /><span style="color: rgb(23, 54, 93);">SELECT * FROM</span> dateCheck<br />GO<br /><br /><span style="color: rgb(0, 176, 80);">/*</span><br /><span style="color: rgb(0, 176, 80);">Date 24/06/2008</span><br /><span style="color: rgb(0, 176, 80);">SQL SERVER 2008</span><br /><span style="color: rgb(0, 176, 80);">*/</span><br />--datetime data type<br /><span style="color: rgb(23, 54, 93);">DROP TABLE</span> dateCheckDateTime;<br />CREATE TABLE dateCheck(dateCol <span style="color: rgb(23, 54, 93);">datetime</span>);<br />GO<br /><span style="color: rgb(0, 176, 80);">--can</span><br /><span style="color: rgb(23, 54, 93);">INSERT INTO</span> dateCheck(dateCol) <span style="color: rgb(23, 54, 93);">VALUES </span>(<span style="color: rgb(255, 0, 0);">'12/31/1753'</span>);<br /><span style="color: rgb(0, 176, 80);">--can't</span><br /><span style="color: rgb(23, 54, 93);">INSERT INTO </span>dateCheck(dateCol) <span style="color: rgb(23, 54, 93);">VALUES </span>(<span style="color: rgb(255, 0, 0);">'01/01/1752'</span>);<br />GO<br /><span style="color: rgb(23, 54, 93);">SELECT * FROM</span> dateCheck;<br />GO<br /><span style="color: rgb(0, 176, 80);">--date data type</span><br /><span style="color: rgb(23, 54, 93);">DROP TABLE</span> dateCheckDate;<br /><span style="color: rgb(23, 54, 93);">CREATE TABLE</span> dateCheckDate(dateCol date);<br />GO<br /><span style="color: rgb(0, 176, 80);">--can</span><br /><span style="color: rgb(23, 54, 93);">INSERT INTO</span> dateCheckDate(dateCol) <span style="color: rgb(23, 54, 93);">VALUES </span>(<span style="color: rgb(255, 0, 0);">'01/01/1753'</span>);<br /><span style="color: rgb(23, 54, 93);">INSERT INTO</span> dateCheckDate(dateCol) <span style="color: rgb(23, 54, 93);">VALUES </span>(<span style="color: rgb(255, 0, 0);">'01/01/1752'</span>);<br /><span style="color: rgb(23, 54, 93);">INSERT INTO</span> dateCheckDate(dateCol) <span style="color: rgb(23, 54, 93);">VALUES </span>(<span style="color: rgb(255, 0, 0);">'01/01/0001'</span>);<br />GO<br /><span style="color: rgb(23, 54, 93);">SELECT * FROM</span> dateCheckDate;<br />GO</span></td></tr></tbody></table>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com7tag:blogger.com,1999:blog-7296940595016042654.post-41526030617578886062008-06-24T00:59:00.000-07:002009-05-13T00:11:28.937-07:00SQL SERVER 2005 - MSXML6 SETUP Fail<p class="MsoNormal" style="font-family:verdana;"><span style="font-size:78%;">SQL SERVER 2005 - MSXML6 SETUP Fail</span></p><p class="MsoNormal" style="font-family:verdana;"><span style="font-size:78%;">MSXML6 is the latest MSXML product from Microsoft, and <span style=""> </span>is contain with SQL Server 2005, Visual Studio 2005, .NET Framework (3.0,3.5) , Windows Vista and Windows XP Service Pack 3.When you going to install SQL server 2005, some certain situation you can notice MSXML6 setup fail to install and all setup program will be fail.<span style="border: 1pt none black; padding: 0in; background: black none repeat scroll 0% 50%; line-height: 115%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;font-size:0;color:black;" > </span>And when you going to run individual setup files, under SQL server 2005 setup folder (“\ Tools\Setup\msxml6.msi”) it will gives you following error “The system administrator has set policies to prevent<span style=""> </span>this installation”<span style=""> </span></span></p> <p class="MsoNormal" style="font-family:verdana;"><span style="font-size:78%;">MS says about this <a href="http://support.microsoft.com/kb/928469">http://support.microsoft.com/kb/928469</a></span></p> <span style="font-size:78%;"><span style="font-family:verdana;"></span></span><p class="MsoNormal"><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqB8UWNy8BbuHbqqc8-KHw8iu56aAS0aa4D2fhUm7dgmmH_wMBZAneNnHF0_RlKU_2BkOkvAZT8hC30XvSFl_8pnX7rkHNpLkHkE3MvsiFm90Nx90vS6Op00z8VGKi_6bYLIpfvEWrATA/s1600-h/20080321_admin.jpg"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqB8UWNy8BbuHbqqc8-KHw8iu56aAS0aa4D2fhUm7dgmmH_wMBZAneNnHF0_RlKU_2BkOkvAZT8hC30XvSFl_8pnX7rkHNpLkHkE3MvsiFm90Nx90vS6Op00z8VGKi_6bYLIpfvEWrATA/s320/20080321_admin.jpg" alt="" id="BLOGGER_PHOTO_ID_5215355698322625522" border="0" /></a><span style=""> </span></p> <p class="MsoListParagraphCxSpFirst" style="text-indent: -0.25in;"><!--[if !supportLists]--><span style=""><span style=""></span></span><!--[endif]--><br /></p><p class="MsoListParagraphCxSpFirst" style="text-indent: -0.25in;"><br /></p><span style="line-height: 115%;"><br /><span style="font-size:85%;"><br /></span></span> <p class="MsoListParagraphCxSpFirst" style="text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-size:85%;"><span style=""><span style=""><br /></span></span></span></p> <p class="MsoListParagraphCxSpFirst" style="text-indent: -0.25in;"><!--[if !supportLists]--><span style=""><span style=""></span></span></p><span style="font-size:78%;"><br /></span> <p class="MsoNormal" style="font-family:verdana;"><span style="font-size:85%;">Solution;</span></p> <span style="font-size:78%;"><span style="font-family:verdana;">1)Go to add remove programs and remove MSXML 6.0 Parser if available<br />2)Then removing few registry values you can sort out this problem.<br />HKEY_CLASSES_ROOT\Installer\Products\<br />Go to above path and check product name call “MSXML 6.0 Parser” and remove all the related values and reinstall SQL server 2005 setup.<br />Related links,<br /><a href="http://blogs.msdn.com/quanto/archive/2005/06/29/434136.aspx">http://blogs.msdn.com/quanto/archive/2005/06/29/434136.aspx</a></span></span>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com1tag:blogger.com,1999:blog-7296940595016042654.post-42274509376872056712007-12-28T04:06:00.000-08:002009-05-13T00:11:49.705-07:00Problem to create an index on a BIT (data type) column (SQL server 2000)<p class="MsoNormal"><span style="font-size:85%;">When we try to create on an index to Bit data type column using previous versions (SQL Server 2000) of SQL server it doesn’t display the bit data type columns.</span></p> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhntEXg8gY-QqTMdTcYybG2DjTIgVHDVDnnDFGrY8HsbmrWRMzbyJPWOWfHyHL56tj7Fy3zVTF-RXijgXOyMrlalJlyAuGJdSkxxWPRodJlTHAiXaD9xUmIMykdXIC8sKzjhYxlYiAkAZU/s1600-h/bitonIndex.JPG"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhntEXg8gY-QqTMdTcYybG2DjTIgVHDVDnnDFGrY8HsbmrWRMzbyJPWOWfHyHL56tj7Fy3zVTF-RXijgXOyMrlalJlyAuGJdSkxxWPRodJlTHAiXaD9xUmIMykdXIC8sKzjhYxlYiAkAZU/s320/bitonIndex.JPG" alt="" id="BLOGGER_PHOTO_ID_5148999369699657842" border="0" /></a><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><span style="font-size:85%;">And when we type columns name manually it show the message box “Do you want to delete this index?” then how we create index to bit data type column using SQL server 2000?bit when you notice, to create index using manage indexes property (all task > manage indexes) we can create index to bit data type columns with out any problem.<br /></span><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF8zGG9aPd2R2iTJwf3x_r4go0LH9J9ebqB5L-iUZsTI8EDcA22NJnxD7xQ0xYADLavEtTgB_xEoL06suqLs9B6dKdvwybuQEhxRe2zCbyHqoZ2o3HhnXeiuwKGQrgrr0wtNF-oEEHAdE/s1600-h/bitonIndex1.JPG"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF8zGG9aPd2R2iTJwf3x_r4go0LH9J9ebqB5L-iUZsTI8EDcA22NJnxD7xQ0xYADLavEtTgB_xEoL06suqLs9B6dKdvwybuQEhxRe2zCbyHqoZ2o3HhnXeiuwKGQrgrr0wtNF-oEEHAdE/s320/bitonIndex1.JPG" alt="" id="BLOGGER_PHOTO_ID_5148999777721550978" border="0" /></a><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><span style="font-size:85%;">And also Good new is SQL server 2005 version you can create an index to bit data type column with out any problem.</span>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com0tag:blogger.com,1999:blog-7296940595016042654.post-3620241719734848882007-09-10T05:16:00.000-07:002009-05-13T00:12:08.517-07:00NULL values and WITH ROLLUP<p class="MsoNormal" style="font-family:verdana;"><span style="font-size:78%;">When you using WITH ROLLUP, you can get group total and full total within the result set but imagine when you allow null columns and writing T-SQL statement with using <b style="">WITH ROLLUP </b>for<b style=""> </b>groups by your GROUP BY columns? It calculate correct values but we can’t sort it out.</span> </p><p class="MsoNormal"><o:p> </o:p></p> <table class="MsoNormalTable" style="width: 100%;" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(216, 216, 216); border-width: medium medium 1pt; padding: 0in 3.75pt; background: rgb(216, 216, 216) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <p class="MsoNormal" style="margin-bottom: 3.75pt; line-height: normal;"><b><span style=";font-family:Verdana;font-size:13;color:black;" ><o:p> </o:p></span></b></p> <br /></td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(216, 216, 216); border-width: medium medium 1pt; padding: 0in 3.75pt; background: rgb(216, 216, 216) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <p class="MsoNormal" style="margin-bottom: 3.75pt; line-height: normal;"><b><span style=";font-family:Verdana;font-size:13;color:black;" ><o:p> </o:p></span></b></p> <br /></td> </tr> <tr style=""> <td colspan="2" style="border-style: solid none none; border-color: rgb(216, 216, 216) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3.75pt 3.75pt 0in; background: rgb(204, 204, 204) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >USE</span><span style=";font-family:";font-size:10;color:black;" > mydb<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >GO<o:p></o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><o:p></o:p></span><span style=";font-family:";font-size:10;" >DROP TABLE</span><span style=";font-family:";font-size:10;color:black;" > ITEM<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >GO<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >CREATE TABLE</span><span style=";font-family:";font-size:10;color:black;" > ITEM<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >(<span style=""> </span>categoryCode </span><span style=";font-family:";font-size:10;" >varchar</span><span style=";font-family:";font-size:10;color:black;" >(10) NOT NULL,<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><span style=""> </span>itemCode </span><span style=";font-family:";font-size:10;" >varchar</span><span style=";font-family:";font-size:10;color:black;" >(10)NOT NULL,<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><span style=""> </span>qty </span><span style=";font-family:";font-size:10;" >int</span><span style=";font-family:";font-size:10;color:black;" > NOT NULL<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >GO<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >INSERT INTO</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >VALUES</span><span style=";font-family:";font-size:10;color:black;" >(</span><span style=";font-family:";font-size:10;" >'category 1'</span><span style=";font-family:";font-size:10;color:black;" >,</span><span style=";font-family:";font-size:10;" >'item 1'</span><span style=";font-family:";font-size:10;color:black;" >,5)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >INSERT INTO</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >VALUES</span><span style=";font-family:";font-size:10;color:black;" >(</span><span style=";font-family:";font-size:10;" >'category 1'</span><span style=";font-family:";font-size:10;color:black;" >,</span><span style=";font-family:";font-size:10;" >'item 2'</span><span style=";font-family:";font-size:10;color:black;" >,4)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >INSERT INTO</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >VALUES</span><span style=";font-family:";font-size:10;color:black;" >(</span><span style=";font-family:";font-size:10;" >'category 1'</span><span style=";font-family:";font-size:10;color:black;" >,</span><span style=";font-family:";font-size:10;" >'item 3'</span><span style=";font-family:";font-size:10;color:black;" >,2)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >INSERT INTO</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >VALUES</span><span style=";font-family:";font-size:10;color:black;" >(</span><span style=";font-family:";font-size:10;" >'category 2'</span><span style=";font-family:";font-size:10;color:black;" >,</span><span style=";font-family:";font-size:10;" >'item 1'</span><span style=";font-family:";font-size:10;color:black;" >,12)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >INSERT INTO</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >VALUES</span><span style=";font-family:";font-size:10;color:black;" >(</span><span style=";font-family:";font-size:10;" >'category 2'</span><span style=";font-family:";font-size:10;color:black;" >,</span><span style=";font-family:";font-size:10;" >'item 2'</span><span style=";font-family:";font-size:10;color:black;" >,8)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >INSERT INTO</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >VALUES</span><span style=";font-family:";font-size:10;color:black;" >(</span><span style=";font-family:";font-size:10;" >'category 3'</span><span style=";font-family:";font-size:10;color:black;" >,</span><span style=";font-family:";font-size:10;" >'item 2'</span><span style=";font-family:";font-size:10;color:black;" >,1)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >GO<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >SELECT</span><span style=";font-family:";font-size:10;color:black;" > isnull(categoryCode,</span><span style=";font-family:";font-size:10;" >' Full'</span><span style=";font-family:";font-size:10;color:black;" >) </span><span style=";font-family:";font-size:10;" >AS</span><span style=";font-family:";font-size:10;color:black;" > categoryCode,<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><span style=""> </span>isnull(itemCode,</span><span style=";font-family:";font-size:10;" >' Total is '</span><span style=";font-family:";font-size:10;color:black;" >) </span><span style=";font-family:";font-size:10;" >AS</span><span style=";font-family:";font-size:10;color:black;" ><span style=""> </span>itemCode,<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><span style=""> </span>sum(qty ) totQty<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >FROM</span><span style=";font-family:";font-size:10;color:black;" > ITEM <o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >GROUP BY</span><span style=";font-family:";font-size:10;color:black;" > categoryCode,itemCode<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >WITH ROLLUP<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >GO<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >--After setting allow nulls<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >ALTER TABLE</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >ALTER COLUMN</span><span style=";font-family:";font-size:10;color:black;" > categoryCode </span><span style=";font-family:";font-size:10;" >varchar</span><span style=";font-family:";font-size:10;color:black;" >(10) NULL<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >ALTER TABLE</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >ALTER COLUMN</span><span style=";font-family:";font-size:10;color:black;" > itemCode<span style=""> </span></span><span style=";font-family:";font-size:10;" >varchar</span><span style=";font-family:";font-size:10;color:black;" >(10) NULL<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >GO<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >INSERT INTO</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >VALUES</span><span style=";font-family:";font-size:10;color:black;" >(</span><span style=";font-family:";font-size:10;" >'category 1'</span><span style=";font-family:";font-size:10;color:black;" >,null,5)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >INSERT INTO</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >VALUES</span><span style=";font-family:";font-size:10;color:black;" >(</span><span style=";font-family:";font-size:10;" >'category 2'</span><span style=";font-family:";font-size:10;color:black;" >,null,4)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >INSERT INTO</span><span style=";font-family:";font-size:10;color:black;" > ITEM </span><span style=";font-family:";font-size:10;" >VALUES</span><span style=";font-family:";font-size:10;color:black;" >(null,</span><span style=";font-family:";font-size:10;" >'item 3'</span><span style=";font-family:";font-size:10;color:black;" >,2)<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >GO<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >SELECT</span><span style=";font-family:";font-size:10;color:black;" > isnull(categoryCode,</span><span style=";font-family:";font-size:10;" >' Full'</span><span style=";font-family:";font-size:10;color:black;" >) </span><span style=";font-family:";font-size:10;" >AS</span><span style=";font-family:";font-size:10;color:black;" > categoryCode,<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><span style=""> </span>isnull(itemCode,</span><span style=";font-family:";font-size:10;" >' Total is '</span><span style=";font-family:";font-size:10;color:black;" >) </span><span style=";font-family:";font-size:10;" >AS</span><span style=";font-family:";font-size:10;color:black;" ><span style=""> </span>itemCode,<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" ><span style=""> </span>sum(qty ) totQty<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >FROM</span><span style=";font-family:";font-size:10;color:black;" > ITEM <o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >GROUP BY</span><span style=";font-family:";font-size:10;color:black;" > categoryCode,itemCode<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;" >WITH ROLLUP<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:";font-size:10;color:black;" >GO</span><span style=";font-family:";font-size:13;color:black;" ><o:p></o:p></span></p> </td> </tr> </tbody></table>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com0tag:blogger.com,1999:blog-7296940595016042654.post-59531921883708776082007-09-10T04:37:00.000-07:002009-05-13T00:13:20.603-07:00timestamp Data type makes my big mistake<span style=";font-family:verdana;font-size:85%;" ><span style="font-weight: bold;">Oh</span>…timestamp Data type makes my <a href="http://dotnetforum.lk/forums/27208/ShowPost.aspx">big mistake</a></span><br /><span style="font-size:78%;"> Few days a go I was added post about timestamp data type but unfortunately it was not the correct way. And the name timestamp data type is a little misleading because timestamp data type has nothing to do with date and time and this is not possible converted to date time data type. We have to notice this, The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL date time data type. (SQL Server Books Online) </span><br /><br /><table class="MsoNormalTable" style="border: 1pt solid rgb(222, 223, 239); width: 100%;" width="100%" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="border: 1pt solid rgb(222, 223, 239); padding: 0in 3.75pt; background: rgb(216, 216, 216) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <p class="MsoNormal"><span style="font-size:85%;">SQL Server Books Online Say’s:</span></p> </td> </tr> <tr style=""> <td style="border: 1pt solid rgb(222, 223, 239); padding: 0in 3.75pt; background: white none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <ul type="disc"><li class="MsoNormal" style="line-height: normal;"><span style="font-size:78%;"><b><span style=";font-family:";font-size:12;" >timestamp</span></b><span style=";font-family:";font-size:12;" > <o:p></o:p></span></span></li></ul> <p class="MsoNormal" style="margin-left: 0.5in; line-height: normal;"><span style="font-size:78%;"><span style=";font-family:";font-size:12;" >The SQL Server <b>timestamp </b>data type has nothing to do with times or dates. SQL Server <b>timestamps</b> are binary numbers that indicate the relative sequence in which data modifications took place in a database. The <b>timestamp</b> data type was originally implemented to support the SQL Server recovery algorithms. Each time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the <b>timestamp</b> values had no relationship to time.<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-left: 0.5in; line-height: normal;"><span style="font-size:78%;"><span style=";font-family:";font-size:12;" >In SQL Server version 7.0 and SQL Server 2000, @@DBTS is only incremented for use in <b>timestamp</b> columns. If a table contains a <b>timestamp</b> column, every time a row is modified by an INSERT, UPDATE, or DELETE statement, the <b>timestamp</b> value in the row is set to the current @@DBTS value, and then @@DBTS is incremented by one.<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-left: 0.5in; line-height: normal;"><span style="font-size:78%;"><span style=";font-family:";font-size:12;" >Never use <b>timestamp</b> columns in keys, especially primary keys, because the <b>timestamp</b> value changes every time the row is modified.<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-left: 0.5in; line-height: normal;"><span style=";font-family:";font-size:12;" ><span style="font-size:78%;">To record the times data modifications take place in a table, use either a <b>datetime </b>or <b>smalldatetime </b>data type to record the events and triggers to automatically update the values when any modification takes place.</span><o:p></o:p></span></p> </td> </tr> </tbody></table> <p class="MsoNormal"><b><o:p> </o:p></b><br /><span style="font-size:78%;">But I have little <a href="http://msdn2.microsoft.com/en-us/library/ms187928.aspx">doubts</a> about CAST or CONVERT function chart its shows as <b><span style="line-height: 115%;font-family:";font-size:12;" >timestamp allow converting (</span></b>Implicit conversions</span><b><span style="line-height: 115%;font-family:";font-size:12;" ><span style="font-size:78%;">) to date time.</span> </span></b></p>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com0tag:blogger.com,1999:blog-7296940595016042654.post-55084185463680120402007-09-04T23:08:00.000-07:002009-05-13T00:13:35.531-07:00Avoid Using TOP Clause much as possible in SQL server<span style=";font-family:Verdana;font-size:85%;" ><u></u>Most of the time we need to return number of rows (first two rows or first five rows) in the table, then normally we are applying TOP clause. Then it can not be a performance issue or time consuming problem because it will takes only few (one or less) milliseconds.<br /></span> <p style="margin: 13.5pt 0in 6pt; line-height: normal;"><b><span style=";font-family:Verdana;font-size:14;color:black;" >Syntax;</span></b> </p><table style="width: 100%;" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="border-style: none none solid; border-width: medium medium 1pt; padding: 0in 3.75pt; background: rgb(216, 216, 216) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <p style="margin-bottom: 3.75pt; line-height: normal;"><b><span style=";font-family:Verdana;font-size:13;color:black;" > </span></b> </p><br /></td></tr><tr style=""> <td style="border-style: solid none none; border-color: rgb(216, 216, 216) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3.75pt 3.75pt 0in; background: rgb(204, 204, 204) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">[<br /> <span style="color: rgb(31, 73, 125);">TOP</span> (expression) [PERCENT]<br /> [ WITH TIES ]<br />]<br /><p style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:'Courier New';color:black;" ></span><span style=";font-family:'Courier New';font-size:13;color:black;" ></span> </p></td></tr></tbody></table> <p style="margin: 13.5pt 0in 6pt; line-height: normal;"><span style=""> </span><b><span style=";font-family:Verdana;font-size:14;color:black;" >Examples;</span></b><span style=";font-family:Verdana;font-size:14;color:black;" ></span> </p><table style="width: 100%;" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="border-style: none none solid; border-width: medium medium 1pt; padding: 0in 3.75pt; background: rgb(216, 216, 216) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <p style="margin-bottom: 3.75pt; line-height: normal;"><b><span style=";font-family:Verdana;font-size:13;color:black;" > </span></b> </p><br /></td><td style="border-style: none none solid; border-width: medium medium 1pt; padding: 0in 3.75pt; background: rgb(216, 216, 216) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <p style="margin-bottom: 3.75pt; line-height: normal;"><b><span style=";font-family:Verdana;font-size:13;color:black;" > </span></b> </p><br /></td></tr><tr style=""> <td colspan="2" style="border-style: solid none none; border-color: rgb(216, 216, 216) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3.75pt 3.75pt 0in; background: rgb(204, 204, 204) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><span style="color: rgb(31, 73, 125);">USE </span>AdventureWorks ;<br />GO<br /><span style="color: rgb(31, 73, 125);">DECLARE</span> @p AS <span style="color: rgb(31, 73, 125);">int</span><br /><span style="color: rgb(31, 73, 125);">SET</span> @p=<span style="color: rgb(192, 0, 0);">'10'</span><br /><span style="color: rgb(31, 73, 125);">SELECT TOP</span>(@p)*<br /><span style="color: rgb(31, 73, 125);">FROM</span> HumanResources.Employee;<br />GO<br /><p style="margin-bottom: 0.0001pt; line-height: normal;"><span style=";font-family:'Courier New';color:black;" ></span> </p></td></tr></tbody></table> <p><span style="line-height: 115%;font-size:12;" >(Books Online – Search for TOP Clause)</span></p><p> </p><p><span style=";font-family:Verdana;font-size:85%;" >But when you use very large table (Ex: more than 1000000) and need to return first 10000 records with using top clause then it will take much time and it can be a performance issue. In that case I am using following method to return first ten thousand records,</span> </p><table style="width: 100%;" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="border-style: solid none none; border-color: rgb(216, 216, 216) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3.75pt 3.75pt 0in; background: rgb(204, 204, 204) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><span style="color: rgb(31, 73, 125);">USE </span>AdventureWorks ;<br />GO<br /><span style="color: rgb(31, 73, 125);">DECLARE</span> @intNum AS <span style="color: rgb(31, 73, 125);">int</span><br /><span style="color: rgb(31, 73, 125);">SET </span>@intNum =10000<br /><br /><span style="color: rgb(31, 73, 125);">SET ROWCOUNT</span> @intNum; <br /><span style="color: rgb(31, 73, 125);">SELECT <span style="color: rgb(0, 0, 0);">* </span>FROM </span>Sales.SalesOrderDetail<br /><span style="color: rgb(31, 73, 125);">ORDER BY</span> UnitPrice,SalesOrderID <br /><span style="color: rgb(31, 73, 125);">SET ROWCOUNT</span> 0<br />GO<br /><p style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-family:'Courier New';"></span><span style=";font-family:'Courier New';font-size:13;" ></span> </p></td></tr></tbody></table> <span style=";font-family:Verdana;font-size:85%;" >Using Rowcount option we can avoid top clause but there is have some limitations, when we applying Rowcount option.<br />i)Inside the view we can’t use Rowcount option.<br />ii)Using Rowcount we can’t create sub query’s (<span style="font-family:Courier New;">Ex. <span style="color: rgb(31, 73, 125);">SELECT</span> TMP.SalesOrderID,TMP.UnitPrice <span style="color: rgb(31, 73, 125);">FROM</span> (<span style="color: rgb(31, 73, 125);">SELECT</span> <span style="color: rgb(31, 73, 125);">TOP</span> 10000 * <span style="color: rgb(31, 73, 125);">FROM</span> Sales.SalesOrderDetail<br /><span style="color: rgb(31, 73, 125);">ORDER BY</span> UnitPrice,SalesOrderID) TMP</span>)</span>Tharindu Dhaneenjahttp://www.blogger.com/profile/14330626360156981828noreply@blogger.com1