Compress or Compact Database with SQL Express

How do you Compress or Compact a Database with SQL Express?

If feedback is of benefit, this would be a useful feature to include with SSME.

Regards,

FlavelleRight click on the database in Object Explorer, select Tasks > Shrink > Database.|||Thanks - that was too easy!

Regards,

Flavelle

complex join replication filtering is not working in 3.5 beta

Hi,

I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:

SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30

After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.

please help.

|||

You have to let us know a little more about your environment. - SQL Server version and SP level. Which version of Server Tools (sqlcesaxx.dll) are you using. And so on.

|||

Microsoft SQL Server Standard Edition (64-bit)
Version: 9.00.2047.00
OS: Windows 2003

sqlcesa35.dll version:

3.5.5365.0

|||

For Merge replication, it is important that you use SQL Server 2005 SP2. Try to install this, if the problem persists, log the problem at connect.microsoft.com/sqlserver

complex join replication filtering is not working in 3.5 beta

Hi,

I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:

SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30

After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.

please help.

|||

You have to let us know a little more about your environment. - SQL Server version and SP level. Which version of Server Tools (sqlcesaxx.dll) are you using. And so on.

|||

Microsoft SQL Server Standard Edition (64-bit)
Version: 9.00.2047.00
OS: Windows 2003

sqlcesa35.dll version:

3.5.5365.0

|||

For Merge replication, it is important that you use SQL Server 2005 SP2. Try to install this, if the problem persists, log the problem at connect.microsoft.com/sqlserver

complex join replication filtering is not working in 3.5 beta

Hi,

I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:

SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30

After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.

please help.

|||

You have to let us know a little more about your environment. - SQL Server version and SP level. Which version of Server Tools (sqlcesaxx.dll) are you using. And so on.

|||

Microsoft SQL Server Standard Edition (64-bit)
Version: 9.00.2047.00
OS: Windows 2003

sqlcesa35.dll version:

3.5.5365.0

|||

For Merge replication, it is important that you use SQL Server 2005 SP2. Try to install this, if the problem persists, log the problem at connect.microsoft.com/sqlserver

complex join replication filtering is not working in 3.5 beta

Hi,

I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:

SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30

After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.

please help.

|||

You have to let us know a little more about your environment. - SQL Server version and SP level. Which version of Server Tools (sqlcesaxx.dll) are you using. And so on.

|||

Microsoft SQL Server Standard Edition (64-bit)
Version: 9.00.2047.00
OS: Windows 2003

sqlcesa35.dll version:

3.5.5365.0

|||

For Merge replication, it is important that you use SQL Server 2005 SP2. Try to install this, if the problem persists, log the problem at connect.microsoft.com/sqlserver

complex join replication filtering is not working in 3.5 beta

Hi,

I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:

SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30

After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.

please help.

|||

You have to let us know a little more about your environment. - SQL Server version and SP level. Which version of Server Tools (sqlcesaxx.dll) are you using. And so on.

|||

Microsoft SQL Server Standard Edition (64-bit)
Version: 9.00.2047.00
OS: Windows 2003

sqlcesa35.dll version:

3.5.5365.0

|||

For Merge replication, it is important that you use SQL Server 2005 SP2. Try to install this, if the problem persists, log the problem at connect.microsoft.com/sqlserver

Compatibility issues between Vista/XP?

HI

I have just started using SQL Server Compact Edition. I have developed a test application just to see if it fits my needs but found something extremely weird.

It's so strange that I guess I'm doing something wrong...

I create the database programmatically from my application if it doesn't exist or open the existing one in the same directory.

The question is that...
When I create a DB from Windows Vista, I can use it normally in that computer and on any other Vista OS computer... but this DB won't work on an XP or 2003!
The opposite applies as well, DBs generated from the same application running on XPs won't work when opening them on Vista. I.E. it seems that DBs generated in those OS are not compatible.

I'm using v3.1 from a c# .Net2 application. I'm not using encryption or anything, just the simplest ConnectionString.

The exception is thrown then I connect and its Message is "Attempted to read or write protected memory. This is often an indication that other memory is corrupt".

Have you ever seen this?
I there something I could test/change?

Thanks in advance.You could try to send me the SDF file and I will test (winxp sp2), if I can reproduce...|||You can download the database I generated on Windows Vista from here.

The Windows XP test machine is a Virtual PC with Windows XP SP2 and all the updates applied. Both computers have the same language settings and I've even made my app to select the CurrentThread's Culture to "en-US" to avoid possible language problems.|||Erik told me via email that he was able to open the DB in his WinXP using the SQL Management Studio.

So I installed a development environment in a WinXP virtual machine to see what happened... results:

