Category Archives: SQL Server 2008

Enabling FILESTREAM In The Database

Once you have your server configured for FILESTREAM, you will need to configure your database. The easiest way is to establish it when you first create the database.


CREATE DATABASE FileStreamFTS ON 
PRIMARY ( NAME = FileStreamFTS_Data,
    FILENAME = 'd:\data\FileStreamFTS_Data.mdf'),
FILEGROUP FileStreamFTS_FileGroup1 
  CONTAINS FILESTREAM( NAME = FileStreamFTS_FileGroup,
    FILENAME = 'd:\data\FileStreamFTS_FileGroup')
LOG ON  ( NAME = FileStreamFTS_Log,
    FILENAME = 'd:\data\FileStreamFTS_Log.ldf')
GO

In the above example, I created my database in a directory on my D drive named “D:\Data”. If you examine the folder you ’ll now see two files, the mdf and ldf files. There is also a folder “FileStreamFTS_FileGroup”. It is in this folder that your files will eventually be stored in.

You may be tempted to poking around in the folder, and perhaps even access the stored files directly. My advice to you: don’t. Microsoft has strongly advised against this practice. After all, you went to all the trouble of setting up FILESTREAM to let SQL Server handle this for you, so let it do the handling. If you are curious to dig deeper into how FILESTREAM works behind the scenes, I would recommend Paul Randal’s excellent white paper available at http://msdn.microsoft.com/en-us/library/cc949109.aspx.

The above example shows how to add FILESTREAM capability when you first create your database. That’s great, but what if you already have an existing database you want to add FILESTREAM to? I have just such an existing database, “ArcaneCode”. To add FILESTREAM I need to issue two ALTER DATABASE statements. First, we need to add a FILEGROUP and indicate that file group contains FILESTREAM objects.


ALTER DATABASE ArcaneCode
ADD FILEGROUP ArcaneFileStreamGroup1 
  CONTAINS FILESTREAM
GO

In the second we have to indicate to the FILEGROUP where the FILESTREAM should be stored. Pass in the directory name where you want your files saved as the FILENAME parameter. As with the create statement the directory can not exist, if it does you’ll get an error.


ALTER DATABASE ArcaneCode
ADD FILE  
  (NAME = 'ArcaneFS_Group'
   , FILENAME = 'D:\Data\ArcaneCodeFS'
   )
TO FILEGROUP ArcaneFileStreamGroup1 
GO

This completes the steps needed to create a database with FILESTREAM, or add it to an existing database. In the next lesson we will look at creating tables that use FILESTREAM.

Enabling FILESTREAM on SQL Server 2008

One of the newly touted features of SQL Server 2008 is file streaming. For some time now SQL Server has allowed users to store large binary objects in the database inside a varbinary(max) field. Performance began to suffer, however, when the binary object was over 1 megabyte or so in size.

SQL Server 2008 solves this through its new FILESTREAM feature. With FILESTREAM SQL Server lets the operating system, Windows Server, do what it does best: handle the storage of binary objects, better known as files. In the database, SQL Server simply stores a reference that will let it open and close the file from the disk. FILESTREAM gives us the best of all worlds. When the file group is backed up so are the files, they can also be made part of a transaction.

By default, FILESTREAM is not enabled when you install SQL Server 2008. Activating it is a simple, two step process. As always though make sure to do proper backups before making any changes to your SQL Server, and be sure to first implement on development, then test systems before making changes to any production server.

First, you will need to enable FILESTREAM for the Windows Server it is running on. Begin by opening the SQL Server Configuration Manager. Once inside click on “SQL Server Services” then highlight the server you want to activate. In this example we clicked on “SQL Server (MSSQLSERVER)”.

image

Now that it is highlighted, right click on it and pick properties from the menu. Click on the FILESTREAM tab in the dialog that appears.

image

If you have a default install, your dialog should appear with nothing enabled, as this one does. Check on the first line, “Enable FILESTREAM for Trancact-SQL access”. This turns on the FILESTREAM feature, but with a drawback. It only works from T-SQL, and then only on the server itself. To make it useful we need to go further.

Once the first line is checked on, the second line, “Enable FILESTREAM for file I/O streaming access” will become enabled. Check it on. At this point the server will allow FILESTREAM from a separate application, but only when run on the same Windows Server that SQL Server is running on.

Since most of your users will not be running on the SQL Server itself, you will want to check on the final option which is now available, “Allow remote clients to have streaming access to FILESTREAM data”. Once this is checked, you can click OK to save your changes.

Note there may be one exception to checking on the final option. If you are running SQL Server Express, it is quite possible your application and the database may all be running on the same computer, and that you do not want any other machine to get access to this database. While this would be an uncommon situation, please note that the capability does exist for you to set things up this way if needed.

Now that the Windows Server is ready to handle FILESTREAM we need to enable the SQL Server. Fortunately this winds up being a simple matter. Open up SQL Server Management Studio, and enter the following command:


  EXEC sp_configure filestream_access_level, 2
  RECONFIGURE

