August 2008 Blog Posts
Late last year, I posted a blog that described the undocumented sp_MSforeachtable stored procedure in Microsoft SQL Server and how it could be used to execute commands on each table within a database. The examples I gave were executing DBCC CHECKTABLE and EXECUTE sp_spaceused() for each table in a given database.
But there's another undocumented stored procedure designed for iteration. This one allows us to iterate through each database in a SQL Server instance. It's called sp_MSforeachdb.
It's usage is very similar to that of sp_MSforeachtable; simply execute the stored procedure, passing it the command that you'd like to execute as...
My good friend Kevin Kline recently posed a question on one of his blogs: what is the best frequency, depth, and type of blog posting? Are readers most interested in more thorough postings that are more akin to full fledged magazine articles, with the understanding that those will appear far less often since they are more time consuming to create? Or is it better to post more frequent, but perhaps less comprehensive postings? Most of these postings would be less than a few hundred words; at the extreme they could be almost Twitter-ish in length and content.
Thanks to everyone who came to my Writing Better Queries session at DevLink this afternoon! I truly appreciate that you took time of your busy schedule to attend! I hope it was worth your while.
As promised, here is a link to the presentation slide deck and demo scripts that I used today.
In addition to the formal feedback that you may have provided to DevLink, I'd love to hear from you. So feel free to either drop me an email directly, or to add comments to the section below.
While teaching a Microsoft SQL Server Reporting Services class in Charlotte, North Carolina, this week, one of the students asked to see an example of a report that uses a Microsoft Excel Spreadsheet as its datasource. Here are some screenshots from the demonstration.
The first step in using an Excel spreadsheet as a datasource is to create a System Data Source Name (DSN) for the spreadsheet. Open the ODBC Data Source Administrator from Control Panel | Administrative Tools.
Add a new System DSN, providing a name in the Data Source Name box, and selecting the workbook that you wish to...
In a prior post, I demonstrated how to use conditional formatting techniques to change the background color of every other row in tabular report. In that example, I used the Immediate If (IIF) function to change the BackgroundColor property of the odd rows to silver.
The IIF function only allows for two states. In my example, the odd rows were silver and the even rows had a white background color.
In the comments section of that post, one reader, Wally, asked to have more than two states, silver, white, and red. Another reader, Darin, replied that you can nest your...
One common question from many of those who are relatively new to Microsoft SQL Server Reporting Services is how to alternate the background color of detail rows in a table data region.
Although I'm sure samples of this have been posted elsewhere on the Internet, I thought I'd share a common technique in the hopes that those searching for a solution will find it, either here or elsewhere.
Let's consider an example. We have an employee phone list report as shown below.
To make the report a little easier to read horizontally, we'd like to change the background color of every...
Fellow SQL Server enthusiast and online friend of mine, Jonathan Kehayias is very active in the MSDN Forums. He frequently provides good, insightful information to those seeking advice.
In response to a recent question in the Database Engine Forum, Jonathan provided a good example how a process marked as sleeping can still hold locks on a SQL Server resource. I'd like to share a slightly modified version of it with you; you can read his original postings here.
Let's consider a very simple table in tempdb. Use SQL Server Management Studio to execute the following query:
USE tempdb ;
--create a table...
Next week at the Nashville SQL Server User Group, John Rives of Amniox is going to speak about using SQL Server with clustered virtualization. Unfortunately, I'm not going to be able to attend due to a prior commitment, however I have spoken with John about his presentation and it definitely sounds promising. If you're in the area, I'd encourage you to come on out. I certainly wish I could be there.
I don't have any SQL Servers in a virtualized environment. In fact, I have only worked with one company that does, and that is on a very underutilized server with...
As Geoff and Derek have already posted, SQL Server 2008 was released to manufacturing (RTM'd) yesterday.
Eager to get the bits? They're available from the MSDN download site.
Business Intelligence Developers Studio (aka BIDS) for Microsoft SQL Server 2005 Reporting Services makes creating engaging and dynamic reports nearly trivial in some respects. You can create drill-down and drill-through reports with just a few mouse clicks.
You can also create reports that provide interactive sorting. Users can click on column headings and have the report automatically sort by that column. This question regularly comes up as I'm leading training sessions and occasionally in the online forums. So, let's walk through a graphical example to demonstrate.
Here's an example of a contact phone list report created in BIDS. It lists...