Advice on developing a mobile app that uses SQL 2005 CE

Hi,

I am new to SQL 2005 CE but fairly experienced with SQL 2000 and know my way around SQL 2005. I am creating a PDA app that must support the following scenario:

My company has a SQL 2000 server with a "computer assets" database. This asset database must be able to be transferred to an .sdf file (SQL 2005 mobile) on the PDA when the user docks their PDA to the desktop PC (not the same machine as the SQL 2000 server). This transfer can be via an option in the mobile app that is initiated by the user, or automatic.

The mobile application basically supports stock-taking of the assets, so users go out on site and record information about the assets. This information is stored in a separate .sdf database on the PDA.

When the user gets back to the office they dock with their desktop and then must transfer the data from the second .sdf database onto the SQL 200 server. Lets call this the "asset metadata".

thats basically it! but im having issues finding the right process to attack the data transfer tasks.

My issues:

- Firstly, how do i populate the .sdf on the PDA for development purposes? Microsoft tutorials seem to be geared at SQL server 2005 management

studio only. I only have SQL server 2005 Management Studio Express on

my development machine as well as all the necessary SQL 2005 CE SDKs and VS2005. We

do not use SQL Server 2005 at my organisation as yet and it will be some time before we migrate across. In effect im looking for something that behaves like a DTS package to transfer data across.

- How to populate the "asset database" .sdf file from the SQL 2000 server when the user has docked to their desktop PC? (This needs to be refreshed )

- How to transfer the "asset metadata" from the .sdf on the PDA to the SQL server?

ive looked at merge replication a little but am not sure whether this is what i need and again most online information seem to be tailored to SQL Server 2005.

Can anyone kindly point me in the right direction?

I believe that merge replication will be the solution to your problems, as it will allow you to: Populate the device SDF, but for development and production purposes, and transfer inserted data from the device to the central SQL Server.

I would strongly recommend upgrading to SQL Server 2005, but SQL Server 2000 is a supported platform for merge synchronization with SQL 2005 CE.

The topics below this covers both SQL Server 2000 and 2005 configurations: http://msdn2.microsoft.com/en-us/library/ms171850.aspx

|||Hi Erik,

Thanks for your post. This article does indeed look like what i was searching for. ive been a bit busy on other work though and unable to dive into it thoroughly.

i will update once i have time to get back to the SQL mobile project again.|||

Hello again,


ive managed to setup everything as follows:

- created the publication for merge replication on the sql 2000 server.
- allowed the publication to have sql server CE subscrptions
- snapshot was created fine (at least sql server said everything was successful/)
- set up IIS for replication and this also went well. IIS is running on the same physical server as SQL 2000

So i then added some code to the applicaiton on my pocket PC, and it seems to be working except that the database on the local device doesnt seem to get any data added to it.

The following VB.Net code is what i am using (i have replaced server names etc with arbitrary values enclosed in < > for privacy reasons)

Try
' Set the Replication object.
repl = New SqlCeReplication()
repl.InternetUrl = "http://<myserver>/AssetSync/sqlcesa30.dll"
repl.Publisher = "<publisher name>"
repl.PublisherDatabase = "AssetRegister"
repl.PublisherLogin = "<login>"
repl.PublisherPassword = "<password>"
repl.Publication = "asset_list"
repl.SubscriberConnectionString = "Data Source=\Assets.sdf"
repl.Subscriber = "MySubscriber"

' Create the Local SSCE Database subscription.
repl.AddSubscription(AddOption.ExistingDatabase)

' Synchronize to the instance of SQL Server 2000 to populate the Subscription.
repl.Synchronize()

Catch ex As SqlCeException
' Use your own error handling routine to show error information.
outputLabel.Text = ex.ToString

Finally
outputLabel.Text = "Subscription Complete!"
' Dispose of the Replication object.
repl.Dispose()
End Try

There are no erros thrown and my application actually outputs "Subscription Complete!" .. but the database is not being populated. My VS2005 project has a blank Assets.sdf file in it.

i have also tried starting the app with no .sdf file and changing the subscription code to "repl.AddSubscription(AddOption.CreateDatabase)"

i am thinking there might be something wrong with the publication? but this behvaes the same - no database created.
|||

