Focusing 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.

Saturday, September 21, 2013

Visual Studio, SQL Management Studio, and SQL Express on a Windows 8 Tablet

I 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.

Side note, my tablet started with around 32GB of space free after pre-installed programs.

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.

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.

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.

Saturday, September 14, 2013

Using IIF instead of CASE

For 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.

Thank you SQL Server 2012.  The IIF has arrived.

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.

Declare @Value1 Int = 1,

@Value2 Int = 2

Print '@Value1=' + Convert(varchar(1),@Value1)

Print '@Value2=' + Convert(varchar(1),@Value2)

Print IIF(@Value1 = @Value2,'They Equal','They Do Not Equal')

Set @Value2 = 1

Print '@Value1=' + Convert(varchar(1),@Value1)

Print '@Value2=' + Convert(varchar(1),@Value2)

Print IIF(@Value1 = @Value2,'They Equal','They Do Not Equal')

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.

Until next time, may well defined requirements be at your back and realistic timelines lie before you.

Thursday, November 22, 2012

SQL Server 2012, and the fog clears on Thanksgiving

If 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.

And then it finally happens.

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.

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.

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.

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.

Happy Thanksgiving to all!

Saturday, May 12, 2012

SQL Saturday #112 - Birmingham 2012

Today 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.

Time Saving Tips & Tricks for SSMS 2012 - Aaron Nelson
What's New in SSIS 2012 - Christopher Price
SQL Server 2012: What's New - Bob Cheatham
Data Quality Services 2012 - Chris Price
Introduction to FileTables - Sven Aelterman
Windowing Functions: The Reason to Upgrade to 2012 - Kevin Boles

As you can tell, it was a great day for learning more about SQL 2012.  


Check the site and find a session near you.  You will not regret it.

Saturday, July 30, 2011

SQL Saturday #81 Birmingham

Today 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".

We had five different sessions to choose from in each of the six time slots. My choices today were

Database Design
SQL "Denali" High Availability
SQL Statistics
Building a Professional Development Plan
Table Partitioning
Advanced DW Scenarios in SSIS

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.

Sunday, July 24, 2011

Upcoming SQL Saturday in Birmingham, AL

It 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.

http://sqlsaturday.com/81/eventhome.aspx

Thursday, November 4, 2010

Send Email in SQL 2008 Express

No Email functionality in SQL Express? No problem. This is a nice article on using CLR to send email in SQL 2008 Express.
http://www.sqlservercentral.com/articles/SQL+Express+2008/71341/

Friday, April 23, 2010

Using Extended Properties for Documentation

This is a nice article on using extended properties to document your database. Much easier than maintaining a Word document.

Create a SQL Server Data Dictionary in Seconds using Extended Properties

Sunday, March 28, 2010

SQL Saturday Free Training

I just attended my first free SQL 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.

A big thank you to all the sponsors and the volunteers that made the day possible.

I want to encourage all of you reading this to go to the SQL Saturday website and find SQL Saturday's in your region. There were many people that I met in sessions who were from Tennessee and Georgia, and some had been to as many as three SQL Saturday's in different locations. It is worth a little driving time.

The hardest part was choosing which sessions to attend, 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.

Parallel Query Execution Dive Deep presented by Kevin Boles.
Database Design Patterns presented by Louis Davidson.
Tuna Helper for SQL Server DBAs presented by Janis Griffin.
Tricks & Tips for Writing Better Queries presented by Joe Webb.
SQL Server Locking and Blocking Made Simple presented by Joe Webb.

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. SQL Server is so robust, there are hundreds of ways to carve the turkey.

I hope if you have never attended SQL Saturday that you will make it a priority. All it costs you is one Saturday.

Monday, December 28, 2009

Redirecting SSIS Data Errors to Avoid Package Failure

When setting up a new Integration Services package, is it often advantageous to leave your errors untrapped 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.


So how do you do it? I'll cover it in broad strokes.

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.

For example, the source table, lets call it T1 has these fields:
ID Int Not Null
Name varchar(30) Not Null
Addr varchar(40) Not Null


To handle error output, you would create a table (T2) with these fields:

ErrCode varchar(100) Null
ErrColumn varchar(100) Null
ID varchar(100) Null
Name varchar(200) Null
Addr varchar(200) Null

Let's go over the noteworthy differences. First I added the ErrCode and ErrColumn fields which will be populated with data from the SSIS error handling.


Most importantly, I changed the data types on all fields to varchar 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.


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.


Now that the structure is in place, it is time to add to the SSIS 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.

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.

Now that the red line is attached, you can edit the properties in your T2 destination to insure that the ErrCode, ErrColumn, 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.

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 SSIS package.

Tuesday, December 22, 2009

SSIS Package Credentials

This is a great article on setting up SSIS Package Credentials, which is more involved than the old DTS days. If you need to schedule your SSIS packages, read this article and it will save you a lot of troubleshooting time.

http://www.sqlservercentral.com/links/81443/131962

Friday, December 18, 2009

Creating a Reporting Service Report Template

I read this good, simple process for creating a template for your company reports that was worth sharing.

