MS SQLCE - Problem with Inserts/Deletes to tables in pulled stored procedures

Author

Problem with Inserts/Deletes to tables in pulled stored procedures

Scott Bradley

2005-06-21, 8:23 pm

This is related to a question which was discussed previously but never
answered (see "Problem with Push Method" thread from 4/15/2005 - not the
first couple of posts but the last few)

In the lower posts of that thread, the report was of a problem using
temporary tables. I have been having similar problems to what was reported
but after doing some investigation and numerous tests, believe the problem is
a lot more severe than that and isn't just limited to temp tables.

The problem seems to be that performing an insert into a table (temporary or
normal) at any point in a stored procedure before you return the resultset
causes the RDA Pull to not create the table. This also seems to be the case
when performing a "delete from xxxx"

Therefore, I could have a simple stored proc containing the following code :-
---------------------------
Select * from TestTable

Insert into StatusTest
Values(1)
---------------------------
This sp would run fine in query analyser and would return results from
TestTable and then perform the insert. When run through RDA Pull this would
result in a table containing the contents of TestTable being created on the
device. The insert would also run fine and insert into the server side table
StatusTest etc.

However, changing the Stored Proc to be :-
---------------------------
Insert into StatusTest
Values(1)

Select * from TestTable

Insert into StatusTest
Values(1)
---------------------------
This again will also run fine in Query Analyser. However, calling the sp
through RDA Pull results in no table being created by the Pull and also no
errors. The insert statements would both still get executed though, proving
that the stored procedure still runs through to completion.

