Connecting to an SQL Server Compact 3.5 database

[Mike C]

Hi everyone. I was wondering if anyone has had any experience connecting to an SQL Server Compact 3.5 database using the LabVIEW database connectivity toolkits. I cannot figure out a way to created a valid connection string or if I let the "open connection to db" vi prompt me for connection info I cannot figure how to configure it.

Thanks in advance for all the help.

Michael

[Yair]

Does the compact edition not use the UDL configuration dialog like other DBs in Windows?

[Mike C]

QUOTE (Yair @ Sep 25 2008, 12:53 PM)

Does the compact edition not use the UDL configuration dialog like other DBs in Windows?

If it does I cannot figure out how to configure it. It does not behave like a normal SQL server or like an Access db. That's why I am having problems.

[Yair]

What happens if you create a UDL file and try setting it as the provider?

Also, did you try searching Google or the MSDN?

[Shaun Hayward]

I havent looked into it yet (due to time constraints) but SQL Compact Edition looks interesting.

From what I have read so far (which isn't so much) it seems that it embeds a database within a particular applicatio. Therefore I would wonder whether you need to do some calls to its DLL / .Net trickery instead of a conventional UDL approach. The MSDN pages are suitably cryptic and basically talk you though using a Wizard in Visual Studio, which doesnt help so much.

[Mike C]

QUOTE (Yair @ Sep 25 2008, 01:04 PM)

What happens if you create a UDL file and try setting it as the provider?

Also, did you try searching Google or the MSDN?

Yiar, thanks for the suggestions. I have been searching the web for a few days with no result. There is lots of examples on how to do it from within Visual Studio but that does not help. Creating my own udl had no result ( I get an error message:"NI_Database_API.lvlib:Conn getDataToModify.vi:1"<ERR>Object 0x000000 is not valid.) I tried a number of different combinations with the same error message. I have been using the normal SQL server for sometime now but this is very different.

My guess is that I need to be calling some other dll before I can connect to the database file (the sql server compact database is contained in a single file) but I don't know how or which one. I was hoping someone else here may have used it and had some tips.

Michael

QUOTE (Shaun Hayward @ Sep 25 2008, 02:11 PM)

I havent looked into it yet (due to time constraints) but SQL Compact Edition looks interesting.

From what I have read so far (which isn't so much) it seems that it embeds a database within a particular applicatio. Therefore I would wonder whether you need to do some calls to its DLL / .Net trickery instead of a conventional UDL approach. The MSDN pages are suitably cryptic and basically talk you though using a Wizard in Visual Studio, which doesnt help so much.

Shaun, thanks for the responce. That's exactly what I gathered by reading through various sites. I am afraid that you are probably correct in that I would need to make some dll calls. SQL server compact is a neat idea but it seems complicated to get working.

Michael

[jcarmody]

QUOTE (Mike C @ Sep 25 2008, 09:28 PM)

SQL server compact is a neat idea but it seems complicated to get working.

Michael,

I'm curious about why you're interested in this product. Do you have a specific need or is this an exercise to learn a new technology? I'm all for learning new things :thumbup: (except, perhaps MS isn't my favorite source :thumbdown: )

I'd recommend SQLite for a small database without onerous licensing that you can include with your application. There's a http://decibel.ni.com/content/docs/DOC-1254' target="_blank">toolkit on ni.com that I've had some success with.

Anyway, good luck with your project. Please forgive me if I'm way off base.

jm

[S59X]

I tried the same thing yesterday. I was able to connect to the database using the following connection string that I found googling the internet:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=path\database.sdf

You can create the database with Microsoft SQL Server Management Studio Express. I just tried a few SQL statemens and it seemed to work. I don't have a project in mind. It was just an attempt from my curiosity.

[Shaun Hayward]

QUOTE (jcarmody @ Sep 26 2008, 05:32 AM)

Michael,

I'm curious about why you're interested in this product. Do you have a specific need or is this an exercise to learn a new technology? I'm all for learning new things :thumbup: (except, perhaps MS isn't my favorite source :thumbdown: )

I'd recommend SQLite for a small database without onerous licensing that you can include with your application. There's a http://decibel.ni.com/content/docs/DOC-1254' target="_blank">toolkit on ni.com that I've had some success with.

Anyway, good luck with your project. Please forgive me if I'm way off base.

