Tonight is the maiden voyage of the Idaho SQL Server User Group and what a way to send it off - with Kalen talking about SQL Server 2005.

Counting heads in the room, it looks like we have about 33 people, including Kalen and Cindy Gross (Microsoft). That's a nice sized group for an evening gathering of a user group in Boise.

After enlightening us with her background, and deep history working with SQL Server, Kalen jumped right into the nuts and bolts of SQL Server 2005: The Costs of Concurrency.

The newly formed Boise SQL Server User Group kicks off its first meeting with Kalen Delaney on June 25, 2008.

I'm happy to see a Microsoft SQL Server user group in Boise. It will fit nicely with the other development and SharePoint groups in town.

For more information, contact Cindy Gross of Microsoft.

I've been so involved with the VSTS RTM yesterday, that I almost didn't notice this one.

Click here to download the latest SQL Server 2008 Community Technology Preview (CTP) and try out the latest features of SQL Server 2008.

Way to go Microsoft, and SQL Server 2005!

For the first time in the report’s history, Microsoft is positioned in the Leader quadrant in Gartner’s Magic Quadrant for Data Warehouse DBMS. The analysts say that SQL Server 2005 is expected to grow in the data warehouse space and Microsoft’s vision for SQL Server 2008 makes clear the company’s intent to become a major presence in the data warehouse market.

 

Read more about this great announcement here.

Call it a code generator, software factory, or just a clever script. If you can write code that writes code - you win, even if just a small victory for humans in this contest we call software development.

For example, I've been working on an ASP.NET application which contains many data entry screens. You know the kind: very simple, table-format with a label and a textbox of a certain width, that may or may not require some validation. In other words, a whole lot of markup like this:

<tr>

  <td class="EditLabel">Number</td>

  <td class="Edit">

    <asp:TextBox ID="txtNumber" runat="Server" Width="200px" MaxLength="20"></asp:TextBox>

  </td>

</tr>

 

Now, if you have to type the above more than once or twice, you will go insane (been there, gone there). More importantly, you will probably introduce a bug or two. So, I opened up SQL Server 2005 Management Studio and wrote the following T-SQL code:

USE SomeDB

GO

 

DECLARE @Table  varchar(128)

DECLARE @Column varchar(128)

DECLARE @Width  varchar(10)

DECLARE @Length int

DECLARE @Type   int

 

SET @Table = 'Employer' -- Pass this as a parameter

 

DECLARE ColumnCursor CURSOR FOR

   SELECT C.Name, C.Max_Length, C.User_Type_ID FROM Sys.Columns C

   INNER JOIN Sys.Tables T ON C.Object_ID = T.Object_ID

   WHERE T.Name = @Table

   ORDER BY Column_ID

 

OPEN ColumnCursor

 

FETCH NEXT FROM ColumnCursor INTO @Column, @Length, @Type

WHILE @@FETCH_STATUS = 0

BEGIN

  IF @Type <> 36 -- No GUIDs

  BEGIN

    IF @Length < 0 SET @Length = 100

    IF @Length < 10

      SET @Width = '50px'

    ELSE IF @Length < 20

      SET @Width = '100px'

    ELSE IF @Length < 50

      SET @Width = '200px'

    ELSE IF @Length < 100

      SET @Width = '300px'

    ELSE

      SET @Width = '400px'

 

    PRINT '<tr>'

    PRINT '  <td class="EditLabel">' + @Column + '</td>'

    PRINT '  <td class="Edit">'

    PRINT '    <asp:TextBox ID="txt' + @Column + '" runat="Server" Width="' + @Width + '" MaxLength="' + CONVERT(varchar(10),@Length) + '"></asp:TextBox>'

    PRINT '  </td>'

    PRINT '</tr>'

  END

  FETCH NEXT FROM ColumnCursor INTO @Column, @Length, @Type

END

 

CLOSE ColumnCursor

DEALLOCATE ColumnCursor

 

You get the picture. Feel free to customize this code to introduce additional formatting, a slick UI, or other business rules to the mix.

