SQL Server Full Text Searching – Can you hear me now?

There are times when you might need to know, programmatically, if the Full Text Search component for SQL Server is even installed, and if so is it installed for a particular database. In SQL Server 2008 this won’t be an issue, as (according to the books on-line, and remember this is still an unreleased product) the full text search engine is integrated into the product and thus will always be there. However, in SQL Server 2005 the Full Text Search engine is an optionally installed component. Worse, by default the FTS engine is not installed, you have to explicitly install it.

Therefore, before setting up your app on a new server it would be a wise precaution to run a simple query and confirm the engine is there. It turns out there’s actually two methods for doing so:

select ServerProperty(‘IsFullTextInstalled’)

and

select FullTextServiceProperty(‘IsFulltextInstalled’)

both behave the same, they return a 1 if FTS is installed, if not installed they return a 0. Be aware they will also return a Null if there is an error (for example, you misspelled IsFulltextInstalled). If it turns out not to be installed, there’s not a great deal programmatically you can do to install new software on the server, you’ll have to have a manual intervention on the part of the system admin or DBA.

Once you have confirmed the engine is installed on the server, you should then check to see if the database has had full text enabled for it. Another simple query fills the bill. The first parameter we pass is the name of a database, the second is the name of the database property we wish to examine, in this case IsFullTextEnabled:

select DatabaseProperty(‘AdventureWorks’, ‘IsFullTextEnabled’);

Like the others, it returns 1 if it’s enabled, 0 if it’s not, or a Null if there was an error (for example, passing in a database name that doesn’t exist). Please note the DatabaseProperty does not indicate the presence of a catalog, it merely indicates whether the database has been enabled for full text searching.

Should the database not be enabled (a 0 is returned) you can enable it by calling a stored procedure:

sp_fulltext_database ‘enable’

This will enable full text searching for your current database. You can also call the stored proc with a ‘disable’ parameter to turn off FTS, but why would you want to ! 😉

By the way, to do most of this you will need DBA level privileges. That’s a logical conclusion, especially if you are calling these as part of a program or set of scripts to create or modify a database.

BarCamp Birmingham 2 Presentations

At last Saturday’s BarCamp Birmingham, I gave three presentations. The first was on Virtual PC 2007. For more info on it just look to my previous post, which has the first video on VPC. I’m currently working on the other videos in the series and should have them up this week.

My second presentation was “The Developer’s Experience”. As promised in the session, here’s the complete PDF of my slides: The Developer Experience. This has hyperlinks to all of the tools I presented.

My final presentation was on Full Text Searching on SQL Server 2005.  First, here is a PDF of the PowerPoint slides: Full Text Search Power Points

Next, most of the demos used SQL statements. This PDF file has all of the SQL plus some associated notes. Full Text Search Demo Scripts

Finally, I didn’t get to demo this at BarCamp due to time, but I do have a WPF project that demonstrated how to call a full text search query from a WPF Windows application. Annoyingly enough WordPress (who hosts my blog) won’t let me upload ZIP files, so I renamed the extension to pdf. After you download the file to your drive, remove the .pdf and put the zip extension back on, then it should expand all the source for you correctly. (Yes, I know, I really need to get a host server for binaries, one of these days I’ll get around to it, but for today…) Source for WPF Demo

See you at the next BarCamp!

Atlanta Code Camp – Introduction to SQL Server 2005 Integration Services (SSIS)

Thanks to everyone who stuck with me during my Saturday morning presentation at the Atlanta Code Camp. For those who didn’t make it, I had come down with either food poisoning or some sort of virus on Thursday night and was extremely sick on Friday. I had recovered enough Saturday to make the camp, even though my voice was just about gone. I promised to post my materials, so without further delay here they are:

First off, here’s the power point slides, in PDF format: Intro to SSIS Slide Deck

Next, here is the script I used to generate my demo: Intro to SSIS Script. If anyone has issues with the directions, please e-mail me and let me know, this is my first pass at this format and I want to ensure it’s usable for everyone.

Finally, here is the project:

SSIS Test 1_zip

I am feeling a bit better today, slept most of Sunday and while I’ve totally lost my voice my fingers still work so I wanted to get this out here.

Thanks to the folks in Atlanta for a great code camp, and thanks again to everyone who attended my session it was a great crowd.

Atlanta Code Camp

I just wanted to let everyone know I will be speaking at the Atlanta Code Camp, on Saturday March 29th 2008. My subject will be Introduction to SQL Server 2005 Integration Services (SSIS). From the preliminary schedule I’ve seen, this looks like an awesome code camp, eight tracks this time! The SQL Server track concentrates a lot on BI (Business Intelligence). Three of the five sessions are on SSIS, so if you are looking to learn more about this subject this is the place to be!

The camp fills up quick, I’m surprised it hasn’t reached it’s limit yet. Just a little over a week away so head over to their site and register now!

Arcane Saturday SQL Tip

I was lucky enough to escape the grip of the dread meeting monster this week and actually got to do some T-SQL coding. I needed to know what sever my T-SQL code was running on. It made a difference as I would be taking one action if we were on a production instance, another if we were on a test box. SQL Server 2005 has some nice system information views built into the master database. All you have to do is use:

Select [name] as CurrentServerName from sys.servers where server_id = 0;

That’s all there is to it, according to books online the row with server id 0 is always the current server. You may find it useful to look through the other rows in the table, as any linked servers are recorded here as well.

Differencing Disks in Virtual PC 2007

Yesterday I mentioned I was going to get SQL Server 2008 installed in a Virtual PC (VPC). Now, I could have setup a virtual machine from scratch, or copied an existing one. But there’s a better way: differencing disks. Differencing disks allow you to create a virtual machine, then use it as a base for new machines. Much like you would create a base class and then let new classes inherit from your base.

My first step was to create a brand new virtual PC. I chose Windows Server 2003, using the one from my MSDN license. I could also have gone with XP, or the advanced versions of Vista licenses you to install up to four virtual machines in addition to itself as the host. So I get my VPC setup with Windows Server 2003, and make sure all of the windows updates have been applied, service packs, etc. In addition, if there are any additional tools / utilities I’d like to have available for every machine I’ll be wanting create from it I’ll install those as well. I’m thinking of things like UltraEdit / Notepad++, IE7, AllSnap, etc. I finally conclude by shutting down the machine.

Exit Virtual PC, and go to the folder where your virtual PC’s reside. First delete the VMC file (the small one) of your Virtual PC. You won’t need it any more, as you’ll never open this VPC directly. If you did, you would break all the machines that inherit from it. Again, not unlike changing the signature of a base class.

Next, mark the VHD, the hard drive as Read Only. Again this is for your protection, to keep you from doing something accidental to the base. At this point we have our base machine created, and can now make new machines from it.

Launch Virtual PC again. Click on File, Virtual Disk Wizard. You are given a simple dialog that lets you know you’re in the Virtual Disk Wizard. Click Next to move along.

[Picture 1 - Welcome to Virtual Disk Wizard]

Next it asks if we want to create a new disk or edit an existing one. We’ll want to create a new one, so just click Next.

[Picture 2 - Create a new virtual disk]

Now it wants to know what kind of disk to create. We’re doing a hard disk, so just take the default of virtual hard disk and click next.

[Picture 3 - Disk Type]

Now it asks where you want to put your virtual hard disk. I keep mine on my D drive, and use a naming convention. I start with the OS, then the main software I am using. I then use either the word Working, to indicate it’s alright to launch and work in it, or Base, to show the vhd should only be used to inherit from and not be launched. In this case I will be using this as a working area for my SQL Server 2008 CTP6, so I used Working. You are free of course to name it whatever you want, use a name like “Hanselman is cool.vhd” if you like I just prefer something a bit more logical.

[Picture 4 - Disk Location]

OK, this is where you need to pay attention, as this is the first time you’ll need to change a default. Here you are asked what type of virtual hard drive to create. The default is dynamically expanding, and it’s what you’d want to use if you are installing an OS from scratch or are creating a second hard drive for your virtual machine. Fixed size would be used if you are creating a disk for something like a USB drive and want to make sure it won’t get too big. Again, this would be used when you need an empty drive.

