The .NET Mobile generator allows you to synchronize a SQL Server CE database with a SQL Server 2000 database and .NET generator allows you to reorganize a published SQL Server 2000 database, while maintaining the publication. In this way, you can have two models in the same knowledge base: one for SQL Server and another for SQL Server CE. You can reorganize the published SQL Server database and then impact the SQL Server CE database to reorganize this subscribed database. You should always reorganize the SQL Server database before the SQL Server CE database.
Synchronizing databases
To synchronize a SQL Server CE database in a .NET Mobile model with a SQL Server database, you have to select and set the following model properties:
-Reorganize server tables: Yes (subscribed database). In this case, the reorganization only invokes the synchronize method to merge both databases (merge replication synchronizes data and schema).
-Publisher Internet URL: http://publisherName/sqlce/sscesa20.dll. sqlce virtual directory points to the SQL server publication snapshot folder. It is the URL used to connect to the SQL Server CE Server Agent.
-Publisher: publisherName. Specifies the name of the Publisher. The Publisher is the computer that is running SQL Server and that contains the publication.
-Publisher database: publisherDatabaseName. Specifies the name of the publication database.
-Publication name: myPublicationName. Specifies the publication name.
-Publisher Internet user name: myInternetUser. You need to set it if your sqlce virtual directory doesn't have Anonymous access checked. It is the login name used when connecting to the SQL Server CE Server Agent.
-Publisher Internet user password: myInternetPassword. You need to set it if your sqlce virtual directory doesn't have Anonymous access checked.
-Publisher user name: publisherUserName Specifies the login name used when connecting to the Publisher.
-Publisher user password: publisherUserPassword. Specifies the login password used when connecting to the Publisher.
Reorganizing a published database
To reorganize a published SQL Server 2000 database in a .NET model, select and set the following model properties:
-Reorganize server tables: Yes (published database). In this case, the reorganization removes part of the publication, executes the normal reorganization, and restores the removed publication parts.
-Distributor: myDistributorServer. It is the Distributor instance name, which can be the same at publisher server.
-Distributor user name: myDistributorUser. Specifies the login name used when connecting to the Distributor.
-Distributor user password: myDistributorPassword. Specifies the login password used when connecting to the Distributor.
How to synchronize databases manually
If you want to synchronize databases manually you can use the following functions:
The default values are: subscriber=Wrkst(), dataStoreName='Default'. To use these functions, set 'Allow non-standard functions when specifying' and 'reorganize server tables'=yes (subscribed database). The Synchronize() function synchronizes the database as does the reorganization with 'reorganize server tables'=yes (subscribed database). DropSubscription() deletes the subscription so that you can Reorganize the database normally.
- SQL Server SERVERNAME has to match the current Microsoft Windows NT machine name. At Enterprise Manager, the local instance name has to match the machine name. If it matches, go to step B; otherwise, remove the local instance and redefine it with the correct name.
- Check sp_helperserver at SQL Query Analyzer: it should return one row with id =0 and name= instance name created in step 1. Check 'select @@servername' at SQL Query Analyzer: if it returns the same instance name, go to step B; otherwise:
- drop the wrong instance name and add the correct name:
- sp_dropserver 'old_name', 'droplogins'
- go
- sp_addserver 'new_name', 'local'
- go
- Restart SQL server service and check 'select @@servername' again.
You can get more details about "Renaming a Server" at KbMicrosoft.
You create publications for SQL Server CE by using standard SQL Server replication tools and techniques. The easiest way to create a publication is by using the Create Publication Wizard (at Enterprise manager -> Tools -> Replication -> Create and Manage Publications).
It is important to select the following options:
- If this is the first publication created for this server, the Create Publication Wizard prompts you to configure the snapshot folder location. The snapshot folder holds the snapshot files that contain the schema and data for published tables. The SQL Server CE Replication Provider must be able to read these snapshot files so as to download the files to the Windows CE-based device. To select and configure the snapshot folder, see step C.
- Select Merge publication in the Select Publication Type page: Data can be updated at the Publisher or any Subscriber. Changes are merged periodically at the Publisher. This supports mobile, occasionally connected Subscribers.
- Select the Devices running SQL Server CE check box at Specify Subscriber Types Page. By selecting Devices running Windows CE, the following options are automatically configured for the publication:
- Support for anonymous subscriptions
- Character mode snapshot
- Select tables for publication at Specify Articles page. (You can change the target table name and define conflict resolution in this step). For autonumber columns on published articles you can check Automatically assign and maintain a unique identity range for each subscription at the "..." button -> Identity Range tab. See 'Replication Limitations.'
- Publication Name.
- Select "Yes, I will define data filters to enable anonymous subscriptions, or customize other properties" at Customize the properties of the publication page if you want to define vertical (columns, static) or horizontal filters.
Notes
- To delete publications: go to Enterprise Manager -> Tools -> Replication -> Configure Publishing, Subscribers, and Distribution -> Publication Databases
- Schema changes: When adding an article to a merge publication for which there are active subscriptions, you must run the Snapshot Agent after adding the article before any Subscribers can synchronize. If the publication already has subscriptions, Subscribers will receive the schema and data for the new article based on this snapshot the next time they synchronize. The Merge Agent will then synchronize any data changes for the subscription.
- To run the Snapshot Agent: go to Enterprise Manager -> Replication Monitor -> Publishers -> Publication -> Snapshot -> Start Agent
- Snapshot Folder. This directory will contain the Server Agent DLL (sscerp20.dll) which is an ISAPI DLL that will relay push/pull requests to SQL Server from our client (and sscesa20.dll). First, create a directory on the machine, for example C:\Inetpub\wwwroot\sqlce, share it and give everyone privileges to it, and place the DLL in it. You can find the DLLs in the following directory: C:\Program Files\Microsoft SQL Server CE 2.0\Server.
- Register the Server Agent DLL. Register the sscerp20.dll from START -> RUN -> Regsvr32 C:\Inetpub\wwwroot\sqlce\sscerp20.dll
- Create Virtual Directory Create a Virtual Directory in IIS to make it available to the public. This exposes the Server Agent to a web interface, which we will use to push/pull information to/from SQL Server. Check whether the virtual directory properties have selected Scripts and Executables at Execute Permissions and Anonymous access at Directory Security.
- Ranged identity columns. SQL Server CE identity columns must have a data type of integer (int 4). SQL Server CE identity columns cannot have a data type of bigint, smallint, tinyint, decimal, or numeric. If you subscribe to a publication having an identity column other than integer (int 4), the creation of that subscription fails on SQL Server CE, so the autonumber attributes defined in GeneXus must be N(x) type where 5<= x <=9.
- You can see more details about replication limitations at ReplicationLimitations
- Initializing SQL Server Reconciler has failed.
- http://support.microsoft.com/default.aspx?scid=kb;en-us;814853 (register the SQL Server CE Replication Provider DLL on the computer where SQL Server CE Server Tools is installed)
- Is your snapshot folder on your SQL Server a network share?
- Check whether the virtual directory anonymous user has permissions to access the publication. (Publication Access List at publication properties).
- A Request to send data to the computer running IIS has failed. The user's device must have network connectivity. For example, the device might not have a working modem, network card, or universal serial bus (USB) connection. Ensure that the computer running Microsoft Internet Information Services (IIS) is available, the web site is running, and that the ISP or network is functioning properly. Check if you can browse to http://192.168.0.15/winceok/sscesa20.dll using Pocket Internet Explorer. Try with IP instead of the machine name. Check configuration settings at Start -> Settings - > Connections -> Advanced -> Select Networks.
- Decompression failed: not enough memory at pocket pc, try again.
- An incorrect or unsupported HTTP function call was made. Ensure that the virtual directory is specified correctly and it has selected Scripts and Executables at Execute Permissions. Check if you can browse to http://192.168.0.15/winceok/sscesa20.dll using Pocket Internet Explorer.
- Authentication Failed: you have the Virtual Directory's Directory Security set on something other than "Anonymous Authentication."
- An error has occurred on the computer running IIS. The virtual directory doesn't have sscerp20.dll, or sscesa20.dll.
- The initial snapshot for publication <publication name> is not yet available: The snapshot agent wasn't successfully generated. See at Enterprise Manager -> Replication monitor -> Agents -> Snapshot Agents -> Last Action. Check the snapshot folder sharing permissions.
- You must rerun snapshot because current snapshot files are obsolete. You need to rerun the snapshot agent to create up to date snapshot files.
- "The table 'xxx' is already published as another article with a different automatic identity support option."
- Article 'xxx' cannot be dropped because there are other articles using it as a join article. First you have to delete "Table to Filter" and then "Filtered Table."
- 29045: The subscription to publication xxx is invalid: Drop the subscription and subscribe again.
- 29045: The initial snapshot for publication xxx is not yet available: run the snapshot agent at Enterprise Manager -> Replication Monitor -> Agents -> Snapshot Agents or at SQL Query Analyzer with the script: "C:\Program Files\Microsoft SQL Server\80\COM\SNAPSHOT.EXE" -Publisher <publishername> -PublisherDB <databasename> -Publication <pulbicationname> -ReplicationType 2 -DistributorLogin <user> -DistributorPassword <password>
- 28559: SQL Server CE encountered problems in opening the SQL Server CE database. 25035: There is a file sharing violation. A different process might be using the file.
- 12005 The column cannot contain null values.
- 28521: The SQL Server CE database is already enabled for replication. You have to reinitialize the subscription.
- -1: publication properties have not been specified (InternetURL, publicationName, publicationDatabase, etc).