tag:blogger.com,1999:blog-25502753444442811362024-03-15T18:10:00.751-07:00SQL Code GuardTo understand code betterSQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.comBlogger38125tag:blogger.com,1999:blog-2550275344444281136.post-44484383068659190882017-02-22T14:50:00.001-08:002017-02-22T14:50:39.400-08:00SQL Code Guard 2.9 released<div dir="ltr" style="text-align: left;" trbidi="on">
Nothing special - just signed with renewed code sign certificate, small fixes, etc<br />
Have fun!<br />
<br />
Wait a minute! Almost forgot.<br />
More great news coming soon, stay tuned, subsribe to twitter https://twitter.com/SQLCodeGuard if you don't want miss somthing fun.</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-22963234366742138812016-03-09T15:07:00.003-08:002016-03-09T15:07:35.317-08:00New developer build of SQL Code Guard released<div dir="ltr" style="text-align: left;" trbidi="on">
Issues fixed since last developer build<br />
<span style="background-color: white; color: #141823; font-family: helvetica, arial, sans-serif; font-size: 14px; line-height: 19.32px;">[*] fixed incorrect binding of MI008 to MI007</span><br style="background-color: white; color: #141823; font-family: helvetica, arial, sans-serif; font-size: 14px; line-height: 19.32px;" /><span style="background-color: white; color: #141823; font-family: helvetica, arial, sans-serif; font-size: 14px; line-height: 19.32px;">[*] fixed xtra EI028 in case with named default constraint</span><br style="background-color: white; color: #141823; font-family: helvetica, arial, sans-serif; font-size: 14px; line-height: 19.32px;" /><span style="background-color: white; color: #141823; font-family: helvetica, arial, sans-serif; font-size: 14px; line-height: 19.32px;">[*] fixed error with IncludeIssues method when issues were not actually marked as Warning except ALL issue</span><span class="text_exposed_show" style="background-color: white; color: #141823; display: inline; font-family: helvetica, arial, sans-serif; font-size: 14px; line-height: 19.32px;"><br />[*] fixed extra MI003 Unqualified column name on xml methods<br />[*] fixed several CGUNP on CREATE/ALTER database (rarely used/new options)</span><br />
<span class="text_exposed_show" style="background-color: white; color: #141823; display: inline; font-family: helvetica, arial, sans-serif; font-size: 14px; line-height: 19.32px;"><br /></span>
<span class="text_exposed_show" style="background-color: white; color: #141823; display: inline; font-family: helvetica, arial, sans-serif; font-size: 14px; line-height: 19.32px;">Full list of issues fixed since last release you can find here - <a href="http://sqlcodeguard.com/index-database-changelog.html#R2_9_developer">http://sqlcodeguard.com/index-database-changelog.html#R2_9_developer</a></span><br />
<br />
Have fun!<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-64413728089649161682015-12-01T14:53:00.005-08:002015-12-01T14:53:59.873-08:00SQLSaturday 426, Lviv, Dec 05 - only few days left!<div dir="ltr" style="text-align: left;" trbidi="on">
Last changes and preparation for SQLSaturday in Lviv - <a href="http://sqlsaturday.com/426/EventHome.aspx">http://sqlsaturday.com/426/EventHome.aspx</a><br />
You still have a time to register and attend it.<br />
Great city, great people, great event - don't miss it!<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com1tag:blogger.com,1999:blog-2550275344444281136.post-1406525639630974212015-11-11T04:49:00.002-08:002015-11-11T05:00:48.882-08:00Support for SSMS 2016 now available<div dir="ltr" style="text-align: left;" trbidi="on">
Good news!<br />
Red-Gate published new version of its SIPF framework with support of SSMS 2016!<br />
Now you can simply download and install SIPF from Red-Gate's site <a href="http://documentation.red-gate.com/display/MA/SSMS+ecosystem+project">http://documentation.red-gate.com/display/MA/SSMS+ecosystem+project</a> - and you'll be able to use SQL Code Guard and ExpressProfiler from SSMS 2016!<br />
<br />
Great job, Red-Gate! Awesome as always!<br />
Thank you, David!<br />
<br />
<br />
<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com5tag:blogger.com,1999:blog-2550275344444281136.post-55967334078178885332015-10-30T08:23:00.001-07:002015-10-30T08:23:17.916-07:00Catching specific statements using new MI008 "Statement used" rule<div dir="ltr" style="text-align: left;" trbidi="on">
Imagine that you want (for some reason) prohibit usage of some statement (or statements), eg - CURSOR<br />
How can you find if this statement is used in the script? To perform this task you should use newly added rule MI008 "Statement used"<br />
<br />
<a name='more'></a><br />
First you must mark this rule as "Warning" on the "Issues" tab of settings window<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhb2Q_tsJ-b9II70ZeMYujRLel1flmRbCry39hicpcX60WSayrjr8StnDjGz2Kh6RBWF2hxDKb3MSOUUc1bU_XqWYSQWCcrVxr8_u49l3lc5ix7hxDElCxK04bOUGJqjv5gpc1uwWtpD6c/s1600/MI008MarkedAsWarning.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="87" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhb2Q_tsJ-b9II70ZeMYujRLel1flmRbCry39hicpcX60WSayrjr8StnDjGz2Kh6RBWF2hxDKb3MSOUUc1bU_XqWYSQWCcrVxr8_u49l3lc5ix7hxDElCxK04bOUGJqjv5gpc1uwWtpD6c/s400/MI008MarkedAsWarning.png" width="400" /></a></div>
<br />
Second - you must specify which statement you want to catch.<br />
Each statement (and event most parts of statement) can be identified by tag. Full list of tag is pretty big and I'm not yet ready to publish it. But here are some of tags:<br />
CREATE INDEX: index-statement<br />
DROP INDEX: drop-index<br />
CREATE TABLE: create-statement-table<br />
DROP TABLE: drop-table<br />
TRUNCATE TABLE: truncate-table<br />
DECLARE CURSOR: cursor-declaration<br />
<i>If you need more tags please contact me, i'll help you.</i><br />
<br />
Ok, imagine that you want to catch DECLARE CURSOR and CREATE TABLE statements<br />
You need to add appropriate tags to MI008 Statements list on "Options" tab of Settings window, one tag per line.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3b8l-3Y242ENI94Q2tqb3IqA29lzWPeEnyZOM_37poMFO1LhkM3pXtylhfvhca-JQlpDlZKf9BSSgfPkIZVHFGmiSE985jeXs9625hShfTlDM1zQoTIEPo2Alf6CtsNv7E929_NXNyUU/s1600/MI008TagList.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="172" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3b8l-3Y242ENI94Q2tqb3IqA29lzWPeEnyZOM_37poMFO1LhkM3pXtylhfvhca-JQlpDlZKf9BSSgfPkIZVHFGmiSE985jeXs9625hShfTlDM1zQoTIEPo2Alf6CtsNv7E929_NXNyUU/s400/MI008TagList.png" width="400" /></a></div>
Now you can process script or database to find if statements you find is really used.<br />
Column "Additional info" of issues list will contain tag of found statement.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiXI69LLuOHjAvMUT96DD-uq9kMxhKVXkt5yM1aAkYaHnFodAJvCnGNTmBAPdTqg78EmvpQWKktVchwNUpVNmXfNXreHKlJ5NIN0aZDXbQ58ZePwrS5oK6J9pVg_ZRWhZdjGqgkxhQLYw/s1600/MI008FoundStatements.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="131" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiXI69LLuOHjAvMUT96DD-uq9kMxhKVXkt5yM1aAkYaHnFodAJvCnGNTmBAPdTqg78EmvpQWKktVchwNUpVNmXfNXreHKlJ5NIN0aZDXbQ58ZePwrS5oK6J9pVg_ZRWhZdjGqgkxhQLYw/s400/MI008FoundStatements.png" width="400" /></a></div>
<br />
<br />
That's all, folks!<br />
Have fun!<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com1tag:blogger.com,1999:blog-2550275344444281136.post-22974706629473797542015-09-21T02:47:00.004-07:002015-09-21T02:47:46.451-07:00I'm alive!!!!<div dir="ltr" style="text-align: left;" trbidi="on">
Hi all!<br />
Sorry for long delay but i was a pretty busy preparing SQLSat<br />
Now mission is accomplished and i'll return to development in the nearest future.<br />
<br />
<div>
<br /></div>
</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-8879455566874722452015-06-25T05:52:00.001-07:002015-06-25T05:52:30.540-07:00ExpressProfiler: say "Hi!" to new developer!<div dir="ltr" style="text-align: left;" trbidi="on">
I glad to introduce new member of Express Profiler team - Chris Moore!<br />
He has many ideas and eager to implement them.<br />
Welcome onboard, Chris!</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com1tag:blogger.com,1999:blog-2550275344444281136.post-45717193505335999652015-06-24T04:36:00.002-07:002015-06-24T04:36:43.866-07:00SQL Code Guard - Developer build: just as planned<div dir="ltr" style="text-align: left;" trbidi="on">
Well, not exactly just as planned, but at last!<br />
Few minor fixes<br />
<div style="color: #25262b; font-family: Arial, Tahoma, Verdana, Helvetica, sans-serif; font-size: 14px; margin-bottom: 4px;">
[*] fixed unparsed with CONTAINS condition</div>
<div style="color: #25262b; font-family: Arial, Tahoma, Verdana, Helvetica, sans-serif; font-size: 14px; margin-bottom: 4px;">
[*] fixed xtra "ST010 Use alias for all table sources" for UPDATE/DELETE statements with single FROM clause item</div>
<div style="color: #25262b; font-family: Arial, Tahoma, Verdana, Helvetica, sans-serif; font-size: 14px; margin-bottom: 4px;">
[*] fixed xtra MI003 "Unqualified column name" on DATENAME(MONTH, GETDATE())</div>
<div style="color: #25262b; font-family: Arial, Tahoma, Verdana, Helvetica, sans-serif; font-size: 14px; margin-bottom: 4px;">
You can download Developer Build at http://sqlcodeguard.com/</div>
<div style="color: #25262b; font-family: Arial, Tahoma, Verdana, Helvetica, sans-serif; font-size: 14px; margin-bottom: 4px;">
Enjoy! At your own risk, of cours ;)</div>
<div style="color: #25262b; font-family: Arial, Tahoma, Verdana, Helvetica, sans-serif; font-size: 14px; margin-bottom: 4px;">
<br /></div>
</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-53194234121027031872015-06-16T03:36:00.001-07:002015-06-16T03:36:35.615-07:00SQL Code Guard - developer builds<div dir="ltr" style="text-align: left;" trbidi="on">
Hi all<br />
Unfortunatelly I'm pretty busy now and will be busy till end of the year<br />
So I have not so much time for development and feature implementation.<br />
It may take a lot of time while I implement enough features or fix enough bugs to make "release".<br />
So i've decided to esatblish "developers build" - build that i'll publish every week/2 weeks or when i decide that changes worths to be published.<br />
Same installation package, still digitally signed, but may contains some untested code.<br />
Use it at your own risk. Well... Everything as before :) so nothing changes except of more frequent builds.<br />
<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-16257083889547070642015-05-20T05:16:00.002-07:002015-05-20T05:20:08.158-07:00SqlSaturday 377, Kiev, May 23 - only 3 days left<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<div style="text-align: left;">
Hello everyone!<br />
As you may already know - on 23rd May in Kiev will be held SqlSaturday 377.<br />
Hurry up!<br />
You can meet many famous speakers, including Kevin Boles, Benjamin Nevares, Dmitri Korotkevitch, Satya Jayanty, Mihail Mateev, Boris Hristov, Dmitry Piliugin and other.<br />
Also you can attend 3 Pre-cons:<br />
Kevin Boles. Tune Like A Guru! - <a href="https://sqlsaturday-377-precon-kevin-boles.ticketforevent.com/">https://sqlsaturday-377-precon-kevin-boles.ticketforevent.com/</a><br />
Dmitri Korotkevitch. SQL Server Internals from the practical angle. - <a href="https://sqlsaturday-377-precon-dmitr.ticketforevent.com/">https://sqlsaturday-377-precon-dmitr.ticketforevent.com/</a><br />
Benjamin Nevarez. SQL Server Query Tuning & Optimization. - <a href="https://sqlsaturday-377-precon-benjamin-nevarez.ticketforevent.com/">https://sqlsaturday-377-precon-benjamin-nevarez.ticketforevent.com/</a><br />
Session schedule can be found here <a href="http://www.sqlsaturday.com/377/Sessions/Schedule.aspx">http://www.sqlsaturday.com/377/Sessions/Schedule.aspx</a><br />
Follow SqlSaturday 377 at Twitter - <a href="https://twitter.com/hashtag/sqlsatkiev?f=realtime&src=hash">#sqlsatkiev</a><br />
See you in Kiev at SqlSaturday 377!<br />
<div>
<br /></div>
</div>
</div>
</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-63236608345415595522015-05-04T07:24:00.003-07:002015-05-04T07:24:50.981-07:00SQL Code Guard v2.8 released<div dir="ltr" style="text-align: left;" trbidi="on">
Main change - SCG moved back to .Net framework 3.0 to restore support of SSMS 2008/2008R2<br />
Few small fixes, additional logging, nothing critical.<br />
Enjoy!<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-2749313673701530642015-05-02T15:38:00.003-07:002015-05-02T15:38:58.647-07:00Using SQL Code Guard TFS Checkin Policy with VS2013<div dir="ltr" style="text-align: left;" trbidi="on">
To use TFS Checking policy the first thing that you must do - is to add policy to your TFS source control<br />
The process itself is pretty simple<br />
<br />
<ol style="text-align: left;">
<li>Install SQL Code Guard at target computer. Please remember that you will need to have SCG installed on each computer on which you will do checkin to project with SCG policy enabled.</li>
<li>Register SQL Code Guard with VS - <a href="http://msdn.microsoft.com/en-us/library/ex6a2fad%28v=vs.110%29.aspx">devenv /setup</a></li>
</ol>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgj3Uq37ae6hSsrHrE9vI7xlpKhgKUsTpdv6n-ylAdvD_-Qns76GAuSx7CAP5qwdSLwdtcZmSJsWQ_qmvyv2Gc3oK4yZLS0Ho9BJzHF5qvtBxjp_OGMZTA6uzNYSggebpz4032uDUxBBqc/s1600/Image+001.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgj3Uq37ae6hSsrHrE9vI7xlpKhgKUsTpdv6n-ylAdvD_-Qns76GAuSx7CAP5qwdSLwdtcZmSJsWQ_qmvyv2Gc3oK4yZLS0Ho9BJzHF5qvtBxjp_OGMZTA6uzNYSggebpz4032uDUxBBqc/s1600/Image+001.png" height="45" width="320" /></a></div>
<ol style="text-align: left;">
<li><a href="https://msdn.microsoft.com/en-us/library/ms181459(v=vs.100).aspx">add checking policy</a></li>
<ol>
<li>select TFS collection / project</li>
<li>open menu TEAM -> Team Project Settings -> Source Control</li>
<li>Select Check-in Policy tab</li>
<li>click Add button</li>
<li>select SQLCodeGuard.CheckinPolicy policy and press OK button</li>
<li>Press OK to close "Source control settings" dialog</li>
</ol>
</ol>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVzTemRUt9SM4rw6a1LpCPJ4cxDfrhZ2-kDQ3ouYvL2vpcSCQVTQGYoOVC9KUUnGJJYPnJbWnNGqD9E2NRE3TNMYgA4x4sOuG8bewQ2GmpNW8PcstdS_h_k70_jFrB9F8bAt3MvLfFvoY/s1600/Image+002.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVzTemRUt9SM4rw6a1LpCPJ4cxDfrhZ2-kDQ3ouYvL2vpcSCQVTQGYoOVC9KUUnGJJYPnJbWnNGqD9E2NRE3TNMYgA4x4sOuG8bewQ2GmpNW8PcstdS_h_k70_jFrB9F8bAt3MvLfFvoY/s1600/Image+002.png" height="220" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div>
Now you can try to use/test checkin policy</div>
<div>
<ol style="text-align: left;">
<li>add (or edit) sql file in TFS</li>
<li>select "Pending changes"</li>
<li>If you didnt placed SQL Code Guard settings file in project directory then you will get message "Unable to find settings"</li>
<li>if there are issues in script file - you will see warning message</li>
</ol>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhIEX7uPXsNWPgT0mAq4myfMrr_yNPSYZKbkyvVfDxPW0b7a7tZ0OuQ54p0PGAP6qpNxMM3zHJ3f5RJL4AHTBKbLoS0s7kjt74_0u9m744BdaTG0YRQagzm03xLmeblScZcc_teTpQvQQ/s1600/Image+003.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhIEX7uPXsNWPgT0mAq4myfMrr_yNPSYZKbkyvVfDxPW0b7a7tZ0OuQ54p0PGAP6qpNxMM3zHJ3f5RJL4AHTBKbLoS0s7kjt74_0u9m744BdaTG0YRQagzm03xLmeblScZcc_teTpQvQQ/s1600/Image+003.png" height="320" width="236" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="text-align: left;">You can doubleclick on message and error list window with full set of of issues will appear</span></div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiT2U_aBUWHAE7TlNkC57JpzaKimKNcK2wIIL0bD-8f7y-azwq6if9OJnnrcSW03YOJt4rr_DokCxUpZD9b8v7r2hYVeBBBo4SIgDHz-RGxprz1w-_hrVBPdwGiNxmyI9Ks1dy79ND_Oo4/s1600/Image+004.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiT2U_aBUWHAE7TlNkC57JpzaKimKNcK2wIIL0bD-8f7y-azwq6if9OJnnrcSW03YOJt4rr_DokCxUpZD9b8v7r2hYVeBBBo4SIgDHz-RGxprz1w-_hrVBPdwGiNxmyI9Ks1dy79ND_Oo4/s1600/Image+004.png" height="101" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="text-align: left;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="text-align: left;">Doubleclick on issue will navigate you to location of issue.</span></div>
<div>
That's all, folks!</div>
<div>
<br /></div>
<div>
<br /></div>
</div>
<br />
<br />
<div>
<br /></div>
</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-41535985181497830152015-03-18T08:33:00.000-07:002015-03-18T08:34:43.731-07:0024HOP Russian Edition<div dir="ltr" style="text-align: left;" trbidi="on">
Spoke at <a href="http://www.sqlpass.org/24hours/2015/russian/%D0%93%D0%BB%D0%B0%D0%B2%D0%BD%D0%B0%D1%8F.aspx">24HOP Russian edition</a>.<br />
Interesting experience.<br />
It is difficult to determine mood of audience if there is no visible audience :)<br />
And looks like that 90 minutes is too less for me.</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-91165553367258274712015-03-04T08:36:00.002-08:002015-03-18T08:35:29.517-07:00SQL Code Guard v2.7 and SQL Server 2008R2: bad news and good news<div dir="ltr" style="text-align: left;" trbidi="on">
Hi all!<br />
Bad news: SQL Code Guard v2.7 is not working with SSMS from SQL Server 2008R2 (and below).<br />
Good news: I know about this issue, already fixed it in my development environment and will release fix in nearest possible future.<br />
Bad news (again) : integration will not work with SSMS from SQL Server 2005. Even in next release.<br />
<br />
<br />
<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-8235773973835745132015-02-17T14:51:00.000-08:002015-02-17T15:09:36.124-08:00SQL Code Guard v2.7 released<div dir="ltr" style="text-align: left;" trbidi="on">
Hola!<br />
<a href="http://sqlcodeguard.com/">SqlCodeGuard v2.7</a> is just released.<br />
<div style="text-align: left;">
To be brief - major changes:</div>
<div style="text-align: left;">
1. Added <a href="http://sqlcodeguard.com/index-database-aq.html#cmdlineutil">command line utility</a><br />
2. Added <a href="http://sqlcodeguard.com/index-database-aq.html#scgallowignore">scgallow(issue)/scgignore(issue`)</a>to control which issues should be registered<br />
3. Added [<a href="http://sqlcodeguard.com/index-database-issues.html#SC004">SC004</a>] Found todo item. You can use "--# todo <text>", "--# bug <text>", "--# fix <text>" or "--# warning <text>" marks in your scripts.<br />
4. Added support for VS2013<br />
5. SQL Code Guard moved to .Net Framework 4.0<br />
<div style="text-align: left;">
<br />
Full list of changes can be found <a href="http://sqlcodeguard.com/index-database-changelog.html#R2_7">here</a> </div>
<div style="text-align: left;">
Bon appétit!</div>
<div style="text-align: left;">
<br /></div>
<br /></div>
<div style="text-align: left;">
<br /></div>
<span style="color: #25262b; font-family: Arial, Tahoma, Verdana, Helvetica, sans-serif; font-size: 14px;"><br /></span>
<span style="color: #25262b; font-family: Arial, Tahoma, Verdana, Helvetica, sans-serif; font-size: 14px;"><br /></span></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-9387938992282612292015-02-09T05:15:00.000-08:002015-02-09T05:15:54.684-08:00ExpressProfiler - new build published<div dir="ltr" style="text-align: left;" trbidi="on">
Just published new signed build of <a href="https://expressprofiler.codeplex.com/">ExpressProfiler</a> - few nice improvements like advanced search, stay on top window, transparency - just to make life easier<br />
Actually there is no change since last august, i simply build, sign and publish version that already was in CodePlex's TFS, but now you can get binaries.<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-38278670852091832922014-11-15T12:56:00.001-08:002014-11-15T12:56:33.888-08:00How to fix: Visual Studio 2012 startup problem: The 'HDIProjectPackage' package did not load correctly. <div dir="ltr" style="text-align: left;" trbidi="on">
After installing Community Edition of VS2013 with some additional packages i've noticed that my VS2012 starts failing to start with message like "The 'HDIProjectPackage' package did not load correctly. The problem may have been caused by a configuration change or by the installation of another extension. You can get more information by examining the file 'C:\Users\************\AppData\Roaming\Microsoft\VisualStudio\11.0\ActivityLog.xml'."<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-oAl7sz16hciYvvf9ozZxghliLSBviMcqm0yw41JQt7AHnn6G4bnufgQ8POpMteqCzjOJ_zeEjOVD1QqvRF4zktJKBNWjfNZQJo6EP2Dz-qYgkwyUCzus7sOucmr7S7FLWhSGvU-qIIA/s1600/Image+031.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-oAl7sz16hciYvvf9ozZxghliLSBviMcqm0yw41JQt7AHnn6G4bnufgQ8POpMteqCzjOJ_zeEjOVD1QqvRF4zktJKBNWjfNZQJo6EP2Dz-qYgkwyUCzus7sOucmr7S7FLWhSGvU-qIIA/s1600/Image+031.png" height="197" width="320" /></a></div>
<br />
Activity log shows<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLTkaKXCNfAcFr38So796nWHnsTT9Hzzhu62q-igqHuo4aq8vqY6nvgizVchHCvxhVptwh208FZB8Nc8FHWkUdupnVSUyNJAz_DR9-X1v7xOjpjibiBSch62FsP1bOd56zgLs-absldGI/s1600/Image+033.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLTkaKXCNfAcFr38So796nWHnsTT9Hzzhu62q-igqHuo4aq8vqY6nvgizVchHCvxhVptwh208FZB8Nc8FHWkUdupnVSUyNJAz_DR9-X1v7xOjpjibiBSch62FsP1bOd56zgLs-absldGI/s1600/Image+033.png" height="68" width="320" /></a></div>
<br />
SetSite failed for package [HDIProjectPackage]<br />
End package load [HDIProjectPackage]<br />
{665BA479-E656-4485-9097-7CE2A22A90F2} (just to let Google to find this page)<br />
<br />
Google knows nothing about HDIProjectPackage. I dont know how to fix errors with VS2012. I'm stuck!<br />
After 24 hours of thinking and meditation (and several reinstalls of both VS2012 and VS2013) I've found that the problem was with "Microsoft Azure HDInsight Tools for Visual Studio" which I've installed via Web Platform Installer<br />
HDIProjectPackage->HDInsight, looks similar, right?<br />
So I uninstalled "Microsoft Azure HDInsight Tools for Visual Studio" and my VS2012 now alive!!!!!<br />
And I can continue to develop next release of <a href="http://sqlcodeguard.com/">SqlCodeGuard</a> with such nice features like, say, preprocessor commands to disable specific issues in particular scripts (or even - part of script)</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com3tag:blogger.com,1999:blog-2550275344444281136.post-87417741918136765532014-10-05T01:56:00.001-07:002014-10-05T01:56:23.272-07:00SQLSaturday 311, Sofia, October 11<div dir="ltr" style="text-align: left;" trbidi="on">
Making last changes before <a href="https://www.sqlsaturday.com/311/eventhome.aspx">sqlsat 311</a>.<br />
By the way, did you ever visited <a href="https://www.sqlsaturday.com/">SQL Saturday</a>? No? Why?<br />
SQL Saturday is fun, SQL Saturday is free, SQL Saturday is just what you need!<br />
Good place to meet famous speakers from all around the world, ask them question that cannot be answered by usual people, make new friends :)<br />
<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-83216670156858972422014-09-15T03:44:00.001-07:002014-09-15T03:44:53.916-07:00To be brief. What to do when PATH variable is too long to be real PATH.<div dir="ltr" style="text-align: left;" trbidi="on">
Sometimes after installing pretty useful addins when starting SSMS you can see error message like "Exception has been thrown by the target of an invocation"<br />
<br />
That can mean that your <a href="http://environmentvariables.org/Path">PATH</a> variable is <a href="http://jl45sql.wordpress.com/2013/01/17/exception-has-been-thrown-by-the-target-of-an-invocation/">tooooo long</a> to fit some system functions - and SSMS crashes.<br />
Question "Why one function allows to write value that other function cannot read?" falls far beyond this topic.<br />
So - what can you do?<br />
<br />
First that came into my mind is manually change PATH variable - remove entries that are no longer needed and replace full folder names with the same names but in 8.3 format.<br />
But stop! My PATH variable is more than 2K chars long! I dont want to make all these changes by my own hands!<br />
Lets write small program instead, ok?<br />
<br />
<!-- code formatted by http://manoli.net/csharpformat/ -->
<pre class="csharpcode"><span class="kwrd">using</span> System;
<span class="kwrd">using</span> System.Runtime.InteropServices;
<span class="kwrd">using</span> System.Text;
<span class="kwrd">namespace</span> ShortenPath
{
<span class="kwrd">class</span> ShortifyPath
{
[DllImport(<span class="str">"kernel32.dll"</span>, SetLastError = <span class="kwrd">true</span>)]
<span class="kwrd">private</span> <span class="kwrd">static</span> <span class="kwrd">extern</span> <span class="kwrd">int</span> GetShortPathName(String pathName, StringBuilder shortName, <span class="kwrd">int</span> cbShortName);
<span class="kwrd">static</span> <span class="kwrd">void</span> Main(<span class="kwrd">string</span>[] args)
{
<span class="kwrd">string</span> path = Environment.GetEnvironmentVariable(<span class="str">"PATH"</span>);
Console.WriteLine(<span class="str">"Current path value:{0}"</span>,path);
<span class="kwrd">string</span>[] chunks = path.Split(<span class="kwrd">new</span> <span class="kwrd">char</span>[] {<span class="str">';'</span>},StringSplitOptions.RemoveEmptyEntries);
StringBuilder sb = <span class="kwrd">new</span> StringBuilder();
<span class="kwrd">foreach</span> (<span class="kwrd">string</span> chunk <span class="kwrd">in</span> chunks)
{
StringBuilder sb1 = <span class="kwrd">new</span> StringBuilder(500);
<span class="kwrd">int</span> n = GetShortPathName(chunk, sb1, 500);
<span class="kwrd">if</span> (0 == n)
{
<span class="kwrd">if</span> (2 != Marshal.GetLastWin32Error())
{
Console.WriteLine(<span class="str">"Something went wrong..."</span>);
Console.WriteLine(<span class="str">"{0}"</span>, Marshal.GetLastWin32Error().ToString());
<span class="kwrd">return</span>;
}
<span class="kwrd">else</span>
{
sb1.Append(chunk);
}
}
sb.AppendFormat(<span class="str">"{0};"</span>, sb1.ToString());
}
Console.WriteLine(<span class="str">"Path is shortened! Old len: {0}, new len {1}"</span>,path.Length,sb.ToString().Length);
Console.WriteLine(sb.ToString());
}
}
}
</pre>
<br />
Compile it using csc.exe<br />
C:\Windows\Microsoft.NET\Framework\v2.0.50727\csc.exe ShortenPath.cs<br />
<br />
And voila!<br />
Run it and the program will print old value of PATH variable and the new value with shortened folder names. If you agree with result then you can <a href="http://www.faqforge.com/windows/setting-the-path-environmental-variable-on-windows-7/">use new value as value for PATH variable</a>.<br />
In my case I've saved about 500 chars from 2000.<br />
<pre class="csharpcode"></pre>
</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com1tag:blogger.com,1999:blog-2550275344444281136.post-28527245672467538212014-08-18T02:05:00.001-07:002014-08-18T02:08:21.580-07:00SQL Code Guard v2.6 released<div dir="ltr" style="text-align: left;" trbidi="on">
Today we released new version of <a href="http://sqlcodeguard.com/">SQL Code Guard, 2.6</a><br />
<div>
Main <a href="http://sqlcodeguard.com/index-database-changelog.html#R2_6">changes</a>:</div>
<div>
1. Bug fixing</div>
<div>
2. Small GUI and usability improvements</div>
<div>
3. Fixed support of SQL2014</div>
<div>
4. Changed behavior of some issues.</div>
<div>
<br /></div>
<div>
Full list of changes you can see at <a href="http://sqlcodeguard.com/index-database-changelog.html#R2_6">http://sqlcodeguard.com/index-database-changelog.html#R2_6</a></div>
<div>
Enjoy!</div>
<div>
<br /></div>
</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-20980005032995567752014-08-04T03:03:00.003-07:002014-08-04T03:03:59.301-07:00ExpressProfiler - new release<div dir="ltr" style="text-align: left;" trbidi="on">
Just released new build of <a href="https://expressprofiler.codeplex.com/">ExpressProfiler</a>.<br />
No major changes, just cosmetic improvements - shortcut here, confirmation dialog there, new captured events (SQL:StmtStarting/Completed, Blocked Process report), descriptions for events/columns.<br />
Still digitally signed, still 2 options - installation package (including <a href="http://www.red-gate.com/products/sql-development/add-ins">Red Gate Ecosystem</a> registration ) and standalone application.<br />
<br />
Also do not forget to try our main t-sql tool - <a href="http://sqlcodeguard.com/">Sql Code Guard</a>. It is really cool :)<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-33068343998027449452014-07-22T06:42:00.002-07:002014-07-22T06:42:44.883-07:00Keep it simple.... you know.<div dir="ltr" style="text-align: left;" trbidi="on">
Twice a week I am asked "why don't you add new cool feature to ExpressProfiler?"<br />
The answer is as simple as ExpressProfiler itself - you should always keep things as simple as you can.<br />
<br />
Should be balance.<br />
On one hand you have nothing that requires nothing and does nothing - perfect minimalism.<br />
On the other hand you have standard SQL Profiler - it does all and requires all.<br />
<br />
Express Profiler should be as simple as possible, so you could easily copy and use it; and as useful as possible so you would be able and wanted to use it.<br />
<br />
And yes - sometimes I simply too lazy to implement feature request.<br />
<br />
<br /></div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-40555588317488313542014-07-07T01:28:00.000-07:002014-07-07T01:28:44.136-07:00[BP017] DELETE statement without WHERE clause<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
Sometimes people ask me: "What wrong with following code? Why issue <a href="http://sqlcodeguard.com/index-database-issues.html#BP017" target="_blank">BP017</a> is registered?"<br />
<pre class="csharpcode"><span class="kwrd">delete</span> d
<span class="kwrd">from</span> dbo.<span class="kwrd">Data</span> d <span class="kwrd">inner</span> <span class="kwrd">join</span> #ids i <span class="kwrd">on</span> d.id = i.id</pre>
<pre class="tsqlcode"></pre>
<div dir="ltr" style="text-align: left;" trbidi="on">
As you can see, rows to be deleted are limited by joining with temporary table named #ids which apparently contains a list of needed row identifiers.<br />
So what is the problem? Why BP017 is registered?<br />
<a name='more'></a><br />
The reason why is that description of issue must be read literally - "without WHERE clause".<br />
Do you see any WHERE clause? No? Well, you got your issue.<br />
<br />
Looks silly, I know :)<br />
<br />
[nerd mode on]<br />
To be precise, "<a href="http://technet.microsoft.com/en-us/library/ms188047.aspx">WHERE </a>Specifies the search condition for the rows returned by the query".<br />
If there is no WHERE clause then all rows can be affected by statement. And if some rows where filtered out by join - we are simply lucky.<br />
[nerd mode off]<br />
<br />
So if it usual for you to delete rows without WHERE clause you can simply turn off this issue or add table name to <a href="http://sqlcodeguard.com/index-database-aq.html#exceptionslist" target="_blank">BP017AllowedTables</a>.<br />
In some cases you can use <a href="http://msdn.microsoft.com/en-us/library/ms177570.aspx" target="_blank">TRUNCATE </a>statement instead of DELETE without limitation. However you should take into account that TRUNCATE is not the same as DELETE (see Remarks and Permissions section in MSDN article - foreign keys, triggers and owner level permissions).<br />
And yet some differences:<br />
1. You cannot truncate view, derived table/CTE or object at linked server.<br />
2. TRUNCATE resets IDENTITY (<a href="http://blog.sqlauthority.com/2012/08/24/sql-server-delete-truncate-and-reseed-identity/">http://blog.sqlauthority.com/2012/08/24/sql-server-delete-truncate-and-reseed-identity/</a>)<br />
<br />
In nearest future I plan to add yet another issue, something like "DELETE statement without WHERE or JOIN clause".<br />
<br />
Follow me at Twitter <a href="https://twitter.com/SQLCodeGuard" target="_blank">@SQLCodeGuard</a> or subscribe to Blog to be the first one to know about it.</div>
</div>
</div>
</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0tag:blogger.com,1999:blog-2550275344444281136.post-80634090347018412422014-06-30T03:05:00.001-07:002014-06-30T03:05:22.483-07:00I like to move it, move it. Upload and download binary data to or from SQL Server using standard tools<div dir="ltr" style="text-align: left;" trbidi="on">
Imagine that you have a huge collection of Hello Kitty images (I do).<br />
One day you make a decision to organize your collection - sort it, label it, tag it, remove duplicates and so on. The first thing you need - sophisticated data storage, of course. And it is obvious that you choose SQL Server - the world's best database platform :)<br />
And since SQL Server is already present in our Universe - how about to load binary data into it?<br />
<br />
<a name='more'></a><h3 style="text-align: left;">
Load data</h3>
What first comes into your mind when you hear "load data into SQL Server"? SSIS of course! Very powerful, very flexible, very.... Too "very" I guess. You need to develop SSIS package, and before you need to learn how you can develop SSIS package... I'm too old and lazy.<br />
Second - as all of true developer - "I can develop my unique data load application!". Yes, you can. Next time.<br />
Maybe there is a really simple solution for one-time ad-hoc data load task?<br />
<br />
Do you remember? When you read MSDN you noticed <a href="http://msdn.microsoft.com/library/ms190312.aspx" target="_blank">OPENROWSET</a> function which primary use "is an alternative to accessing tables in a linked server and is a
one-time, ad hoc method of connecting and accessing remote data by using
OLE DB."<br />
However, "OPENROWSET
also supports bulk operations through a built-in BULK provider that
enables data from a file to be read and returned as a rowset."<br />
What that means for us? That means that we can read binary file from a disk :)<br />
<br />
<pre class="csharpcode"><span class="kwrd">declare</span> @rawdata varbinary(<span class="kwrd">max</span>)
<span class="kwrd">select</span> @rawdata = <span class="kwrd">CAST</span>(b <span class="kwrd">as</span> varbinary(<span class="kwrd">max</span>)) <span class="kwrd">FROM</span> <span class="kwrd">OPENROWSET</span>(<span class="kwrd">BULK</span> N<span class="str">'d:\hello\kitty.jpg'</span>, SINGLE_BLOB) a(b)</pre>
<br />
When you know how to load single file into variable - you know how to load all files.<br />
Of course it's not all that simple - you cannot pass variable as a file name so you must use dynamic sql, SQL Server should have appropriate security permissions to access directory with file and so on, but all these problems can be easily solved.<br />
<br />
<h3 style="text-align: left;">
Unload data</h3>
Ok. We successfully loaded data into database, sorted, cleared, all duplicates were removed - now time to unload images back to the disk.<br />
How to do that?<br />
Unfortunately there is no t-sql statement to accomplish this task. But we have <a href="http://msdn.microsoft.com/library/ms162802.aspx" target="_blank">BCP</a> utilty!<br />
To unload binary data to a file on disk you need:<br />
1. Query that returns single line with single binary column<br />
<pre class="csharpcode"><span class="kwrd">select</span> KittyImage <span class="kwrd">from</span> KittyTable <span class="kwrd">where</span> id = 1</pre>
2. Simple format file (image.fmt) to strip unnecessary leading bytes from output:<br />
<pre>9.0
1
1 SQLBINARY 0 0 "" 1 Data ""
</pre>
<br />
Now you can call BCP:<br />
<pre>bcp "select KittyImage from KittyTable where id = 1" queryout kitty.jpg -T -n -f image.fmt</pre>
<pre></pre>
Done. You unloaded single image to file on the disk.<br />
To unload multiple files I usually use T-SQL to generate content of the <a href="http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/batch.mspx?mfr=true">bat file</a> and run this file.<br />
<br />
That's all, folks! </div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com2tag:blogger.com,1999:blog-2550275344444281136.post-79894160712566194172014-06-23T01:59:00.000-07:002014-06-23T01:59:43.809-07:00ExpressProfiler: Yet another "when and why" story.<div dir="ltr" style="text-align: left;" trbidi="on">
Core of <a href="https://expressprofiler.codeplex.com/" target="_blank">ExpressProfiler</a> was born about 8 years ago when I worked for a company whose mission was the migration of Oracle and Sybase to SQL Server. We used a tool for automated database code conversion and migration (a pretty good tool btw - <a href="http://blogs.msdn.com/b/ssma/" target="_blank">SSMA</a>).<br />
If you already know what is the difference between an "automatic" and an "automated" conversion then you may guess that there were a lot of manual work involved on the rewriting and optimization of converted code.<br />
<a name='more'></a><br />
The testing and optimization process required, of course, the execution of converted code, measuring execution time, proving that execution results were correct and a bunch of other stuff.<br />
Sometime the execution performance was tooo bad. This wasn’t unexpected - we had code automatically converted from Oracle (where it was tightly optimized by Oracle's DBAs) and tried to run this code on SQL Server - without the appropriate indexes, without taking into account SQL Server specifics, and we used some emulation of Oracle's features (package variables, collections and so on).<br />
So it was no surprise that we experienced troubles with performance.<br />
As you may know one of the first steps when optimizing a large SQL system (it was a really huge system - thousands of stored procedures which were involved in very complex calculation processes) is tracing execution of the system down to SP:StmtCompleted, gathering aggregated statistics, finding the most expensive statements and eliminating them.<br />
You may ask me, "why did you trace executed statements? You have sys.dm_exec_xxx DMVs! You can easily get statistics!"<br />
Well, there is no such thing as a free lunch.<br />
DMVs provide aggregated statistics, with no information of query execution distribution. Also these DMVs operate on the data from the execution plan cache, which can be flushed (either fully or partially) and you can lose all your statistics. So...<br />
We were forced to use trace data, to be specific, <a href="http://msdn.microsoft.com/en-us/library/cc293613.aspx" target="_blank">server side traces</a> (all these funny sp_trace_xxx stored procedures).<br />
And these traces provided us with many gigabytes of trace data (you may imagine how much trace data can be generated by heavy a calculation process that runs for twenty hours!). And this data needs to be stored somewhere, and after getting the trace we have to read it, aggregate it and so on and so forth! Such a waste of resources.<br />
So, after little research (which involved looking at standard classes as <a href="http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.trace.tracefile.aspx" target="_blank">TraceFile</a>, <a href="http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.trace.tracetable%28v=sql.120%29.aspx" target="_blank">TraceTable </a>and <a href="http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.trace.traceserver%28v=sql.120%29.aspx" target="_blank">TraceServer</a>) I decided (as every true DBA and programmer does) to create my own teeny-weeny class for working with SQL traces "on the fly".<br />
No sooner said than done, in a few days I had a basic trace class and a whole family of useful tracing tools built on top of it.<br />
Meanwhile, the conversion project was successfully completed and I decided to write a short article (with example) on custom tracing for a developer site.<br />
As time went by, the site announced its closure, and I’ve rewritten the example as a first version of ExpressProfiler and published it on CodePlex.<br />
<br />
I hope that this small tool can be useful not only for me but also for many DBAs and developers all over the world :)</div>
SQL Code Guardhttp://www.blogger.com/profile/02111207794115510347noreply@blogger.com0