Note the number at the end of the first line. Valid values for this are “0”, “1”, and “2”. “0” will disable FILESTREAM completely. “1” will enable it, but only for T-SQL code. “2” will fully enable it and allow access via .NET or other code. Note that these are slightly different from the way the Windows Server rights are set. Here, in order to run anything other than T-SQL you must use a “2”, regardless of where the code is executed (local or remote).

It is also possible to change this setting in the GUI. Right click on the server in SQL Server Management Studio, in this case it is “(local)”, and pick “Properties” from the menu. Go to the “Advanced” page and the FILESTREAM is the top most item, you can see it here with all of its options.

image 

Now your server has been properly configured to handle file streaming, you’ll want to create a database in a way to handle file streaming. We’ll do that in the next installment in this series.

Atlanta SQL Saturday – Full Text Searching

Let me start with a big thank you to everyone involved in the Atlanta SQL Saturday. It was a great event, well run and well organized. Also a big thanks to all those who attended my sessions, and put up with my cold.

Here is the information for the Full Text Searching presentation. Still working on the getting started with SSIS details, if you want to see the 2005 version it’s at http://code.msdn.microsoft.com/SSIS . I’ll add the 2008 base in the next day or two.

First off, the slides and sample code can be located at the Code Gallery site I setup specifically for Full Text Searching with SQL Server:

http://code.msdn.microsoft.com/SqlServerFTS

Look on the downloads page to see various projects around SQL Server Full Text Searching. I’ve created one “release” for each of the projects around FTS. Be sure to look on the right side at the various releases in order to see the various projects.

Next, you can get started with the basics by reading these entries on my blog:

Lesson 0 – Getting the Bits to do Full Text Searching in SQL Server 2005
Lesson 1 – The Catalog
Lesson 2 – The Indexes
Lesson 3 – Using SQL
Lesson 4 – Valid Data Types
Lesson 5 – Advanced Searching

After that you’ll be ready for some advanced topics.

Can you hear me now? Checking to see if FTS is installed.
Exploring SQL Servers FullTextCatalogProperty Function
Using the ObjectPropertyEx Function
Using FORMSOF in SQL Server Full Text Searching
Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search
Creating and Customizing Noise Words in SQL Server 2005 Full Text Search
Creating and Customizing Noise Words / StopWords in SQL Server 2008 Full Text Search
Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists

Finally, you can find some videos I did for JumpstartTV at:

http://www.jumpstarttv.com/profiles/3177/Robert-Cain.aspx

Introduction To Data Warehousing and Business Intelligence

At the Atlanta SQL Saturday 2009 one of the presentations I am doing is “Introduction to Data Warehousing and Business Intelligence”.

You can download the slide deck for this presentation in PDF format.

Any sample code came from either my Intro to SSIS presentation or the book Programming SQL Server 2008.

SQL Server Sample Data – The SQL Name Game

Like most folks, I seem to have a perpetual need for realistic test data. While there are many databases available, sometimes the need is quite simple. All I need is some names, perhaps dates and phone numbers that can be used for testing my applications, SSIS or SQL Server Reports. I decided to take care of this need once and for all, and set out with a simple goal. At the conclusion of my work I wanted to wind up with a realistic looking, but totally fake set of data. I wanted to do it in the simplest means possible, using whatever tools I had available. Finally, I wanted to do it as quickly as possible.

Along the way I documented my efforts, as well as created a sample table with 100,000 rows. When I started I thought to publish everything in a blog post, but it turned out to be far too much for a single blog post. Thus I decided to document everything in a white paper, and upload all the code to a MSDN Code Gallery site. Note that while I used the 2008 versions of SQL Server and Visual Studio, the SQL Scripts should run just fine with SQL Server 2005.

You can find everything at http://code.msdn.microsoft.com/SqlServerSampleData . Look in the downloads section for the complete PDF with all the details, as well as all of the sample data. Using the techniques outlined in the white paper you too could easily be generating your own test data for a wide variety of projects.

Generating a PDF file from a Reporting Services Report Viewer Control

In yesterday’s post, I demonstrated how to generate a SQL Server Reporting Services report without having to have SQL Server Reporting Services. The sample application used the Microsoft Report Viewer control to display the report. A common need in business is to generate documents, such as PDFs, that will later be archived. It turns out if you are using a report viewer control, this is easy to do programmatically.

First, you need to add one using statement to the top of your class, in addition to the others that were added yesterday.

using System.IO;

Next, we only need a few lines of code to generate the PDF.

      Warning[] warnings;

      string[] streamids;

      string mimeType;

      string encoding;

      string extension;

 

      byte[] bytes = reportViewer1.LocalReport.Render(

        "PDF", null, out mimeType, out encoding, out extension,

        out streamids, out warnings);

 

      FileStream fs = new FileStream(@"D:\ReportOutput.pdf", FileMode.Create);

      fs.Write(bytes, 0, bytes.Length);

      fs.Close();

This code snippet came right from the MSDN Books on Line, and is pretty simple. I could have selected another format by changing the first value passed into the Render method, for example “EXCEL” would have rendered it as a Microsoft Excel document.

In the code samples I placed the above sample in it’s own button, but I could just have easily placed it under one of the other demo buttons.

This ability brings up some interesting possibilities. For example, the report viewer control does not have to be visible to the user in order for this to work. Thus you could create an application that every night generated a series of reports and saved them as PDFs to some central location, such as a web server or document control server. All the user (assuming one was around) would have to see is a progress bar, the reports themselves never get displayed.

Using SQL Server Reporting Services in Client Mode

Recently I did a presentation at the March BSDA meeting. I showed how to use SQL Server Reporting Services without a SQL Server, or more specifically a SQL Server running Reporting Services. It got an enthusiastic response so I thought I’d add to it here by adding some reminder documentation, as much for myself as for all of you wonderful readers.

Using Reporting Services in Client, or Local mode is a 4 step process. First, you will need an XSD schema file to create the report on. Once you have the XSD you will be able to move to the second step, creating the report. Third you will need to place a Report Viewer control on your windows form, WPF form, or ASP.NET page. Finally you will need to write some code that generates an ADO.NET dataset, loads the report in the report viewer control, then binds it all together. Lets look at this step by step.

Normally when you create a report you connect to a database, then base it off of some object like a query, view, or stored procedure. The report is then uploaded to a Reporting Services server, which takes care of hosting it, displaying it, and generating the data for it. With client mode you have no server available, so we have to instead create a surrogate. That’s where our XSD file comes in.

Right click in Solution Explorer and “Add a new item”, and from the list of goodies select “XML Schema”. Name it something appropriate, letting the default extension be XSD. For this example I will be getting customer order data, so I’ve given it the name CustomerOrders.xsd. Visual Studio will think about it then add it to the project, and even helpfully open it for you. I don’t know about you, but hand typing XML Schema’s isn’t my idea of fun, so you should glace at it, go “that’s nice” then close it.

Now right click on the XSD file in Solution Explorer, and pick “Open with….”. In the dialog that appears, select “Dataset Editor”. When you do, Visual Studio presents a big scary warning message letting you know that you could lose contents, and that this will forever be a dataset XSD file. We have nothing in the file, so we’re cool with this, just click OK.

You will now be presented with a big surface area. In the middle it tells you to drag items from the server explorer or right click. If you have a table, view, or stored procedure you are free to drag it in, but most of the time you’ll want to base this off of a SQL query. Right click on the surface, and select Add…., Table Adapter from the menu. The first screen asks you for the database connection. This is the only time you’ll actually need a connection, in this example I am using the good old Northwind database. I pointed at Northwind and clicked next.

image

Next we are asked how we are going to access the data. Since we have a SQL Statement just pick the default of “Use SQL statements” and click Next.

image

Now take your SQL Statement and paste it in, and click Next.

image

OK, click Finish to wrap up the addition of the XSD. By default the adapter has a generic name, we should give ours something more meaningful. Click in the top bar, then enter a new name. Since my example report is for customer order data, I’ll name it CustomerOrders. I then went to the bottom bar and renamed the TableAdapter1 to CustomerOrderTableAdapter. You should now see something like:

image

Note that this will become your Data Source for the report. The data source will have the name of the XSD followed by the name of the source, in this case it will read CustomerOrders_CutomerOrders. OK, now it’s time to create the report.

Go back to Solution Explorer, right click and pick Add New Item. Navigate to the Reporting area and pick Report Wizard. Note the file extension should end in RDLC. If you have used Reporting Services before, you will know that reports typically end in RDL. However, client mode reports have just a slightly different syntax to them, thus the RDLC extension to differentiate the two. While you can modify an RDL to become an RDLC and vice versa, you have to do so by hacking the XML behind the report.

Note you can also choose just Report, but then you’ll have to setup everything manually. For this simple example though, we’ll just use the Report Wizard.

image

Give your report a meaningful name and click Add. The report wizard then shows you a welcoming screen if you’ve never run it before, just click Next.

Now we need to pick the data source. In this example, you want the CustomerOrders branch, so select it and click Next.

image

The next screen asks if we want a Tabular or Matrix report. Select the one for you, in my example I picked Tabular and clicked Next. The next screen asks how we want to display the data. For my example, I opted to group by the customers company name and contact name, then the order data went into the details area. Fill out as appropriate for your report and click Next.

image

The next screen asks how we want things laid out. This affects the look and feel of the report. For my example I just took the default and clicked next, however you are free to play with this to experiment with the different looks and feels your reports might have.

Likewise the next screen is also a look and feel one, asking what colorings we want to apply. Pick one that makes you happy and click next. You can always change it later, many times I pick the Generic one (which adds no colors) then fix it up afterward.

The final screen is the wrap up. Give your report a meaningful name and click Finish.

image

OK, you have a report, now you need a container. Open up the user interface you want to place the report viewer control on. In my example I went with a very simple Windows Forms application.

