TD6.0 Connect to Compact SQL db via OLEDB

[Techdrill]

Dear All,
We are considering MSSql Compact database to alternative for small database we use in our app.
Currently we are experiencing problems connecting to MSSql Compact database.
The error we get is:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Connection to normal MS Sql server (Express) via OLEDB works. Connection from C# .NET application with the same connection
string to MSSql Compact on the same machine works.
Sample attached, please put MyDb.sdf in C:\Temp
You can get MS Sql server 3.5 compact from
http://www.microsoft.com/en-gb/download ... px?id=5783
Currently testing under TD6.0 SP2.
Any help would be highly appreciated.

Best regards.

[Jeff Luther]

"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\\Temp\\MyDb.sdf;"

Where did you find the Provider=<> string to use?

[Techdrill]

Hi,
on:
http://www.connectionstrings.com/provid ... -oledb-3-5

[Techdrill]

Some more info:
It seems some kind problem in connection with Unicode inside your implementation.
If we convert connection string to ANSI it seems to work (we will still do some more testing
but at least we do not get an error when creating a session):

Code: Select all

Set sTemp = 'Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\\Temp\\MyDb.sdf;'
Call SalStrToMultiByte(sTemp, sTempANSI, ENC_ANSI )
Call SqlCreateSession( S1, sTempANSI)

So it is a bug.

We got the idea, when we saw that in old versions (TD3.0) this works.

Best regards

[Jeff Luther]

So it is a bug.

I don't think so. TD as of v5.1 is in Unicode, all strings in TD are Unicode/double-byte and it sounds like the DB doesn't support Unicode? (At least the DB client doesn't appear to.) I talked with our router developer about this and confirmed that the TD internally is passing the string in Unicode, as-is in Unicode with no conversion.

And we don't think that the fact that the DB doesn't support a Unicode provider string makes this a 'bug.'

2 suggestions:
1 - find out if they have a Unicode-enabled version of that CE DB and upgrade to that.
or...
2 - do what you are now doing: Convert the Provider string to ANSI (using, I guess, SalStrToMultiByte).

Be aware, though, that DB string-type columns also might only support ANSI. So your Unicode vs. ANSI incompatibility issue might not be over yet.

[UVS]

I may be wrong, but isn't the CE version of MS SQL Server for Mobile devices? Whereas the Express Edition of SQL Server should be able to connect to it the same as any other version of SQL Server?

MartinD

[Techdrill]

Dear All,
This thing is still bothering us...

The error we get in TD6.0 is:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Connection with TD3.0 connection works.
Samples attached, please put TEST1.sdf in C:\Temp
You can get MS Sql server 3.5 compact from
http://www.microsoft.com/en-gb/download ... px?id=5783

Any help would be highly appreciated.