Al’s Blog

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 [...]

For those of you who write SQL, Redgate (of ants, sql dependancy tracker, and sql compare fame) have released a free download SQL Prompt which provides Intellisense-style prompting). It works in Query Analyzer, Enterprise Manager, etc.

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 [...]

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 [...]

This is a great stored procedure you can use to generate SQL insert statements from your data. Kind of like how my SQL Data Export did some years ago, but this runs from inside SQL so is potentially a bit more portable.

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 [...]

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 [...]

Another example of using OPENXML to get data into SQL Server from ADO/ADO.NET. This one will fail when the Xml chunk you pass in is bigger than 8000 characters (ntext limitation). However you can pass an XmlDocument stream object to ADO and do that instead and avoid this limitation. MSXML3 or newer needed.
Here’s the [...]

Loading several DataTables into a DataSet using DataRelations when there are invalid constraints causes the error message “Failed to enable constraints. One or more rows contain values….”.
Solution is documented here
[csharp]
foreach (DataTable dt in dataset.Tables)
{
if (dt.HasErrors)
{
Console.WriteLine(”ERRORS IN : ” + dt.TableName);
foreach (DataRow dr in dt.GetErrors())
{
Console.WriteLine(dr.RowError);
}
}
}
[/csharp]