In my toolbox, I navigated to the Reporting section, where I only found one control, the MicrosoftReportViewer control. (Note I am using Visual Studio 2008 SP1, if you are on an earlier version your names may differ slightly). Grab it and drop it onto your design surface. I also added a Button control to the form to kick off the report display process.

image

Now it’s time for the last step, adding some code. In this example I’ve used a Windows Form. Opening it, the first thing we find in the form load area is::

      this.reportViewer1.RefreshReport();

(Note I left my report viewer control named reportViewer1.) Delete it, we’ll have it refresh elsewhere.

Now we need to add some using statements to the top of our class.

//Add these to the standard list above

using System.Data.Sql;

using System.Data.SqlClient;

using Microsoft.Reporting.WinForms;

The first two will be used in accessing our Northwind database, you may need to use different libraries if you were going to another database. I’ve also included a referenced to the Reporting.WinForms library so we can manipulate the report programmatically.

Now let’s go to the code for the button click event. First, we need to reset the report viewer in case we’d been using it to host another report.

      // Reset in case report viewer was holding another reportViewer1

      reportViewer1.Reset();

Next We need to set the report viewer to local mode. This tells it we’ll be supplying the report name from a local file, and binding the report to a local ADO.NET datasource.

      // Set the processing mode for the ReportViewer to Local

      reportViewer1.ProcessingMode = ProcessingMode.Local;

Our third step is to create a local report variable, and set it’s reference to the report viewer’s local report. This will make it easier to work with. Then we’ll set the location of the report we want to use.

      LocalReport localReport = reportViewer1.LocalReport;

      localReport.ReportPath = @"D:\Presentations\SQL Server\SSRS RDLC\SSRS_RDLC\Report2.rdlc";

Now we need to create an ADO.Net dataset, and populate it. I implemented most of that functionality in a method called GetCustomerOrders, which I’ll append at the bottom of these instructions. It’s very straight forward code.

      DataSet dataset = new DataSet("Northwind");

 

      // Get the sales order data

      GetCustomerOrders(ref dataset);

At this stage we have told it where our report is, and have created the dataset. Now we need to create a datasource for the report itself. We’ll use the ReportDataSource object. For the name, we’ll use the same name as the XSD schema, CustomerOrders_CustomerOrders. Then for the value we will give it the table from the dataset we created in code. It’s possible for a report to have multiple datasets, in the report we’d give each one it’s own name (based on the XSD) then here we’d bind the dataset table to the name we’d used in the report. Once done we will then add the new ReportDataSource to the local reports DataSources collection. Finally, we’ll referesh the report viewer to make it generate the report.

      // Create a report data source for the sales order data

      ReportDataSource dsCustomers = new ReportDataSource();

      dsCustomers.Name = "Customers_Customers";

      dsCustomers.Value = dataset.Tables["Customers"];

 

      localReport.DataSources.Add(dsCustomers);

 

      // Refresh the report

      reportViewer1.RefreshReport();

You can download a copy of these instructions, along with the entire sample project including code and reports, at the Microsoft Code Gallery site http://code.msdn.microsoft.com/SqlServerRSClient . As promised, below is a copy of the GetCustomerOrders routine, for your reference.

    private void GetCustomerOrders(ref DataSet dsNorthwind)

    {

      string sqlCustomerOrders = "SELECT c.[CustomerID]"

        + " ,c.[CompanyName]"

        + " ,c.[ContactName]"

        + " ,c.[ContactTitle]"

        + " ,c.[Address]"

        + " ,c.[City]"

        + " ,c.[Region]"

        + " ,c.[PostalCode]"

        + " ,c.[Country]"

        + " ,c.[Phone]"

        + " ,c.[Fax]"

        + " ,o.[OrderID]"

        + " ,o.[CustomerID]"

        + " ,o.[EmployeeID]"

        + " ,o.[OrderDate]"

        + " ,o.[RequiredDate]"

        + " ,o.[ShippedDate]"

        + " ,o.[ShipVia]"

        + " ,o.[Freight]"

        + " ,o.[ShipName]"

        + " ,o.[ShipAddress]"

        + " ,o.[ShipCity]"

        + " ,o.[ShipRegion]"

        + " ,o.[ShipPostalCode]"

        + " ,o.[ShipCountry]"

        + "  FROM [Northwind].[dbo].[Customers] c"

        + "  join [Northwind].[dbo].[Orders] o on c.CustomerID = o.CustomerID";

 

      SqlConnection connection = new

        SqlConnection("Data Source=(local); " +

                      "Initial Catalog=Northwind; " +

                      "Integrated Security=SSPI");

 

      SqlCommand command =

          new SqlCommand(sqlCustomerOrders, connection);

 

      SqlDataAdapter customerOrdersAdapter = new

          SqlDataAdapter(command);

 

      customerOrdersAdapter.Fill(dsNorthwind, "CustomerOrders");

 

    }

SQL Server Full Text Searching at the Atlanta Code Camp

On March 14th, 2009 I presented “Getting Started with SQL Server Full Text Search 2005/2008” at the Atlanta Code Camp. This post has all the links relevant to my talk.

First off, the slides and sample code can be located at the Code Gallery site I setup specifically for Full Text Searching with SQL Server:

http://code.msdn.microsoft.com/SqlServerFTS

Look on the downloads page to see various projects around SQL Server Full Text Searching. I’ve created one “release” for each of the projects around FTS. Be sure to look on the right side at the various releases in order to see the various projects.

Next, you can get started with the basics by reading these entries on my blog:

Lesson 0 – Getting the Bits to do Full Text Searching in SQL Server 2005
Lesson 1 – The Catalog
Lesson 2 – The Indexes
Lesson 3 – Using SQL
Lesson 4 – Valid Data Types
Lesson 5 – Advanced Searching

After that you’ll be ready for some advanced topics.

Can you hear me now? Checking to see if FTS is installed.
Exploring SQL Servers FullTextCatalogProperty Function
Using the ObjectPropertyEx Function
Using FORMSOF in SQL Server Full Text Searching
Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search
Creating and Customizing Noise Words in SQL Server 2005 Full Text Search
Creating and Customizing Noise Words / StopWords in SQL Server 2008 Full Text Search
Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists

Finally, you can find some videos I did for JumpstartTV at:

http://www.jumpstarttv.com/profiles/3177/Robert-Cain.aspx

Windows 7 and the Asus Eee PC 1000HE

I’ve had my eye on a netbook for some time, while I like my 17 inch laptop for all day developing, it’s a bit large for lugging to code camps. In addition the battery life has slowly been dwindling over the years, so I wanted something with long battery life.

image After some consideration, I picked the Asus PC 1000HE. It has a 10 inch display, it’s keyboard is 92% size and surprisingly comfortable even for my huge hands. The battery life so far has been phenomenal. Running on the mid level power setting with the back light at almost full bright and all the wireless turned on I still get over six hours. I imagine if I ran it in power saving mode, and doing the tweaks I could easily achieve the 9.5 hours of advertised battery life.

I did opt for the extra chip to expand to 2 gig, replacing the 1 gig chip with the 2 took me all of 10 minutes.

The unit came with XP Home, but I’ve been using Windows 7 since the public beta and couldn’t face going back to XP. Thus the first thing I did was install Windows 7 on the Asus.

So far, the only thing I have found that Windows 7 did not recognize was the hard wired Ethernet jack. This was easily remedied. I went to the downloads section of the Asus website and picked out my machine, with the XP Home system. I quickly found the LAN driver and downloaded it.

Since it was a Zip all I had to do was expand it, then I went into Windows 7 device manager and found the "unrecognized" Ethernet jack. I told Windows 7 to look for a new driver, pointed it to the folder where I had unzipped it to and boom it worked.

I have not had the opportunity to test the built in camera or microphones, but Windows device manager shows them as being present and fully functional.

So far I’ve installed what I call the Office "basics", Word, PowerPoint, Excel, and Visio Viewer and all of them work. I installed Virtual PC 2007, and while it ran my virtual machines it was just a tad on the slow side. Not to be unusable, but a slow experience.

In addition I didn’t want to always have to lug around an external drive with my VPCs on it, so I went ahead and installed Visual Studio 2008 (with SP1) and SQL Server 2008 Developer (with the GDR extensions). So far both seem to run fine, although I haven’t put them to anything extensive as of yet.

In the short time I’ve had the machine, there are a few tips I’ve picked up that I would like to pass along.

F11
Get used to the F11 (the typical shortcut), or "Full Screen" mode for your web browser. It makes browsing a very nice experience. Without it the tabs, url bar and window title bar, plus any of the extra tool bars that get installed will easily suck up 1/3 to 1/2 of the 600 vertical pixels. Full Screen mode makes this pretty much irrelevant. I can easily see everything I need to on a website in full 1024×600 mode.

Hide the Ribbon
In Office, you can hide the ribbon toolbar by simply double clicking on one of the ribbon tabs. When you hover over the tab the ribbon will appear. This saves a lot of real estate, but makes it quite easy to still use the ribbon. In addition you can easily toggle the hidden mode by double clicking a tab again to unhide the ribbon. 

Hide the Taskbar
Right click on Windows 7’s taskbar, select properties, then check the "Auto-hide the Taskbar". While the Taskbar doesn’t seem to take up much room, you’d be surprised how nice having that little bit of extra real estate can be.

TouchCursor
The one thing I don’t like about the design of the 1000HE’s keyboard is that the Home, End, Page Up, and Page Down keys are not their own keys. Instead you have to press the blue Fn key, then the left, right, up or down arrows to access these often used keys.

Instead I use a little app called TouchCursor. With it I can setup alternate key combos for these and other keys so that I never have to move my hands off the "home" keys on the letters. By default the spacebar is the toggle key, so SpaceBar + I moves the cursor up one row, SpaceBar + K moves it back down.