In our case we want the third option, Differencing. What this does is tell the Virtual PC application to base the new hard drive on an existing one. From here on out, only the changes you make to the virtual drive will be recorded. This has a lot of benefits. First it saves you disk space, in that you can use the same base with multiple virtual machines. Second, it lets you install the base OS only once, and not have to keep recreating it over and over. Finally, you can create multiple generations of disks. For example, I could create a base of Windows 2003, then another base with Visual Studio added. I could then use that base to inherit from, and create two drives. One could be used with SQL Server 2008, the other with SQL Server 2005. In our case we’re keeping it simple, so pick Differencing and click next.

[Picture 5 - Hard Disk Options]

Next we need to pick the virtual hard drive we want to base our new machine on. In this case I am selecting my Windows Server 2003 core base, and clicking next.

[Picture 6 - Pick Base Hard Drive]

Next we are told it has all the info it needs. All we have to do is click Finish and we’ll have our new Virtual Hard Disk.

[Picture 7 - Complete Disk Creation]

Virtual PC thoughtfully tells us it was successful.

[Picture 8 - Confirmation Message]

OK, we have a new disk, but now we need to tell Virtual PC we want to use it. Back on the Virtual PC Console, Select File, New Virtual Machine Wizard OR click the New… button on the console. Virtual PC has a need to tell us what we just picked, so just click Next.

[Picture 9 - Create Machine Wizard]

This time we are creating a new virtual machine, which will be based on the virtual hard drive we just created, so take the default and click next.

[Picture 10 - Create a machine]

Next we need to give our machine a name. I usually give it the same name as the hard drive, except for the vmc extension. Name yours and click next.

[Picture 11 - Macine name and location]

Now it asks what OS we’ll be using. Note it has automatically detected that I’m using Windows Server 2003, so all I have to do is click next.

[Picture 12 - Confirm Operating System]

Now it asks what my default RAM size will be. I figure 256 MB is a bit small, since I have the ram I upped it to 768 MB. Set yours according to the free space you can spare and click next.

[Picture 13 - Select default amount of memory]

Now we’re asked if we want to use an existing disk or create a new one. Obviously we want to use the differencing one we just created, so click next.

[Picture 14 - Existing Disk or New Disk - We want existing]

Next it asks where our existing drive is, pick it out using the Browse… button or type it in.

[Picture 15 - Pick name of existing disk]

Let me call your attention to the Check Box, “Enable undo disks”. If you leave this unchecked, your virtual machine will behave like a normal computer. Any changes you make are applied and saved. If you check the undo option on, then during your session any changes are written to a temporary file. When you exit the VPC, you are asked if you want to save any changes you made. If you say yes, they will be permanently applied to the virtual machine. If you say no, they are discarded, lost forever. Undo disks are ideal for test situations where you want to run the same changes over and over but not save them. Testing software installs, for example, or in a classroom where you want the students to do labs but not save them.

While Undo Disks can be very helpful, in this situation I don’t really need them as I want to keep all my changes so I will leave this unchecked and click next.

[Picture 16 - Complete Machine Wizard]

OK, we’re at the finish line. All we have to do is click Finish to complete the creation of our new virtual machine.

Let me call your attention to the file sizes of our new machine. Take a look at them in explorer…

[Picture 17 - Explorer snapsho showing small size of vhd]

Note how tiny the vhd file is right now. That’s because it’s based on another drive, where all the OS bits are. As we open it and apply changes (such as installing SQL Server 2008) it will grow in size, but we’ll always be able to save the disk space of the OS as it’s coming from another file.

The down side to differencing disks is speed, because they are in multiple files the performance won’t be as great. Additionally you can’t update the base machine without breaking its descendants. However, differencing disks offer several advantages as well. They save you time, in that you can create a base OS once and use it over and over. As you can see above they can also save you disk space, in that the core OS only takes up space once on your drive and not over and over.

Consider Differencing Disks, and whether they might be appropriate to your development environment.

SQL Server 2008 February CTP 6 Released

