Answered by:
TIME-DataType, SqlServer2008 and Sql Server Compact 3.5 with Sync Framework 2.1

Question
-
Hello,
I want to synchronize some tables from my Sql Server 2008 to some clients with Sql Compact 3.5.
But there is a problem: It rises an exception.
The datatype TimeSpan (Time(0)) from Sql Server 2008 can't be converted to sql compact.
It's really urgent.Thanks for your help.
Ronny - From Germany ;)
Wednesday, March 16, 2011 2:41 PM
Answers
-
Hello,
i've solve the problem and now I just singing a song for me :)
It's a little-bit dirty but it works.
I've added a not computed nullable datetime-column like "ConvertedFrom"
and then I've added a Trigger for Update and Insert.UPDATE [dbo].[ScheduleItem]
SET [ConvertedFrom]=CAST([From] AS DATETIME)
WHERE [ID] IN ( SELECT [ID] FROM INSERTED )For the current rows I've executed a little sql-update-action,
And know the synchronization works.
I have removed the "From"-column from the server-provision and now I've only the ConvertedFrom-DateTime-Helper-Column.Thanks for your Help! :)
Bye, Ronny- Marked as answer by jigu2014Microsoft employee, Editor Wednesday, April 6, 2011 5:03 PM
Wednesday, March 23, 2011 7:53 AM
All replies
-
what is the data type specified in your sql compact?Wednesday, March 16, 2011 2:54 PM
-
what is the data type specified in your sql compact?
Hi,
I make a client provision with SqlCeSyncScopeProvisioning.
It automaticly creates the compact-db.
The created datatype in the compact-db is varchar. But that's not what I need.
I'd like to convert sql server 2008 -> Time-Datatype to Sql Compact -> DateTime-Datatype in sql compact.
Because the time-datatype is not support in sql compact.(Time(0)) 7:00:00 -> ( DateTime) 7:00:00 1/1/1900 or somthing like that.
How to convert or anything else?
Thanks
Tuesday, March 22, 2011 9:59 AM -
you can specify the the datatype for a column using the following approach:
DbSyncTableDescription newTableDescription = new DbSyncTableDescription("Sales.NewTable"); DbSyncColumnDescription newTableIdCol = new DbSyncColumnDescription(); DbSyncColumnDescription newTableContentCol = new DbSyncColumnDescription(); newTableIdCol.UnquotedName = "NewTableId"; newTableIdCol.Type = "int"; newTableIdCol.IsPrimaryKey = true; newTableContentCol.UnquotedName = "NewTableContent"; newTableContentCol.Type = "nvarchar"; newTableContentCol.Size = "100"; newTableContentCol.IsPrimaryKey = false; newTableDescription.Columns.Add(newTableIdCol); newTableDescription.Columns.Add(newTableContentCol); scopeDesc.Tables.Add(newTableDescription);
Tuesday, March 22, 2011 10:19 AM -
I have changed the Type from "Time" to "DateTime" but it rises an exception on Synchronize.
Exception: {"Es ist keine Zuordnung vom DbType-Wert System.TimeSpan zu einem bekannten Wert für SqlCeType vorhanden."}
In English: There is no assignment of DbType value System.TimeSpan available at a known value for SqlCeType.
// Server-Provision
using ( var con = new SqlConnection ( connectionString ) ) { var readOnlyScope = new DbSyncScopeDescription ( "TerminalReadOnlyScope" ); var scheduleItemTable = SqlSyncDescriptionBuilder.GetDescriptionForTable ( "ScheduleItem", con ); scheduleItemTable.Columns["From"].Type = "datetime"; // Before this, the type was "Time" readOnlyScope.Tables.Add(scheduleItemTable); readOnlyScope.Apply ( ); }
// Client-Provision
using ( var serverConn = new SqlConnection ( serverConnectionString ) ) { using ( var clientConn = new SqlCeConnection ( clientConnectionString ) ) { var readOnlyScope = SqlSyncDescriptionBuilder.GetDescriptionForScope ( "TerminalReadOnlyScope", serverConn ); var readOnlyProvision = new SqlCeSyncScopeProvisioning ( clientConn, readOnlyScope ); readOnlyProvision.Apply ( ); } }
// Synchronization
using ( var serverConn = new SqlConnection ( serverConnectionString ) ) { using ( var clientConn = new SqlCeConnection ( clientConnectionString ) ) { var readOnlySync = new SyncOrchestrator ( ); readOnlySync.LocalProvider = new SqlCeSyncProvider ( "TerminalReadOnlyScope", clientConn ); readOnlySync.RemoteProvider = new SqlSyncProvider ( "TerminalReadOnlyScope", serverConn ); readOnlySync.Direction = SyncDirectionOrder.Download; readOnlySync.Synchronize ( ); } }
Tuesday, March 22, 2011 11:01 AM -
i actually meant changing the data type on the sql ce side, not on the sql server side.
have you tried mapping it to a nvarchar(16)?
Tuesday, March 22, 2011 2:08 PM -
Hello,
with nvarchar, it works. But I need to calculate with it.
I've a complex Linq to Entities query and there is no
supported function to convert the nvarchar to DateTime in SqlCe !?The second way I've tried, is to add a computed column to sql server, like this, -> ComputedFrom = CAST ( 'From' AS DATETIME ),
but if i apply the server provision, it rises an exception.-> "'ComputedFrom'-Column can not be changed because it is a computed column or because it is the result of a UNION operator"
It also does not work. :(
Tuesday, March 22, 2011 2:24 PM -
sorry, i've tried with nchar(16) again, it doesn't work. It rises an ArgumentException on Synchronize().
Exception: "There is no assignment of DbType value System.TimeSpan available at a known value for SqlCeType"
It is really really urgent and I will sing a song for everyone who solve the problem :)
Tuesday, March 22, 2011 2:50 PM -
if you're fine with storing it as varchar on the CE side and just manipulating it back when you do your calculation, provision your server as it is, provision your client specifying the column type as nvarchar(16), then subscribe to the server's ChangesSelected event, then manually assign the time column to the varchar column then remove it.
static void ServerChangesSelected(object sender, DbChangesSelectedEventArgs e) { //let's check if we're synching the table we're interested if (e.Context.DataSet.Tables.Contains("testtable2")) { var dataTable = e.Context.DataSet.Tables["testtable2"]; //let's add the new column dataTable.Columns.Add(new DataColumn("timevarchar")); foreach (DataRow row in dataTable.Rows) { // if it's an update or an insert, assign the time column to the varchar column if (row.RowState == DataRowState.Modified || row.RowState == DataRowState.Added) { row["timevarchar"] = row["timecolumn"]; } } var columns = e.Context.DataSet.Tables["testtable2"].Columns; //remove the time column columns.Remove("timecolumn"); } }
Tuesday, March 22, 2011 3:31 PM -
Sorry it's not working. The ChangesSelected-Event was not triggered.
It failes on Synchronize with the above Exception. "There is not assignment of DbType value System.TimeSpan...."
It only works if I remove the Time-Datatype-Column 'From' from the Server-Provision.
It would satisfy me even if I could synchronize the computed DateTime-helper-column. As I previously descriped.Wednesday, March 23, 2011 7:13 AM -
Hello,
i've solve the problem and now I just singing a song for me :)
It's a little-bit dirty but it works.
I've added a not computed nullable datetime-column like "ConvertedFrom"
and then I've added a Trigger for Update and Insert.UPDATE [dbo].[ScheduleItem]
SET [ConvertedFrom]=CAST([From] AS DATETIME)
WHERE [ID] IN ( SELECT [ID] FROM INSERTED )For the current rows I've executed a little sql-update-action,
And know the synchronization works.
I have removed the "From"-column from the server-provision and now I've only the ConvertedFrom-DateTime-Helper-Column.Thanks for your Help! :)
Bye, Ronny- Marked as answer by jigu2014Microsoft employee, Editor Wednesday, April 6, 2011 5:03 PM
Wednesday, March 23, 2011 7:53 AM -
Sorry it's not working. The ChangesSelected-Event was not triggered.
It failes on Synchronize with the above Exception. "There is not assignment of DbType value System.TimeSpan...."
It only works if I remove the Time-Datatype-Column 'From' from the Server-Provision.
It would satisfy me even if I could synchronize the computed DateTime-helper-column. As I previously descriped.
are you subscribing to the provider that has the Time column? i have this working even on bidirectional sync with time to datetime mapping and even vice versa.Wednesday, March 23, 2011 8:01 AM -
Then I do something wrong.
Can You please post a sample for synching a time to datetime-column?
I subscribe the time-column, on server-provision, if i make no changes on the server-provision
the client provsision will automaticly create a sql compact db with a nchar(16) for the time-column.
But I cannot sync. It rises an exception. Above descriped.Wednesday, March 23, 2011 8:17 AM -
i'll post the code when i get home. am assuming when casting time to datetime, you're just going to use the time part of the datetime right?Wednesday, March 23, 2011 8:22 AM
-
yes, that's right. thanks ;)Wednesday, March 23, 2011 8:25 AM
-
last question, do you require the column name to be the same on both client and server?Wednesday, March 23, 2011 8:43 AM
-
No Problem on Client-Side. On Server-Side I cannot change the ColumnName oder DataType. :)
Wednesday, March 23, 2011 9:04 AM -
here you go
static void Main(string[] args) { //setup the server connection var serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=Aggregator; Integrated Security=True"); // define a new scope named TimeTest var scopeDesc = new DbSyncScopeDescription("TimeTest"); // get the description of the TestTable table from the server database var tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("TestTable2", serverConn); // add the table description to the sync scope definition scopeDesc.Tables.Add(tableDesc); // set scope to be provisioned based on the scope definition var serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc); if (!serverProvision.ScopeExists("TimeTest")) { //apply the scope definition serverProvision.Apply(); } //setup the client connection var clientConn = new SqlConnection("Data Source=localhost; Initial Catalog=Client1; Integrated Security=True"); // retrieve scope definition from server var clientScope = SqlSyncDescriptionBuilder.GetDescriptionForScope("TimeTest", serverConn); // lets change the column with time data type to a datetime column on the client clientScope.Tables["TestTable2"].Columns["timecolumn"].Type = "datetime"; // set scope to be provisioned based on the scope definition var clientProvision = new SqlSyncScopeProvisioning(clientConn, clientScope); if (!clientProvision.ScopeExists("TimeTest")) { //apply the scope definition clientProvision.Apply(); } // create the sync orchestrator var syncOrchestrator = new SyncOrchestrator(); // set local provider of orchestrator syncOrchestrator.LocalProvider = new SqlSyncProvider("TimeTest", clientConn); // set the remote provider of orchestrator syncOrchestrator.RemoteProvider = new SqlSyncProvider("TimeTest", serverConn); // set the direction of sync session to Download syncOrchestrator.Direction = SyncDirectionOrder.Download; // subscribe to changes selected event of the remote provider (server with time column) ((SqlSyncProvider)syncOrchestrator.RemoteProvider).ChangesSelected += ServerChangesSelected; // execute the synchronization process var syncStats = syncOrchestrator.Synchronize(); // print statistics Console.WriteLine("Start Time: " + syncStats.SyncStartTime); Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal); Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal); Console.WriteLine("Complete Time: " + syncStats.SyncEndTime); Console.WriteLine(String.Empty); Console.ReadKey(); } static void ServerChangesSelected(object sender, DbChangesSelectedEventArgs e) { //let's check if we're synching the table we're interested if (e.Context.DataSet.Tables.Contains("testtable2")) { var dataTable = e.Context.DataSet.Tables["testtable2"]; //rename the old column (we cant change the column's datatype if it has data) dataTable.Columns["timecolumn"].ColumnName = "OldTimeColumn"; //let's add the new column having the name of the original column but with a datetime type dataTable.Columns.Add(new DataColumn("timecolumn", typeof(DateTime))); foreach (DataRow row in dataTable.Rows.Cast<DataRow>().Where(row => row.RowState == DataRowState.Modified || row.RowState == DataRowState.Added)) { //convert the time data to datetime row["timecolumn"] = DateTime.Parse(row["oldtimecolumn"].ToString()); } var columns = e.Context.DataSet.Tables["testtable2"].Columns; //remove the time column columns.Remove("oldtimecolumn"); } }
Wednesday, March 23, 2011 10:41 AM -
Hello,
thanks for your code :)
But it doesn't work on my pc :(// lets change the column with time data type to a datetime column on the client clientScope.Tables["TestTable2"].Columns["timecolumn"].Type = "datetime";
Und Synchronize - It rises an exception. Above descriped.
On Client-Side, I've Sql Compact 3.5 SP2 and Windows 7 SP1, VS2010 and on Server-Side,
there is Windows Server 2008 R2 and Sql Server 2008,
It works with my dirty code.
Do not worry anymore.Bye :)
Thursday, March 24, 2011 12:21 PM