A similar failure to create a table would also occur if I changed the first
insert to be something like :-
Delete from TestTable
(The table would be created ok if this was changed to Truncate Table
TestTable suggesting that it is the Delete statement it doesn't like)

Note: In the above examples, the tables shown are not important. They are
simple tables just used for example purposes and so there are no issues with
constraints etc. The same results occur regardless of tables

In short, there appears to be a problem returning a resultset AFTER any
inserts/deletions to tables (either temp or normal). Returning a resultset
BEFORE performing inserts/deletions is fine. In either scenario, all of the
sp is still executed so commands affecting server side tables are actioned
even if they occur after the resultset return. Only problem is that the
results don't come back

This seems like a bug/limitation with quite basic functionality and so I
can't believe it hasn't been hit by numerous people. Therefore, either I'm
one of only a few needing insertions for RDA Pull SP's or it is not a bug
that occurs under all circumstances.

I've tried to think of workarounds and mainly came to the same conclusion as
the author in the previous thread which is to execute a Stored Procedure
first of all to perform all of the necessary inserts/deletions and create the
data in a table and then perform a seperate pull to read the data out of the
table. This is not particularly the way I'd like to go and is also not ideal
due to temporary tables not being an option as they won't persist betwen the
execution of the sp and the subsequent pull. Therefore having a static table
in place of every temporary table in the stored proc's would be required,
which is firstly not a great design choice for the db and secondly, would
require extra columns to key the data by some sort of unique identifier for
each device (to allow multiple devices to store data in that static table).
I don't want to go down the route of each device temporarily creating a
static table every time it connects and then dropping it due to the overhead,
extra security considerations and bad practices (even without considering the
fact that each sp requires different table structures etc.)

Therefore, I'm loathe to go down the rather messy route stated above but at
the moment it appears the only option. The inserts into temp tables are
mainly needed as the code builds up hierarchys of the data or temporary
tables to lookup against when retrieving other data. One is also very long
and complex. Trying to re-write without using inserts would be v.difficult

If it isn't just my environment playing up then surely this is a massive bug
in SQL CE/RDA due to it affecting basic functionality (inserts/returning
results)

To confirm, all of my tests have been executed both on the server and
through RDA with only RDA having problems. As you can see above, the sp's
don't have to be anything clever and even the simplest fail in the right
circumstances

Marius Bucur

2005-06-22, 3:23 am

When you have more than one statement in a stored procedure that is used
with RDA Pull it is a good idea to
add at the beginning of stored procedure SET NOCOUNT ON (so only the
relevant result set will be returned not the messages indicating the number
of rows affected by a Transact-SQL statement ).
Try this way an I think that will solve your problem.
Marius

"Scott Bradley" <Scott Bradley@discussions.microsoft.com> wrote in message
news:08F8FC4F-141C-41B6-99F5- 2FBA5E91B1A0@microso
ft.com...
> This is related to a question which was discussed previously but never
> answered (see "Problem with Push Method" thread from 4/15/2005 - not the
> first couple of posts but the last few)
>
> In the lower posts of that thread, the report was of a problem using
> temporary tables. I have been having similar problems to what was
> reported
> but after doing some investigation and numerous tests, believe the problem
> is
> a lot more severe than that and isn't just limited to temp tables.
>
> The problem seems to be that performing an insert into a table (temporary
> or
> normal) at any point in a stored procedure before you return the resultset
> causes the RDA Pull to not create the table. This also seems to be the
> case
> when performing a "delete from xxxx"
>
> Therefore, I could have a simple stored proc containing the following code
> :-
> ---------------------------
> Select * from TestTable
>
> Insert into StatusTest
> Values(1)
> ---------------------------
> This sp would run fine in query analyser and would return results from
> TestTable and then perform the insert. When run through RDA Pull this
> would
> result in a table containing the contents of TestTable being created on
> the
> device. The insert would also run fine and insert into the server side
> table
> StatusTest etc.
>
> However, changing the Stored Proc to be :-
> ---------------------------
> Insert into StatusTest
> Values(1)
>
> Select * from TestTable
>
> Insert into StatusTest
> Values(1)
> ---------------------------
> This again will also run fine in Query Analyser. However, calling the sp
> through RDA Pull results in no table being created by the Pull and also no
> errors. The insert statements would both still get executed though,
> proving
> that the stored procedure still runs through to completion.
>
> A similar failure to create a table would also occur if I changed the
> first
> insert to be something like :-
> Delete from TestTable
> (The table would be created ok if this was changed to Truncate Table
> TestTable suggesting that it is the Delete statement it doesn't like)
>
> Note: In the above examples, the tables shown are not important. They are
> simple tables just used for example purposes and so there are no issues
> with
> constraints etc. The same results occur regardless of tables
>
> In short, there appears to be a problem returning a resultset AFTER any
> inserts/deletions to tables (either temp or normal). Returning a
> resultset
> BEFORE performing inserts/deletions is fine. In either scenario, all of
> the
> sp is still executed so commands affecting server side tables are actioned
> even if they occur after the resultset return. Only problem is that the
> results don't come back
>
> This seems like a bug/limitation with quite basic functionality and so I
> can't believe it hasn't been hit by numerous people. Therefore, either
> I'm
> one of only a few needing insertions for RDA Pull SP's or it is not a bug
> that occurs under all circumstances.
>
> I've tried to think of workarounds and mainly came to the same conclusion
> as
> the author in the previous thread which is to execute a Stored Procedure
> first of all to perform all of the necessary inserts/deletions and create
> the
> data in a table and then perform a seperate pull to read the data out of
> the
> table. This is not particularly the way I'd like to go and is also not
> ideal
> due to temporary tables not being an option as they won't persist betwen
> the
> execution of the sp and the subsequent pull. Therefore having a static
> table
> in place of every temporary table in the stored proc's would be required,
> which is firstly not a great design choice for the db and secondly, would
> require extra columns to key the data by some sort of unique identifier
> for
> each device (to allow multiple devices to store data in that static
> table).
> I don't want to go down the route of each device temporarily creating a
> static table every time it connects and then dropping it due to the
> overhead,
> extra security considerations and bad practices (even without considering
> the
> fact that each sp requires different table structures etc.)
>
> Therefore, I'm loathe to go down the rather messy route stated above but
> at
> the moment it appears the only option. The inserts into temp tables are
> mainly needed as the code builds up hierarchys of the data or temporary
> tables to lookup against when retrieving other data. One is also very
> long
> and complex. Trying to re-write without using inserts would be
> v.difficult
>
> If it isn't just my environment playing up then surely this is a massive
> bug
> in SQL CE/RDA due to it affecting basic functionality (inserts/returning
> results)
>
> To confirm, all of my tests have been executed both on the server and
> through RDA with only RDA having problems. As you can see above, the sp's
> don't have to be anything clever and even the simplest fail in the right
> circumstances

Sponsored Links