Friday, May 29, 2009

Teaser – Scripting SQL Compact in SSMS 2008

How about if you could do this?:

script1

Or this?:

script2 

Keep watching this space…

(Comments/wishes welcome)

Sunday, May 24, 2009

ExportSqlCE 1.9.2 – bug fix

I noticed this forum post, where the poster reports 2 Sync Framework bugs. I knew I was affected by the last one, as I use the same LIKE clause (LIKE ‘__sys%’) to exclude system columns from the ExportSqlCE generated script. I assumed that this clause would exclude all columns beginning with 2 underscores and the letters “sys” – it does indeed, but the like clause also includes any column names that contain the 3 letters, “sys”, like “IsSystem” and “tbSysConfig”  at postion 3. This is due to the fact that the “_” (underscore) is actually a LIKE wildcard character, as documented here.   :-(   -  A possible solution (which is implemented in version 1.9.2), is the following WHERE statement:

SUBSTRING(COLUMN_NAME, 1,5) <> '__sys'

Go and get version 1.9.2 here.

Friday, May 15, 2009

ExportSqlCe 1.9.1 released

This release contains major improvements in performance, I have measured at least 50 % improvement in time to export (on a large database from 12 to 5 seconds).

In addition to performance improvements, the following fixes and changes have been implemented:

- Fixed large file related bug
- Shows execution time in seconds
- Implementation of patch from user rymenbe:
     (performance improvements, by caching column names and keeping a single connection open)
- ran FxCop against the project, fixed various Globalization issues

Go get it. And provide any feedback here.

Tuesday, May 12, 2009

SqlCeCmd tutorial part two – Creating database objects and adding data

This is the second part of a 3 part series with some examples of sqlcecmd usage. The first part is here. This second part deals with creation of database objects and adding data.

To create a table

sqlcecmd -d "Data Source=C:\test2.sdf" -q "CREATE TABLE NewTable (Id int NOT NULL, Info nvarchar(50) NULL) "

To create other objects

To create other objects (foreign keys, indexes) use the appropriate CREATE or ALTER statement as documented in SQL Compact BOL.

To rename a table

sqlcecmd -d "Data Source=C:\test2.sdf"
-q "sp_rename 'NewTable', 'New table' "

So in other words, any valid SQL Compact SQL statement (including UPDATE and DELETE) can be executed form the command line with the “q” option.

To insert data in a table

sqlcecmd -d "Data Source=C:\test2.sdf"
-q "INSERT INTO [New Table] (Id, Info) VALUES (1, N’Test’)"

To run multiple statements in a single “batch”

To run multiple staements in a batch, use the “i” option to specify an input file. The input file should be a text file with the desired SQL statements, each separated by the word GO on a line of it own:

INSERT INTO [New Table] (Id, Info) VALUES (2, N'Test2')
GO
INSERT INTO [New Table] (Id, Info) VALUES (3, N'Test3')
GO
INSERT INTO [New Table] (Id, Info) VALUES (4, N'Test4')
GO

(Contents of c:\input.txt)

Once the file has been created, for example with help from the Database Publishing Wizard (in VS 2008) or ExportSqlCe, you can run the commands in the file like so:

sqlcecmd -d "Data Source=C:\test2.sdf" -i c:\input.txt

Next installment: Querying data…

Monday, May 11, 2009

SQL Compact 3.5 knowledge base articles

This blog entry contains links to the Microsoft public knowledge base articles related to SQL Compact 3.5. For a link to all kb articles relating to SQL Compact, see the link in the left column.

 

Error message when you use the Visual Studio 2008 IDE to manipulate a SQL Server Compact 3.5 Database file which is larger than 128 Megabytes: "The database file that is larger than the configured maximum database size" (968436)

 

FIX: Access violations occur when you run an application under heavy load conditions after you install the 64-bit version SQL Server Compact 3.5 Service Pack 1 (970269)

 

FIX: Non-convergence occurs when you synchronize a SQL Server Compact 3.5 client database with the server by using Sync Services for ADO.NET in a Hub-And-Spoke configuration (969858)

 

FIX: Some rows are deleted when you repair a database by using the Repair method together with the RepairOption.RecoverCorruptedRows option in SQL Server 2005 Compact Edition and in SQL Server Compact 3.5 (967963)

 

FIX: Error message when you run a query in SQL Server Compact 3.5: "The column name cannot be resolved to a table. Specify the table to which the column belongs" (968864)

 

FIX: An error message is logged, and the synchronization may take a long time to finish when you synchronize a merge replication that contains a SQL Server Compact 3.5 subscriber: "UpdateStatistics Start app=CPWerx mobile.exe" (963060)

 

FIX: Error message when you synchronize data for a SQL Server Compact Edition subscriber in SQL Server 2005 and in SQL Server 2008: "A call to SQL Server Reconciler failed. Try to resynchronize. HRESULT 0x80004005 (29006)" (962003)

 

FIX: Error message when you try to open a database file from a CD in SQL Server Compact 3.5 with Service Pack 1: "Internal Error using read only database file" (959697)

 

FIX: Error message when you run a "LINQ to Entities" query that uses a string parameter or a binary parameter against a SQL Server Compact 3.5 database: "The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses" (958478)

 

After you execute the SqlCeEngine.Compact method against a SQL Server Compact 3.5 client database that is synchronized to a central database server, some changes to the client database may not be uploaded (967502)

 

FIX: The SQL Server Compact 3.5 Service Pack 1 update for Windows CE Platform Builder 5.0 is available (956362)

 

Description of cross-version compatibility scenarios for merge replication in SQL Server Compact 3.5 (956208)

 

Description of SQL Server Compact 3.5 Service Pack 1 (955965)

 

Description of the improvements that SQL Server Compact 3.5 Service Pack 1 provides for logging, for enabling Client Agent log settings, and for configuring log file rotation settings (955968)

 

Error message when you try to connect to SQL Server Compact 3.5 Service Pack 1 from SQL Server Management Studio in SQL Server 2008: "Unable to load Microsoft SQL Server Compact" (952218)

 

SQL Server Compact 3.5 Service Pack 1 Server Tools are not installed in WOW mode when you run IIS 7.0 on a 64-bit Windows Server 2008-based computer or on a 64-bit Windows Vista-based computer (955966)

 

Description of the various build versions of SQL Server Compact Edition (950550)

 

A file and some registry entries are missing when you upgrade SQL Server Compact 3.5 Beta to SQL Server Compact 3.5 (945317)

 

You cannot compile a SQL Server Compact-based OLE DB application for Windows Mobile 5.0, and the Transact.h file cannot be found (945374)

 

Exception error message when you use Visual Studio 2008 to deploy a SQL Client-based application to a mobile device or to an emulator: "Missing method Exception" (945371)

 

The column of the rowversion data type is replicated to the subscriber after you synchronize the data between a SQL Server 2005 publisher and a SQL Server Compact 3.5 subscriber (945372)

 

Error message when you try to drag a table from a SQL Server Compact 3.5 database file to a Windows form in Visual Studio 2008: "An error occurred while performing the drop: Exception has been thrown by the target of an invocation" (945375)

 

A smart device application cannot be started on an emulator or on a mobile device if you develop the application on a computer that has Visual Studio 2008 and Visual Studio 2005 installed (945316)

 

FIX: Error message when you try to delete the rows from the table in SQL Server 2005 Compact Edition or in SQL Server Compact 3.5: "Major Error 0x80004005, Minor Error 0. Attempted to divide by zero" (947002)