I would start by testing the publication from a desktop, namely via SQL Server 2005 management studio, or using this utility from Nick Randolph: http://www.softteq.com/tools/publish.htm

advice about a worm intrusion alert

XP Home, limited user account. Newbie to this group - I know next to
nothing about ports but am an experienced computer user otherwise.
Can anyone interpret this for me - just started to get these recently -
this is only the second one. Got it while using a user account in my XP
Home machine.
Security Alert - Medium Rick
Norton Internet Worm Protection has detected and blocked an intrusion
attempt.
The text in More Info was as follows:
Intrusion: MS SQL PacketResolution DoS
Intruder: 192.168.1.1 (domain(53))
Risk Level: Medium.
Protocol: UDP
Attacked IP: COMPUTER NAME (192.168.1.2)
Attacked Port: ms-sql-m(1434)
The Intruder address was my router, to which one Win98SE computer is
connected by ethernet (not mentioned in report) and the other on the
192.168.1.2 address is my XP Home machine, wirelessly connected to the
router.
I clicked OK and then the wireless connection lost its IP and
connectivity - and I had no internet access on the wireless XP machine.
Router was still connected to internet fine - all lights glowing properly.
Computer upstairs 192.168.1.3 was on and could connect to the internet -
no one was using it at the time of the alert. It has Zone Alarm free
version to prevent any outgoing stuff, and also NAV and Spybot S&D
resident (teatimer). It is on Win98SE. No alerts showing.
This machine runs Windows XP Home (user account) has NAV, Counterspy and
Zone Alarm free. Wireless network is WPA-PSK with 63 character pw.
Log off and on did not restore the wireless (always does usually).
Log off and then on to Admin acct - again wireless network did not work
but I got a windows error - windows is recovering from a serious error.
Still no connection.
Did a warm reboot and then everything was back to normal.
I do a Norton AV and Counterspy scan daily. Clear.
I think all the Windows/wireless hassle was due to the Norton blocking
the request, and I think the "intrusion" was legitimate - but I don't
want to "allow" it unless someone can explain the details to me. Many
thanks to any network gurus who can interpret please.
Rev Robert M Jones, Wimborne Baptist Church, UK
http://www.wimborne-baptist.org.uk
Free trial of Mailwasher Pro - effective email spam filter - (commission
goes to our partners in Bulgaria)
http://fta.firetrust.com/index.cgi?id=420Port 1434 is the SQL Browser service used for locating SQL Servers.
I would NOT allow Ports 1434 or 1433 to be open to the outside.
Is this a NAT router directly connected to your DSL/Cable modem?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robert M Jones" <robert53newsgroups-ms2@.NOSPAMyahoo.co.uk> wrote in message
news:uTWSOL$DHHA.4620@.TK2MSFTNGP04.phx.gbl...
> XP Home, limited user account. Newbie to this group - I know next to
> nothing about ports but am an experienced computer user otherwise.
> Can anyone interpret this for me - just started to get these recently -
> this is only the second one. Got it while using a user account in my XP
> Home machine.
> Security Alert - Medium Rick
> Norton Internet Worm Protection has detected and blocked an intrusion
> attempt.
> The text in More Info was as follows:
> Intrusion: MS SQL PacketResolution DoS
> Intruder: 192.168.1.1 (domain(53))
> Risk Level: Medium.
> Protocol: UDP
> Attacked IP: COMPUTER NAME (192.168.1.2)
> Attacked Port: ms-sql-m(1434)
> The Intruder address was my router, to which one Win98SE computer is
> connected by ethernet (not mentioned in report) and the other on the
> 192.168.1.2 address is my XP Home machine, wirelessly connected to the
> router.
> I clicked OK and then the wireless connection lost its IP and
> connectivity - and I had no internet access on the wireless XP machine.
> Router was still connected to internet fine - all lights glowing properly.
> Computer upstairs 192.168.1.3 was on and could connect to the internet -
> no one was using it at the time of the alert. It has Zone Alarm free
> version to prevent any outgoing stuff, and also NAV and Spybot S&D
> resident (teatimer). It is on Win98SE. No alerts showing.
> This machine runs Windows XP Home (user account) has NAV, Counterspy and
> Zone Alarm free. Wireless network is WPA-PSK with 63 character pw.
> Log off and on did not restore the wireless (always does usually).
> Log off and then on to Admin acct - again wireless network did not work
> but I got a windows error - windows is recovering from a serious error.
> Still no connection.
> Did a warm reboot and then everything was back to normal.
> I do a Norton AV and Counterspy scan daily. Clear.
> I think all the Windows/wireless hassle was due to the Norton blocking the
> request, and I think the "intrusion" was legitimate - but I don't want to
> "allow" it unless someone can explain the details to me. Many thanks to
> any network gurus who can interpret please.
> --
> Rev Robert M Jones, Wimborne Baptist Church, UK
> http://www.wimborne-baptist.org.uk
> Free trial of Mailwasher Pro - effective email spam filter - (commission
> goes to our partners in Bulgaria)
> http://fta.firetrust.com/index.cgi?id=420|||Arnie Rowland wrote:
> Port 1434 is the SQL Browser service used for locating SQL Servers.
> I would NOT allow Ports 1434 or 1433 to be open to the outside.
> Is this a NAT router directly connected to your DSL/Cable modem?
>
Thanks for the reply. This is all a mystery to me.
Set up is an ADSL Router with NATS firewall incorporated. I have Skype
if that is relevant - the entry for that against its icon in Zone Alarm
is "Listening to Port(s) TCP:80,443,14695"
The router is set with IP Filtering enabled, for filtering inbound
traffic - there are no entries in the table in that section.
The section on Virtual Server Configuration DMZ host has:
"Those IP packets from the Internet that do NOT belong to any
applications configured in the port forwarding table will be: Discarded"
There is nothing set up in the port forwarding section
Any more checking I should do? The router NATS seems to do its job in
terms of the Shields Up tests, but I haven't then disabled the NATS to
test the actual ZA software firewall on the machine itself.
Rev Robert M Jones, Wimborne Baptist Church, UK
http://www.wimborne-baptist.org.uk
Free trial of Mailwasher Pro - effective email spam filter - (commission
goes to our partners in Bulgaria)
http://fta.firetrust.com/index.cgi?id=420|||>>> On 11/24/2006 at 11:03 AM, in message
<uTWSOL$DHHA.4620@.TK2MSFTNGP04.phx.gbl>, Robert M
Jones<robert53newsgroups-ms2@.NOSPAMyahoo.co.uk> wrote:
> Security Alert - Medium Rick
> Norton Internet Worm Protection has detected and blocked an
> intrusion
> attempt.
> The text in More Info was as follows:
> Intrusion: MS SQL PacketResolution DoS
> Intruder: 192.168.1.1 (domain(53))
> Risk Level: Medium.
> Protocol: UDP
> Attacked IP: COMPUTER NAME (192.168.1.2)
> Attacked Port: ms-sql-m(1434)
Do you even have SQL installed on your machine? My guess is that you
don't.
As a result, port 1434 is not used by any specific program, but is
available for any program that needs a new UDP port to use.
Because of this, the DNS resolver is using it to make a DNS request
(your name server is probably set as 192.168.1.1). Your DNS server
responds to port 1434. However, Norton incorrectly classifies this as
an attack. It probably isn't.|||Joel Maslak wrote:
> <uTWSOL$DHHA.4620@.TK2MSFTNGP04.phx.gbl>, Robert M
> Jones<robert53newsgroups-ms2@.NOSPAMyahoo.co.uk> wrote:
> Do you even have SQL installed on your machine? My guess is that you
> don't.
> As a result, port 1434 is not used by any specific program, but is
> available for any program that needs a new UDP port to use.
> Because of this, the DNS resolver is using it to make a DNS request
> (your name server is probably set as 192.168.1.1). Your DNS server
> responds to port 1434. However, Norton incorrectly classifies this as
> an attack. It probably isn't.
That's sort of what I thought - but until I can be sure I did not want
to give Norton any instructions to allow or remember - just saying "ok"
when I get the block message.
Any advice on checking I can do (other than routine AV and spyware
scans) most welcome.
Rev Robert M Jones, Wimborne Baptist Church, UK
http://www.wimborne-baptist.org.uk
Free trial of Mailwasher Pro - effective email spam filter - (commission
goes to our partners in Bulgaria)
http://fta.firetrust.com/index.cgi?id=420sql