Microsoft has published a detailed KB article about the problems fixed by SR1. The article includes a link to download the update.

Aside from many fixes, there are three major improvements found in this release:

• Cross-database references - support is improved so that you can reference objects within different databases by using database project references or by referencing a database metafile (.dbmeta). This support reduces or eliminates the cross-database reference warnings within a database project
• Improved file support within SQL Server file groups - you can define files within file groups as database project properties instead of having to create files and file groups within the pre-deployment storage script.
• Variables - a Variables page is added to the database properties. This new page enables you to define setvar variables for use in the deployment scripts.

I saw a demo of these at Tech-Ed, and thought I would share with my friends.

Essentially, the Database Operations team at Microsoft uses a suite of jobs on all their servers for performing backups, automated database maintenance, improve performance , and improving disaster recovery. I'm sure every SQL Server expert/consultant out there has his or her own bag of tricks, but Microsoft is finally sharing theirs.

 

These SQL maintenance jobs provide automated tasks such as database or transaction logs backup, performing database optimization like Index defrag or update statistics, cleaning MSDB backup history and recycling the SQL error log. Each of these jobs runs stored procedures that are installed when the jobs are created.

 

The following are the jobs get installed with this package.

• SQLBackupAll
• SQLBackupDiffAll
• SQLBackupTranAll
• SQLBackupChecker
• SQLDBCCAll
• SQLIndexDefragAll
• SQLUpdateStatistics
• SQLCleanupMsdbBackupHistory
• SQLCycleErrorLog

Download the automation jobs here, and then refer to the Word document inside.

Welcome to Camp! Portland Code Camp 3.0 (as was 2.0) is being hosted at the gorgeous WSU Vancouver campus.

There are quite a few attendees here. I counted around 90 at this morning's welcome session.

Click the above to zoom-in

As for my SQL Server 2005 Worst Practices talk, if you'd like to download the slides, code, or sample project, please click here.

Trainers and presenters have been wanting a sample database that is less complex than AdventureWorks, but more interesting than Northwind. Thanks to my friends at SolidQ for letting me know about AdventureWorksLT (Light) ...

(Click to see a larger view)

You can download AdventureWorksLT from CodePlex or Microsoft.

SQL Injection attacks are a well know exploit of insecure database systems. If you don't know about SQL Injection, you can change that by visiting Wikipedia.

If you feel like injecting some SQL into your site, you should visit the SQL Injection Cheat Sheet page for all of the approaches. As the author mentions, only MySQL, Microsoft SQL Server, (some) ORACLE, and (some) PostgreSQL are supported and that most of the samples are not correct for every situation.

Happy hacking!

We all have our favorite database structure generator or schema reporting tool. Maybe we have built one of them in the past. I always tell my clients that it's a great "first application" to build when learning SQL Management Objects (SMO).

I just found out about SqlSpec.

SqlSpec generates documentation in two formats: HTML or CHM. I particularly like the CHM format, especially when you want a single, compact file containing all your documentation about one or more databases. The CHM is indexed so it is easily searchable for any keyword. If you visit their Samples page, you can see what these CHM files look like.

Naturally, SqlSpec supports SQL Server and Analysis Services, but also other legacy databases like Oracle, MySQL, and Access. It's $149 and they have a free trial version.

Pretty slick.

One of the coolest controls that Visual Studio 2005 includes is the report design and view functionality of the ReportViewer controls. What used to be a server-only function, .RDL (now .RDLC) files can be rendered client-side by Web or Windows applications with this control. Reports can contain tabular, aggregated, and multidimensional data.

Thanks to Peter Myers for pointing me to this site to answer all of my (and your) questions and help unlock the hidden potential of the ReportViewer control.

Over the weekend Microsoft released some cool add-ins for Excel 2007 and Visio 2007:

