Report Designers For SQL Server 2008 and Visual Studio 2008 - CTP5 and future

I've seen a lot of questions and misunderstanding as to how these products are bundled/SKU'd and what Microsoft's plans are for when SQL Server 2008 RTMs, and what designer will be supported by what IDE. So I'll try to explain, to the best of my knowledge, how these different permutation of SQL and VS versions work together.

Visual Studio 2005/2008 is an IDE shell that can include several dev environments, such as C#, C++, VB, ASP.NET/HTML, RDL/RptProj, etc.

When you install SQL Server 2005 or 2008 (CTP), and specify the client/dev tools option, the setup will check if you already have a SKU of Visual Studio 2005 installed, and if you do, it will only need to install the Report Designer and RptProject support into your existing VS shell.

However if you did not have VS 2005 installed, the setup for SSRS will install the VS shell, and the Report Designer and RptProject. This shell with only support for SSRS (and SSAS, SSIS, if you were installing those as well) is branded BI Development Studio (BIDS).

BIDS is not really a different application than Visual Studio 2005. You can notice, because when you click the BIDS shortcut on your Start/Programs menu, it will show the VS 2005 splash screen. You can also check the target path of the shortcut and notice that it's the same as the target path of your original VS 2005 shortcut (if you did have VS prior to installing SSRS).

When you're developing reports in BIDS/VS, you don't need to have a Report Server installed on that machine (this would be called the "client tools" only installation of SQL Server Reporting Services). You do need a Report Server if you plan to deploy your reports after development.

With regards to VS 2008, I believe that unfortunately the current November CTP5 does not integrate the BIDS/Report Designer into the IDE. So, in other words, you cannot yet use VS 2008 to develop SSRS 2008 Reports.

You will need to either have already installed the VS 2005 IDE, so SQL 2008 can install the report designer bits, or just let the SQL Server 2008 setup install BIDS (which is still the 2005 IDE).

**Note that Microsoft released an update for support of SQL Server 2008 in VS 2005 (you'd receive errors when trying to connect to a SQL 2008 DB from the Server Explorer). This is NOT related to the report designer.

Another thing to keep in mind is that for SSRS 2008, there's a new RDL schema (with the new Tablix report item, and other neat stuff), but as of this CTP5, you can only work with this schema from the Standalone Report Designer preview, that is installed with the CTP5. If you try to open an RDL created from this designer, using BIDS/VS 2005, you should receive an error, because you can only work with 2005-schema RDLs in BIDS/VS 2005.

According to the SSRS team, once SQL 2008 RTMs, they will have integrated the codebase of the new designer, found in the standalone designer preview, with VS 2008/BIDS. It may even be as soon as the next CTP6, but until then, if you want to create brand spanking new SSRS 2008 reports, then you must use the Standalone Designer Preview. If you deploy 2005 RDLs to a SSRS 2008 Report Server, they will be "upgraded" under-the-hood to 2008 in order for the reports to be usable in that version of SSRS.

Not unlike when SQL 2005 was released, and you were forced to convert your SSRS 2000 reports to the new 2005 schema in order to work with them in VS 2005/BIDS, or be forced to have VS.NET 2003 installed to continue to support the SQL 2000 schemas; so will you have to follow the same rules when going from 2005 to 2008. Unfortunately, that's a limitation in how the Report Designer in VS targets a specific schema version for RDLs, so it can't work with multiple schemas.

If you want this feature, go to the Microsoft Connect site, and enter your feature request for RDL version multi-targeting in VS 2008 :-)

I hope this clears up any misunderstanding.

So to sum it up:

Current November CTP5 - has 2 designers: BIDS and Standalone.

- standalone designer preview gives a little taste of what's to come, and is the only way to work directly with the new 2008 schema and features (e.g. Tablix, etc...).

- BIDS has not yet changed, and will work with RDLs in the SQL 2005 schema only, but once you try to deploy to a SSRS 2008 CTP Report Server, the server does an under-the-hood upgrade of the RDL to 2008 so it can store it in the DB.

