Recently I found myself having to debug a large stored procedure that calls all sorts of sub-procedures. Also, the data set was rather large and it only failed sometimes. SQL Management Studio in this scenario isn't very helpful for several reasons:

• The number of PRINT messages that it'll show is limited, so if the error is out of range... tough luck.
• Even with PRINT messages it can be hard to pin down which statement is actually in error.
• Line numbers are never accurate.
• It seems View Dendencies sometimes misses out on dependencies.
• View Dependencies doesn't show the number of times a proc is called from another proc.

SQL Profiler, which also comes with SQL Server is a much better tool when it comes to pin-pointing the erronous statement(s), after which you can modify your code to show an error message that prints the values causing the hickup. When you use SQL Profiler, be sure to select more than just the default events. Click Show All Events and select all Error events, as well as the Stored Procedure events that show Starting and Completed of SQL statements with stored procedures. Then, once the root procedure is done, do a Find through the trace for "Error", and you can see exactly which statement is causing the error.

Every once in a while (when I use a new dev environment) I hit this error:

Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

Each time I forget how to fix this, so by posting here I know I'll never forget. Check Pinal Dave's blog post on this error (hint: it's in the Option menu). Thanks Pinal!

I keep forgetting how to force the transaction log in SQL Server to shrink, so I'm posting here primarily so I know where to find the how to :). Often the log does not shrink when you try to shrink from SQL Managent Studio. The solution, before you shrink the database do:

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

Thanks to Pinal Dave for this one (see his post SQL SERVER - Shrinking Truncate Log File - Log Full)

We have an ASP.NET application that we normally run under Forms Authentication using the ASP.NET Membership API. For a particular client we changed this to using Windows Authentication instead. On the production environment, we were running into the following exception:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

We checked all the connection strings the app uses... all good. Finally, we figured out what was wrong. We had removed the Membership section from web.config so it was going back to the default in machine.config. That setting uses the LocalSqlServer connection string, which we don't use. However, in the default machine.config, this points to the App_Data folder using SQL Server Express. In most environments, this wouldn't be an issue immediately, because SQL Server Express would just create the  aspnet database and use that. However, in a hardened environment SQL Server Express is either not there (our case) or has no rights to create the App_Data folder and/or place create a new database. ASP.NET doesn't know this... it just can't access the SQL Server instance it is looking for, hence the above exception.

De dag na DevDays organiseert DevConnections twee workshops:

• ASP.NET AJAX Extensions Inside Out door Dino Esposito
• Optimising and best practices for all SQL Server 2005 features door Bob Beauchemin

Als je nog wat wilt leren na DevDays, dan is dit een hele goede kans. Beide sprekers kennen het onderwerp van haver tot gort, dus het is zeker de moeite waard. Nog niet helemaal overtuigd? Bob en Dino spreken allebei op DevDays, dus als je daar bent kun je eerst even een kijkje nemen. Je kunt je op de DevDays eventueel ook inschrijven voor een van de workshops.

SQL Server 2005 is al weer toe aan z'n 2e service pack. Deze is te downloaden van http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx. Een nieuwe versie van de SQL Server 2005 Books Online kun je vinden op http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx.