Category Archives: Programming

SQL Server Compact Edition Connection Strings

In my recent presentation I talked about an important but subtle difference with connection strings when using SQL Server Compact Edition. It was so important I thought I’d make a special blog post out of it.

There are two methods for programmatically accessing data in SQL Server Compact Edition (SSCE). The first method is using the System.Data.SqlServerCe library. When you create an instance of the SqlCeEngine, you need to pass a connection string formatted like so:

DataSource=”mydatabasename.sdf”; Password=’mypassword’

This method is valid, by the way, for version 3.1 or 3.5 of SSCE. The second method, available with Visual Studio 2008 and the 3.5 version of SSCE is to use LINQ to SQL. When creating the DataContext object, you also need a connection string formatted like so:

Data Source=mydatabasename.sdf; Password=’mypassword’

Note very carefully the two differences. First, the name of the sdf file lacks the double quote marks in the LINQ to SQL version. Second, note the Data Source phrase has a space between the words in the LINQ version, where the SqlCeEngine version lacks the space.

It’s a small distinction, but it’ll drive you nuts if you don’t catch it. I drove myself nuts for quite a while because I didn’t notice the extra space in Data Source when I began experimenting with LINQ to SQL! Hopefully my pain will save others some hair pulling.

Advertisements

Presenting Getting Started with SQL Server Compact Edition 3.5 at BUG.NET Meeting

Just wanted to let everyone know I’ll be doing a presentation this coming Tuesday night, August the 12th for the Birmingham .Net Users Group (BUG.NET). My topic, as you may have guessed from the title, will be using SQL Server Compact Edition.

While I will be using Visual Studio 2008, I will point out which pieces are 2005 compatible. I will also cover the use of both traditional coding techniques as well as how to use LinqToSQL to talk to the Compact Edition.

The meeting takes place at 6:30 pm at New Horizons Training Center in Homewood.

I also plan a new series of blog posts to start later this week on the subject, and will be creating a new Code Gallery site to hold my examples.

Also, don’t forget the regular BSDA meeting this coming Thursday night, the 14th. Also starting at 6:30 pm at New Horizons, Shannon Brooks-Hamilton, a software usability expert, will be there to talk about user interface design. Lots of good thought material on how we can make better UIs for our users.

Arcane Review: Expert SQL Server 2005 Integration Services

If you recall my “Good Reads” post from June 25th, you will remember I am a big believer in books as a learning medium. I like to employ a lot of different ways to learn: user groups, blogs, podcasts, videocasts, and magazines to name a few. But for really in depth coverage, it’s hard to beat a nice book in your hands. I got some good feedback from my mention last week of Andy Leonard’s new e-book on Data Dude, so I thought that I would continue by adding book reviews to the blog every so often.

Expert SQL Server 2005 Integration Services For this review I thought I’d cover a book that seems to constantly be on my desk lately: Expert SQL Server 2005 Integration Services, by Brian Knight and Eric Veerman. This book does a really good job and is specifically targeted toward the data warehousing professional. One entire chapter is devoted to ETL for dimension tables; another chapter focuses on the fact tables. It was great to have coverage so focused on these topics.

Another favorite part of the book is the two chapters on deploying and managing SSIS packages. So often these topics are glossed over, especially the managing piece. The book does a great job in covering all the tools and practices around this subject. I’ll mention one more chapter, one that focuses on package reliability. They cover logging, auditing, event handling, checkpoint files, and even suggestions on testing error handling logic.

There are many more chapters in the book, such as migration from DTS (SQL Server 2000) and Scalability, for you to discover. The other thing I love about this book is the brevity. The authors cover an amazing amount of information in just 382 pages. As a busy, busy person I very much appreciate the conciseness they achieved without sacrificing any clarity.

I’ve met both authors, and have heard them speak. They are both very nice, knowledgeable individuals, and I highly encourage you to attend one of their presentations if you get the chance, or if not at least buy their book from your favorite retailer; you will find it a great investment.

Boy Howdy Those Deep Fried Bytes Are Yummy

Long time readers of my blog or Twitter posts will know I am a big fan of podcasts. There’s a new one worth taking a listen to:

Deep Fried Bytes

Deep Fried Bytes is a new podcast hosted by Mississippi MVP Keith Elder and Chris “Woody” Woodruff. I listened to their inaugural episode on the way to the office this morning and quite enjoyed it. While they will cover all aspects of technology, they will have a heavy focus on .Net development.

The audio quality was superb, it may have been a first episode but their production quality and format made it sound like they’d been podcasting for years. I’ve already added the show to my Zune as a subscription, and recommended it to the Zune Marketplace. I’m eagerly looking forward to the next episode!

Using FormsOf in SQL Server Full Text Searching

In the past I’ve talked about some advanced text searching techniques for SQL Server Full Text Searching. Another you can take advantage of is FormsOf. Let’s say we’ve setup a full text search index on the Adventure Works ProductDescription tables’ Description field. (For more info on how to do this, go to the Arcane Lessons page of this site and scroll down to the “Getting Started With SQL Server 2005 Full Text Searching” section.)

