Category: Uncategorized

Why is it so hard to move data from Excel to SQL Server?

~ chriseyre2000 ~ Leave a comment

Business users love Excel.  It is there prefered format of data.

If you can get report data into Excel then they rarely require other reports.

The problem comes when you want to take data the other way.

I have an application that has it’s business logic implemented as a set of stored procedures.  It is a data transformation application so this is the best place to do this.

We need to source certain data from Excel.  OPENDATASOURCE is the recommended solution as follows:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:Financeaccount.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

Aside from the drive mapping problems that you can get when the data is not on a local drive on the server there are the following issues:

Data types are implied from the data.  When you let Excel guess it has a better than 50% chance of being wrong.

If there are empty columns for a few rows it assumes the entire row is NULL. 

These two can be complete showstoppers.

The only solution was to move to feeding the data from access… 

Code Sample for Datasets and DataGridViews

~ chriseyre2000 ~ Leave a comment
        public Form1()
        {
            InitializeComponent();
            SqlConnection cnt = new SqlConnection(GetConnectionString());

            cnt.Open();
            SqlCommand cmd = cnt.CreateCommand();
            cmd.CommandText = “select * from Orders; select * from [Order Details]”;
            northwind.Load( cmd.ExecuteReader(), LoadOption.OverwriteChanges, “Orders”,”Order Details”);
            cnt.Close();
        }

        static private string GetConnectionString()
        {
            // To avoid storing the connection string in your code,
            // you can retrieve it from a configuration file, using the
            // System.Configuration.ConfigurationSettings.AppSettings property
            return “Data Source=(local);Initial Catalog=Northwind;”
                + “Integrated Security=SSPI;”;
        }

        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            string s = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();
            orderDetailsBindingSource.Filter = “OrderId=” + s;
        }

Software Requirement Specifications

~ chriseyre2000 ~ Leave a comment

I have been working on a number of SRS’ lately. These do help in the developement of the application. At least you have a formal definition of what the project is trying to do.

A SRS helps in the development of the test plan.

Recently we have found one slight flaw. If the customer does not read the SRS it’s value decreases significantly. If the customer is not interested in reading a large formal document then a few small technical memos would have a greater chance to be read.

SQL Server Error handling problems part 2

~ chriseyre2000 ~ Leave a comment

SQL Server 2000 has even more error handling problems than I blogged about before.

It would appear that error handling has not been thought through at all.

Take the RAISERROR command as an example. This appears to be a very elegant means of raising errors to the user.  You only need to use sp_addmessage to register a new message. The problem is that the messages are server global.

This means that if you server is ever to host a third party application you cannot safely define your own messages for fear of a third party app stamping all over them.  This should be scoped at a database level, providing at least some control.

The more I look at sql server error handling the more depressed I get. 

NDoc for VS2005

~ chriseyre2000 ~ Leave a comment

One of the key rights of an open source licence (and therefore a key property of an open source project) is the right to fork. If you think that a project has stagnated or been terminated then you have the right to pick up the pieces and take it in a direction of your own – provided that you have the time, inclination and ability (or can hire someone to do that for you). This is just not available in the closed source world.

NDoc2 died due to lack of resources (and due to the vapourware that is Sandcastle). However NDoc2005 lives on. This is only a beta version. However beta in the open source world means a lot more than beta in the closed source world – especially for a development tool.

This seems to work just fine as is. It creates MSDN style documentation from C# 2.0 source. I had it working without reading the manual in about five mins. This is in comparison to Sandcastle (the microsoft equivalent) where I have yet to generate a single file. This may get my project sufficient documentation to last until Sandcastle catches up (in a year or so).

Windows Genuine Advantage – for who?

~ chriseyre2000 ~ Leave a comment

Microsofts Genuine Advantage program will check whether you have a registered copy of Windows.

This is a bit of a liberty – don’t they trust me? I have a fully licenced version of XP Pro on this machine.

OK I don’t mind performing this once to download a particular piece of software. However once I have demonstrated to Microsoft that my machine is genuine that should be it. However Microsoft have a different opinion. Every time that you needto install software that is protected by WGA you need to go through the same hoops. How can my machine that is already a Genuine Windows machine stop being a genuine windows machine? Can they decide that it has been revoked?

Why you can't do without the transaction log…

~ chriseyre2000 ~ Leave a comment

I have been working with SQL Server 2000 for a while now.  I’m no DBA…

Today I encountered a problem that the investigation of which led me to a greater understanding of SQL Server.

We have a reasonably large database that is recreated everyday as a snapshot of a larger system. The transaction log was getting rather large. Given that the database is set up to use the Simple recovery model and we were using no explicit transactions why is it getting so large?

Why can’t we switch off the transaction log?

It turns out that it is almost impossible to write directly to the database – bulk inserts only.  A majority of data access goes directly into the transaction log  which is then applied to the database.   In order  to clear down the transaction  log you need to backup the transaction log or pretend that you have.

This can be performed with the command BACKUP LOG {database_name} NO_LOG.

Databases can be set up with the Auto-Shrink option (but it is not a good idea to do so).  This will allow the transaction log to shrink itself.  The problem is that you have no control over when this happens and it will kill performance when it occours – it will pick the worst possible time to do it.  The best option is to pick a known quiet time and backup the log yourself.

For more details see “Shrinking the Transaction Log” in Books Online.

In addition we found that all data modification commands are wrapped in an automatic transaction.  This meant that our transaction log included a 21 million row insert as a single transaction…

Sandcastle – Microsofts answer to NDoc

~ chriseyre2000 ~ Leave a comment

Sandcastle is now available as a CTP.

This is Microsofts answer to NDoc. They have been so sucessful in announcing this product that they have killed off NDoc.

Now if you got a product that was used to generate documentation you would expect it to be accompanied with at least the minimal amount of documentation. In this case not a sausage.

The normal practice for this is to eat your own dogfood and use it’s own codebase as an example.