(Note, if you are a fan of CodeRush, you’ll know it too wants to use the space bar. Fortunately TouchCursor is configurable, so I changed the toggle from the SpaceBar to the letter A. Now on my system A+I is up, A+K is down, etc. )

That’s all I have on the Asus for now, but I’ll soon be putting it through it’s paces. Thursday night I will be speaking at the BSDA, then Saturday I will be at the Atlanta Code Camp giving a 9 am presentation on SQL Server Full Text Searching. After that I’ll be sure to blog and let you know how having the small form factor laptop worked for doing presentations.

If you have any handy tips for using the small netbooks, please leave a comment with your tip or suggestion. I’d love to hear about them!

Tracking down SQL Server Integration Services issues with Collation

At work I’ve been developing a big suite of packages to convert data from an Oracle system into our Data Warehouse. A lot of the supporting tables are almost a straight pull, except we are of course adding our own primary key, and then setting up a non clustered unique index on what had been the primary key in the old system.

A few tables have been driving us batty though, giving us “duplicate value” errors when trying to insert the rows from our SSIS package. The first thing I did to try and track down the problem was create an error table, and instead of having the package fail have it redirect error rows to my new error table. In case you are wondering, this is going to be a “one time shot” use for the packages, so we chose not to invest a lot of time and effort into error handling. We either want all the rows or none, and we’ll be running the packages manually so we’ll be there to know the results. But I digress.

When I went to look at the error table, it had all the rows from our source system in it. I scratched my head, thinking that can’t be right. A quick search found the answer in the Technet Forums. I needed to go into the OLE DB Destination and set the Max Commit count to 1. Of course you wouldn’t want to leave it like that for production, but for debugging it worked great. Once I did that, I was able to rerun the package and quickly identify my misbehaving row.

Next I looked at the value, and then looked for a similar value in my table. What I found was my source system had two rows, something like this example:

Arcane Code

Arcane code

Yes, the only difference was the second row had a lowercase letter at the beginning of the second word. Our Oracle instance had case sensitivity turned on. To it, these were two entirely different values. However, by default SQL Server is case insensitive; to it these two were the same. So my dilemma, how to fix this one column without having to alter my entire database?

It turns out there is an option in the Create Table syntax to set the collation. First, you should find out what your collation is currently set to. This is easy enough, just open SQL Server Management Studio, right click on the database and pick properties. Right there on the front page is the Collation.

image

Alternatively I could have run this SQL in SSMS (substitute your database name where I have AdventureWorks2008):

select databasepropertyex('AdventureWorks2008', 'collation')

Either way, in this example the default is SQL_Latin1_General_CP1_CI_AS. The important thing to note is the “_CI_”, which indicates case insensitivity. If we wanted to set the entire database, we would issue commands to change this to SQL_Latin1_General_CP1_CS_AS, which stands for case sensitivity. But as I said, in my case I don’t want to affect the entire database, so instead I will use this collation name in the create table syntax. Here is a simple example:

create table TestTable
(
  BogusPK bigint identity
  , FieldFromOracle varchar(200) collate SQL_Latin1_General_CP1_CS_AS not null
  , AnotherField varchar(200) null
)

All that I had to do was insert the collate clause between the data type and the not null clause. Note that this only affects the one column I had an issue with. FieldFromOracle is now a case sensitive column, I can add “Arcane Code” and “Arcane code” and still be able to add a unique index. The second column, here named “AnotherField” will remain case insensitive, the behavior you normally expect.

Before I wrap this up, I know someone will point out that allowing primary keys in your system that only differ in case is bad practice. For the record I totally agree, however this is a soon to be legacy system built by a vendor. Additionally, for various reasons I was not allowed to do any data cleansing to the source system. Just pull it like it is and put it in the warehouse. I imagine most of you are like me, that you don’t get to live in the ideal world, so hopefully knowing how to diagnose and deal with collation issues between databases will make your life a little easier.

Using a Local Reporting Services 2008 Report with an ADO.NET Data Set

SQL Server Reporting Services is an incredibly full featured reporting tool. An often asked question though is “How can I use Reporting Services without setting up a full SQL Server just to run Reporting Services?”

Fortunately the folks at Microsoft thought of this, and created a version of Reporting Services that runs in Local, or what Microsoft calls Client mode. There are several ways to use client mode, you can bind the report right to the database if you wish, or to an object. However if you have an application that’s been around for a bit, or maybe you’ve been around for a bit, chances are you have a lot of ADO.NET DataSets you’d love to use for data in a report. Today we’ll look at how to bind those data sets to a SQL Server Reporting Services report.

Let’s say you have created an application to work with the AdventureWorks2008 database. You user has now asked you for one last feature. They wish to display a list of Vendors in a report. They want to preview the report, print it, and be able to export it to a PDF format.

Based on your experience you know that SQL Server Reporting Services would be a good choice. However your client does not have an instance of SQL Server Reporting Services running in their corporation. Thus the path is clear, use SQL Server Reporting Services in Client mode.

