Hi All,
I need to update a table on a server from my PDA, so what I am doing is the
following:
I pull the table to the PDA via RDA Pull. Tracking is ON and the table has
an autoincrement primary key as well. All I want to do is add to this table
the data captured by my PDA (so I am not interested in any data coming from
the server thus I am using a filter to bring an effectively empty table..)
Then, I copy all the data from my local table to this newly pulled table ...
I do an INSERT INTO Table (col1, col2, ..) select col1, col2, ... FROM the
local PDA table.. My records get copied and now I am ready to send them via
a PUSH. The problem is of course that since my newly pulled table didn't
have any records to start with, its primary key starts to increment from
1... and when I do a PUSH there is a problem because such a primary key w/ a
value of 1 already exists on the table on the server side... so in order to
force it to count from where it should, I did the following:
Instead of pulling an empty table from the server, I pull the last record
in.. I just do this in my PULL call: select * from server-table where pkey =
(select max(pkey) from server-table).. where pkey is the primary key. This
will give me the last row (the highest value of the primary key). Then, I do
the copy from the local PDA table to this newly pulled table so its primary
key instead of incremeting from 1, it increments from where it should...
Then, I do a PUSH...
Is there a better approach for this kind of situation? Anyway, I have
problems with this approach because in the future I may have multiple PDAs
that want to send their local data to the server table and then it will be a
primary key mess..
I would be really grateful if you know of a better way or at least to
confirm that this is a reasonsable way of doing things,
Thanks!
you can try to manage identity columns in an RDA architecture, but as
you said, it gets very difficult across a large pool of mobile users. you'd
be better off using a GUID and the NewID() function as your primary
key on this table. then you don't have to worry about ranges and conflicts.
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
"vvf" <novvfspam@.hotmail.com> wrote in message
news:e5gz%23chyFHA.720@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I need to update a table on a server from my PDA, so what I am doing is
> the
> following:
> I pull the table to the PDA via RDA Pull. Tracking is ON and the table has
> an autoincrement primary key as well. All I want to do is add to this
> table
> the data captured by my PDA (so I am not interested in any data coming
> from
> the server thus I am using a filter to bring an effectively empty table..)
> Then, I copy all the data from my local table to this newly pulled table
> ...
> I do an INSERT INTO Table (col1, col2, ..) select col1, col2, ... FROM the
> local PDA table.. My records get copied and now I am ready to send them
> via
> a PUSH. The problem is of course that since my newly pulled table didn't
> have any records to start with, its primary key starts to increment from
> 1... and when I do a PUSH there is a problem because such a primary key w/
> a
> value of 1 already exists on the table on the server side... so in order
> to
> force it to count from where it should, I did the following:
> Instead of pulling an empty table from the server, I pull the last record
> in.. I just do this in my PULL call: select * from server-table where pkey
> =
> (select max(pkey) from server-table).. where pkey is the primary key.
> This
> will give me the last row (the highest value of the primary key). Then, I
> do
> the copy from the local PDA table to this newly pulled table so its
> primary
> key instead of incremeting from 1, it increments from where it should...
> Then, I do a PUSH...
> Is there a better approach for this kind of situation? Anyway, I have
> problems with this approach because in the future I may have multiple PDAs
> that want to send their local data to the server table and then it will be
> a
> primary key mess..
> I would be really grateful if you know of a better way or at least to
> confirm that this is a reasonsable way of doing things,
> Thanks!
>
>
|||> you can try to manage identity columns in an RDA architecture, but as
> you said, it gets very difficult across a large pool of mobile users.
> you'd
> be better off using a GUID and the NewID() function as your primary
> key on this table. then you don't have to worry about ranges and
> conflicts.
The problem is that it is not to fun to maintain a databas where primary
keys are GUID.
select * from Customer
where CustId = 123A224D-916E-40DA-B159-6E1E399D4A50
Ola Ekelund
SoftConsult, SWEDEN
|||Hi,
"Darren Shaffer" <darrenshaffer@.discussions.microsoft.com> wrote in message
news:#eM6$viyFHA.2516@.TK2MSFTNGP12.phx.gbl...
> you can try to manage identity columns in an RDA architecture, but as
> you said, it gets very difficult across a large pool of mobile users.
you'd
> be better off using a GUID and the NewID() function as your primary
> key on this table. then you don't have to worry about ranges and
conflicts.
Thanks for the answer. Copying from the local PDA table to the newly pulled
table should be done via a stored procedure right? (to improve
performance)... that's just because I always have to do the INSERT INTO...
SELECT FROM.
The other question that I have is: Let's say I have two PDAs that are trying
to do a PUSH. The first PDA does the PUSH first, and thus SQL Server is busy
receiving the PUSH from the first PDA. Meanwhile, before this PUSH is over,
the second PDA is attempting a PULL and then a PUSH... would a second PUSH
be a problem because the SQL Server would be busy "processing" the first
PUSH from the first PDA? Or should it just spawn a different thread and
server the second PUSH as well even though the first PUSH has not completed
yet?
Thanks!
