Horizontal Partitioning

We’ve got a moderately large database here – 80gb, 5 tables and 500m rows – and we had problems updating it. Following a bout of Horizontal Partitioning we’ve now got 130 tables and those rows distributed evenly across them. However when a check constraint was updated today SQL Server 2000 wouldn’t allow data to import and we got the error message:

UNION ALL view ‘Classifier_z’ is not updatable because a partitioning column was not found.

In the back of my mind i’d remembered about trusted and untrusted check constraints and after a bit of Googling (I did use Google so I can use it as a verb ;)) I found this page which displays all the currently untrusted constraints in your table, and further allows you to set them to trusted. Here’s the code too, just in case that target page disappears.

To display untrusted constraints:
[sql]
SELECT ctu.TABLE_SCHEMA, ctu.TABLE_NAME, cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS ctu
ON ctu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE objectproperty( object_id(quotename(cc.CONSTRAINT_NAME)), ‘CnstIsNotTrusted’ ) = 1
[/sql]

To make all constraints trusted:
[sql]
DECLARE @tablename nvarchar(300), @constraint nvarchar(300)
DECLARE @constraints cursor
SET @constraints = cursor fast_forward FOR
SELECT quotename(ctu.TABLE_SCHEMA) + N’.’ + quotename(ctu.TABLE_NAME), quotename(cc.CONSTRAINT_NAME)
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS ctu
ON ctu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE objectproperty( object_id(quotename(cc.CONSTRAINT_NAME)), ‘CnstIsNotTrusted’ ) = 1
OPEN @constraints
WHILE (1=1)
BEGIN
FETCH @constraints INTO @tablename, @constraint
IF @@fetch_status < 0 BREAK
EXEC('ALTER TABLE ' + @tablename + ' WITH CHECK CHECK constraint ' + @constraint)
END
[/sql]

Sorting a DataTable

Normally if you were trying to sort a DataTable you would do see something like

[csharp]
DataTable table = retrieveTable();
DataView view = new DataView(table);
view.Sort = “Name”;
datagrid1.DataSource = view;
datagrid1.DataBind();
[/csharp]

Doing this works fine for UI cases, but if you need to get access to the sorted table you’ll want to do this.

[csharp]
DataTable unsortedTable = retrieveTable();
DataTable sortedTable = new DataTable();
foreach (DataRow row in unsortedTable.Select(string.Empty, “Name”)) {
sortedTable.ImportRow(row);
}
[/csharp]

And then you can mess with your sortedTable object.

Getting data out of a locked MS Access MDE

Problem: I have an access 97 MDE file (compiled MDB) which uses an external data source (.DAT ?) on a network via linked tables. It has user security on it and I need to get the data out into SQL server. Because of the user security the upsizing wizard with Access 2000 cannot convert the database before upsizing it.

Solution: If you have a login to the access database then open the MDE with shift held down, or press F11 once you’re in the MDE to bring up the table/query lists.

Select the table you want to export, hit File, Save As/Export and choose ODBC. Create an ODBC connection to your SQL server and export the data. This copies the data to SQL server.

However, you may get ‘overflow’ messages back with no detail as to what went wrong. If this happens export the same table to a temporary Excel spreadsheet. The export process this time will create a temp table called TABLENAME_export_errors. Fix those errors in your table, and you can export to ODBC again.

SQL Server Reporting visibility

So it’s late in the day and i’ve been working on the darned SQL Server Reporting for my latest project most of the day, but i’m sure that i’ve found a bug, rather than simply got confused.

I’ve got a report with a bunch of controls whose visibility i’d like to control from a parameter. Not a difficult problem you’d assume, except that it appears that the visibility logic in the system is the wrong way around for expressions.

Example. The following makes the column invisible. Change the True to False and it appears. Go figure.

SQL Server Reporting Services with multi-value parameters

Sometimes you need to pass multiple parameters into SQL Server Reporting Service.

Unfortunately Microsoft didn’t include this functionality in the first release, although they’re rumoured to be looking into this. This means that if you want to include a bunch of parameters and pass them through to an SQL ‘IN’ statement it’s a little awkward, but not impossible.

You pass through all the items as a delimited string, then in your stored procedure convert the delimited list to a table using a user-defined function. Details of how to do this part are given in this article from 4guysfromrolla.

In SQL Server Reporting define a report parameter and pass this through to your Stored Procedure through the data source mapping.

When you view the report you’ll see a textbox letting your user enter a value. They can enter a single value now, or multiple delimited values. Neat. Or you could automate the process and invoke reporting service from your app passing the parameters via the URL.