Preliminary Work

Prior to beginning our work, we’ll need to do two basic setup steps. First, if you don’t have it already, you will need to download the Adventure Works 2008 database from CodePlex. Install it following their instructions. Here is the current location for AdventureWorks2008:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=18407

Now open up Visual Studio 2008 and create a new C# WinForms application. Note that while the ReportViewer control we’ll be using works fine in WinForms, ASP.Net, or WPF, for simplicity we’ll use a WinForms application. Give your project a meaningful name (I used ReportingServicesLocal).

Create the DataSource

Normally Reporting Services knows what tables and columns are available because you have setup a connection to a database. In this scenario however, we are going to bind the report to an in memory ADO.NET DataTable.

At design time then Reporting Services does not know about the DataSet, and so we must create a surrogate for Reporting Services. Thus we’ll create a special type of XML schema definition to stand in for our not yet created DataSet.

To accomplish this, first we need to create the Data Source schema by following these steps:

1. Right click on the project in the Solution Explorer window.

2. Select Add, New Item.

3. Click on the Data leaf of the Visual C# Items branch in the Add New Item window.

4. Pick the DataSet item. Give it a meaningful name, such as VendorList.xsd.

clip_image002

Now we need to add a table to the DataSet.

1. In your toolbox, under DataSet find the Data Table tool and drag it onto the design surface.

2. Click the DataTable1 and rename it to Vendors.

The last step in the process is to add our columns to the Vendors DataTable we just created.

1. Right click on the name of your DataTable and pick Add, Column from the pop up menu.

2. For the first column type in VendorName. Note that if we needed to, we could now go to the properties window and change the DataType to something other than the default of System.String. For this lab, everything we’ll use is a string so this won’t be needed.

3. Repeat step 2, adding these column names: AddressLine1, AddressLine2, City, StateProvinceName, PostalCode

When done it should look like:

clip_image004

Create the Report.

Now that we have a schema, we’re ready to create the report and add the components to it. To create the report, follow these basic steps.

1. Right click on the project and select Add, New Item.

2. In the pop up window, go to the Reporting leaf under the Visual C# branch.

3. Pick “Report”, and give the report a meaningful name such as VendorList.rdlc.

clip_image006

Now that the report is created, we need to add the components and data columns to it.

1. With the blank report, drag a Table control onto the report body.

2. Open the Data Sources window by selecting Data, Show Data Sources from the Visual Studio menu.

3. You should see the VendorList DataSet, under it the Vendors DataTable, and under it the columns.

clip_image008

4. Drag the VendorName to the first column of the table. Next, drag the City to the second column, and the StateProvinceName to the third.

5. Right click on the column header for StateProvinceName and pick “Insert Column to the Right”.

6. Drag the PostalCode to this newly inserted column. Your report should now look something like:

clip_image010

Adding the Report Viewer to the Windows Form

Now that the setup tasks are complete, it’s time to get to the user interface ready. First we’ll do some basic setup of the form.

1. When you created the basic project Visual Studio created a default Windows Form, Form1.cs. Start by changing the Text property to read “Report Viewer”.

2. While we’re at it, let’s change the (Name) property to frmReportViewer.

Now add the Report Viewer control to the form.

1. In the toolbox, navigate to the Reporting area, and drag a MicrosoftReportViewer control onto the form. Resize so it takes up the lower 90% or so of the form.

2. Change the name to rvwMain (or something meaningful).

Next add a button to the form. We’ll use it to trigger the report.

1. From the Common Controls area of the toolbox, drag a button control onto the form.

2. Change the (Name) property to btnDataSet.

3. Change the Text property to DataSet.

4. Double Click on the button to open up it’s code behind.

We’ll be supplying the data to the ADO.Net dataset using SQL Server, so we need to go to the top of the form and add a reference to the System.Data.SqlClient.

using System.Data.SqlClient;

Now let’s go into the btnDataSet_Click event, and add some code to fill our dataset. This code snippet will bind to our local SQL Server, create a command to do a simple select statement to a view, and fill the dataset.

  /* Fill the Dataset ------------------------------------*/
  string qry = "select v.Name as VendorName "
                  + ", v.AddressLine1 "
                  + ", v.AddressLine2 "
                  + ", v.City "
                  + ", v.StateProvinceName "
                  + ", v.PostalCode "
               + "from Purchasing.vVendorWithAddresses v "
              + "order by v.Name ";

  string connectionstring = @"Server=(local);"
    + "Database=AdventureWorks2008;Trusted_Connection=True;";

  SqlConnection connection = new SqlConnection(connectionstring);
  SqlCommand cmd = new SqlCommand(qry, connection);

  SqlDataAdapter daVendor = new SqlDataAdapter();
  daVendor.SelectCommand = cmd;
  DataSet dsVendors = new DataSet();
  daVendor.Fill(dsVendors);

Note that in the first line of our select statement, we had to use v.Name as VendorName. The column names we return from our dataset must match the column names we entered in the Data Source back in Exercise 2 Step 3. Fortunately SQL easy to use “AS” syntax makes this simple.