By now you’ve probably heard the news that the latest SQL Server 2008 CTP is out. This release has a lot of new BI functionality included, such as:

  • Full Text Search
  • Support for Word export in Reporting Services
  • Data Visualization Enhancements
  • Report Design Enhancements in BIDS

I intend to install the latest CTP in a virtual environment and start testing out some of the cool new functionality. If you want to follow along, head over to http://connect.microsoft.com and sign up to play with the SQL Server 2008 CTP! Now if you’ll excuse me I need to get started installing!

SQL Server 2005 Full Text Searching at the Huntsville Alabama Code Camp

My third and final presentation for the Alabama Code Camp 6 is “Introduction to SQL Server Full Text Searching”. Here are the materials I’ll be using during the demo.

First, here is a PDF of the PowerPoint slides:

Full Text Search Power Points

Next, most of the demos used SQL statements. This PDF file has all of the SQL plus some associated notes.

Full Text Search Demo Scripts

Finally, I did a WPF project that demonstrated how to call a full text search query from a WPF Windows application. Annoyingly enough WordPress (who hosts my blog) won’t let me upload ZIP files, so I renamed the extension to pdf. After you download the file to your drive, remove the .pdf and put the zip extension back on, then it should expand all the source for you correctly. (Yes, I know, I really need to get a host server for binaries, one of these days I’ll get around to it, but for today…)

Source for WPF Demo

Introduction to SQL Server Integration Services – Huntsville Alabama Code Camp

My second post of the day at Alabama Code Camp 6 in Huntsville is “Introduction to SQL Server Integration Services”.

The slide deck is here: Intro to SSIS Slide Deck

The “cheat sheet” or script I used to do the demo is here: Script for doing the SSIS Demo You can step through it to recreate all of the things I did in the demo today.

Finally here is the finished project. It’s actually zipped, but my current host doesn’t like zip extensions so when you download it change the extension from txt back to ZIP. Finished SSIS Project

Steel City SQL Server Users Group – SQL Server 2005 Full Text Searching

Tonight I’ll be presenting at the Steel City SQL Users Group “Introduction to SQL Server Full Text Searching”. Here are the materials I’ll be using during the demo.

First, here is a PDF of the PowerPoint slides:

Full Text Search Power Points

Next, most of the demos used SQL statements. This PDF file has all of the SQL plus some associated notes.

Full Text Search Demo Scripts

Finally, I did a WPF project that demonstrated how to call a full text search query from a WPF Windows application. Annoyingly enough WordPress (who hosts my blog) won’t let me upload ZIP files, so I renamed the extension to pdf. After you download the file to your drive, remove the .pdf and put the zip extension back on, then it should expand all the source for you correctly. (Yes, I know, I really need to get a host server for binaries, one of these days I’ll get around to it, but for today…)

Source for WPF Demo

Look forward to seeing you at the New Horizons Training center tonight, 6:00 PM!

Don’t Uninstall Visual Studio 2005 Yet!

One of the great benefits of Visual Studio 2008 is the ability for it to target multiple .Net Frameworks. This means, in theory you could go ahead and begin using Visual Studio 2008 even though you still need to write apps that are 2005 / .Net 2.0 compliant. You might be tempted to go ahead and uninstall 2005. And that would be fine if you are only doing .Net development. But wait…

If you are still doing SQL Server BIDS (Business Intelligence Developer Studio) then don’t uninstall Visual Studio 2005! Currently there’s no support in VS2008 for doing SQL Server 2005 BIDS Development. If you uninstall VS2005 you won’t be able to do any more BIDS work. Trust me, I found out the hard way.

After uninstalling VS2005, I went to do a BIDS project and that’s when I got hit with the nasty surprise. The uninstall had also removed the Dev Environment that was shared with BIDS. I tried to rerun the install of my SQL Server Developer Edition, but for some reason it thought I wanted to upgrade. It kept giving me the message “You cannot upgrade a version of SQL Server from the GUI, you must use the command line.”

I finally had to reinstall VS2005, along with all it’s service packs. After that I was able to work on my BIDS projects again. So take it from me, if you are still doing SQL Server 2005 Business Intelligence projects, Visual Studio 2005 still has some life in it yet.

