Today’s post is a short and simple one. No system is error free. The key is in providing easy and efficient mechanisms to capture these errors and enabling the users of the system to extract meaningful information from these error logs.

Microsoft SQL Server logs certain system events and user-defined events to both – the SQL Server error log and the Microsoft Windows application log. Here is what MSDN has to say about SQL Server error logs:

“View the SQL Server error log to ensure that processes have completed successfully (for example, backup and restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, or other server-level error messages.”

Every time the SQL Server instance is started, a new SQL Server error log is created. The default number of logs that the SQL Server will collect before recycling the logs is 6. A user can customize the Error log limit from SSMS by following the steps outlined below:

1. In the Object Explorer, expand the SQL Server instance node
2. Expand Management and navigate to SQL Server Logs
3. 
4. Right-click SQL Server Logs and click “configure”
5. Only 2 options are available:
• Limit the number of the error log files before they are recycled
• Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of SQL Server is started
• SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below
• Maximum number of error log files
• Specify the maximum number of error log files created before they are recycled
• The default is 6, which is the number of previous backup logs SQL Server retains before recycling them
• The user can specify a maximum log file count of 99
6. 

References:

• Viewing the SQL Server error log
• FIX: The size of the SQL Server 2005 error log file or of the SQL Server 2008 error log file grows very quickly when query notifications are created and destroyed in a high ratio

Until we meet next time,

Be courteous. Drive responsibly.

Tweet to @nakulv_sql

It is very important to keep revisiting one’s code – especially, when the underlying platform changes or when teams start using newer features within legacy code.

Until SQL Server 2005, the typical understanding of the keyword “DECLARE” was that it had only one meaning – it was used to define an object/variable within a T-SQL batch. Any variables/objects defined using DECLARE would have a value of NULL, unless a value is explicitly assigned to them. Starting SQL Server 2008, however, things changed. DECLARE now had 2 meanings:

• DECLARE is still used to “define” an object/variable within a T-SQL batch AND
• DECLARE can also be used to assign a value to a variable

At first glance, this is good! It saves 2 lines of code:

USE tempdb
GO
DECLARE @x INT
SET @x = 123

becomes:

USE tempdb
GO
DECLARE @x INT = 123

But, can things really be this simple? The simple answer: NO.

DECLARE can now raise an exception

Up until SQL Server 2008 came around, the sole purpose of DECLARE was to ‘define’ objects. Unless something was horribly wrong, this statement was pretty harmless. However, because the purpose of the DECLARE is not to define & assign, things get a little bit complicated. The assignment part of the DECLARE keyword can now raise an exception. Here’s an example.

SQL Server 2005 and below

Because DECLARE would not generate an exception, it was okay to have the DECLARE statements outside of the TRY…CATCH blocks.

USE tempdb
GO
DECLARE @x INT

BEGIN TRY
    SET @x = 'abc'
    SELECT @x
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_SEVERITY() AS ErrorSeverity,
           ERROR_STATE() AS ErrorState,
           ERROR_PROCEDURE() AS ErrorProcedure,
           ERROR_LINE() AS ErrorLine,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

Because we are trying to assign a text value to an Integer field, an exception would be raised within the TRY block and caught by the CATCH block.

SQL Server 2008 and above

Attempting to perform both the definition and the assignment using a DECLARE statement, outside of the TRY…CATCH would result in an unhandled exception.

USE tempdb
GO
DECLARE @x INT = 'abc'

The unhandled exception would look like:

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value ‘abc’ to data type int.

Solution:

The solution is very simple – wrap the DECLARE within a TRY…CATCH block.

USE tempdb
GO
BEGIN TRY
    DECLARE @x INT = 'abc'
    SELECT @x
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_SEVERITY() AS ErrorSeverity,
           ERROR_STATE() AS ErrorState,
           ERROR_PROCEDURE() AS ErrorProcedure,
           ERROR_LINE() AS ErrorLine,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

The output would be a handled error, as demonstrated in the above screenshot.

The morale of the story:

• DECLARE statements involving assignment must now be within a TRY…CATCH block
• Making a change, however seemingly small, requires detailed thought and planning

References:

• DECLARE statement
• Exception handling in T-SQL/TRY…CATCH

Until we meet next time,

Be courteous. Drive responsibly.

Often, students and colleagues come up to me and enquire how do they get started learning about some of the newest technologies. They generally do not seem to have issues with getting the study materials (most common study materials are the many blogs on the Internet and MSDN documentation). The one thing they do have issues with is procuring software to practice their skills on.

Software, like any other commodity has a price. It takes a large amount of money to envision, design, develop, test, market a piece of software. Documentation and after sales support and training are also expensive for any ISV. This stands true for the Microsoft software as well (Operating Systems, Visual Studio, SQL Server and other products). While Microsoft does have the Microsoft Student Partner program for students, the program cannot cover every student in every university on the planet. Also, the program is not available for anybody else who would like to get started on a little programming. So, if you want to get started on one of the new Visual Studio or SQL Server editions and can do without the advanced design, development & test capabilities; then the following are perhaps the pieces of software that you are looking for:

• Visual Studio Express Editions Home page – http://www.microsoft.com/express
• The following products can be downloaded from: http://www.microsoft.com/visualstudio/en-us/products/2010-editions/express
• Visual Studio 2010 for Windows Phone
• Visual Studio Web Developer 2010
• Visual Basic 2010 Express
• Visual C++ 2010 Express
• Visual C# 2010 Express
• SQL Server Express Editions Home page – http://www.microsoft.com/sqlserver/en/us/editions/express.aspx
• Visual Studio Lightswitch – http://www.microsoft.com/visualstudio/en-us/lightswitch

The most attractive part about these “Express” editions is that they are free. However, the salient benefit is that these editions are covered by Microsoft support. They are eligible for automatic product upgrades and you can get all the technical support that you would need.

The other benefit is that you now own a licensed product for free. Isn’t that a wonderful? I use them on my test system at home, and have never felt the need for more.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql

One of the most popular of the blogging tools currently being used is the Windows Live Writer. In order to configure the Windows Live Writer to work with your blog on the Blogs Module of BeyondRelational.com, you only need to follow the following very simple steps. If you don’t have your own blog space, and would like to have one, please refer Appendix A for details on how BeyondRelational.com can help you.

Launch Windows Live Writer and choose to add a new blog account. (Note: If you are using Windows Live Writer on the concerned workstation for the first time, it would directly ask you to Add a blog account. All other steps are same as those described below).

When asked for the type of Blog Service to use, choose “Other”

Next, supply the following information: 1. Your Blog Home page URL (Refer Appendix B to learn how to identify your blog’s home page URL) 2. Your login User Name 3. Your login Password

Next, select the following: 1. Blog type: Metaweblog API 2. Posting URL: http://apis.beyondrelational.com/Metaweblogapi.ashx (NOTE: The blog type and posting URL remain constant for all bloggers throughout the Beyondrelational.com website)

The system will now attempt to detect your blog editor settings

Allow Live Writer to create a temporary post in order to download the blog editing theme. Downloading the blog editing into Windows Live Writer theme allows you to preview your blog as it would appear when published on your blog.

Finally, a confirmation message is displayed confirming that the blog setup with Windows Live Writer is ready for use!

Click “Finish” and that’s it. Windows Live Writer is now configured to post to your blog on BeyondRelational.com.

Important note: The blog stream on the website is a cached page. If your blog is not visible on the stream straightaway, please check back after some time (30 minutes worked for me)

All you need to do now is to start writing! Happy Blogging!

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql

Appendix A: Creating your own blog space on BeyondRelational.com

Creating a blog space on BeyondRelational.com is super easy! Become a member of the BeyondRelational.com community and follow the steps mentioned below to create a new blog:

Under the “Modules” section, navigate to the “Blogs” module and click on “Add New Blog”

Fill in the Title and brief summary for your blog. After you accept to the BeyondRelational.com terms & conditions, you can either choose to publish your blog immediately, or save as a draft for future review.

That’s it! Your blog is now created.

IMPORTANT:

Before you can use this blog space, though, please note the following:

1. Once you choose to “Publish” your blog, the blog is sent to the administrator for approval
2. Once the administrator has approved the blog space request, please allow at least 24 hours for the blog to show up on your profile and become “live”

If you have questions around the BeyondRelational.com terms & conditions of usage or around BeyondRelational.com’s privacy policy, please refer the following links:

• Terms of Usage: http://beyondrelational.com/modules/23/pages/362/posts/7156/terms-of-usage-beyondrelationalcom.aspx
• Privacy Policy: http://beyondrelational.com/modules/23/pages/362/posts/7157/privacy-policy-beyondrelationalcom.aspx

Appendix B: Knowing your Blog’s Home Page URL

When your blog was created, the administrator would have sent across the home page URL for your blog. In case you lost it, or would need to confirm, here are the steps that you can follow:

Go to the “Modules” section of the BeyondRelational.com website. Next, click on “Blogs” to navigate to the Blogs module

Click on the “Bloggers” stream and navigate to your blog space.

Either hover over the blog link or click on it to navigate to your blog’s home page. The highlighted link in the status bar in the image above is the URL for your blog’s home page.

Per the example used in the screenshot, the home page for my blog is: http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx

A lot changed on the technology space this month. Today, I would write about a couple of these announcements that are bound to dominate the technology space in 2012. There are a lot of new announcements coming through, but below are the products that I would be watching out for.

New Server Systems!

SQL Server 2012

The much awaited SQL Server 2012 or “Denali” (aliased: “SQL 11” due to it’s build number) has been Released To Market (RTM). It would be available for General Release around April 01, 2012.

Quoting from the official Microsoft web-site: “As the foundation of the cloud-ready information platform, SQL Server 2012 will help organizations unlock breakthrough insights across the organization as well quickly build solutions and extend data across on-premises and public cloud backed by mission critical confidence.”

• You can read more about this announcement on the SQL Server team’s TechNet Blog here: http://blogs.technet.com/b/dataplatforminsider/archive/2012/03/06/sql-server-2012-released-to-manufacturing.aspx
• If you are interested in downloading SQL Server 2012 RTM (which would automatically expire after 6 months), you can do so from here: http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

If you are an Independent Software Vendor (ISV), you would have already begun evaluating your product for compatibility (and ultimate certification) with the SQL Server 2012 release. If you have not done so yet, it’s not too late. Here are some resources that might be of help:

• Compare the editions: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx
• Changes in licensing: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx
• Deprecated, Discontinued feature list; breaking and behavioural changes: http://msdn.microsoft.com/en-us/library/cc707787(v=sql.110).aspx
• What’s new?: http://social.technet.microsoft.com/wiki/contents/articles/3783.what-s-new-in-sql-server-2012.aspx

A whitepaper on what’s new in SQL Server 2012 is also available at: http://download.microsoft.com/download/E/9/D/E9DBB1BB-89AE-4C70-AF02-AAFC29451A85/SQL_Server_Whats_New_Whitepaper_7_11.pdf

I believe that before you even begin evaluating SQL Server 2012, spend some time to read through the licensing changes. This time, the changes are severe enough that they need immediate attention. They may affect the overall cost of your product/solution.

The Windows Server 8 Operating System

With almost all products moving towards the cloud, it’s time that a dedicated cloud computing server platform be available. Geared towards optimizing virtualization and “delivering the most dynamic, available, and cost-effective server platform for the private cloud”, Windows Server 8 Operating system platform is now in the Beta phase, and is available for review at: http://www.microsoft.com/en-us/server-cloud/windows-server/v8-default.aspx.

After Windows Server 2008 and 2008 R2, the name Windows Server 8 becomes a little confusing, but, it’s still in Beta, and we can hope that the name would change (maybe Windows Server 2012?).

Client System Announcements

3rd party controls

The product that I work upon uses the DevExpress (http://www.devexpress.com/) control suite. They look great, and have the unique “Wow!” factor that makes an application attractive and easy to use. While I spend most of my time within the SQL Server Management Studio, I hear from colleagues that DevExpress is perhaps one of the most popular control suites for WinForms based applications. After all, there has to be a reason why they have been awarded with tons of research & development awards (http://www.devexpress.com/Home/Awards.xml).

They recently announced the product roadmap for 2012: http://www.devexpress.com/Home/Announces/Roadmap-2012.xml.

I would quote the following lines from the roadmap, which essentially contains the gist of all that would shape the technology scene in 2012:

“Simply put: 2012 is going to be the year of Visual Studio 11 and Windows 8. I don’t see anything else on the horizon that is of such importance as these two technologies. Client development will evolve with WinRT, and ASP.NET (and MVC) will continue to grow in influence, especially with the tightening orbits around the whole HTML5/CSS3/JavaScript ecosystem.”

The Windows 8 Operating System – Aero is Out, Metro is In

The Aero interface is perhaps the most distinguishing feature and one of the most dramatic user experience change that Microsoft introduced with Windows Vista and matured with Windows 7. With Windows 8, things are going to be even more exciting:

• From the screenshots that I have seen, there’s NO “start” button!
• Pull-down menus are replaced with clear labels and “charms”
• The whole user experience is similar to the Windows phone – marking the start of an era where portable devices are now shaping the experience on a desktop/laptop
• The user experience now involves a new sense – touch!

Above all, this new user experience has a new name (which I like!): Metro.

Check out more details on this new kid on the block at: http://windows.microsoft.com/en-US/windows-8/consumer-preview

I am all excited and can’t wait to get my hands on a copy of Windows 8 Consumer Preview edition.

Concluding…

Technologies change, but there are very few times wherein so many welcome and exciting changes are being announced from all quarters. The year 2012 is going to be fun!

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql