tag:blogger.com,1999:blog-76445278794924210052024-02-20T03:26:20.999-06:00SQL Server DevelopmentFocusing on setup and development using SQL Server products from 2000 to 2008 and beyond. Also about anything geeky that compels me to write a blog entry.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.comBlogger32125tag:blogger.com,1999:blog-7644527879492421005.post-76580105051444590152013-09-21T16:48:00.000-05:002013-09-21T16:48:00.698-05:00Visual Studio, SQL Management Studio, and SQL Express on a Windows 8 TabletI recently bought a Dell Latitude 10 Essentials tablet with Windows 8, my first experience with any personal Windows 8 device. Of course one of the first things I wanted to do was install my development tools on this 64GB tablet, but I was skeptical if it had the memory and the juice to handle the Studios and database engine service.<br />
<br />
Side note, my tablet started with around 32GB of space free after pre-installed programs.<br />
<br />
So I installed Visual Studio 2012, SSMS 2012, and SQL Express 2012 DB engine, and have 12 GB to spare. Not a lot but it fits. Now all I need is to add a 64GB or 128GB SD card to the open slot, and I will have plenty of space for database and code hosting.<br />
<br />
So if you are considering this type of setup on a Windows 8 tablet, my one piece of advice is do not settle for 32GB of internal memory because you will be disappointed. I would say 64GB plus SD expansion room is a minimum, and if you can get 128GB internal or better yet 256GB+ in SSD internal, the more the better.<br />
<br />
I will post updates if anything changes after a few weeks of use. If anyone reading this has some experience with this type of setup, please post in comments.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-23462115221387305682013-09-14T16:01:00.000-05:002013-09-14T17:14:30.201-05:00Using IIF instead of CASEFor years the primary means of expressing conditional logic in SQL Server has been the CASE statement, which allows for powerful set-based logic and I personally love. However, sometimes you just have a quick two-way decision that you need to make in your data set or just with variables, and CASE syntax feels a bit overkill. Especially if you also code in a programming language that gives you the IIF function.<br />
<br />
Thank you SQL Server 2012. The IIF has arrived.<br />
<br />
Now you can (and I already have, extensively) deal with situations where if the Boolean answer to a formula is true, do this, else do that using IIF. Here is a quick, simple example.<br />
<br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Declare</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value1</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Int</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">=</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> 1</span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">,</span></span></span><br />
<span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">
</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value2</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Int</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">=</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> 2</span></span><br />
<span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">
</span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Print</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'@Value1='</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">+</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;">Convert</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">varchar</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">1</span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">),</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value1</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">)</span></span></span><br />
<span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">
</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Print</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'@Value2='</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">+</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;">Convert</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">varchar</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">1</span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">),</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value2</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">)</span></span></span><br />
<span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">
</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Print</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;">IIF</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value1</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">=</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value2</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">,</span></span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'They Equal'</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">,</span></span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'They Do Not Equal'</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">)</span></span></span><br />
<span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">
</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Set</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value2</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">=</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> 1</span></span><br />
<span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">
</span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Print</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'@Value1='</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">+</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;">Convert</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">varchar</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">1</span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">),</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value1</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">)</span></span></span><br />
<span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">
</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Print</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'@Value2='</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">+</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;">Convert</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">varchar</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">1</span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">),</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value2</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">)</span></span></span><br />
<span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">
</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">Print</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;">IIF</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">(</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value1</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">=</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">@Value2</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">,</span></span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'They Equal'</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">,</span></span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'They Do Not Equal'</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">)</span></span></span><br />
<br />
This is not revolutionary, but it does make for a nice shortcut in true/false scenarios, and it is another step toward shared syntax with external programs. A small but good addition to SQL 2012.<br />
<br />
Until next time, may well defined requirements be at your back and realistic timelines lie before you.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-84806512553318636162012-11-22T09:54:00.002-06:002012-11-22T09:54:26.512-06:00SQL Server 2012, and the fog clears on ThanksgivingIf you have been developing databases for five, ten, or more years, you have probably been there too. The spot where plans finally catch up to reality. You were in the trenches with old technology (in my case Foxpro 6), trying to make it do the impossible, despondent that the day may never come when you have the ability to implement real solutions again building on a real technology stack.<div>
<br /></div>
<div>
And then it finally happens.</div>
<div>
<br /></div>
<div>
After three weeks of conversion pain, we have now implemented a complete redesign of our core system that is driven by my new best friend.... SQL Server 2012.</div>
<div>
<br /></div>
<div>
Oh, I still love SQL 2000, 2005, and 2008, and they will still play an important role in the spaces that they currently hold (until 2000 support ends next year). But SQL 2012, shiny and new, full of new functionality, is the spring in our human cannonball trick.</div>
<div>
<br /></div>
<div>
In addition to making me want to blog again about SQL Server, it means that I can begin to turn musings in the back of my mind into concrete plans that can become reality.</div>
<div>
<br /></div>
<div>
While I did not have time to blog on the server setup and production installation of our SQL Server, I look forward to again being able to join my community of SQL developers in sharing successes and failures, helping others find answers that I have already found, and enjoying the benefits of the countless others that do the same things, making developing in and supporting the SQL Server platform such a joy.</div>
<div>
<br /></div>
<div>
Happy Thanksgiving to all!</div>
J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-71756895153466856162012-05-12T20:09:00.001-05:002012-05-12T20:11:27.036-05:00SQL Saturday #112 - Birmingham 2012Today was another great SQL Saturday in Birmingham. A day packed full of meeting other SQL professionals, good food, and great sessions of information. If you have never been to a SQL Saturday and you are reading this blog, you should go. Here is a quick run down of the sessions that I attended today.<br />
<div>
<br /></div>
<div>
Time Saving Tips & Tricks for SSMS 2012 - Aaron Nelson</div>
<div>
What's New in SSIS 2012 - Christopher Price</div>
<div>
SQL Server 2012: What's New - Bob Cheatham</div>
<div>
Data Quality Services 2012 - Chris Price</div>
<div>
Introduction to FileTables - Sven Aelterman</div>
<div>
Windowing Functions: The Reason to Upgrade to 2012 - Kevin Boles</div>
<div>
<br /></div>
<div>
As you can tell, it was a great day for learning more about SQL 2012. </div>
<div>
<br /></div>
<div>
<a href="http://www.sqlsaturday.com/default.aspx">http://www.sqlsaturday.com/default.aspx</a>
</div>
<div>
<br /></div>
<div>
Check the site and find a session near you. You will not regret it.</div>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-16447608863646021042011-07-30T21:09:00.000-05:002011-07-30T21:09:45.645-05:00SQL Saturday #81 BirminghamToday I had the great pleasure of meeting other SQL Server professionals from all over the Southeast, and listening to some great presentations on everything from design and statistics to the upcoming SQL "Denali".<br />
<div><br />
We had five different sessions to choose from in each of the six time slots. My choices today were<br />
<br />
Database Design<br />
SQL "Denali" High Availability<br />
SQL Statistics<br />
Building a Professional Development Plan<br />
Table Partitioning<br />
Advanced DW Scenarios in SSIS<br />
<br />
</div><div>It was not just informative. These events are about networking and being a part of a community centered around the SQL Server product. Plus, on top of great sessions and door-prizes, we were provided with coffee, doughnuts, sodas, and a nice box lunch. If you have not been to one, I highly recommend it.</div>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-66176422260795746752011-07-24T09:39:00.000-05:002011-07-24T09:39:09.717-05:00Upcoming SQL Saturday in Birmingham, ALIt is time for year three of the incredible FREE training day on SQL Server in Birmingham, AL. This event has grown each year across the country, and this year will be no exception. If you are anywhere in the Southeast region, I recommend you make the trip to Birmingham on July 30th. Otherwise, please go to the main site and find a SQL Saturday closer to you. You owe it to yourself to take advantage of these free events to continue your professional development and take advantage of networking with other SQL Server professionals.<br />
<br />
<a href="http://sqlsaturday.com/81/eventhome.aspx">http://sqlsaturday.com/81/eventhome.aspx</a>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-22855476548932927082010-11-04T17:05:00.001-05:002010-11-04T17:05:00.738-05:00Send Email in SQL 2008 ExpressNo Email functionality in SQL Express? No problem. This is a nice article on using CLR to send email in SQL 2008 Express.<br /><a href="http://www.sqlservercentral.com/articles/SQL+Express+2008/71341/">http://www.sqlservercentral.com/articles/SQL+Express+2008/71341/</a>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-73504685162382932302010-04-23T17:44:00.001-05:002010-04-23T17:44:00.835-05:00Using Extended Properties for DocumentationThis is a nice article on using extended properties to document your database. Much easier than maintaining a Word document.<br /><br /><a href="http://www.mssqltips.com/tip.asp?tip=1499&ctc">Create a SQL Server Data Dictionary in Seconds using Extended Properties</a>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-60030700843005722472010-03-28T11:14:00.003-05:002010-03-28T11:55:28.367-05:00SQL Saturday Free TrainingI just attended my first free <span id="SPELLING_ERROR_0" class="blsp-spelling-error">SQL</span> Saturday training day (#29 in Birmingham, AL) and it was an amazing day. The training was of the highest quality and as I went through the day with free coffee/soda and free lunch provided, I could not believe all of the value that was being provided for free. Of course it is not free, just free to we the trainees.<br /><br />A big thank you to all the sponsors and the volunteers that made the day possible.<br /><br />I want to encourage all of you reading this to go to the <a href="http://www.sqlsaturday.com/"><span id="SPELLING_ERROR_1" class="blsp-spelling-error">SQL</span> Saturday </a>website and find <span id="SPELLING_ERROR_2" class="blsp-spelling-error">SQL</span> Saturday's in your <span id="SPELLING_ERROR_3" class="blsp-spelling-corrected">region</span>. There were many people that I met in sessions who were from Tennessee and Georgia, and some had been to as many as three <span id="SPELLING_ERROR_4" class="blsp-spelling-error">SQL</span> Saturday's in different locations. It is worth a little driving time.<br /><br />The hardest part was choosing which sessions to <span id="SPELLING_ERROR_5" class="blsp-spelling-corrected">attend</span>, since it was pretty common to have two or more sessions that I was interested in going at the same time. Here is the list that I finally went with.<br /><br />Parallel Query Execution Dive Deep presented by Kevin Boles.<br />Database Design Patterns presented by Louis Davidson.<br />Tuna Helper for <span id="SPELLING_ERROR_6" class="blsp-spelling-error">SQL</span> Server <span id="SPELLING_ERROR_7" class="blsp-spelling-error">DBAs</span> presented by Janis Griffin.<br />Tricks & Tips for Writing Better Queries presented by Joe Webb.<br /><span id="SPELLING_ERROR_8" class="blsp-spelling-error">SQL</span> Server Locking and Blocking Made Simple presented by Joe Webb.<br /><br />In each session, I already knew some of the material and I learned some new things, and most importantly gained some insight from people who approached problems from different angles. <span id="SPELLING_ERROR_9" class="blsp-spelling-error">SQL</span> Server is so robust, there are hundreds of ways to carve the turkey.<br /><br />I hope if you have never attended <span id="SPELLING_ERROR_10" class="blsp-spelling-error">SQL</span> Saturday that you will make it a priority. All it costs you is one Saturday.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-72829866861467456082009-12-28T21:10:00.000-06:002009-12-28T20:46:53.835-06:00Redirecting SSIS Data Errors to Avoid Package FailureWhen setting up a new Integration Services package, is it often advantageous to leave your errors <span id="SPELLING_ERROR_0" class="blsp-spelling-error">untrapped</span> so your package crashes and you can interpret the errors. But after you get past the first wave of setup, it is often best to trap for additional data errors in your code to prevent complete package failures.<br /><br /><br />So how do you do it? I'll cover it in broad strokes.<br /><br />I start with my source table structure. In order to capture error output, I want to create a table that matches the field names of the source table, but not the data type. The idea is to have a table that is so wide open, it can handle any data you throw at it.<br /><br />For example, the source table, lets call it T1 has these fields:<br />ID Int Not Null<br />Name <span id="SPELLING_ERROR_1" class="blsp-spelling-error">varchar</span>(30) Not Null<br /><span id="SPELLING_ERROR_2" class="blsp-spelling-error">Addr</span> <span id="SPELLING_ERROR_3" class="blsp-spelling-error">varchar</span>(40) Not Null<br /><br /><br />To handle error output, you would create a table (T2) with these fields:<br /><br /><span id="SPELLING_ERROR_4" class="blsp-spelling-error">ErrCode</span> <span id="SPELLING_ERROR_5" class="blsp-spelling-error">varchar</span>(100) Null<br /><span id="SPELLING_ERROR_6" class="blsp-spelling-error">ErrColumn</span> <span id="SPELLING_ERROR_7" class="blsp-spelling-error">varchar</span>(100) Null<br />ID <span id="SPELLING_ERROR_8" class="blsp-spelling-error">varchar</span>(100) Null<br />Name <span id="SPELLING_ERROR_9" class="blsp-spelling-error">varchar</span>(200) Null<br /><span id="SPELLING_ERROR_10" class="blsp-spelling-error">Addr</span> <span id="SPELLING_ERROR_11" class="blsp-spelling-error">varchar</span>(200) Null<br /><br />Let's go over the noteworthy differences. First I added the <span id="SPELLING_ERROR_12" class="blsp-spelling-error">ErrCode</span> and <span id="SPELLING_ERROR_13" class="blsp-spelling-error">ErrColumn</span> fields which will be populated with data from the <span id="SPELLING_ERROR_14" class="blsp-spelling-error">SSIS</span> error handling.<br /><br /><br />Most importantly, I changed the data types on all fields to <span id="SPELLING_ERROR_15" class="blsp-spelling-error">varchar</span> fields and made sure the size was big enough to handle even bad data. Depending on your situation, you may adjust these sizes much larger to accommodate the worst possible over sized data records.<br /><br /><br />Lastly, I allowed Nulls in all of the columns to ensure that every field of data in the row with errors can be stored in the error table, regardless of Nulls.<br /><br /><br />Now that the structure is in place, it is time to add to the <span id="SPELLING_ERROR_16" class="blsp-spelling-error">SSIS</span> package. In the Data Flow where you are pulling from your source table, in our case the nondescript T1 table, we must add the error output. Within the data flow section, drag a destination object from the toolbar that connects to your new T2 table. Then click on the Source object that connects to T1 and drag the red line from the bottom of the object to the T2 Destination box.<br /><br />If you edit the Error Output in the T1 Data Source, you can specify what should be done with each column of data. You can have a column cause the package to fail on error, ignore the error, or redirect the error output, which is the option we want to use. By redirecting all columns on error, they become available to consume in your T2 table data destination.<br /><br />Now that the red line is attached, you can edit the properties in your T2 destination to insure that the <span id="SPELLING_ERROR_17" class="blsp-spelling-error">ErrCode</span>, <span id="SPELLING_ERROR_18" class="blsp-spelling-error">ErrColumn</span>, and all other fields are mapped properly from your error output to your T2 fields. Once you have the mappings all established, you should be ready to run.<br /><br />To me the great benefit of this type of error handling comes in dealing with bad data that you are pulling from production databases into secondary aggregate, data warehouse, or reporting databases. It allows you to get most of the data into the primary tables and the leisure to review the bad results afterward. Of course, if the data you are pulling has data requirements that would make partial data useless, you must consider this when considering the error output option. Regardless, using error output is an excellent way to troubleshoot as you are developing a new <span id="SPELLING_ERROR_19" class="blsp-spelling-error">SSIS</span> package.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-32732430141848090812009-12-22T19:58:00.000-06:002009-12-22T19:58:00.233-06:00SSIS Package CredentialsThis is a great article on setting up <span id="SPELLING_ERROR_0" class="blsp-spelling-error">SSIS</span> Package Credentials, which is more involved than the old <span id="SPELLING_ERROR_1" class="blsp-spelling-error">DTS</span> days. If you need to schedule your <span id="SPELLING_ERROR_2" class="blsp-spelling-error">SSIS</span> packages, read this article and it will save you a lot of troubleshooting time.<br /><br /><a href="http://www.sqlservercentral.com/links/81443/131962">http://www.sqlservercentral.com/links/81443/131962</a>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-13378105907423824272009-12-18T09:38:00.002-06:002009-12-18T22:11:16.427-06:00Creating a Reporting Service Report TemplateI read this good, simple process for creating a template for your company reports that was worth sharing.<br /><br /><a href="http://sqlblogcasts.com/blogs/testas/archive/2009/12/16/creating-a-report-template-in-ssrs-2000-2005-2008.aspx">http://sqlblogcasts.com/blogs/testas/archive/2009/12/16/creating-a-report-template-in-ssrs-2000-2005-2008.aspx</a>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-640445013960757312009-12-11T09:12:00.002-06:002009-12-14T19:54:40.836-06:00Netbook upgrade to Windows 7 with USB keyGreat tip on how to setup your thumb drive for loading Windows 7. Is it time for a <span id="SPELLING_ERROR_0" class="blsp-spelling-error">netbook</span> upgrade?<br /><br /><a href="http://technet.microsoft.com/en-us/magazine/dd535816.aspx">http://technet.microsoft.com/en-us/magazine/dd535816.aspx</a>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-12897738636564452102009-11-25T19:23:00.003-06:002009-11-25T19:40:32.554-06:00Building My First Website Using Microsoft ExpressionsIn years past, I have used MS Front Page to develop several websites, both personal and as a consultant. In the last few years I have only built ASP.NET web apps until this last weekend. I dipped my toe back into the website design pool using a tool that was new to me, Microsoft Expressions.<div><br /></div><div>Expressions has many similarities to Front Page, but it goes further in mixing the graphical design tools with a light layer of the .NET controls to extend your site's functionality. In my case I just needed to build a basic website without any data interaction, at least at this point. I found the templates, menus, and toolbars to be well laid out and fairly intuitive.</div><div><br /></div><div>I was able to get up and running and finish the first draft of a full website in around ten hours never having seen the interface ahead of time. To me that is the best indication of a well designed product.</div><div><br /></div><div>The dynamic web templates allowed me to easily manage the top banner, navigation menu, and side bar separately from the page content on each page throughout the site. I did discover some hangups with the template approach. After developing eight or nine pages, I realized I needed to add a new editable zone to the template. I quickly discovered that this is a major issue once you have already applied your template to one or more web page.</div><div><br /></div><div>Overall I give Expressions Web a big thumbs up as an excellent web development tool, and I look forward to using more of the functionality over time.</div>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-26279469013051699612009-11-14T09:51:00.004-06:002009-11-14T10:16:34.058-06:00Conditionally Blocking Scheduled ReportingAs is true for many <span class="blsp-spelling-error" id="SPELLING_ERROR_0">DBAs</span>, I have developed a custom set of <span class="blsp-spelling-error" id="SPELLING_ERROR_1">SSIS</span> packages along with a database that manages scheduled Reporting Services reports. It delivers to a mixture of email recipients and FTP locations after generating the output <span class="blsp-spelling-error" id="SPELLING_ERROR_2">PDFs</span> and Excel results.<div><br /></div><div>Then came the inevitable weekend of trouble. The set of <span class="blsp-spelling-error" id="SPELLING_ERROR_3">SQL</span> Server 2005 databases, which provide the data for the reports, failed to load due to trouble with the source legacy systems. However, since the data in the <span class="blsp-spelling-error" id="SPELLING_ERROR_4">SQL</span> Server databases still contained yesterday's data, the report schedule had no problem completing the scheduled reporting.</div><div><br /></div><div>Therein lay the problem. I did not want any reports to generate, or worse yet email to recipients, if the recent day of data was not included.</div><div><br /></div><div>The solution is fairly simple. The <span class="blsp-spelling-error" id="SPELLING_ERROR_5">SSIS</span> packages that load the <span class="blsp-spelling-error" id="SPELLING_ERROR_6">SQL</span> Server databases were logging to a table, and included a special status column. When each database was finished loading and had completed any subsequent updates required, a log entry with a status of DONE and UPDATED were entered. I just created a stored procedure to scan the log for databases that were scheduled to be loaded, filtered by UPDATED, and with a log time that fit into the correct date/time range for the current day's data load.</div><div><br /></div><div>If the stored procedure found any records that did not meet the above criteria, it returned a result reporting accordingly. Then I just added a call to this stored procedure in my primary scheduled reporting stored procedure, where the list of scheduled reports to process is generated. If the data load check reported problems, I prevent the scheduled reports from processing. Then once the problem with the data load is resolved and all data is available, all I have to do it run the <span class="blsp-spelling-error" id="SPELLING_ERROR_7">SQL</span> Agent job that runs the scheduled reports and this time it will succeed.</div><div><br /></div><div>I'm always a fan of a little more work in the back end to make a little less work for the routine. Not to mention, you don't have to answer Fred's email asking, "Why did I get two emails of the same report, and which one is right?"</div>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-39490757753249349942009-10-18T10:39:00.004-05:002009-10-18T10:41:44.297-05:00A New Tablet ComputerThis is a super cool new kind of Tablet computing on the horizon. I can easily see how this could change the delivery mechanism for text books on college campuses in time, besides the obvious business uses.<div><br /></div><div><a href="http://gizmodo.com/5365299/courier-first-details-of-microsofts-secret-tablet">http://gizmodo.com/5365299/courier-first-details-of-microsofts-secret-tablet</a></div><div><br /></div>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-69908792069119885662009-10-18T09:12:00.003-05:002009-10-18T10:36:22.531-05:00Researching and Bypassing DNS IssuesSo we have almost finished transition three out of four. Move to a domain - check. Move to a new file/print server - check. Number three was the biggie.<div><br /></div><div>Move the servers to an off-site data center - almost check.</div><div><br /></div><div>We moved thirty seven servers to a data center last weekend, changed all of the external and internal <span class="blsp-spelling-error" id="SPELLING_ERROR_0">IP</span> addresses, and planned to be up and running on last Monday morning. I can hear you chuckling as you read, and yes it was probably too ambitious, but that was the directive so we did it.</div><div><br /></div><div><span class="blsp-spelling-error" id="SPELLING_ERROR_1">DNS</span> issues, both internal and external, are one of the big problems in a transition like this. The <span class="blsp-spelling-error" id="SPELLING_ERROR_2">DNS</span> server must be properly updated with all <span class="blsp-spelling-error" id="SPELLING_ERROR_3">IP</span> changes, the local office <span class="blsp-spelling-error" id="SPELLING_ERROR_4">DNS</span> server must be updated as well, and the external registrar must be updated with new external <span class="blsp-spelling-error" id="SPELLING_ERROR_5">IP</span> addresses, which can take a few days to propagate.</div><div><br /></div><div>So if you are like me, a non-networking guy supporting the transition, what are some of the best ways to work on connectivity issues for users? Start with <span class="blsp-spelling-error" id="SPELLING_ERROR_6">DNS</span>. Here are some of the main testing steps I took to evaluate problems.</div><div><br /></div><div>1) Ping the name you are trying to reach. If it resolves to the correct <span class="blsp-spelling-error" id="SPELLING_ERROR_8">IP</span> address but times out, there may be a real connection issue. If it resolves to the old <span class="blsp-spelling-error" id="SPELLING_ERROR_9">IP</span> address or something else unexpected...</div><div>2) Flush the <span class="blsp-spelling-error" id="SPELLING_ERROR_10">DNS</span>. From the command prompt, just run "<span class="blsp-spelling-error" id="SPELLING_ERROR_11">ipconfig</span> /<span class="blsp-spelling-error" id="SPELLING_ERROR_12">flushdns</span>" and it will remove any cache of <span class="blsp-spelling-error" id="SPELLING_ERROR_13">DNS</span> resolution so you can verify the <span class="blsp-spelling-error" id="SPELLING_ERROR_14">IP</span> is coming from the latest changes on the <span class="blsp-spelling-error" id="SPELLING_ERROR_15">DNS</span> server. Also this is a good time to check the <span class="blsp-spelling-error" id="SPELLING_ERROR_16">DNS</span>1 and <span class="blsp-spelling-error" id="SPELLING_ERROR_17">DNS</span>2 <span class="blsp-spelling-error" id="SPELLING_ERROR_18">IP</span> addresses in the <span class="blsp-spelling-error" id="SPELLING_ERROR_19">NIC</span> properties to verify the PC/Server is pointing at the correct <span class="blsp-spelling-error" id="SPELLING_ERROR_20">DNS</span> server. Then Ping again. If you get the same problem, move on to the next step.<br />3) Ping the <span class="blsp-spelling-error" id="SPELLING_ERROR_21">IP</span> address. If you can't Ping it, it doesn't always mean anything since some servers are configured to prevent pinging them. But if you know you could ping it from another machine, you should be able to ping it from the current machine.</div><div>4) Check <span class="blsp-spelling-error" id="SPELLING_ERROR_22">DNS</span> Suffixes. If you have multiple domains like we do, during this type transition you could have some resources showing up under the wrong zone due to <span class="blsp-spelling-error" id="SPELLING_ERROR_23">config</span> errors. So perhaps SERVER1.domain1.com now is setup under SERVER1.domain2.com. If you add <span class="blsp-spelling-error" id="SPELLING_ERROR_24">DNS</span> suffixes to the suffix list in the <span class="blsp-spelling-error" id="SPELLING_ERROR_25">NIC</span> properties, you can allow more opportunity for short names to resolve. By that I mean when you commonly go to SERVER1 rather than typing SERVER1.domain1.com, the suffix list will kick in to help.</div><div>5) For file shares, use the <span class="blsp-spelling-error" id="SPELLING_ERROR_26">IP</span>. If you can't map a drive or reach a resource through \\SERVER1 then just type \\<span class="blsp-spelling-error" id="SPELLING_ERROR_27">IPADDRESS</span> and see if you can reach the file share. When <span class="blsp-spelling-error" id="SPELLING_ERROR_28">DNS</span> issues are in play, often it is best to just work around it until the dust settles. Make sure you document the places where you do this type of thing so you can go back and change them to names when the issues are resolved.</div><div>6) If you must, change the hosts file. I save this for a last resort, but especially on servers, it is sometimes necessary in order to get processes working quickly. The hosts file (on Windows <span class="blsp-spelling-error" id="SPELLING_ERROR_29">XP</span>/2003, find it at Windows\system32\drivers\etc\hosts) allows you to bypass the <span class="blsp-spelling-error" id="SPELLING_ERROR_30">DNS</span> server. You type in an <span class="blsp-spelling-error" id="SPELLING_ERROR_31">IP</span> address followed by the name you want to use to resolve to that <span class="blsp-spelling-error" id="SPELLING_ERROR_32">IP</span> address. For example, adding "1.1.1.1 <span class="blsp-spelling-error" id="SPELLING_ERROR_33">MyPC</span>" to the hosts file means that if you ping or reference "<span class="blsp-spelling-error" id="SPELLING_ERROR_34">MyPC</span>" it will resolve to <span class="blsp-spelling-error" id="SPELLING_ERROR_35">IP</span> 1.1.1.1. It is like a personal <span class="blsp-spelling-error" id="SPELLING_ERROR_36">DNS</span> server for your PC/Server managed only by you.</div><div>WARNING: If you make changes to this file, you are overriding the real <span class="blsp-spelling-error" id="SPELLING_ERROR_37">DNS</span> server so do it with caution, and preferably only do it on a temporary basis. If you leave in your changes, you risk having the <span class="blsp-spelling-error" id="SPELLING_ERROR_38">IP</span> changing again in the future, the <span class="blsp-spelling-error" id="SPELLING_ERROR_39">DNS</span> server updating, and you never getting the changes.</div><div><br /></div><div>Those are some of the highlights of repeating issues and resolutions I used this last week. Hope it helps someone else in their time of transition.</div>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-25603982653301223002009-09-30T21:54:00.004-05:002009-09-30T22:45:56.751-05:00Handling Layered TransitionsWe've all been there. The company is adding a new domain and the PCs and servers must be moved to the domain. A new file server is replacing the old one, and everyone must be remapped to use the new server. The servers are being moved to an off-site data center, and all the external IPs must be rerouted. The company is moving offices.<div><br /></div><div>Any of these transitions can cause stress in the office. In my case, we are going through all of the above.</div><div><br /></div><div>So how do you keep your sanity and avoid self-destructing during these stressful times? Here are a few tips I am trying to follow.</div><div><br /></div><div>1) Always remember the pressure that everyone else is feeling. This means you can't take things personally if emotions run high, as they are bound to do. Try to be the calm voice of reason.</div><div><br /></div><div>2) Find out how you can help rather than complaining about mistakes. When everyone is overwhelmed by the pressures of the transition, it is inevitable that some details will be overlooked and some mistakes will be made. What is important is that you contribute to the solutions.</div><div><br /></div><div>3) Put in the hours that are needed to get the job done. I like my home life as much as anyone, but these transition times are time limited, they don't go on forever. If you put in extra time to make it go more smoothly, and your co-workers do the same, you will all have a much smoother transition.</div><div><br /></div><div>The bottom line to me is that major transitions are always tough, and sometimes you just have to gut it out.</div>J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-3704579901951763432009-09-20T18:37:00.001-05:002009-09-20T18:37:00.099-05:00Logmein.com Ups and DownsI have been using <a href="http://www.logmein.com/"><span id="SPELLING_ERROR_0" class="blsp-spelling-error">Logmein</span>.com </a>for about three weeks now to access my PC. As a quick overview, <span id="SPELLING_ERROR_1" class="blsp-spelling-error">logmein</span>.com allows you to remotely access your PC from any computer with an <span id="SPELLING_ERROR_2" class="blsp-spelling-error">internet</span> connection using a web browser. You install a local service on the PC that you are logging into which talks to the <span id="SPELLING_ERROR_3" class="blsp-spelling-error">logmein</span> server, thereby resolving your <span id="SPELLING_ERROR_4" class="blsp-spelling-error">IP</span> address even if you have a floating <span id="SPELLING_ERROR_5" class="blsp-spelling-error">IP</span> on a home <span id="SPELLING_ERROR_6" class="blsp-spelling-error">internet</span> connection like <span id="SPELLING_ERROR_7" class="blsp-spelling-error">DSL</span>.<br /><br />This isn't anything new, and <span id="SPELLING_ERROR_8" class="blsp-spelling-error">GoToMyPC</span>.com seems to be the most heavily marketed player at this point. However, <span id="SPELLING_ERROR_9" class="blsp-spelling-error">logmein</span>.com offers a free option with limited functionality. Basically, you can <span id="SPELLING_ERROR_10" class="blsp-spelling-error">login</span> and use your PC but you can not transfer files or print locally.<br /><br />You can register multiple PCs in your one website account, thereby simplifying your remote access needs.<br /><br />Overall I have to say I am very impressed. Typically I have used Windows remote desktop to connect to my PC in <span id="SPELLING_ERROR_11" class="blsp-spelling-corrected">conjunction</span> with a <span id="SPELLING_ERROR_12" class="blsp-spelling-error">VPN</span> connection. Remote desktop is great but I hate dealing with the slow speed of the <span id="SPELLING_ERROR_13" class="blsp-spelling-error">VPN</span>, and the <span id="SPELLING_ERROR_14" class="blsp-spelling-error">VPN</span> drop outs.<br /><br />With <span id="SPELLING_ERROR_15" class="blsp-spelling-error">logmein</span>, when I first <span id="SPELLING_ERROR_16" class="blsp-spelling-error">login</span> the screen resolution is sized to fit the browser and it maintains the remote <span id="SPELLING_ERROR_17" class="blsp-spelling-error">PC's</span> aspect ratio. This can be very handy for quick access to do something and then log back out. A complaint I have always had about remote desktop is that the screen will <span id="SPELLING_ERROR_18" class="blsp-spelling-error">resize</span> if you are logging in from a PC that uses smaller screen resolution than the remote PC. This can be a slow and sometimes messy process.<br /><br />On the flip side, my beloved shortcut keys don't work through <span id="SPELLING_ERROR_19" class="blsp-spelling-error">Logmein</span>, while they do work with remote desktop. Honestly though, this is the main shortcoming that I have found so far. Overall, I'm a fan, and I think I will continue to be a user.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com1tag:blogger.com,1999:blog-7644527879492421005.post-15905275516722917352009-09-17T19:38:00.000-05:002009-09-17T19:38:00.702-05:00Windows Shortcut KeysSometimes when I use Windows shortcut keys, it reminds me of the good old days of <span id="SPELLING_ERROR_0" class="blsp-spelling-error">Wordperfect</span>. <span id="SPELLING_ERROR_1" class="blsp-spelling-error">Ok</span>, I take that back. Trying to remember those <span id="SPELLING_ERROR_2" class="blsp-spelling-error">hotkeys</span> was <span id="SPELLING_ERROR_3" class="blsp-spelling-corrected">miserable</span>. That doesn't change the fact that there are a few Windows shortcut keys that I use every day and don't want to live without.<br /><br />Here are a few of my favorites.<br /><br /><span id="SPELLING_ERROR_4" class="blsp-spelling-error">WindowsKey</span> + E<br />I hate digging for Windows Explorer in the Start Menu or right-clicking the Start Menu to explore. Give me a quick Win+E and I'm in business.<br /><br /><span id="SPELLING_ERROR_5" class="blsp-spelling-error">WindowsKey</span> + R<br />Quick access to the Run... box makes for a quick "<span id="SPELLING_ERROR_6" class="blsp-spelling-error">cmd</span>" or "<span id="SPELLING_ERROR_7" class="blsp-spelling-error">regedit</span>" or whatever the occasion calls for.<br /><br /><span id="SPELLING_ERROR_8" class="blsp-spelling-error">WindowsKey</span> + L<br />I usually Lock my PC when I wander away for lunch or at the end of the day. While you can get there from <span id="SPELLING_ERROR_9" class="blsp-spelling-error">CTRL</span> + ALT + DEL, this is even faster.<br /><br /><span id="SPELLING_ERROR_10" class="blsp-spelling-error">CTRL</span> + SHIFT + <span id="SPELLING_ERROR_11" class="blsp-spelling-error">ESC</span><br />Fast access to the Task Manager so I can see which program is hammering the CPU or eating all the memory.<br /><br /><span id="SPELLING_ERROR_12" class="blsp-spelling-error">CTRL</span> + ALT + DEL<br />The old classic which I mainly use for a password change.<br /><br />Feel free to comment with any of your favorite shortcut keys.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-83086095181290431452009-09-14T18:35:00.002-05:002009-09-14T18:41:24.458-05:00Domain Move Part 2After the first attempt at moving a SQL 2005 Server from one domain to another, I learned several lessons to help me make the permanent transition to the new domain. Here are a few of the key things I checked and/or changed.<br /><br />- Verify that the new DNS server has entries for the short names of all servers that SQL Server needs to access. I found that several servers would resolve to [servername].[full domain name] but not to simply [servername]. I had to request CNAME entries for all the resources that required (due to extensive existing code) the ability to access the server through the short name.<br />- Make sure all services are running under local accounts.<br />- Make sure the Reporting Services execution account is running under a local account.<br />- If any objects are owned by domain level accounts, change ownership to local accounts.<br />- If any SQL Agent Jobs are scheduled to run under domain accounts, change them to local accounts.<br />- Check SSIS packages for any domain account ownership and change to local account.<br /><br />I still had a few issues, but for the most part I was able to change one SQL 2005 Server from one domain to another and add a SQL 2005 and a SQL 2000 server to the domain from a workgroup with little trouble after checking the previous list and making changes as needed.<br /><br />If you have any more tips, please feel free to leave comments to flesh out the topic.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-85049518195911122482009-09-10T20:34:00.001-05:002009-09-10T20:34:00.197-05:00FILESTREAM Article on SQLServerCentral.comOne week ago, an article I wrote on FILESTREAM in SQL 2008 was published on <a href="http://www.sqlservercentral.com/">SQLServerCentral.com</a>. This was the first time I had written for a professional publication, online or in print, and I want to thank <a href="http://www.sqlservercentral.com/Authors/Articles/Steve_Jones/3/">Steve Jones </a>for giving me the opportunity.<br /><br />In case you missed it, here is a link to my article.<br /><a href="http://www.sqlservercentral.com/articles/FILESTREAM/67668/">http://www.sqlservercentral.com/articles/FILESTREAM/67668/</a><br /><br />I highly recommend that those of you with any inclination toward documentation or training start writing articles. It not only forces you to dig deeper on a subject than you may have done otherwise, but it also helps to establish you as a professional in the field. I know we all have busy lives, but I think it is worth it. Only time will tell.<br /><br />And speaking of Steve Jones, check out his recent <a href="http://www.sqlservercentral.com/blogs/steve_jones/archive/2009/09/09/writing-a-technical-article-where-to-publish.aspx">blog entry on Technical Article Writing </a>where he lists several good places to submit your work.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-18752662753475502432009-09-09T20:20:00.001-05:002009-09-09T22:10:47.909-05:00T-Mobile's Cheap Internet and Mobile BloggingI am using a 30 day trial of T-Mobile's web plan for cell phones. This plan includes unlimited web access but does not include downloads or a text plan. The up side... it only costs $10/Month.<br /><br />So far I am very pleased. In fact I am writing this blog on my Shadow phone at a time that would usually be lost to simple waiting. Since I am used to the two letters per key typing method, I can crank out the words at about 60% of keyboard typing speed.<br /><br />The strange thing is that T-Mobile limits this cheap web plan to phones that it considers non-smart phones. If you have a G1 you have to use at least the basic $25/Month data plan. Even stranger is the fact that they don't consider a phone like the Shadow, which runs Windows Mobile 6, a smart phone. But hey, I'm not complaining.<br /><br />This little phone is still not as efficient as the ever popular netbooks, but on the flip side I always have my phone. Mostly I think it fulfills a role that I require within the ever broadening array of Internet enabled devices.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-42694043269122458562009-09-03T07:39:00.005-05:002009-09-03T10:10:59.176-05:00SSIS Packages fail on ODBC connections after .NET Framework 3.5 SP1 or 2.0 SP2If you work with ODBC connections through third-party ODBC drivers such as MySQL, you may have already experienced the pain of this Windows Critical Update. This is my tale of woe.<br /><br />I was innocently applying critical Windows Updates to one of my SQL Servers, one is the .NET Framework 3.5 SP1. I read the details and saw that it also includes SP2 for both .NET 2.0 and 3.0, but still it is a critical update. As always, I there may be some bumps in the road but I figure I will just work through them. Updates applied. Basic database and reporting all test fine. So far so good.<br /><br />The next morning, all of my SSIS packages that pull data through ODBC connections have failed, and as a result all of my subsequent scheduled reporting has also failed. That is what I call having Monday on Wednesday.<br /><br />I know what happened due to my daily email report of all jobs from the last 24 hours, sorted by the errors first. In checking my SSIS log files, I find this error:<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">OnError,[Server],[UserAccount],[TableName],{GUID1},{GUID2},9/1/2009 11:15:48<br />PM,9/1/2009 11:15:48 PM,-1071607767,0x,SSIS Error Code<br />DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DataReader Source"<br />(31)" failed because error code 0x80131937 occurred, and the error row<br />disposition on "output column "[ColumnName]" (1848)" specifies failure on error.<br />An error occurred on the specified object of the specified component.<br />There may be error messages posted before this with more information about the<br />failure.</span><br /></span><br />After some research, I find that this is a known issue on an <a href="http://blogs.msdn.com/sqlblog/archive/2009/04/09/after-installation-of-net-framework-3-5-sp1-or-net-framework-2-0-sp2-ssis-packages-using-odbc-3rd-party-drivers-may-fail.aspx">msdn blog</a>. It seems that .NET Framework 2.0 SP2 changes the way that the OdbcDataReader checks for Nulls, resulting in compatibility issues with some ODBC drivers. It impacted both the MySQL and CVIEW (for ISAM connections) ODBC drivers that I was using to pull in all my data.<br /><br />As explained in the excellent blog post, the preferred option is to add error output to your SSIS packages, thereby handling the bogus errors. Unfortunately I have six or seven packages with twenty or more DataReaders in each package, so it would not be a quick fix. The blog mentions option #2 is to roll back your updates, which for me was the only option. I had production people waiting.<br /><br />Due to the 3.5 constraints, I had to remove .Net 3.5 SP1, then .Net 3.0 SP2, and finally .Net 2.0 SP2. Then, because 3.0 and 2.0 updates are slipstreamed into the 3.5 SP1 update, at that point I had zero .Net frameworks installed. I had to reinstall .Net 2.0 and SP1, which is all I need for my SQL 2005 and related functionality. It took roughly 20-30 minutes to uninstall and reinstall along with reboots, not including any download requirements.<br /><br />Afterward, I started playing with the website and realized the IIS default page was not even available. I looked at properties in the default website, looked at the ASP.NET tab... bingo, the ASP.NET version is set to... blank. I click the drop-down and see .Net 2.0 is available, but knowing that the scope of the settings goes far beyond this setting, I went back to the command line.<br /><br />I knew I needed to run regiis, but I don't do this frequently so I searched, <a href="http://msdn.microsoft.com/en-us/library/k6h9cz8h(VS.80).aspx">found a refresher</a>, and ran the following command:<br /><br />C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\Aspnet_Regiis -i<br /><br />Because I am on a 64-bit server, I was wroking in the Frameword64 subdirectory, but if you are on 32-bit, it is the same but you browse to the Framework subdirectory.<br /><br />Now my baseline ASP.NET is configured in IIS. I browse to the "/reportserver" directory in IE8 and get a (403) Forbidden error. That's an improvement.<br /><br />I looked around in various places at various settings for a bit, just trying to help me think. In the Reporting Services Configuration Manager, it hit me that I had just made a mess of IIS and in the process the virtual directories had been through the ringer. So under Report Server Virtual Directory and Report Manager Virtual Directory, I checked the "Apply default settings" checkbox and applied the changes.<br /><br />Boom, back in business. Reports were up and running and I was able to run my SSIS package to generate all my scheduled reports. Then I remembered one other little ASP.NET application that I wrote for managing the report schedules. Tried to pull it up in the browser and it didn't open.<br /><br />After looking at IIS settings for a while, I finally found the problem. So I right-click on the Virtual Directory, go to Properties-->Documents tab and default.<span id="SPELLING_ERROR_0" class="blsp-spelling-error">aspx</span> is not in the list. In the process of resets, apparently the default content page list had been reset on my virtual directory. I added default.<span id="SPELLING_ERROR_1" class="blsp-spelling-error">aspx</span> to the list and boom, everything is back up and running.<br /><br />I hope that at some point Microsoft makes allowances for this issue in their .Net updates but in the meantime, doing without .Net 2.0 SP2 is fine by me.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0tag:blogger.com,1999:blog-7644527879492421005.post-35457010031899004892009-08-29T09:36:00.000-05:002009-08-29T09:36:20.581-05:00First Attempt At Changing Domains on a SQL 2005 ServerWell I have finished my first pass at moving one of our three SQL Servers to our new domain. I had to roll it back for the moment, but I learned a great deal in the process. This server runs on Windows 2003 Server 64-bit and SQL 2005 Enterprise Edition 64-bit. This server also runs Reporting Services and Integration Services packages to run scheduled reporting, so there are a number of things to test.<br />
<br />
1) I changed the DNS Server IP addresses in the network settings to point at the DNS server which is part of the new Active Directory.<br />
<br />
2) I changed the domain from the current domain to the new domain and completed the required reboot.<br />
<br />
3) I setup security groups from the new domain on the SQL Server and assigned them the appropriate permissions. By assigning the domain groups as logins, it simplifies adding and removing employee permissions by making it a simple Active Directory group addition or removal for the user account.<br />
<br />
4) I could connect to the server from my local PC (which I had already moved to the domain) from Management Studio. That gave me comfort that basic name resolution and integrated security were working properly.<br />
<br />
5) From an IE browser, I tried to pull up the reporting services browser. No luck. In researching it, I found that the encryption key was not working after the domain change. I also found that a domain account from the old domain was setup for "Execute as" permissions. I attempted to change this to a new domain account but it failed. Tried restoring the backup of the encryption key, failed. I tried creating a new encryption key, failed.<br />
<br />
Basically everything I tried to get Reporting Services up and running under the new domain failed. I was able to join the old domain and reset Reporting Services back to a working state so I can do some more research on the issues I encountered. I am considering moving the server to workgroup security and getting everything working right first, and then joining the new domain. Until I do some more research I am not sure.<br />
<br />
Based on month end and some other priorities, I probably will not get another attempt at this until the middle of September. If anyone reading this has some advice or some good articles/blog entries on the subject, I welcome them.J Hugh Thomashttp://www.blogger.com/profile/13917026021776686566noreply@blogger.com0