Future CTPs and RTM Plans - 2 designers: BIDS/VS 2008 for developers, and a Standalone (with Office 2007 Ribbon UI) for Information Workers who don't "live" in Visual Studio.

- standalone designer and VS 2008/BIDS will provide much of the same functionality when developing reports, with the only difference being that BIDS will be hosted in a VS 2008 shell, while the standalone designer will be, well, standalone. And it will have a Ribbon UI (from Office 2007), so non-dev folks don't freak out, but rather feel familiar and comfortable while developing their SSRS reports (this will not be the same as the Report Builder!!!).

If you have SSRS 2005 reports, and you don't wish to convert them to 2008, DON'T OPEN THEM in Visual Studio 2008. For all your SSRS 2008 Reports, you will need the Standalone designer, and eventually either that or VS 2008. You will need to have both versions (2005 and 2008) of BIDS/Visual Studio installed side-by-side (which is supported by Microsoft), if you have to support both RDL versions.

Cheers!

Technorati Tags: Reporting Services,Sql Server,Visual Studio,SSRS,BI

SQL Server 2008 CTP 5 (November) Released

Microsoft has just released the latest CTP for their SQL Server 2008 suite of products. Grab your copy here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=3BF4C5CA-B905-4EBC-8901-1D4C1D1DA884&displaylang=en

This CTP promises to have a lot more features implemented, including a more complete version of the Reporting Services standalone designer tool. The SSRS team has overhauled the UI and included the Office 2007's ribbon interface, making this a very sexy UI for end-users who wish to create reports in a ad-hoc fashion.

I'll be testing this CTP, and will try to post some of the new features on this blog as soon as I can, so stay "tuned".

Thoughts on Code Analysis and Reviews

Code reviews can play an important part in the software development process. Static code analysis tools like FxCop and NDepend can do a great job of telling the developer what might be wrong with the code or any possible pressure points.

They can be customized to include most development standards and rules that a company has adopted, ranging from capitalization rules, all the way to thresholds for cyclomatic complexity (measure of how complex the code path is). Also, if your company uses a Continuous Integration (CI) approach to building and packaging software bits, then these tools can be integrated into the process to prevent code that doesn't meet the defined rules and standards from entering the build process, and possibly notifying the build manager and developer.

These tools can be more unforgiving, especially if you're not totally familiar with the errors it is "taught" to catch, whether in logic flow or in design patterns. That's why a "manual" or personal code-review can be extremely valuable when used in tandem with these tools.

In a lot of companies that perform regular code reviews, these sessions become a finger-pointing, frustrating experience for most people. Usually the managers will bring dev team into a room with a projector and some pizza, and will start pulling up random code to be reviewed by all on the big screen. Some people may find some of the criticism to their work offensive, and it can become very destructive to the team.

A better approach that I have seen is to teach the developers to send a review request of their code to a peer/co-worker directly via email. This usually leads to one or both of two things:

(A) the developers can learn something new or improved in a non-demeaning way, or

(B) they may become personal heroes, since by looking at someone's code during a review, peers may find a solution to another problem they may have been trying to address.

I would recommend implementing manual/personal code reviews as a team-building exercise, as well as to promote better coding practices among the dev team. And then introduce static code analysis with TFS/FxCop, NDepend, etc., as part of the source code check-in process, and/or during the build process.

Catching bad code or errors early on in the dev process costs a lot less than later on after the software has been built and/or possibly released.

Prevent SQL Injection by leaving a legacy

How to prevent SQL Injection attacks for generations to come:

Displaying the query SQL in your Reports

I was recently asked by a co-worker the following question:
"Is there a programmatic way to pull the dataset SQL and display it on a [Reporting Services 2005] report?"

With a little bit more background information I was able to help her out. Some important facts that needed to be pointed out:

• what flavor or DBMS are you using (hopefully SQL Server 2005)?


• Are you allowed to create and use stored procedures as the backend of your reports?

With those facts straight, we can approach this problem with the following solution (I am sure there are other, perhaps more elegant, ways to solve this, so add your comments).

UPDATED:
If Stored Procedures are not being used, then you can simply include a textbox in your report body with the following expression:
=Datasets!mydatasetname.CommandText

