Result set differences in a SQL Server Compact database PC vs Mobile device
up vote
0
down vote
favorite
Interesting problem: I have a SQL Server CE 3.5 database with some data in it. I run a query on the database, using a mobile device and obtain a result set.
This works fine 99% of the time, but on occasion I get records in the database where the query returns an empty result set.
If I take a copy of the same database file from my mobile device and connect to it with Query analyzer, then run the exact same query (as copied/pasted from the debugger), the query returns records. The query itself does a JOIN and GROUP BY on two tables by a referential identity key field.
Now if I make a clone the same records involved via a series of:
INSERT INTO MyTable (EntireFieldListExceptForIDKey)
SELECT
(EntireFieldListExceptForIDKey)
FROM
MyTable
WHERE
IDKey = Original
The query is now able to correctly assemble a result set on the cloned records on the mobile device.
Can anyone explain this, and possibly how to detect/overcome?
sql sql-server-ce
edited Nov 19 at 19:42
marc_s
568k12810991249
asked Nov 19 at 18:16
Dave Monkman
1
add a comment |
up vote
0
down vote
favorite
Interesting problem: I have a SQL Server CE 3.5 database with some data in it. I run a query on the database, using a mobile device and obtain a result set.
This works fine 99% of the time, but on occasion I get records in the database where the query returns an empty result set.
If I take a copy of the same database file from my mobile device and connect to it with Query analyzer, then run the exact same query (as copied/pasted from the debugger), the query returns records. The query itself does a JOIN and GROUP BY on two tables by a referential identity key field.
Now if I make a clone the same records involved via a series of:
INSERT INTO MyTable (EntireFieldListExceptForIDKey)
SELECT
(EntireFieldListExceptForIDKey)
FROM
MyTable
WHERE
IDKey = Original
The query is now able to correctly assemble a result set on the cloned records on the mobile device.
Can anyone explain this, and possibly how to detect/overcome?
sql sql-server-ce
edited Nov 19 at 19:42
marc_s
568k12810991249
asked Nov 19 at 18:16
Dave Monkman
1
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Interesting problem: I have a SQL Server CE 3.5 database with some data in it. I run a query on the database, using a mobile device and obtain a result set.
This works fine 99% of the time, but on occasion I get records in the database where the query returns an empty result set.
If I take a copy of the same database file from my mobile device and connect to it with Query analyzer, then run the exact same query (as copied/pasted from the debugger), the query returns records. The query itself does a JOIN and GROUP BY on two tables by a referential identity key field.
Now if I make a clone the same records involved via a series of:
INSERT INTO MyTable (EntireFieldListExceptForIDKey)
SELECT
(EntireFieldListExceptForIDKey)
FROM
MyTable
WHERE
IDKey = Original
The query is now able to correctly assemble a result set on the cloned records on the mobile device.
Can anyone explain this, and possibly how to detect/overcome?
sql sql-server-ce
edited Nov 19 at 19:42
marc_s
568k12810991249
asked Nov 19 at 18:16
Dave Monkman
1
Interesting problem: I have a SQL Server CE 3.5 database with some data in it. I run a query on the database, using a mobile device and obtain a result set.
This works fine 99% of the time, but on occasion I get records in the database where the query returns an empty result set.
If I take a copy of the same database file from my mobile device and connect to it with Query analyzer, then run the exact same query (as copied/pasted from the debugger), the query returns records. The query itself does a JOIN and GROUP BY on two tables by a referential identity key field.
Now if I make a clone the same records involved via a series of:
INSERT INTO MyTable (EntireFieldListExceptForIDKey)
SELECT
(EntireFieldListExceptForIDKey)
FROM
MyTable
WHERE
IDKey = Original
The query is now able to correctly assemble a result set on the cloned records on the mobile device.
Can anyone explain this, and possibly how to detect/overcome?
sql sql-server-ce
sql sql-server-ce
edited Nov 19 at 19:42
marc_s
568k12810991249
asked Nov 19 at 18:16
Dave Monkman
1
edited Nov 19 at 19:42
marc_s
568k12810991249
asked Nov 19 at 18:16
Dave Monkman
1
edited Nov 19 at 19:42
marc_s
568k12810991249
edited Nov 19 at 19:42
marc_s
568k12810991249
edited Nov 19 at 19:42
marc_s
568k12810991249
568k12810991249
asked Nov 19 at 18:16
Dave Monkman
1
asked Nov 19 at 18:16
Dave Monkman
1
asked Nov 19 at 18:16
Dave Monkman
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
This is most likely due to a corrupt index, as both copying the file to your desktop and creating a new table will cause an Index rebuild.
It is recommended to regularly Compact your database to prevent this - also make sure you are using the latest runtime binaries.
answered Nov 19 at 19:13
ErikEJ
30.9k24884
k gotcha, I open with a shrink, which I had assumed was sufficient.
– Dave Monkman
Nov 19 at 19:30
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
This is most likely due to a corrupt index, as both copying the file to your desktop and creating a new table will cause an Index rebuild.
It is recommended to regularly Compact your database to prevent this - also make sure you are using the latest runtime binaries.
answered Nov 19 at 19:13
ErikEJ
30.9k24884
k gotcha, I open with a shrink, which I had assumed was sufficient.
– Dave Monkman
Nov 19 at 19:30
add a comment |
up vote
0
down vote
This is most likely due to a corrupt index, as both copying the file to your desktop and creating a new table will cause an Index rebuild.
It is recommended to regularly Compact your database to prevent this - also make sure you are using the latest runtime binaries.
answered Nov 19 at 19:13
ErikEJ
30.9k24884
k gotcha, I open with a shrink, which I had assumed was sufficient.
– Dave Monkman
Nov 19 at 19:30
add a comment |
up vote
0
down vote
up vote
0
down vote
This is most likely due to a corrupt index, as both copying the file to your desktop and creating a new table will cause an Index rebuild.
It is recommended to regularly Compact your database to prevent this - also make sure you are using the latest runtime binaries.
answered Nov 19 at 19:13
ErikEJ
30.9k24884
This is most likely due to a corrupt index, as both copying the file to your desktop and creating a new table will cause an Index rebuild.
It is recommended to regularly Compact your database to prevent this - also make sure you are using the latest runtime binaries.
answered Nov 19 at 19:13
ErikEJ
30.9k24884
answered Nov 19 at 19:13
ErikEJ
30.9k24884
answered Nov 19 at 19:13
ErikEJ
30.9k24884
answered Nov 19 at 19:13
ErikEJ
30.9k24884
30.9k24884
k gotcha, I open with a shrink, which I had assumed was sufficient.
– Dave Monkman
Nov 19 at 19:30
add a comment |
k gotcha, I open with a shrink, which I had assumed was sufficient.
– Dave Monkman
Nov 19 at 19:30
k gotcha, I open with a shrink, which I had assumed was sufficient.
– Dave Monkman
Nov 19 at 19:30
k gotcha, I open with a shrink, which I had assumed was sufficient.
– Dave Monkman
Nov 19 at 19:30
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380485%2fresult-set-differences-in-a-sql-server-compact-database-pc-vs-mobile-device%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