After installing VS2005 + SQLServer2005 + SQLServerCE:
1) A connection cannot be stablished from a .Net application
2) The database can be opened from SQL Server Management Studio

but (and this is the most interesting part)

3) After opening the DB with the Management Studio, the database file is modified (checked size and date) and now it CAN be opened from the same .Net application.

so, the SQL Server Management Studio is "fixing" the database.

4) The "fixed" database now works in XP and Vista.

will continue investigating.

(for your information. The original Vista database was generated programatically creating everything using SQL commands).|||More info:

Repairing the database (SQLCeEngine.Repair) works more or less.

- If I repair the Vista-generated database in WixXP before opening it, it will work.

- If I repair it in Vista and then try to open it in XP it won't work.
- In the other hand... if I repair the DB in XP it won't work in Vista.

So, it seems like you have to repair the database before opening it on the other OS. And repairing it on one makes it incompatible on the other.

ajjjjjj!!!!|||

Ok, Fernando.

Using the supplied app and sdf and this connection string, it get the same error (on XP SP2)

Code Snippet

Data Source ="TestDBIbistic2007.sdf"; File Mode=Read Only; Temp Path = %temp%

It is fixed by changing the connection string to:

Code Snippet

Data Source ="TestDBIbistic2007.sdf"

So the sdf is in a bad state somehow...

|||The database will be located in a read-only drive, that's why I added the "FileMode=Read Only" thing (and therefore "Temp Path=%temp%").

I will test if I can open the database without the Read Only modifier from a read only folder.
Another solution is to copy the database (that shouldn't be too big) to the temp folder and use it from there...

Anyway

Thanks a lot for your help, Erik!

Compatibility issues between Vista/XP?

HI

I have just started using SQL Server Compact Edition. I have developed a test application just to see if it fits my needs but found something extremely weird.

It's so strange that I guess I'm doing something wrong...

I create the database programmatically from my application if it doesn't exist or open the existing one in the same directory.

The question is that...
When I create a DB from Windows Vista, I can use it normally in that computer and on any other Vista OS computer... but this DB won't work on an XP or 2003!
The opposite applies as well, DBs generated from the same application running on XPs won't work when opening them on Vista. I.E. it seems that DBs generated in those OS are not compatible.

I'm using v3.1 from a c# .Net2 application. I'm not using encryption or anything, just the simplest ConnectionString.

The exception is thrown then I connect and its Message is "Attempted to read or write protected memory. This is often an indication that other memory is corrupt".

Have you ever seen this?
I there something I could test/change?

Thanks in advance.You could try to send me the SDF file and I will test (winxp sp2), if I can reproduce...|||You can download the database I generated on Windows Vista from here.

The Windows XP test machine is a Virtual PC with Windows XP SP2 and all the updates applied. Both computers have the same language settings and I've even made my app to select the CurrentThread's Culture to "en-US" to avoid possible language problems.|||Erik told me via email that he was able to open the DB in his WinXP using the SQL Management Studio.

So I installed a development environment in a WinXP virtual machine to see what happened... results:

After installing VS2005 + SQLServer2005 + SQLServerCE:
1) A connection cannot be stablished from a .Net application
2) The database can be opened from SQL Server Management Studio

but (and this is the most interesting part)

3) After opening the DB with the Management Studio, the database file is modified (checked size and date) and now it CAN be opened from the same .Net application.

so, the SQL Server Management Studio is "fixing" the database.

4) The "fixed" database now works in XP and Vista.

will continue investigating.

(for your information. The original Vista database was generated programatically creating everything using SQL commands).|||More info:

Repairing the database (SQLCeEngine.Repair) works more or less.

- If I repair the Vista-generated database in WixXP before opening it, it will work.

- If I repair it in Vista and then try to open it in XP it won't work.
- In the other hand... if I repair the DB in XP it won't work in Vista.

So, it seems like you have to repair the database before opening it on the other OS. And repairing it on one makes it incompatible on the other.

ajjjjjj!!!!|||

Ok, Fernando.

Using the supplied app and sdf and this connection string, it get the same error (on XP SP2)

Code Snippet

Data Source ="TestDBIbistic2007.sdf"; File Mode=Read Only; Temp Path = %temp%

It is fixed by changing the connection string to:

Code Snippet

Data Source ="TestDBIbistic2007.sdf"

So the sdf is in a bad state somehow...

|||The database will be located in a read-only drive, that's why I added the "FileMode=Read Only" thing (and therefore "Temp Path=%temp%").

I will test if I can open the database without the Read Only modifier from a read only folder.
Another solution is to copy the database (that shouldn't be too big) to the temp folder and use it from there...

Anyway

Thanks a lot for your help, Erik!