Huntsville User Group – SQL Server 2005 Full Text Searching

Tonight I’ll be presenting at the Huntsville Users Group “Introduction to SQL Server Full Text Searching”. Here are the materials I’ll be using during the demo.

First, here is a PDF of the PowerPoint slides:

Full Text Search Power Points

Next, most of the demos used SQL statements. This PDF file has all of the SQL plus some associated notes.

Full Text Search Demo Scripts

Finally, I did a WPF project that demonstrated how to call a full text search query from a WPF Windows application. Annoyingly enough WordPress (who hosts my blog) won’t let me upload ZIP files, so I renamed the extension to pdf. After you download the file to your drive, remove the .pdf and put the zip extension back on, then it should expand all the source for you correctly. (Yes, I know, I really need to get a host server for binaries, one of these days I’ll get around to it, but for today…)

Source for WPF Demo

Look forward to seeing you in Huntsville tonight!

Allowing SQL Server 2005 Developer Edition to Receive Remote Connections

I was working with another developer on a SQL Server project, and we decided to copy his database changes to my box. The simplest thing would be to just connect to my instance of SQL Server 2005 Developer Edition from his, and then apply the changes. When we tried though we kept getting the error:

“An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. “

Huh? I’m scratching my head thinking the developer edition is supposed to be the same as the enterprise edition, and what good is a SQL Server that won’t allow connections? So I do a little digging and found a great knowledge base article 914277. According to it the Express and Developer editions are configured to NOT allow remote connections. When you think about it, there is some logic behind it. The Express edition is designed for light weight applications used locally and the Developer Edition is designed for a single developer to create test and prototype databases and then connect to the main development servers that will be used.

There are times though when it makes sense to turn on the ability to remote connect. Collaboration is one of the main reasons I can point out, as was the case with my co-worker and I. Another is testing, you may want to install and test your new application in a clean virtual PC and let it connect to your developer instance of SQL Server 2005.

The instructions from the knowledge base article are very well laid out and simple, so I won’t bother to reiterate them here. I did want to get the word out however so others could find this as well. We didn’t find it necessary to have to complete the firewall pieces of the instructions, only the first two parts. However we are behind a pretty heavy duty firewall at work, so your environment may be different.

Alabama Code Camp

The sixth Alabama Code Camp is coming up February 23rd, 2008. Registration is now open, as is the call for speakers. Many, including myself have submitted, you can see them by going to the Alabama Code Camp site and clicking on the speakers link. The list of speakers is very impressive, no less than eight MVPs, and at least two authors. I’m humbled to be amongst such distinguished company!

Here’s the synopsis for my two sessions, in case you are curious:

Introduction to SQL Server Integration Services

Whether you are creating a full blown data warehouse, doing a data conversion from an old system to a new one, or integrating applications together SQL Server Integration Services can help. Get an overview of this powerful tool built into SQL Server.

The Developer Experience

Learn about tips and tricks to enhance your experience as a developer both in the physical world and the virtual world. See hardware that can make your life easier, software additions for Windows and Visual Studio, even how just a few tweaks in the Visual Studio options can make your experience as a developer more pleasant and productive.

This is shaping up to be an impressive code camp, so don’t hesitate and get registered today!

Getting Table Space Usage in SQL Server 2005

I had the need to determine how much space was being used by each of my tables in a certain schema. I found various solutions on the web, most of which involved the built in system stored procedure sp_spaceused. However none of them produced the output quite like I wanted it. Most worked on a single table, or else produced the output in multiple returns. So I did what comes naturally and cobbled together my own solution.It’s fairly straight forward. I create a temp table to hold the output, then create a cursor to cycle through all the table names in the schema set in the variable @schemaname. I set it to the most common one, dbo, but you may wish to change it. Next I cycle through each of the tables using a cursor and for each one call the sp_spaceused and insert the output as a new row in my temp table. Finally I select the results back to the user and drop the temp table to clean up.