• Table Analysis Tools for Excel - this add-in provides you with easy-to-use tasks that leverage SQL Server 2005 Data Mining under the covers to perform powerful analytics on your spreadsheet data.
• Data Mining Client for Excel - This add-in allows you to go through the full data mining model development lifecycle within Excel 2007 using either your spreadsheet data or external data accessible through your SQL Server 2005 Analysis Services instance.
• Data Mining Templates for Visio - This add-in allows you to render and share your mining models as annotatable Visio 2007 drawings.

Download the new add-ins here.

There are way too many Crystal Reports out there in the world and I would love to see more of them converted to Reporting Services. Therein lies the problem. I had heard that Crystal (now BusinessObjects) had blocked companies, like Hitachi, from building converters, because they would be reverse engineering (or using the APIs to reverse engineer) the .rpt files, and that would violate the license agreement.

Microsoft only provides migration guidance, but I see that one company, KTL Solutions, has a conversion product available, and another company, Jeff-Net is offering a $25/report conversion service at rpttordl.com.

The much anticipated and eagerly awaited Analysis Services 2005 Performance Guide has been released and is available on the Microsoft download site.

Richard Waymire has publishes his long-anticipated whitepaper on permissions, security objects, and other security concerns centered around using the Database Professionals edition.

Thanks for attending my talk last night and letting me share my SQL Server 2005 Worst Practices. I hope I didn't embarass anyone too badly by highlighting your practice in my talk!

Here are the slides, script, and sample project from last night's talk.

Also, a small correction: MyGeneration's site is www.mygenerationsoftware.com

If you are in the Boise area, be sure to attend Thursday's meeting of the Boise .NET User Group. I'll be delivering a talk on "SQL Server 2005 Worst Practices". If time permits, I'll also be diving into any other SQL Server/Visual Studio topics that are of interest, so bring your questions!

Please note that we will be meeting at the Microsoft building downtown (old ProClarity building).

Last week, while teaching a SQL Server 2005 Reporting Services class, I built this simple C# Windows forms application to help "push" an RDL report to multiple servers. It is a simple application that calls the CreateFolder and CreateReport Web methods on the ReportService2005 Web service. It's easy enough to customize for your purposes.

According to my students, this is a very common problem they face, as they have nine identical servers, with identical folder structures, and reports. They are generating and running scripts today, but wanted something more automated.

Feel free to download the source code.

I did find the Reporting Services Scripter utility, which looked promising.

I just noticed that there is a December update to the SQL 2005 samples. The December 2006 update is identical to the July 2006 update, except that support for Windows Vista has been added. If you already downloaded the July update and do not run on Windows Vista, then you do not need to download this update.

I'm getting more and more questions regarding running Visual Studio 2005 on Vista. Here's the summary from Microsoft:

"Visual Studio 2005 is supported on Windows Vista. We recommend that developers install Visual Studio 2005 Service Pack 1 and the Visual Studio 2005 SP1 Update for Windows Vista as they become available (SP1 end of this year, VS Update for Vista Q1 next year). We also recommend that Visual Studio 2005 be run with elevated administrator privileges. Visual Studio 2003 & 2002 are not supported on Windows Vista. The underlying frameworks (.NET Fx 3.0, 2.0 & 1.1) are supported and applications using them will run on Windows Vista."

(Update 14 Dec) Here are some more resources ...

• Visual Studio 2005 on Windows Vista Issue List
• Heath Stuart's blog

You can read more on Soma's blog.

As for SQL Server 2005, I've heard that you won't have any problems installing the Developer edition, but if you want to install Standard or Enterprise versions, you'll need to install the CTP of Beta 2. If you are wanting to use Reporting Services, remember that RS uses IIS and IIS 7 + Vista are different. Here is a blog post (in Spanish but the screens are pretty self-explanatory), instructing on how to properly configure IIS on Vista for RS. Beyond that, there is guidance in an older June article.

Thank you for the good time last night at the Iowa .NET User Group meeting. I enjoyed spending the evening with these easy-going, but tech-savvy folks.

We had a good turnout, great questions, and a fun time afterward at the 801 steak and chop house!

If you are looking for my SQL Server 2005 script of love. You can find it here.