FormsOf has two ways to use it, the Inflectional mode and the Thesaurus mode. Let’s look at an example, and then I’ll explain the differences. For this example, we’ll combine data from several tables to get back some meaningful information from the AdventureWorks database.

— Example 1 – FORMSOF INFLECTION

 

select [Name], ProductNumber, [Description]

  from [Production].[Product] p

    , [Production].[ProductDescription] pd

    , [Production].[ProductModelProductDescriptionCulture] pmpdc

 where p.ProductModelID = pmpdc.ProductModelID

   and pmpdc.ProductDescriptionID = pd.ProductDescriptionID

   and CONTAINS(pd.[Description], ‘FORMSOF(Inflectional, light)’ )

 

— Example 2 – FORMSOF THESAURUS

 

select [Name], ProductNumber, [Description]

  from [Production].[Product] p

    , [Production].[ProductDescription] pd

    , [Production].[ProductModelProductDescriptionCulture] pmpdc

 where p.ProductModelID = pmpdc.ProductModelID

   and pmpdc.ProductDescriptionID = pd.ProductDescriptionID

   and CONTAINS(pd.[Description], ‘FORMSOF(Thesaurus, light)’ )

Both examples use the same, I admit, rather bizarre syntax. Inside the string you pass to the CONTAINS clause, you put FORMSOF, with the word Inflectional or Thesaurus, a comma, then the word or phrase you want to search form. In this example I use Contains, but FormsOf also works with FreeText as well.

So what is the difference between Inflectional and Thesaurus? Inflectional finds all of the tenses of a word. For example, if you passed in Start, Inflectional will find Start, Started, and Starting. For nouns, Inflectional finds the single, plural, and possessive forms.

Thesaurus works like you would expect a thesaurus to. It will find variations of a word, Start, Begin, etc. Essentially, words that have the same meaning.

How do these relate to Contains and FreeText? Normally Contains looks for an exact match. FullText matches the meaning, but not the exact words in the query. FullText is very similar to Thesaurus, and even uses the Thesaurus in its work. FullText will break out the search string into its individual words, if there is more than one word. For each word, it generates the inflectional forms of the word, then identifies a list of matches for each word based on the thesaurus. FormsOf(Thesaurus… on the other hand just uses the thesaurus to do the search, without going through the inflectional step.

Using a combination of Contains, FreeText, and FormsOf you can give your users some real flexibility, ranging from exact matches to wide open searches.

SQL Server Migration Assistant

At work we have an Oracle based system we’ll be retiring sometime next year. We want to keep the data around for reporting, but don’t have the manpower or funds to properly flatten the database into a true data warehouse schema. We are leaning then to copy the data into our SQL warehouse database in almost a direct copy of the legacy’s schema with just a few minor tweaks.

In looking around for an efficient way to handle this, I found a tool called the SQL Server Migration Assistant for Oracle. This handy tool will copy everything, tables, views, stored procedures, triggers, just about everything you’d want.

In my case, all I really want is the table layouts. I will be making a minor tweak to the table layouts so I can combine four databases into a single one. In just a few hours, I was able to use SSMA to generate create table scripts for several hundred tables. I then brought the script into my favorite text editor, UltraEdit, and used it’s regular expression capability to add a source database field as the first field in every create table script. Saved ’em, ran it and in short order have a complete schema ready to hold my data.

Even using the SQL Server Migration Assistant in such a limited fashion, I was still able to save myself weeks of coding and trying to figure out how to manually map Oracle datatypes to SQL Server. If you are looking at any kind of data conversion take a look at this tool. You can find more info on the SSMA site at:

http://www.microsoft.com/sql/solutions/migration/oracle/default.mspx

Birmingham Tech News and Events

There are some big doings going on in the event community over the next few weeks.

First off, this Thursday May 8th at 6:30 pm the Birmingham Software Developers Association (BSDA) will be having “The Variety Show”. Join us for a variety of 15 minute presentations by various club members on what they’ve been working on lately–there should be something for everyone and the floor is open for anyone who’d like to do a short presentation.

On Friday, May 9th the IPSA group will meet during lunch at the McWane center, the topic will be The Social Media Toolbox.

Next week, the Birmingham .Net User Group (Bug.Net) will be having it’s regular meeting on May 13th at 6:30 pm. Stay tuned to their website for speaker and details.

Then, on Wednesday, May 14th at 6:30 pm the BSDA and Bug.Net are pleased to co-present a special event. Regional speaker Michael Neel will be here to talk on DataSets:

DataSets are Evil. They will hog your CPU, steal your RAM, and rob your home. This is the story surrounding DataSets, but what is fact and what is myth? In this session we will look at DataSets and the tools that go with them to see how they can save you development time while not crashing the server. We’ll also dive into DataSets in 2008 with LINQ to DataSets and Unit Testing with DataSets.

Learn more about Michael at vinull.com/profile

Finally, beginning at 5:30 pm on May 20th the Steel City SQL Group will meet. MVP Kevin Bowles will be here to talk about SQL Server 2008 Development. Kevin is a great speaker, his sessions are always loaded with useful information.

With the exception of the IPSA meeting, all of the other events will be held at the New Horizons training center in Homewood. A special thanks to the folks at New Horizons for making their facilities open to the Birmingham user group community!