You may note I check to see if the temp table exists as the very first thing, then if it is I drop it. Then as the last step I drop it. Call it paranoid programming, if something crashes part way through the routine I wanted the code to be “self repairing”, i.e. I didn’t want to have to remember to run the drop table statement just to start the procedure all over.

Also, you may have read somewhere that cursors in SQL Server are “evil”. Well, as a general rule you should avoid them in favor of set based coding. However there are times, such as this when I need to call a stored procedure for each row in the cursor, that a cursor will solve the issue nicely. Additionally this isn’t code I’m going to be running 500 times a day on a production server, but instead just occasionally as a system monitor type of utility.

You may notice that most of the columns are all returned as strings (varchars). That’s because the stored procedure returns them in that way. I may go back later and modify this to convert them to numbers so I can do some math, but we’ll see if that need arises.

Here’s the code, use it and abuse it, modify it and enjoy it!

/******************************************************************************

**    File: “GetTableSpaceUseage.sql”

**    Name: Get Table Space Useage for a specific schema

**    Auth: Robert C. Cain

**    Date: 01/27/2008

**

**    Desc: Calls the sp_spaceused proc for each table in a schema and returns

**        the Table Name, Number of Rows, and space used for each table.

**

**    Called by:

**     n/a – As needed

**

**    Input Parameters:

**     In the code check the value of @schemaname, if you need it for a

**     schema other than dbo be sure to change it.

**

**    Output Parameters:

**     NA

**

*******************************************************************************

**        Change History

*******************************************************************************

**    Date:            Author:                Description:

**    ——–        ——–            —————————————

**

*******************************************************************************/

 

/*—————————————————————————-*/

/* Drop the temp table if it’s there from a previous run                     */

/*—————————————————————————-*/

if object_id(N‘tempdb..[#TableSizes]’) is not null

  drop table #TableSizes ;

go

 

/*—————————————————————————-*/

/* Create the temp table                                                     */

/*—————————————————————————-*/

create table #TableSizes

  (

    [Table Name] nvarchar(128)   /* Name of the table */

  , [Number of Rows] char(11)    /* Number of rows existing in the table. */

  , [Reserved Space] varchar(18) /* Reserved space for table. */

  , [Data Space] varchar(18)    /* Amount of space used by data in table. */

  , [Index Size] varchar(18)    /* Amount of space used by indexes in table. */

  , [Unused Space] varchar(18)   /* Amount of space reserved but not used. */

  ) ;

go

 

/*—————————————————————————-*/

/* Load the temp table                                                        */

/*—————————————————————————-*/

declare @schemaname varchar(256) ;

— Make sure to set next line to the Schema name you want!

set @schemaname = ‘dbo’ ;

 

— Create a cursor to cycle through the names of each table in the schema

declare curSchemaTable cursor

  for select sys.schemas.name + ‘.’ + sys.objects.name

      from    sys.objects

            , sys.schemas

      where   object_id > 100

              and sys.schemas.name = @schemaname

              /* For a specific table uncomment next line and supply name */

            –and sys.objects.name = ‘specific-table-name-here’    

              and type_desc = ‘USER_TABLE’

              and sys.objects.schema_id = sys.schemas.schema_id ;

 

open curSchemaTable ;

declare @name varchar(256) ;  /* This holds the name of the current table*/

 

— Now loop thru the cursor, calling the sp_spaceused for each table

fetch curSchemaTable into @name ;

while ( @@FETCH_STATUS = 0 )

  begin    

    insert into #TableSizes

            exec sp_spaceused @objname = @name ;       

    fetch curSchemaTable into @name ;   

  end

 

— Important to both close and deallocate!

close curSchemaTable ;     

deallocate curSchemaTable ;

 

/*—————————————————————————-*/

/* Feed the results back                                                     */

/*—————————————————————————-*/

select [Table Name]

      , [Number of Rows]

      , [Reserved Space]

      , [Data Space]

      , [Index Size]

      , [Unused Space]

from    [#TableSizes]

order by [Table Name] ;

 

/*—————————————————————————-*/

/* Remove the temp table                                                     */

/*—————————————————————————-*/

drop table #TableSizes ;