Wednesday 22 June 2011

Bulk copy, full text and a severe error

I have a program that is using SqlBulkCopy to batch update data in a SQL Server Express 2008 database. Everything was working fine until, for no apparent reason, the bulk inserts failed with the following exception:

“A severe error occurred on the current command. The results, if any, should be discarded.”

After much hair pulling the cause of the error turned out to be the addition of a full text index on one of the fields being updated. Removing the full text index stopped the exception from being thrown. That’s all well and good but I needed a full text index.

The index had been defined something like this:

EXEC sp_fulltext_database 'enable'
GO

CREATE FULLTEXT CATALOG MyCatalog
GO

CREATE FULLTEXT INDEX ON dbo.MyTable
(
    MyColumn
    Language 0X0
)

KEY INDEX PK_MyTable ON MyCatalog WITH CHANGE_TRACKING AUTO

After some experimentation the problem seemed to be with the CHANGE_TRACKING option. By setting it to OFF the bulk copy worked fine but failed with either AUTO or MANUAL.* For my purposes this was acceptable because the data was fairly static and updated infrequently. I was left with having to ensure the index was rebuilt or populated appropriately as a separate process.

 

References

* Full-Text Index Population - http://msdn.microsoft.com/en-us/library/ms142575.aspx

Monday 20 June 2011

Error installing SQL Server Express 2008

I ran in to an issue when trying to install SQL Server Express 2008 with Advanced Services. The installation file was a single executable (SQLEXPRADV_x86_ENU.exe)  but when I ran it I got the following error in a dialog box:

“SQL Server Setup has encountered the following error:

Invoke or BeginInvoke cannot be called on a control until the window handle has been created..”

The solution to the problem is:

  1. SQLEXPRADV_x86_ENU.exe is a self-extracting Zip file. Use WinZip to extract it to a local folder.
  2. Open a command window and navigate to the folder containing the extracted setup files.
  3. Run setup.exe from the command prompt.

That’s it.

Note that if you want to use Add or Remove Programs feature to add new features you can use the extracted files as the ‘installation media’.

Sunday 12 June 2011

A few useful exception types

Sometimes it’s useful to throw exceptions from your code, for example if an incoming method argument is incorrect for some reason. Throwing Exception isn’t very specific so what should we throw and when? Here’s a quick aide-mémoire for a few exception types I use:

Exception type When to use it
ArgumentException The exception that is thrown when one of the arguments provided to a method is not valid.
InvalidOperationException The exception that is thrown when a method call is invalid for the object's current state.
FormatException The exception that is thrown when the format of an argument does not meet the parameter specifications of the invoked method.
NotImplementedException The exception that is thrown when a requested method or operation is not implemented.

References

http://msdn.microsoft.com/en-us/library/system.systemexception.aspx

Monday 6 June 2011

Saving changes is not permitted

I’ve run into this problem a couple of times now and it’s really annoying every time it happens. I created a table in SQL Express using the SQL Management Studio and saved it. I then tried adding some new columns to the table but when I saved it again the following error appeared:

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

The dialog is really helpful:

Untitled

The solution is as follows:

  1. Go to Tools > Options…
  2. Select the Designers node.
  3. Uncheck the Prevent saving changes that require table re-creation option.

 

Untitled

Job done.