tag:blogger.com,1999:blog-25562973541504439942024-03-19T15:16:36.445-07:00Developer's Stuffhardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.comBlogger91125tag:blogger.com,1999:blog-2556297354150443994.post-77096800311106537682018-02-15T21:24:00.000-08:002018-02-15T21:24:42.285-08:00TSQL enhancements in recent SQL version<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="background-color: white; box-sizing: inherit; color: #222222; font-family: "Roboto Slab", serif; font-size: 1.5rem; line-height: 34px; margin: 0px 0px 10px; padding: 0px;">
<strong style="box-sizing: inherit;">String_AGG</strong></h2>
<div>
<strong style="box-sizing: inherit;"><span style="background-color: white; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; font-weight: 400;">This new function helps to generate comma separated string from </span></strong><span style="background-color: white; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px;">the contents of a column from several records in a single string value</span></div>
<div>
<span style="background-color: white; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px;"><br /></span></div>
<div>
<span style="background-color: white; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px;"><br /></span></div>
<div>
<span style="background-color: white; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px;">It was previously possible through XML trick - </span></div>
<div>
<span style="background-color: white; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px;"><br /></span></div>
<div>
<span style="background-color: white; box-sizing: inherit; color: blue; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">select</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: magenta; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"><i style="box-sizing: inherit;">stuff</i></span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">(</span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">(</span><span style="background-color: white; box-sizing: inherit; color: blue; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">select</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: red; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">‘,’</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: silver; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">+</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">[name]</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: blue; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">as</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">[text()]</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><br style="background-color: white; box-sizing: inherit; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;" /><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: blue; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">from</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">names</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: blue; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">for</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">xml</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">path</span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">(</span><span style="background-color: white; box-sizing: inherit; color: red; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">”</span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">)</span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">)</span><span style="background-color: white; box-sizing: inherit; color: silver; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">,</span><span style="background-color: white; box-sizing: inherit; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">1</span><span style="background-color: white; box-sizing: inherit; color: silver; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">,</span><span style="background-color: white; box-sizing: inherit; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">1</span><span style="background-color: white; box-sizing: inherit; color: silver; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">,</span><span style="background-color: white; box-sizing: inherit; color: red; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">”</span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">)</span></div>
<div>
<span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"><br /></span></div>
<div>
<span style="background-color: white; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px;">which will be easily converted to below using string_agg</span></div>
<div>
<span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"><br /></span></div>
<div>
<div>
<div>
<div>
<span style="background-color: white; box-sizing: inherit; color: blue; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">select</span><span style="background-color: white; color: #373737; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"> </span><span style="background-color: white; box-sizing: inherit; color: magenta; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"><i style="box-sizing: inherit;">string_agg</i></span><span style="background-color: white; box-sizing: inherit; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">(</span><i style="box-sizing: inherit; color: magenta; font-family: "Courier New"; font-size: 13.3333px;">cast</i><span style="background-color: white; color: maroon; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">(Name as varchar(Max)), </span><span style="background-color: white; box-sizing: inherit; color: red; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;">‘,’</span><span style="background-color: white; box-sizing: inherit; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"><span style="color: #373737;">) From User</span></span></div>
</div>
</div>
</div>
<div>
<span style="background-color: white; box-sizing: inherit; font-family: "Courier New"; font-size: 13.3333px; font-style: italic;"><span style="color: #373737;"><br /></span></span></div>
<div>
<span style="background-color: white; box-sizing: inherit; font-style: italic;"><h2 style="box-sizing: inherit; color: #222222; font-family: "Roboto Slab", serif; font-size: 1.5rem; font-style: normal; line-height: 34px; margin: 0px 0px 10px; padding: 0px;">
<strong style="box-sizing: inherit;">Trim</strong></h2>
<div style="box-sizing: inherit; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; font-style: normal; margin-bottom: 1.25rem; padding: 0px;">
This new function has been requested for a lot of developers for a long time.</div>
<div style="box-sizing: inherit; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; font-style: normal; margin-bottom: 1.25rem; padding: 0px;">
Removing the empty spaces in a string always demanded the use of two functions, like this:</div>
<blockquote style="-webkit-font-smoothing: antialiased; box-sizing: inherit; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; margin: 0px 0px 1.25rem 1.25rem; padding: 0px 0px 0px 3.75rem;">
<div style="box-sizing: inherit;">
<span style="box-sizing: inherit; font-family: "Courier New"; font-size: 10pt;"><span style="box-sizing: inherit; color: blue;">SELECT</span> <span style="box-sizing: inherit; color: magenta;"><i style="box-sizing: inherit;">RTRIM</i></span><span style="box-sizing: inherit; color: maroon;">(</span><span style="box-sizing: inherit; color: magenta;"><i style="box-sizing: inherit;">LTRIM</i></span><span style="box-sizing: inherit; color: maroon;">(</span> <span style="box-sizing: inherit; color: red;">‘ test ‘</span><span style="box-sizing: inherit; color: maroon;">)</span><span style="box-sizing: inherit; color: maroon;">)</span> <span style="box-sizing: inherit; color: blue;">AS</span> <span style="box-sizing: inherit; color: maroon;">Result</span><span style="box-sizing: inherit; color: silver;">;</span></span></div>
</blockquote>
<div style="box-sizing: inherit; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; font-style: normal; margin-bottom: 1.25rem; padding: 0px;">
This new function simplifies this task:</div>
<blockquote style="-webkit-font-smoothing: antialiased; box-sizing: inherit; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; margin: 0px 0px 1.25rem 1.25rem; padding: 0px 0px 0px 3.75rem;">
<div style="box-sizing: inherit;">
<span style="box-sizing: inherit; font-family: "Courier New"; font-size: 10pt;"><span style="box-sizing: inherit; color: blue;">SELECT</span> <span style="box-sizing: inherit; color: #ff0080;"><b style="box-sizing: inherit;">TRIM</b></span><span style="box-sizing: inherit; color: maroon;">(</span> <span style="box-sizing: inherit; color: red;">‘ test ‘</span><span style="box-sizing: inherit; color: maroon;">)</span> <span style="box-sizing: inherit; color: blue;">AS</span> <span style="box-sizing: inherit; color: maroon;">Result</span><span style="box-sizing: inherit; color: silver;">;</span></span></div>
</blockquote>
<div style="font-family: "Courier New"; font-size: 13.3333px;">
<br /></div>
<h2 style="box-sizing: inherit; color: #222222; font-family: "Roboto Slab", serif; font-size: 1.5rem; font-style: normal; line-height: 34px; margin: 0px 0px 10px; padding: 0px;">
<strong style="box-sizing: inherit;">Translate</strong></h2>
<div style="font-family: "Courier New"; font-size: 13.3333px;">
<span style="color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; font-style: normal;">Translate does the work of several replace functions, simplifying some queries.</span> </div>
<div style="font-family: "Courier New"; font-size: 13.3333px;">
<br /></div>
<div style="box-sizing: inherit; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; font-style: normal; margin-bottom: 1.25rem; padding: 0px;">
Using <strong style="box-sizing: inherit;">‘Replace’</strong> function the transformation would be like this:</div>
<blockquote style="-webkit-font-smoothing: antialiased; box-sizing: inherit; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; margin: 0px 0px 1.25rem 1.25rem; padding: 0px 0px 0px 3.75rem;">
<div style="box-sizing: inherit;">
<span style="box-sizing: inherit; font-family: "Courier New"; font-size: 10pt;"><span style="box-sizing: inherit; color: blue;">select</span> <span style="box-sizing: inherit; color: magenta;"><i style="box-sizing: inherit;">replace</i></span><span style="box-sizing: inherit; color: maroon;">(</span><span style="box-sizing: inherit; color: magenta;"><i style="box-sizing: inherit;">replace</i></span><span style="box-sizing: inherit; color: maroon;">(</span><span style="box-sizing: inherit; color: magenta;"><i style="box-sizing: inherit;">replace</i></span><span style="box-sizing: inherit; color: maroon;">(</span><span style="box-sizing: inherit; color: red;">‘[137.4, 72.3]’</span><span style="box-sizing: inherit; color: silver;">,</span><span style="box-sizing: inherit; color: red;">‘[‘</span><span style="box-sizing: inherit; color: silver;">,</span><span style="box-sizing: inherit; color: red;">‘(‘</span><span style="box-sizing: inherit; color: maroon;">)</span><span style="box-sizing: inherit; color: silver;">,</span><span style="box-sizing: inherit; color: red;">‘,’</span><span style="box-sizing: inherit; color: silver;">,</span><span style="box-sizing: inherit; color: red;">‘ ‘</span><span style="box-sizing: inherit; color: maroon;">)</span><span style="box-sizing: inherit; color: silver;">,</span><span style="box-sizing: inherit; color: red;">‘]’</span><span style="box-sizing: inherit; color: silver;">,</span><span style="box-sizing: inherit; color: red;">‘)’</span><span style="box-sizing: inherit; color: maroon;">)</span> <span style="box-sizing: inherit; color: blue;">as</span> <span style="box-sizing: inherit; color: maroon;">Point</span></span></div>
</blockquote>
<div style="box-sizing: inherit; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; font-style: normal; margin-bottom: 1.25rem; padding: 0px;">
Using the <strong style="box-sizing: inherit;">‘Translate’</strong> function the transformations becomes way simpler:</div>
<blockquote style="-webkit-font-smoothing: antialiased; box-sizing: inherit; color: #373737; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: 16px; margin: 0px 0px 1.25rem 1.25rem; padding: 0px 0px 0px 3.75rem;">
<div style="box-sizing: inherit;">
<span style="box-sizing: inherit; font-family: "Courier New"; font-size: 10pt;"><span style="box-sizing: inherit; color: blue;">SELECT</span> <span style="box-sizing: inherit; color: #ff0080;"><b style="box-sizing: inherit;">TRANSLATE</b></span><span style="box-sizing: inherit; color: maroon;">(</span><span style="box-sizing: inherit; color: red;">‘[137.4, 72.3]’</span> <span style="box-sizing: inherit; color: silver;">,</span> <span style="box-sizing: inherit; color: red;">‘[,]’</span><span style="box-sizing: inherit; color: silver;">,</span> <span style="box-sizing: inherit; color: red;">‘( )’</span><span style="box-sizing: inherit; color: maroon;">)</span> <span style="box-sizing: inherit; color: blue;">AS</span> <span style="box-sizing: inherit; color: maroon;">Point</span><span style="box-sizing: inherit; color: silver;">,</span> <br style="box-sizing: inherit;" /> <span style="box-sizing: inherit; color: #ff0080;"><b style="box-sizing: inherit;">TRANSLATE</b></span><span style="box-sizing: inherit; color: maroon;">(</span><span style="box-sizing: inherit; color: red;">‘(137.4 72.3)’</span> <span style="box-sizing: inherit; color: silver;">,</span> <span style="box-sizing: inherit; color: red;">‘( )’</span><span style="box-sizing: inherit; color: silver;">,</span> <span style="box-sizing: inherit; color: red;">‘[,]’</span><span style="box-sizing: inherit; color: maroon;">)</span> <span style="box-sizing: inherit; color: blue;">AS</span> <span style="box-sizing: inherit; color: maroon;">Coordinates</span></span></div>
</blockquote>
<div style="font-family: "Courier New"; font-size: 13.3333px;">
<br /></div>
<h2 style="box-sizing: inherit; color: #222222; cursor: default; font-family: Georgia, Times, "Times New Roman", serif; font-size: 1.1875rem; font-style: normal; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; text-align: left; visibility: visible;">
<span style="box-sizing: inherit; font-weight: 700;">IIF</span></h2>
<div class="MsoNormal" style="box-sizing: inherit; color: #222222; cursor: default; font-style: normal; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible;">
<span style="font-family: Arial, Helvetica, sans-serif;">The IIF function is another function Access supports. It accepts as first input a predicate, as second input an expression to return in case the predicate is true, and as third input an expression to return in case the input is false or unknown. Here’s an example for using the function:</span></div>
<div style="box-sizing: inherit; color: #222222; cursor: default; font-style: normal; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible;">
</div>
<div style="font-size: 13.3333px;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="font-size: 16px; font-style: normal;">SELECT IIF(1 = 2, 'a', 'b') AS iif_result;</span> </span></div>
<div style="font-size: 13.3333px;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<pre language="text" linenumbers="none" style="box-sizing: inherit; font-family: monospace, monospace; font-size: 16px; font-style: normal; overflow: auto;">iif_result
----------
b</pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; font-family: monospace, monospace; font-size: 16px; font-style: normal; overflow: auto;">
</pre>
<h2 style="box-sizing: inherit; font-family: monospace, monospace; font-size: 16px; font-style: normal; overflow: auto; text-align: left;">
<span style="box-sizing: inherit; color: #222222; font-family: Georgia, Times, "Times New Roman", serif; font-size: 19px; font-weight: 700; white-space: normal;">CONCAT</span></h2>
<pre language="text" linenumbers="none" style="box-sizing: inherit; font-family: monospace, monospace; font-size: 16px; font-style: normal; overflow: auto;"><span style="box-sizing: inherit; color: #222222; font-family: Georgia, Times, "Times New Roman", serif; font-size: 19px; font-weight: 700; white-space: normal;">
</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; font-style: normal; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;"><span style="box-sizing: inherit; color: #222222; white-space: normal;">The CONCAT function concatenates the input values into a single result string. </span>The concatenation operator + yields a NULL on NULL input. The CONCAT function </span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; font-style: normal; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;">converts NULL inputs to empty strings before concatenation.</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; font-style: normal; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; font-style: normal; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; font-style: normal; overflow: auto;"><h2 style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; text-align: left; visibility: visible; white-space: normal;">
<span style="box-sizing: inherit; font-weight: 700;"><span style="font-family: Arial, Helvetica, sans-serif; font-size: large;">FORMAT</span></span></h2>
<div style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible; white-space: normal;">
</div>
<div class="MsoNormal" style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible; white-space: normal;">
<span style="font-family: Arial, Helvetica, sans-serif;">The FORMAT function allows you to format an input value to a character string based on a .NET format string. You can optionally indicate a culture when relevant. As an example, the following code formats the result of the GETDATE() function using the format string 'd' (meaning, short date pattern), in one case using US English culture, and in another Japanese:</span></div>
<div style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible; white-space: normal;">
</div>
<pre language="text" linenumbers="none" style="box-sizing: inherit; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;">SELECT FORMAT(GETDATE(), 'd', 'en-US') AS us, FORMAT(GETDATE(), 'd', 'ja-JP') AS jp</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; overflow: auto;"><div class="MsoNormal" style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible; white-space: normal;">
<span style="font-family: Arial, Helvetica, sans-serif;">This function allows a lot of flexibility in formatting inputs. For example, the following code formats product IDs as 10-digit strings with leading zeros:</span></div>
<div style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible; white-space: normal;">
</div>
<pre language="text" linenumbers="none" style="box-sizing: inherit; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;">SELECT FORMAT(productid, '0000000000') AS strproductid, productname
FROM Production.Products;</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; overflow: auto;"><h2 style="box-sizing: inherit; color: #222222; cursor: default; font-family: Georgia, Times, "Times New Roman", serif; font-size: 1.1875rem; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; text-align: left; visibility: visible; white-space: normal;">
<span style="box-sizing: inherit; font-weight: 700;">DROP IF EXISTS</span></h2>
<div style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible; white-space: normal;">
<span style="font-family: Arial, Helvetica, sans-serif;">One of the handiest T-SQL enhancements with SQL Server 2016 is the new DROP IF EXITS statement. It lets you check if an object exists before dropping it enabling you to skip wiring all those IF EXISTS blocks that we all write in out T-SQL scripts. You can see an example below.</span></div>
<pre style="box-sizing: inherit; overflow: auto;"><code class="language-text" style="box-sizing: inherit;"><span style="font-family: Arial, Helvetica, sans-serif;">DROP TABLE IF EXISTS [dbo].[MyTable];</span></code></pre>
<h2 style="box-sizing: inherit; color: #222222; cursor: default; font-family: Georgia, Times, "Times New Roman", serif; font-size: 1.1875rem; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; text-align: left; visibility: visible; white-space: normal;">
<span style="box-sizing: inherit; font-weight: 700;">STRING_SPLIT Function</span></h2>
<div style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible; white-space: normal;">
<span style="font-family: Arial, Helvetica, sans-serif;">STRING_SPLIT is another handy new function that makes it easy to parse strings based on a delimiter. The string can be a varchar, nvarchar, char, or nchar data type.</span></div>
<pre style="box-sizing: inherit; overflow: auto;"><code class="language-text" style="box-sizing: inherit;"><span style="font-family: Arial, Helvetica, sans-serif;">DECLARE @string varchar(100) = 'Richard, Mike, Mark'
SELECT value FROM string_split(@string, ',')</span></code></pre>
<pre style="box-sizing: inherit; overflow: auto;"><code class="language-text" style="box-sizing: inherit;"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></code></pre>
<pre style="box-sizing: inherit; overflow: auto;"><code class="language-text" style="box-sizing: inherit;"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></code></pre>
<pre style="box-sizing: inherit; overflow: auto;"><code class="language-text" style="box-sizing: inherit;"><h2 style="box-sizing: inherit; color: #222222; cursor: default; font-family: Georgia, Times, "Times New Roman", serif; font-size: 1.1875rem; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; text-align: left; visibility: visible; white-space: normal;">
<span style="box-sizing: inherit; font-weight: 700;">FOR JSON Clause</span></h2>
<div style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible; white-space: normal;">
<span style="font-family: Arial, Helvetica, sans-serif;">Likewise, the FOR JSON clause was add to enable you to convert tabular data to JSON. Unlike XML which has a native data type, the new JSON support is enabled by using the NVARCHAR data type. You can see an example of using the new FOR JSON clause in the following listing:</span></div>
<pre style="box-sizing: inherit; font-family: monospace, monospace; font-size: 16px; overflow: auto;"><code class="language-text" style="box-sizing: inherit; font-family: monospace, monospace; font-size: 1em;">SELECT object_id, name FROM sys.tables FOR JSON PATH</code></pre>
<h2 style="box-sizing: inherit; color: #222222; cursor: default; font-family: Georgia, Times, "Times New Roman", serif; font-size: 1.1875rem; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; text-align: left; visibility: visible; white-space: normal;">
<span style="box-sizing: inherit; font-weight: 700;">JSON Functions</span></h2>
<ul style="box-sizing: inherit; list-style-image: initial; list-style-position: inside; margin: 1em 0px; padding: 0px 0px 0px 25px; white-space: normal;">
<li style="box-sizing: inherit; color: #222222; list-style-type: none; margin-bottom: 25px;"><span style="font-family: Arial, Helvetica, sans-serif;">ISJSON -- The ISJSON function tests whether a string contains valid JSON.</span></li>
<li style="box-sizing: inherit; color: #222222; list-style-type: none; margin-bottom: 25px;"><span style="font-family: Arial, Helvetica, sans-serif;">JSON_VALUE -- The JSON_VALUE function extracts a scalar value from a JSON string.</span></li>
<li style="box-sizing: inherit; color: #222222; list-style-type: none; margin-bottom: 25px;"><span style="font-family: Arial, Helvetica, sans-serif;">JSON_QUERY -- The JSON_QUERY function extracts an object or an array from a JSON string.</span></li>
<li style="box-sizing: inherit; color: #222222; list-style-type: none; margin-bottom: 25px;"><span style="font-family: Arial, Helvetica, sans-serif;">JSON_MODIFY -- The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.</span></li>
</ul>
<h2 style="box-sizing: inherit; color: #222222; cursor: default; font-family: Georgia, Times, "Times New Roman", serif; font-size: 1.1875rem; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; text-align: left; visibility: visible; white-space: normal;">
<span style="box-sizing: inherit; font-weight: 700;">OPENJON Function</span></h2>
<div style="box-sizing: inherit; color: #222222; cursor: default; line-height: 29px; margin-bottom: 1.5em; margin-top: 1.5em; padding: 0px; visibility: visible; white-space: normal;">
<span style="font-family: Arial, Helvetica, sans-serif;">The OPENJSON table-value function (TVF) queries JSON text and can locate an array of JSON objects or iterate through the elements of array and generate a rowset from the results. You can see an example of using the OPENJSON function below.</span></div>
<pre style="box-sizing: inherit; font-family: monospace, monospace; font-size: 16px; overflow: auto;"><code class="language-text" style="box-sizing: inherit; font-family: monospace, monospace; font-size: 1em;">DECLARE @JSON NVARCHAR(100)
SET @json = N'[ null, "string", 1, [true, false], ["a","b","c"], {"obj1":"obj2"} ]';
SELECT * FROM OPENJSON( @json )</code></pre>
</code></pre>
</pre>
</pre>
<pre language="text" linenumbers="none" style="box-sizing: inherit; overflow: auto;"><span style="font-family: Arial, Helvetica, sans-serif;"> </span></pre>
</pre>
</span></div>
</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-70605473335677514152017-10-08T20:15:00.002-07:002017-10-08T20:15:43.536-07:00Git Commands<div dir="ltr" style="text-align: left;" trbidi="on">
git pull --rebase origin develop<br />
git -rebase continue<br />
git reset --hard;git clean -f -d;git pull;git checkout HEAD;git status;</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-42118656559424582862016-01-20T06:27:00.000-08:002016-01-20T07:33:10.394-08:00Remove SSL from reporting service <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white;">
<div style="color: #444444; font-family: verdana; font-size: 11px; line-height: 17px;">
To work around this</div>
<div style="color: #444444; font-family: verdana; font-size: 11px; line-height: 17px;">
Edit:</div>
<div style="color: #444444; font-family: verdana; font-size: 11px; line-height: 17px;">
C:\Program Files\Microsoft SQL Server\MSRS10.<serverinstance>\Reporting Services\ReportServer\rsreportserver.config</serverinstance></div>
<div style="color: #444444; font-family: verdana; font-size: 11px; line-height: 17px;">
<serverinstance><br /></serverinstance></div>
<div style="color: #444444; font-family: verdana; font-size: 11px; line-height: 17px;">
<serverinstance><span style="color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 14px; line-height: 20.162px;">Change SecureConnectionLevel from 2 to 0</span></serverinstance></div>
<serverinstance><span style="color: #333333; font-family: Segoe UI, Lucida Grande, Verdana, Arial, Helvetica, sans-serif;"><span style="font-size: 14px; line-height: 20.162px;"><br /></span></span><add key="SecureConnectionLevel" style="color: #444444; font-family: verdana; font-size: 11px; line-height: 17px;" value="2"> (Change from current value to 0)</add></serverinstance></div>
<div style="background-color: white; color: #444444; font-family: Verdana; font-size: 11px; line-height: 17px;">
Here is what the values mean:<br />
3 Most secure—Use SSL for absolutely everything.<br />
2 Secure—Use SSL for rendering and methods that pass credentials but don't insist on it for all SOAP calls.<br />
1 Basic Security—Accepts http but rejects any calls that might be involved in the passing of credentials.<br />
0 Least Secure—Don't use SSL at all.</div>
</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-79518668685758449282015-03-24T02:38:00.004-07:002015-03-24T02:41:17.461-07:00Add MVC into webforms project<div dir="ltr" style="text-align: left;" trbidi="on">
Install below nuget pacakges<br />
<br />
<span style="background-color: #edeff0; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px;">1) Microsoft </span><span class="skimlinks-unlinked" style="background-color: #edeff0; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin: 0px; padding: 0px;">ASP.NET</span><span style="background-color: #edeff0; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px;"> MVC</span><br />
<span style="background-color: #edeff0; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px;">2) WebGrease</span><br />
<span style="background-color: #edeff0; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px;">3) </span><span style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin: 0px; padding: 0px;">Modify web.config or Add assembly of Abstractions, MVC and Routing </span><br />
<strong style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin: 0px; padding: 0px;"><span style="background-color: #f4f5f7; font-weight: normal; line-height: 17.3999996185303px;"><add assembly="System.Web.Abstractions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"> </add></span><br style="background-color: #f4f5f7; font-weight: normal; line-height: 17.3999996185303px; margin: 0px; padding: 0px;" /><span style="background-color: #f4f5f7; font-weight: normal; line-height: 17.3999996185303px;"><add assembly="System.Web.Mvc, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"> </add></span><br style="background-color: #f4f5f7; font-weight: normal; line-height: 17.3999996185303px; margin: 0px; padding: 0px;" /><span style="background-color: #f4f5f7; font-weight: normal; line-height: 17.3999996185303px;"><add assembly="System.Web.Routing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"></add></span></strong><br />
<strong style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin: 0px; padding: 0px;"><span style="background-color: #f4f5f7; font-weight: normal; line-height: 17.3999996185303px;">4) </span></strong><span style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin: 0px; padding: 0px;">Modify <span class="skimlinks-unlinked" style="margin: 0px; padding: 0px;">global.asax</span></span><br />
<div style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
Next you will need to add in the code for MVC triggers inside <strong style="margin: 0px; padding: 0px;">global.asax</strong> (create one if it does not exist)</div>
<div style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
Add the following lines after <%@ Application Language="C#" %></div>
<div style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
<br /></div>
<div style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
<span style="background-color: #f4f5f7; line-height: 17.3999996185303px;"><%@ Import Namespace="System.Web.Mvc" %> </span><br />
<span style="background-color: #f4f5f7; line-height: 17.3999996185303px;"><%@ Import Namespace="System.Web.Routing" %></span></div>
<div style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
<span style="background-color: #f4f5f7; line-height: 17.3999996185303px;"><br /></span></div>
<div style="background-color: white; color: #555555; font-family: Verdana, 'BitStream vera Sans', Helvetica, sans-serif; font-size: 12px; line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
<span style="line-height: 17.3999996185303px;">Add the following after </span><span style="background-color: #f4f5f7; line-height: 17.3999996185303px;"><</span><span style="line-height: 17.3999996185303px;">script runat="server"</span><span style="background-color: #f4f5f7; line-height: 17.3999996185303px;">> </span><br />
<span style="background-color: #f4f5f7; line-height: 17.3999996185303px;"><br /></span>
<br />
<div style="line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
public void RegisterGlobalFilters(GlobalFilterCollection filters)<br />
{<br />
<span class="skimlinks-unlinked" style="margin: 0px; padding: 0px;">filters.Add(new</span> HandleErrorAttribute());<br />
}</div>
<div style="line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
public static void RegisterRoutes(RouteCollection routes)<br />
{<br />
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");</div>
<span style="background-color: #f4f5f7; line-height: 17.3999996185303px;"></span><br />
<div style="line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
routes.MapRoute("Home",<br />
"home/{action}/{id}",<br />
new { controller = "Home", action = "Index", id = UrlParameter.Optional });}</div>
<div style="line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
<br /></div>
<div style="line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
<span style="line-height: 17.3999996185303px;">add the following inside application_start</span></div>
<div style="line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
<span style="line-height: 17.3999996185303px;"><br /></span></div>
<div style="line-height: 17.3999996185303px; margin-bottom: 10px; padding: 0px;">
<span style="background-color: #f4f5f7; line-height: 17.3999996185303px;">RegisterGlobalFilters(GlobalFilters.Filters); </span><br />
<span style="background-color: #f4f5f7; line-height: 17.3999996185303px;">RegisterRoutes(RouteTable.Routes);</span></div>
</div>
</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-26204429937191484612014-07-16T07:10:00.003-07:002015-01-01T23:17:21.300-08:00How to manually uninstall SQL if uninstalling from Add/Remove Programs fails<div dir="ltr" style="text-align: left;" trbidi="on">
msiinv.exe is the most useful tool I came across.<br />
<div>
<br /></div>
<div>
One of the common uses of msiinv.exe is if someone is trying to install one of the recent beta builds of VS 2005 or .NET Framework 2.0 and the setup UI states that you are not allowed to install because a previous beta version of <insert here="" name="" product=""> is on the machine and you must uninstall that first. Sometimes after receiving this error, a user will look in Add/Remove Programs and the product that setup is complaining about is nowhere to be found, or there is an Add/Remove Programs entry for that product but trying to remove it claims that the product is not on the computer and asks if you would like to remove the entry from the Add/Remove Programs list.</insert></div>
<div>
<br /></div>
<div>
<div>
these cases, you can use the following steps:</div>
<div>
<br /></div>
<div>
Download msiinv.zip from the following location:</div>
<div>
<br /></div>
<div>
http://blogs.msdn.com/b/astebner/archive/2005/07/01/434814.aspx</div>
<div>
<br /></div>
<div>
Extract the contents of msiinv.zip to the folder c:\msiinv on your system</div>
<div>
Click on the Start menu, choose Run, type cmd and click OK</div>
<div>
Type this command: c:\msiinv\msiinv.exe -p > c:\msiinv\msiinv_output.txt </div>
<div>
<br /></div>
<div>
Note: This command must be run from a cmd prompt or it will not create a log file as expected.</div>
<div>
These steps will create a text file named c:\msiinv\msiinv_output.txt with a list of each product that Windows Installer thinks is installed on the system. Then you can open the text file in any text editor and search the list of products for the name of the product that setup told you to uninstall. The output will look something like this (I am using an example from a machine that has .NET Framework 2.0 beta 2 installed):</div>
<div>
<br /></div>
<div>
Microsoft .NET Framework 2.0 Beta 2</div>
<div>
Product code: {7A1ADD0C-17F3-47B8-B033-A06E189C835D}</div>
<div>
Product state: (5) Installed.</div>
<div>
Package code: {856D48D2-6F94-466D-9732-534DB5854FB3}</div>
<div>
Version: 2.0.50215</div>
<div>
<note: after="" am="" because="" but="" example="" i="" info="" is="" isn="" it="" more="" my="" of="" omitting="" rest="" t="" the="" there="" this="" to="" useful=""></note:></div>
<div>
<br /></div>
<div>
Now we have the Windows Installer product code and we can use that to uninstall the product by running msiexec /x <product code=""> (make sure that you include the curly braces in this command line). If the product is actually installed on your system you will see a progress screen and uninstall will complete.</product></div>
<br /></div>
</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-67969433742115112732014-03-27T02:26:00.001-07:002014-03-27T02:26:16.971-07:00Rebuild Index<div dir="ltr" style="text-align: left;" trbidi="on">
-- Ensure a USE <databasename> statement has been executed first.</databasename><br />
<br />
GO<br />
<br />
SET NOCOUNT ON;<br />
<br />
-- define index defrag rate for this run<br />
DECLARE @fragrate float;<br />
SET @fragrate = 30.0; -- set to do something if defrag rate HIGH<br />
<br />
DECLARE @rbrirate float;<br />
SET @rbrirate = 50.0; -- set to rebuild if defrag rate EXTREME<br />
<br />
-- define table holding index information<br />
DECLARE @indexinfo TABLE(objectid int, indexid int, partitionnum bigint, frag float)<br />
<br />
DECLARE @objectid int;<br />
DECLARE @indexid int;<br />
DECLARE @partitioncount bigint;<br />
DECLARE @schemaname nvarchar(130);<br />
DECLARE @objectname nvarchar(130);<br />
DECLARE @indexname nvarchar(130);<br />
DECLARE @partitionnum bigint;<br />
DECLARE @partitions bigint;<br />
DECLARE @frag float;<br />
DECLARE @command nvarchar(4000);<br />
<br />
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function<br />
INSERT INTO @indexinfo<br />
SELECT<br />
object_id AS objectid,<br />
index_id AS indexid,<br />
partition_number AS partitionnum,<br />
avg_fragmentation_in_percent AS frag<br />
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')<br />
WHERE avg_fragmentation_in_percent > @fragrate AND index_id > 0<br />
--and object_id = object_id('mlsdata');<br />
<br />
-- Declare the cursor for the list of partitions to be processed.<br />
DECLARE partitions CURSOR FOR SELECT * FROM @indexinfo;<br />
OPEN partitions;<br />
<br />
-- Loop through the partitions.<br />
FETCH NEXT FROM partitions<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>INTO @objectid, @indexid, @partitionnum, @frag;<br />
<br />
WHILE (@@fetch_status <> -1)<br />
BEGIN;<br />
IF (@@fetch_status <> -2)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>BEGIN<br />
<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>FROM sys.objects AS o<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>JOIN sys.schemas as s ON s.schema_id = o.schema_id<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>WHERE o.object_id = @objectid;<br />
<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT @indexname = QUOTENAME(name)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>FROM sys.indexes<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>WHERE object_id = @objectid AND index_id = @indexid;<br />
<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT @partitioncount = count (*)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>FROM sys.partitions<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>WHERE object_id = @objectid AND index_id = @indexid;<br />
<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>IF @frag < @rbrirate<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>IF @frag >= @rbrirate<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>IF @partitioncount > 1<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));<br />
<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>EXEC (@command);<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>PRINT N'Executed (Defrag ' + CAST(@frag AS varchar(12)) + '): ' + @command;<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>--PRINT @command;<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>END;<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>-- Get the next record to process<br />
FETCH NEXT FROM partitions<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>INTO @objectid, @indexid, @partitionnum, @frag;<br />
END;<br />
<br />
-- Close and deallocate the cursor.<br />
CLOSE partitions;<br />
DEALLOCATE partitions;<br />
<br />
<br />
<br /></div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-60279610701034612872013-09-10T05:19:00.001-07:002013-09-10T05:19:03.272-07:00Deny access permission to table<div dir="ltr" style="text-align: left;" trbidi="on">
DENY SELECT, INSERT, UPDATE, DELETE ON [dbo].[Table] TO [user]<br /><div>
<br /></div>
</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-15721645082970110722013-07-09T02:15:00.003-07:002013-07-09T02:19:04.184-07:00ReBuild Master Database From setup.exe<div dir="ltr" style="text-align: left;" trbidi="on">
F:\>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINA<br />
CCOUNTS=Domain\Administrator /SAPWD=sa password</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-60500196516026067262013-07-09T02:14:00.002-07:002017-04-12T16:16:52.158-07:00Frequent Starting up Database<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="http://www.wilkinson.com.au/wiki/index.php?title=Frequent_Starting_Up_Database_messages_in_Event_Viewer">G</a>o to SQL Options and mark Auto close to false.</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-61049038250896295072013-03-21T08:05:00.003-07:002013-03-21T08:05:40.640-07:00The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.<div dir="ltr" style="text-align: left;" trbidi="on">
<span class="Apple-style-span" style="color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px;">use [master]<br />GO<br />GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [public]<br />GO<br />use [master]<br />GO<br />GRANT EXECUTE ON [sys].[sp_OAMethod] TO [public]<br />GO<br />use [master]<br />GO<br />GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [public]<br />GO<br />use [master]<br />GO<br />GRANT EXECUTE ON [sys].[sp_OADestroy] TO [public]<br />GO<br />use [master]<br />GO<br />GRANT EXECUTE ON [sys].[sp_OAStop] TO [public]<br />GO<br />use [master]<br />GO<br />GRANT EXECUTE ON [sys].[sp_OACreate] TO [public]<br />GO<br />use [master]<br />GO<br />GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [public]<br />GO<br />sp_configure 'show advanced options', 1<br />GO<br />reconfigure<br />go<br /><br />exec sp_configure<br />go<br />exec sp_configure 'Ole Automation Procedures', 1<br />-- Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.<br />go<br />reconfigure<br />go</span></div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com5tag:blogger.com,1999:blog-2556297354150443994.post-39133538097821617412013-02-13T21:50:00.004-08:002013-02-13T21:51:57.844-08:00The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.<div dir="ltr" style="text-align: left;" trbidi="on">
sp_configure 'show advanced options', 1
<br />
GO
<br />
reconfigure
<br />
go
<br />
exec sp_configure
<br />
go
<br />
exec sp_configure 'Ole Automation Procedures', 1
<br />
-- Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
<br />
go
<br />
reconfigure
<br />
go</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-75226095517420103722013-02-11T07:39:00.002-08:002013-02-11T07:39:21.387-08:00SSRS repeat header on each pagehttp://remicaron.wordpress.com/2010/01/05/how-to-get-ssrs-to-repeat-the-row-headers-tablix-2008/hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-27721373614265392282013-01-02T05:05:00.001-08:002017-04-12T16:20:12.411-07:00Auto Login windows server<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: #f7f7f7; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px;">1. In the </span><strong style="background-color: #f7f7f7; border: 0px; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px; margin: 0px; padding: 0px; vertical-align: baseline;">Start</strong><span style="background-color: #f7f7f7; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px;"> menu click </span><strong style="background-color: #f7f7f7; border: 0px; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px; margin: 0px; padding: 0px; vertical-align: baseline;">Run</strong><span style="background-color: #f7f7f7; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px;"> and enter </span><strong style="background-color: #f7f7f7; border: 0px; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px; margin: 0px; padding: 0px; vertical-align: baseline;">control userpasswords2</strong><span style="background-color: #f7f7f7; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px;">. After clicking </span><strong style="background-color: #f7f7f7; border: 0px; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px; margin: 0px; padding: 0px; vertical-align: baseline;">OK</strong><span style="background-color: #f7f7f7; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px;"> to the User Accounts window will show up.</span><br />
<span style="background-color: #f7f7f7; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px;">2. Now click the account you want to login automatically and uncheck </span><strong style="background-color: #f7f7f7; border: 0px; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px; margin: 0px; padding: 0px; vertical-align: baseline;">Users must enter a user name and password to use this computer</strong><span style="background-color: #f7f7f7; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px;"> checkbox. If you now click </span><strong style="background-color: #f7f7f7; border: 0px; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px; margin: 0px; padding: 0px; vertical-align: baseline;">Apply</strong><span style="background-color: #f7f7f7; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px;"> it will ask for the password of the user you selected. Enter the password of the selected user twice and click </span><strong style="background-color: #f7f7f7; border: 0px; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px; margin: 0px; padding: 0px; vertical-align: baseline;">OK</strong><span style="background-color: #f7f7f7; color: #333333; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 13px;">. From now on every time you start Windows it will automatically login!</span></div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-21594541346172279212012-10-29T08:13:00.001-07:002012-10-29T08:17:41.032-07:00SQL server change timezone<div dir="ltr" style="text-align: left;" trbidi="on">
<br /></div>
SQL server change timezone.
Change server's timezone.
Go to Properties > Advance and change the language to English.
Go to Security > Logins > Edit user and in general select language to English.hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-27536024959389088452012-10-25T03:11:00.003-07:002012-10-25T03:11:49.143-07:00Could not load type System.ServiceModel.Activation.HttpModule<div dir="ltr" style="text-align: left;" trbidi="on">
<br /></div>
Could not load type System.ServiceModel.Activation.HttpModule
http://devonenote.com/2010/06/could-not-load-type-system-servicemodel-activation-httpmodule/hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-56203113443025042292012-08-07T04:48:00.002-07:002012-08-07T04:48:34.480-07:00Show and Hide leaves blank space.Tablix can solve issue
http://www.sqlchick.com/entries/2011/3/5/dealing-with-white-space-due-to-a-hidden-textbox-in-reportin.htmlhardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-42348628049774507432012-07-30T06:52:00.001-07:002012-07-30T06:52:25.090-07:00Add Row Group in SSRShttp://sql-bi-dev.blogspot.in/2010/09/groups-in-ssrs-2008.htmlhardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-53207810526924789232012-07-13T00:34:00.002-07:002012-07-13T00:35:15.585-07:00Could not load file or assembly dll Access is denied<div dir="ltr" style="text-align: left;" trbidi="on">
<br /></div>
Found the issue was related to impersonation. Some calls where impersonated and the impersonated user did not have rights to %SystemRoot%\Microsoft.NET\Framework\versionNumber\Temporary ASP.NET Fileshardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-34419096342155504552012-06-19T04:41:00.000-07:002016-02-01T02:53:30.799-08:00<div dir="ltr" style="text-align: left;" trbidi="on">
This link will have sample code for all kind of QB transactions.
file:///C:/Program%20Files/Intuit/IDN/Common/newOSR/index.html<br />
<br />
https://developer-static.intuit.com/qbSDK-current/Common/newOSR/index.html <br />
<br />
https://developer-static.intuit.com/qbSDK-current/Common/newOSR/index.html</div>
hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-77200318092355696352012-05-26T01:08:00.002-07:002012-05-26T01:08:20.680-07:00%1% is not valid win32 application<div dir="ltr" style="text-align: left;" trbidi="on">
Try this if it works for you
1) Go to Start>Run>type Sfc /PurgeCache (hit enter and let it complete)
2) Go to Start>Run>type Sfc /scannow (hit enter and let it complete)
Restart machine.
<br /></div>hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-61179904639755517172012-03-03T02:52:00.000-08:002012-03-03T02:52:51.305-08:00FIFO Avg Costhttp://www.accountingformanagement.com/average_costing_method_materials_costing.htmhardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-13120589694785232382012-02-21T08:50:00.000-08:002012-02-21T08:50:07.409-08:00Network camera urlhttp://www.crazypixels.com/support.htmhardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-73620850475489050362012-01-27T23:47:00.000-08:002012-01-27T23:47:11.826-08:00Tab Slideout Menuhttp://wpaoli.building58.com/2009/09/jquery-tab-slide-out-plugin/<br />
<br />
http://www.building58.com/examples/tabSlideOut.html<br />
<br />
http://www.roseindia.net/tutorial/jquery/TabSlideOut.htmlhardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com1tag:blogger.com,1999:blog-2556297354150443994.post-74772484467348507392012-01-10T03:14:00.001-08:002012-01-10T03:14:33.281-08:00sort stored procedure by modified dateSELECT name, create_date, modify_date,type <br />
FROM sys.objects <br />
WHERE type = 'P' order by modify_date deschardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0tag:blogger.com,1999:blog-2556297354150443994.post-38721025908166814072011-10-14T02:15:00.000-07:002011-10-14T02:15:14.741-07:00SSRS DB is properly working but SSRS webservice url is giving 500 error.Check event view entry and if u find below entry:-<br />
<br />
Message: The current identity (NT AUTHORITY\NETWORK SERVICE) does not have write access to 'C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\RSTempFiles\'.<br />
<br />
Solution :- Assign Full Control to NETWORK SERVICE user account for this folder C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\RSTempFiles\hardi.modihttp://www.blogger.com/profile/05590372920603163001noreply@blogger.com0