Batch execute SQL files in a directory

Having to excecute hundreds of .sql files in an environment without port 1433 open led me to this nugget of a Usenet post: How to execute multi-file.

Here is the gist of it, the command line for executing all those .sql files with osql:

FOR %i IN (*.sql) DO OSQL -n -U sa -P sapassword -d databasename -i %i

I am sure I could have merged them all into one file or made some sort of batch vbs, but this did the trick nicely.

Posted: Monday, July 25, 2005 5:21:07 AM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
SQL Server

Whidbey Beta 1 to Beta 2 Migration with Typed Datasets

Today we are migrating my project from Whidbey Beta1 to Beta 2 (if you know why this is happening today, go ahead and laugh with us), and our biggest hurdle so far is Typed Datasets.

We used dozens of XSD schemas to create typed datasets. Of course, Beta 2 no longer gives a menu option for creating a typed dataset directly from a schema (see this post in the Fedback Center for Visual Studio). The prospect of converting to the Dataset type of schema was daunting, and moving to the command line for xsd.exe was not going over so well either.

We did discover, however, that you can still generate the dataset using the IDE. In the properties of the XSD file, you can set the custom tool to MSDataSetGenerator. Then you can right-click the XSD file and choose Run Custom Tool. The IDE generates the code for the dataset. Whew!

Posted: Friday, July 01, 2005 4:25:55 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
.Net 2.0

Select Distinct in a DataTable (The Whidbey Version)

In my last post I described a “Select Distinct“ function for a datatable. This is a whole lot easier using Whidbey. You can get a distinct table based on a column in the DefaultView of the datatable:

DataTable d = dataSetName.dataTableName.DefaultView.ToTable(true, new string[] { "ColumnName" });

Posted: Wednesday, May 25, 2005 7:06:56 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
.Net 2.0 | ADO.Net

Select Distinct in a DataTable

I needed to perform a “Select Distinct“ on a datatable, not in the database, for binding to a listbox. Found a good solution here. Modified it according to one of the comments to use a hash table so sort order won't matter. Here is my version of the function:

private DataTable SelectDistinct(DataTable sourceTable, string sourceColumn)
{
    DataTable result = null;
    try
    {
        result = new DataTable();
        result.Columns.Add(sourceColumn, sourceTable.Columns[sourceColumn].DataType);
        Hashtable ht = new Hashtable();
        foreach (DataRow dr in sourceTable.Rows)
        {
            if (!ht.ContainsKey(dr[sourceColumn]))
            {
                ht.Add(dr[sourceColumn], null);
                DataRow newRow = result.NewRow();
                newRow[sourceColumn] = dr[sourceColumn];
                result.Rows.Add(newRow);
            }
        }
        return result;
    }
    catch (System.Exception ex)
    {
        ExceptionManager.Publish(ex);
        return null;
    }
    finally
    {
        if (result != null)
            result.Dispose();
    }
}
Posted: Wednesday, May 25, 2005 6:26:46 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
ADO.Net

Getting Started with the User Interface Application Block

There is an excellent “Hello World” article  for getting started with the UIAB at CodeProject by Roy Clemmons. If you have checked the docs that come with the block, you can appreciate a simplistic example. The block is a powerful piece of code, but you have to get started before harnessing the monster. I had a very difficult time distilling the example code from the block down to something simple that worked. The article from CodeProject did the trick.
Posted: Tuesday, April 12, 2005 4:51:36 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
.Net 2.0

Virtual PC SourceSafe Network Problem

We have been using Virtual PCs to host a separate development environment. Some of the team members have been having sporadic network issues with SourceSafe on the Virtual PCs. They were unable to get all of the projects involved down to the Virtual PC without getting a “Network Not Found” error. Eventually they would get all the files, but it was clearly not the best situation.

The Virtual PC image was sysprepped before distributing it, to avoid network problems. Unfortunately, that is not completely the case. Sysprep does reset the MAC address on the Virtual PC, but it apparently happens when sysprep runs, not when the user is re-setting up the PC. After distributing the image, the subsequent users still had the same MAC address. This was causing network problems when more than one person were using the Virtual PCs at the same time. The problem manifested itself mostly with SourceSafe, especially when there was a large change and multiple users were getting all the latest files at the same time.

At least the solution was simple once we realized that the MAC address was the issue (thanks to an astute network admin who had noticed some problems with a particular MAC address on one of the switches). In the .vpc file (it's XML), there is an entry for ethernet address. It contains the MAC address the Virtual PC uses. If you remove the data from the tag, leaving the empty tags, Virtual PC will auto-generate a new MAC address the next time it starts up. Since that change the “Network not Found” error has disappeared.

Posted: Wednesday, February 23, 2005 1:54:57 AM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
Strange Problems

Data Access Application Block v2 Bug

Mark Brown posted his fix for a bug in the FillDataSet method of the SqlHelper in the Data Access Application Block. The problem occurs when trying to use the FillDataSet method to fill a typed dataset that contains more than two tables. It works great for the first two, not at all for the subsequent tables.

I gave Marks\'s fix a try, but it still didn't fix the problem for me. I did more digging and found a slightly different fix straight from the authors of the DAAB on the GotDotNet workspace for the DAAB. The fix is in the bug section (direct links don't seem to work) This cleared up my problem right away. Of course, the bug is fixed in version 3+ of the DAAB. If you are like me, the MSDN site has been my source for the code for the application blocks, and of course there is only 2.0 on the site.

I did not realize they were on version 3 of the DAAB. The authors have added support for an abastract factory to make the SqlHelper ADO.Net provider independant. I don't know about the rest of you, but the only projects I have ever had to change the database provider on have been Access upgrades. I have never had to move a database back end, say from SqlServer to Oracle. Is it more common than I realize?

Posted: Thursday, January 27, 2005 4:28:15 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
.Net 2.0 | ADO.Net

Reloading for the New Year

After a harried development and delivery of a good sized Software Spec document to a client before the holidays, I have tried again to load XP SP2 on my laptop (hp compaq nx5000) without success. Despite updating all the drivers and the ROM before loading SP2, I still get blue screens after the profile loads, but not every time. The plan now is to wipe it out and start from scratch.

Posted: Wednesday, January 05, 2005 7:58:06 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
Strange Problems