SQL CE Reset + Best Tools to Manage SQL CE

Question

Hello, I need some help with regards to the SQL Compact Edition.

1. I need to reset all indexes and empty databases (latter part I've done) - maybe there is something out there but I'm not using the correct key words to search with.

2. What is the best too to manage SQL CE databases? I am currently using SqlCe40Toolbox.exe .... but I'm sure there are better, any suggestions?

Thanks for reading my thread.

Answer 1

1: Not sure what you mean by reset indexes, once a lot of data has been deleted, you can use the Maintenance menu in the Toolbox to Compact the database, to reclaim unused disk Space.

2: I have a list of all available tools here: http://erikej.blogspot.dk/2009/04/sql-compact-3rd-party-tools.html (Disclaimer: I am the author of sqlce40toolbox.exe).

 

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 2

Nice tool you have there, I found it pretty useful. I would like to know how to reset all indexes back to 0 ... since I automatically give an ID through SQL Identity, I would like to empty the database and start afresh, since I was using it for testing. Is this possible using any one of your tools?

Also thanks for your blog link, some wikid info out there!

Cheers.

Answer 3

Use a command like:

ALTER TABLE [OrderDetails] ALTER COLUMN OrderDetailId IDENTITY (1,1)

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 4

Use a command like:

ALTER TABLE [OrderDetails] ALTER COLUMN OrderDetailId IDENTITY (1,1)

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 5

Hi jacqmizz,

Regarding your first question, since you want to reset all indexes and empty databases, we can remove the databases from your application and recreate the ones we needed.

To your second question, we can manage SQL Server Compact databases with Visual Studio or SQL Server Management Studio. For more detail information, please refer to the following link:

Server Explorer/Database Explorer:
http://msdn.microsoft.com/en-us/library/x603htbk.aspx

Managing SQL Server Compact Edition Databases with SQL Server Management Studio:
http://technet.microsoft.com/en-us/library/ms172933(v=sql.90).aspx

---

Allen Li
TechNet Community Support

Answer 6

Thanks for your replies, I found the information very useful, however a small comment to Allen Li - SQL Server Management Studio is not reading my .sdf database 4.0 ... I will give it a couple of other tries though, now knowing that it may be used, maybe wasn't persistent enough last round! Thanks

Answer 7

No version of SSMS supports SQL Server Compact 4.0

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 8

Ahhh that's why Erik ..... then I will continue using SqlCe40Toolbox.exe ......

If I were to suggest and improvements, I would definitely add the reset database indexes and data .... it would be nice to have.

PS: Nice blog too +my vote 

Answer 9

Reset data - is that: DELETE FROM [table] ? In that case it is already there (per table) ??

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog