CRecordset takes 10 seconds to open

Hi All

I'm new to sql server. I have built simple database apps using MFC CRecordset over MS Access. I'm tying to learn about SQL server by building a simple app using MFC CRecordset in Visual Studio 2005.

I have multiple CRecordset classes within my app. When I developed the app over MS Access I read somewhere that it was better to have a single database object that is shared by multiple recordsets. So I ended up with something like this -

Code Snippet

// In the class header - 2 CRecordset derived classes

CMyRecordSet m_MyRecordSet; // Connects to the database using

// its "GetDefaultConnect" string

CAnotherSet * m_pAnotherSet;

// Within a "CreateRecordSets" method

if (!m_MyRecordSet.IsOpen()) m_MyRecordSet.Open();

// This works fine

m_pAnotherSet = new CAnotherSet (m_MyRecordSet.m_pDatabase);

m_pAnotherSet->Open();

// This open takes 10s to execute,

// there are 10 rows of data in the table

I have about 6 recordsets that I create in this way, there is a tiny amount of data in the database. The open for the next record sets return immediately.

The same app over MS Access works fine - no delays. SQLServer Express and Access are both running on my development PC.

Can anyone tell me why this call takes so long and whether there is a way of avoiding it?

Subsequent calls to the database return immediately.

Thanks

Alec

SQL Server 2005 Express 9.00.3042.00

Microsoft SQL Server Management Studio Express 9.00.2047.00

Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

I thought that I had found the cause of this but I've only found out how to reproduce it.

If I change the character set to unicode in the Visual Studio 2005 project settings the problem goes away. (This is an MFC SDI form view application.) I had originally creating my sql database tables by exporting them from Access and this had created nvarchars. I changed these to varchars but still I get the 10s delay when the project is built with "No Character set" selected. I'm not sure what the side affects of leaving unicode selected may be so I don't really want to change this without understanding the underlying cause.

In the CRecorset code generated by visual studio it says

// The string types below (if present) reflect the actual data type of the

// database field - CStringA for ANSI datatypes and CStringW for Unicode

// datatypes. This is to prevent the ODBC driver from performing potentially

// unnecessary conversions. If you wish, you may change these members to

// CString types and the ODBC driver will perform all necessary conversions.

// (Note: You must use an ODBC driver version that is version 3.5 or greater

// to support both Unicode and these conversions).

My ODBC driver version is 2000.85.1117.00 - am I looking at bthe wrong version number? (this is from the ODBC create new datasource window).

I have varchars being loaded in CString so is there any conversion necessary? (even if there was this convserion should not take so long).

Can anyone explain the delay related to selected charatcter set?

Thanks

Alec