tag:blogger.com,1999:blog-47597265648720478742024-03-13T01:51:31.018-07:00Real World Information Systems<a href="http://www.realworldis.com">RealWorldIS.com</a>Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.comBlogger79125tag:blogger.com,1999:blog-4759726564872047874.post-43464430567211505512016-06-17T00:43:00.001-07:002016-06-17T00:43:44.179-07:00Performance Tuning: Oracle and SQL Server<p>In the past few weeks, I was able to attend an Oracle Performance Optimization training put on by my organization.  The training was performed by Gaja Vaidyanatha(@dbperfman).  Now Gaja was a very cogent and informative trainer.  The reason for this post though, is that i finally had the more formal training in the Oracle side to compare to the SQL Server side.  In around 2010 or so, i went to a week long Performance Tuning and Optimization class put on by Microsoft.  That was conducted by Josh Vickery (<a href="https://www.linkedin.com/in/joshvickery">https://www.linkedin.com/in/<strong>joshvickery</strong></a><strong>)</strong> also a blazingly smart guy.</p> <h2>What’s common between Oracle and SQL Server Performance Tuning?</h2> <p>Well hopefully without coming across as a hanger onner, what’s common is how obvious in hindsight the general concepts are.   Theory of constraints and basic analysis and scientific method rule the day.  Of course the tools vary, but the basic approach is the same.  Gather data on where your database operations are spending most of their time, determine the largest blocker.  Optimize that, then re-measure.  Lather, rinse, repeat.  So both formal instructions were not necessarily new frameworks of thinking for someone who’s had analytical tasks before.  But like most training, there are tricks of the trade and good mindsets shared</p> <h3>Hints, connection settings, session settings</h3> <p>Both platforms have a framework of database session hints, connection settings, sessions settings.  Most of which you should avoid.  While the Einsteins (<a href="http://www.lhotka.net/weblog/MoreOnMortAndTheFutureOfLanguages.aspx">ref: Lhotka</a>) may point out that you can get such and such a performance increase or less code, etc.  They come at great cost.  #1 the difficulty the next person is going to have figuring out what the heck you were trying to do.  #2 entropy: the characteristics of the database will change and/or product upgrades will obviate the need for the custom configuration or actually make it so its harmful.  Every time I hear about such thing, i think, what are the odds that I can outthink the engineers who designed either of these database platforms.  </p> <p>I will concede one point that was made.  That if the hint or setting provides information that wasn’t previously available to the database platform.  e.g. cardinality on a temporary table which can’t be known at design time. </p> <h3>It’s the application, stupid!</h3> <p>Other than obvious database perf issues, it’s usually going to be application design issues that drive performance.  Either the application is querying too much data for what it needs, querying the same data repetivily, having long-running applications on the user thread.  While databases will continue to evolve and can be made to perform better and better for cheaper and cheaper, the application design owns the user experience.</p> <h2>What’s different between Oracle and SQL Server Performance Tuning?</h2> <p>Some will read that question and say ‘everything’.  I’m going to be contrarian and say at the high level, not much.  The steps and tools are obviously different.  But you still view an execution plan for a given query (either estimated or actual), or use a profiler tool to analyze waits</p> <h3>Tooling</h3> <p>The tooling for Oracle is a lot less mature than SQL Server.  Maybe not a lot less, but much less accessible.  In SQL Server, through the Management Studio (SSMS), for a query, you can view the estimated execution plan (before actually running the query) or the actual execution plan (after running the query).  In Oracle, the provided tools – SQL Developer does have a way to the view the estimated plan.  To view the actual plan, one has to set a session variable to ‘trace’, browse to the file system of the server (!), copy down a binary file, pass it to an executable called TKProf, then view the output as a text file.  I will say although the SQL Server Management Studio’s graphical view of the execution plan is easier to navigate, the outline view of the execution plan on the Oracle side does have its charms.</p> <p>In fairness, you might consider Oracle’s tracing/TK prof workflow more similar to SQL Server Profiler and now Extended Events as it includes wait stats and things of this nature.  So then you’re back to this being somewhat limited to sysadmins vs. developers.  </p> <p>But to the point of it being less accessible, graning permission to n number of developers to access the file system of a database server is odd or possibly untenable.  Luckily for the class I was in, one of our very sharp Oracle DBAs came up with a workaround to expose the default OS trace file location of one of our test servers over HTTP using a simple web server.  Otherwise, the 3 day class would have been spent looking at PowerPoint.  It would seem that third-party tools such as TOAD would provide this capability, but was unable to confirm that with some regular TOAD users</p> <table cellspacing="0" cellpadding="2" width="648" border="0"><tbody> <tr> <td valign="top" width="122"> </td> <td valign="top" width="106">SQL Server </td> <td valign="top" width="434">Oracle</td> </tr> <tr> <td valign="top" width="122">Estimated Execution Plan</td> <td valign="top" width="106">Click the ‘Display Estimated Execution Plan’ button in the toolbar <br /><a href="https://lh3.googleusercontent.com/-CyyXO5qQHJk/V2OqIpy3krI/AAAAAAAABhc/OXIhTtvhXAU/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 5px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-Et2E72zpJcs/V2OqJE7vNOI/AAAAAAAABhk/XGF52-YFBs8/image_thumb%25255B1%25255D.png?imgmax=800" width="402" height="213" /></a></td> <td valign="top" width="434">Click the Explain Plan button in the toolbar<a href="https://lh3.googleusercontent.com/-E05F25N2LxY/V2OqJnvViuI/AAAAAAAABhs/7lZqyIbq8HU/s1600-h/image%25255B7%25255D.png"> <br /><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 5px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-W7OCK8TRBgQ/V2OqKCgGSHI/AAAAAAAABh0/hqdoXT2sy3A/image_thumb%25255B3%25255D.png?imgmax=800" width="172" height="289" /></a> <br /><a title="https://markhoxey.wordpress.com/2012/07/10/obtaining-an-execution-plan/" href="https://markhoxey.wordpress.com/2012/07/10/obtaining-an-execution-plan/">https://markhoxey.wordpress.com/2012/07/10/obtaining-an-execution-plan/</a></td> </tr> <tr> <td valign="top" width="122">Actual Execution Plan</td> <td valign="top" width="106">Click the ‘Include Actual Execution Plan’ button in the toolbar <br /><a href="https://lh3.googleusercontent.com/-DHr0JG_-nC8/V2OqKQJjjsI/AAAAAAAABh8/uClRAoX3thU/s1600-h/image%25255B12%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 5px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-P7pzyosOJ58/V2OqKya1G-I/AAAAAAAABiE/mSjV48J45cY/image_thumb%25255B6%25255D.png?imgmax=800" width="367" height="334" /></a></td> <td valign="top" width="434">Execute the SQL commands to setup a trace file for yourself <br /><font face="Courier New">alter session set tracefile_identifier='MyTrace';</font> <br /> <br />Turn on tracing <br /> <p><font face="Courier New">alter session set sql_trace=true; <br />alter session set events '10046 trace name context forever, level 12' ;</font></p> <p>Execute your SQL</p> <p>Turn-off tracing</p> <p><font face="Courier New">alter session set events '10046 trace name context off'; <br />alter session set sql_trace=false;</font></p> <p><font face="Courier New">?Find the file on your servers files system</font></p> <p><font face="Courier New">Copy to your hard drive</font></p> <p><font face="Courier New">Run the command TKPROF input.trc output.txt</font></p> <p><a href="https://lh3.googleusercontent.com/-uER5hMKVzBY/V2OqLZt8ScI/AAAAAAAABiM/zNPt-nKTr2w/s1600-h/image%25255B16%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 5px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-zNtmSDehDIM/V2OqLht4yLI/AAAAAAAABiU/0yTfiaYRBE0/image_thumb%25255B8%25255D.png?imgmax=800" width="353" height="360" /></a></p> </td> </tr> </tbody></table> <p>So while the steps and individual commands are different, the concepts of Performance Tuning and Optimization in both the SQL Server and Oracle platform are very similar.  If you can find third party tools or open source tools to get around the rough edges of Oracle’s trace file access, developers should find their optimization skills translate well across products.</p>Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-90451747939557780532016-06-16T23:30:00.002-07:002016-06-16T23:30:55.638-07:00Database Architect - what does it all mean?It's interesting to have such varied work and challenges in front of me in my work for the California Department of Corrections and Rehabilitation (CDCR). My title is Database Architect, which could mean a lot of things and often does. I looked back at the week and just noted the variation of tasks, in topic, depth, level of hands-on involvement, hard vs. soft skills, everything. Blessed to get to work with and around really talented and hard-working people that keep the machine going.<br />
<br />
Some of my week looked like:<br />
<br />
Received training on Oracle Performance tuning<br />
Conducted training on SQL Server Integration Services, including helping someone get the brand new version SQL Server Data Tools installed.<br />
Helping a group migrate data and customize into a SharePoint list<br />
Trained on SQL Server Reporting Services Report Builder<br />
Helped a developer troubleshoot Web Services over a proxy<br />
Various network architecture plans and database questions<br />
<br />
And luckily, since many people take vacation in summer:<br />
<br />
Covering for our Infrastructure Architect, helping (mostly just facilitating) the build of CDCR's Inmate Network.<br />
<br />
Covering for a database administrator, i've planned and will be performing the organization's first Database backup based on differentials with staged recovery (long way of saying what was expected to take >15 hours should be completed in <5 p=""><br />
Planning portions of a vendor's data center move<br />
<br />
<br />
<br />
<br /></5>Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com3tag:blogger.com,1999:blog-4759726564872047874.post-38556825088889514512016-02-13T13:16:00.001-08:002016-02-13T13:18:11.542-08:00Data Warehouse models–comparing Kimball to Inmon<p>Have recently seen various folks whiteboarding approaches to data warehouse (enterprise data, data stores, ods, etc.) One thing I’m noticing is that they generally fit one or both of the historically accepted approaches, those proffered by Ralph Kimball (and group) and from the writings of Bill Inmon.  </p> <p>Here’s a comparison diagram I have referenced many times over the years.  The diagram, and article, is from 2010, but the original thought dates back to the 1990’s or earlier.</p> <p><img src="https://bennyaustin.files.wordpress.com/2010/05/kimball-vs-inmon4.jpg" /></p> <p><a href="https://bennyaustin.files.wordpress.com/2010/05/kimball-vs-inmon4.jpg">https://bennyaustin.files.wordpress.com/2010/05/kimball-vs-inmon4.jpg</a><u></u><u></u></p> <p><u></u><u></u></p> <p><a href="https://bennyaustin.wordpress.com/2010/05/02/kimball-and-inmon-dw-models/">https://bennyaustin.wordpress.com/2010/05/02/kimball-and-inmon-dw-models/</a><u></u><u></u></p> <p><u></u><u></u></p> <p>Take them for what they’re worth and for the era they come from.  Inmon’s model contains what looks like the current buzzword of ‘data lake’ (though Gartner and others are skeptical of the approach)  In all practicality, the only OLAP cubes developed today against a star schema layer.   Agile tools like PowerPivot and Tableau bypass this somewhat, but still contain a step where one models groupings (i.e. dims) and aggregate (i.e. facts)</p>Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-1834830802278111442016-01-31T22:19:00.000-08:002016-02-13T13:17:28.112-08:00Prep notes for my upcoming talk on 'Partially Contained Databases'<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
Did a prep session
this past Friday for my upcoming talk
(<a href="http://sac.sqlpass.org/">Feb. 3</a>) on '<a href="http://sac.sqlpass.org/">Partially Contained Databases</a>' to the Sacramento SQL Server Users
Group (@SacSQLUG) (Some #spoileralerts
if any the group is reading this before the Feb meeting) Had a few great, gracious current or former
co-workers view the talk and demo over a remote screen sharing session and
provide feedback.<span style="font-size: 11pt;"> </span></div>
<span style="font-family: "calibri"; font-size: 11pt;"></span><br />
<ol><span style="font-family: "calibri"; font-size: 11pt;">
<li><span style="font-size: 11pt;">The feedback from a group of
people with differing experiences with SQL Server was hugely
valuable.</span><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">As a presenter, you are
so close to the topic, you can include things as obvious that you forgot
actually took you quite a few leaps to discover yourself.</span></li>
<li><span style="font-size: 11pt;">The call to action, which I
will add to the slides, is to 'try it out'.</span><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">Try using partial containment for a
simple example like a reporting service account that should have access to
only one database.</span></li>
<li><span style="font-size: 11pt;">This topic is fast becoming a
standard practice to employ for high
availability/disaster recovery scenarios.</span><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">(At this time, I'll
leave it to the viewer's great mental ability to make the connection)</span><span style="font-size: 11pt;"> </span></li>
</span></ol>
<span style="font-family: "calibri"; font-size: 11pt;">
</span><br />
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
Some of the core
feedback (which may only make sense when the final presentation is shared)</div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
</div>
<ul>
<li><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">hard time following the difference between
types of accounts</span></li>
<li><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">too much time spent on the 'intro'; that is
the demonstration of backup/restore in an uncontained database to demo the
problem trying to be solved</span></li>
<li><span style="font-size: 11pt;">appreciated mention of the connection to certification exams</span></li>
<li><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">pre-requisites of DBA knowledge</span><span style="font-size: 11pt;"> </span></li>
</ul>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
My thought process incorporating this feedback:</div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
</div>
<ul>
<li><span style="font-size: 11pt;">Simplified the login and user creation in
the demo.</span><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">Namely, removed the example
using Windows authentication</span></li>
<li><span style="font-size: 11pt;">Removed a time-consuming section on various
permutations of recovering logins where the password is known/not known,
original server is available/unavailable.</span><span style="font-size: 11pt;">
</span><span style="font-size: 11pt;">This content is saved for a future blog post.</span></li>
<li><span style="font-size: 11pt;">Pre-requisites: well, I think it's OK to
make an assumption or two since the talk is aimed to the SQL Server user
group.</span><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">And in any case, that ship has
sailed for this week's talk.</span><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">I may
review the abstract</span><span style="font-size: 11pt;"> </span><span style="font-size: 11pt;">for future use and
make sure it calls out the viewer is familiar with database backup/restore.</span><span style="font-size: 11pt;"> </span></li>
</ul>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
Hurts my heart a bit
to give up the Windows auth and login recovery content, because the content is
a good practice. But the time they eat
up distracts from the two key takeaways:</div>
<span style="font-family: "calibri"; font-size: 11pt;"></span><br />
<ol><span style="font-family: "calibri"; font-size: 11pt;">
<li><span style="font-size: 11pt;">With a partially contained
database, logins can live with the schema of a user database rather than
the system database (that's a clue for the High Availability/Disaster
Recovery connection)</span></li>
<li><span style="font-size: 11pt;">The db_owner and other
database roles have additional power within a user database.</span></li>
</span></ol>
<span style="font-family: "calibri"; font-size: 11pt;">
</span><div>
<span style="font-family: "calibri"; font-size: 11pt;">Fun stuff. and i think if i just script out the sample database creation step, all the scripts could be published to let others run the demo themselves.</span></div>
<span style="font-family: "calibri"; font-size: 11pt;">
</span><br />
<ol style="direction: ltr; font-family: Calibri; font-size: 11pt; margin-bottom: 0in; margin-left: 0.375in; margin-top: 0in; unicode-bidi: embed;" type="1">
</ol>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-23139077669315084092016-01-29T23:34:00.000-08:002016-02-13T13:17:49.887-08:00Musings on Data Warehouse projects- dealing with data sources of varying quality<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
Had a discussion
this past week with some great colleagues re: the quality of the database
design of a potential source system (or lack thereof, depending on point of
view) I've racked my brain trying to
find an article that provided some caution in dealing with this. Almost sure it came from either a book or
article from the Kimball Group. I will
paraphrase it:</div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
<br /></div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
From
time to time, a data source will appear to warrant improvement of some
kind. The data source provider may be all for it. Couldn't that
spreadsheet be turned into a proper system? </div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
Avoid
such efforts. Your team will be constantly distracted by work of a very
different cadence than that which data warehousing requires.</div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
<br /></div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
The closest I could
find was a snippet in “<a href="http://www.amazon.com/gp/product/0470640383/ref=as_li_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=0470640383&linkCode=as2&tag=re00b9-20&linkId=4SVFIFCNPVYNITXZ">The Microsoft Data Warehouse Toolkit</a>”, from the chapter
on <a href="http://%28http/codeidol.com/community/sql/business-requirements-example-adventure-works-cycl/2557/)">Business
Requirements Example: Adventure Works Cycles</a> (emphasis mine)</div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
<br /></div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
"….price
lists and international support are important issues to his organization, but
they are transaction system problems because they involve enhancing transaction
systems or building new IT infrastructure. <span style="font-style: italic;">You
can help with better reporting, but you shouldn't be dealing with connectivity
and data capture issues if you can avoid it.</span> "</div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
<br /></div>
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
I have taken this
viewpoint into many battles over scope of a data warehouse project… I have not won all of those battles ;) Nonetheless, it’s a positive outcome to have
this caution incorporated into your mindset when evaluating sources. When possible move the system development
'out' to the proper parties as quickly as possible.</div>
<br />
<div style="font-family: "calibri"; font-size: 11pt; margin: 0in;">
<br /></div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com1tag:blogger.com,1999:blog-4759726564872047874.post-42899658074358544192015-10-28T17:22:00.002-07:002016-02-13T13:18:22.320-08:00Oracle's SQL Developer (11gR2) Date display issue<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Oracle’s SQL
Developer (as shipped with 11gR2) has a <span style="text-decoration: line-through;">bug</span>
feature that by default, causes only the date portion of datetime columns to
show in the displayed query results.</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://www.zen-workshop.com/blog/v2/learning/default-date-field-display-format-in-oracle-sql-developer/">http://www.zen-workshop.com/blog/v2/learning/default-date-field-display-format-in-oracle-sql-developer/</a></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="direction: ltr;">
<br />
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-color: #A3A3A3; border-style: solid; border-width: 1pt; direction: ltr;" valign="top">
<tbody>
<tr>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 4.5423in;"><div class="" style="clear: both; text-align: center;">
<span style="font-family: Calibri; font-size: 14.6667px; text-align: start;">Here’s the default</span></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
</td>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 4.4979in;"><div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
And here it is set
to the ISO 8601-style date</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
YYYY-MM-DD
HH24:MI:SS</div>
</td>
</tr>
<tr>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 4.5423in;"><div style="margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJ0sUlDgA-ED0PV9T21wfDNTn1IVTKSmc9saziD-Dpqali15lcBq-VrW8tutDcgk1t_n-ksmjEnfk2XuQxAkKRiOHlBRSQYvFkQLf0DoE8i66aHhQRdbLNt-Y0HFgphtOH74bvQIGAeHU/s1600/clip_image001.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="229" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJ0sUlDgA-ED0PV9T21wfDNTn1IVTKSmc9saziD-Dpqali15lcBq-VrW8tutDcgk1t_n-ksmjEnfk2XuQxAkKRiOHlBRSQYvFkQLf0DoE8i66aHhQRdbLNt-Y0HFgphtOH74bvQIGAeHU/s320/clip_image001.jpg" width="320" /></a>
</div>
</td>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 4.4979in;"><div style="margin: 0in;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRXLyezEeuXUeqpF-SmL63UFLDkmDurdnUOk-f80uEv7r-98ymlFxrkWBriy9y-BA6TLhfp-eqHilINLw2wUxypvbYLW2oW1_hwrtBErA3JuaeETAK4nzErmkl_8slgr7FiCZwFm6OLVY/s1600/clip_image002.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRXLyezEeuXUeqpF-SmL63UFLDkmDurdnUOk-f80uEv7r-98ymlFxrkWBriy9y-BA6TLhfp-eqHilINLw2wUxypvbYLW2oW1_hwrtBErA3JuaeETAK4nzErmkl_8slgr7FiCZwFm6OLVY/s320/clip_image002.jpg" width="320" /></a></div>
</div>
</td>
</tr>
</tbody></table>
</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br />
Good luck!</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<br />
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-17961048930545655022015-06-19T19:49:00.000-07:002016-02-13T13:18:41.576-08:00Generate a Remote Desktop Connection Manager config file from SQL Server Central Management Server<div dir="ltr">
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
If you are a SQL Server database administrator (DBA) who also has need to administer servers via Remote Desktop, this script will be of tremendous help to you at least once, and may be something you run regularly.</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
<br /></div>
<div style="font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
<span style="color: black;">This script will query your SQL Server Central Management Server (not using it yet? - check out </span><a href="http://www.mssqltips.com/sql-server-video/98/easily-manage-your-sql-servers-with-cms-and-pbm/">Easily Manage your SQL Server with CMS and PBM Webcast</a><span style="color: black;">) and generate the contents of a a config file for </span><a href="https://www.microsoft.com/en-us/download/details.aspx?id=44989">Microsoft's Remote Desktop Connection Manager</a><span style="color: black;">. All free tools or built into your SQL Servers.</span></div>
<div style="font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
<span style="color: black;"><br /></span></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
Get the script at:</div>
<div style="margin: 0in;">
<span style="color: black; font-family: Segoe UI;"><a href="https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Central-ff90a417">SQL Server Central Management Server - Generate a Remote Desktop Manager config</a></span></div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
<br /></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
<span style="font-weight: bold;">Prerequisites:</span></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
Install Remote Desktop Connection Manager (RDCMan) version 2.7 from <a href="https://www.microsoft.com/en-us/download/details.aspx?id=44989">https://www.microsoft.com/en-us/download/details.aspx?id=44989</a></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
Have populated a SQL Server Central Management Server (CMS) with at least one group and server</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
<br /></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
To use, run the script against the instance holding your CMS data.</div>
<div style="margin: 0in;">
</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLBHtE3axPoCv15wqcHrkNIRarYVGVNJ07q-gw_1VW7R7ImGIX0z8oaSctnIMY7L4XwzDH7Hk_PKxUVsPJ32_5sPIt5YvdWL8q13mDQi5tuYPqcgdzcBCjGjJ6syP_Vd0hL4m6M7LffkU/s1600/image-716902.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_6162283861807833106" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLBHtE3axPoCv15wqcHrkNIRarYVGVNJ07q-gw_1VW7R7ImGIX0z8oaSctnIMY7L4XwzDH7Hk_PKxUVsPJ32_5sPIt5YvdWL8q13mDQi5tuYPqcgdzcBCjGjJ6syP_Vd0hL4m6M7LffkU/s320/image-716902.png" /></a></div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
Click on the link to the XML. Select All and Copy:</div>
<div style="margin: 0in;">
</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjd0kCnhsvZrbVOxgDUzt-c4emzKrWMBv3EoBmY20EU-QL_RW74aFm5sTFF2Nwjs0IbpXNFLV6aSvRryKWCDEdb78xPg2j7VhYZvExIzYcTxIV5vFVWpTL2HwUPKatz1wAL8vJOVlrLoaA/s1600/image-719106.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_6162283867208382162" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjd0kCnhsvZrbVOxgDUzt-c4emzKrWMBv3EoBmY20EU-QL_RW74aFm5sTFF2Nwjs0IbpXNFLV6aSvRryKWCDEdb78xPg2j7VhYZvExIzYcTxIV5vFVWpTL2HwUPKatz1wAL8vJOVlrLoaA/s320/image-719106.png" /></a></div>
<div style="margin: 0in;">
<span style="font-family: Calibri; font-size: 11pt;">Create a new text file</span><span style="font-family: 'Segoe UI'; font-size: 9.75pt;"> with the extension *.rdg (for example MyCMSServers.rdg) Paste the XML content:</span></div>
<div style="margin: 0in;">
</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhT5S8-6cQ7ie7ELtaFQ5SwvXhKwNk2L-oVuuSAZBJH4HNgcjeDktG6hVIW7c6gFMcrqzg9tS7bYN97Y8MnHeqwNzZT7s6mLmNTtROlnAD6YBqJq-DeiVV098FmMfY-ImcLtxXfvI-4gDc/s1600/image-720890.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_6162283875982508674" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhT5S8-6cQ7ie7ELtaFQ5SwvXhKwNk2L-oVuuSAZBJH4HNgcjeDktG6hVIW7c6gFMcrqzg9tS7bYN97Y8MnHeqwNzZT7s6mLmNTtROlnAD6YBqJq-DeiVV098FmMfY-ImcLtxXfvI-4gDc/s320/image-720890.png" /></a></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
In Remote Desktop Connection Manager, File->Open the *.rdg file</div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXqJUSEMkz3SFrEoJq2Eo8vk2pr5TR9eqEwF9KTM5G4eJ5L3ivsWuZK26bKSFdb9nhiU0OeddW43A5cRVSUMTW2TyPXoALWWeemsV-LT39yGVpwjrUQ1ptQfq4imJ4ociWA4CGZXOOmwY/s1600/image-723373.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_6162283884720903138" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXqJUSEMkz3SFrEoJq2Eo8vk2pr5TR9eqEwF9KTM5G4eJ5L3ivsWuZK26bKSFdb9nhiU0OeddW43A5cRVSUMTW2TyPXoALWWeemsV-LT39yGVpwjrUQ1ptQfq4imJ4ociWA4CGZXOOmwY/s320/image-723373.png" /></a></div>
<div style="margin: 0in;">
</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
<br /></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
<span style="font-weight: bold;">Details:</span></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
All unique host names are placed under a group called '_All Servers'. The 'Smart Lists' dynamically filter based on the groups in your SQL CMS. This is done by placing a comment in the RDCMan node for each server. </div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
<br /></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
The script doesn't yet generate hierarchical groups in RDCMan. Keep an eye out for edge cases that I haven't tested yet: special characters in the server or group names, hosts that are found in more than one group, etc.</div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
<br /></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
Get the script at:</div>
<div style="margin: 0in;">
<span style="color: black; font-family: Segoe UI;"><a href="https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Central-ff90a417">SQL Server Central Management Server - Generate a Remote Desktop Manager config</a></span></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
<br /></div>
<div style="color: black; font-family: 'Segoe UI'; font-size: 9.75pt; margin: 0in;">
Hope you enjoy!</div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com1tag:blogger.com,1999:blog-4759726564872047874.post-34881986557135415882014-05-28T12:50:00.000-07:002016-02-13T13:18:41.583-08:00An approach to SQL Server Rough Tuning<div class="WordSection1">
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Looking for a way to address SQL Server database performance in a production, virtualized environment? There are many sources of expert advice from very smart people in the SQL Server world. But often the most well-thought, well-intentioned advice is not easily or quickly implemented in a complex organization. The reasons could be technical, political or simply availability of time and people. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">This diagram is my current approach to "rough" tuning a SQL Server: The idea that a server administrator or database administrator (DBA) can turn various knobs and flip switches to assigned scarce resources to a database server, but as a practical matter, the inner workings of a given application and database may not be changed… or at least not changed quickly. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin: 0in; text-align: center;">
<a href="https://onedrive.live.com/view.aspx?cid=F5FF1D7E2EB587F8&resid=F5FF1D7E2EB587F8%2130255&app=WordPdf"><span style="color: blue; text-decoration: none;"></span></a><span style="color: blue; text-decoration: none;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbVMrUz7M0nLgXHdChrX9dvVz9z9k2W_-_gJli228jtdL7ClhmQ8bZJL2Lu70Vgy6JziAQXDk-5F1Z4eg6Mi1wQcUOWNPlN65g_r9VgEVeOLPcvNlCQfBkrQajgQ4rP90orvg4nh56j3E/s1600/image003-718114.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbVMrUz7M0nLgXHdChrX9dvVz9z9k2W_-_gJli228jtdL7ClhmQ8bZJL2Lu70Vgy6JziAQXDk-5F1Z4eg6Mi1wQcUOWNPlN65g_r9VgEVeOLPcvNlCQfBkrQajgQ4rP90orvg4nh56j3E/s320/image003-718114.jpg" height="640" id="BLOGGER_PHOTO_ID_6018565667870782386" width="497" /></a></span><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Example situations:<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l0 level1 lfo1; text-indent: -.25in; vertical-align: middle;">
</div>
<ul>
<li><span style="text-indent: -0.25in;">A third party vendor application with a proprietary schema, the application might have updates, but there are dependencies or license costs that take time.</span></li>
<li><span style="text-indent: -0.25in;">An internal application has received an influx of new activity, but the development team is fully off on another high-priority project</span></li>
<li><span style="text-indent: -0.25in;">A legacy application with original developers long gone; no known test environment to experiment</span></li>
</ul>
<br />
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l0 level1 lfo1; text-indent: -.25in; vertical-align: middle;">
<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l1 level1 lfo2; text-indent: -.25in; vertical-align: middle;">
<o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 27.0pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Method<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 27.0pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Took some best practices, including some selections from the guidance on the <a href="http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf"> SQL Server perfmon counters of interest poster</a> available from Dell/Quest, and added some of the basic steps available to Server administrators and Database Administrators.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 27.0pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 27.0pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Document available as a <a href="https://onedrive.live.com/view.aspx?cid=F5FF1D7E2EB587F8&resid=F5FF1D7E2EB587F8%2130255&app=WordPdf"> PDF</a><o:p></o:p></span></div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-19830021760581794732014-03-24T12:49:00.002-07:002016-02-13T13:18:41.580-08:00SQL Server Data Tools - An Installation Adventure<div class="WordSection1">
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">With SQL Server 2012, your old friend "Business Intelligence Developer Studio" or "BIDS" has been replaced by a component called "SQL Server Data Tools". SQL Server Data Tools, or SSDT is the primary authoring environment for SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS) projects. In addition, a new way of working with SQL Server Databases is available. The new method treats a SQL Server database as a Visual Studio project, enabling adaption of developer concepts like version control and deployment. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;"><br /></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">While SSDT is a very innovative product, the steps to arrive at </span><span style="font-family: Calibri, sans-serif; font-size: 11pt;">a working installation of SSDT can be a bit of an adventure. If you have installed SQL Server 2012 from installation media, selecting all features, or at least selected all client tools, your adventure begins here. </span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">An entry for SQL Server Data Tools will appear in your Start menu, under the folder for "Microsoft SQL Server 2012"<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcZWwRDktNon5iWqOx_BUkvjI-n3pdQ5YvWP0_JG8DozsIBKWWp0RRAq90fz28XgAF2tj3rfzE5NeqXWuFfBY0dhmM7CU0ye83kWa7PXvUJVDAHF59Bsx8hiGd1Tp8knNu20ZQvpdIXhE/s1600/image001-782318.png"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcZWwRDktNon5iWqOx_BUkvjI-n3pdQ5YvWP0_JG8DozsIBKWWp0RRAq90fz28XgAF2tj3rfzE5NeqXWuFfBY0dhmM7CU0ye83kWa7PXvUJVDAHF59Bsx8hiGd1Tp8knNu20ZQvpdIXhE/s320/image001-782318.png" id="BLOGGER_PHOTO_ID_5993405164108298914" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Click on SQL Server Data Tools and you will soon see that the "Microsoft Visual Studio 2010 Shell" is starting up.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhusvczh7PxQ-fTIAttN544y84MjEKfFmXZv7nUs1oixbRra8izTj-6AzI2OZAlN2ir4BKJTiiC9e-VRILRraqUsuETitiFGgAG4oNL7yHxLDZuRgoZUXtTsJ2lbBPPClsqgn2zDVnXlX4/s1600/image035-784857.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhusvczh7PxQ-fTIAttN544y84MjEKfFmXZv7nUs1oixbRra8izTj-6AzI2OZAlN2ir4BKJTiiC9e-VRILRraqUsuETitiFGgAG4oNL7yHxLDZuRgoZUXtTsJ2lbBPPClsqgn2zDVnXlX4/s320/image035-784857.jpg" id="BLOGGER_PHOTO_ID_5993405174162839602" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">You will be promoted to select environment settings. If you used SQL Server 200X's Business Intelligence Developer Studio or plan to focus this machine on SQL Server Business Intelligence projects, select "Business Intelligence Settings". Click 'Start Visual Studio'.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiu21lfh4DgkmiWzurN7jkn1OTrRdFEP99vGuGsJ8D1b_-ClHqhfMMInaOyh6XaDIXVeCQmUSK3tvXuOei4xRP14GvFiyHttGWZ0yKvPfCGHqwqXpwAUQ8HSC5gJFmw3kuF-op7RFISvBY/s1600/image036-787956.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiu21lfh4DgkmiWzurN7jkn1OTrRdFEP99vGuGsJ8D1b_-ClHqhfMMInaOyh6XaDIXVeCQmUSK3tvXuOei4xRP14GvFiyHttGWZ0yKvPfCGHqwqXpwAUQ8HSC5gJFmw3kuF-op7RFISvBY/s320/image036-787956.jpg" id="BLOGGER_PHOTO_ID_5993405189814012818" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">After a potentially brief pause, and this loading screen...<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgs-wbIAA9Y3jMB3NsRmNdCpLJLaJ9SV9YutK8u6AQPS_1CzWcbaBKuKgIBdfXnC0n7GhX6S8Pr_z445osy9LHxpff0uBJEuZO8zYzkCnI4ujRiA1JjGuGtTrJ90ouINMXgfj6O6YDpMY/s1600/image006-790323.png"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgs-wbIAA9Y3jMB3NsRmNdCpLJLaJ9SV9YutK8u6AQPS_1CzWcbaBKuKgIBdfXnC0n7GhX6S8Pr_z445osy9LHxpff0uBJEuZO8zYzkCnI4ujRiA1JjGuGtTrJ90ouINMXgfj6O6YDpMY/s320/image006-790323.png" id="BLOGGER_PHOTO_ID_5993405198503717714" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">… and you are brought into the 'Microsoft Visual Studio 2010 Shell"<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhjVE9zPvxwG_XnYXVErWjZ7VGGxoT1XkQvnyG8MB1_gWgGiyFUN6VoyQEwjzVx5J7NduEggn0pkWWzFZDtMdHkLRrcQqASwn7hRJbtdQKjwAiHYopzJ3DAO_XJzad4ABhLA0opj1LMuI/s1600/image037-792696.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhjVE9zPvxwG_XnYXVErWjZ7VGGxoT1XkQvnyG8MB1_gWgGiyFUN6VoyQEwjzVx5J7NduEggn0pkWWzFZDtMdHkLRrcQqASwn7hRJbtdQKjwAiHYopzJ3DAO_XJzad4ABhLA0opj1LMuI/s320/image037-792696.jpg" id="BLOGGER_PHOTO_ID_5993405208417104546" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Click on New Project…<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhb2vcDilHQVpXvNNgBJLDRgEW11iSWYbNWdl8D_ivpwj0RkZzIs-rzPAQySXyli8b0I-5ASlRprkRo8sJFHr9emb0F_2HoQid_f81oNOIrbfifgMGnyA0JuJmsDI57BLPStMVuKm0rFp4/s1600/image038-795286.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhb2vcDilHQVpXvNNgBJLDRgEW11iSWYbNWdl8D_ivpwj0RkZzIs-rzPAQySXyli8b0I-5ASlRprkRo8sJFHr9emb0F_2HoQid_f81oNOIrbfifgMGnyA0JuJmsDI57BLPStMVuKm0rFp4/s320/image038-795286.jpg" id="BLOGGER_PHOTO_ID_5993405223394359906" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Now you can actually create SSAS, SSIS, or SSRS projects at this point. You are good to go as far as that feature is concerned. Then check out the new option "SQL Server"…<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2DJR0AB1iQhmedW4J1EAZKUoKg6K9IIByRMGSgf4wh47FFb3CvYWQ1GJUG9gsGsVhlwZe1Kgl1TxQh6gai6AOnCY9AZ3-IvjonZZ1QeWrD3kuMxNVWieq2jRtc2-VgMpCFkSgig5neGU/s1600/image039-702125.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2DJR0AB1iQhmedW4J1EAZKUoKg6K9IIByRMGSgf4wh47FFb3CvYWQ1GJUG9gsGsVhlwZe1Kgl1TxQh6gai6AOnCY9AZ3-IvjonZZ1QeWrD3kuMxNVWieq2jRtc2-VgMpCFkSgig5neGU/s320/image039-702125.jpg" id="BLOGGER_PHOTO_ID_5993405252406378386" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Note the text "Provides instructions for installing Microsoft SQL Server Data Tools from the web". Clicking this link presents this message...<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoQOcEiwY_8lg6LoSmkElpa4oHUatUFMBgjKjugHFsCxabwqg1WZZj2EldmXv2u6Cs6DWjAHgBbSwry67iUzaSg61xxCJmWyIKa4gpMMLHSkV1GvOzlO7LoVDB1C4lRlu5gpih9HwfDtM/s1600/image040-704486.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoQOcEiwY_8lg6LoSmkElpa4oHUatUFMBgjKjugHFsCxabwqg1WZZj2EldmXv2u6Cs6DWjAHgBbSwry67iUzaSg61xxCJmWyIKa4gpMMLHSkV1GvOzlO7LoVDB1C4lRlu5gpih9HwfDtM/s320/image040-704486.jpg" id="BLOGGER_PHOTO_ID_5993405259375704034" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Clicking "Install" opens this web page...<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPQ2hYKtd7tS2NOXBxHOzguAKkoIbNwD8gkFuBt1pX0FIsMpgIWU958a29vQg2zDB59bBMB5gpCZ9v3ouCwWkIN-sLVwBa3nC2GYWUwVNIhweL-TIH33EadAdYZ4UNsxqxxzRftIp5QL4/s1600/image041-707183.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPQ2hYKtd7tS2NOXBxHOzguAKkoIbNwD8gkFuBt1pX0FIsMpgIWU958a29vQg2zDB59bBMB5gpCZ9v3ouCwWkIN-sLVwBa3nC2GYWUwVNIhweL-TIH33EadAdYZ4UNsxqxxzRftIp5QL4/s320/image041-707183.jpg" id="BLOGGER_PHOTO_ID_5993405274452993570" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">If you click on 'Download SQL Server Data Tools', as of the time of this writing, you're taken to a page about downloading Visual Studio 2013 trial editions. I went this route on another machine with Visual Studio 2013 installed, so far, haven't quite figured out how to get SSDT enabled, so...<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0DscZZuTmJyN9rleyHASkTOccgpGVwxf9CSRnQ9XKMFX7LvLeCvkHj8NlnaL6BgW54aaDZBujVO_8B72Ga_ti-wpEb2rN0-IYjlT9eUOAxueBwalugVRN9k8SfYvpMS-kqmAecaRCX80/s1600/image042-710221.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0DscZZuTmJyN9rleyHASkTOccgpGVwxf9CSRnQ9XKMFX7LvLeCvkHj8NlnaL6BgW54aaDZBujVO_8B72Ga_ti-wpEb2rN0-IYjlT9eUOAxueBwalugVRN9k8SfYvpMS-kqmAecaRCX80/s320/image042-710221.jpg" id="BLOGGER_PHOTO_ID_5993405285654856722" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Click back…<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">And click SSDT Visual Studio 2012<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBzn-YQjTJsbxDrVd6XayuVXLxzQbew_YwdZOMvNzulNVP9LUKgKQwVNXKyViro-gvjywhrIaFeKrgXq2fi9FYPqiEfeJnIgDkhbIvI9DqIyDPMoNpjW9S8by64fE1rRNqrOJvK1IFPcY/s1600/image043-711991.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBzn-YQjTJsbxDrVd6XayuVXLxzQbew_YwdZOMvNzulNVP9LUKgKQwVNXKyViro-gvjywhrIaFeKrgXq2fi9FYPqiEfeJnIgDkhbIvI9DqIyDPMoNpjW9S8by64fE1rRNqrOJvK1IFPcY/s320/image043-711991.jpg" id="BLOGGER_PHOTO_ID_5993405293106029170" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">On the next page, scroll about halfway down to step 2 and click 'Download SQL Server Data Tools for Visual Studio 2012'<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwmUNQRLsQf6ZsPDCY14vi5_BbRllh7CQxkeUMZU20yyRDbXafQxqw53XB_Cnd945Ph0yC45nGeSyHChJevgDQuLNgMtI7oLVDnJMsRNr1nMEA4AJsPGFgdL5qmoRZs6M5ffaECc7JJ-g/s1600/image044-714361.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwmUNQRLsQf6ZsPDCY14vi5_BbRllh7CQxkeUMZU20yyRDbXafQxqw53XB_Cnd945Ph0yC45nGeSyHChJevgDQuLNgMtI7oLVDnJMsRNr1nMEA4AJsPGFgdL5qmoRZs6M5ffaECc7JJ-g/s320/image044-714361.jpg" id="BLOGGER_PHOTO_ID_5993405305079634690" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">For a single installation, go ahead and click 'Run'. (To save the file for use on other workstations, click 'Save')<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUcaUeulC_rKmFrsQD6886-F1hTytNdb5Bwx9lsDovN8PIIVZDxtWUn_qAVoSow89_MfJ3YZnVF3iKaUHbGLAkn-rFRKN6tKB2pspOoj_UMOSHdoGsOtLIYZi0NbS48xN8U6mG3KAn5J0/s1600/image045-716042.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUcaUeulC_rKmFrsQD6886-F1hTytNdb5Bwx9lsDovN8PIIVZDxtWUn_qAVoSow89_MfJ3YZnVF3iKaUHbGLAkn-rFRKN6tKB2pspOoj_UMOSHdoGsOtLIYZi0NbS48xN8U6mG3KAn5J0/s320/image045-716042.jpg" id="BLOGGER_PHOTO_ID_5993405313039057474" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Then thoroughly read the License Terms, and if they are amenable, click 'I agree…' and 'Install'<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-hxUDj6TuaKfTYBwL2JUgrIJMxnU2k1c6WSHeiAEqgj03EILaxLQUoBXYGpMF1QGgd2jQC306cxScdnGPWaYYNYLSUeLEFyTHi0Oq8SrDv7KcS3VIdYTX2iqpKWoUpDwbJ6ruTMgo3YE/s1600/image046-718498.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-hxUDj6TuaKfTYBwL2JUgrIJMxnU2k1c6WSHeiAEqgj03EILaxLQUoBXYGpMF1QGgd2jQC306cxScdnGPWaYYNYLSUeLEFyTHi0Oq8SrDv7KcS3VIdYTX2iqpKWoUpDwbJ6ruTMgo3YE/s320/image046-718498.jpg" id="BLOGGER_PHOTO_ID_5993405318721772914" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">The Microsoft .NET Framework 4.5. Your environment may vary.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBb7ZhWjSJqP4zjUUfCU6BS4ESTZ4rtRT3mhNVDtnZrSiMYYSvASd7F5dCXQhUNpguhXbhsXvuUti5v_jzyt1pS9Ras3Y-BgquY0nItkUdkGNXcWpL8LPMgBbSg2De4xX51OSyMLlcI9o/s1600/image047-722016.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBb7ZhWjSJqP4zjUUfCU6BS4ESTZ4rtRT3mhNVDtnZrSiMYYSvASd7F5dCXQhUNpguhXbhsXvuUti5v_jzyt1pS9Ras3Y-BgquY0nItkUdkGNXcWpL8LPMgBbSg2De4xX51OSyMLlcI9o/s320/image047-722016.jpg" id="BLOGGER_PHOTO_ID_5993405339104209186" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Restart is required if the .NET Framework 4.5 is installed<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">After the restart, Setup will continue<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixMiV3MOCzXMNgy7VToFZ5SMZzQV7vy4DgRBmd1Dfz5k7Cv78ASy10rawWzeHpWNlgteATPlBZhAXbLhnx6ikDBNpQW0mJ16exhIGTVJuNEM-H4i-fXbhURW-tx4iKafcbPvm5Znr4v_c/s1600/image048-724529.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixMiV3MOCzXMNgy7VToFZ5SMZzQV7vy4DgRBmd1Dfz5k7Cv78ASy10rawWzeHpWNlgteATPlBZhAXbLhnx6ikDBNpQW0mJ16exhIGTVJuNEM-H4i-fXbhURW-tx4iKafcbPvm5Znr4v_c/s320/image048-724529.jpg" id="BLOGGER_PHOTO_ID_5993405348083570658" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">After this, the SQL Server Data project will be available under 'SQL Server Data Tools', right? No, to access the SQL Server tools, they're under 'Microsoft Visual Studio 2012'<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqQL0eQ1tMwfKsi2psojaOinJkRbN3fvhgivMkdNZAYbCWqfSJpYqAMzmhX6bVookU8uyAggFl4T1Ow2TLVcoF79KaV1a2W6nklWyA1iXz7Tiv2MlzAVoZcICJ83QJ-oZb3YaFa1WQ_yo/s1600/image049-726668.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqQL0eQ1tMwfKsi2psojaOinJkRbN3fvhgivMkdNZAYbCWqfSJpYqAMzmhX6bVookU8uyAggFl4T1Ow2TLVcoF79KaV1a2W6nklWyA1iXz7Tiv2MlzAVoZcICJ83QJ-oZb3YaFa1WQ_yo/s320/image049-726668.jpg" id="BLOGGER_PHOTO_ID_5993405356369465906" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">The 'SQL' menu with the 'Schema Compare' and 'Data Compare' are now available.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhV__Ciprt9c43E7GxrQTtkNacdqlVqe7WYfzZHnD8I1gCNOhFrSkjDVG6Wxn9LAfW-1z3YVkWtQrtN1HNoEL9Kc6PXgDfH2jLcBPI-9OSzjQ1l6rCLjLYPw1KhZpOmtpxigbE57jYqUgE/s1600/image050-729389.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhV__Ciprt9c43E7GxrQTtkNacdqlVqe7WYfzZHnD8I1gCNOhFrSkjDVG6Wxn9LAfW-1z3YVkWtQrtN1HNoEL9Kc6PXgDfH2jLcBPI-9OSzjQ1l6rCLjLYPw1KhZpOmtpxigbE57jYqUgE/s320/image050-729389.jpg" id="BLOGGER_PHOTO_ID_5993405369680494306" /></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The SQL Server Data Tools look very compelling. As I'm help my organization migrate to SQL Server 2012, the new features will replace some work that currently requires manual processes or intricate scripting. Perhaps the install story will tighten up as SQL Server 2014 is released or the Visual Studio 2013 version becomes a bit more clear to me It is a bit of an adventure to install - nothing difficult, just unclear at some steps the progress towards a working installation. Hopefully, this blog post helps you be more confident when you decide to start using SQL Server Data Tools.</div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-26765926115316968932014-03-21T13:48:00.002-07:002014-03-21T13:48:57.615-07:00IT Project Staffing for Emerging and Legacy applications<br />
I've been catching up in the past week or on the Oregon Health Exchange ("Covered Oregon") issues. This apparently started popping off in November 2013. There was a recent <a href="http://www.govtech.com/health/Oracles-Contracts-for-Cover-Oregon.html">spate</a> of <a href="http://www.govtech.com/health/Oregons-Failed-Health-Exchange-Gets-Major-Personnel-and-Procurement-Overhaul.html">articles </a>on GovTech regarding the oversight.<div>
<br /></div>
<div>
The <a href="http://www.oregon.gov/DAS/docs/co_assessment.pdf">Cover Oregon Website Implementation Assessment by First Data</a> contained an interesting nugget in their recommendations regarding IT Project Staffing:</div>
<div>
<br /></div>
<blockquote class="tr_bq">
<blockquote class="tr_bq">
IT Project Staffing - The exchange project was a large, complex IT project. Complex IT projects introduce an innate resource risk that can only be mitigated through careful staff planning. First Data recommends the State reconsider how IT projects are staffed in the State. The exchange project filled many of its staffing needs using temporary positions, which are difficult to fill due to their lack of employment security. Additionally, qualified staff hired into temporary positions are likely to continue to search for alternate permanent state positions. Consequently, the exchange project regularly struggled to sustain the anticipated project team size and skills. As a result, a large number of staff members were acquired through contracts. Where possible, introducing temporary positions or consultant positions to an organization to backfill or support the systems that will be replaced would naturally align staff attrition with the technology and application lifecycles. Reserving the permanent or long-term positions for the ‘go-forward’ technologies will also provide the state with the capability to develop stronger, more cohesive IT support teams. </blockquote>
</blockquote>
<br />
The opposite tact is common, historically, of course. The emerging project is established with temporary positions or consultants. Existing, permanent staff remain with the legacy application. This very commonly leads to a brain drain as soon as it becomes clear the emerging project will be the new normal. Absent extraordinary efforts to retain those experienced staff - pay, working environment, chances for new projects - they will simply start searching for other employment.<br />
<br />
First Data's recommendation is the opposite. Place the temporary positions with the legacy system immediately, where it will naturally tail down. If the legacy system is needed longer, extending a temporary position is relatively easy, the person in that position may be relieved not to have to start a job search soon. The experienced staff immediately start adding value to the emerging system based on the familiarity with the organization and working environment.Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-19944641358076860402014-02-18T09:19:00.001-08:002014-02-24T23:24:49.023-08:00A five-year Resume (2008-2013) reviewed<div class="WordSection1"> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">A five year resume is a visionary exercise in career planning: Visualize the type of professional you would like to be in five year and write the resume for that person; The education, the skills, the type of positions and projects that person would have on their resume. Suffice to say, compare and contrast with the Five year Plan… presumably a plan starting from today forward 5 years. The five-year resume shows the forward 5 years, allowing you to create a plan to get there.<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""><a href="http://www.careers-advice-online.com/five-year-career-development-plan.html">http://www.careers-advice-online.com/five-year-career-development-plan.html</a><o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""><a href="http://jme.sagepub.com/content/31/1/128.abstract">http://jme.sagepub.com/content/31/1/128.abstract</a><o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">In 2008, I wrote a five-year resume - the time period ending in 2013. This post will grade (those not interested in my navel gazing can stop here, but I'm committed to publishing this to hold myself accountable) My memory and documentation of the process are incomplete, but roughly speaking, to find material for a five year resume , I identified senior leaders in my organization or that I had met through various professional groups. I may have included some biographies of leaders in the business space that I read through periodicals such as Information Week or GovTech. I viewed their resumes, articles, and LinkedIn profiles. Through that, I assembled some of the projects or positions I would need to pursue to have similar experience. I listed the education that overlapped my interest with my model's education. From all that, boiled it down to things that could reasonably be accomplished within 5 years.<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style="border-collapse:collapse;border:none"> <tbody> <tr> <td width="261" valign="top" style="width:196.0pt;border:solid #A3A3A3 1.0pt;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="color:black">Objective</span></b><span style="color:black"><o:p></o:p></span></p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style="border-collapse:collapse;border:none"> <tbody> <tr> <td width="64" valign="top" style="width:48.0pt;border:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black"> <o:p></o:p></span></p> </td> <td width="189" valign="top" style="width:141.9pt;border:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">Results-oriented technical project manager with hands on ability in application development and reporting and experience leading technical teams seeking to take on a large enterprise project<o:p></o:p></span></p> </td> </tr> </tbody> </table> </td> <td width="434" valign="top" style="width:325.45pt;border:solid #A3A3A3 1.0pt;border-left:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfFqy39RQI7sdzq_oPOkv99IsgFuFuuBdDVLF8oxDlLdb_oqUsdlrmcnBQ6OFXJCkXo4zi6Jyc2poil63FiA31uo5PwjqMwDJ6MgVBWF7DPqYJ08S_ZzFCLszhHGDjE85_wveGytdofN4/s1600/image009-758331.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfFqy39RQI7sdzq_oPOkv99IsgFuFuuBdDVLF8oxDlLdb_oqUsdlrmcnBQ6OFXJCkXo4zi6Jyc2poil63FiA31uo5PwjqMwDJ6MgVBWF7DPqYJ08S_ZzFCLszhHGDjE85_wveGytdofN4/s320/image009-758331.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5981789753875400098" /></a><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comments: Basically have done all of those, have the ability to do so on an ongoing basis, and am continuing to do so; What's next?<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> </td> </tr> <tr> <td width="261" valign="top" style="width:196.0pt;border:solid #A3A3A3 1.0pt;border-top:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style="border-collapse:collapse;border:none"> <tbody> <tr> <td width="128" valign="top" style="width:96.35pt;border:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="color:black">Education</span></b><span style="color:black"><o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="font-size:10.0pt;color:black">Further Training </span></b><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black"> Related Courses <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black">Project Management - Advanced<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black">ITIL/ITSM, etc MBA-level<o:p></o:p></span></p> </td> <td width="64" valign="top" style="width:48.0pt;border:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> </td> <td width="69" valign="top" style="width:51.5pt;border:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black"> <o:p></o:p></span></p> </td> </tr> </tbody> </table> </td> <td width="434" valign="top" style="width:325.45pt;border-top:none;border-left:none;border-bottom:solid #A3A3A3 1.0pt;border-right:solid #A3A3A3 1.0pt;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi56ZIaXqIseCAjTOJkCgYplrjyHtMD8lEC7qbHoHRIL-Yk2AP6-z6euVxCDrF_ARiz4zSXGX61ncz3X732UIBPkfpTOItqXPoXNIMcr24DExLII78EJeUKL3DlWlcjqi_3CC055lHZf8s/s1600/image010-760997.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi56ZIaXqIseCAjTOJkCgYplrjyHtMD8lEC7qbHoHRIL-Yk2AP6-z6euVxCDrF_ARiz4zSXGX61ncz3X732UIBPkfpTOItqXPoXNIMcr24DExLII78EJeUKL3DlWlcjqi_3CC055lHZf8s/s320/image010-760997.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5981789766894134210" /></a><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comment: Partial: Earned the PMP in 2008 and am into my second cycle maintaining the credential; have spent next to no time on ITIL or ITSM; Not sure what I was getting at about MBA-level, but didn't <o:p></o:p></span></p> </td> </tr> <tr> <td width="261" valign="top" style="width:196.0pt;border:solid #A3A3A3 1.0pt;border-top:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="color:black">Skills</span></b><span style="color:black"><o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="font-size:10.0pt;color:black">Organizational</span></b><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black">Project management professional<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black">Certified in ITSM/ITIL technical management processes<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black">Participating in and leading groups to accomplish project goals<o:p></o:p></span></p> </td> <td width="434" valign="top" style="width:325.45pt;border-top:none;border-left:none;border-bottom:solid #A3A3A3 1.0pt;border-right:solid #A3A3A3 1.0pt;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi56ZIaXqIseCAjTOJkCgYplrjyHtMD8lEC7qbHoHRIL-Yk2AP6-z6euVxCDrF_ARiz4zSXGX61ncz3X732UIBPkfpTOItqXPoXNIMcr24DExLII78EJeUKL3DlWlcjqi_3CC055lHZf8s/s1600/image010-760997.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi56ZIaXqIseCAjTOJkCgYplrjyHtMD8lEC7qbHoHRIL-Yk2AP6-z6euVxCDrF_ARiz4zSXGX61ncz3X732UIBPkfpTOItqXPoXNIMcr24DExLII78EJeUKL3DlWlcjqi_3CC055lHZf8s/s320/image010-760997.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5981789766894134210" /></a><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comment: Basically accomplished; but for the aforementioned ITSM or ITIL; Earned PMP and lead many project groups<o:p></o:p></span></p> </td> </tr> <tr> <td width="261" valign="top" style="width:196.0pt;border:solid #A3A3A3 1.0pt;border-top:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="font-size:10.0pt;color:black">Communication</span></b><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black">Assess client needs in information systems and offering appropriate solutions<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black">Proactively offering solutions and training to customers of varied skill levels<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black">Delivered training to classes<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <span style="font-size:10.0pt;color:black">Published technical author – blog – magazine articles – etc.<o:p></o:p></span></p> </td> <td width="434" valign="top" style="width:325.45pt;border-top:none;border-left:none;border-bottom:solid #A3A3A3 1.0pt;border-right:solid #A3A3A3 1.0pt;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfFqy39RQI7sdzq_oPOkv99IsgFuFuuBdDVLF8oxDlLdb_oqUsdlrmcnBQ6OFXJCkXo4zi6Jyc2poil63FiA31uo5PwjqMwDJ6MgVBWF7DPqYJ08S_ZzFCLszhHGDjE85_wveGytdofN4/s1600/image009-758331.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfFqy39RQI7sdzq_oPOkv99IsgFuFuuBdDVLF8oxDlLdb_oqUsdlrmcnBQ6OFXJCkXo4zi6Jyc2poil63FiA31uo5PwjqMwDJ6MgVBWF7DPqYJ08S_ZzFCLszhHGDjE85_wveGytdofN4/s320/image009-758331.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5981789753875400098" /></a><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comment: Have pretty muchreached these goals. I now commonly work with clients at various skill levels and authority; Very often I am the technical lead responsible for proposing a solution vs. just working against a requirements. Most of my class room training has been ad hoc or small group; Publishing is the one I'm wobbling on. I am decently active on my blog and social media, but I think I envisioned publishing articles to industry publications or sites<o:p></o:p></span></p> </td> </tr> <tr> <td width="261" valign="top" style="width:196.0pt;border:solid #A3A3A3 1.0pt;border-top:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="font-size:10.0pt;color:black">Technical</span></b><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">Microsoft Certified IT Professional; Microsoft Solutions Framework<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">Architectures – n-tier, web-based<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">Platforms – Business Intelligence platforms. Microsoft (.Net, COM, DCOM, SQL Server); Oracle, PHP/mySQL<o:p></o:p></span></p> </td> <td width="434" valign="top" style="width:325.45pt;border-top:none;border-left:none;border-bottom:solid #A3A3A3 1.0pt;border-right:solid #A3A3A3 1.0pt;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfFqy39RQI7sdzq_oPOkv99IsgFuFuuBdDVLF8oxDlLdb_oqUsdlrmcnBQ6OFXJCkXo4zi6Jyc2poil63FiA31uo5PwjqMwDJ6MgVBWF7DPqYJ08S_ZzFCLszhHGDjE85_wveGytdofN4/s1600/image009-758331.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfFqy39RQI7sdzq_oPOkv99IsgFuFuuBdDVLF8oxDlLdb_oqUsdlrmcnBQ6OFXJCkXo4zi6Jyc2poil63FiA31uo5PwjqMwDJ6MgVBWF7DPqYJ08S_ZzFCLszhHGDjE85_wveGytdofN4/s320/image009-758331.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5981789753875400098" /></a><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comment: Pretty much a slam dunk on these; Achieved and MCTS (the evolution of MCITP), could probably do more; Am comfortable with the architectures and platforms I listed, and even ones I couldn't have envisioned 5 years ago<o:p></o:p></span></p> </td> </tr> <tr> <td width="261" valign="top" style="width:196.0pt;border:solid #A3A3A3 1.0pt;border-top:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="color:black">Work Experience</span></b><span style="color:black"><o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:27.0pt;margin-bottom:.0001pt"> <b><span style="font-size:10.0pt;color:black">Consultant Non-profit, etc. small firm 1/2009 – </span></b><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Board-level consulting on IT strategy, etc.<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Web or application design or selection<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Fund-raising, etc<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> </td> <td width="434" valign="top" style="width:325.45pt;border-top:none;border-left:none;border-bottom:solid #A3A3A3 1.0pt;border-right:solid #A3A3A3 1.0pt;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZVpWMewkmGFTr4F38YV9bfMui83fbS3aYK5UPzW0oCWimgkfTewDKbLpMcWCeOwtQAEulMiqGml1OBZIWYpE1U9K6Pl_1W8iyv2WdzjgSJBTQ5OaZ9rmb0UnHNPSTyC1CuX7qlSgov10/s1600/image011-763391.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZVpWMewkmGFTr4F38YV9bfMui83fbS3aYK5UPzW0oCWimgkfTewDKbLpMcWCeOwtQAEulMiqGml1OBZIWYpE1U9K6Pl_1W8iyv2WdzjgSJBTQ5OaZ9rmb0UnHNPSTyC1CuX7qlSgov10/s320/image011-763391.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5981789772939658530" /></a><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comment: Going to generously give myself a complete on this one; For at least, 3 groups my children are involved in, I have at times been the primary web or technology person; In 2008, I probably envisioned spending more time with professional groups or consulting for other non-profits, however the opportunity to help groups that my sons and other children enjoy so much has been very rewarding<o:p></o:p></span></p> </td> </tr> <tr> <td width="261" valign="top" style="width:196.0pt;border:solid #A3A3A3 1.0pt;border-top:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="font-size:10.0pt;color:black">Project Manager Medium to large organization 1/2009 – </span></b><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Lead Business Intelligence adoption, ideally organization-wide<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• End user component<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Technical project team, and hand-off to operations project team<o:p></o:p></span></p> </td> <td width="434" valign="top" style="width:325.45pt;border-top:none;border-left:none;border-bottom:solid #A3A3A3 1.0pt;border-right:solid #A3A3A3 1.0pt;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZVpWMewkmGFTr4F38YV9bfMui83fbS3aYK5UPzW0oCWimgkfTewDKbLpMcWCeOwtQAEulMiqGml1OBZIWYpE1U9K6Pl_1W8iyv2WdzjgSJBTQ5OaZ9rmb0UnHNPSTyC1CuX7qlSgov10/s1600/image011-763391.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZVpWMewkmGFTr4F38YV9bfMui83fbS3aYK5UPzW0oCWimgkfTewDKbLpMcWCeOwtQAEulMiqGml1OBZIWYpE1U9K6Pl_1W8iyv2WdzjgSJBTQ5OaZ9rmb0UnHNPSTyC1CuX7qlSgov10/s320/image011-763391.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5981789772939658530" /></a><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comment: I can see I hedged my organization with the term 'medium to large organization'. Ended up staying with one large organization - the California Dept. of Corrections - the entire time, starting my 10th year with the organization now. But moving around within the organization has given me a chance to get these kind of opportunities. For this experience, really completed this in the past year as I moved to a new position - database architect - but was deeply involved in handing off the old position - business intelligence to some new team members.<o:p></o:p></span></p> </td> </tr> <tr> <td width="261" valign="top" style="width:196.0pt;border:solid #A3A3A3 1.0pt;border-top:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="font-size:10.0pt;color:black">Project Manager - 2 Medium to large organization 1/2009 – </span></b><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Manage full-blown, department or large unit-wide project from near initation to completion<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Apply PMBOK best practices<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Cross functional teams<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><b><span style="font-size:10.0pt;color:black">Project Manager - 1 Medium to large organization 1/2009 – </span></b><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Manage full-blown, department or large unit-wide project from near initation to completion<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Apply PMBOK best practices<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Cross functional teams<o:p></o:p></span></p> </td> <td width="434" valign="top" style="width:325.45pt;border-top:none;border-left:none;border-bottom:solid #A3A3A3 1.0pt;border-right:solid #A3A3A3 1.0pt;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNdDS9qyYg3zf1hAfQXFnspU86dgOa4amI23V_6qQNs4FFlM3bAiSUC9cjxQ4pC0EjkjFjPTZFSeg_tkxN2_NUYD4wMtraViKH6MCxHBMjfSvCbo88pEMlkwbsdiNYecvkYcuO0jYFrkY/s1600/image012-769920.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNdDS9qyYg3zf1hAfQXFnspU86dgOa4amI23V_6qQNs4FFlM3bAiSUC9cjxQ4pC0EjkjFjPTZFSeg_tkxN2_NUYD4wMtraViKH6MCxHBMjfSvCbo88pEMlkwbsdiNYecvkYcuO0jYFrkY/s320/image012-769920.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5981789802617634146" /></a><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comment: I envisioned managing at least 2 big projects, and although I didn't change organization, I probably ended up managing at least 4-5 in this manner for a single unit - COMPSTAT - of the Dept. of Corrections. Didn't hold the working title of 'Project Manager' at most points, but nonetheless, was doing PMBOK in an appropriately tailored way; Nearly every project involved some type of cross-functional team; usually a mix of technologists and business program areas<o:p></o:p></span></p> </td> </tr> <tr> <td width="261" valign="top" style="width:196.0pt;border:solid #A3A3A3 1.0pt;border-top:none;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">T</span><b><span style="font-size:10.0pt;color:black">echnical Author Publishing House 1/2009 – </span></b><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Author technical articles – blog- article - book<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:10.0pt;color:black">• Companion teaching, courses, speaking<o:p></o:p></span></p> </td> <td width="434" valign="top" style="width:325.45pt;border-top:none;border-left:none;border-bottom:solid #A3A3A3 1.0pt;border-right:solid #A3A3A3 1.0pt;padding:4.0pt 4.0pt 4.0pt 4.0pt"> <p style="margin:0in;margin-bottom:.0001pt"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIg_gw2hr5FEhLoewUAUHCNcDWQW4-culcfT1pSCCYn9r-RDmA43oJvoKeOMVXI0Qw8Djtm2YqwkfZ31COyScGEsNTAjQHyTArwnxG8ysS7VVS8NBriPe-e_dduct9OiF2zIKQofGjo1g/s1600/image013-776681.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIg_gw2hr5FEhLoewUAUHCNcDWQW4-culcfT1pSCCYn9r-RDmA43oJvoKeOMVXI0Qw8Djtm2YqwkfZ31COyScGEsNTAjQHyTArwnxG8ysS7VVS8NBriPe-e_dduct9OiF2zIKQofGjo1g/s320/image013-776681.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5981789834827341938" /></a><o:p></o:p></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comments: Had much higher hopes here; early on I had submitted articles to a few IT trade magazines such as MSDN or Code; nothing accepted; Of course the world changed at the same time, with much more emphasis on community and open articles; so my blog and other social media represents at least some effort; Have arranged many workshops and presentations internal to the organization, not nearly enough public stuff<o:p></o:p></span></p> </td> </tr> </tbody> </table> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Next steps:<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">From this exercise, I would say I didn't end up exactly at plan, but it was a good check on a yearly basis or so to see if there were any big areas . I probably could've set more stretched goals, or documented in more detail the types of project which I wanted to undertake. Ultimately, very happy with where I've landed along this journey.<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Well, clearly the next step is to research and start a new five year resume, to set a target for 2019! Rough ideas include a new game plan for publishing and speaking. As my commitments to my sons' youth groups change shape as they age, I hope to spend more time in professional groups or perhaps find another non-profit in my community to assist. The next level of leaders I hope to emulate are hyper-involved in networking, their community, and the development of the staff around them. Education is always necessary, and I'll need to define a focus to the menagerie of online courses, webcasts, and videos that are available more than ever before, but competing for time with other activities.<o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="margin:0in;margin-bottom:.0001pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">This has been my experience with a five-year resume. I hope that if you've constructed one or are in the process, in what you see here, it will guide your to creating something that you can review at the end of the five year duration and measure yourself, identify next steps… and celebrate what you've accomplished.<o:p></o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-13793373855811795892014-02-11T17:31:00.002-08:002016-02-13T13:19:19.947-08:00Tortoise SVN - Update Failed! - Adapting an SSIS development approach for Window Server 2008<div class="WordSection1">
<div class="MsoNormal">
Trained some new developers on a SQL Server BI solution that required them to edit various source files - including SQL Server Integration Services (SSIS) packages. Well immediate after the orientation to the Version Control repository a new developer received an error attempting to update the local repository.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnc3LIyUOy6FwBo6ZLGv_PgFfTRdrqXy11ST8eP9-rFi25jjn9QLHOiKhb9iuKT_D4IWhhm5XJmsDnB0B6DoGhETDpJ42VtZ7dCPWshO3CJxUszoDMCqN4ky0kfh7ZfwomYqVsX8NmT8g/s1600/image001-753778.jpg"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnc3LIyUOy6FwBo6ZLGv_PgFfTRdrqXy11ST8eP9-rFi25jjn9QLHOiKhb9iuKT_D4IWhhm5XJmsDnB0B6DoGhETDpJ42VtZ7dCPWshO3CJxUszoDMCqN4ky0kfh7ZfwomYqVsX8NmT8g/s320/image001-753778.jpg" id="BLOGGER_PHOTO_ID_5979285768316848818" /></a></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Turned out a couple of assumptions or constraints were in play<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l0 level1 lfo11; text-indent: -.25in; vertical-align: middle;">
<span style="mso-list: Ignore;">1.<span style="font: 7.0pt "Times New Roman";"> </span></span>Development of SQL Server Integration Services packages is easier from a server console. The connection rules aren't an issue. The connection to source systems is usually - not always - but usually faster. Running a long-running package doesn't interfere with the developer's ability to do other programs on their laptop… or close it up and go home while the long-running process churns on.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l0 level1 lfo11; text-indent: -.25in; vertical-align: middle;">
<span style="mso-list: Ignore;">2.<span style="font: 7.0pt "Times New Roman";"> </span></span>Using TortoiseSVN to edit-merge-commit from a remote repository running on a file server was a simple and elegant version control approach.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l0 level1 lfo11; text-indent: -.25in; vertical-align: middle;">
<span style="mso-list: Ignore;">3.<span style="font: 7.0pt "Times New Roman";"> </span></span>Having the source code on a shared folder on the server's local file system was convenient<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l0 level1 lfo11; text-indent: -.25in; vertical-align: middle;">
<span style="mso-list: Ignore;">4.<span style="font: 7.0pt "Times New Roman";"> </span></span>When there were two developers and a Server running Windows Server 2003, this generally wasn't a problem - we weren’t stepping on each other's folders and/or 2003 security was fairly lenient.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt;">
<br /></div>
<div class="MsoNormal">
After migrating this technique to Windows Server 2008, these error became apparent quickly.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l3 level1 lfo12; text-indent: -.25in; vertical-align: middle;">
<span style="mso-list: Ignore;">1.<span style="font: 7.0pt "Times New Roman";"> </span></span>It was still convenient to develop SSIS packages from the server's console.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l4 level1 lfo13; text-indent: -.25in; vertical-align: middle;">
<span style="mso-list: Ignore;">2.<span style="font: 7.0pt "Times New Roman";"> </span></span>The shared folder on the local file system became problematic. The 2008 security (it behaves like Windows Vista or Windows 7 does) became more finicky about file ownership and ability to edit and delete files with another's ownership.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt; mso-list: l4 level1 lfo13; text-indent: -.25in; vertical-align: middle;">
<span style="mso-list: Ignore;">3.<span style="font: 7.0pt "Times New Roman";"> </span></span>Multiple developers currently in edit or merge, but not yet commit steps would collide with each other if one another's work overlapped in the file system.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 27.0pt;">
<br /></div>
<div class="MsoNormal">
The solution I took was to have each developer create their own 'SourceCode' folder under their 'Documents' folder (i.e. C:\Users\%USERNAME%\Documents\SourceCode). <o:p></o:p></div>
<div class="MsoNormal">
Tradeoffs are it is a bit trickier location to which to navigate. And the redundant copies of the code for multiple developers have a minimal impact on storage. The advantage, of course, is a truer work process with SVN - each developer is forced to develop SSIS packages separately editing and merging; mitigating most conflicts through collaboration.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com1tag:blogger.com,1999:blog-4759726564872047874.post-9077824481681916062014-01-09T15:45:00.001-08:002014-01-15T23:44:51.383-08:00Pencil Project as an alternative to Visio for IT professionals<div class="WordSection1"> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Pencil Project advertises itself as an open source Graphical User Interface (GUI) prototyping tool. The project stresses it's available for "All" platforms. "All" platforms includes Windows, Mac, and Linux (Fedora package).<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">(<a href="http://pencil.evolus.vn/">Installed version</a> | <a href="http://portableapps.com/apps/graphics_pictures/pencil-project-portable"> Portable Version</a>)<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">As my career has progress in roles such as developer, database administrator, project manager, or analyst, I have always used the software Microsoft Visio. My first experience with it was in college when Visio was made by a separate company, before being acquired by Microsoft. I have always carried it with me, buying my own license when working for small companies, and using an enterprise license more recently. <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">As much as Microsoft Visio has been help, it does have limitations. The software is not always 'with you', or may be unattainable or inaccessible to other team members due to licensing issues.<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Pencil Project in a portable version can be carried on your portable drive or cloud drive.<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <b><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Business Analysis<o:p></o:p></span></b></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Pencil Project has a basic, but complete Flowcharting shapes available. Connectors allow basic connections between shapes and stay connected as the diagram is arranged.<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbA4_DrRsC9LgG4FbQP_-AdsQGcXwhacaLu7TNxqk5CAA9SlYs2A0dxyONprDy2xZpz1-Jd8IdWwbl6fndIdofCysn66sZ3ly6euhtMeekj211y-MhHuNyNPlIZf_-W7n_aT75Ticd4Ok/s1600/image006-732496.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbA4_DrRsC9LgG4FbQP_-AdsQGcXwhacaLu7TNxqk5CAA9SlYs2A0dxyONprDy2xZpz1-Jd8IdWwbl6fndIdofCysn66sZ3ly6euhtMeekj211y-MhHuNyNPlIZf_-W7n_aT75Ticd4Ok/s320/image006-732496.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5967045877598925426" /></a><o:p></o:p></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">There are three simple connector choices:<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqjmbviO1NgC68xrxvPol6mx3B4Gb1gnEzu8VqyNa1uI0FI5sAbAk1lYFp4HdUUOxA6i4j-IabmmMucyn23vXNZoH0wop4d3Hzllz8S1cw40eBoK0JsDbK9EnhQxFlnhunOCGSrfitP64/s1600/image003-734862.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqjmbviO1NgC68xrxvPol6mx3B4Gb1gnEzu8VqyNa1uI0FI5sAbAk1lYFp4HdUUOxA6i4j-IabmmMucyn23vXNZoH0wop4d3Hzllz8S1cw40eBoK0JsDbK9EnhQxFlnhunOCGSrfitP64/s320/image003-734862.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5967045888307795058" /></a><o:p></o:p></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <b><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Database<o:p></o:p></span></b></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Pencil Project contains no templates or Shapes for representing Data or Database objects. Another diagramming tool is necessary.<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <b><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">UI prototyping<o:p></o:p></span></b></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Extensive templates and shapes for popular Uis.<o:p></o:p></span></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzYb0pA4l1-wdZ0E176qg5Myp6JEeCXQaUtr_XPpF34U2f6jQGSbEu4phyphenhyphen8M49h3AQrqJnzFq17SuoZOzRfS7ASeExr9Z5D-QGrPfiMk4USCBAuIDges-VJKKCoXTeyAz3OFClYMHUJiA/s1600/image007-736991.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzYb0pA4l1-wdZ0E176qg5Myp6JEeCXQaUtr_XPpF34U2f6jQGSbEu4phyphenhyphen8M49h3AQrqJnzFq17SuoZOzRfS7ASeExr9Z5D-QGrPfiMk4USCBAuIDges-VJKKCoXTeyAz3OFClYMHUJiA/s320/image007-736991.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5967045898960008930" /></a><o:p></o:p></p> <p style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:0in;margin-left:1.45pt;margin-bottom:.0001pt"> <span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com2tag:blogger.com,1999:blog-4759726564872047874.post-45125219205374609052013-12-26T15:50:00.004-08:002016-02-13T13:18:41.586-08:00SQL Server 2012 AlwaysOn Availability Group - My experience with a proof-of-concept<div class="WordSection1">
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Just had my experience building our organization's first SQL Server 2012 AlwaysOn Availability Group. This is a proof-of-concept environment that will be used to demonstrate if we should build out this environment across physical locations. This post summarizes the resources used in this initial build.<o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"> <o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">The white papers from Microsoft were the more comprehensive resource. Used these when planning, and justifying the proof-of-concept.<o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"><a href="http://technet.microsoft.com/en-us/library/ff877884.aspx">Overview of AlwaysOn Availability Groups (SQL Server)</a><o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"><a href="http://msdn.microsoft.com/en-us/library/hh781257.aspx">Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery</a><o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"> <o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Once it got time to actually build, these step-by-steps guides or checklists were used:<o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"><a href="http://blogs.technet.com/b/canitpro/archive/2013/08/20/step-by-step-creating-a-sql-server-2012-alwayson-availability-group.aspx">Step-By-Step: Creating a SQL Server 2012 AlwaysOn Availability Group</a><o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"><a href="http://social.technet.microsoft.com/wiki/contents/articles/6198.how-to-build-sql-server-2012-alwayson-hyper-v-virtual-machines-for-demos-emu-build.aspx">How to Build SQL Server 2012 AlwaysOn Hyper-V Virtual Machines for Demos</a><o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"> <o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Here were some of the issues I ran into, and lessons learned<o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"> <o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 16pt;">Separation of Duties - Server Administration and Database Administration<o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">In my organization, there is a separate Server team controlling virtualization and Server OS while the 'Database' team has full rights to the database software. Our first build required some back-and-forth collaboration to resolve issues that went above and beyond a standard database server</span></div>
<ul>
<li><div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
Specify "Enterprise Edition" of Windows server. The 'Failover Clustering' feature simply isn't present in Windows Server Standard Edition (<a href="http://technet.microsoft.com/en-us/library/cc725923(v=WS.10).aspx">http://technet.microsoft.com/en-us/library/cc725923(v=WS.10).aspx</a>)<o:p></o:p></div>
</li>
<li><div class="MsoNormal" style="margin-left: 19.6pt; mso-list: l1 level1 lfo2; text-indent: -0.25in; vertical-align: middle;">
The Server Administration team first created a computer object, then granted me Full Control on the computer object in Active Directory. (In addition to being a local administrator on the actual servers) <a href="http://technet.microsoft.com/en-us/library/cc731002.aspx">http://technet.microsoft.com/en-us/library/cc731002.aspx</a><o:p></o:p></div>
</li>
<li><div class="MsoNormal" style="margin-left: 19.6pt; mso-list: l1 level1 lfo2; text-indent: -0.25in; vertical-align: middle;">
After cluster creation, an IP address was reserved for the cluster object.</div>
</li>
</ul>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"><strong>Lessons learned:<o:p></o:p></strong></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Setup up through the Windows Server Failover Cluster step mostly involves the Server Administration role. Expect some collaboration needed between Server Administration and Database Administration in new environments<o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"> <o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 16pt;">AlwaysOn Availability Group configuration<o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">The AlwaysOn Availability Group configuration itself was extremely straight-forward. The issues I ran into turned out to be items that, either by habit or due the environment, were overlooked when creating a test. Might not run into these in production with the stricter controls on individual SQL Server instances<o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"> <o:p></o:p></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Note that the database to be made 'AlwaysOn' should be attached or restored to the 'Primary' SQL Server instance in the cluster. During the AlwaysOn group creation, it can be restored to any secondary instances.</span><span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"> <o:p></o:p></span></div>
<ul>
<li><div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">The Developer Edition of SQL Server 2012 has TCP/IP disabled. I had only enabled it on the secondary instances and had connected to them while connected via Remote Desktop to the primary instance. TCP/IP had to be enabled on the primary instance as well so the secondary instances could talk back. (<a href="http://connect.microsoft.com/SQLServer/feedback/details/693322/tcp-ip-disabled-by-default-on-sql-server-denali-ctp-3-refresh">http://connect.microsoft.com/SQLServer/feedback/details/693322/tcp-ip-disabled-by-default-on-sql-server-denali-ctp-3-refresh</a>)</span><span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"> <o:p></o:p></span></div>
</li>
<li><div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">All SQL Server instances were set to run as the local account \MSSQLSERVER. This was changed to use the same domain account on all servers in the group (<a href="http://technet.microsoft.com/en-us/library/ff878308.aspx#Accounts">http://technet.microsoft.com/en-us/library/ff878308.aspx#Accounts</a>)<o:p></o:p></span></div>
</li>
</ul>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"><strong>Lessons learned:<o:p></o:p></strong></span></div>
<div style="margin-bottom: 0in; margin-left: 19.6pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Assess the SQL Server instances to be included in an AlwaysOn group against the standard build for a standalone SQL Server in production.<o:p></o:p></span></div>
<div class="MsoNormal">
<o:p> </o:p></div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-82051515660631061092013-07-29T14:37:00.000-07:002016-02-13T13:18:41.589-08:00Fixup Script for conflict tables on a Transactional Replication<div class=WordSection1><p><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'>This script fixes up the conflict tables generated by a transactional replication subscription, and marks them as system tables. <o:p></o:p></span></p><p><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'>In SQL Server replication, a subscription to a publication of the type 'Transactional replication with updatable subscriptions' allow edits on the subscriber. Conflicts in those edits (e.g. a record modified at the subscriber and publisher) have to be resolved. The default setting is 'Keep the publisher change'. If a given subscriber is on the losing side of the change, conflicts are stored on the subscribers and can be viewed through the Conflict Viewer on the subscriber. They are stored in conflict tables per publication, per table in the format 'dbo.conflict_{PublicationName}_{TableName}. These tables are system tables.<o:p></o:p></span></p><p><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'>In the scenario that led to the need for this script, both of the servers was patched to SQL Service Pack 2, and replication stopped and restarted. At some point during the process, the conflict tables showed as User Tables. While replication did work continue successfully, having the tables as User Tables was problematic. For starters, permission grants or scripts that target all user tables risked allowing edits to these replication tables, and the Object Explorer view in SSMS became quite messy.<o:p></o:p></span></p><table class=MsoNormalTable border=1 cellspacing=0 cellpadding=0 style='border-collapse:collapse;border:none'><tr><td style='border:solid #BBBBBB 1.0pt;padding:.75pt .75pt .75pt .75pt'><p class=MsoNormal align=center style='text-align:center'><b><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'>Before<o:p></o:p></span></b></p></td><td style='border:solid #BBBBBB 1.0pt;border-left:none;padding:.75pt .75pt .75pt .75pt'><p class=MsoNormal align=center style='text-align:center'><b><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'>After<o:p></o:p></span></b></p></td></tr><tr><td style='border:solid #BBBBBB 1.0pt;border-top:none;padding:.75pt .75pt .75pt .75pt'><p class=MsoNormal><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqFergxfOr6bPKcyfjmErrIrs2wtFXb5eiGPnIVSIROp304k-WFrDdrIkGA2nBOj5BNq4oUALpC28nJ7CCYZByYpN3jivtz_Xjt46mHTv6QkMpK4h8Ew8kafgcprlaVGQsjOIjwEk8R8o/s1600/image005-783012.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqFergxfOr6bPKcyfjmErrIrs2wtFXb5eiGPnIVSIROp304k-WFrDdrIkGA2nBOj5BNq4oUALpC28nJ7CCYZByYpN3jivtz_Xjt46mHTv6QkMpK4h8Ew8kafgcprlaVGQsjOIjwEk8R8o/s320/image005-783012.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5906155056197764338" /></a></span><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'><o:p></o:p></span></p></td><td style='border-top:none;border-left:none;border-bottom:solid #BBBBBB 1.0pt;border-right:solid #BBBBBB 1.0pt;padding:.75pt .75pt .75pt .75pt'><p class=MsoNormal><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiievrjHrS5LH5-51ZTwwI1xRgT-pITel85TCps4V_l9D-6nTZlLU5RuifkJSwybFFm3ZKY8V86_jBPHyGDzFrmKV_mbZwDvLKu-hsKDRmeW7tthrAnw3zsKPTNqydiYYXcCSdIJZ7VZP4/s1600/image006-785404.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiievrjHrS5LH5-51ZTwwI1xRgT-pITel85TCps4V_l9D-6nTZlLU5RuifkJSwybFFm3ZKY8V86_jBPHyGDzFrmKV_mbZwDvLKu-hsKDRmeW7tthrAnw3zsKPTNqydiYYXcCSdIJZ7VZP4/s320/image006-785404.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5906155064011161266" /></a></span><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'><o:p></o:p></span></p></td></tr></table><p><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'>Limited testing for this has occurred - basically confirmed successful insert, update, and delete transactions before and after running this script.<o:p></o:p></span></p><p><span style='font-size:9.5pt;font-family:"Segoe UI","sans-serif";color:black'>Please note much credit for assisting in finding the underlying symptoms and general brainstorming should go to all the people at @SacSQLUG, and @SQLSoldier<o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white'><b><span style='font-size:8.0pt;line-height:125%;font-family:"Segoe UI","sans-serif";color:#E66A38'>SQL<o:p></o:p></span></b></p><p class=MsoNormal style='line-height:125%;background:white'><span style='font-size:8.0pt;line-height:125%;font-family:"Segoe UI","sans-serif";color:black;display:none'>Edit|<span class=pluginremoveholderlink>Remove</span><o:p></o:p></span></p><div style='mso-element:para-border-div;border-top:1.5pt;border-left:1.0pt;border-bottom:1.5pt;border-right:1.0pt;border-color:#D0D2D2;border-style:solid;padding:7.0pt 7.0pt 7.0pt 7.0pt;background:white'><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlmlcom1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>/* <o:p></o:p></span></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlmlcom1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FixupConflictTables <o:p></o:p></span></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlmlcom1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'> <o:p></o:p></span></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlmlcom1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>Will examine the conflict tables generated by a transactional replication subscription, and mark them <o:p></o:p></span></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlmlcom1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>as system tables if for some reason they are showing up as User Tables <o:p></o:p></span></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlmlcom1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>*/</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>--</span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>set</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>this</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>constant</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>to</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>have</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>this</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>script</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>perform</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>the</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>action</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>, </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>otherwise</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>it</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>will</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>output</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>the</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>resulting</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>script</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>only</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>DECLARE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>ExecuteNow</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>bit</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SET</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>ExecuteNow</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> = </span><span class=sqlnumber1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>0</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>--</span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>find</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>the</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>name</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>of</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>the</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>associated</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>publication</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>DECLARE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>Publication</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>sysname</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SELECT</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>Publication</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> = </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>publication</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FROM</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>dbo</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>.</span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>MSreplication_subscriptions</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>PRINT</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlstring1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>'--Fix up conflict tables for the publication '</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> + </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>Publication</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>--</span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>buffers</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>to</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>hold</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SQL</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>statements</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>DECLARE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SQL</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>nvarchar</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>(</span><span class=sqlnumber1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>4000</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>) <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SET</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SQL</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> =</span><span class=sqlstring1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>''</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>DECLARE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>tablename</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>sysname</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>--</span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>loop</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>through</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>the</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>tables</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>that</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>look</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>like</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlstring1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>'conflict_{Publication}_{tablename) and aren'</span></span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>t</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>marked</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>as</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>MS</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>shipped</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> (</span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>i</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>.</span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>e</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>. </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>system</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>objects</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>) <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>DECLARE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>curConflictTables</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>CURSOR</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>LOCAL</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FAST_FORWARD</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>READ_ONLY</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FOR</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SELECT</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>name</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FROM</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>sys</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>.</span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>tables</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>WHERE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>name</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>LIKE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlstring1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>'conflict_'</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> + </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>Publication</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> + </span><span class=sqlstring1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>'_%'</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>AND</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>is_ms_shipped</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> = </span><span class=sqlnumber1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>0</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>OPEN</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>curConflictTables</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FETCH</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>NEXT</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FROM</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>curConflictTables</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>INTO</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>tablename</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>WHILE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> @</span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FETCH_STATUS</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> = </span><span class=sqlnumber1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>0</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>BEGIN</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> --</span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>build</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>the</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>sql</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>to</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>convert</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>this</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>to</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>a</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>system</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>table</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SET</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SQL</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> = </span><span class=sqlstring1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>'sys.sp_MS_marksystemobject ''' + @tablename + '''</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'>' <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>PRINT</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SQL</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>IF</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>ExecuteNow</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> = </span><span class=sqlnumber1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>1</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>EXECUTE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>sp_executesql</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>SQL</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FETCH</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>NEXT</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>FROM</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>curConflictTables</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>INTO</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>@</span></span><span class=sqlvariable1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>tablename</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>END</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlcom1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>-- close a cursor</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>CLOSE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>curConflictTables</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='line-height:125%;background:white;border:none;padding:0in'><span class=sqlkeyword1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>DEALLOCATE</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> </span><span class=sqlid1><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New"'>curConflictTables</span></span><span style='font-size:10.0pt;line-height:125%;font-family:"Courier New";color:black'> <o:p></o:p></span></p></div><p class=MsoNormal><o:p> </o:p></p></div>Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-67319146804704339992013-04-23T08:13:00.002-07:002013-04-23T08:13:28.945-07:00WinDirStat for SQL Server DBAs<div class="WordSection1">
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">WinDirStat can be used to quickly assess the drive usage by focusing on the relative usage of the drive. As a SQL Server DBA, disk volume and usage may not be your direct purview, but undoubtedly it can affect you. Usually it will affect you at the worst time: The disk is full and SQL Server database and supporting files are to blame. The immediate task affecting the DBA will be to get the disk usage under control - this requires you to assess how to quickly recover disk space in low risk areas. A follow-on task to such an issue will be to then assess capacity and make recommendations. WinDirStat is a free, open source tool that can help in these situations.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">The three pane layout of WinDirStat allows you to simultaneously see and compare the following aspects:</span><span style="font-family: Calibri, sans-serif; font-size: 11pt;"> </span></div>
<div class="MsoNormal" style="margin-left: 20.75pt; mso-list: l0 level1 lfo1; text-indent: -.25in; vertical-align: middle;">
</div>
<ul>
<li>Relative Size per folder</li>
<li>Relative Size per file type</li>
<li>Proportional graphical view of each file</li>
</ul>
<br />
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: Calibri, sans-serif; font-size: 11pt;">For example, in the below screen shot, a drive was nearly 99% full. This is a simple example because the drive is organized very well already and has a very specific use. It becomes clear quickly that the folder 'SQLLogs' uses the most space, that the 'LDF File' file type or 'SQL Server Log File' is the file type at issue. And through the graphical view, you can quickly click and assess that the largest file is a particular log file that has grown to 118 GB.</span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<b><span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Example #1:<o:p></o:p></span></b></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLwzSGP89Qrg0lVy4_6hNlpnSUffuEwMSXsPFFnOqfkG3WjjErp2gxQKXPQ0ILpvc4Hx72iNSmBo5_d-wU9bcSh7KTR4LghkzQmo2F_juQpmLjAvyTKdy9Mqp3qUbuDTcuWibf6Gam8J0/s1600/image005-706471.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5869709782252658034" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLwzSGP89Qrg0lVy4_6hNlpnSUffuEwMSXsPFFnOqfkG3WjjErp2gxQKXPQ0ILpvc4Hx72iNSmBo5_d-wU9bcSh7KTR4LghkzQmo2F_juQpmLjAvyTKdy9Mqp3qUbuDTcuWibf6Gam8J0/s320/image005-706471.jpg" /></a></span><span style="font-family: "Courier New"; font-size: 11.0pt;"><o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">This is a simple example because the drive is organized very well already and has a very specific use. It becomes clear quickly that the folder 'SQLLogs' uses the most space, that the 'LDF File' file type or 'SQL Server Log File' is the file type at issue. And through the graphical view, you can quickly click and assess that the largest file is a particular log file that has grown to 118 GB.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">In this example, a more multipurpose volume on a development server, seeing the three aspects (by folder, by file type, and the graphical view) showed a more nuanced picture. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<b><span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Example #2:<o:p></o:p></span></b></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKtiY3zFD2SGoPCW_o0oyvKYxi8M4GXZSeFj4H_Ec0-Aa1MAAetsPcVP6QKm8rPH49fMKxS9EHXBlvrHLzsaXZb-OAS_N7AkFMKxy8Qin4HJMoJ2bVumpN1mrBFCjlBbIX1iRVSLlbRDk/s1600/image006-709108.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5869709795062091586" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKtiY3zFD2SGoPCW_o0oyvKYxi8M4GXZSeFj4H_Ec0-Aa1MAAetsPcVP6QKm8rPH49fMKxS9EHXBlvrHLzsaXZb-OAS_N7AkFMKxy8Qin4HJMoJ2bVumpN1mrBFCjlBbIX1iRVSLlbRDk/s320/image006-709108.jpg" /></a></span><span style="font-family: "Courier New"; font-size: 11.0pt;"><o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">The largest folder is the 'BackupsMonthly'. The largest file type was 'BAK File' - SQL Server Backups. Clicking the large blue squares revealed the largest SQL Server Backups. But in this case, those files were relatively stable in size, that is, a certain amount of backup files were necessary. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Secondarily, this revealed a large usage of the Recycle Bin (@RECYCLE.BIN) which was easily cleaned. Also a few large disk images ('ISO' shown as associated with 'Virtual CloneDrive' in the image above) related to software that was a candidate to be relocated to another server.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<b><span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">How to get WinDirStat<o:p></o:p></span></b></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">WinDirStat is a free, open source tool. It can be downloaded from <a href="http://windirstat.info/">WinDirStat.info</a>. Since it is open source, the program can be modified or extended and through the folks at PortableApps.com, there is a<a href="http://portableapps.com/apps/utilities/windirstat_portable"> portable version</a> available.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">I recommend acquiring the portable version of WinDirStat. Portable WinDirStat requires no installation, so the files can simply be copied - on your workstation, a network shared folder, or onto your SQL Servers themselves<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 20.75pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div class="MsoNormal">
<br /></div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com1tag:blogger.com,1999:blog-4759726564872047874.post-27996142110723257522012-12-12T01:36:00.000-08:002012-12-12T01:36:11.021-08:00Alternatives to handling null values in aggregate functions or How to avoid the "Warning: Null value is eliminated by an aggregate or other SET operation."<div class="WordSection1">
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Most SQL developers have written many queries that perform calculations using aggregate functions such as SUM() or COUNT(). Many have not thought deeply about the what is happening within that aggregate function, and lesser so deeply about the handling of NULL values. While typically, the developer should not have to think deeply about such internals, there is a situation where it might rise to your attentions. When you see the "Warning: Null value is eliminated by an aggregate or other SET operation".<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">In most querying activities, the "Warning: Null value is eliminated by an aggregate or other SET operation" wouldn't bubble up to your attention. In SQL Server Management Studio, you might notice if you click over to the 'Messages' tab:<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiO7DQzGAkt0OvPo8Baj0txc_Hp9D-NdBj-zlhmnu1zSeAd2hCRunUh34v2zONHh4Yg7QYJ7hjk7oANfu6qxq-dMSDi9DMBM6Pwv1CEh2zYy8ekFb93TLb36i4try2_JcukE8vTah4DJG4/s1600/image010-761755.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5820990056870297538" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiO7DQzGAkt0OvPo8Baj0txc_Hp9D-NdBj-zlhmnu1zSeAd2hCRunUh34v2zONHh4Yg7QYJ7hjk7oANfu6qxq-dMSDi9DMBM6Pwv1CEh2zYy8ekFb93TLb36i4try2_JcukE8vTah4DJG4/s320/image010-761755.jpg" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">This appears on an otherwise working query. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">There is a situation where this demands developer attention, when it is encountered in a SQL Server Integration Services package. A Microsoft Connect-reported issue describes the scenario where "<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">An SSIS package which runs fine but sporadically fails with "Warning: Null value is eliminated by an aggregate or other SET operation" (<a href="http://www.blogger.com/An%20SSIS%20package%20which%20runs%20fine%20but%20sporatically%20fails%20with%20%22Warning:%20Null%20value%20is%20eliminated%20by%20an%20aggregate%20or%20other%20SET%20operation%22">Bug 483175</a>). <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">This can occur when the query for a Source Adapter is written in such a way that it aggregates potentially Null values, either directly or through dependent objects.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Microsoft has weighed in with "The real problem is why the underlying OLEDB provider returns a failure code then. Probably the warning is related but not necessary. The specific warning can happen to be there." Agree or disagree with the resolution to that, the reality is something about the query in the Source Adapter that will have to change. The following is a very simple example of the scenario, and two alternatives to change the query such that OLEDB warning will no longer occur.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">The requirement from the script was a result set that will count the not null values.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">The first step in the script is to setup a very simple table variable. Without specifying anything for the column beyond the name and data type, it will allow Null values. The script then inserts two legitimate values, one Null value, then one zero. This will be sufficient to demonstrate that different logical results are gathered by counting rows, by counting a column, and by summing.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">DECLARE @tst TABLE<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">(<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 55.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">VALUE int<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">INSERT INTO @tst (Value) VALUES (1)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">INSERT INTO @tst (Value) VALUES (7)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">INSERT INTO @tst (Value) VALUES (NULL)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">INSERT INTO @tst (Value) VALUES (0)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SELECT * FROM @tst<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">The first test is the query most developers would naturally right. You might have even built this through the Query Designer:<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SELECT COUNT(*) AS CountOfRows, COUNT(value) AS CountOfValues, SUM(value) AS SumOfValues FROM @tst<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRVjlOzChgV8SnX5t50IsXhmRoHNK9k_BG5HR48CXbCg16uz8uzDH3poeqer40ysQ7QR6Ke4yghyphenhyphengU3Rut1G8bSRL3RpEIJfEJHdpI1YJ8enVmwFLaIRaU3oK4M0zf10y4JVu9JF3bfMc/s1600/image003-763236.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5820990070186830050" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRVjlOzChgV8SnX5t50IsXhmRoHNK9k_BG5HR48CXbCg16uz8uzDH3poeqer40ysQ7QR6Ke4yghyphenhyphengU3Rut1G8bSRL3RpEIJfEJHdpI1YJ8enVmwFLaIRaU3oK4M0zf10y4JVu9JF3bfMc/s320/image003-763236.png" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Results meet requirements, the 'CountOfValues' clearly returns the '3' legitimate values. The 'CountOfRows' and 'SumOfValues' are true for what they are, but neither can be used to gain any insight into the number of non-null values. Back on the 'Messages' tab, though, the Warning about null values appears - this is the type of query that will cause an SSIS Source Adapter to raise an exception.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeY7p5OsaRaTYOdstspsbsAY2PWGr9HDvTRUQU9d8tC5KfzsBWUdc5SoSzzQ-L2DZDHnq0ThJJ7__kA1MzH-Na41vCLyBFKTdZDV2AhI8Q2XkfJw4ze3gM0MQkghazntkhCaE9vaewb8c/s1600/image011-765230.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5820990074937312786" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeY7p5OsaRaTYOdstspsbsAY2PWGr9HDvTRUQU9d8tC5KfzsBWUdc5SoSzzQ-L2DZDHnq0ThJJ7__kA1MzH-Na41vCLyBFKTdZDV2AhI8Q2XkfJw4ze3gM0MQkghazntkhCaE9vaewb8c/s320/image011-765230.jpg" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">What's happening here? Basically, this query is relying on the database management systems handling of null values in a certain way, instead of having the query itself specify how nulls should be handled. The ANSI-SQL 92 standard specifies that warnings be raised in the case of aggregate functions that rely on the elimination of null values before completing. (<a href="http://www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/">Gotcha! SQL Aggregate Functions</a> and NULL has deep discussion on this topic) The implication is that a given database management system might handle null values differently, or the handling could be set through configuration. There are schools-of-thought that the result of the aggregate function would be a Null value since at least one value is a null value. Perhaps from a purist point-of-view, but not particularly useful for business reporting or many common SQL querying examples.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">So how to avoid this error?<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Alternative 1: <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Simply add the statement 'SET ANSI_WARNINGS OFF' before your script. As you might expect, this suppresses any of the ANSI-specified warnings such as the handling of null values in aggregate functions. In the following sample code, the ANSI_WARNINGS is turned off, the same query is ran. No warning appears on the Messages screen. In this sample ANSI_WARNINGS is turned back so the rest of the script can be demonstrated. But it is not strictly required to set ANSI_WARNINGS back on. Setting it to Off will only last through that query session. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">--Alternative 1: turn warnings off, rerun<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SET ANSI_WARNINGS OFF<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SELECT COUNT(*) AS CountOfRows, COUNT(value) AS CountOfValues, SUM(value) AS SumOfValues FROM @tst<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">--turn warnings back on, for this session<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SET ANSI_WARNINGS ON<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbAXNIkghyhEtWnExiX2dxEuriA0clwxeLXszcp1peRcaf4TGIne5Dmv5E_BT9OFQ94qLgHPLdZNTh1amUBE4gCA3zze8ekzNVyi73eiiUTbRoxFP4ND8hjAeER45Kr25W8fvk1RQaVPo/s1600/image006-767325.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5820990083787731714" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbAXNIkghyhEtWnExiX2dxEuriA0clwxeLXszcp1peRcaf4TGIne5Dmv5E_BT9OFQ94qLgHPLdZNTh1amUBE4gCA3zze8ekzNVyi73eiiUTbRoxFP4ND8hjAeER45Kr25W8fvk1RQaVPo/s320/image006-767325.png" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaCZQT0Sn7hl4sZ-8HpdkMaxoUCK2HrBIMOu4nXHe8xtqzfDkGVjSlfLw3_V9FgPxE3oiwEAvgWtlOJqgY4AbFwYZYKYwEP8mWEG-Twvg5rBXTDaQvT9iXeXg2RO1hKOnSnHqwLBKCR3U/s1600/image012-769092.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5820990091660678402" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaCZQT0Sn7hl4sZ-8HpdkMaxoUCK2HrBIMOu4nXHe8xtqzfDkGVjSlfLw3_V9FgPxE3oiwEAvgWtlOJqgY4AbFwYZYKYwEP8mWEG-Twvg5rBXTDaQvT9iXeXg2RO1hKOnSnHqwLBKCR3U/s320/image012-769092.jpg" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Disadvantages I have found so far. From a testability view, this suppression of warning for the remainder of the query and session could suppress other legitimate warnings. The only practical consequence I have seen is that if the query is designed with a graphical editor such as the Query Editor, the SET ANSI_WARNINGS statement will not be included in the edited query, and could be overwritten inadvertently in a copy-paste operation. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Alternative 2:<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Rewrite the query to make explicit the handling of Null values. In the rewritten version, the query won't rely on the Database Management Systems' handling of Nulls or the ANSI standard, the handling will be explicitly stated. Basically, the Nullable column is converted to a nonnullable boolean column based on whether the original value was populated or not. The new nonnullable boolean column (the bit data type in SQL Server) can be summed to meet the original requirement of a count of non-null values. In the sample script, this is all done in a single statement. If the query was the definition of an object such as a view, you would expect the column to have a not null constraint.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">--Alternative 2: rewrite to provide a logical handling of the NULL value<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SELECT COUNT(*) AS CountOfRows, SUM(CASE WHEN Value IS NOT NULL THEN 1 ELSE 0 END) AS CountOfValues, SUM(COALESCE(value,0)) AS SumOfValues FROM @tst<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbAXNIkghyhEtWnExiX2dxEuriA0clwxeLXszcp1peRcaf4TGIne5Dmv5E_BT9OFQ94qLgHPLdZNTh1amUBE4gCA3zze8ekzNVyi73eiiUTbRoxFP4ND8hjAeER45Kr25W8fvk1RQaVPo/s1600/image006-767325.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5820990083787731714" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbAXNIkghyhEtWnExiX2dxEuriA0clwxeLXszcp1peRcaf4TGIne5Dmv5E_BT9OFQ94qLgHPLdZNTh1amUBE4gCA3zze8ekzNVyi73eiiUTbRoxFP4ND8hjAeER45Kr25W8fvk1RQaVPo/s320/image006-767325.png" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7jAcd-N_rZRPJlXSt9nGkAl5hPBtdq6ew_aRolB6l9OU-8IkWTLxgjow1EBYXigbn3qSY-ZQJepZE15k_TmobipswNcgeqG9xHySapMWKm09OqR_Bnvs5wfmfTn_qtZ-t0x3LQs_jpJA/s1600/image013-771580.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5820990099770208594" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7jAcd-N_rZRPJlXSt9nGkAl5hPBtdq6ew_aRolB6l9OU-8IkWTLxgjow1EBYXigbn3qSY-ZQJepZE15k_TmobipswNcgeqG9xHySapMWKm09OqR_Bnvs5wfmfTn_qtZ-t0x3LQs_jpJA/s320/image013-771580.jpg" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Disadvantages seem to be adjusting to the idea of using the SUM() aggregate function to produce what one might naturally write using the COUNT() aggregate function. It is supported, but less readable in a graphically query designer.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Conclusion:<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">In most cases, writing a query "Warning: Null value is eliminated by an aggregate or other SET operation" should barely register with you. At best, it might prompt you to verify the null-ability of the columns which you are querying matches your expectation. However, if the query is to be used in the Source Adapter of a SQL Server Integration Services package, action must be taken. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">If it is a quick work-around that is needed, and there is a strong protection against the inadvertent modification of a query, you might consider adding that statement 'SET ANSI_WARNINGS OFF' before the beginning of the query. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">If a more resilient situation is called for, for example if the query is to be accessed from multiple callers in the form of a database view, you may consider doing the work of rewriting the query to explicitly handle null values.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Entire Script:<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">DECLARE @tst TABLE<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">(<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 55.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">VALUE int<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">INSERT INTO @tst (Value) VALUES (1)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">INSERT INTO @tst (Value) VALUES (7)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">INSERT INTO @tst (Value) VALUES (NULL)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">INSERT INTO @tst (Value) VALUES (0)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SELECT * FROM @tst<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">--works but triggers the 'Warning: Null value is eliminated by an aggregate or other SET operation' warning<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">--SELECT COUNT(*) AS CountOfRows, COUNT(value) AS CountOfValues, SUM(value) AS SumOfValues FROM @tst<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">--Alternative 1: turn warnings off, rerun<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SET ANSI_WARNINGS OFF<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SELECT COUNT(*) AS CountOfRows, COUNT(value) AS CountOfValues, SUM(value) AS SumOfValues FROM @tst<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">--turn warnings back on, for this session<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SET ANSI_WARNINGS ON<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">--Alternative 2: rewrite to provide a logical handling of the NULL value<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Courier New"; font-size: 11.0pt;">SELECT COUNT(*) AS CountOfRows, SUM(CASE WHEN Value IS NOT NULL THEN 1 ELSE 0 END) AS CountOfValues, SUM(COALESCE(value,0)) AS SumOfValues FROM @tst<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div class="MsoNormal">
<br /></div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com4tag:blogger.com,1999:blog-4759726564872047874.post-46954257871405647822012-12-07T19:32:00.002-08:002012-12-07T19:32:53.755-08:00Reporting Services Report Optimized for the 'Select All' option of a Multi-Select Parameter<div class="WordSection1">
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">A SQL Server Reporting Services (SSRS) report was behaving dramatically slow when the user selected all parameters in a multi-select parameter list of 30-40 locations. The report performed within 5-10 seconds when 1 location was selected. Selecting all locations would eventually time out, exceeding what I believe is a 5 minute connection time out by default in a Reporting Services in SharePoint integrated mode. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Below are the steps used to improve this performance to an acceptable level..<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Removed the Where clause that was filtering by the location parameter<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiels2SmYyk0NRF6_aj3G6CYJ4nDXKzSqApPQlHmD2GOytsOAgoEP_79rPzUy3ZzbbNqztojImf25tSz_wRJlvX5XuCQFwHRF_I3507_QK1JTS9Jx9rUnIe7ywq2vIa_twLecQiN-ZOAO4/s1600/image007-790246.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5819400183406836658" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiels2SmYyk0NRF6_aj3G6CYJ4nDXKzSqApPQlHmD2GOytsOAgoEP_79rPzUy3ZzbbNqztojImf25tSz_wRJlvX5XuCQFwHRF_I3507_QK1JTS9Jx9rUnIe7ywq2vIa_twLecQiN-ZOAO4/s320/image007-790246.jpg" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Changed it to a DataSet filter.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcqUXLwO_yzzhBZgXW7iKzEjV6H5leWzSSmNtLMYjLEOxIo6WpzqhahgP0s_RpipS-TTOkD5tbk_L1pfFcgE7JQ7ocSZoj5ZZ7IYmArltJTOYAVAhHMJfMwzC-u2NhShazq4aEz8NWevM/s1600/image008-792281.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5819400194408800994" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcqUXLwO_yzzhBZgXW7iKzEjV6H5leWzSSmNtLMYjLEOxIo6WpzqhahgP0s_RpipS-TTOkD5tbk_L1pfFcgE7JQ7ocSZoj5ZZ7IYmArltJTOYAVAhHMJfMwzC-u2NhShazq4aEz8NWevM/s320/image008-792281.jpg" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">When passed in the query, SQL Server was trying to incorporate it in the query plan. Because the institution is a string value that itself needs to be parsed, this made for a complex loop through the passed in institutions. This might be optimized in the future by 'closing' the view (there are some requirements on the structure of the view to do so)<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">The query plan for the query filtered compared to the unfiltered query were similar. Both actually rely on table scans because there is some text parsing work at play to get at key attributes. The unfiltered query actually reports more expensive operations in the form of the hash match and is reported to be slightly larger in cost. <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSZQ1j_YYG3g70d16vJqPYc6u8zAE4JM7NH8pR7-XybWVaw89YCd5LOolGXLAWCT1Tccc7IdDqR6BWcmhjr8NbjO4eXZ-AjhoeKqIzvP_x0k8MoouFm-Kgj225z7v7z2fDlobYyCtGB5Y/s1600/image009-796280.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5819400208792762130" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSZQ1j_YYG3g70d16vJqPYc6u8zAE4JM7NH8pR7-XybWVaw89YCd5LOolGXLAWCT1Tccc7IdDqR6BWcmhjr8NbjO4eXZ-AjhoeKqIzvP_x0k8MoouFm-Kgj225z7v7z2fDlobYyCtGB5Y/s320/image009-796280.jpg" /></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">In practice, though the unfiltered returns 100-200 rows in a second, the filtered query returns in between 1-5 minutes depending on how many locations are filtered for.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">Data Set Filters are conceptually filtered 'client-side'. In the case of Reporting Services, the 'client-side' is the Report Server. It has to go out to the database server, pull 'all' data matching the query, then process and only display the ones that match the filter. So more data is transferred between the two processes… and more work is done in the reporting server process. Usually that is not ideal<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 1.45pt; margin-right: 0in; mso-margin-top-alt: 0in;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">In this situation, it was faster to get all rows, unfiltered, out of the database, make them available for reporting, and have the report decide which to display.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-46078980269195354192012-11-22T01:30:00.000-08:002012-11-22T01:30:08.274-08:00How is pitching a movie script like pitching a software solution?A couple of nights ago, I listened to a <a href="http://www.nerdist.com/2012/11/nerdist-podcast-max-landis/">podcast featuring Max Landis</a> at the <a href="http://nerdist.com/">Nerdist.com</a> where he talked about the screenwriting process. In it he covers his screenwriting experience and then... in about the last half-hour, talks about three approaches to pitching a script to movie producers. Basically, getting his script funded or purchased. After thinking about it further, I considered, could his approaches be used in pitching software solutions or alternatives to clients or internal customers. How is pitching a movie script like pitching a software solution?<br />
<br />
<a href="http://en.wikipedia.org/wiki/Max_landis">Max Landis</a>' bona fides include just a few past screenwriting credits, then the cult movie <a href="http://www.imdb.com/title/tt1706593/"><i>Chronicle</i></a> and the YouTube video <a href="http://the%20death%20and%20return%20of%20superman/"><i>The Death and Return of Superman</i></a>. Apparently he has something like twelve scripts sold and in production. In an unconventional profession, Landis is a bit unconventional-er... (more unconventional) than many. He cleanly acknowledges this and spends solid parts of the podcast describing what goes in his mind as he as creating. Midway through the podcast, he starts weaving a story idea of a British sailor in the late 18th century. The technique he uses to 'pitch' this story he will then reveal is his favorite of three approaches of pitching a script to movie producers. <br />
<br />
The first approach Landis describes is to pitch the script by talking through the plot scene-by-scene. The second approach, he uses in the British sailor story, is to talk about the script as if you just saw the film and want to talk your friends into seeing it with you. The third approach he mentions is to start with an idea, a potentially big and abstract idea, and flesh it out with possible scenarios that would center of the idea. Now can we pitch, or sell, or present, a software solution using these approaches?<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyAuY2qIlQCbr_QxfSL1ZEcC9LYOemfTMa-mJEwEnX-pNyoDHD4nWMHOHpmSQB3kxpdAz14e7Y4j8v5dObcYCdTcqkBhgMYqlBb8phlZaNgt7MxaLHUbIO5Gh3aLP5R2rKkfX77HEQJVY/s1600/ClapBoard.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyAuY2qIlQCbr_QxfSL1ZEcC9LYOemfTMa-mJEwEnX-pNyoDHD4nWMHOHpmSQB3kxpdAz14e7Y4j8v5dObcYCdTcqkBhgMYqlBb8phlZaNgt7MxaLHUbIO5Gh3aLP5R2rKkfX77HEQJVY/s1600/ClapBoard.jpg" /></a></div>
<br />
<br />
<span style="font-size: large;">Scene-by-Scene</span><br />
The scene-by-scene approach is traditionally used in pitching software solutions. It is what I learned at University in courses on Software Development Life Cycle (SDLC). In this approach, you gather the requirements and present them back as data flows or process charts. Waterfall models, stereo-typically, exude this step-by-step sequential feel. Also, agile techniques such as Use Cases and User Stories can have a fairly rote sequentiality. The process starts at point A, goes through these steps and ends when point B is reached. Some demos and slide decks follow this approach: this is how you log in, this is how you search, this is how you view, this is how you edit.<br />
<br />
This can work. Just like the example in the podcast of a script for a movie where the studio already has the idea. It is a sequel, or a remake, or just the idea has been developed independently. This can be a situation where the solution is routine or the customer already knows what they want - but doesn't know if you can do it.<br />
<br />
I've presented quite a few solutions with this style, and most were to an extent already initiated or just matter-of-fact accepted. They may have been required regulatory compliance and my solution was pretty much the only alternative available and feasible. <br />
<br />
On the other hand I have pitched at least two projects using this approach unsuccessfully. At least one I wish had been successful. In that case, there was a opportunity to provide some automation support for California prison staff that traveled around that state negotiating transfer of prisoners between prisons. They had to take into take into account a number of factors of each prisoner's criminal history, manually cross reference availability at other locations and then print appropriate documents to authorize transfers. I led a team that both did site visits to see this process firsthand and had subject-matter experts visit and review documentation. I presented it back to leadership as mostly flowcharts and diagrams, to sort of... prove that we understood the need and were technically competent. But none of the documentation really resonated and the project didn't go anywhere at the time. This customer may or may not have been willing to do the work if I had presented it more vibrantly. <br />
<br />
Another project, I will confess, I was glad was not selected. It was for a subset of results from a specialized type of audit. I had taken the approach of presenting a brief but thorough scope document that mostly focused on the structure that would be needed to provide reliable, trust-able numbers while most of the stakeholders focused on the high-level summary output output. This was largely to tease out the customer's willingness to commit their resources to doing detailed quality work such that the reporting would be reliable and sustainable over time. They weren't... and so the 'scene-by-scene' technique was effective in bearing that out.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6JBLgl_ar4zcN9gzRYe2M-njCpcsz-ZXxlBnHoY0l3Y12qeFcRRjxWbaFC8jyRwvaJAxvVKFZavdGeDAYU5GkD0pn4WiT6cwC2tb27RVoaqQYH5YF7aH-iHkve1YhOSKBX6AnGrVb_18/s1600/Visualizing.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6JBLgl_ar4zcN9gzRYe2M-njCpcsz-ZXxlBnHoY0l3Y12qeFcRRjxWbaFC8jyRwvaJAxvVKFZavdGeDAYU5GkD0pn4WiT6cwC2tb27RVoaqQYH5YF7aH-iHkve1YhOSKBX6AnGrVb_18/s1600/Visualizing.jpg" /></a></div>
<br />
<br />
<span style="font-size: large;">Talk about the Solution as if you have Seen it</span><br />
This technique can definitely be used to pitch a software solution, and it is definitely more difficult. Classic techniques such as storyboards and prototypes are probably the most obvious tools used in this approach. It's not just the use of them, though, I've found. The sequence and pacing and story being told through the purely visual artifacts is important. In this technique, it helps to talk present tense tense - you're talking about the business process as it exists with your solution in place. The client wants to get to this place and should start talking in the same present tense: "Yes, and we have ..," or "Exactly, then we do ..." <br />
<br />
Landis in his script-pitching process will describe the script in a 'loose' way when pitching, leaving a lot of details to the imagination, not necessarily telling the audience the entire ending in one sitting. When using this approach to pitch a software solution I tend to skip over things like logging in or key entry and menu navigation. Not because they're not needed, but because they don't directly help the end state be visualized. They exist 'around' the goal. To the extent possible, I would even argue as a philosophy to minimize the these tertiary features in the eventual solution. With the movement towards 'apps' vs. applications, 'chromeless' windows, single sign-on (SSO), this seems to be a trend throughout the software industry.<br />
<br />
The 'pitch' of the software solution need not be complete and comprehensive. If the customer intrigued by the start of solution, showing them only one logical path to an end point ends the creative process. My interpretation of this to leave out a lot of features. I try to be careful not to imply we missed them or don't know how to deliver a feature, but instead make it clear that there is a lot of, perhaps infinite, unexplored ground. For example, a grid view in a application might have a related print-friendly report. Then when you show the next grid view, the customer expect to see a print-friendly report, or email link button. Given the ability of modern development environments and platforms for reuse and patterns, filling in the 'story' to meet the customer's expectation could require modest effort.<br />
<br />
The project that comes to mind that I have 'pitched' this way was a need to bring data from electronic-filed court documents into a custom application. This started with a call from a client, I vividly remember, on a day I was home sick. I made a special effort to take his call because I knew he was concerned about our ability to service his needs on some new types of court cases he would be involved in very quickly. I described to him what he would see on the court's website, then where it would show up in his application. All the while I was being honest and realistic about what could be done. I even described to him data that might not be exist in the electronic-filed documents or fields that might be unreliable, where he would have to manually edit and fix entries. The next opportunity to 'pitch' this was with a prototype using a pilot court case. My prototype made a point of showing the court's website in one window and showing the custom application's data in a second window. The visibility wasn't a technical requirement, but showed the process<br />
<br />
If I recall, the prototype was more functional then it needed to be. The pilot court case was known well in advance and many features could have been hard-coded. We had already started down the road of using a bevy of patterns and practices that would eventually be required. Since multiple courts had their own formats, interfaces were used, files were imported from the web and translated to XML, then deposited back into a data layer. But it was the narrative told through the prototype, not the detailed flowcharts that helped the client visualize his business operating with my software solution in place and gave him the confidence in our ability to support him.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE8KxkE20hPDvEu44irrY6X7vl4C1T-sYI_QVyxvJs2WX1kRv5C85BbSqg9BlHHxDGqH9Qc7nJYsB4myhLSag8PA1Ca-eIGzMYpFqHeG6MDcBMG7e7O96KV5Y_yMx4KMKI37vm5cz78_8/s1600/idea.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE8KxkE20hPDvEu44irrY6X7vl4C1T-sYI_QVyxvJs2WX1kRv5C85BbSqg9BlHHxDGqH9Qc7nJYsB4myhLSag8PA1Ca-eIGzMYpFqHeG6MDcBMG7e7O96KV5Y_yMx4KMKI37vm5cz78_8/s1600/idea.jpg" /></a></div>
<br />
<br />
<span style="font-size: large;">The Abstract Idea</span><br />
The third approach was just starting with an idea, a sort of central theory around which a need or opportunity existed. Starting with the abstract idea is really hit-or-miss. I think inherently you have to take a lot of swings and expect just a few that gain any traction. How many back-of-the-napkin ideas have you discussed with a client or customer? How many of those gained any traction? Of those, how many ended up being funded projects?<br />
<br />
My experience is getting many of these out there, many of them nothing comes about. In other cases, I may not be the one selected to pursue it. A good attitude to take when you are involved in this idea generation, if the customer or client pursues it some other way is just to think 'great for them, they'll probably come to me when they want to do X project or have Y need'. <br />
<br />
One idea that I pitched and pursued was for an organization that had a ton of headaches around employees transferring between work assignments. One of the headaches was that the employee's user names contained an acronym indicating the location at which they worked. To keep this manageable, the user names had to be changed upon transfer. This disrupted the employee This disrupted any other systems to which they needed access rights. (SSO was some ways off) I was the supervisor of a technical support unit for one of the location so I had some ability to influence. The idea was, 'what if the employee's account name was just their first and last name?' With my team we filled in gaps around the idea: let's do this if two employees have the same name; let's handle employees with special accounts this way. Since we controlled at least the user names of new employees to our location, we put it in place with local management support and it later became a standard for the entire organization. Now, this isn't that 'big' of an idea and undoubtedly I wasn't the first one or only one to have it, but it should show how just starting your 'pitch' around an abstract idea can actually be put in motion.<br />
<br />
Is pitching a movie script like pitching a software solution?<br />
<br />
<span style="font-size: x-small;">(Note: in the <a href="http://www.nerdist.com/2012/11/nerdist-podcast-max-landis/">Nerdist podcast</a>, the specific discussion about the pitch techniques starts at about 1:26:00. The British sailor story pitch starts around 45:00)</span><br />
<br />
<br />Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-24136733887885821152012-11-14T02:41:00.001-08:002012-11-14T02:41:59.727-08:00Anatomy of an SSIS Package InfoPath Import<div class="WordSection1">
<div class="MsoNormal" style="margin-left: 19.6pt;">
Notes: This is the draft of a sample project and code article being developed to document a solution for collecting disparate values via a Microsoft Office InfoPath form deployed in SharePoint server. The data model may remind you of structures that are referred to as 'key-value pairs' or 'entity-attribute-value'. Although the focus here was on meeting a business need versus strict adherence to a pattern<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
This has been core to my work, but I believe the generic pattern you see may have some use to others. Since I could take this topic in many directions <o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt; mso-list: l0 level1 lfo1; text-indent: -.25in; vertical-align: middle;">
<span style="font-family: Symbol; font-size: 10.0pt;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";"> </span></span></span>upstream to the mechanics of deploying this form in a SharePoint server<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt; mso-list: l0 level1 lfo1; text-indent: -.25in; vertical-align: middle;">
<span style="font-family: Symbol; font-size: 10.0pt;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";"> </span></span></span>downstream to the database structures for working with this data all the way to incorporating it into performance metrics and a data warehouse, or <o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt; mso-list: l0 level1 lfo1; text-indent: -.25in; vertical-align: middle;">
<span style="font-family: Symbol; font-size: 10.0pt;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";"> </span></span></span>across to the management of multiple InfoPath forms using this model<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
Since I'm not yet decided where to start the sample project, this is an anatomy of the 'middle', the place where the data crosses from the user interface oriented structures of InfoPath to the raw data where it can be used in further database processes.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<b>Preparation Step<o:p></o:p></b></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjP96vW6cN7dSRdeJuTM9H0m-RjEE2iQ2q173OMasJDBapYJTMAKHFnZKYsQycd38mT7xPfxu91dsB7QlPDhI6Pi2Qyp19YD7wyE8gTQRaVRqUgXgH0K9OTCqcaAUcwdby2bGrD8jAqBK8/s1600/image006-718385.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5807861446470726914" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjP96vW6cN7dSRdeJuTM9H0m-RjEE2iQ2q173OMasJDBapYJTMAKHFnZKYsQycd38mT7xPfxu91dsB7QlPDhI6Pi2Qyp19YD7wyE8gTQRaVRqUgXgH0K9OTCqcaAUcwdby2bGrD8jAqBK8/s320/image006-718385.png" /></a><o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
Creates the Working folder and Archive folder if they don't already exist<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<b>For Each Form<o:p></o:p></b></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihUSrbE2J-Oa2_PvAzK6dv45lvo8-W06Q4fpoHjzaLzZYDMO6urvrkCkh9nDIkhgn4Y4xHxvDQ9JJKtJAnBf37wBQ3G3HyXOGVI2aPk2ZFJr3kV6m-imi7yAW59G233bBVrE9C2Yp7geU/s1600/image001-721727.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5807861458966509986" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihUSrbE2J-Oa2_PvAzK6dv45lvo8-W06Q4fpoHjzaLzZYDMO6urvrkCkh9nDIkhgn4Y4xHxvDQ9JJKtJAnBf37wBQ3G3HyXOGVI2aPk2ZFJr3kV6m-imi7yAW59G233bBVrE9C2Yp7geU/s320/image001-721727.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
The 'Prepare XML File' container prepares the XML file generated by submitting an InfoPath form for access as a tabular data set, suitable for importing to a relational database. The XML file is progressively transformed and the output of each step stored in the working folder. This enables instrumentation or troubleshooting to occur.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
The steps include:<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
"Copy File Into Working Space" [File System Task]: Copying the current file being processed as is into the working directory<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
"Strip Namespaces" [XML Task]: Removes to a custom namespace generated by the InfoPath form submittal called 'my'. This is accomplished through a short XSLT script.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
"Wrap in a table element" [XML Task]: Encapsulates the default 'myFields' node in a node called 'myTable'. This allows the later data flow task to see the file as a dataset with each InfoPath field as a column. Without this, each column appears as a separate data set. This is accomplished through a short XSLT script.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
"Pivot to Key-Values" [XML Task]: In this solution, the exact names and quantity of the fields vary over time or are unknown at design time. This step converts every InfoPath field into a 'field' record in the file, making the true InfoPath field name into an attribute. It then can be imported as data rather than being part of the metadata.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
The 'Import Raw Values to Output' container actually imports from the file into a data destination such as a relational database in the 'Import Raw Values' [Data Flow Task]. This is how the values from multiple InfoPath forms get to a single location against which further queries can be written. Since the form was earlier pivoted to key values, the InfoPath field name is one of the columns that is available to import.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
The last step 'Archive the File' [File System Task] completes the processing of that form. The form is moved to a subdirectory of the Directory being processed, renamed with a timestamp that can be used to examine the contents of the file at that point-in-time. That subdirectory is compressed or archived in a separate process.<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
Connection Managers<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhO-K7l_FFKTub2_S6698i9UKXh0H9MB1OcGiPN0nO_hM5naOamc4h9nVcaCa0Nvb75RbRKdutjmi7OFbtb4C7X85Zkqj8xjYUhJbEN9ouNoP572eC1IJ1qY15mYN7Z2tZbdaPp3VWqrlI/s1600/image003-725481.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5807861477144060482" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhO-K7l_FFKTub2_S6698i9UKXh0H9MB1OcGiPN0nO_hM5naOamc4h9nVcaCa0Nvb75RbRKdutjmi7OFbtb4C7X85Zkqj8xjYUhJbEN9ouNoP572eC1IJ1qY15mYN7Z2tZbdaPp3VWqrlI/s320/image003-725481.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
The connection managers for the package support the InfoPath XML translation. The output of each step, and then the final transformed copy is output to the file system to ease troubleshooting. Each of the separate XSLT scripts that does some transformation to the file is a file connection. This allows the XSLT to be housed in a shared source directory. <o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
For the sample code, the 'output' and 'outputtext' connection managers exist to demonstrate exporting multiple InfoPath forms to a flat files. In production, this would be a relational database such as SQL Server<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal" style="margin-left: 19.6pt;">
<br /></div>
<div class="MsoNormal">
<br /></div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com1tag:blogger.com,1999:blog-4759726564872047874.post-10704444369635803162012-11-06T15:19:00.000-08:002016-02-13T13:19:04.543-08:00SQL Server Agent Job - Failed Stats due to negative duration<div class="WordSection1">
<div class="MsoNormal">
Have some jobs that collect statistics from SQL Server Agent job history, looking for spikes and trends in data integration. Over the weekend, those stat collection jobs failed.<o:p></o:p></div>
<div class="MsoNormal">
<o:p> </o:p></div>
<div class="MsoNormal">
Turns out there were jobs that reported a dramatic ‘negative’ duration.<o:p></o:p></div>
<div class="MsoNormal">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSK7FYfXmYw1WGmnHxLvsfuZ4MptBCNxPslz6HCeC6Igjjwzw-d-kg3E_63y_PudVL_WqY_8fppnw9o4JQEhlDSFEimjc0VVopY2Fij8ohC1lRy_Z8B71Wvuzx15tNktEPZnMAB2YRTlg/s1600/image001-785872.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5807389720151999282" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSK7FYfXmYw1WGmnHxLvsfuZ4MptBCNxPslz6HCeC6Igjjwzw-d-kg3E_63y_PudVL_WqY_8fppnw9o4JQEhlDSFEimjc0VVopY2Fij8ohC1lRy_Z8B71Wvuzx15tNktEPZnMAB2YRTlg/s320/image001-785872.png" /></a><o:p></o:p></div>
<div class="MsoNormal">
The cause, this jobs had started at 2:00 AM Sunday morning, then the Daylight Savings Time kicks in. The job finishes 4 minutes later, but by then it’s actually ~56 minutes earlier than the start time. For some reason this gets reported as ~-3976 days, 21 hours… Then an hour later, it’s 2:00 AM again, and the job runs normally.<o:p></o:p></div>
<div class="MsoNormal">
<o:p> </o:p></div>
<div class="MsoNormal">
Fun stuff!<o:p></o:p></div>
</div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-33837919494694436182012-01-03T22:26:00.001-08:002012-01-03T22:27:22.700-08:00Maxim - Communicate in the 2nd person<div class="mobile-photo">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWjnO0_tmnw9COFkaNZruFZSUE7B85uhfsQZjBIET7TmdPmF_CSbkbn6wCg4aNc5Ml4bwQkhoN_IEwq0B63jGvYtlvretUtJ5Wsm3NlxHmcf3CcMTJ13EahXRdSef4cp4H3H5r3zamDoU/s1600/Maxim-Communicate+In+the+2nd+person-786917.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5693659421567679666" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWjnO0_tmnw9COFkaNZruFZSUE7B85uhfsQZjBIET7TmdPmF_CSbkbn6wCg4aNc5Ml4bwQkhoN_IEwq0B63jGvYtlvretUtJ5Wsm3NlxHmcf3CcMTJ13EahXRdSef4cp4H3H5r3zamDoU/s320/Maxim-Communicate+In+the+2nd+person-786917.png" /></a></div>
<div>
Communicate in the 2nd person.</div>
<div>
</div>
<div>
As the diagram reads, this reminder to communicate in the 2nd person so you focus on the recipient of the message. What will the reader want to know? What's important to them? Not 100% recalling how this was arrived at, it could be a tangential application of any number of <a href="http://www.amazon.com/gp/product/1439167346/ref=as_li_ss_tl?ie=UTF8&tag=re00b9-20&linkCode=as2&camp=1789&creative=390957&creativeASIN=1439167346">Dale Carnegie' topics</a>, but actually is more likely due to a pet peeve of seeing emails overwhelmed with "I's.</div>
<div>
</div>
<div>
</div>Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-25534509488793883632011-12-27T20:01:00.001-08:002012-01-03T22:34:59.086-08:00Maxim - Acknowledge communications within 24 hours<div class="mobile-photo">
A visualization to remind of the need to acknowledge communications within 24 hours</div>
<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv_GFTe3YQeZzp63Je0sMNDJD_iKayH4JYARQefurF3sO7GG7fmPiFvO2Y037taK2WDvE2LIguMtPL0T9FpGDwIkQN_e5FsBfYOMW0VQSgBxPo_FJrE87ZbFpTXQ-RpQ9HQ-UWLl626_w/s1600/Maxim-Acknowledge+Within+24+Hours-792733.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5691024483770037762" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv_GFTe3YQeZzp63Je0sMNDJD_iKayH4JYARQefurF3sO7GG7fmPiFvO2Y037taK2WDvE2LIguMtPL0T9FpGDwIkQN_e5FsBfYOMW0VQSgBxPo_FJrE87ZbFpTXQ-RpQ9HQ-UWLl626_w/s320/Maxim-Acknowledge+Within+24+Hours-792733.png" /></a> <br />
<div>
</div>
<div>
This is an reminder of a maxim from <a href="http://www.amazon.com/gp/product/096427552X/ref=as_li_ss_tl?ie=UTF8&tag=re00b9-20&linkCode=as2&camp=1789&creative=390957&creativeASIN=096427552X">Harry Shapiro's Legacy</a>, a book of business wisdom by a friend, previous client, and mentor, <a href="http://www.menssana.org/books/about_author.php">Dr. Irving David Shapiro</a>. His Maxim specifically advises to "Always return every electronic communication...the same day you receive it". In this day and age, you may receive communication at all hours from various time zones, so 'same day' can be tough to measure, but within 24 hours at least reminds you of the theme if you wake up to messages from the previous day.</div>Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-4630172283707128842011-12-20T23:54:00.001-08:002011-12-20T23:59:31.731-08:00Maxim - Three email rule<div class="mobile-photo">
</div>
<div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
A visualization of the three email rule</div>
</div>
<div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDSEmDxa4GIpYvOQQOlkLBF2ZMtbZnXJWzN_lcRj66CKhb31UNzzx8guZDNNQ5-lV_hFJ7P_KDQt4fTcO4kOJTApbjoRAKgHDzheQaBpjWlUzMMHzZ1KgZfY4r8DSIkwylt5B51fG1kVI/s1600/Maxim-Three+Email+Rule-745249.png" style="margin-bottom: 1em; margin-right: 1em;"><img alt="" border="0" height="351" id="BLOGGER_PHOTO_ID_5688486811039752498" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDSEmDxa4GIpYvOQQOlkLBF2ZMtbZnXJWzN_lcRj66CKhb31UNzzx8guZDNNQ5-lV_hFJ7P_KDQt4fTcO4kOJTApbjoRAKgHDzheQaBpjWlUzMMHzZ1KgZfY4r8DSIkwylt5B51fG1kVI/s400/Maxim-Three+Email+Rule-745249.png" width="400" /></a> </div>
</div>
<div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<br /></div>
</div>
<div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
In a recent office move, there was occasion for me to take down a bunch of hand-written post-it notes that I had written myself to remind of various professional maxims or just general good work habits. Figured rather than just put them back up at the new desk, would take a few moments to convert them into a more preservable format. The goal is to be relatively visual so they are in my face, the words are often too easy to ignore. But I'm certainly not anywhere decent in drawing, so block diagrams are my best play at something legible.</div>
</div>
<div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<br /></div>
</div>
<div>
The maxims themselves are generally adaptations, synthesis or snippets of my reading, conversations, training or other knowledge. I will try to credit them when possible. This one is a generality I've seen in a few different forms, including a 2-mail rule. Thought that sometimes refers to the request-response work flow, so i find the '3' email rule easy to apply by counting the number of messages in a thread. </div>Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0tag:blogger.com,1999:blog-4759726564872047874.post-57347466941295869582011-09-28T00:13:00.000-07:002011-09-28T00:14:11.398-07:00Connecting to Oracle from SQL Server - Initial steps<div>
Have started down the path of connecting to an Oracle database from an SQL Server. The Oracle database will be a key enterprise system, replacing a couple of legacy systems that currently are used to source a data warehouse.<br />
</div>
<div>
</div>
<div>
Not fully complete in terms of integrating or loading data via an ETL tool, but first things first. I expect to have futher posts as I work or relearn nuances of Oracle, syntax, etc.</div>
<div>
</div>
<div>
To start with what ended up working:</div>
<div>
Installed the <strong>Oracle Database 11<em>g</em> Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit) </strong>from <a href="http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html">http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html</a><br />
</div>
<div>
</div>
<div>
Used the Adminstrator option during installation to get additional tools for Oracle, including a Net Manager that managed Oracle-specific connections. My gut feeling, of course is that this is hefty footprint (~600 Mb install) on a server to mostly get ODBC drivers, but thus far, haven't been able to determine what's needed and not needed.</div>
<div>
</div>
<div>
After installing, in the install locations there was a seperate executable called odbc_install to run to get the ODBC drivers to show up <br />
</div>
<div>
</div>
<div>
"Installed" (really just copy-pasted) <a href="http://portableapps.com/apps/development/database_browser_portable">Database Browser Portable</a> to a server directory. This tool provides a rudimentary, but platform-independent viewer to the database. Oracle has various viewers in its client package, but at least some require 'Java' to be installed, but I'm hesitant to do so on a server. The Windows server and SQL server have their own set of maintenance issues with updates and service packs, and since using the Oracle-specific viewers won't be used on a regular basis in production that seems like an extra maintenance vector.</div>
<div>
</div>
<div>
<img height="428" id="il_fi" src="http://img.teck.in/DatabaseBrowserPortable.png" style="height: 334px; padding-bottom: 8px; padding-right: 8px; padding-top: 8px; width: 458px;" width="593" /></div>
<div>
Was able to use Database Browser Portable was able to connect to the Oracle database two ways:<br />
1. Setting up an Data Source Name (DSN) in the ODBC settings. In Database Browser Portable, choose an ODBC setting<br />
2. In Database Browser Portable, choose 'Oracle'. Now when doing so, it appears there is a drop-down for TNS names... Mine didn't list any, but typing in manually what I new the TNS name to be worked<br />
<br />
Thus far, not seeing any technical difference in Database Browser Portable between the two techniques, so suffice to say, i'll lean toward the Oracle connection type, to avoid the dependency upon the DSN. Not sure yet if the DSN will be required, or even just come in handy, when designing the actual ETL packages i'll need to build.<br />
<img height="478" id="il_fi" src="http://i1-win.softpedia-static.com/screenshots/Windows-Portable-Applications-Portable-Database-Browser_1.png" style="height: 461px; padding-bottom: 8px; padding-right: 8px; padding-top: 8px; width: 574px;" width="641" /><br />
<br />
What didn't work for me so far to get to this point were 'installing' only the 'InstantClient' from <a href="http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html">http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html</a>. 'Installing' is in quotes again because it appears the basic technique is to unzip various modules that represent the Instant Client, and copy them to a directory. Then only the aforementioned 'ODBC_install' is the only actual 'install' on the server. Using this technique to get the base instant client and ODBC drivers would lead to a ~100-200 MB directory: still a large footprint for simple ODBC access to database, but especially since nothing is 'installed', this seemed hopeful. Ran through the steps in this article, <a href="http://celemotan.wordpress.com/2008/10/22/instantclient/"><span style="font-family: Calibri; font-size: small;">http://celemotan.wordpress.com/2008/10/22/instantclient/</span></a>, though, including manually setting environment variables and wasn't able to get an ODBC connection to test successfully against the name I was using for the TNS name. <br />
<br />
An post on the support forum for Portable Database Browser asserted it would be possible to get it up an running with only the InstantClient in a directory. This was intriguing as it would imply nothing needs to be installed, not even the ODBC driver, and perhaps then this native access technique could be used in SSIS or similar. In my situation, wasn't able to get any combination of server, port and service name working. <span style="color: #1f497d; font-family: "Calibri", "sans-serif"; font-size: 11pt;"><a href="http://www.dbsoftlab.com/database-browser/1161-database-browser-on-oracle-instant-client.html">http://www.dbsoftlab.com/database-browser/1161-database-browser-on-oracle-instant-client.html</a></span></div>
Chris Andersonhttp://www.blogger.com/profile/16104054039305171828noreply@blogger.com0