If you'd like, you can set the visibility of this textbox based on another textbox's toggle property, and set the text property for that 2nd textbox as "view report query" with a blue color so it looks like a link or action of some sort.

If you use stored procedures, then read on:

We must adopt a few "standards" regarding reporting and SQL, when approching the problem with this solution.

First, you will want to use Stored Procedures (sproc) for all your report datasets. This allows you to do some extra things with your sql code that you can't do in the report dataset designer.

Second, you will want to use dynamic sql, since you're wanting to output the sql statement as part of your query results (e.g., in a column of your dataset).

SQL Server contains a stored procedure called "sp_executesql" that allows you to execute sql statements much in the same way as using EXEC(@sql). Except, it adds a layer of protection against SQL Injection Attacks by introducing parameterized queries. So in fact what you'd have for your stored procedure is something like the sproc I am including at the bottom of this post.

Notice that we have 2 ways of getting the sql statement: as a rpt developer, you may choose to display the "DebugSql" column in the report, and as SQL developer, you can choose to execute your sproc with the @debug parameter set to 1, so it only prints the sql that would execute. This offers some flexibility for all.

Either way, in the report dataset designer, you can just specify query type of Text (not StoredProcedure, it gets kinda buggy when dealing with dynamic sql). Then for the query text you'll enter something like this:

EXEC dbo.GetMyReportData @param1, @param2

(no need to include the @debug parameter, since it has a default).

There's an excellent whitepaper titled "The Curse and Blessings of Dynamic SQL" by Erland Sommarskog (http://www.sommarskog.se/dynamic_sql.html), where he discusses all the possible ways of doing dynamic sql, including some best practices (like the sp_executesql sproc). I would highly recommend taking a look at this if you need more information on the topic or would like to understand more about the sp_executesql procedure (it's also documented in the SQL Server Books Online at http://msdn2.microsoft.com/en-us/library/ms188001.aspx).

