SQL Server 2008 has been released last week and is now available via MSDN for download.

Below you can see the versions available for download. Enjoy!

Greg Olsen
Yellow Duck Guy

This is a handy little piece of code I wrote, which can help if you ever need to refresh your database views within SQL Server. I have tested this on SQL Server 2005 recently. I have also had a SQL Job run this stored procedure to update my views more frequently.  I had used this to keep views up-to-date for database integration projects.

   1: CREATE Procedure dbo.RefreshAllViews

   2: AS

   3:  

   4: DECLARE @ViewName nvarchar(max)

   5: DECLARE @SQL nvarchar(max) 

   6:  

   7: DECLARE extensionViews CURSOR FOR

   8:     -- Get all views within the database

   9:     SELECT [name] As ViewName

  10:     FROM sys.views

  11:  

  12: OPEN extensionViews

  13:  

  14: FETCH NEXT FROM extensionViews

  15: INTO @ViewName

  16:  

  17: -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

  18: WHILE @@FETCH_STATUS = 0

  19: BEGIN

  20:  

  21: -- Build the dynamic SQL for updating the view on the fetched row

  22: SET @SQL =

  23: 'IF EXISTS (SELECT * FROM sysobjects WHERE type = ''V'' AND name = ''' + @ViewName +''')

  24:     BEGIN

  25:         exec sp_refreshview N''dbo.'+ @ViewName + '''END'

  26:  

  27: exec(@SQL)

  28:      

  29:    -- This is executed as long as the previous fetch succeeds.

  30:    FETCH NEXT FROM extensionViews

  31:    INTO @ViewName

  32: END

  33:  

  34: CLOSE extensionViews

  35: DEALLOCATE extensionViews 

  36:  

  37: GO

Hope you find a use for this!

Greg Olsen
Yellow Duck Guy

Sometimes I use this simple but effective date function in my reports (NZ reports) by using the 'Code' functionality given by SQL Reporting Services.  Therefore I thought I would paste this on my site for quick reference. Feel free to use. 

Public Function FormatDate(ByVal d As Date)
 Return d.ToString("dd/MM/yyyy")
End Function

To find where to paste your code then simply goto 'Report Properties' from the Report Menu (in Report Designer) and select the 'Code' tab. Then paste your code inside the window made available and click OK.

Then to use in your reports, simply call it like the following ...

=code.FormatDate(Fields!DATE.Value)

The function above also illustrates how you can write your own functions and call them within your report.

If you are ever wondering where your code will be stored then have a look inside your RDL (report exension .rdl) report file and search for the XML tags of <CODE></CODE>. You will notice it inside there.

Simple aye! Happy coding.

Yellow Duck Guy
Greg Olsen

There are 2 options for creating Microsoft SQL Server Reporting Services Reports:

• Report Builder
• Report Designer

Report Builder

Microsoft hasn't forgotten the end user i.e. your Business Analyst. SQL Server is shipping with an end-user reporting tool right in the box.  Report Builder is a ClickOnce Windows Forms application that users download from the report server to their local computer.

That means that end users install it from the Web browser, but once installed it's not a browser-based application. To get started with Report Builder, browse to your Reporting Services home page.  This will have a URL something like http://YellowDuckGuyServer/Reports (or http://localhost/Reports if you're running the browser (within IIS) on the same box with SQL Server 2005 itself - sometimes common where starting out!).  Next you will need to click the Report Builder link in the home page menu bar to install and launch Report Builder.  The Report Builder will load with the New Report dialog box, showing all the available report models. I suggest you try this out if you want to get started with the Report Builder.  Your DBA will need to do a bit of setup before your end users can create their own reports.  Your end users also will need to know about their data also in order to design useful reports.

Users create reports within the Report Builder by simply dragging fields from the predefined report models onto a pre-designed report layout template. Users can format, group and sort, and filter their data. In addition, they can edit or define formulas. With Report Builder, users don't need to understand the underlying structure of the data source and they don't need to understand any complex computing languages. They simply need to be familiar with the data in their data sources.

Report Designer

Report Designer is the tool I use the most when designing reports and is focused towards your developers. You can use Report Designer within Visual Studio after installing Reporting Services on your machine or simply the Report Designer.  I'm currently using SQL Reporting Services 2005 with Visual Studio 2005.
Expression Builder within Report Designer used to create report expressions is written in VB or something close to it so your VB developers out there will love it!

So your developers (that includes me!) will use Report Designer to create more complex reports.  You have complete control over the layout, and you can add advanced features such as expressions, custom assemblies that run from the report, and report interaction for drilling down or linking to related data. You can also create basic reports that consist of simple tables, matrix, image, or lists.

In Report Designer, you can create a report in three ways.

1. You can create a blank report and build your report from scratch - I recommend this option.
2. You can use Report Wizard, which automatically creates a table or matrix report based on information you provide. This option I would not recommend if you really want to learn best practices with report building.
3. You can also import an existing report from Microsoft Access.
   

Reports are published to a report server as Report Definition Language (RDL). files as I slightly mentioned in my previous SSRS Yellow Duck Guy posting. Because a report definition is an XML document, you can create and edit reports using anything you like that can attack an XML file i.e. XML Notepad.

Under the hood, Report Designer uses the Reporting Services Simple Object Access Protocol (SOAP) API to publish reports to a report server.   You also have the option to upload reports using Report Manager on the report server (this could also be on your local machine).

Well now you should know the options available for building reports with SQL Server Reporting Services (SSRS).

Yellow Duck Guy
Greg Olsen

Microsoft SQL Server 2005 Reporting Services (SSRS) will provide you very rich enterprise reporting capabilities and Web-enabled reporting functionality.

You create reports which can report off a number of different data sources (I prefer SQL Server) normally via Reporting Services "DataSet" Report Designer object. SQL Reporting Services add-in Report Designer for Visual Studio allows to you automatically deploy your reports within Visual Studio to a centalised report server. The central report server will also manage security and subscriptions (think of these as scheduled tasks) for your report(s).  The report server also allows you to upload the report (file extension of .rdl) file via the built in Report Manager.

SSRS reports can be published in different formats i.e. PDF, Excel, CSV, HTM just to name a few.

Below is an overview of where SSRS sits within the breakdown of SQL Server product from Microsoft.

More Reporting Services Information to be delivered on Yellow Duck Guy - so keep checking back for more!

Yellow Duck Guy
Greg Olsen

Well if you would like to list all the databases on your Microsoft SQL Server then you can simply use this T-SQL piece of code below (tested with Microsoft SQL Server 2005):

PRINT 'List of all databases within your server:'
GO
EXEC sp_MSforeachdb @command1="print 'DATABASE: ' + ('?')"

Enjoy!

Yellow Duck Guy
Greg Olsen

• SQL Server 2008 Developer Edition x86, English
  (http://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID=6539)
• SQL Server 2008 Developer Edition x64, English
  (http://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID=6540)
• SQL Server 2008 Developer Edition x86, English ISO
  (http://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID=6856)
• SQL Server 2008 Developer Edition x64, English ISO
  (http://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID=6858)

One new feature: There are new data types, DATE  & TIME

Tell us any cool things you find!!

Yellow Duck Guy
Greg Olsen