sql server replication: Hello,

I posted a question yesterday and got no response. I
will try to post the question in a different way and make
it more clear.

I am trying to create a merge subscription on a Windows
CE.Net device. I am using SQL Server 2000 sp3a and SQL
Server CE 2.0 sp3. My IIS connection is fine. Using IE,
I can receive the proper message from the Server Agent on
both the IIS computer as well as the CE.NET device. The
code I am using to create the subscription is as follows:

Dim oRepl As New SqlCeReplication
' Set the values for the CE replication object
oRepl.Publisher = "Fstest"
oRepl.PublisherDatabase = "fsdb"
oRepl.Publication = "USA_MPOS2"
oRepl.PublisherLogin = "sa"
oRepl.PublisherPassword = "sapassword"
oRepl.SubscriberConnectionString = _
"Provider=Microsoft.SQLServer.OLEDB.CE.2.0; "
& _
"Data Source=\fsdb.sdf"

oRepl.Subscriber = "USA_MPOS2"

oRepl.InternetUrl
= "http://PPP_PEER/USA_MPOS2/sscesa20.dll"
' No Internet login or password since it is an
anonymous subscription

' Make sure we get updates as well as send them
oRepl.ExchangeType = ExchangeType.BiDirectional

Try
' If the local DB exists, then just synch to
it
If Not System.IO.File.Exists( _
"\fsdb.sdf") Then

' Tell SQL CE to create the database
oRepl.AddSubscription
(AddOption.CreateDatabase)

End If
' Now synchronize with the current snapshot
' in Merge Replication

oRepl.Synchronize()
MessageBox.Show("Replication has successfully
completed")
Catch ex As SqlCeException
'TODO: Add Error handling code here
Call ShowErrors(ex)
End Try

' Cleanup
oRepl.Dispose()

The code runs on the CE device. It creates a new
database, creates an anonymous merge subscription on the
server (which I can see), but when it begins to
synchronize, these are the three error messages in order
that I get.

Error code: 80004005

Minor err: 28557
Source: Microsoft SQL Server 2000 Windows CE Edition
Error par: data source = \fsdb.sdf;provider =
Microsoft.SQLServer.OLEDB.CE.2.0

Minor err: 28560
Message: The OLE DB Execute method failed. The SQL
statement is not valid. [,,,SQL Statement,,]

Minor err: 25082
Message: Unique indexes and constraints cannot be created
on columns that will allow null values [,,,,,]

I must say that I suspect the schema file that the
Snapshot Agent produced. The first schema file that is
pulled across has the following content:

drop table [dbo].[cash_status]
GO
CREATE TABLE [dbo].[cash_status] (
[activity_code] [nvarchar] (50) NOT NULL ,
[meal_date] [datetime] NULL ,
[meal_id] [nvarchar] (50) NULL ,
[station_id] [int] NULL ,
[start_bank] [numeric](7, 2) NULL ,
[food_cost] [numeric](7, 2) NULL ,
[operating_cost] [numeric](7, 2) NULL ,
[drawer_total] [numeric](7, 2) NULL ,
[over_short] [numeric](7, 2) NULL ,
[remarks] [nvarchar] (78) NULL ,
[file_id] [int] NULL CONSTRAINT
[DF_cash_status_file_id] DEFAULT (0),
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
CONSTRAINT [DF__cash_stat__rowgu__0BAD2365] DEFAULT (newid
())
)
GO

I have been stumped by this for several days. I am in a
bind and would appreciate any help that anyone could
provide.

Thanks,

Marv Jones
..

Marv
I have only run this type of scenarion using the CE emulator in the VS envivonment, and haven't seen this error. As far as I can see from my limited experience, the schema of the cash_status table shouldn't cause any problems. I think the problem lies with another article - the first error mentions a problem with a unique constraint and SQL CE is different from SQL Server in this respect as SQL Server will allow a single null in a column with a unique constraint, however CE won't. Perhaps this is the problem. I'd check the other snapshot files for the one with the unique constraint and look to see if the table column is nullable, which is documented as causing failure. If you haven't already done so then 'Enabling Logging by the SQL Server CE Server Agent' might give a bit more info
Regards

Paul,

Thanks for the response. I have never tried to run it on
an emulator. Seems like it should work the same.

Marv
[quoted text, click to view]
emulator in the VS envivonment, and haven't seen this
error. As far as I can see from my limited experience, the
schema of the cash_status table shouldn't cause any
problems. I think the problem lies with another article -
the first error mentions a problem with a unique
constraint and SQL CE is different from SQL Server in this
respect as SQL Server will allow a single null in a column
with a unique constraint, however CE won't. Perhaps this
is the problem. I'd check the other snapshot files for the
one with the unique constraint and look to see if the
table column is nullable, which is documented as causing
failure. If you haven't already done so then 'Enabling
Logging by the SQL Server CE Server Agent' might give a
bit more info.
[quoted text, click to view]