tag:blogger.com,1999:blog-58928834844186195122024-03-12T18:04:20.571-05:00SQL Servingsjaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.comBlogger91125tag:blogger.com,1999:blog-5892883484418619512.post-23026576915190295222013-02-10T14:29:00.001-06:002013-02-10T14:29:49.016-06:00My Review of Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 2012<div class="hreview"><div class="item"><p><a href="http://oreilly.com/product/0790145345219.do">Originally submitted at O'Reilly</a></p><div><img src="http://images.powerreviews.com/images_products/01/26/21254299_100.jpg" class="photo" align="left" style="margin: 0 0.5em 0 0"><p style="margin-top:0">Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 2012</p></div><a href="http://shop.oreilly.com/product/0790145345219.do" style="display: none;" class="url fn"><span class="fn">Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 2012</span></a></div><br clear="left"><p><strong class="summary">Good resource for 70-463 Exam</strong></p><div>By <strong>Jaype</strong> from <strong>Kenosha, WI</strong> on <strong><abbr title="2013210T1200-0800" class="dtreviewed" style="border: none; text-decoration: none;">2/10/2013</abbr></strong></div><p><div style="margin: 0.5em 0; height: 15px; width: 83px; background-image: url(http://images.powerreviews.com/images/stars_small.gif); background-position: 0px -180px;" class="prStars prStarsSmall"> </div></p><div style="display: none"><span class="rating">5</span>out of 5</div><p><strong>Pros: </strong>Well-written, Accurate, Helpful examples, Easy to understand</p><p><strong>Best Uses: </strong>Intermediate, Certification Resource, Student</p><p><strong>Describe Yourself: </strong>Sql Database Administrato</p><p style="margin-top:1em" class="description">I've passed the 70-448 exam, BI for SQL 2008. The 70-463 exam concentrates mostly on the SSIS part of the Microsoft BI stack. This MS Press Training Kit covers all of the enhancements made to SSIS in 2012. It also covers the design of a data warehouse. There's a lot here.<br xmlns:pr="xalan://com.pufferfish.core.beans.xmlbuilders.xsl.Functions"><br>The authors did a good job in matching the books sections to the exam objectives. I thought the examples were clear and easy to follow. The practice exam, included on the CD, will come in handy when I'm closer to exam time.<br><br>I think this book will be enough for me to pass the 70-463 exam. But if you have no SSIS background you may want to also use some other resources.</p><p style="margin-top:0.5em">(<a href="http://www.powerreviews.com/legal/terms_of_use.html" rel="license">legalese</a>)</p></div>jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com6tag:blogger.com,1999:blog-5892883484418619512.post-50120956395502125812010-12-31T18:03:00.002-06:002010-12-31T18:04:38.135-06:00Moving to new homeI'm moving SQL Servings to a new site, at <a href="http://www.pertell.com/sqlservings">http://www.pertell.com/sqlservings</a>. See you there.jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com1tag:blogger.com,1999:blog-5892883484418619512.post-62894802378261844252010-12-16T10:08:00.000-06:002010-12-16T10:08:51.674-06:00SQL and Relational Theory Master Class by C.J. Date<div style="margin-bottom: .0001pt; margin: 0in;"><span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">I recently had a chance to review C.J. Date's video, Relational Theory Master Class. This is an excellent resource and I highly recommend it for anyone who wishes to better understand the theory behind relational database design. But be prepared; this is not a beginner’s class. You should have a basic understanding of SQL and database design before viewing this video. And while the content is expensive at $299 I feel it’s worth it. It’s still cheaper than attending a similar seminar in person, and you can review it whenever you like.<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;">The video is a companion to Date’s book, SQL and Relational Theory, but it’s not required to read or purchase the book too. There’s 16 hours of video content, and I feel the class does a better job of describing relational theory. One of the features of the video I like is that it’s not static; that is, the camera focus is not always on the speaker or a slide deck. Moving between the speaker, the audience, and the content keeps the viewer’s interest. And you can hear the questions asked; it’s almost like you’re attending the class in person. <span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span><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;">The content is excellent. It’s not centered on any specific platform. The methods you learn here help you to better understand why your table joins work the way they do, regardless if your database is Oracle or SQL Server. Once you better understand relation theory your code will make more sense. <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;">You can find SQL and Relational Theory Master Class at <a href="http://oreilly.com/catalog/0636920002710">O'Reilly Books</a></span></div>jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-67940096836587113402010-10-04T16:23:00.001-05:002010-10-04T16:23:37.925-05:00What I learned today: No backup or restore with the DAC<p><font color="#000000">For everything I teach, I learn something in return.</font></p> <p><font color="#000000">I began to train a new junior level DBA today. He’s someone that works with us already as a developer but he’s shown an interest in learning how to be a DBA so I’m beginning to teach him what I know. To start off we began by discussing his daily tasks, then we started to talk about backup and recovery, topics I gave him as an assignment last week. For this week’s homework I told him to read up on the Dedicated Administrator Connection (DAC) and to get familiar with SQLCMD. I mentioned how the DAC was useful in situations where the server was under a strain and you need a way to connect to troubleshoot the problem. Then I said you can use it for backups and recoveries.</font></p> <p><font color="#000000">I should have quit when I was ahead.</font></p> <p><font color="#000000">As my new colleague correctly pointed out, you can’t use the DAC for backups or recovery. If you try, you’ll get the following error:</font></p> <p><font color="#ff0000">Msg 3637, Level 16, State 3, Line 1 <br />A parallel operation cannot be started from a DAC connection. <br />Msg 3013, Level 16, State 1, Line 1 <br />BACKUP DATABASE is terminating abnormally.</font></p> <p><font color="#000000">And BOL can’t make it any clearer:</font></p> <p><em>SQL Server prohibits running parallel queries or commands with the DAC. For example, error 3637 is generated if you execute either of the following statements with the DAC:</em> </p> <ul> <li><em>RESTORE</em></li> <li><em>BACKUP</em> </li> </ul> <p><em>(Ref: <a href="http://technet.microsoft.com/en-us/library/ms189595.aspx">http://technet.microsoft.com/en-us/library/ms189595.aspx</a>)</em></p> <p><font color="#000000">For some reason I’d forgotten that. It was something that I had read a long time ago and forgot since I don’t use the DAC very often, never in production other than to confirm that it was set up through the firewall. I had assumed that backing up a database in an emergency would be one of the reasons I’d use the DAC. Luckily I was reminded before a situation arose where I relied on it.</font></p> <p><font color="#000000">So I actually learned two things today. I was reminded about the restriction of the DAC, and I learned to be more careful about what I tell others when it comes to SQL, not to assume facts and to do my own homework. I hope I remember both lessons equally.  </font></p> <p> </p> <p> </p> <p><font color="#ff0000"> </font></p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-69142185530619367832010-09-30T08:58:00.001-05:002010-09-30T08:59:42.254-05:00Using the IGNORE_DUP_KEY index optionThis is a quick demo I put together for a talk on indexes earlier. It shows the behavior of the IGNORE_DUP_KEY option when you create a unique index.<br />
First, create a small sample table. I used tempdb but feel free to use whatever test database you want. Then create a unique clustered index on the field1 column.<br />
<br />
<pre class="csharpcode"><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> Table1
(
field1 <span class="kwrd">int</span> <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,
field2 <span class="kwrd">char</span>(2) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>
)
<span class="kwrd">GO</span>
<span class="kwrd">CREATE</span> <span class="kwrd">UNIQUE</span> <span class="kwrd">CLUSTERED</span> <span class="kwrd">INDEX</span> cl_Table_1 <span class="kwrd">ON</span> Table1
(
field1 <span class="kwrd">ASC</span>
)
GO</pre><br />
Next insert a few records into the table. I’m inserting records in two separate blocks to trap the intentional duplicate key error.<br />
<pre class="csharpcode"><span class="kwrd">
</span></pre><pre class="csharpcode"><span class="kwrd">BEGIN</span> TRY
INSERT <span class="kwrd">INTO</span> Table1 <span class="kwrd">VALUES</span>(1, <span class="str">'aa'</span>)
INSERT <span class="kwrd">INTO</span> Table1 <span class="kwrd">VALUES</span>(2, <span class="str">'ab'</span>)
INSERT <span class="kwrd">INTO</span> Table1 <span class="kwrd">VALUES</span>(3, <span class="str">'bb'</span>)
<span class="kwrd">END</span> TRY
<span class="kwrd">BEGIN</span> CATCH
<span class="kwrd">SELECT</span> ERROR_MESSAGE ()
<span class="kwrd">END</span> CATCH
<span class="kwrd">BEGIN</span> TRY
INSERT <span class="kwrd">INTO</span> Table1 <span class="kwrd">VALUES</span>(4, <span class="str">'cc'</span>)
INSERT <span class="kwrd">INTO</span> Table1 <span class="kwrd">VALUES</span>(3, <span class="str">'ac'</span>)
INSERT <span class="kwrd">INTO</span> Table1 <span class="kwrd">VALUES</span>(5, <span class="str">'dd'</span>)
<span class="kwrd">END</span> TRY
<span class="kwrd">BEGIN</span> CATCH
<span class="kwrd">SELECT</span> ERROR_MESSAGE ()
<span class="kwrd">END</span> CATCH
<span class="kwrd">SELECT</span> field1, field2 <span class="kwrd">FROM</span> Table1</pre><pre class="csharpcode"></pre><style type="text/css">
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
</style> You won’t get any errors in the first block because all three values are unique as defined by the key. The second block will generate the duplicate key error when it tries to insert the value 3 into field1. At this point the error is trapped and value 5 doesn’t get inserted. Viewing the values shows the 4 records. <br />
<br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 175px;" unselectable="on"><tbody>
<tr> <td valign="top" width="76">field1</td> <td valign="top" width="97">fiels2</td></tr>
<tr> <td valign="top" width="76">1</td> <td valign="top" width="97">aa</td></tr>
<tr> <td valign="top" width="76">2</td> <td valign="top" width="97">ab</td></tr>
<tr> <td valign="top" width="76">3</td> <td valign="top" width="97">bb</td></tr>
<tr> <td valign="top" width="76">4</td> <td valign="top" width="97">cc</td></tr>
</tbody></table><br />
Now lets alter the index to ignore duplicate key errors. When you use this option any error is ignored and execution of the batch continues. Only the duplicate record is not inserted. We’ll insert three more records and again trap any errors.<br />
<br />
<pre class="csharpcode"><span class="kwrd">ALTER</span> <span class="kwrd">INDEX</span> cl_Table_1 <span class="kwrd">ON</span> Table1 <span class="kwrd">SET</span> ( IGNORE_DUP_KEY = <span class="kwrd">ON</span> );
<span class="kwrd">GO</span>
<span class="kwrd">BEGIN</span> TRY
INSERT <span class="kwrd">INTO</span> Table1 <span class="kwrd">VALUES</span>(6, <span class="str">'zz'</span>)
INSERT <span class="kwrd">INTO</span> Table1 <span class="kwrd">VALUES</span>(2, <span class="str">'dd'</span>)
INSERT <span class="kwrd">INTO</span> Table1 <span class="kwrd">VALUES</span>(7, <span class="str">'cc'</span>)
<span class="kwrd">END</span> TRY
<span class="kwrd">BEGIN</span> CATCH
<span class="kwrd">SELECT</span> ERROR_MESSAGE ()
<span class="kwrd">END</span> CATCH</pre><pre class="csharpcode"></pre>This time no error is generated. The duplicate value of 2 is ignored and execution continues, meaning that the next record of 7 is also inserted. Viewing the values shows this.<br />
<br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 200px;" unselectable="on"><tbody>
<tr> <td valign="top" width="100">field1</td> <td valign="top" width="100">field2</td></tr>
<tr> <td valign="top" width="100">1</td> <td valign="top" width="100">aa</td></tr>
<tr> <td valign="top" width="100">2</td> <td valign="top" width="100">ab</td></tr>
<tr> <td valign="top" width="100">3</td> <td valign="top" width="100">bb</td></tr>
<tr> <td valign="top" width="100">4</td> <td valign="top" width="100">cc</td></tr>
<tr> <td valign="top" width="100">6</td> <td valign="top" width="100">zz</td></tr>
<tr> <td valign="top" width="100">7</td> <td valign="top" width="100">cc</td></tr>
</tbody></table>jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com1tag:blogger.com,1999:blog-5892883484418619512.post-74064897829839387792010-08-21T09:19:00.001-05:002010-08-21T09:19:52.793-05:00Does your company owe you training?<p>Recently I read an interesting post by Steve Jones (<a href="http://www.sqlservercentral.com/blogs/steve_jones/default.aspx" target="_blank">Blog</a> | <a href="http://twitter.com/way0utwest" target="_blank">Twitter</a>) on <a href="http://www.sqlservercentral.com" target="_blank">SQL Central</a> about <a href="http://www.sqlservercentral.com/articles/Editorial/70982/" target="_blank">Personal Investments</a>, how you invest in your career. It’s also something I’ve been thinking about lately. I agree with Steve that I’m responsible for my own education. But I feel that companies should be, to some extent, as well.</p> <p>I fall into the “accidental dba” category. When a senior member of our team left I was assigned to work on maintaining and monitoring our databases, something no-one really did before that. It fell to me because I had the most knowledge about SQL Server, as opposed to just writing T-SQL statements. I learned what I could by going to all the free events I could find. I joined my local SQL User Group and attended as many meetings as possible. I bought and read many excellent SQL books and blogs. I took any free or cheap training resource that was available. Eventually I passed a number of Microsoft certification exams. I’m proud of what I’ve achieved on my own, and I will continue to further my technical education.</p> <p>There’s a cost to my training, even if it’s free. There’s the hours of attending conferences and viewing webcasts. There’s the price of purchasing books, and the time it takes to read them. There’s even travel related costs, such as attending a SQL Saturday event in another state. I pay the cost gladly, and I’ve already seen the benefits.</p> <p>But at what point should a company start providing training? </p> <p>Now I don’t believe your company should always pay for your education. As Steve said in his post, you should make your own investment in your career, showing your managers the value of your training. You’re the one responsible for keeping your skills up to date. But it should be a partnership. Your company also needs to make an investment. They have as much to gain as you do.</p> <p>This isn’t just to motivate an employee, giving you a nice job perk. Frankly, if you’re not self-motivated to get the basic skills needed to do your job maybe you should start looking for another position. But if your job suddenly requires specialized knowledge they should help you attain that knowledge.           </p> <p>Let’s say, for instance, that your company has decided to use clustering for high availability. And since you’re the SQL ‘expert” they’ve come to you to set it up. You’ve read up on clustering, and you know in theory how it works. But you’ve never worked in a clustering environment, let alone design the architecture involved. Shouldn’t your company help you learn what you need to know to succeed? After all, they have more at stake in this case than you do.</p> <p>I just feel that companies should also contribute to your technical education. I’m interested in how other’s feel about it.          </p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-25918438898364701932010-08-16T19:15:00.001-05:002010-08-16T19:15:34.409-05:00What I learned today – CHECKDB and database snapshots<p>A few days ago I was sitting in my local coffee shop, getting a little light reading done. At work we’re about to move from the Standard Edition of SQL 2005 to the Enterprise Edition and I wanted to learn more about some features of database snapshots. Snapshots are basically just a read-only copy of a database taken at a specific time. Anyway I was reading <strong>Chapter 3 – Database and Database Files</strong> in the excellent book <a href="http://www.amazon.com/Microsoft-SQL-Server-2008-Internals/dp/0735626243" target="_blank">Microsoft SQL Server 2008 Internals</a>. The discussion turned to space allocated for the snapshots. That’s when I read that DBCC CHECKDB actually runs against a snapshot of the database.</p> <p>That caught me by surprise. Until then I thought that the CHECKDB ran against the database itself.</p> <p>Now I thought I was doing OK with protecting our databases. I faithfully run CHECKDB weekly against all databases and check for any errors. I set the CHECKSUM option and verify backups. I occasionally restore databases on a standby server to make sure there’s no problems with the backups. And I monitor database and drive space pretty closely.</p> <p>But I was flying blind. I have no idea how near I came to having CHECKDB fail because of a lack of space. So I started digging a little deeper into CHECKDB. And I was able to learn a little bit more of how CHECKDB works with snapshots.</p> <p>For anything concerning CHECKDB, your first resource should be Paul Randal (<a href="http://sqlskills.com/BLOGS/PAUL/" target="_blank">Blog</a> | <a href="http://twitter.com/PaulRandal" target="_blank">Twitter</a>). Read his excellent series on <a href="http://sqlskills.com/BLOGS/PAUL/category/CHECKDB-From-Every-Angle.aspx" target="_blank">CHECKDB From Every Angle</a> to get a good understanding of what’s going on. And he also wrote <strong>Chapter 11 – DBCC Internals</strong> in <a href="http://www.amazon.com/Microsoft-SQL-Server-2008-Internals/dp/0735626243" target="_blank">Microsoft SQL Server 2008 Internals</a>, a great book you should read anyway.    </p> <p>CHECKDB needs a consistent view of the database to work with. In SQL 7 it acquired table locks. In SQL 2000 it analyzed the transaction log. Beginning with SQL 2005 and going forward it creates a hidden snapshot on the same volume as the database – you have no control over where it’s placed. If you’re running CHECKDB at the same time that your server has a heavy workload the snapshot can run out of space and you’ll get an error showing that CHECKDB didn’t complete. If that happens you have a few options;</p> <ul> <li>Create your own database snapshot on a drive that has enough space and run CHECKDB against that snapshot. CHECKDB will know that its running against a snapshot and won’t create another one. </li> <li>Start the SQL service in single user mode. </li> <li>Put the database in read-only mode.  </li> </ul> <p>Once CHECKDB has finished it will delete the snapshot if it created one. </p> <p>But there are a few gotcha’s with CHECKDB creating the snapshot. First, it has to be created on an NTFS drive. Second, you can’t use the WITH TABLOCK hint. And third, you can’t run against tempdb; but why would you want to?.   </p> <p>I’m sure I’m over simplifying. I still need to finish Chapter 11.</p> <p>One other thing I’m wondering. If CHECKDB can create a snapshot database in SQL 2005 regardless of the edition, can the Standard Edition create one with a hidden flag? </p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com2tag:blogger.com,1999:blog-5892883484418619512.post-72622923353319756922010-08-09T12:23:00.001-05:002010-08-09T12:23:04.691-05:00Why I bought an iPad<h5>Disclaimer</h5> <p>I bought my new iPad a week ago. I thought I’d write a post explaining why I bought one, and kind of giving it a review against other eReaders I’ve used. But I noticed that Steve Jones wrote something similar at <a href="http://www.sqlservercentral.com/blogs/steve_jones/archive/2010/08/06/ereaders-_2D00_-kindle-v-nook-v-ipad.aspx" target="_blank">SQL Musings</a> a few days ago. I had almost finished my post when I read it, and I decided to go ahead and finish it with this disclaimer. But please read Steve’s post, he raises some excellent points. </p> <h5>Background</h5> <p>I read. A lot. Always have. And since I have a long daily commute I have a lot of time in which to read. My tastes range from light summer reading, books that are perfect for taking to the beach or on vacation, to heavily technical training manuals. </p> <p>My problem is what to carry with me. I usually use my morning commute to study or to read the news. The afternoons are most likely spent doing research. Nights and weekends are for reading the latest novel. For instance I’m currently studying for the SQL 2008 Business Intelligence certifications so I’m going through the <a href="http://http://www.amazon.com/MCTS-Self-Paced-Training-Exam-70-448/dp/0735626367/ref=sr_1_1?s=books&ie=UTF8&qid=1281287181&sr=1-1" target="_blank">MS Press Training Kit for 70-448</a>. But I’m also reading Carl Hiassen’s latest novel <a href="http://www.amazon.com/Star-Island-Carl-Hiaasen/dp/0307272583/ref=sr_1_1?s=books&ie=UTF8&qid=1281287130&sr=1-1" target="_blank">Star Island</a>. And I’ve got James Lee Burke’s <a href="http://www.amazon.com/Glass-Rainbow-Dave-Robicheaux-Novel/dp/1439128294/ref=sr_1_1?s=books&ie=UTF8&qid=1281287085&sr=1-1" target="_blank">The Glass Rainbow</a> on tap.</p> <h5>Older Devices</h5> <p>I’ve been reading eBooks for a while now, on any number of devices. I started by reading pdf files on my desktop computer. Then came Microsoft Reader and their .lit files. Of course Word documents and text files were included. </p> <p>But the problem with using a desktop computer was that it wasn’t portable. Once I got an IT job I could justify buying a laptop computer. Even more handy were PDA devices, precursors to the smart phones of today. Each of these devices also had issues. Laptops, at least the ones I had years ago, were bulky, took a while to boot up, and generally didn’t have a lot of extra storage. And PDA screens were too small for my liking. If I made the font large enough to read comfortably then I was turning pages constantly.  </p> <h5>My devices now</h5> <p>Enter the Amazon Kindle. I know that the Sony Reader came first, but it never really tempted me. I thought the price was too high and the book selection was too small. I thought the same of the Kindle 1 when it first came out. It wasn’t until the Kindle 2 was released, with a growing number of eBooks and a cheaper price, that I got behind it.</p> <p>I also had a different laptop now, an HP Pavilion TX2000 that’s a Tablet PC with a swivel screen that can run as a slate or a classic laptop. This 3 year old PC has 4 GB of RAM and a 500 GB hard drive so it can run pretty much anything I want.</p> <p>And as you can tell from the title of this post, I just bought an Apple iPad. I’ve got the 16 GB WiFi model, more than enough storage for my pdfs and other documents. </p> <p>I should also say I’ve ruled out the Nook from Barnes and Noble as too much like the Kindle. I know it can share books, but I don’t know anyone else with a Nook and I don’t think the eBook selection is as good as Amazon.</p> <h5>Kindle pros and cons</h5> <p>I’ve been using the Kindle 2 now since it came out and I love most of it. It’s light, easy to hold in one hand while reading. It has a large enough storage capacity of 1.5 GB, free 3G Whispernet connectivity and direct connect to Amazon or other sites to download a book in 60 seconds. You can adjust the font and the number of words per line, and the readability on the Kindle is the best of my three main devices. The battery life is 2 weeks with 3G off, 4 days with it on. And you can read in direct sunlight, so I can read just about anywhere. There’s no back light, but I have a clip-on for the few times I read in bed. The last Kindle upgrade added collections, a way of grouping my books by category.</p> <p>My only regret about the Kindle is that it’s not the best device to read technical books or pdfs on. Originally, I could email a pdf to a special Kindle account and it would convert it to a Kindle-formatted ebook. Later there was an update that allows me to copy a pdf directly to the Kindle. And there’s always been 3rd party tools like MobiReader and Calibre that could convert pdfs to a format that Kindles can read. However big pdfs are a problem, and converted pdfs never really convert cleanly. The 2.5 update allows zooming into pdfs and you can rotate the screen, but there’s still an uncomfortable amount of scrolling and page turning that makes reading pdfs on the Kindle very difficult, at least for me.     </p> <h5>Tablet PC pros and cons</h5> <p>I love my tablet pc. It fits my needs as a laptop computer, allowing me to run every application I want, plus it’s Wacom and touch enabled so I can take handwritten notes. Amazon and other eBook manufacturers have released a Kindle app for pcs so I can sync my Kindle books between devices. I’ve got built-in WiFi and a Sprint 3G data card so I can connect to the internet from just about anywhere. Using Adobe Reader allows me to size my pdfs to any size I need. And when I’m studying its great to have the ability to work directly in another application to try out some of the things I’m reading about in my current pdf.</p> <p>But the tablet pc is the most expensive device. It’s also has the least amount of battery life, though I have more than enough for my daily commutes. It takes the longest to start up either cold or warm. It’s the bulkiest by far. And the screen is unreadable in sunlight. </p> <h5>iPad pros and cons</h5> <p>I fought getting an iPad for a while, I really did. I thought for the most part it would just duplicate what i could already do on my tablet pc. But it does have some distinct advantages. First, it’s much lighter. Not as light as my Kindle but pounds less that my tablet. It starts up almost immediately. The larger screen makes the iPad ideal for my technical documents. I can browse the web and read emails through the WiFi connection. And like my tablet, there’s a Kindle app so I can sync up my Amazon books. </p> <p>Again, like the tablet, I can’t read the screen outdoors. It’s far more expensive than the Kindle ($499 vs $139 for the lowest priced models) but not as expensive as my tablet. Battery life is started at 10 hours. That’s acceptable for my commutes but I’d have to carry a charger for vacations. </p> <h5>My conclusions</h5> <p>I really want to have a single ebook reader but my perfect device isn’t out there just yet. I want a device that can easily read all formats, has great battery life, is small enough to be carried easily, can be read in all light conditions, has Internet connectivity through WiFi or 3G, and is affordable. Until that device is available I’ll be using my iPad for technical readings and my Kindle for my more causal readings.</p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com1tag:blogger.com,1999:blog-5892883484418619512.post-44641324935805068582010-07-27T08:54:00.001-05:002010-07-27T08:54:03.082-05:00Creating your own default trace<p>Are you aware of the default trace that SQL Server runs when it starts? Did you know it’s configurable?</p> <p>No, you can’t edit it. But you can still run a trace on startup that captures only the events that you want.</p> <p>The first thing you should do is to see if the default trace is enabled. Simple enough, just run sp_configure with advanced options and look for 'default trace enabled'. Or query sys.configurations for configuration_id 1568.</p> <pre class="csharpcode"><span class="kwrd">SELECT</span> * <br /><span class="kwrd">FROM</span> sys.configurations<br /><span class="kwrd">WHERE</span> configuration_id = 1568</pre><br /><br /><p>On my laptop it is.</p><br /><br /><p><a href="http://lh3.ggpht.com/_R-tbJU7CXwQ/TE7k6U1PLpI/AAAAAAAAArE/6jyfR0XmQOQ/s1600-h/image15.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/_R-tbJU7CXwQ/TE7k6zUCc_I/AAAAAAAAArI/udcYm_Wwhvc/image_thumb15.png?imgmax=800" width="588" height="162" /></a></p><br /><br /><p>There’s a few ways to see what the trace is doing. First find the trace id. It should be 1, but you can make sure…</p><br /><br /><pre class="csharpcode"><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> sys.traces<br /><br /><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> ::fn_trace_getinfo(0) </pre><br /><br /><pre class="csharpcode"><font face="Verdana">…then query fn_traace_geteventinfo(1) to see what events the default trace is looking at. You can see the descriptions of the events by joining to some system views. </font></pre><br /><br /><pre class="csharpcode"><span class="kwrd">SELECT</span> tc.name <span class="kwrd">AS</span> [Category],<br /> dt.eventid, te.name <span class="kwrd">AS</span> [Event Name], <br /> dt.columnid, tcol.name <span class="kwrd">AS</span> [<span class="kwrd">Column</span> Name] <br /><span class="kwrd">FROM</span> ::fn_trace_geteventinfo(1) <span class="kwrd">AS</span> dt<br /> <span class="kwrd">INNER</span> <span class="kwrd">JOIN</span> sys.trace_events <span class="kwrd">AS</span> te <span class="kwrd">ON</span> dt.eventid = te.trace_event_id<br /> <span class="kwrd">INNER</span> <span class="kwrd">JOIN</span> sys.trace_columns <span class="kwrd">AS</span> tcol <span class="kwrd">ON</span> dt.columnid = tcol.trace_column_id<br /> <span class="kwrd">INNER</span> <span class="kwrd">JOIN</span> sys.trace_categories <span class="kwrd">AS</span> tc <span class="kwrd">ON</span> te.category_id = tc.category_id </pre><br /><br /><pre class="csharpcode"><a href="http://lh4.ggpht.com/_R-tbJU7CXwQ/TE7k7eiYglI/AAAAAAAAArM/iSyiFodAqtc/s1600-h/image21.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/_R-tbJU7CXwQ/TE7k8SdwyzI/AAAAAAAAArQ/xTIDy8t98lg/image_thumb24.png?imgmax=800" width="517" height="348" /></a> </pre><br /><style type="text/css"><br /><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><style type="text/css"><br /><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><style type="text/css"><br /><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><style type="text/css"><br /><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><br /><br /><p>But what if, after looking at the events that the default trace is looking at, you decide to add to the list? Or maybe you want to skip some events? Remember, you can’t alter the default trace.  </p><br /><br /><p>First disable the default trace. Use sp_configure, the change doesn’t require a restart. It will also close the trace.</p><br /><br /><pre class="csharpcode">sp_configure <span class="str">'default trace enabled'</span>, 0;<br /><span class="kwrd">go</span><br /><br />reconfigure</pre><br /><br /><pre class="csharpcode"><font face="Verdana">Next create a stored procedure in the master database that will create and start your trace. Use sp_trace_create to generate a trace id, use sp_trace_setevent to select the events you want to capture, and finally start the trace with sp_trace_setstatus. Read more about creating traces <a href="http://msdn.microsoft.com/en-us/library/ms190362.aspx" target="_blank">here</a>. If you aren’t familiar with creating traces via T-SQL use SQL Profiler to capture the events you need and set whatever properties you need then extract the trace properties to a script file.</font></pre><br /><br /><pre class="csharpcode"><font face="Verdana">Finally mark your new procedure to start when SQL does by using sp_procoption. This procedure takes 3 parameters; the name of your procedure, the option ‘startup’, and a true or false value. ‘Startup’ is the only option available, true sets to autostart, and false sets to not autostart. </font></pre><br /><br /><pre class="csharpcode"><font face="Verdana">In my demo script below I’ve created a trace that will track what a certain user does in the database as well as when they log in or out. </font></pre><br /><br /><pre class="csharpcode">USE [master]<br />GO<br /><br />CREATE PROCEDURE [dbo].[MyDefaultTrace]<br />AS<br /><br />begin <br /> declare @rc int<br /> declare @TraceID int<br /> declare @maxfilesize bigint<br /> declare @filecount int<br /> set @maxfilesize = 100<br /> set @filecount = 5<br /><br /> exec @rc = sp_trace_create @TraceID output, 2, <br /> N'C:\Perflogs\MyDefaultTrace', <br /> @maxfilesize, NULL, @filecount<br /><br /> declare @on bit<br /> set @on = 1<br /> exec sp_trace_setevent @TraceID, 14, 7, @on<br /> exec sp_trace_setevent @TraceID, 14, 23, @on<br /> exec sp_trace_setevent @TraceID, 14, 8, @on<br /> exec sp_trace_setevent @TraceID, 14, 64, @on<br /> exec sp_trace_setevent @TraceID, 14, 1, @on<br /> exec sp_trace_setevent @TraceID, 14, 9, @on<br /> exec sp_trace_setevent @TraceID, 14, 21, @on<br /> exec sp_trace_setevent @TraceID, 14, 25, @on<br /> exec sp_trace_setevent @TraceID, 14, 41, @on<br /> exec sp_trace_setevent @TraceID, 14, 49, @on<br /> exec sp_trace_setevent @TraceID, 14, 57, @on<br /> exec sp_trace_setevent @TraceID, 14, 2, @on<br /> exec sp_trace_setevent @TraceID, 14, 6, @on<br /> exec sp_trace_setevent @TraceID, 14, 10, @on<br /> exec sp_trace_setevent @TraceID, 14, 14, @on<br /> exec sp_trace_setevent @TraceID, 14, 26, @on<br /> exec sp_trace_setevent @TraceID, 14, 66, @on<br /> exec sp_trace_setevent @TraceID, 14, 3, @on<br /> exec sp_trace_setevent @TraceID, 14, 11, @on<br /> exec sp_trace_setevent @TraceID, 14, 35, @on<br /> exec sp_trace_setevent @TraceID, 14, 51, @on<br /> exec sp_trace_setevent @TraceID, 14, 12, @on<br /> exec sp_trace_setevent @TraceID, 14, 60, @on<br /> exec sp_trace_setevent @TraceID, 15, 7, @on<br /> exec sp_trace_setevent @TraceID, 15, 15, @on<br /> exec sp_trace_setevent @TraceID, 15, 23, @on<br /> exec sp_trace_setevent @TraceID, 15, 8, @on<br /> exec sp_trace_setevent @TraceID, 15, 16, @on<br /> exec sp_trace_setevent @TraceID, 15, 64, @on<br /> exec sp_trace_setevent @TraceID, 15, 9, @on<br /> exec sp_trace_setevent @TraceID, 15, 17, @on<br /> exec sp_trace_setevent @TraceID, 15, 41, @on<br /> exec sp_trace_setevent @TraceID, 15, 49, @on<br /> exec sp_trace_setevent @TraceID, 15, 57, @on<br /> exec sp_trace_setevent @TraceID, 15, 6, @on<br /> exec sp_trace_setevent @TraceID, 15, 10, @on<br /> exec sp_trace_setevent @TraceID, 15, 14, @on<br /> exec sp_trace_setevent @TraceID, 15, 18, @on<br /> exec sp_trace_setevent @TraceID, 15, 26, @on<br /> exec sp_trace_setevent @TraceID, 15, 66, @on<br /> exec sp_trace_setevent @TraceID, 15, 3, @on<br /> exec sp_trace_setevent @TraceID, 15, 11, @on<br /> exec sp_trace_setevent @TraceID, 15, 35, @on<br /> exec sp_trace_setevent @TraceID, 15, 51, @on<br /> exec sp_trace_setevent @TraceID, 15, 12, @on<br /> exec sp_trace_setevent @TraceID, 15, 60, @on<br /> exec sp_trace_setevent @TraceID, 15, 13, @on<br /> exec sp_trace_setevent @TraceID, 15, 21, @on<br /> exec sp_trace_setevent @TraceID, 43, 7, @on<br /> exec sp_trace_setevent @TraceID, 43, 15, @on<br /> exec sp_trace_setevent @TraceID, 43, 8, @on<br /> exec sp_trace_setevent @TraceID, 43, 64, @on<br /> exec sp_trace_setevent @TraceID, 43, 1, @on<br /> exec sp_trace_setevent @TraceID, 43, 9, @on<br /> exec sp_trace_setevent @TraceID, 43, 41, @on<br /> exec sp_trace_setevent @TraceID, 43, 2, @on<br /> exec sp_trace_setevent @TraceID, 43, 10, @on<br /> exec sp_trace_setevent @TraceID, 43, 3, @on<br /> exec sp_trace_setevent @TraceID, 43, 11, @on<br /> exec sp_trace_setevent @TraceID, 43, 35, @on<br /> exec sp_trace_setevent @TraceID, 43, 51, @on<br /> exec sp_trace_setevent @TraceID, 43, 12, @on<br /> exec sp_trace_setevent @TraceID, 43, 5, @on<br /> exec sp_trace_setevent @TraceID, 43, 13, @on<br /> exec sp_trace_setevent @TraceID, 43, 6, @on<br /> exec sp_trace_setevent @TraceID, 43, 14, @on<br /> exec sp_trace_setevent @TraceID, 43, 62, @on<br /> exec sp_trace_setevent @TraceID, 45, 7, @on<br /> exec sp_trace_setevent @TraceID, 45, 8, @on<br /> exec sp_trace_setevent @TraceID, 45, 64, @on<br /> exec sp_trace_setevent @TraceID, 45, 1, @on<br /> exec sp_trace_setevent @TraceID, 45, 41, @on<br /> exec sp_trace_setevent @TraceID, 45, 10, @on<br /> exec sp_trace_setevent @TraceID, 45, 3, @on<br /> exec sp_trace_setevent @TraceID, 45, 11, @on<br /> exec sp_trace_setevent @TraceID, 45, 35, @on<br /> exec sp_trace_setevent @TraceID, 45, 51, @on<br /> exec sp_trace_setevent @TraceID, 45, 12, @on<br /> exec sp_trace_setevent @TraceID, 45, 5, @on<br /> exec sp_trace_setevent @TraceID, 45, 13, @on<br /> exec sp_trace_setevent @TraceID, 45, 6, @on<br /> exec sp_trace_setevent @TraceID, 45, 14, @on<br /> exec sp_trace_setevent @TraceID, 45, 62, @on<br /> exec sp_trace_setevent @TraceID, 45, 15, @on<br /> exec sp_trace_setevent @TraceID, 41, 7, @on<br /> exec sp_trace_setevent @TraceID, 41, 15, @on<br /> exec sp_trace_setevent @TraceID, 41, 8, @on<br /> exec sp_trace_setevent @TraceID, 41, 64, @on<br /> exec sp_trace_setevent @TraceID, 41, 1, @on<br /> exec sp_trace_setevent @TraceID, 41, 41, @on<br /> exec sp_trace_setevent @TraceID, 41, 10, @on<br /> exec sp_trace_setevent @TraceID, 41, 3, @on<br /> exec sp_trace_setevent @TraceID, 41, 11, @on<br /> exec sp_trace_setevent @TraceID, 41, 35, @on<br /> exec sp_trace_setevent @TraceID, 41, 51, @on<br /> exec sp_trace_setevent @TraceID, 41, 12, @on<br /> exec sp_trace_setevent @TraceID, 41, 5, @on<br /> exec sp_trace_setevent @TraceID, 41, 13, @on<br /> exec sp_trace_setevent @TraceID, 41, 6, @on<br /> exec sp_trace_setevent @TraceID, 41, 14, @on<br /><br /><br /> -- Set the Filters<br /> declare @intfilter int<br /> declare @bigintfilter bigint<br /><br /> exec sp_trace_setfilter @TraceID, 11, 0, 6, N'BadUser'<br /> <br /> exec sp_trace_setstatus @TraceID, 1<br /><br />end<br /><br />GO<br /><br />EXEC sp_procoption N'MyDefaultTrace', 'startup', '1'<br /><br />GO</pre><br /><style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><style type="text/css"><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><style type="text/css"><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><br /><br /><p>Now when I check to see what traces are running trace id #1 is the trace created in my procedure. The trace start is also written to the SQL error log when the master database starts.</p><br /><br /><p><a href="http://lh5.ggpht.com/_R-tbJU7CXwQ/TE7k9UIZz1I/AAAAAAAAArU/V-P2TWRqcD4/s1600-h/image%5B3%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/_R-tbJU7CXwQ/TE7k-WczRcI/AAAAAAAAArY/-VB83KNz3Mc/image_thumb%5B1%5D.png?imgmax=800" width="513" height="451" /></a>  </p><br /><br /><p>As always, please test this on a server you can play with before putting into production. </p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-86401290796782552402010-07-21T21:02:00.001-05:002010-07-21T21:02:00.649-05:00Winning the DBA Open<p>Last weekend I, like millions of other golf fans around the world, watched as Louis Oosthuizen, an unknown (at least to me) golfer out of South Africa, won The Open Championship at St Andrews by an astounding 7 strokes. He showed confidence in his abilities throughout the tournament, never losing his composure playing in some of the toughest conditions on any course in the world. Congratulations, Louis!</p> <p> <img style="display: block; float: none; margin-left: auto; margin-right: auto" src="http://picsrv.opengolf.com/img/DownloadFile.dwn?filename=CROP_82279a3a-6c8e-4799-817b-ab1b7e1d859a_A006850_13_27.jpg" /></p> <p>As i watched the game, I began to think that being a DBA and being a champion golfer weren’t that different. Both need to be confident in their abilities, both need to have a solid game plan, and both have to be able to execute their craft under pressure, being prepared for any eventuality.</p> <h4>Preparing for the tournament</h4> <p>A good golfer gets to the course early, days before the tournament begins. They play the course, talk to locals to learn any particular quirks the course may have, and practices hundreds of shots of different lengths or difficulties.</p> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="golfer" src="http://www.designedtoat.com/clipart3/golf_14.gif" /></p> <p>Good DBAs do the same. They find out about the host server, talking to the system team or SAN administrator; what kind of disk subsystem is there? what’s the memory or CPU like? does the server meet the requirements for SQL?</p> <h4>You drive for show…</h4> <p>The golfer starts his round on the tee. He’ll determine the club to use based on experience and conditions; where’s the wind coming from? how long is the hole? where’s the danger spots? A well hit drive will end up far down the fairway, perfectly placed for his next shot.  </p> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Click to view" src="http://classroomclipart.com/images/gallery/Clipart/Sports/Golf_Clipart/TN_crca_golfer_clipart.jpg" /></p> <p>The DBA does the same when he sets up the server. His experience comes into play knowing what has to be done, and server conditions will help him to correctly place the system databases and data and log files for other databases. He’ll know what options to set and what to set them to. His server will be set up to optimal conditions, perfectly placed for hosting the company data.</p> <h4>…the approach shot…</h4> <p>Now the golfer will try to get his ball onto the green. He’ll again choose the right club, line up the shot, take a practice swing or two, then hits his best shot. He’ll know how his ball will fly because he has hit the same shot hundreds of times before so he can compare against past performance.</p> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Click to view" src="http://classroomclipart.com/images/gallery/Clipart/Sports/Golf_Clipart/TN_13-11-08_R12A.jpg" /></p> <p>The DBAs approach shot is to benchmark his system. He should know how the server will perform during peak and non-peak times. </p> <h4>---getting out of a hazard…</h4> <p>Sometimes, though, the golfer’s approach shot is mishit. Or maybe a gust of wind holds up the shot and it lands in a greenside bunker. A good golfer knows how to get out of those situations and save par.</p> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="humorous golf hazard" src="http://www.webweaver.nu/clipart/img/entertainment/sports/golf/golf-hazard.gif" /></p> <p>Similarly, a DBA must know how to get out of disaster scenarios. He knows he has set up and is maintaining  a proper backup strategy and has practiced recovering databases until he knows the procedure inside and out. He knows the different options available for DBCC CHECKDB. </p> <h4>---and you putt for dough</h4> <p>This is where the good golfers shine. They don’t fade when standing over a three foot putt with the tournament on the line. They know they’ve made the same putt over and over before, and they’ve practiced for just this occasion. They read the putt’s line, judge the speed, and with millions of eyes watching they calmly knock it hit it center cup.  </p> <p></p> <p></p> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Click to view" src="http://classroomclipart.com/images/gallery/Clipart/Sports/Golf_Clipart/TN_22-3-07_4.jpg" /></p> <p>The DBA also is good under pressure. They’ve written and checked over their scripts, and they’re confidant they’ll perform as written. They don’t wilt when the CIO is standing over their shoulder asking why performance is so bad on their server.  </p> <h4>The caddy…</h4> <p>All good golfers know the value of their caddy. A caddy’s job is to help his golfer, pointing out the course yardage, offering the right club, taking some of the pressure of the golfer. Sometimes the caddy is also a teacher, pointing out flaws in the golfer’s swing.</p> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Golf Caddies Baltusrol" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhugjSOb9gxpaGNrHeFm6xO8S_0QBtufuRXxYMKeCXzYdTBo-IYRrDxV4EJChxcMatGlmZO6UxbgK0vuFDetpY8XbGjEAQeCmjLlMXWPexOQOBoTzy0pIG3-5vKPCE-1rT2QKnaqQ8cuVzy/s400/golf_Caddies_Baltusrol_2.jpg" /></p> <p>A DBA also needs a caddy. Their caddy can be a mentor or coworker at work. They can use social networks for advice on specific problems or as a sounding board.       </p> <h4>Preparing for the next tournament</h4> <p>The golfer is always practicing. He’s at the driving range on his day off, constantly working on correcting swing flaws and working on new shots.</p> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" src="http://imagecache6.allposters.com/LRG/27/2744/M2BTD00Z.jpg" /></p> <p>Likewise the DBA is constantly learning. They attend their local PASS chapter meetings or go to national conventions. They’re constantly reading blogs and whitepapers, learning new skills to help them manage their servers or help their company. </p> <p></p> <p></p> <p></p> <p></p> <p></p> <p>The moral? Like in golf, or any endeavor, the secret of being a good DBA is to be prepared. Learn your craft. Ask your caddy when you have questions. Practice when you’re able. Keep learning. And you too can win the DBA Open.</p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-46895786378998342012010-07-19T16:38:00.001-05:002010-07-20T08:30:14.664-05:00What I learned today – importing mixed data types from Excel<p>Recently a coworker came up with an issue I never ran into before. He’s importing a large number of Excel spreadsheets into a SQL 2005 instance. One of the columns contains mixed data; 1, 2, 3, x, y, z for example. What he found is that if the first 20 or so rows are strictly numeric then the connection treats the whole column as numeric, and alphanumeric values are returned as NULLs. He’s querying using OPENROWSET, but it looks like this happens in SSIS as well.  </p> <p>What his research found was that there’s a few registry keys that come into play. There’s also another property he needed to set in his connection string. When he adds “IMEX=1” it allows importing of mixed data types based on the the registry setting “ImportMixedTypes”. The default value is “text”. One drawback to text is that you’re limited to 255 characters. The only other available value is “Majority Type”. When you use Majority Type some of the values are scanned and the data type with the most values is used. </p> <p>The number of rows scanned is based on the “TypeGuessRow” registry setting. By default it’s set to 8, but valid values are 0 to 16. If you set it to 0 then all rows are sampled. Setting it to a value between 1 and 16 samples that number.</p> <p>Both keys can be found at HKEY_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/.   </p> <p>Here are a few articles that explain the issue better.</p> <p><a href="http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!318.entry?sa=781031802">http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!318.entry?sa=781031802</a></p> <p><a href="http://www.instantpages.ltd.uk/ADODB_WP.htm">http://www.instantpages.ltd.uk/ADODB_WP.htm</a></p> <p><strong>UPDATED 7/20/2010:</strong> You can read more about the actual issue <a href="http://irwinj.blogspot.com/2010/07/sql-server-excel-import-and-mixed-data.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+HellocruelWorld+(Hello+(cruel)+World)" target="_blank">here</a>.</p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com3tag:blogger.com,1999:blog-5892883484418619512.post-16568254376040708152010-07-14T07:22:00.001-05:002010-07-14T07:22:43.189-05:00Changing the name of your SQL server<p>My company recently changed their standard naming conventions for computers, so yesterday I had to rename my workstation. Usually this isn’t a big deal, except that I’m running locally a default instance of SQL 2005 and a named instance of SQL 2008.Again, not a big deal since this is just my local playground. But I wanted to sync up the names.</p> <p>Let’s say that my laptop was named “CHICAGO”. That makes the default instance also “CHICAGO'”, and my named instance “CHICAGO\KATMAI”. Now my laptop name changed to “NEWCHICAGO”. My SQL instances stay as “CHICAGO” and “CHICAGO\KATMAI”. How do you change them to match the new computer name?</p> <p>Couldn’t be simpler, just execute two procedures. For the default instance.</p> <pre class="csharpcode"><span class="kwrd">USE</span> master;<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">EXEC</span> sp_dropserver <span class="str">'CHICAGO'</span>;<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">EXEC</span> sp_addserver <span class="str">'NEWCHICAGO'</span>, <span class="kwrd">local</span>;<br /><span class="kwrd">GO</span></pre><br /><style type="text/css"><br /><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style>It’s the same for a named instance. Just add the instance name. <br /><br /><pre class="csharpcode"><span class="kwrd">USE</span> master;<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">EXEC</span> sp_dropserver <span class="str">'CHICAGO\KATMAI'</span>;<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">EXEC</span> sp_addserver <span class="str">'NEWCHICAGO\KATMAI'</span>, <span class="kwrd">local</span>;<br /><span class="kwrd">GO</span><br /><font face="Verdana">Then, just restart the SQL service and you should see the name change.</font></pre><br /><br /><pre class="csharpcode"><font face="Verdana">Note, though, that this only changes the server name, not the instance name. I haven’t had an opportunity to try that yet.</font></pre><br /><style type="text/css"><br /><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-89896355678589584482010-07-13T09:13:00.001-05:002010-07-13T09:13:36.546-05:00Another use for PowerShell and SQL<p>A few months ago I wrote a PowerShell script that looped through servers and databases  to save the properties inside an Excel spreadsheet (<a href="http://sqlservings.blogspot.com/2010/03/powershell-and-sql.html" target="_blank">PowerShell and SQL</a>). I thought it’s time to add another one.</p> <p>One of the daily tasks I set up for myself as a SQL DBA is to go through the error logs for anything that may be an indicator of a problem. This is especially true on Sunday afternoons or Monday mornings, after my weekly DBCC CHECKDB job runs. I’ve configured the jobs to email me the results, but since I’m monitoring 10 instances they can be time consuming to read through. I usually just cut to the bottom, where DBCC reports the number of errors it finds. If the number of errors is 0, all is well and good. Otherwise, of course, further investigation is needed.</p> <p>This seemed like a natural for a new PowerShell script. One of the great strengths of PowerShell is it’s ability to connect to as many remote servers as needed. And since I knew that the DBCC CHECKDB summary is written in the error log all I need to do is to read the logs. I had read this <a href="http://sqlblog.com/blogs/buck_woody/archive/2010/03/29/open-the-sql-server-error-log-with-powershell.aspx" target="_blank">post</a> by Buck Woody (<a href="http://sqlblog.com/blogs/buck_woody/default.aspx" target="_blank">blog</a> | <a href="http://twitter.com/buckwoody" target="_blank">twitter</a>) about doing almost exactly what I wanted to do. I merged his script with my earlier script and made a few other changes. My final code looks like this:</p> <pre class="csharpcode">[System.Reflection.Assembly]::LoadWithPartialName(<span class="str">'Microsoft.SqlServer.SMO'</span>) | out-null <br /><br /><span class="rem">#Get SQL account information</span><br />$credential = Get-Credential<br />$loginName = $credential.UserName -replace(<span class="str">"\\","</span><span class="str">") <br />$password = $credential.Password <br /><br />#Get variables<br />$logDate = read-host("</span>Enter log date<span class="str">")<br />$searchPhrase = "</span>*<span class="str">"<br />$searchPhrase += read-host("</span>Enter search phrase<span class="str">")<br />$searchPhrase += "</span>*<span class="str">"<br />$srvlist = @(get-content "</span>C:\Power\SQL_Servers.txt") <br /><br /><span class="rem">#Loop through all instances </span><br /><span class="kwrd">foreach</span> ($instance <span class="kwrd">in</span> $srvlist) <br />{<br /> $instance<br /> $srv = New-Object (<span class="str">'Microsoft.SqlServer.Management.Smo.Server'</span>) $instance<br /> $srv.ConnectionContext.LoginSecure = $false<br /> $srv.ConnectionContext.set_Login($loginName)<br /> $srv.ConnectionContext.set_SecurePassword($password) <br /> $srv.ReadErrorLog(0) | where {$_.LogDate <span class="preproc">-gt</span> $logDate -and $_.Text <span class="preproc">-like</span> $searchPhrase} <br />}</pre><br /><style type="text/css"><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><br /><br /><pre class="csharpcode"><font face="Verdana">Since I don’t always access the servers from a computer on the domain, I added login credentials. You can remove these if you want to use Windows authentication. After that I read a date and search phrase I type in. I found that this can come in handy when I want to change what I’m searching for. For instance, I can enter the phrase “DBCC CHECKDB” and the script will return the summaries based on the date I entered. Or I can search for “BACKUP” or “ERROR”, whatever my heart desires.</font></pre> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com1tag:blogger.com,1999:blog-5892883484418619512.post-10572838225304482152010-07-06T11:02:00.001-05:002010-07-06T11:02:08.751-05:00Stop SQL Agent when enabling Service Broker on msdb<p>I forget this every time I set up a new server. </p> <p>When I set up database mail on anew server I usually get the message “Service Broker is not enabled on msdb. Do you want to enable it?” Clicking OK gives me an unresponsive SQL server. Running the command “ALTER DATABASE msdb SET Enable_Broker” runs forever with no message returned.</p> <p>What I forget each and every time is that the SQL Agent is running. Checking the locks or running sp_who2 shows that my ALTER DATABASE is blocked by the process running the Agent. Simply stopping the Agent allows the statement to finish, whether from the query window or clicking OK in the dialog box mentioned earlier. Then I just restart the Agent and everything’s hunky-dory again.</p> <p>Maybe now I’ll remember it. </p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-11484597753747965292010-07-05T10:35:00.001-05:002010-07-05T10:35:18.169-05:00My favorite SQL features<p>Recently I ran across a contest sponsored by Microsoft SQL Server MVP Ken Simmons (<a href="http://cybersql.blogspot.com" target="_blank">Blog</a> | <a href="http://twitter.com/kensimmons" target="_blank">Twitter</a>). One of the ways to enter the contest was to create a blog post listing favorite SQL features. But how do you select a favorite? That’s like asking someone to choose a favorite song. Like most things the the SQL world, the correct answer is “it depends”, usually on what I’m trying to do at the time. </p> <p>So here’s my top 10 favorite SQL features and tips, in the order that they come to mind. I’m ignoring features I’m not currently using, like backup compression and data encryption, and limiting myself to those I use the most often.</p> <ol> <li><strong>Integration with PowerShell</strong>. This is a great feature to administer multiple SQL instances easily. For those who haven’t been on the internet in the last few years, PowerShell is Microsoft’s new scripting language based on the .NET framework. And starting with SQL 2008, PowerShell became integrated with SQL. What does this mean to you as a DBA? Well, imagine yourself as the new head DBA for your company, and you’re now in charge of 100 SQL instances. Your boss tells you that your first task is to standardize all instances. How can you quickly view the properties of each server? With PowerShell and remoting, you can easily query each instance and save the results to an Excel spreadsheet for easy analysis. I gave an example of how I do it in an earlier blog post <a href="http://sqlservings.blogspot.com/2010/03/powershell-and-sql.html" target="_blank">here</a>.       </li> <li><strong>Dynamic Management Views and Functions (DMV).</strong> Introduced with SQL 2005, DMVs are a great way to see how your server is performing. I’m not going to go into them in depth here since they’ve been written about by many others much better than I can. If you’re not familiar with them, do yourself a favor and start reading about them. They’ll make your life much easier.</li> <li><strong>Storing queries in the toolbox</strong>. Like all DBAs, I’ve got a number of queries that I run over and over again. There’s numerous ways to store these for easy access, but my favorite for short one-line queries is to store them on the toolbox. Simply open the toolbox (Ctrl + Alt + x) and drag your query over. To recall it, open the toolbox, copy the desired query, and paste it into a new query window.</li> <li><strong>Policies.</strong> Introduced in SQL 2008, Policy Based Management allows DBAs to manage multiple servers from a central server. Policies can standardize server configurations and check them for compliance.    </li> <li><strong>Report Services.</strong> Built-in reporting capabilities for the same price as the database engine. ‘nuff said. </li> <li><strong>IntelliSense.</strong> This feature gives the same statement completion that you see in Visual Studio, not surprising since SQL Management Studio uses the same IDE and Visual Studio. Unfortunately it doesn’t work against 2005 servers or earlier. It did in the earlier betas but was removed in the final release. Hopefully Microsoft will put it back. </li> <li><strong>Object Explorer Details</strong>. You can now see details of different database objects using the details view (F7). It’s also configurable. Just right click on the screen and choose what columns you want to see.    <a href="http://lh6.ggpht.com/_R-tbJU7CXwQ/TDH7sMjkiHI/AAAAAAAAAqs/xA1FM1BHdyk/s1600-h/image%5B17%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_R-tbJU7CXwQ/TDH7s8WR0vI/AAAAAAAAAqw/dyr_Fll6Zy0/image_thumb%5B7%5D.png?imgmax=800" width="602" height="366" /></a>    </li> <li><strong>Running queries against multiple instances.</strong> If you set up Registered Groups and register SQL instances by groups, you can execute a query against all servers in the group. You can select all groups, but you can’t choose to ignore instances inside the group. It’s all or nothing. But still, a significant timesaver when you need to run multiple statements against multiple instances.   </li> <li><strong>Setting variable values.</strong> One of the nice things in the .NET world is the ability to use easily change the value of a variable; i += 1 for instance. Now you can do that in SQL too. Instead of  </li> <pre class="csharpcode"><span class="kwrd">SET</span> @i = @i + 1</pre><br /><br /> <pre class="csharpcode"><font face="Verdana">you can now do it like this</font> </pre><br /><br /> <pre class="csharpcode"><span class="kwrd">SET</span> @i += 1</pre><br /> <style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><br /><br /> <li><strong>SQL Community.</strong> Not technically part of SQL itself, but I feel it’s important nevertheless. I first began learning about SQL on my own and quickly found myself overwhelmed. There was no time to learn at work, and I needed to find the knowledge to do my job. I found the <a href="http://chicago.sqlpass.org/" target="_blank">Chicago SQL User Group</a> and found what I needed; experienced DBAs volunteering to talk about a subject they love. ChiSUG lead me to <a href="http://www.sqlpass.org/" target="_blank">PASS</a>, an international group with tons of free resources. I began following blogs written by the same people authoring the articles on PASS and presenting at the group meetings. Reading these blogs lead me to twitter, where I can follow SQL experts in real-time and ask for help with the #SQLhelp tag. Finally there’s <a href="http://www.sqlsaturday.com/" target="_blank">SQL Saturdays</a>, day-long events with SQL experts speaking on various topics. Notice the recurring theme about these resources; they are all free. I’m constantly amazed by the amount of content that SQL DBAs and developers make available for no cost, and the time they put in as volunteers at in-person events. To the entire SQL community, thank you. I wouldn’t be where I am today without your help.  </li><br /></ol> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-83579236251222480642010-06-29T14:35:00.001-05:002010-06-29T14:35:52.874-05:00PIVOT compared to CASE for a crosstab result<p>Yesterday I was playing with a test query for a coworker using the PIVOT keyword when it dawned on me that I never compared it to using CASE statements. So, here’s the requirements.</p> <p>There is a table called lab_results, with a partial DDL statement below</p> <pre class="csharpcode"><strong><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> [dbo].[lab_results](<br /> [lab_result_id] [<span class="kwrd">char</span>](12) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,<br /> [patient_id] [<span class="kwrd">char</span>](8) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,<br /> [test_code] [<span class="kwrd">numeric</span>](4, 0) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,<br /> [sample_date] [datetime] <span class="kwrd">NULL</span>,<br /> .............................<br /> .............................<br /> <span class="kwrd">CONSTRAINT</span> [PK_lab_results] <span class="kwrd">PRIMARY</span> <span class="kwrd">KEY</span> <span class="kwrd">CLUSTERED</span> <br />(<br /> [lab_result_id] <span class="kwrd">ASC</span><br />) <span class="kwrd">ON</span> [<span class="kwrd">PRIMARY</span>]<br /><br /><span class="kwrd">CREATE</span> <span class="kwrd">NONCLUSTERED</span> <span class="kwrd">INDEX</span> [IX_lab_results_3] <span class="kwrd">ON</span> [dbo].[lab_results] <br />(<br /> [test_code] <span class="kwrd">ASC</span>,<br /> [sample_date] <span class="kwrd">ASC</span><br />) <span class="kwrd">ON</span> [Secondary]</strong></pre><br /><br /><pre class="csharpcode">This table has approximately 18 million rows, and you need to get a count of test codes during each year. Anything prior to 1/1/2006 would be counted together. The data would look something like this</pre><br /><br /><pre class="csharpcode"><a href="http://lh5.ggpht.com/_R-tbJU7CXwQ/TCpLC0PjFlI/AAAAAAAAAqU/kRMAKzOqLuY/s1600-h/image%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_R-tbJU7CXwQ/TCpLDgwwydI/AAAAAAAAAqY/SsOaRrjldak/image_thumb%5B2%5D.png?imgmax=800" width="430" height="366" /></a> </pre><br /><br /><pre class="csharpcode">Prior to SQL 2005, I would use a series of CASE statements to perform a count of each test code</pre><br /><br /><pre class="csharpcode"><strong><span class="kwrd">SELECT</span> test_code<br /> , <span class="kwrd">SUM</span>(<span class="kwrd">CASE</span> <span class="kwrd">WHEN</span> DATEPART(yy, sample_date) = 2010 <span class="kwrd">THEN</span> 1 <span class="kwrd">END</span>) <span class="kwrd">AS</span> [2010]<br /> , <span class="kwrd">SUM</span>(<span class="kwrd">CASE</span> <span class="kwrd">WHEN</span> DATEPART(yy, sample_date) = 2009 <span class="kwrd">THEN</span> 1 <span class="kwrd">END</span>) <span class="kwrd">AS</span> [2009]<br /> , <span class="kwrd">SUM</span>(<span class="kwrd">CASE</span> <span class="kwrd">WHEN</span> DATEPART(yy, sample_date) = 2008 <span class="kwrd">THEN</span> 1 <span class="kwrd">END</span>) <span class="kwrd">AS</span> [2008]<br /> , <span class="kwrd">SUM</span>(<span class="kwrd">CASE</span> <span class="kwrd">WHEN</span> DATEPART(yy, sample_date) = 2007 <span class="kwrd">THEN</span> 1 <span class="kwrd">END</span>) <span class="kwrd">AS</span> [2007]<br /> , <span class="kwrd">SUM</span>(<span class="kwrd">CASE</span> <span class="kwrd">WHEN</span> DATEPART(yy, sample_date) = 2006 <span class="kwrd">THEN</span> 1 <span class="kwrd">END</span>) <span class="kwrd">AS</span> [2006]<br /> , <span class="kwrd">SUM</span>(<span class="kwrd">CASE</span> <span class="kwrd">WHEN</span> DATEPART(yy, sample_date) < 2006 <span class="kwrd">THEN</span> 1 <span class="kwrd">END</span>) <span class="kwrd">AS</span> [Pre2006]<br /><span class="kwrd">FROM</span> lab_results<br /><span class="kwrd">GROUP</span> <span class="kwrd">BY</span> test_code</strong></pre><br /><br /><pre class="csharpcode">Using the new (to SQL 2005 anyway) PIVOT keyword, I could rewrite the query like this</pre><br /><br /><pre class="csharpcode"><strong><span class="kwrd">SELECT</span> test_code, [2010], [2009], [2008], [2007], [2006], [2005] <span class="kwrd">AS</span> Pre2006<br /><span class="kwrd">FROM</span> <br />(<br /> <span class="kwrd">SELECT</span> test_code, sample_date<br /> , <span class="kwrd">CASE</span> <span class="kwrd">WHEN</span> DATEPART(yy, sample_date) < 2006 <span class="kwrd">THEN</span> 2005<br /> <span class="kwrd">ELSE</span> DATEPART(yy, sample_date) <br /> <span class="kwrd">END</span> <span class="kwrd">AS</span> LabResultYear <br /> <span class="kwrd">FROM</span> lab_results<br />) <span class="kwrd">AS</span> tmp<br />PIVOT (<span class="kwrd">COUNT</span>(sample_date) <span class="kwrd">FOR</span> LabResultYear <span class="kwrd">IN</span> ([2010], [2009], [2008], [2007], [2006], [2005])) <span class="kwrd">AS</span> p;</strong></pre><br /><br /><pre class="csharpcode">But I was never looked at the execution plan before yesterday to see how each was run. So I finally did, and here’s what I saw. First the plan for the CASE statement…</pre><br /><br /><pre class="csharpcode"><a href="http://lh6.ggpht.com/_R-tbJU7CXwQ/TCpLEObzjgI/AAAAAAAAAqc/MCN-r1suwRQ/s1600-h/image%5B10%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_R-tbJU7CXwQ/TCpLE2tkF-I/AAAAAAAAAqg/CrSRpErL644/image_thumb%5B6%5D.png?imgmax=800" width="642" height="442" /></a> </pre><br /><br /><pre class="csharpcode">…and then the plan for the PIVOT example.</pre><br /><br /><pre class="csharpcode"> <a href="http://lh4.ggpht.com/_R-tbJU7CXwQ/TCpLFcwYm4I/AAAAAAAAAqk/Vw4ULVOjQvs/s1600-h/image%5B15%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_R-tbJU7CXwQ/TCpLF5JQzLI/AAAAAAAAAqo/mZiSTyeC6eM/image_thumb%5B9%5D.png?imgmax=800" width="648" height="398" /></a> </pre><br /><br /><p></p><br /><br /><p></p><br /><br /><p>At first glance they seem identical. Both are using the same operators and both are using an index seek on the non-clustered index. However there are small differences. The CASE statement completes 2 seconds faster than the PIVOT query, 45 seconds to 47. The index seek,while having an overall higher percentage of the whole, has the same operator costs as the PIVOT. The difference shows in the Stream Aggregate operator. The CASE operator is 10.8, which is 10% of the whole query. In the PIVOT example, the Stream Aggregate is 18% of the whole, and the operator cost is 19.8.</p><br /><br /><p>One other point about the PIVOT keyword. If you compare my code snippets to the screenshots of the executions, you’ll notice that I’m actually running against the master database and using a fully qualified name. If I ran it directly against the database where the table lives it would fail. Why? because that database, while running in on a SQL 2005 server, is still at compatibility level 7 (I know,I know). It doesn’t work unless you’re at level 9 or higher.</p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-42756160550102561312010-06-28T08:29:00.001-05:002010-06-28T08:29:00.090-05:00Report Server issues<p>I’ve had a lot of Report Server issues in the last few months. I thought we solved it once, only to see it come up again in a slightly different way. But I think I can put it all to bed now.</p> <p>The first time was a few months ago, right when I left on vacation (isn’t that always the way?). The C drive on one of our reporting servers began to run out of space, filling up 6 – 8 GB in a matter of hours.</p> <p>Before I get too far ahead of myself, let me describe our layout. We have four regional databases running SQL 2005 Standard Edition, each with data for that region. Two regions are in our corporate headquarters, the other two are in my office. We also have two SQL 2008 Standard Edition servers that run in a virtual environment, one in each datacenter. The reports hosted on each reporting center are identical except for their data sources; the corporate report server fetches data from the two corporate databases, and the local server does the same. The issue we had was on the server in the corporate datacenter.</p> <p>Our first step was to check the temp files. We noticed a few small files and one massive, 8 GB file that we couldn’t delete without stopping and restarting the report service. A few hours later the massive file reappeared. All of these temp files were prefixed “RSTemp_”.</p> <p>We moved the location of the temp files to a larger drive to give us more time before the largest temp file grew too large. By default the location is on C:\Program Files\Microsoft SQL Server\MSRS10.<<Instance Name>>\Reporting Services. You can change it by modifying the rsreportserver.config file and adding the entry to the <Service> tag:</p> <pre class="csharpcode"> <span class="kwrd"><</span><span class="html">Service</span><span class="kwrd">></span><br /> ---<br /> <strong><span class="kwrd"><</span><font color="#0000ff"><span class="html">FileShareStorageLocation</span><span class="kwrd">></span><br /> <span class="kwrd"><</span><span class="html">Path</span><span class="kwrd">></span>I:\RSTempFiles<span class="kwrd"></</span><span class="html">Path</span><span class="kwrd">></span><br /> <span class="kwrd"></</span><span class="html">FileShareStorageLocation</span><span class="kwrd">></span></font></strong><br /> <span class="kwrd"></</span><span class="html">Service</span><span class="kwrd">></span></pre><br /><br /><pre class="csharpcode"><span class="kwrd"></span></pre><br /><style type="text/css"><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><br /><br /><p>That gave us extra space, but it didn’t solve the issue. A little more digging showed that we had a long running report that was also getting bad data. The query for the report was looking for a scalar value but was returning multiples. The query was also a long running query. What we found was happening was that the report execution was timing out, but the query was still active. The user didn’t see the timeout and constantly tried to re-run the report. Consequently this report was continuously paged out to disk, and it never died. Once we cleaned up the data and fixed the reports query, the problem hasn’t reappeared.</p><br /><br /><p>The next issue was related to performance. We noticed at certain times of day the report server would get really sluggish. Reports that would complete in 10 seconds would now take 10 minutes. Looking at the server resources showed no increase in CPU, IO, or memory usage. We didn’t see a repeat of the super-big temp file, though there was an increase in the number of temp files.</p><br /><br /><p>A little more investigation showed that, when the performance began to degrade, there were locks being held by one process clearing the sessions that blocked another process that was writing session data. We were finally able to track this to a third party application we use to monitor performance. It didn’t look like the software was configured properly.</p><br /><br /><p>In investigating both of these issues we looked at the data in the ExecutionLogStorage table in the ReportServer database. If you’re not familiar with this table, you should check out Robert Bruckner’s excellent post on <a href="http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx" target="_blank">ExecutionLog2 View - Analyzing and Optimizing Reports</a>. </p><br /><br /><p>In the first issue, we were able to identify the report by seeing the pagination values in the additional information data. In the second, we could spot when the slowness would begin by seeing the TimeProcessing values rise.          </p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-19591298501393148142010-05-14T08:23:00.000-05:002010-05-14T08:23:44.466-05:00Catching upNothing SQL related in this post, just clearing my mind and catching up.<br />
<br />
First, my laptop went kablooie a few weeks back. I've been using a Pavilion TX 2000 that I ordered custom from HP for the past few years. I chose this model because I wanted a computer that could run the old Tablet XP OS, and I loved the swivel top that let me use it as either a notepad or as a full blown laptop (Apple, you need to add the ability to write on the iPad). Anyway, a few weeks ago my pc wouldn't boot at all, just kept giving me a black screen. I couldn't even get to the BIOS screen or boot from a CD. Luckily I had also bought an extended warranty that was still in effect. So, after opening a ticket with HP, I shipped the unit back to them, and they had it back to me about 10 days later. I was without the laptop for about 2 1/2 weeks, of which 1 was vacation time anyway. And not counting the week between when the unit first went bad and I shipped it back, I hardly missed it.<br />
<br />
But I did miss it. I work mostly on my laptop at work and on my commute. This is my lab, where I can install my own programs and other beta apps. It's also the machine I blog from, mostly while on my commute. So it's good to have it back. I'm just finishing installing SQL 2008 R2, VS 2010, and Office 2010. I should be good to go by the time I go back to work on Monday.<br />
<br />
Speaking about work, I did mention that I'm just finishing up a two-week vacation. The first week my wife and I took a cruise to the Mexican Riviera. We had a wonderful time. Perfect weather, great scenery, wonderful people. I'd like to go back soon and see some of the sights we missed this time around. This second week is just relaxing, doing yard work, mostly taking it easy. <br />
<br />
Next week I'll write about an issue I ran across with our report server just before I left.jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-58905471239311532142010-04-15T21:36:00.001-05:002010-04-15T21:36:13.631-05:00What I learned tonight – tempdb size when SQL starts<p>I went to the April monthly meeting of the <a href="http://chicago.sqlpass.org/" target="_blank">Chicago SQL Server User Group</a> tonight to see Brad McGehee (<a href="http://www.bradmcgehee.com/" target="_blank">Blog</a> | <a href="http://twitter.com/bradmcgehee" target="_blank">Twitter</a>). Brad’s in town to present at <a href="http://www.sqlsaturday.com/31/eventhome.aspx" target="_blank">SQLSaturday # 31</a> in Chicago and he also gave a talk to us tonight on optimizing tempdb performance.</p> <p>It was a good session because it went over tempdb and best practices. Most of the information I already knew but it’s always good to hear it again, even as a refresher. And there’s usually something I took for granted but was wrong about. And tonight was no exception.</p> <p>I knew that the data and log files for tempdb are recreated when the SQL service starts. But I thought that tempdb got it’s db options from the model database. For instance, if the model database is set at 2 MB for the data and 1 MB for the log then tempdb would inherit those same settings. But i was wrong. As Brad mentioned tonight, tempdb starts out as 8 MB for the data and the log file is 1 MB. On my laptop its’s actually 512 KB but maybe I heard him wrong.</p> <p>Anyway, to properly resize tempdb you should run the ALTER DATABASE command…</p> <pre class="csharpcode"><span class="kwrd">USE</span> master;<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> tempdb <br /><span class="kwrd">MODIFY</span> <span class="kwrd">FILE</span> ( NAME = <span class="str">'tempdev'</span>, <span class="kwrd">SIZE</span> = 25600KB , FILEGROWTH = 5120KB );<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> tempdb <br /><span class="kwrd">MODIFY</span> <span class="kwrd">FILE</span> ( NAME = <span class="str">'templog'</span>, <span class="kwrd">SIZE</span> = 2048KB , FILEGROWTH = 1024KB );<br /><span class="kwrd">GO</span></pre><br /><br /><p><style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><br /><br /> <br />…and tempdb will be correctly resized after every restart. And you’ve heard that you should create multiple tempdb files depending on the number of CPU’s you have? Well, just do that at the same time. Here I’m renaming, moving, resizing, and adding a file to tempdb;  </p><br /><br /><pre class="csharpcode"><span class="kwrd">USE</span> master;<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> tempdb <br /><span class="kwrd">MODIFY</span> <span class="kwrd">FILE</span> (NAME = <span class="str">'tempdev'</span>, NEWNAME = <span class="str">'tempdev1'</span>, FILENAME = <span class="str">'C:\SQL2008\DATA\tempdev1.mdf'</span>);<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> tempdb <br /><span class="kwrd">MODIFY</span> <span class="kwrd">FILE</span> (NAME = <span class="str">'templog'</span>, FILENAME = <span class="str">'C:\SQL2008\DATA\templog.ldf'</span>);<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> tempdb <br /><span class="kwrd">MODIFY</span> <span class="kwrd">FILE</span> (NAME = <span class="str">'tempdev1'</span>, <span class="kwrd">SIZE</span> = 25600KB , FILEGROWTH = 5120KB);<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> tempdb <br /><span class="kwrd">MODIFY</span> <span class="kwrd">FILE</span> (NAME = <span class="str">'templog'</span>, <span class="kwrd">SIZE</span> = 2048KB , FILEGROWTH = 1024KB);<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> tempdb <br /><span class="kwrd">ADD</span> <span class="kwrd">FILE</span> (NAME = <span class="str">'tempdev2'</span>, <br /> FILENAME = <span class="str">'C:\SQL2008\DATA\tempdev2.ndf'</span>, <br /> <span class="kwrd">SIZE</span> = 25600KB , FILEGROWTH = 5120KB);<br /><span class="kwrd">GO</span></pre><br /><br /><p>The usual disclaimers go here. Don’t use this script without understanding it as it can screw up your server. Don’t use it in production until you’ve testes it.</p><br /><br /><p>And I’ll leave the discussion of multiple tempdb files for now.</p><br /><br /><p></p><br /><style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-81444577517638101862010-04-13T09:11:00.001-05:002010-04-13T09:11:10.539-05:00SQL error log finds all copies of resource db after moving db<p>Just a short post about something interesting I saw in the SQL error log a while back.</p> <p>A while ago, I moved the system databases off the C drive’s of our servers to another drive on the SAN. That included moving the resource database. You can read Microsoft’s recommendations on moving system db’s on <a href="http://msdn.microsoft.com/en-us/library/ms345408.aspx" target="_blank">MSDN</a>.</p> <p>Anyway, I copied the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to a backup location in case of an emergency. But only one database was attached.  </p> <p>This interesting entry in the SQL error log was after I restarted the server: <br /> <br /><em><strong>The resource database has been detected in two different locations. Attaching the resource database in the same directory as the master database at 'O:\SQL2005\DATA\master.mdf' instead of the currently attached resource database at 'O:\SQL2005\Data\mssqlsystemresource.mdf'.</strong></em></p> <p>So, SQL saw both the attached db and the unattached backup copy when I restarted the service. The only time I saw the message was the first reboot, not any subsequent restarts. </p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-70184132718164023052010-03-24T18:17:00.001-05:002010-03-24T18:17:46.547-05:00Using the Inserted and Deleted tables outside of a trigger<p>Just a simple little post about a conversation about triggers I had earlier today with a developer, specifically using them to audit update and delete statements against a table. During our talk I mentioned that, starting in SQL 2005, the inserted and deleted tables were available outside of a trigger. He wasn’t aware of that until I mentioned it. So, for anyone else who’s not familiar with the inserted and deleted tables, here’s a little summary.</p> <p>Briefly, when you insert, update, or delete records from a table, SQL stores those records in a virtual table; inserted for inserts and deleted for updates and deletes. These tables are available inside of table triggers. Microsoft SQL <a href="http://msdn.microsoft.com/en-us/library/ms191300(SQL.90).aspx" target="_blank">Books Online</a> has an article explaining the use of the inserted and deleted tables inside of triggers; read that first as a refresher. In our shop we use them for monitoring who is updating and deleting certain critical tables, and we can roll back changes if necessary.  </p> <p>Starting with SQL 2005, you can use the OUTPUT clause to access the inserted and deleted tables outside of DML triggers. You can read the article in <a href="http://msdn.microsoft.com/en-us/library/ms177564(SQL.90).aspx" target="_blank">Books On Line</a>.   </p> <p>Here’s a simple demo script to run. I added a primary key and a non nullable column to show what happens in error conditions.  </p> <pre class="csharpcode"><span class="kwrd">USE</span> tempdb;<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">SET</span> NOCOUNT <span class="kwrd">ON</span>;<br /><span class="kwrd">GO</span><br /><br /><span class="rem">-- Create a testing table</span><br /><span class="kwrd">USE</span> [tempdb]<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">IF</span> <span class="kwrd">EXISTS</span> (<span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> sys.objects <span class="kwrd">WHERE</span> object_id = OBJECT_ID(N<span class="str">'[dbo].[TestOutput]'</span>) <span class="kwrd">AND</span> type <span class="kwrd">in</span> (N<span class="str">'U'</span>))<br /><span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> [dbo].[TestOutput]<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">USE</span> [tempdb]<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> [dbo].[TestOutput](<br /> [Field1] [<span class="kwrd">int</span>] <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,<br /> [Field2] [<span class="kwrd">char</span>](1) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,<br /> <span class="kwrd">CONSTRAINT</span> [PK_TestOutput] <span class="kwrd">PRIMARY</span> <span class="kwrd">KEY</span> <span class="kwrd">CLUSTERED</span> <br /> (<br /> [Field1] <span class="kwrd">ASC</span><br /> )<span class="kwrd">ON</span> [<span class="kwrd">PRIMARY</span>]<br />) <span class="kwrd">ON</span> [<span class="kwrd">PRIMARY</span>]<br /><br /><span class="kwrd">GO</span><br /><br />--Testing <span class="kwrd">of</span> Outupt <span class="kwrd">statement</span><br /><span class="kwrd">DECLARE</span> @TestOutput_Audit <span class="kwrd">TABLE</span><br />(<br /> Field1 <span class="kwrd">INT</span>,<br /> Field2 <span class="kwrd">CHAR</span>(1),<br /> [<span class="kwrd">Action</span>] <span class="kwrd">CHAR</span>(1),<br /> Action_by <span class="kwrd">VARCHAR</span>(25),<br /> Action_Date DATETIME<br />)<br /><br /><span class="rem">-- Testing Insert</span><br />INSERT <span class="kwrd">INTO</span> TestOutput (Field1, Field2)<br /><span class="kwrd">OUTPUT</span> inserted.Field1, inserted.Field2, <span class="str">'I'</span>, SUSER_NAME(), GETDATE()<br /><span class="kwrd">INTO</span> @TestOutput_Audit (Field1, Field2, [<span class="kwrd">Action</span>],Action_by, Action_Date)<br /><span class="kwrd">VALUES</span> (1, <span class="str">'A'</span>)<br /><br />INSERT <span class="kwrd">INTO</span> TestOutput (Field1, Field2)<br /><span class="kwrd">OUTPUT</span> inserted.Field1, inserted.Field2, <span class="str">'I'</span>, SUSER_NAME(), GETDATE()<br /><span class="kwrd">INTO</span> @TestOutput_Audit (Field1, Field2, [<span class="kwrd">Action</span>],Action_by, Action_Date)<br /><span class="kwrd">VALUES</span> (1, <span class="str">'A'</span>)<br /><br /><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> TestOutput<br /><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> @TestOutput_Audit<br /><br /><span class="rem">-- Testing Update</span><br /><span class="kwrd">UPDATE</span> TestOutput<br /><span class="kwrd">SET</span> Field2 = <span class="str">'B'</span><br /><span class="kwrd">OUTPUT</span> deleted.Field1, deleted.Field2, <span class="str">'U'</span>, SUSER_NAME(), GETDATE()<br /><span class="kwrd">INTO</span> @TestOutput_Audit (Field1, Field2, [<span class="kwrd">Action</span>],Action_by, Action_Date)<br /><span class="kwrd">WHERE</span> Field1 = 1<br /><br /><span class="kwrd">UPDATE</span> TestOutput<br /><span class="kwrd">SET</span> Field2 = <span class="kwrd">NULL</span><br /><span class="kwrd">OUTPUT</span> deleted.Field1, deleted.Field2, <span class="str">'U'</span>, SUSER_NAME(), GETDATE()<br /><span class="kwrd">INTO</span> @TestOutput_Audit (Field1, Field2, [<span class="kwrd">Action</span>],Action_by, Action_Date)<br /><span class="kwrd">WHERE</span> Field1 = 1<br /><br /><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> TestOutput<br /><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> @TestOutput_Audit<br /><br /><span class="rem">-- Testing Delete</span><br /><span class="kwrd">DELETE</span> TestOutput<br /><span class="kwrd">OUTPUT</span> deleted.Field1, deleted.Field2, <span class="str">'D'</span>, SUSER_NAME(), GETDATE()<br /><span class="kwrd">INTO</span> @TestOutput_Audit (Field1, Field2, [<span class="kwrd">Action</span>],Action_by, Action_Date)<br /><span class="kwrd">WHERE</span> Field1 = 1<br /><br /><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> TestOutput<br /><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> @TestOutput_Audit</pre><br /><style type="text/css"><br /><br /><br /><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><br /><br /><pre class="csharpcode"><font face="Verdana">The INSERT statement simply inserts a single record into my table. Afterwards you can see the same record in the table as well as my audit table. The second record wasn’t added to either table because it’s a primary key violation.</font></pre><br /><br /><p><strong><font color="#ff0000">Msg 2627, Level 14, State 1, Line 18 <br /> <br />Violation of PRIMARY KEY constraint 'PK_TestOutput'. Cannot insert duplicate key in object 'dbo.TestOutput'. <br /><br /> <br />The statement has been terminated.</font></strong></p><br /><br /><p><strong>Field1      Field2 <br /> <br />----------- ------ <br /><br /> <br />1           A </strong></p><br /><br /><p><strong>Field1      Field2 Action Action_by                 Action_Date <br /> <br />----------- ------ ------ ------------------------- ----------------------- <br /><br /> <br />1           A      I      PERTELLT3\John            2010-03-24 17:58:03.407</strong></p><br /><br /><p></p><br /><br /><p>I make a simple UPDATE. Now the table shows the update and the audit shows the value before the update took place. The second update fails because Field2 doesn’t allow NULL values; the main record isn’t updated nor added to the audit table. <style type="text/css"><br /><br /><br /><br /><br /><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style></p><br /><br /><p><font color="#ff0000"><strong>Msg 515, Level 16, State 2, Line 33 <br /> <br />Cannot insert the value NULL into column 'Field2', table 'tempdb.dbo.TestOutput'; column does not allow nulls. UPDATE fails. <br /><br /> <br />The statement has been terminated.</strong></font></p><br /><br /><p><strong>Field1      Field2 <br /> <br />----------- ------ <br /><br /> <br />1           B </strong></p><br /><br /><p><strong>Field1      Field2 Action Action_by                 Action_Date <br /> <br />----------- ------ ------ ------------------------- ----------------------- <br /><br /> <br />1           A      I      PERTELLT3\John            2010-03-24 17:58:03.407 <br /><br /> <br />1           A      U      PERTELLT3\John            2010-03-24 17:58:03.413</strong></p><br /><br /><p></p><br /><br /><p>Finally, when I delete the record I can see the record is gone from the main table but stored in the audit table.</p><br /><br /><p><strong>Field1      Field2 <br /> <br />----------- ------ </strong></p><br /><br /><p><strong>Field1      Field2 Action Action_by                 Action_Date <br /> <br />----------- ------ ------ ------------------------- ----------------------- <br /><br /> <br />1           A      I      PERTELLT3\John            2010-03-24 17:58:03.407 <br /><br /> <br />1           A      U      PERTELLT3\John            2010-03-24 17:58:03.413 <br /><br /> <br />1           B      D      PERTELLT3\John            2010-03-24 17:58:03.413</strong></p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-50514309621552355472010-03-22T09:10:00.001-05:002010-03-22T09:10:30.644-05:00Taking 70-448 SQL BI Exam<p>One of my goals has been to learn Microsoft’s SQL Business Intelligence stack. I’ve been reading different books, blogs and whitepapers to learn as much as I can, but I wasn’t very structured in my approach. While I picked up lots of new information and tips, I felt I was spinning my wheels. And I keep getting sidetracked with work projects and learning the basics of other new technologies.</p> <p>I’ve been successful in the past with Microsoft certifications. Beginning way back in the SQL 7 days, I took and passed exams to achieve MCP, MCDBA, and MCSD status. I also took and passed SQL 2005 Database Administrator and Database Developer exams a few years back. </p> <p>I’ve been pretty involved with Reporting Services since it first came out as an add on to SQL 2000. But I’ve only played around briefly with the old DTS and newer Integration Services. And I’ve never even opened an Analysis Services database. So overall I have a long way to go.</p> <p>So I’ve settled on a method that I believe will help me. I’ve decided to take the 70-448 exam, Microsoft SQL Server 2008 – Business Intelligence Development and Maintenance. I feel by studying for this specific exam I’ll get a head start on learning BI. I know I’m not going to learn everything there is about BI with one exam, but it should give me enough background to go ahead and finish up on my own. And setting a goal of passing 70-448 will, hopefully, keep me on track. </p> <p>Now when I took other exams I also struggled to keep to my studies. It was only when I actually scheduled the exam that I really sat down and concentrated. I’m not going to do that just yet. For the SQL 2005 exams I passed I knew enough that I could get by with using only the Microsoft Press Training Kits. I don’t feel anywhere near that comfortable with 70-448. But I’m still going to set a date. Today is Monday, March 22, 2010. I’m going to give myself just over 6 months to take and pass this exam. I’ll schedule the test on August 22nd, and I will pass the test by October 1st. That will also give me a little leeway if I fail the first time.</p> <p>I plan on blogging on my progress for 3 reasons. First, if I write about it, it will help me understand the concept better. As someone once said, you don’t understand a concept until you can explain it to your mother. Second, it should keep me on schedule. I don’t want to get to October 1st and admit publically that I didn’t take the test. And third, it will act as a study group for others who also want to take 70-448. The more the merrier. </p> <p><strong>Resources</strong></p> <p>I’ve only got a few resources so far. I’ll add to this list as I go.</p> <p><a href="http://www.amazon.com/Knights-24-Hour-Trainer-Integration-Programmer/dp/0470496924/ref=sr_1_1?ie=UTF8&s=books&qid=1269266393&sr=1-1" target="_blank"><img alt="MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server 2008-Business Intelligence Development and Maintenance: MCTS Exam 70-448 (Self-Paced Training Kits)" src="http://ecx.images-amazon.com/images/I/51jpFQ%2BwbGL._BO2,204,203,200_PIsitb-sticker-arrow-click,TopRight,35,-76_AA240_SH20_OU01_.jpg" /></a></p> <p>The first is the Microsoft Press 70-448 Training Kit, written by Eric Veerman, Teo Lachev, and Dejan Sarka of Solid Quality Mentors. I’ve always liked the MS Press TK’s. They come with practice exams, as well as a coupon for 15% your test.</p> <p><img alt="Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services (Wrox Programmer to Programmer)" src="http://ecx.images-amazon.com/images/I/51yYrPAZe%2BL._BO2,204,203,200_PIsitb-sticker-arrow-click,TopRight,35,-76_AA240_SH20_OU01_.jpg" /></p> <p>The second book I’ll be using is Knight’s 24-Hour Trainer, published by Wrox and written by Brian Knight, Devin Knight, and Mike Davis. I chose this book because it includes a CD that contains videos of the lessons.</p> <p>There will be more resources listed later, as I progress towards my personal D-Day. </p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com5tag:blogger.com,1999:blog-5892883484418619512.post-24944420374632265342010-03-16T14:10:00.003-05:002010-03-17T07:02:45.542-05:00PowerShell and SQL<p>When PowerShell was first released a few years back I wasn’t too excited. I didn’t use scripting very much, mainly for cleaning out old backup files from our archived storage. I thought that using VBScript would suffice for my limited means. But I started reading up on PowerShell V1, even tinkering around with it a bit. However learning PowerShell soon dropped down on my “Must Learn” technologies. There was just too many other things being introduced.</p><p>My mind didn’t change much when PowerShell V2 was in beta. Then Microsoft told us how PowerShell was going to be integrated into SQL Server 2008 (among other products) and PowerShell began moving back up my list. I’d read articled on MSDN and blogs by SQL experts, but my thinking never really changed. I figured that PowerShell was just one more way of doing things I already did in other ways.</p><p>One of the items on my “To Do” list has always been to document the servers I’m responsible for. I toyed with different methods of gathering the information, from running SSIS packages to a central repository to executing T-SQL queries in a Reporting Service report to manually (and tediously) typing the data into Word docs.</p><p>Then I read an article by Edwin Sarmiento on <a href="http://www.mssqltips.com/author.asp?authorid=18" target="_blank">MSSQL Tips</a>. In his article, he gave an example of using SMO inside PowerShell to write the information to an Excel spreadsheet. Finally the light bulb went on; I could use PowerShell to create my Run Book!</p><p>Now I’m not going to go into an in-depth explanation on PowerShell, or SMO. There’s ton’s of information available, and they explain much clearer than I can. I’m still learning myself. Check out Allen White (<a href="http://sqlblog.com/blogs/allen_white/default.aspx" target="_blank">blog</a>), Buck Woody (<a href="http://blogs.msdn.com/buckwoody/default.aspx" target="_blank">blog</a> | <a href="http://twitter.com/buckwoody" target="_blank">twitter</a>), Aaron Nelson (<a href="http://sqlvariant.com/wordpress/" target="_blank">blog</a> | <a href="http://twitter.com/SQLvariant" target="_blank">twitter</a>) and many others that I’m not mentioning here. I just wanted to share the method I’m beginning to use. It’s still evolving as I learn new tricks.</p><p>The basis of my script was the one used by Edwin Sarmiento in this <a href="http://www.mssqltips.com/tip.asp?tip=1759" target="_blank">article</a>. I’ve added a few things of my own to make it more like what I want. For instance I add server information as well as database information. And I put each server onto a different worksheet.  </p><p>The first thing my script does is to load the SMO namespace. After that I use the Get-Credential cmdlet to get an account to connect to the servers (I won’t always be connecting with Windows Authentication), create an Excel object and get a list of servers from a text file. After that I make sure that there’s enough worksheets in the workbook for all servers listed. Since a new Excel workbook has 3 worksheets by default I start my loop at 4. </p><pre class="csharpcode">#Get list of servers
$srvlist = @(get-content <span class="str">".\SQL_Servers.txt"</span>)
#Counter variable <span class="kwrd">for</span> rows
$c = $srvlist.Count
$intRow = 1
#Verify there's a sheet <span class="kwrd">in</span> the workbook <span class="kwrd">for</span> each server
<span class="kwrd">for</span> ($i = 4; $i -le $c; $i++)
{
$Workbook.Sheets.Add()
}</pre><br />
<br />
<p>Then it’s just a matter of looping through the servers and databases, writing the values to the workbooks and applying formatting. My final step is to rename each sheet.</p><br />
<br />
<pre class="csharpcode">$name = $instance -replace(<span class="str">"\\", "</span>-")
$Sheet.Name = $name</pre><br />
<br />
<p>Here’s an example of what one of my sheets looks like…</p><br />
<br />
<p><a href="http://lh5.ggpht.com/_R-tbJU7CXwQ/S5_Xt9vuDZI/AAAAAAAAAqI/Jv0EkRmZnEA/s1600-h/image4.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_R-tbJU7CXwQ/S5_XvuIsTgI/AAAAAAAAAqM/ufJq4sQA1NY/image_thumb2.png?imgmax=800" width="708" height="436" /></a> </p><br />
<br />
<p>…and here’s the whole script. For this example I stopped at 7 columns so it would all show on the screen without scrolling. But my final script will show more information about both the servers and the databases.</p><br />
<br />
<pre class="csharpcode"><span class="preproc">#region</span> LoadAssemblies
[System.Reflection.Assembly]::LoadWithPartialName(<span class="str">'Microsoft.SqlServer.SMO'</span>) | <span class="kwrd">out</span>-<span class="kwrd">null</span>
<span class="preproc">#endregion</span>
#Get SQL account information
$credential = Get-Credential
$loginName = $credential.UserName -replace(<span class="str">"\\","</span><span class="str">")   $password = $credential.Password
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $False
$Workbook = $Excel.Workbooks.Add()
#Get list of servers
$srvlist = @(get-content "</span>.\SQL_Servers.txt<span class="str">")
#Counter variable for rows
$c = $srvlist.Count
$intRow = 1
#Verify there's a sheet in the workbook for each server
for ($i = 4; $i -le $c; $i++)
{
$Workbook.Sheets.Add()
}
$a = 1
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in $srvlist)
{
$Sheet = $Workbook.Worksheets.Item($a)
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.set_Login($loginName)
$srv.ConnectionContext.set_SecurePassword($password)
#set headers
$Sheet.Cells.Item($intRow,1) = "</span>INSTANCE NAME:<span class="str">"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2) = $instance
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow += 1
$Sheet.Cells.Item($intRow,1) = "</span>VERSION:<span class="str">"
$Sheet.Cells.Item($intRow,2) = "</span>EDITION:<span class="str">"
$Sheet.Cells.Item($intRow,3) = "</span>COLLATION:<span class="str">"
$Sheet.Cells.Item($intRow,4) = "</span>OS VERSION:<span class="str">"
$Sheet.Cells.Item($intRow,5) = "</span>PLATFORM:<span class="str">"
$Sheet.Cells.Item($intRow,6) = "</span>PHYS MEM:<span class="str">"
$Sheet.Cells.Item($intRow,7) = "</span>NUM CPU:<span class="str">"
for ($col = 1; $col –le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
#get values
$intRow += 1
$Sheet.Cells.Item($intRow,1) = $srv.Information.Version
$Sheet.Cells.Item($intRow,2) = $srv.Information.Edition
$Sheet.Cells.Item($intRow,3) = $srv.Information.Collation
$Sheet.Cells.Item($intRow,4) = $srv.Information.OSVersion
$Sheet.Cells.Item($intRow,5) = $srv.Information.Platform
$Sheet.Cells.Item($intRow,6) = $srv.Information.PhysicalMemory
$Sheet.Cells.Item($intRow,7) = $srv.Information.Processors
$intRow += 2
$Sheet.Cells.Item($intRow,1) = "</span>DATABASES<span class="str">"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$intRow += 1
$Sheet.Cells.Item($intRow,1) = "</span>DATABASE NAME<span class="str">"
$Sheet.Cells.Item($intRow,2) = "</span>COLLATION<span class="str">"
$Sheet.Cells.Item($intRow,3) = "</span>COMPATIBILITY LEVEL<span class="str">"
$Sheet.Cells.Item($intRow,4) = "</span>AUTOSHRINK<span class="str">"
$Sheet.Cells.Item($intRow,5) = "</span>RECOVERY MODEL<span class="str">"
$Sheet.Cells.Item($intRow,6) = "</span>SIZE (MB)<span class="str">"
$Sheet.Cells.Item($intRow,7) = "</span>SPACE AVAILABLE (MB)<span class="str">"
for ($col = 1; $col –le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow += 1
$dbs = $srv.Databases
ForEach ($db in $dbs)
{
#Divide the value of SpaceAvailable by 1KB
$dbSpaceAvailable = $db.SpaceAvailable/1KB
#Format the results to a number with three decimal places
$dbSpaceAvailable = "</span>{0:N3}<span class="str">" -f $dbSpaceAvailable
$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $db.Collation
$Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel
#Change the background color of the Cell depending on the AutoShrink property value
if ($db.AutoShrink -eq "</span>True<span class="str">")
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 4) = $db.AutoShrink
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel
$Sheet.Cells.Item($intRow, 6) = "</span>{0:N3}<span class="str">" -f $db.Size
#Change the background color of the Cell depending on the SpaceAvailable property value
if ($dbSpaceAvailable -lt 1.00)
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor
$intRow += 1
}
$Sheet.UsedRange.EntireColumn.AutoFit()
$name = $instance -replace("</span>\\<span class="str">", "</span>-")
$Sheet.Name = $name
$intRow = 1
$a += 1
}
#Save file and close Excel.
$xlExcel8 = 56
$timeStamp = Get-Date -Format "yyyyMMdd_HH_mm"
$fileName = ".\ServerInfo_" + $timeStamp + ".xls"
$Workbook.SaveAs($fileName, $xlExcel8)
$Excel.Quit
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)</pre><br />
<style type="text/css">
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }</style><br />
<br />
<p>I’ve run this script using PowerShell V2 and Excel 2007 and the Excel 2010 beta with no problems. I don’t believe there’s anything specific to V2 in the script. But I do have a warning, though. If, while you run the script, you get impatient and click somewhere inside Excel, you’ll get an error and the script will stop (NOTE TO SELF: Add error handling!). The error is <font size="2">HRESULT 800ac472 and it’s an Excel error, not PowerShell.</font></p>jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com5tag:blogger.com,1999:blog-5892883484418619512.post-7728205115351191592010-03-01T13:12:00.001-06:002010-03-01T13:12:46.122-06:00Issues installing SQL 2005 SP3<p>Before I get to the details, I’d like to ask a few questions. I’d be interested in hearing from anyone who’s had problems installing SQL service packs or hotfixes. Has anyone else had the errors I’m mentioning below, or anything similar? What procedures or best practices do you follow when installing patches?  </p> <p>Last week I finally was able to install SQL 2005 SP3 on our production servers. I’ve installed countless service packs and hotfixes in the past on numerous servers both physical and virtual, development and production. And I’ve never run into the problems I had last week.</p> <p>In this case, I’m upgrading four production sites from SQL 2005 SP2 with the CU7 hotfix installed, build 3239 (see <a href="http://www.sqlservercentral.com/articles/Administration/2960/" target="_blank">this article from SQLServerCentral</a> that matches build numbers to service packs). When I installed SP3 on our dev and test servers I received the usual warning about running software. So last week I tried to stop the SQL services on the first 2 servers. This brought up Error #1: The services were telling me that I lacked the proper permissions to stop the SQL service. Now my account is an administrative account on the servers so it has plenty of rights, certainly enough to stop the service. ISP3.</p> <p>Now comes Error #2: The system databases on a SAN drive were read only, and again I didn’t have proper permissions to rewrite them! Again, I’m an administrator and should have been able to do this with no problems. Reading the install logs just gave me the same information. And again, this error only occurred on the first 2 servers, not the final 2.</p> <p>Next I logged off the servers and logged back in using the same account that the SQL services run under. I continued to get the same 2 errors on Server 1, but Server 2 let me complete the installation. Still no errors at all on Servers 3 or Server 4. Now I’m getting concerned. </p> <p>My next step was to restart Server 1. After the server came back up, I could finally install the service pack. When I finished I checked the Windows and SQL error logs for any clues that would explain why my permissions were being denied. I must admit my Windows admin skills aren’t the greatest so I probably missed the cause. I did notice that the anti-virus program we run had similar issues at the same time, but the anti-virus doesn’t hit the database files and I wasn’t trying to do anything with the anti-virus service. My guess is that somehow Server 1 and Server 2 somehow couldn’t validate my account after I logged in.</p> <p>Anyway, I thought I was finished. Not quite. My final step was to verify the new version. So I ran SELECT SERVERPROPERTY('ProductLevel') and all four servers returned SP3. The build number now showed as 9.00.4035. All looks good, right? A few days later I ran EXEC sp_server_info.</p> <p>Finally we get to Error #3. On Server 2, remember that I had problems at first with permissions but I thought the SP installed correctly on the 2nd try. But now looking at the value for DBMS_VER and SYS_SPROC_VERSION I’m seeing the build is still showing 9.00.3239! Why? I’m not sure. I think it’s due to 1 of 2 reasons.</p> <p>First, even though the SP3 install looked to be successful the second time it somehow skipped a component. During the reinstall I thought I selected all components, but it’s possible that I only thought I did, skipping something. NOTE TO SELF: for future installs document what components you’re installing. At least take a screenshot of what I’ve checked.</p> <p>The second possibility is because of Analysis Services. SSAS was installed on Server 2 but the service is disabled. That makes sense since we’re not using SSAS currently, but the reason it was installed on just one server is lost to the mists of time. In any case the SP3 install logs show that SSAS was disabled and it’s at build 3239. But by that logic it would only be as 3239 if it was running during other patches. Again, I can’t be sure.</p> jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com0tag:blogger.com,1999:blog-5892883484418619512.post-78185065963290610452010-02-16T11:43:00.003-06:002010-02-16T12:34:10.308-06:00How I save table sizes for all databases<p>Back when I first started monitoring our production servers, I wanted a way to track the size and growth of all tables in our databases. At the time, we were really only worried about 1 legacy database, and all the objects in that database were in the dbo schema. So tracking size growth was pretty easy; I ran the following script against that database weekly and compared results. </p><div class="csharpcode"><pre class="csharpcode"><span class="kwrd">declare</span> @TableName sysname
<span class="kwrd">declare</span> mySpace <span class="kwrd">cursor</span> <span class="kwrd">for</span>
<span class="kwrd">select</span> name <span class="kwrd">from</span> sys.sysobjects <span class="kwrd">where</span> xtype = <span class="str">'U'</span>
<span class="kwrd">order</span> <span class="kwrd">by</span> name
<span class="kwrd">open</span> mySpace
<span class="kwrd">fetch</span> <span class="kwrd">next</span> <span class="kwrd">from</span> mySpace <span class="kwrd">into</span> @TableName
<span class="kwrd">while</span> <span class="preproc">@@fetch_status</span> = 0
<span class="kwrd">begin</span>
insert <span class="kwrd">into</span> PerfDB.dbo.TableSpace(TableName, NumRows, Reserved, DataUsed, IndexUsed, Unused)
<span class="kwrd">exec</span> sp_spaceused @TableName
<span class="kwrd">fetch</span> <span class="kwrd">next</span> <span class="kwrd">from</span> mySpace <span class="kwrd">into</span> @TableName
<span class="kwrd">end</span>
<span class="kwrd">close</span> mySpace
<span class="kwrd">deallocate</span> mySpace</pre></div><p>After a while, we began to create new projects that required new databases. And these databases had objects that were created into separate schemas. So now, if I wanted to track changes to all tables in all databases, I’d have to run the above code against all databases. Do-able, certainly, but not exactly what I wanted to maintain. I’d have to add the code to each new database we added to the server. And sp_spaceused only returns the name of the table, not the schema name. I wanted to separate the tables by database as well as schema.</p><p>So I played around with using two well known undocumented procedures, sp_MSForEachDB and sp_MS_ForEachTable. I’ve used them before, but I never nested foreachtable inside foreachdb. And I still needed a way to separate the schema from the table name. And I wanted to get away from using a cursor.</p><p>I found the following script on a <a href="http://www.sqlservercentral.com/Forums/Topic476475-8-1.aspx" target="_blank">SQLServerCentral</a> forum that gave me an idea. Running this script showed that the value of ‘?’ when running sp_MSForEachTable showed the schema name as well as the table name.</p><pre class="csharpcode"><span class="kwrd">declare</span> @command1 <span class="kwrd">varchar</span>(8000)
<span class="kwrd">select</span> @command1 =
<span class="str">'
IF '</span><span class="str">'@'</span><span class="str">' <> '</span><span class="str">'master'</span><span class="str">' AND '</span><span class="str">'@'</span><span class="str">' <> '</span><span class="str">'model'</span><span class="str">' AND '</span><span class="str">'@'</span><span class="str">' <> '</span><span class="str">'msdb'</span><span class="str">' AND '</span><span class="str">'@'</span><span class="str">' <> '</span><span class="str">'tempdb'</span><span class="str">'
begin
use [@] execute sp_MSForEachTable '</span><span class="str">'print '</span><span class="str">''</span><span class="str">'?'</span><span class="str">''</span><span class="str">' '</span><span class="str">'
end
'</span>
<span class="kwrd">exec</span> sp_MSforeachdb @command1, <span class="str">'@'</span></pre><div class="csharpcode"><pre><font face="Verdana">So now I can just run sp_spaceused inside the sp_MSForEachTable block and strip out the schema into a separate column.</font></pre></div><div class="csharpcode"><p><font face="Verdana">Here’s the code I use now</font></p><pre class="csharpcode"><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> #TableSpace (
DatabaseName nvarchar(128) <span class="kwrd">NULL</span>,
SchemaName nvarchar(128) <span class="kwrd">NULL</span>,
TableName nvarchar(128) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,
<span class="kwrd">rows</span> <span class="kwrd">int</span> <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,
reserved <span class="kwrd">varchar</span>(15) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,
<span class="kwrd">data</span> <span class="kwrd">varchar</span>(15) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,
index_size <span class="kwrd">varchar</span>(15) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,
unused <span class="kwrd">varchar</span>(15) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>)
<span class="kwrd">DECLARE</span> @<span class="kwrd">SQL</span> <span class="kwrd">varchar</span>(8000)
<span class="kwrd">SELECT</span> @<span class="kwrd">SQL</span> =
<span class="str">'
IF '</span><span class="str">'@'</span><span class="str">' <> '</span><span class="str">'master'</span><span class="str">' AND '</span><span class="str">'@'</span><span class="str">' <> '</span><span class="str">'model'</span><span class="str">' AND '</span><span class="str">'@'</span><span class="str">' <> '</span><span class="str">'msdb'</span><span class="str">' AND '</span><span class="str">'@'</span><span class="str">' <> '</span><span class="str">'tempdb'</span><span class="str">'
BEGIN
USE [@] EXECUTE sp_MSForEachTable '</span><span class="str">'INSERT INTO #TableSpace (TableName, rows, reserved, data, index_size, unused) EXEC sp_spaceused '</span><span class="str">''</span><span class="str">'?'</span><span class="str">''</span><span class="str">';
UPDATE #TableSpace SET SchemaName = LEFT('</span><span class="str">''</span><span class="str">'?'</span><span class="str">''</span><span class="str">', CHARINDEX('</span><span class="str">''</span><span class="str">'.'</span><span class="str">''</span><span class="str">', '</span><span class="str">''</span><span class="str">'?'</span><span class="str">''</span><span class="str">', 1) - 2) WHERE SchemaName IS NULL;
UPDATE #TableSpace SET DatabaseName = '</span><span class="str">''</span><span class="str">'@'</span><span class="str">''</span><span class="str">' WHERE DatabaseName IS NULL; '</span><span class="str">'
END
'</span>
<span class="kwrd">EXEC</span> sp_MSforeachdb @<span class="kwrd">SQL</span>, <span class="str">'@'</span></pre><style type="text/css">
.csharpcode, .csharpcode pre{font-size: small;color: black;font-family: consolas, "Courier New", courier, monospace;background-color: #ffffff;/*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt {background-color: #f4f4f4;width: 100%;margin: 0em;}.csharpcode .lnum { color: #606060; }</style></div><p>In my example I’m creating a temporary table, but in practice I use a permanent table.  Line 16 is obvious, it skips the system databases. Line 18 calls the sp_spaceused procedure for each table inside each database.</p><p>Line 19 is where I strip out the schema name. I just use CHARINDEX function to return the position of the dot separator, then I use the LEFT function to return the characters before it. Since ‘?’ returns the table name as [Schema].[TableName] I also want to skip the brackets, so I move the position one more place to the left to take care of the ] character. I could have been even fancier and did even more string manipulation to take care of the [ character, but it was just as easy to use the REPLACE function on the whole column.</p><p>And Line 20 stores the database name. Running the script on my laptop running SQL 2008 gives me results like these</p><p><a href="http://lh3.ggpht.com/_R-tbJU7CXwQ/S3rZWWL3YxI/AAAAAAAAAp8/HjboRWresBs/s1600-h/image%5B6%5D.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_R-tbJU7CXwQ/S3rZXGnyqMI/AAAAAAAAAqA/GBAZoShoOeg/image_thumb%5B7%5D.png?imgmax=800" width="687" height="482" /></a> </p><p>I’ve run this against SQL 2005 and SQL 2008 servers. I haven’t tested it against SQL 2000. As with any code you find on the internet, please test it and become familiar with it before you put it into production.   </p>jaypehttp://www.blogger.com/profile/09643701158012749143noreply@blogger.com2