I'm honored to be speaking at the Iowa .NET User Group this week. My topic will be on SQL Server 2005 for Developers. If you are in the area, please stop by. Visit their site for more information.

You might have heard, and it was such a tragedy that the group's founder, Eric Jacobs, and another member, Josh Trainor, were killed in a plane crash last Month. My prayers go out to the family and friends of Eric and Josh.

I'm back home now, but had a total blast at DevConnections in Las Vegas this week. I saw many friends and colleagues who I hadn't seen for awhile. I was surprised that there were ~4700 attendees at the show, with quite a large exhibiter hall too. It's really become a mini Tech-Ed.

From left to right: Richard Hundhausen, Andrew Kelly, Dino Esposito, Peter DeBetta,
Brian Moran, Stacia Misner, Rushabh Mehta, Jeff Jones, Douglas McDowell.

Visit my personal photo album for more photos (Conferences > Dev Connections 2006 Las Vegas).

Although I don't have their newest (7th edition) book yet, I plan on picking it up. From what I've read at the site, the Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) seems to be just what the industry needs for the critical intersection of technologies.

Nice work Bill and Peter!

Although the venue was changed at the last minute (to better digs at DigiPen) and my colleague Steven Borg bailed out to have a kid (that still hasn't come yet), Seattle Code Camp 2.0 was awesome!

If you attended my talks on SQL Server 2005, you can find my demo bits here.

I happened upon this article, which summarizes the datetime datatype and how it works in SQL Server, including common pitfalls and general recommendations.

The site is run by Tibor Karaszi a fellow Solid Quality Learning mentor.

That's right. Seattle Code Camp 2.0 is upon us, with a late-breaking venue change ...

Camp will be held at Nintendo's DigiPen Univeristy, which is very near the Microsoft Campus.

Consultants from our company will be doing talks on SQL Server, Team System, and Ruby. See you there, this Saturday and Sunday (October 28 and 29).

I enjoyed traveling to Spokane (I have family there) and presenting to the Spokane .NET User Group (SNUG?) on various SQL Server 2005 T-SQL, engine, and data type enhancement topics. This was my second INETA event, and they just keep getting better.

One of the attendees works for a company called SprayCool, which has a technology for keeping mission-critical servers and data centers cool, and running smoothly. This has to be the "coolest" thing I've heard of in awhile! SprayCool is the process of using liquid evaporation, or phase change, to cool electronics. A fine mist of coolant is sprayed onto electronic hot spots and immediately evaporates. The vapor is then captured and the heat is rejected as it circulates through a heat exchanger. This results in an extremely efficient method of cooling.

Reporting in Team System is handled by SQL Server 2005 Reporting Services. As such, team members get to enjoy (and are restricted-by) the built-in report rendering extensions (CSV, Excel, HTML, Image, MHTML, PDF, and XML). What's missing from this list is the Microsoft Word DOC format and a richer Microsoft Excel XLS format.

This is where a product called OfficeWriter comes in. It enables you to use Excel or Word to create templates utilizing data markers and merge fields for databinding sections of the document to the various Team Foundation Server data items. After a quick configuration of Reporting Services, your reports can generate documents/spreadsheets based on these templates without the need for Microsoft Office on the server. There's also an integration with Reporting Services that will let you create these documents without any coding at all. Users will design their RDL reports using Excel or Word, without the need for Visual Studio or SQL Report Builder.

Check out their latest version of OfficeWrite (v3.6) at http://officewriter.softartisans.com

Peter DeBetta and I are going to have fun putting together a presentation on "worst practices", and by doing so we will be illustrating the "best practices".

Look for us to be onstage together at SQL Connections this Fall at the Mandalay Bay.

So, what are some of your "worst practices" that you'd like to share?

Thanks to my colleague Kalen Delaney for letting us know that the BOL and Samples have been updated. Download your updates today!

A friend of mine, John Paul Cook, has tricked out SQL Server 2005 database diagrams to show more than just relationships between tables.

Read the DevX article and download the code.

Pascal Belaud, a .NET evangelist at Microsoft France, has started a beta plan for his popular OlyMars product. Version 1.5 will support .NET 2.0 and SQL Server 2005.

So what exactly is OlyMars?

It is a SQL Server Centric .Net Code Generator and is a both flexible and powerful generator based on database modeling. It allows instant generation of both T-SQL and .Net code providing a complete library of stored procedures, .NET classes and ready-to-use custom web and form controls related to the database modeling, including associated documentation. These elements are illustrated in two default Windows and Web applications and can be used in custom applications. SQL Server centric .Net code generator is fully extensible to use custom templates and extended properties and consequently can be adjusted to generate any custom code respecting a homogeneous implementation scheme.

Microsoft recently announced Microsoft Office PerformancePoint Server 2007. I believe this product was a result of the acquisition of Proclarity (Boise, ID) and a combination SQL Server 2005 BI, and Microsoft Excel.

Read the announcement from InfoWorld (Yahoo).

A bit late, but nevertheless, I wanted to let you know that there's an update you should all be aware of ... the samples and sample databases!

• SqlServerSamples.msi -- 19,092 KB
• AdventureWorksDB.msi -- 27,992 KB
• AdventureWorksDBCI.msi -- 29,116 KB
• AdventureWorksBI.msi -- 7,592 KB
• AdventureWorksBICI.msi -- 8,814 KB

If you are using SQL Server 2005, then you will want to download this service pack. There are 32 and 64-bit versions available on the page.

Here is a list of bugs fixed and features added to SP1. Here is a link to SQL Server 2005 Express SP1.

If your domain includes working with Microsoft connected systems (BizTalk, Windows Workflow Foundation, Windows Communication Foundation, SQL Server, Visual Studio, etc.) then you should be aware of this user group.

The UG covers the greater Seattle area, but you can stay engaged through their Website.

Check out our article on Data Mining in the October issue of Business IQ Magazine. You'll need to download the October issue and read it for yourself! It's about 5mb.

I was asked this again last week, so it's time to blog!

When installing SQL Server 2005, you need to click the Advanced button to get to the screen below so that you can include the sample databases and project code. If you just check the boxes for the various database services, these won't get installed by default.

My apologies for being late on uploading these, but I had to spend an incredible weekend touring Jerusalem and Bethlahem. Then, I jumped on a flight to Dubai, and am currently in a hotel in downtown, preparing for the SQL Server Magazine Roadshow tomorrow.

My sample code from my Keynote, Asynchronicity, Security, SMO, and Mobile presentations, as well as the afternoon of the SQLCLR seminar is in the process of being uploaded. I should have a link in a day or two. Please email me if you need something before then!

Thanks again for attending my sessions. I really enjoyed visiting your country, meeting you, and helping launch SQL Server 2005!

Update (23 Nov) - I haven't found the official site for the slides and demo code, so I've uploaded them here.

Thanks to one of my favorite attendees, Oren Ellenbogen, who is a CodeSmith guru, for providing a CodeSmith solution to my SQL Server concordance generator that I demonstrated at SQL Week in Israel.

A "concordance generator" is something I came up with, which scans through all of the columns, in all of the tables, in a database, and looks for anomalies, such as two columns with the same name, but with different data types, sizes, nullability, etc. It's good for people who like their database structures just so -- like Monk!

Thank you everyone for attending my talks. I'm going to start answering the questions I've been asked here. Feel free to email me if you have additional questions, or if my answer(s) aren't clear.

Q. Can I convert .RDLC (ReportViewer control) files to .RDL (Reporting Services) files?
A. You bet. Check out this page for more information.

Q. How can I clear out all the connection pools from my middle-tier
A. ADO.NET 2.0 has the new SqlConnection.ClearAllPools() static method.

You can watch Steve Ballmer's keynote here. I've also uploaded a few photos from the event. My personal favorite was meeting Paul senior from Orange County Choppers (American Chopper).

Tip: To view my photos, select Conferences and Launch 2005 - San Francisco.