SQLTeam.com Articles

Testing with Profiler Custom Events and Database Snapshots

Mon, 22 Jun 2009 00:00:00 GMT

We've all had them. One of those stored procedures that is huge and contains complex business logic which may or may not be executed. These procedures make it an absolute nightmare when it comes to debugging problems because they're so complex and have so many logic offshoots that it's very easy to get lost when you're trying to determine the path that the procedure code took when it ran. Fortunately Profiler lets you define custom events that you can raise in your code and capture in a trace so you get a better window into the sub events occurring in your code. I found it very useful to use custom events and a database snapshot to debug some code recently and we'll explore both in this article. I find raising these events and running Profiler to be very useful for testing my stored procedures on my own as well as when my code is going through official testing and user acceptance. It's a simple approach and a great way to catch any performance problems or logic errors.

Advanced SQL Server 2008 Extended Events with Examples

Mon, 25 May 2009 00:00:00 GMT

In the previous article we learned about SQL Server 2008 Extended Events terminology, components and took a look at a simple example. In this article we'll take a deeper look with some more complex examples.

Introduction to SQL Server 2008 Extended Events

Tue, 19 May 2009 00:00:00 GMT

SQL Server 2008 Extended Events are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events.

Monitoring SQL Server Agent with Powershell

Tue, 24 Mar 2009 00:00:00 GMT

This article introduces the reader to Powershell. The application that it demonstrates is one that monitors SQL Server Agent to make sure it is running.

SQL Server Version

Thu, 29 Jan 2009 00:00:00 GMT

I'm continually trying to track down what service packs are installed on various SQL Servers I support. I can never find the right support page on Microsoft's site. So here's an article with all the SQL Server version information I can track down. If you know of any older versions or can help me fill out any missing data, please post in the comments and I'll update the article.

Scheduling Jobs in SQL Server Express - Part 2

Mon, 01 Dec 2008 00:00:00 GMT

In my previous article Scheduling Jobs in SQL Server Express we saw how to make simple job scheduling in SQL Server 2005 Express work. We limited the scheduling to one time or daily repeats. Sometimes this isn't enough. In this article we'll take a look at how to make a scheduling solution based on Service Broker worthy of the SQL Server Agent itself.

Scheduling Jobs in SQL Server Express

Wed, 27 Aug 2008 00:00:00 GMT

As we all know SQL Server 2005 Express is a very powerful free edition of SQL Server 2005. However it does not contain SQL Server Agent service. Because of this scheduling jobs is not possible. So if we want to do this we have to install a free or commercial 3rd party product. This usually isn't allowed due to the security policies of many hosting companies and thus presents a problem. Maybe we want to schedule daily backups, database reindexing, statistics updating, etc. This is why I wanted to have a solution based only on SQL Server 2005 Express and not dependent on the hosting company. And of course there is one based on our old friend the Service Broker.

Using SET NULL and SET DEFAULT with Foreign Key Constraints

Tue, 12 Aug 2008 00:00:00 GMT

Cascading Updates and Deletes, introduced with SQL Server 2000, were such an important, crucial feature that it is hard to imagine providing referential integrity without them. One of the new features in SQL Server 2005 that hasn't gotten a lot of press from what I've read is the new options for the ON DELETE and ON UPDATE clauses: SET NULL and SET DEFAULT. Let's take a look!

SQLTeam.com Reader Survey

Wed, 06 Aug 2008 00:00:00 GMT

I'm conducting a survey of the readers on the site. If you have a few moments I'd appreciate it if you could fill it out. It's only nine questions and will take just a few minutes. I'm trying to learn more about what topics are interesting to SQLTeam readers.

SQL Server 2008: Table Valued Parameters

Thu, 24 Jul 2008 00:00:00 GMT

In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multiple rows of data to SQL Server, developers either had to send one row at a time or come up with other workarounds to meet requirements. While a VB.Net developer recently informed me that there is a SQLBulkCopy object available in .Net to send multiple rows of data to SQL Server at once, the data still can not be passed to a stored proc.

Possibly the most anticipated T-SQL feature of SQL Server 2008 is the new Table-Valued Parameters. This is the ability to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.

Alerts for when Login Failures Strike

Mon, 14 Jul 2008 00:00:00 GMT

When repeated SQL Server login failures occur, a DBA should investigate. It could just be someone repeatedly typing in the wrong password. Worst case is a virus attack flooding your network with connection requests. Receiving an e-mail while login failures are occurring allows DBAs to investigate and fix the issue as soon as possible. So how is DBA notified of login failures without flooding their inbox?

PASS Summit 2008 Speaker List

Wed, 25 Jun 2008 00:00:00 GMT

PASS released the list of Spotlight Speakers and their session titles and abstracts. They also released a partial list of the regular session speakers with their titles and abstracts. This is the first 50 sessions of the 130+ total sessions. As more speakers confirm their participation they'll update the speaker list. Don't forget that the price increases by $200 after June 30th, 2008.