Hi,
I am wondering why a database that was 2Gb in size would come down to just 300Mb when I copy all the data from the origional database into a blank new one.
I ran a database shrink on the origional database but that only reduced it from 2.1 Gb to 2Gb.
I was shocked to see the size difference when I copied the data.
As far as I can tell the copied database still contains all the records of the origional.
I remember that MSAccess had a Compact function that literally copied all the data from one databse into a new empty one - is there the equivalent for SQL?
I am still working on my project, but figured I would share my findings with the community, on how I imported my SQL Server database tables into a new SQL Compact Edition database. Unfortunately, I haven't figured a workaround to the IDENTITY_INSERT issue, but other than that, I will proceed.
1) I downloaded and played with the tool presented in the article "Generate SQL Insert statements for your SQL Server 2000 Database" I made the following modifications . (my modification can be found here) a) Disable scripting dependencies b) Add a filter for objects to script c) Prompt for each table from which to export data
2) From the scripts output by this tool, I had to (which I may integrate into the tool later... who knows) a) Remove 'On Primary' statements after the create table statements. b) Remove date defaults. c) Most other SQL generated by the tool seemed to work.
3) Execute each 'Create Table' statement individually.
4) For the Data Import, I had to add semi-colons to the end of each Insert Line.
5) I Have found no workaround for the IDENTITY_INSERT issue. If anyone has a good idea, I would love to hear it. In my case, I was importing the DotNetNuke core store module tables populated with about 2500 products and 20+ categories consisting of a simple categories table and products table. I imported the categories table into a fresh MS Access database file, and then ran the update query against the attached products table in the original SQL Database, then regenerated the insert statements, and imported the data generating fresh product ID's. This database will only be read only, so it didn't matter about maintaining the Product ID's, just the relationship between the products and categories.
Separately, I found a great blog article on how to embed a SQL script as a resource for your application and run it on your applications first run, so my steps above would provide a quick foundation for generating that sql script.
I have problems in migrating. Here are the questions: 1. Is the ShrinkDatabase task the SSIS equivalent of this script? If so, I'm having problems creating a new connection since it is an mdb file stored on local computer and not on a server. Is there a workaround on this? 2. If I have to use the script task, I use this codes:
Set Engine = CreateObject("DAO.DBEngine.35") Engine.CompactDatabase "C:.path...database_UnCompress.mdb", "C:path... estdatabase.mdb" or
Set Engine = CreateObject("JRO.JetEngine") Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pathdatabase.mdb", _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pathcompacted_database.mdb" Both produce the same error: Option Strict On disallows late binding. Any suggestions?
3. I also tried to use the Execute SQL Task, using dbcc shrinkdatabase. But I seem lost again in creating a connection. Should I consider "database_UnCompress.mdb" as flat file source?
I'm an Access man moving towards SQL Server and I'm interested in knowing if there is a utility for my SQL Server version 7 database that is similar to Access' Compact and Repair utility? I'd like a recommendation from a guru on what Prevenatative Maintenance measures I should be running daily/weekly/monthly etc...
I want to access to a SQL Compact database with an application develope with Visual Basic ans using ADO (not ADO .Net). Is it possible and how ? Thank You.
I am new to SQL Mobile developement and trying to developing a small device appliction. My C# code is shown below. My code is executing correctly, but i am not able to see the inserted records in to my SQL Compact database. What i am doing here? Please help.
FYI, I have added the database in to my solution. My solution and database resides in E:\some directory, where as the my assembly code base represents the datasource as \ProgramFilesMobExpTrackerMobExpTracker.sdf.
SqlCeConnection connection
{
get
{
if (_connection == null)
{
_connection = new SqlCeConnection(ConnectionString);
_connection.Open();
}
return _connection;
}
}
string CurrentFolder
{
get {return Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
I got a SqlCe2.0 database here called JOB.sdf, size is 260K. When I tried to compact it, it took very long and that JOB.sdf.tmp was created with 27.7M!!!, I can still open table in the database, but I found that there is some invalid data (unreadable square boxes). It looks like database was corrupted. My application gave error:
Note: " " is displayed as "SOHSOH....." with black background.
Apart from compacting database, is there manual job or tool we can do/use to fix a corrupted database caused by power failure? (e.x: all data saved successfully before power failure)?
I'm porting my C++ (EVC3) application from Sql CE 2.0 to Sql Mobile 3.0 (with OLE DB). To compact a 2.0 database, i used ISSCECompact interface. But it doesn't exists with the new version. I've only found C# and VB.Net samples in MSDN ...
I have an application that uses a SQL Server Compact 3.5 database backend. Everytime the application starts I want to zero out my database, meaning remove all records and reseed all identities. This datatbase is then filled based on a directory structure. Once this DB is filled I then send the entire database to a Windows Mobile PPC device via 802.11g WIFI. Before sending the database I first create an MD5 checksum on both the server and client sides to see if the database actually needs to be transfered. If the checksums match I will not send it as the client already has the lastest copy.
My problem is that I can never count on the database identity values starting at the same number on any particular instance of the application so the checksums are usually different. To solve this problem I need to ensure that everytime I start my server application the database (and all identifiers) will always be the same, will always be as if I just creaed it. How do I do this?!?!?!? This seems like it would be a trivial operation as I can imagine its use in multiple situations. Anyone know how to zero out the database?
I need to create a desktop program on vb.net , based SQL compact edition. but I do not know how to connect to CE database. I already create the sdf file, but I can not connect it from the poject , is there any way to do it ?
I am currently working in VB.NET with SQL Mobile 2005. I am trying to compact a database, .sdf, using the SqlCeEngine Object compact(connectionstring). When I use this code to compact a small database, only about 300KB, it returns okay however when I try to compact a larger database, about 9MB, it returns with a SQL Error Code: 0, "The operation completed successfully" however with no compacted database. The code works however it only seems to work for smaller databases. Does anyone have any ideas on how to correct this error or tell me why it returns with that type of error.
Every time I modify my SQL Server database, it grows by about 1 MB. I haven't added any data, in fact have reduced column sizes. Is there a utility that will compact the database and return it to a more manageable size?
I'm using VS2008 RTM with a new SQL Compact 3.5 database and I read in VS2008 and SQL Compact 3.5 BOL that Database Diagrams are supported as well as Foreign Key creation from Server Explorer.
However, there doesn't seem to be support for this. Database Diagram commands are not displayed for this new database (but it is for my SQL Express database) as instructed by BOL.
Can anyone tell me if VS2008 fully supports SQL Compact 3.5 with database diagrams and visual tools to create relationships? If so, how can you create a database diagram in VS2008 for SQL Compact 3.5?
So I started an SQL CE database for use inside a mobile application. I used SSMS to create a .sdf file (because if I let Visual Studio do it, SSMS can't open it because it will be version 3.5).
The reason I wanted to use SSMS is because I wanted to be able to design the database in design view, and populate it with initial information for the application to use. That means adding tables and rows.
The problem is, SSMS doesn't seem to have a design view available when working with CE databases. It also doesn't seem to have a feature for adding rows into tables. It's essentially as useless as VS2008 for designing my mobile database. It only lets me add tables, and I can't even do that visually in design view. I have to use those cumbersome forms.
Is there any way to design a CE database in design view? And add rows of data into tables? It feels like I'm overlooking something.
Note: I dont need to subscribe or publish the database. The mobile application just needs to use the database as a repository.
The device I'm working on has a filesystem on compact flash, but we're concerned about burning it up with excessive writes. One solution might be to keep the SQL database in the the object store, perhaps dumping a copy out to the CF as backup. Any pointers as to how to accomplish this? If we were to keep the db in a file on the CF, what's the SQL Server CE behavior for writing to the file? Does it write every time a record is created/updated, or is there any ability to control when the data is flushed to the file?
When I try to create a subscription to my SQL Server Compact 3.5-database file, it gives an SqlCeException-message that says that the file is not enabled for replication. How do enable it?
My SQL Server Management Studio won´t connect to my compact server file right now, so that method is not an option for me right now.
We have a plan to use Microsoft SQL Server 2005 Compact Edition RC1 as a backend. Our Front-end is VS .NET (VB.NET).
We want to know how to edit the Compact Edition database (.SDF). Is there any editor is available right now in the market? or microsoft provided any editor for this.
OR Can i use microsoft 2005 sql server standard edition for this.
Hi there, after some hours of installing and experiments i can now open and sql server compact database (sdf) iam also able to create a new database and add tables and columns. (everything with the ms sql server management studio express)
i can also open the northwind database. but iam not able to view the data .
if i rightclick on a normal database table i can choose ->open table and see its content.
but on a compact table i have at rightclick only
edit table properties delete
refresh
how can i see the data in the table or add new content ?
I'll try and ask one more time. Does anyone have a clue how to fix this?
"A SQL Server Compact Edition DLL could not be loaded. Reinstall SQL Server Compact Edition. [ DLL Name = sqlceca30.dll ]"
or
"SQL Server Compact Edition encountered problems when opening the database" "Internal error: The database is not initialized."
I've been fighting this for well over a week now. My XP based version works fine but this error occurs routinely on my CE4.2 device. I cannot find anything meaningful using Google other than 'look for non disposed' replication objects which I've done. Is this bad hardware? I don't believe I'm out of memory as the app is about 1.2MB including about 8 DLL's and GC indicates I have about 300KB of objects at the time of the failure. I also use a bunch of static (global) objects that I use throughout the code.
It would be nice if I could get the lower level error messages causing these failures but that is probably too much to ask.
The odd thing is that it's only the replication engine that fails as even after the failure, the app can reopen the DB and work with the data just fine.
My basic order of the application is...
1 Open DB SqlCEConnection 2 Create/Prepare any SqlCECommand objects 3 Do work here 4 Dispose any SqlCECommand objects 5 Close DB 6 Dispose SqlCEConnection object 7 Create SqlCEReplication object 8 BeginSynchronize 9 ... error here.. 10 Dispose SqlCEReplication object 11 Reopen DB SqlCEConnection
I've tried creating the Replication object at program start and that does not help. I'm guessing there is something about .net that I don't understand either with stack space, # dll's or whatever. I've rearranged the order of the Dispose, Create, SQL CE objects to include just about every permutation but still am hitting this same problem.
Are there any tools that can help me profile this app for memory problems or usage? Everything seems geared for .Net and not .NetCF.
How Can I get Identitiy field from database while inserting new row in sql server 2005 compact edition.
Ex:
I am inserting row in a table through SqlQuery ("insert into ....") in which one of the field is of type Identity which generates number automatically. I want that number to pick up that number and used it in child table....
I need some advise/help how to deploy a small sql server compact 3.5 database on networkshare where about 8 people have access to it(install de client ) and can modify the database with aprox 3000 records. I'm using visual basic 2008 and created a compact database and filled it with one table of an export of an access database done with data port console. And created a new windows forms application and saved the project. Publish is not the problem but how too set it up...... There is not much info to find on how to get a sql server 3.5 database running on a network share. Who can help ???
Hi, I have compact sql databases which will be local on multiple users devices. Due to space constraints, for one of the tables i have had to use auto incrementing integer which works fine for the local database but i would like to merge all of the users databases into a global database. The table format can be seen below:
Code SnippetCREATE TABLE Players ( ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, FirstName NVARCHAR(32), LastName NVARCHAR(32) );
The Players table will merge fine as GUIDs are used. However, how will the sync capabilities of compact SQL handle the sessions table? When it pushes the local data to the remote database will it change the Sessions.ID column to a unique field (as no doubt lots of people will have 1, 2, 3 in their local databases and the global database must have a unique ID), and then transfer this changed ID back to the local database? Furthermore, if it changes the Sessions.ID column during the merge it will also need to update the SessionDetail.SessionID foreign key to maintain referential integrity, is this also handled?
So my question is, how much of this sync and data merge is automated and are there any good examples or pointers for my scenario? I cannot reasonably use a GUID for Session.ID as there will be lots of SessionDetail entries and the size would be far too much.
End Sub this code worked well. we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).
we've got a SQL Server 2005 which replicates with an SQL Server Compact 3.5. Every 10 to 20 synchronisations we're getting the error mentioned above. A Soft reset of the device helps to make the synchronization working again.
Why is this error happening and how can we resolve this?
BTW, we've also running system that replicates with an SQL Server Compact 3.0 without having this problems.
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String
With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName
'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With
'Notify user MsgBox("Data restored successfully")
Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table
'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want) Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename)
'User notification ) MsgBox("A new empty database was created successfully")
'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification MsgBox("The database was compacted successfully")
i tried to connect to a SQL Server Compact Database (version 3.5) with SQL Server Management Studio Express (Version 9.00.3042.00).
But i get the follwing error message (sorry, it's a german message):
..................................... Es kann keine Verbindung mit 'D:DocumentsVisual Studio 2008Projects\_ProduktivSuperkalibrator_PrototypenSuperkalibrator_PrototypenDatenbankDatabaseTest.sdf' hergestellt werden.
Sie versuchen, auf eine ältere Version einer Datenbank von SQL Server Compact Edition zuzugreifen. Falls es sich um eine Datenbank von SQL Server CE 1.0 oder SQL Server CE 2.0 handelt, führen Sie 'upgrade.exe' aus. Falls es sich um eine Datenbank von SQL Server Compact Edition 3.0 oder höher handelt, führen Sie die Komprimierung und Reparatur aus. [ Db version = 3505053,Requested version = 3004180,File name = D:DocumentsVisual Studio 2008Projects\_ProduktivSuperkalibrator_PrototypenSuperkalibrator_PrototypenDatenbankDatabaseTest.sdf ] (SQL Server Compact Edition ADO.NET Data Provider) .....................................
Is there a solution to manage Compact Databases in Version 3.5?
everything is ok for first run. but i leave the program relogin than that error occured ppc2003 second edition devices. Windows mobile 5.0 device works fine. can anybody help me?
VS2005 ver 8.0.50727 SSCE31VSTools-ENU.exe loaded SSCE31SDK-ENU.msi loaded
machine 1 SQL 2005 loaded
machine 2 http://192.168.20.22/ssce/sqlcesa30.dll "Microsoft SQL Server Compact Edition Server Agent" looks fine
pocket pc side C:Program FilesMicrosoft SQL Server Compact Editionv3.1SDKinwce400armv4
End Try ------------------------------------------------------------------------------ i added the following code to very beginning of my code too. that code lock my device
Declare Function LoadLibrary Lib "coredll" Alias "LoadLibrary" (ByVal lpLibFileName As String) As IntPtr
