create tables and insert data in sql server mobile on dekstop

Hello (sorry my bad english, im brazilian)

I was using Visual Studio 2003 and SQL Server CE 2.0 for C# mobile applications. The .sdf database were created in the emulator or in the mobile device itself using Query Analizer.

The application developed need some initial data to run, and this data is obtained executing one service that reads a postgree database, and insert the data in the SQL CE database of the mobile device. But, given the size of the database (maybe 10.000 rows), it tooks too much time (sometimes 6 hours).

Now we are migrating to Visual Studio 2005 and SQL Server 2005 Mobile Edition.

I want to know if its possible to create the .sdf database and load the data into this database on the desktop. Maybe through the execution of a .sql script, or through a service executed on the desktop.

After this, its just upload de .sdf file to the mobile device.

Thanks

Robson

Yes, you can create and populate your SQL Mobile database on the desktop as long as that desktop or server meets one of these criteria:

1. it contains a licensed copy of Visual Studio 2005

2. it contains a licesed copy of SQL Server 2005

3. it runs Windows XP Tablet PC edition

The code to do so is covered in the SQL Mobile Books Online.

There are other approaches as well, including third party tools like those at www.primeworks.pt, using SQL Server 2005 Integration Services, or creating and populating the database within SQL Server 2005 management studio.

-Darren

|||

Daren,

thanks for the help... I have found the way to create a sql server mobile 2005 database and insert data on the desktop using c# (running on desktop off course) at these forum topics:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=213973&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

I′ve used my licensed copy of Visual Studio 2005 to do it. Now I′ll test the solution to make a benchmark... I hope that now i will be able to create the database for my application faster....

thanks again...

|||

Please let us know how it works out for you.

Darren

|||

I have created the following table ('cidade' means city in portuguese):

CREATE TABLE cidade ( idcidade numeric(18,0) NOT NULL, codigo integer NOT NULL, descricao nvarchar(80) NOT NULL, ddd nvarchar(3), naturalidade nvarchar(80), idunidadefederativa numeric(18,0) NOT NULL )

The program (written in c# with visual studio 2005 and sql server 2005 mobile edition) insert 5565 rows in this table. It reads a sql insert line from a text file and execute the sql, eg:

INSERT INTO cidade VALUES (1, 1, 'ALTA FLORESTA D OESTE', NULL, NULL, 21)

It tooks 6 seconds to do it (running on a HP notebook with celeron processor).So, 927.5 rows per second.

Before, when we insert data on a database located at a pocket pc, this operation took 20 - 30 minutes (using c# compact framework from visual studio 2003 and sql ce 2.0).

|||

thanks for sharing your benchmark results - that's very good news.

-Darren

sql

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create New SQL Server Mobile Edition Database

Hi, I follow the instruction to install SQL Server 2005 Mobile Edition, but when I try to change the Data Source to add a new connection, I don't find "Create New SQL Server Mobile Edition Database".
Does anyone know how to fix this?

Thanks Paolo GiorgioThe following steps should give you a brand new Sql Mobile database

- Go to the new connection dialog. Quickest way is to right click on Data Connections in the Server Explorer and choosing to add a connection

- Make sure you have the Sql Mobile provider selected. The full name is "Microsoft SQL Server Mobile Edition (.NET Framework Data Provider for SQL Server CE)"

- Now you should see a 'Create' button which will let you create a new Sql Mobile database|||

I just follow your step, but when i have to choose the Sql Mobile provider, "Microsoft SQL Server Mobile Edition" is not available, evenif i installed .Net compact framework for Sql Mobile.

|||Do you see Sql Mobile in your Add/Remove programs list?|||Which Program List?|||In your Control Panel->Add or Remove programs, do you see an entry which says "Microsoft Sql Server 2005 Mobile [ENU] Developer Tools"?|||No i don't find in the List.
I find only "Microsoft Sql Server 2005 Mobile [ENU] Beta 3".|||I try to find "Microsoft Sql Server 2005 Mobile [ENU] Developer Tools" by Google but i don't find anything|||

In the early beta days of "Microsoft SQL Server 2005 Mobile [ENU] Developer Tools" was registered as "Microsoft SQL Server 2005 Mobile [ENU] Beta 3".

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi NRO, SQL Mobile

|||

Thank you, but it still not working.
We follow your instruction, reboot the PC, but the Dataprovider still not in the list of the Data Sources.

Any other solution?
Thanks,
Paolo Giorgio

|||DEVTOOLS ==> Microsoft SQL Server 2005 Mobile [ENU] Beta 3 or Microsoft SQL Server 2005 Mobile [ENU] Developer Tools

Uninstall & Reinstall DEVTOOLS using the MSI sqlmobile30devtoolsenu.msi in wcu\SQLCE sub directory of your Whidbey Installation Disc.

Goto ADD/REMOVE programs to uninstall DEVTOOLS.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Create New SQL Server Mobile Edition Database

Hi, I follow the instruction to install SQL Server 2005 Mobile Edition, but when I try to change the Data Source to add a new connection, I don't find "Create New SQL Server Mobile Edition Database".
Does anyone know how to fix this?

Thanks Paolo GiorgioThe following steps should give you a brand new Sql Mobile database

- Go to the new connection dialog. Quickest way is to right click on Data Connections in the Server Explorer and choosing to add a connection

- Make sure you have the Sql Mobile provider selected. The full name is "Microsoft SQL Server Mobile Edition (.NET Framework Data Provider for SQL Server CE)"

- Now you should see a 'Create' button which will let you create a new Sql Mobile database|||

I just follow your step, but when i have to choose the Sql Mobile provider, "Microsoft SQL Server Mobile Edition" is not available, evenif i installed .Net compact framework for Sql Mobile.

|||Do you see Sql Mobile in your Add/Remove programs list?|||Which Program List?|||In your Control Panel->Add or Remove programs, do you see an entry which says "Microsoft Sql Server 2005 Mobile [ENU] Developer Tools"?|||No i don't find in the List.
I find only "Microsoft Sql Server 2005 Mobile [ENU] Beta 3".|||I try to find "Microsoft Sql Server 2005 Mobile [ENU] Developer Tools" by Google but i don't find anything|||

In the early beta days of "Microsoft SQL Server 2005 Mobile [ENU] Developer Tools" was registered as "Microsoft SQL Server 2005 Mobile [ENU] Beta 3".

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi NRO, SQL Mobile

|||

Thank you, but it still not working.
We follow your instruction, reboot the PC, but the Dataprovider still not in the list of the Data Sources.

Any other solution?
Thanks,
Paolo Giorgio

|||DEVTOOLS ==> Microsoft SQL Server 2005 Mobile [ENU] Beta 3 or Microsoft SQL Server 2005 Mobile [ENU] Developer Tools

Uninstall & Reinstall DEVTOOLS using the MSI sqlmobile30devtoolsenu.msi in wcu\SQLCE sub directory of your Whidbey Installation Disc.

Goto ADD/REMOVE programs to uninstall DEVTOOLS.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

create mobile database from xls, csv, text or mdb

Hello,

Since my experience in VS is extremely limited, i'd like to be exused if questions sound silly.

My problem is that, at my device application project, i want to build a database that will retrieve data from .xls, or .csv, or .txt, or .mbd files. If i've noticed well, there is no support for OLEDB or ODBC, since by the time that i add tableadapter to my database.xsd and use these, after insertion i get multiple errors informing that system.data.oledb. .... or .odbc "type is not defined".

If it cannot be done, and since i still try to figure out how smart devices function, transact with databases, e.c.t. is there any suggestion?

Thank you in advance

Kostas

you'll likely need to use sql server mobile.|||

I'am trying to do so with sql mobile.

Going-> right click on "deviceapplication1" -> add -> new item -> sql mobile database

This gives me an empty database. In order to fill it i go at server explorer, right click on "AppDatabase2.sdf" (new created database), -> modify connection -> change (data source field) -> i choose microsoft ODBC data source -> user or system data source names: ms access database -> OK. Then browsing for mdb file (my access databace), and it's in server explorer; no problem yet. Then going to solution explorer, opening "AppDatabase2DataSet.xsd", drug & drop tables from server explorer to "AppDatabase2DataSet.xsd" (making table adapters(?)). Then i get this huge error list that "system.data.odbc...." Type is not defined.

Either i need to add some reference (as i did at VBA) since even autofill doesn't work (I try at the top of code to insert "imports system.data.odbc), or it's just not supported in sql mobile database

|||you can't use access as your datasource. appdatabase2.sdf IS your sql server mobile database, and that needs to remain your datasource. no odbc, no ole db in NETCF. your only mobile database option for .net development is a sql server mobile DB.|||

That is something that i have already understood after some experiment.

The question is how sql mobile works and if i can update the tables from an external (server or just a given path) sourse. I have created an sdb, with the tables required, but this tables are empty, and need to be updated each day from source files of the above mentioned format.

|||I'm not a drag and drop type of guy, but from what you've said, you're trying to create a set of adapters between Access and SQL Server Mobile -- you can't do this, there's no ODBC implementation within NETCF for the CF to talk to Access. I learned yesterday from a posting by Ilya Tumanov that there's a util called AccessSynchronizer for Access<-->SQL Server Mobile synchronization, you may want to look into it. Personally I'd just dump the stuff from Access into XML or CSV, and import using my own code, but that may not be for you. Another option is to wrap your datasource in a web service and proxy the sync operation that way.|||

Thank you Andrew, i'll take a look at accessSynchronizer. If my knowledge and experience wasn't so poor i could ask about the code for importing CSV since this is the format that interest me more, but i guess that it will be rather difficult for me fix.

Thank you very much

|||

I believe opennetcf has a csv importer, you may want to consider that ...

this post is a bit old, but may still be relevant:
http://groups.google.com/group/microsoft.public.dotnet.framework.compactframework/browse_thread/thread/671b1480a962e8e0/92d0ba873b07398d%2392d0ba873b07398d

CREATE INDEX statement for SQL Mobile not documented?

Hello,

I'm unable to find documentation for the CREATE INDEX statement for SQL Mobile. DROP INDEX is documented however. Is this a known issue or am I missing something? This can be seen here:

http://msdn2.microsoft.com/en-us/library/ms173440(en-US,SQL.90).aspx

If you scroll the left pane up a little, you will notice that CREATE INDEX is not in the tree. Am I missing something?

Thanks.Thanks Ravi for catching this. And we are aware of it too.
We already have a bug opened in our Bug Database for this.

The next web site refresh should pickup the fix.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation|||Yes, the topic will be available in the December download version of SQL Server Books Online.

In the meantime, you can use the CREATE INDEX topic in the full version of Books Online : http://msdn2.microsoft.com/en-us/library/ms188783(en-US,SQL.90).aspx

Regards,|||

Finally it is live in SQL Mobile Books Online @. http://msdn2.microsoft.com/en-us/library/ms345331(en-US,SQL.90).aspx

Thanks,.

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

CREATE INDEX statement for SQL Mobile not documented?

Hello,

I'm unable to find documentation for the CREATE INDEX statement for SQL Mobile. DROP INDEX is documented however. Is this a known issue or am I missing something? This can be seen here:

http://msdn2.microsoft.com/en-us/library/ms173440(en-US,SQL.90).aspx

If you scroll the left pane up a little, you will notice that CREATE INDEX is not in the tree. Am I missing something?

Thanks.Thanks Ravi for catching this. And we are aware of it too.
We already have a bug opened in our Bug Database for this.

The next web site refresh should pickup the fix.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation|||Yes, the topic will be available in the December download version of SQL Server Books Online.

In the meantime, you can use the CREATE INDEX topic in the full version of Books Online : http://msdn2.microsoft.com/en-us/library/ms188783(en-US,SQL.90).aspx

Regards,|||

Finally it is live in SQL Mobile Books Online @. http://msdn2.microsoft.com/en-us/library/ms345331(en-US,SQL.90).aspx

Thanks,.

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Create device in emulator from Management Studio

Hi,

i know i have the chance to access my mobile device (physical or emulator) from Management Studio.
I went to Connect Object Explorer, server type Sql Server Mobile but i can′t connect to my emulator and i can′t see any way to connect to a physical device either. How can i do this?

The only thing i can do is create a sql server mobile database (.sdf).

Thanks
SP

Moving to Sql Server Compact Edition forum where it has got better chance of being answered.

-Thanks,

Mohit

|||

You can do this only if your device (or emulator) is connected through active sync.

Object Explorer:

Connect->

Database file-> Select 'Browse for more' and then select 'Mobile Device' from the file browser.

If you want to create a new database on the device, then just select 'New database' instead of 'Browse'

Thanks

Pragya