When Written: Jan 2007
I have just finished a web application for a client and one of the final stages of this meant giving them a simple way of updating the database on the web server with the latest information. This particular web application allowed users to search in a variety of ways the client’s stock list and request a quote for a particular item. The database for the web server was held in a SQL Server 2005 Express database and consisted of approximately four thousand records. I needed a way of updating this data that would not cause any downtime on the web but that was easy to implement and preferably did not involve VPNs or ‘knocking holes’ in the firewalls. Express versions of SQL server 2005 do not come with the DTS tool that users of other versions would be familiar with which allows easy transferring of data.
An other possibility that I have used in the past was the command line bcp utility but this can be a little tricky to get set up, with its multiple switches. But both of these solutions may still have resulted in their being downtime on the web site as the data was transferred and we particularly wanted to avoid this. I did at one point consider having two databases and switching between a ‘live’ one and the one that was going to be updated, but after some tests I found that the ‘BULK INSERT’ command with its speed of update, seemed to offer what was needed. The idea was to provide two protected web pages, one of which allows the user to upload a tab delimited text file from their local machine to the web server. This file would be produced by their in house database system. The other web page basically runs the following command on the data source:
“BULK INSERT mytable FROM ‘myfile.txt’ WITH ( FIRSTROW = 2, FIELDTERMINATOR = ‘\t’, ROWTERMINATOR = ‘\n’)”
This is the syntax for a tab delimited file ignoring the first row as it contained column names. The command takes only seconds to import the thousands of records and so there is no interruption to the web application. Obviously you need to put this between a BEGIN TRANS and a COMMIT TRANS command so that should it fail halfway through you can ‘roll back’ the database to the old dataset. This solution seems to work extremely well, and the site went live just before the Christmas break.
Article by: Mark Newton
Published in: Mark Newton