Also, even though in this example we use SQL Server, the connection could be to any Data Source such as MySQL or Oracle. The important thing is we wind up with a DataSet to bind to.

In the same btnDataSet_Click method we now need to tell the report viewer control which report to run, then where to get it’s data. To tell the ReportViewer control to use a local report (as opposed to a report residing on a Reporting Services Server) we need to set the ReportEmbeddedResource property.

  rvwMain.LocalReport.ReportEmbeddedResource = "ReportingServicesLocal.VendorList.rdlc";

Note the format of the string we pass in. It has the name of the project, then a dot, then the name of the report complete with it’s rdlc extension. You should also know this is case sensitive.

Now we need to tell the report where our data really is. To do this, we’ll tell it to bind the Vendor data table from the VendorList data source to the dataset we generated in the step above.

  rvwMain.LocalReport.DataSources.Add(
    new Microsoft.Reporting.WinForms.ReportDataSource(
    "VendorList_Vendors", dsVendors.Tables[0]));

We need to create a new ReportDataSource to pass into the Add method of the reports DataSources. In the constructor for the ReportDataSource we pass in two parameters. The first is the name of the DataTable we are binding to.

Note that it’s syntax is a bit odd, you have to address it with first the DataSet name, then use an underscore to append the name of the specific DataTable.

The second parameter is the specific table from the dataset to bind to. Since we only had 1 we can use the simple .Tables[0] syntax shown here. We could have also given it a specific name.

One final note, in this simple example we are only binding one data source. However it’s possible for reports to have multiple data tables contained in them. To bind each one, we would simply have created a data table for each in the XSD, then added the code to the step above to read each one in, then bound them in this step by repeating this line of code for each one.

Finally we’re ready to display the report. Simply add this line to trigger the generation of the report.:

      rvwMain.RefreshReport();

Test your application.

Everything is now setup, you should be ready to run.

1. Launch the app from Visual Studio.

2. Once open, click on the DataSet button.

3. Your screen should look something like:

clip_image012

 

And there you go, you too can now easily create nice looking reports from your existing ADO.NET datasets.

Jumpstart Today

Just wanted to mention my video, Altering SQL Server Full Text Catalogs is the video of the day on JumpStartTV. This is great timing after my weekend presentation of FulL Text Searching at the Alabama Code Camp. Thanks to everyone who attended my session, you were a lot of fun. Thanks also to the whole Code Camp team for putting on a great event. Also thanks to the contestants and the MVPs who allowed me to draft them into being judges for the Speaker Idol contest.

If you happen to pick this up after Ground Hog’s Day, you can jump right to it at http://jumpstarttv.com/altering-sql-server-full-text-catalogs-_547.aspx . You can see all of the videos I did at http://jumpstarttv.com/profiles/3177/Robert-Cain.aspx, along with the videos I’ve watched.

Presenting SQL Server 2005 2008 Full Text Searching at Alabama Code Camp

On January 31st, 2009 I am presenting “Getting Started with SQL Server 2005/2008” at the Alabama Code Camp that is taking placin Montgomery, Alabama. This post has all the links relevant to my talk.

First off, the slides and sample code can be located at the Code Gallery site I setup specifically for Full Text Searching with SQL Server:

http://code.msdn.microsoft.com/SqlServerFTS

Look on the downloads page to see various projects around SQL Server Full Text Searching. I’ve created one “release” for each of the projects around FTS. Be sure to look on the right side at the various releases in order to see the various projects.

Next, you can get started with the basics by reading these entries on my blog:

Lesson 0 – Getting the Bits to do Full Text Searching in SQL Server 2005
Lesson 1 – The Catalog
Lesson 2 – The Indexes
Lesson 3 – Using SQL
Lesson 4 – Valid Data Types
Lesson 5 – Advanced Searching

After that you’ll be ready for some advanced topics.

Can you hear me now? Checking to see if FTS is installed.
Exploring SQL Servers FullTextCatalogProperty Function
Using the ObjectPropertyEx Function
Using FORMSOF in SQL Server Full Text Searching
Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search
Creating and Customizing Noise Words in SQL Server 2005 Full Text Search
Creating and Customizing Noise Words / StopWords in SQL Server 2008 Full Text Search
Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists

Presenting SQL Server Full Text Searching at Alabama Code Camp

This Saturday, January 31st is the Alabama Code Camp in Montgomery AL. I’ll have the privilege of once again presenting one of my favorite subjects, SQL Server Full Text Searching. Don’t think this session is just for DBAs, I think developers will find it useful as well since I will include a demo code sample that calls a full text search query from a C# application. My session is currently scheduled last session of the day, and rumor has it attendees might have an extra chance at a give away at the end of my talk.

Speaking of give a ways, don’t forget the Speaker Idol contest. Still no entries, they are due to my in-box by noon this Friday the 30th! Five minute talk gets you a shot at a 1 year Premium MSDN Subscription.

See ya’ll there!