Here is the sample stored procedure SQL, enjoy!

 IF EXISTS (SELECT * FROM dbo.sysobjects
           WHERE id = OBJECT_ID('dbo.GetMyReportData')
             AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    BEGIN
        DROP PROCEDURE dbo.GetMyReportData
    END
GO

CREATE PROCEDURE dbo.GetMyReportData
  @param1 NVARCHAR(25)
  , @param2 NVARCHAR(25)
  , @debug BIT = 0
AS
BEGIN

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT
  col1 AS Column1
  , col2 AS Column2
  , col3 AS Column3
  , @N_debugSql AS DebugSql
FROM
  myTable WITH(NOLOCK)
WHERE
  col1 = @N_param1
  AND col2 = @N_param2'

DECLARE @parameterList NVARCHAR(MAX)
SET @parameterList = N'
  @N_param1 NVARCHAR(25)
  , @N_param2 NVARCHAR(25)
  , @N_debugSql NVARCHAR(MAX)'

IF @debug = 0
  BEGIN
    EXECUTE sp_executesql @sql, @parameterList
      , @N_param1 = @param1
      , @N_param2 = @param2
      , @N_debugSql = @sql
  END
ELSE
  BEGIN
    PRINT (@sql)
  END

END

How do you organize emails and calendar?

After reading a blog by Scott Hanselman on the topic of email organization with Outlook, as well as one of my co-workers tips for Outlook 2007, I was curious to find out how other folks organize their life. This includes anything from emails, to tasks, calendaring, IMs, communication in general. Also, what tools and programs are you using to keep yourself organized? Do you use a plan like the GTD (Get Things Done) or Franklin Covey’s?

So to start off….here is my so called “system”:

Before
Well….it’s morphing a little since I’ve started using categories and search folders in Outlook 2007. But before, when I used Outlook 2003, I set up multiple subfolders under my Inbox (including one for distribution list emails, travel, and lodging, as well as Admin stuff). Then, using multiple rules, I would automatically have them moved to these folders as they arrived.

Inbox
I confess my Inbox is a dumping ground for old messages. I usually archive items that are about 4-6mos old…..but anything else that isn’t picked up by rules stays in my inbox. I use the search feature to find messages. However it looks like I am starting to go the “google” way to do emails…tagging them by using the categories feature, and combining indexed search with search folders.

Calendar
I try to color code appointments and things on my calendar by using the category selection, but to this day I don’t have a single view of my client calendar (since I have an exchange account at my client and use their Outlook) merged with my work calendar. That would be a nice thing to get working but I am not sure I can do that yet, like the blog post mentioned earlier. I am, however interested in syncing my personal Yahoo calendar, and my wife’s Google calendar up so I can see them using Outlook, and so I can overlay them. If you’ve done that, please share your story.

Mobile
I have a Windows Mobile phone, so I can get my emails and calendar synced, and I have only my Inbox and a couple of the distribution list folders synced up to my phone (no need to see other messages on the mobile).

Instant Messenger
Finally, I have several IM accounts (Yahoo, MSN, GTalk, and I think I even had an ICQ acct at some point…though haven’t used that in years), but honestly, I generally stay on MSN/Windows Live Messenger. When I am the client, since they block IMs, I use Meebo (http://www.meebo.com/), which uses the Internet protocol (HTTP) to transport your messages, so it doesn’t get blocked by firewalls. Also, they allow you to connect to multiple IM services using one ID (VERY NICE)…and it runs from the web browser (which is good and bad). I heard that the Google Talk client allowed you to connect to other IM services besides Google’s, but I’ve yet to try it.

I’d be interested in finding out what other ways are being used to stay organized with your communications, so post your comments, dear readers!

SQL Server 2008 CTP4 Reporting Services

so...the folks at Microsoft recently released the next CTP for SQL Server 2008 (formerly "Katmai"). With this preview, we finally get to see what's in store for the Reporting Services (SSRS2008) components in the SQL Server Suite.

SSRS 2008 comes with some significant changes in the report design experience, as well as the deployment of the server components. If you're familiar with the current and previous versions of SSRS, you know that it uses IIS as the web server platform for hosting its web service endpoints that allow access to the Report Catalog. In the 2008 version, this is replaced with a clever hosting implementation of the http.sys device driver, which listens for particular HTTP requests and forwards them to the application domains for the ReportServer and Report Manager URL, hosted by the SSRS Windows Service. No longer is there a dependency on IIS for your Report Server, reducing the server footprint, and allowing for more resources to be used by the Report Server itself.

Another enhancement is in the area of Report Rendering. While this particular "side" of SSRS has been, by far, the hardest to extend due to its complexity, if you have written any previous rendering extensions (kudos to you first of all...), they will have to be completely re-written. The Rendering Engine has been completely overhauled (worthy of its own post, soon to come, dear readers), and with this release we also get 2 new additions to the current list of supported renderers: OfficeWriter for Excel and OfficeWriter for Word.

Finally, another "side" worthy of mention is the new stand-alone Report Designer which will target non-developer audiences who do not have (or choose not to have) to the Visual Studio IDE. The stand-alone report designer includes support for the new Tablix report item, which combines the functionality of the Table, List, and Matrix into a single, easy-to-use control (NOTE: the Tablix control is not available in the SSRS 2008 designer for Visual Studio, yet).

Go ahead and download the latest CTP from MSDN (http://msdn2.microsoft.com/en-us/sql/bb498264.aspx), and give it a test run. I'll try to post about each of these (and other) new features in dedicated future posts.

FREE Extended Battery for Samsung BlackJack

Do you have a Samsung BlackJack? Do you hate the battery life for their standard batteries? Well....worry no more! Cingular is responding to complaints for customers on the Blackjack battery life, by offering an extended battery (and the back-plate to fit it) FOR FREE. That's right. ABSOLUTELY FREE.

Catches:
This offer expires on 4/15/2007 and is not being very well advertised by Cingular (thanks RichDizz and a MSFT employee for tipping off).

Your phone must have an IMEI 352794010030189 are 352794012269959 to be eligible for this offer. You phone's IMEI can be found either on the packaging or under the battery on the phone and contains 15 digits.

Click the following link to redeem your offer:
https://www.web-rebates.com/cingular/blackjack/Step1.asp