Having trouble getting through to a local SQL Server using the SQLOLEDB provider? I sure did. I’m trying to get a base VPC setup with some Microsoft server products which need access to a local SQL Server. The obvious answer is to use (local) or . to get at the local SQL Server and not have to worry about changes in the machine name. This, however, turns out not to work with the default settings of SQL Server 2005.

You basically have to enable TCP/IP using SQL Server Configuration Manager for it to work. I little gotcha for ya

I’m currently working on my zero day install list of software I cannot live without but I simply cannot wait to point out this nifty piece of software a colleague just showed me. The premise is simple: Intellisense for SQL Server. Red Gate delivers on this as they have done so many times before on other products for SQL Server, best of all, it’s free. Can you believe that?

Now on to the important stuff: SQL Prompt which is the name of this delicious tool. It’s a whole bag of tricks for your SQL statements:

• Intellisense on tables, columns, and even joins.
• Auto capitalization of keywords like SELECT, INSERT, and so on.
• Keyword formatting, code snippet integration other extended features.
• Works in SQL Management Studio, Query Analyser, Enterprise Manager, Visual Studio .NET 2003, Visual Studio 2005, UltraEdit, and surprisingly EditPlus 2 which is my text editor of choice.

• It’s free for now. You can grab a copy for the price of nothing until September 1st.

I give this tool a solid thumbs up.

Red Gate SQL Prompt.

A customer needed Reporting Services 2005 but was running SQL Server 2000 and didn’t want to invest in a 2005 license as 2000 fits their needs just fine. Naturally I was exited to learn that Reporting Services would be available for SQL Express. That however was several months ago.

Now it seems the support has finally been released allowing us to use SQL Server 2000 as data backend and SQL Express as reporting frontend.

SQL Server Express with Advanced Services

In an interesting turn of events I learned that SQL Server 2000 doesn’t actually run transactions atomically when XACT_ABORT is set to OFF and an error occurs. A colleague of mine sent along a very interesting article describing basic error handling in stored procedures including the description of the XACT_ABORT option. Here’s quote from Books Online:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

And here’s the article I mentioned above. A very good read; should be mandatory for everyone coming within 10 meters of SQL Server 2000.

Error handling in stored procedures is a very tedious task, because T-SQL offers no exception mechanism, or any On Error Goto. All you have is the global variable @@error which you need to check after each statement for a non-zero value to be perfectly safe. If you call a stored procedure, you also need to check the return value from the procedure.

[Implementing Error Handling with Stored Procedures]

It’s with some satisfaction that I’m able to fill this one under the “solved” section. I’m currently building a Reporting Services solution which generates price lists which includes images of products along with some other information. My dataset includes a column with a path for the image of a particular product, but I can’t be sure that the image actually exists in the file system which means that a call to System.IO.File.Exists is needed. Thus my trouble begins: The methods always returns false even though the file is accessible…. Hours of head scratching ensues until I find that Reporting Services includes its own policy file which doesn’t allow access to the file system.

A quick change to the file Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\rssrvpolicy.config makes all the difference in the world. Initially I gave RS FullTrust just to narrow the potential sources of error to a minimum, but FileIOPermission will suffice in my case. Specifically I changed the following node from

                    <CodeGroup
                            class="FirstMatchCodeGroup"
                            version="1"
                            PermissionSetName="Nothing">

to

                    <CodeGroup
                            class="FirstMatchCodeGroup"
                            version="1"
                            PermissionSetName="FullTrust">

Also do remember that we are deailing with XML thus case very muhc matters. "Fulltrust" does not yield the same results as "FullTrust".

I needed a way to sort dynamically in a Reporting Services report today. Google was kind enough to provide me with a sample which did exactly that on GotDotNet. Head on over to the Dynamic Sorting Sample.

Also I found a pretty interesting set of links pertaining to Reporting Services during my search. Go have a look at those at Reporting Services - Links on Ohad's WebLog.

Here’s something interesting for those of you interested in Reporting Services. Looks like Microsoft release some reports which grab information from the IIS logs to make reports of the activity on your web server.

The Microsoft SQL Server Report Pack for Internet Information Services (IIS) is a set of 12 Microsoft SQL Server 2000 Reporting Services reports that works with a sample database of information extracted from Microsoft Internet Information Services (IIS) log files. This database can be populated with your own data using the Log Parser included with the IIS 6.0 Resource Kit. You can use also the sample reports as templates for designing new reports.

Microsoft SQL Server Report Pack for Internet Information Services (IIS)