Can someone clarify why only a single-statement can be executed in a command?

I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.

I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time?

For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?

Have you tried it at all?

From my understanding alot of database servers limited the execution of multiple sql statements because you would end up with people doing stuff like "select * from tablename where column='" + value + "'" and then you would have people that would inject sql into value and they sould turn it into value ='"; DELETE * FROM TABLENAME ".

I think in most cases where multiple statements are executed - perhaps you can use joins? I'm really not sure. For the most part I try to use stored procedures where necessary - But that's just my personal preference. Hope this hielps.|||

I haven't tried, but the documentation (Mobile Edition Books Online) seems pretty clear:

The data provider for SQL Server Mobile has the following limitations:

No support for batch queries. Queries must be a single SQL statement. For example, the following statement is valid:

Copy Code

SELECT * FROM Customers

This statement is not valid:

Copy Code

SELECT * FROM Customers; SELECT * FROM Customers2

|||Right but its not the fact that you will use it - its the fact that many people wouldn't.

IMHO if you need to use batched sql statemnents that are reliant on a state of the prior sql statement you should use stored procedures. (unless of course it isn't supported)|||

Hi MarcD & Shaun,

I would like to clarify the positioning of SQL Server Everywhere Edition (SSEv) against other Microsoft SQL Server products such as SQL Server Standard/Enterprise Edition (SQL EE), SQL Server Express Edition (SQL XE).

SSEv is for small apps where you need basic data store and querying capabilities and not stored procs, sql batching ... etc. If you need these rich/advanced capabilities we would recommend you to use SQL XE.

SSEv is not a redundant product when compared to SQL XE. Each caters to different customer needs.

I hope I have not disappointed you by this post but thats the detail I can give for now :(

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

Can someone clarify why only a single-statement can be executed in a command?

I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.

I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time?

For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?

Have you tried it at all?

From my understanding alot of database servers limited the execution of multiple sql statements because you would end up with people doing stuff like "select * from tablename where column='" + value + "'" and then you would have people that would inject sql into value and they sould turn it into value ='"; DELETE * FROM TABLENAME ".

I think in most cases where multiple statements are executed - perhaps you can use joins? I'm really not sure. For the most part I try to use stored procedures where necessary - But that's just my personal preference. Hope this hielps.

|||

I haven't tried, but the documentation (Mobile Edition Books Online) seems pretty clear:

The data provider for SQL Server Mobile has the following limitations:

No support for batch queries. Queries must be a single SQL statement. For example, the following statement is valid:

Copy Code SELECT * FROM Customers

This statement is not valid:

Copy Code SELECT * FROM Customers; SELECT * FROM Customers2

|||Right but its not the fact that you will use it - its the fact that many people wouldn't.

IMHO if you need to use batched sql statemnents that are reliant on a state of the prior sql statement you should use stored procedures. (unless of course it isn't supported)
|||

Hi MarcD & Shaun,

I would like to clarify the positioning of SQL Server Everywhere Edition (SSEv) against other Microsoft SQL Server products such as SQL Server Standard/Enterprise Edition (SQL EE), SQL Server Express Edition (SQL XE).

SSEv is for small apps where you need basic data store and querying capabilities and not stored procs, sql batching ... etc. If you need these rich/advanced capabilities we would recommend you to use SQL XE.

SSEv is not a redundant product when compared to SQL XE. Each caters to different customer needs.

I hope I have not disappointed you by this post but thats the detail I can give for now :(

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
Meenal
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>
|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>

Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
MeenalMeenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
--
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
--
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
--
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>

Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
MeenalMeenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>