http://sqlblogcasts.com/blogs/testas/archive/2009/12/16/creating-a-report-template-in-ssrs-2000-2005-2008.aspx

Friday, December 11, 2009

Netbook upgrade to Windows 7 with USB key

Great tip on how to setup your thumb drive for loading Windows 7. Is it time for a netbook upgrade?

http://technet.microsoft.com/en-us/magazine/dd535816.aspx

Wednesday, November 25, 2009

Building My First Website Using Microsoft Expressions

In 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.

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.

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.

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.

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.

Saturday, November 14, 2009

Conditionally Blocking Scheduled Reporting

As is true for many DBAs, I have developed a custom set of SSIS 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 PDFs and Excel results.

Then came the inevitable weekend of trouble. The set of SQL 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 SQL Server databases still contained yesterday's data, the report schedule had no problem completing the scheduled reporting.

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.

The solution is fairly simple. The SSIS packages that load the SQL 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.

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 SQL Agent job that runs the scheduled reports and this time it will succeed.

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?"

Sunday, October 18, 2009

A New Tablet Computer

This 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.


Researching and Bypassing DNS Issues

So 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.

Move the servers to an off-site data center - almost check.

We moved thirty seven servers to a data center last weekend, changed all of the external and internal IP 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.

DNS issues, both internal and external, are one of the big problems in a transition like this. The DNS server must be properly updated with all IP changes, the local office DNS server must be updated as well, and the external registrar must be updated with new external IP addresses, which can take a few days to propagate.

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 DNS. Here are some of the main testing steps I took to evaluate problems.

1) Ping the name you are trying to reach. If it resolves to the correct IP address but times out, there may be a real connection issue. If it resolves to the old IP address or something else unexpected...
2) Flush the DNS. From the command prompt, just run "ipconfig /flushdns" and it will remove any cache of DNS resolution so you can verify the IP is coming from the latest changes on the DNS server. Also this is a good time to check the DNS1 and DNS2 IP addresses in the NIC properties to verify the PC/Server is pointing at the correct DNS server. Then Ping again. If you get the same problem, move on to the next step.
3) Ping the IP 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.
4) Check DNS 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 config errors. So perhaps SERVER1.domain1.com now is setup under SERVER1.domain2.com. If you add DNS suffixes to the suffix list in the NIC 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.
5) For file shares, use the IP. If you can't map a drive or reach a resource through \\SERVER1 then just type \\IPADDRESS and see if you can reach the file share. When DNS 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.
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 XP/2003, find it at Windows\system32\drivers\etc\hosts) allows you to bypass the DNS server. You type in an IP address followed by the name you want to use to resolve to that IP address. For example, adding "1.1.1.1 MyPC" to the hosts file means that if you ping or reference "MyPC" it will resolve to IP 1.1.1.1. It is like a personal DNS server for your PC/Server managed only by you.
WARNING: If you make changes to this file, you are overriding the real DNS 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 IP changing again in the future, the DNS server updating, and you never getting the changes.

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.

Wednesday, September 30, 2009

Handling Layered Transitions

We'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.

Any of these transitions can cause stress in the office. In my case, we are going through all of the above.

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.

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.

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.

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.

The bottom line to me is that major transitions are always tough, and sometimes you just have to gut it out.

Sunday, September 20, 2009

Logmein.com Ups and Downs

I have been using Logmein.com for about three weeks now to access my PC. As a quick overview, logmein.com allows you to remotely access your PC from any computer with an internet connection using a web browser. You install a local service on the PC that you are logging into which talks to the logmein server, thereby resolving your IP address even if you have a floating IP on a home internet connection like DSL.

This isn't anything new, and GoToMyPC.com seems to be the most heavily marketed player at this point. However, logmein.com offers a free option with limited functionality. Basically, you can login and use your PC but you can not transfer files or print locally.

You can register multiple PCs in your one website account, thereby simplifying your remote access needs.

Overall I have to say I am very impressed. Typically I have used Windows remote desktop to connect to my PC in conjunction with a VPN connection. Remote desktop is great but I hate dealing with the slow speed of the VPN, and the VPN drop outs.

With logmein, when I first login the screen resolution is sized to fit the browser and it maintains the remote PC's 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 resize 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.

On the flip side, my beloved shortcut keys don't work through Logmein, 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.

Thursday, September 17, 2009

Windows Shortcut Keys

Sometimes when I use Windows shortcut keys, it reminds me of the good old days of Wordperfect. Ok, I take that back. Trying to remember those hotkeys was miserable. 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.

Here are a few of my favorites.

WindowsKey + E
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.

WindowsKey + R
Quick access to the Run... box makes for a quick "cmd" or "regedit" or whatever the occasion calls for.

WindowsKey + L
I usually Lock my PC when I wander away for lunch or at the end of the day. While you can get there from CTRL + ALT + DEL, this is even faster.

CTRL + SHIFT + ESC
Fast access to the Task Manager so I can see which program is hammering the CPU or eating all the memory.

CTRL + ALT + DEL
The old classic which I mainly use for a password change.

Feel free to comment with any of your favorite shortcut keys.