jm

Jim,

Thanks for the hint - I hadn't come across SQLite before - it looks like its doing much of the same kind of thing

My only question is that the link you posted does not seem to have anywhere to actually download the toolkit - am I missing something?

[Mike C]

QUOTE (rbogomir @ Sep 26 2008, 05:21 AM)

I tried the same thing yesterday. I was able to connect to the database using the following connection string that I found googling the internet:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=path\database.sdf

You can create the database with Microsoft SQL Server Management Studio Express. I just tried a few SQL statemens and it seemed to work. I don't have a project in mind. It was just an attempt from my curiosity.

I have tried the same thing and it did not work. I am actually using the sql server compact not mobile so the string I tried was a bit different: Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\mydb1.sdf

On the up side I get a different error message now: Code: -2147467259, NI_Database_API.lvlib:DB Tools Open Connec (String).vi->test db.vi<ERR>Exception occured in Microsoft SQL Server Compact OLE DB Provider: in NI_Database_API.lvlib:DB Tools Open Connec (String).vi->test db.vi

When you created the database did you give it a password? Is there anything else I should be aware of?

QUOTE (Shaun Hayward @ Sep 26 2008, 08:09 AM)

Jim,

Thanks for the hint - I hadn't come across SQLite before - it looks like its doing much of the same kind of thing

My only question is that the link you posted does not seem to have anywhere to actually download the toolkit - am I missing something?

Shaun, I was just looking at that myself. I found the vi's here. I have not had a chance to try it but it looks interesting

Michael

QUOTE (jcarmody @ Sep 26 2008, 04:32 AM)

Michael,

I'm curious about why you're interested in this product. Do you have a specific need or is this an exercise to learn a new technology? I'm all for learning new things :thumbup: (except, perhaps MS isn't my favorite source :thumbdown: )

I'd recommend SQLite for a small database without onerous licensing that you can include with your application. There's a

http://decibel.ni.com/content/docs/DOC-1254'

target="_blank">toolkit

on ni.com that I've had some success with.

Anyway, good luck with your project. Please forgive me if I'm way off base.

jm

I writing an application that needs the database to be contained in a single file. My first choice was Access but I've recently heard of SQL Server Compact and I thought it may be a much better alternative. I have worked with SQL Server before so I figured it would be an easy transition. If I could only figure out how to connect to the database!

I have no reason to want to use MS products. I will give SQLite a try, thanks for the suggestion.

[S59X]

QUOTE (Mike C @ Sep 26 2008, 03:29 PM)

I have tried the same thing and it did not work. I am actually using the sql server compact not mobile so the string I tried was a bit different: Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\mydb1.sdf

On the up side I get a different error message now: Code: -2147467259, NI_Database_API.lvlib:DB Tools Open Connec (String).vi->test db.vi<ERR>Exception occured in Microsoft SQL Server Compact OLE DB Provider: in NI_Database_API.lvlib:DB Tools Open Connec (String).vi->test db.vi

When you created the database did you give it a password? Is there anything else I should be aware of?

I am also using sql server compact. I tried that connection string also and it didn't work neither. I found the strings http://www.connectionstrings.com/?carrier=sqlserver2005ce' rel='nofollow' target="_blank">here. I didn't give any password when creating the database. I got that error message few times. As a matter of fact, this is the only error message that I got - even for syntax errors in sql statements. Not very informative.

[Mike C]

QUOTE (rbogomir @ Sep 26 2008, 01:57 PM)

I am also using sql server compact. I tried that connection string also and it didn't work neither. I found the strings http://www.connectionstrings.com/?carrier=sqlserver2005ce' rel='nofollow' target="_blank">here. I didn't give any password when creating the database. I got that error message few times. As a matter of fact, this is the only error message that I got - even for syntax errors in sql statements. Not very informative.

Rbogomir,

I can get the same error message even if I point to a non existent sdf file. This makes me think that the service is not even running. Is there anything else you needed to do? How did you solve your problem? I have a suspicion that I need to have IIS running for this to work, is IIS running on your machine?

Thanks again for taking the time to help me out.

[alukindo]

Mike:

The SQL Server Compact Edition database requires that you reference the System.Data.SqlServerCE.dll so that it can run in-process with your executable. In this case your executable environment is LabVIEW. Once you have compiled your executable, then it will be your custom LabVIEW application run-time environment.

I have attached a commented LabVIEW example that shows successful connection, data insert, and data read from an SQL Server CE database file. The database was created using (SQL Server Management Studio Express) SSMSE free edition downloaded from Microsoft Web Site. The database used in this example has an empty password. I tried uploading the CETest.sdf (*.sdf database) file but the upload failed. ---Rejected by the LAVA FTP server?

Be sure to download the latest version of SQL Server CE which is 3.5 SP 1

I hope this helps.

Anthony L.

[S59X]

QUOTE (Mike C @ Sep 26 2008, 10:11 PM)

I can get the same error message even if I point to a non existent sdf file. This makes me think that the service is not even running. Is there anything else you needed to do? How did you solve your problem? I have a suspicion that I need to have IIS running for this to work, is IIS running on your machine?

I haven't done anything special. IIS is not running on my machine. I tried this on WinXP SP3 using LV 8.6 and Database Connectivity Toolkit. It is strange that it only works when I specify Microsoft.SQLSERVER.MOBILE.OLEDB.3.0 as provider in the connection string and not with Microsoft.SQLSERVER.CE.OLEDB.3.5 as one would expect. Is there a way to check what providers do I have installed on my machine? I don't like the error messages since they don't tell us anything usefull to solve the problem. Here is the VI that works on my machine. It uses a simple table with two columns - a numeric and b nchar.

[Mike C]

QUOTE (alukindo @ Sep 27 2008, 07:18 PM)

Mike:

The SQL Server Compact Edition database requires that you reference the System.Data.SqlServerCE.dll so that it can run in-process with your executable. In this case your executable environment is LabVIEW. Once you have compiled your executable, then it will be your custom LabVIEW application run-time environment.

I have attached a commented LabVIEW example that shows successful connection, data insert, and data read from an SQL Server CE database file. The database was created using (SQL Server Management Studio Express) SSMSE free edition downloaded from Microsoft Web Site. The database used in this example has an empty password. I tried uploading the CETest.sdf (*.sdf database) file but the upload failed. ---Rejected by the LAVA FTP server?

Be sure to download the latest version of SQL Server CE which is 3.5 SP 1

I hope this helps.

Anthony L.

Anthony,

Thanks very much for the reply. I'll give it a try once I get a chance (unfortunately something else came up that needs my attention). This is new programming territory for me and I needed some help. Your approach makes sense and I expect it would work for me. I am surprised to see that your vi does not use any of the database vi's. Thanks again for the help.

Michael

[jcarmody]

QUOTE (Shaun Hayward @ Sep 26 2008, 09:09 AM)

Jim,

Thanks for the hint - I hadn't come across SQLite before - it looks like its doing much of the same kind of thing

My only question is that the link you posted does not seem to have anywhere to actually download the toolkit - am I missing something?

I just joined a http://forums.ni.com/ni/board/message?board.id=170&thread.id=359065' target="_blank">thread on the NI LabVIEW forum where I learned that you can use the database connectivity toolkit if you install an ODBC driver for SQLite. I guess I should have known that...

[noviceS]

THERE IS A MENTION OF AN ATTACHMENT OF EXAMPLE VI, Where is IT?

 

 

The SQL Server Compact Edition database requires that you reference the System.Data.SqlServerCE.dll so that it can run in-process with your executable. In this case your executable environment is LabVIEW. Once you have compiled your executable, then it will be your custom LabVIEW application run-time environment.

I have attached a commented LabVIEW example that shows successful connection, data insert, and data read from an SQL Server CE database file. The database was created using (SQL Server Management Studio Express) SSMSE free edition downloaded from Microsoft Web Site. The database used in this example has an empty password. I tried uploading the CETest.sdf (*.sdf database) file but the upload failed. ---Rejected by the LAVA FTP server?

Be sure to download the latest version of SQL Server CE which is 3.5 SP 1

[Phillip Brooks]

THERE IS A MENTION OF AN ATTACHMENT OF EXAMPLE VI, Where is IT?

 

 

A few years ago the forums experienced a database corruption and many of the attached files were lost or their links to specific posts were lost.

 

An admin may still have some ability to search for this file, but its been a long time. I checked the OPs status and they haven't logged into LAVA in several years.