Queries in SQL compact Edition

If my research is correct SQL CE as of this date does not support STORED PROCEDURES (queries).

If this is true, how can it hope to compete with MS ACCESS or MS Foxpro as a Desktop database.

This seems to be a lot of hype for something that appears to be totally in-adequate.

I thought this was a way to wean off of MS ACCESS.

Can someone tell me what I am missing ?

Thanks

John

SQL CE is not a replacment for MS Access, but can be used as small footprint, in-process database engine together with yur custom application. Queries (meaning select statements in your code) is supported. The storage of your query strings must be handled by your application, not by SQL CE.|||

Access is a front-end database application tool--not a DBMS. Access by default uses JET as its DBMS engine. SQLCe is not an Access replacement but it can be a faster, lighter, more secure alternative to JET.

AFA stored procedures, these can be implemented in your code but they are not stored in the database or exectuted by the DBMS engine. This gives you the ability to create your own common code routines to replace SP functionality--something JET does not have. While JET supports canned queries, these do not support the logic afforeded by a real SP as supported by TSQL and SQL Server.

For many situations even JET is overkill--SQLCe is designed for these situations--especially when security of the stored data is an issue. JET databases cannot be encrypted--SQLCe databases can.

See my EBook FMI: www.hitchhikerguides.net.

q; exit stored procedure

Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
follows:
Exec Sp1
Inserts, updates
Exec Sp2
Inserts, upadtes
Exec Sp3
If error Exit Exec Sp1
Can I end Sp1 if I catch an error in Sp3Jim
CREATE PROCEDURE BigOne
AS
DECLARE @.err integer
BEGIN TRANSACTION
EXEC @.err = sp1
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp2
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp3
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp4
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACION
GO
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> follows:
> Exec Sp1
> Inserts, updates
> Exec Sp2
> Inserts, upadtes
> Exec Sp3
> If error Exit Exec Sp1
> Can I end Sp1 if I catch an error in Sp3
>|||Hi Uri,
Thank you very much for your help. I am currently getting Deadlock message
from time to time, I was wondering how this commit rollback will effect it.
Since there will not be auto-commit for each update (I have many updates
before calling SP2, SP3, and one single commit for all the update, will this
increase the chance of deadlock or decrease it?
"Uri Dimant" wrote:
> Jim
> CREATE PROCEDURE BigOne
> AS
> DECLARE @.err integer
> BEGIN TRANSACTION
> EXEC @.err = sp1
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp2
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp3
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp4
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> COMMIT TRANSACION
> GO
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> > follows:
> >
> > Exec Sp1
> > Inserts, updates
> > Exec Sp2
> > Inserts, upadtes
> > Exec Sp3
> > If error Exit Exec Sp1
> >
> > Can I end Sp1 if I catch an error in Sp3
> >
>
>|||JIM
Read this article
http://www.sql-server-performance.com/deadlocks.asp
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> Hi Uri,
> Thank you very much for your help. I am currently getting Deadlock message
> from time to time, I was wondering how this commit rollback will effect
> it.
> Since there will not be auto-commit for each update (I have many updates
> before calling SP2, SP3, and one single commit for all the update, will
> this
> increase the chance of deadlock or decrease it?
>
> "Uri Dimant" wrote:
>> Jim
>> CREATE PROCEDURE BigOne
>> AS
>> DECLARE @.err integer
>> BEGIN TRANSACTION
>> EXEC @.err = sp1
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp2
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp3
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp4
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> COMMIT TRANSACION
>> GO
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
>> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
>> > follows:
>> >
>> > Exec Sp1
>> > Inserts, updates
>> > Exec Sp2
>> > Inserts, upadtes
>> > Exec Sp3
>> > If error Exit Exec Sp1
>> >
>> > Can I end Sp1 if I catch an error in Sp3
>> >
>>|||Thanks Uri, yes I was reading that article, since the commit will be
performed for a long transaction I will probably get more deadlocks in this
case. Do you agree with me?
"Uri Dimant" wrote:
> JIM
> Read this article
> http://www.sql-server-performance.com/deadlocks.asp
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> > Hi Uri,
> >
> > Thank you very much for your help. I am currently getting Deadlock message
> > from time to time, I was wondering how this commit rollback will effect
> > it.
> > Since there will not be auto-commit for each update (I have many updates
> > before calling SP2, SP3, and one single commit for all the update, will
> > this
> > increase the chance of deadlock or decrease it?
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Jim
> >> CREATE PROCEDURE BigOne
> >> AS
> >> DECLARE @.err integer
> >> BEGIN TRANSACTION
> >> EXEC @.err = sp1
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp2
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp3
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp4
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> COMMIT TRANSACION
> >> GO
> >>
> >>
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> >> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> >> > follows:
> >> >
> >> > Exec Sp1
> >> > Inserts, updates
> >> > Exec Sp2
> >> > Inserts, upadtes
> >> > Exec Sp3
> >> > If error Exit Exec Sp1
> >> >
> >> > Can I end Sp1 if I catch an error in Sp3
> >> >
> >>
> >>
> >>
>
>

q: SP name in the trigger

There are two different Stored Procedures insert data into a table, is it
possible to catch what stored procedure name was that trigged the trigger in
the trigger?Hi Jim
I don't know of an easy way, but you should be able to use the context_info
such as
CREATE TABLE MyTab ( id int not null identity, val int )
CREATE PROCEDURE sprInsert1
AS
BEGIN
DECLARE @.value varbinary(128)
SET @.value = CAST('sprInsert1' AS varbinary(128))
SET CONTEXT_INFO @.value
INSERT INTO MyTab ( val ) VALUES ( 1 )
END
CREATE PROCEDURE sprInsert2
AS
BEGIN
DECLARE @.value varbinary(128)
SET @.value = CAST('sprInsert2' AS varbinary(128))
SET CONTEXT_INFO @.value
INSERT INTO MyTab ( val ) VALUES ( 2 )
END
CREATE TRIGGER TrgMyTabInsert ON MYTab
FOR INSERT
AS
BEGIN
SELECT CAST(context_info AS varchar(10))
FROM SYSPROCESSES
WHERE SPID = @.@.SPID
END
EXEC sprInsert1
GO
EXEC sprInsert2
GO
EXEC sprInsert2
GO
EXEC sprInsert1
EXEC sprInsert2
EXEC sprInsert2
GO
John
"JIM.H." wrote:

> There are two different Stored Procedures insert data into a table, is it
> possible to catch what stored procedure name was that trigged the trigger
in
> the trigger?|||Hi Jim
I don't know of an easy way, but you should be able to use the context_info
such as
CREATE TABLE MyTab ( id int not null identity, val int )
CREATE PROCEDURE sprInsert1
AS
BEGIN
DECLARE @.value varbinary(128)
SET @.value = CAST('sprInsert1' AS varbinary(128))
SET CONTEXT_INFO @.value
INSERT INTO MyTab ( val ) VALUES ( 1 )
END
CREATE PROCEDURE sprInsert2
AS
BEGIN
DECLARE @.value varbinary(128)
SET @.value = CAST('sprInsert2' AS varbinary(128))
SET CONTEXT_INFO @.value
INSERT INTO MyTab ( val ) VALUES ( 2 )
END
CREATE TRIGGER TrgMyTabInsert ON MYTab
FOR INSERT
AS
BEGIN
SELECT CAST(context_info AS varchar(10))
FROM SYSPROCESSES
WHERE SPID = @.@.SPID
END
EXEC sprInsert1
GO
EXEC sprInsert2
GO
EXEC sprInsert2
GO
EXEC sprInsert1
EXEC sprInsert2
EXEC sprInsert2
GO
John
"JIM.H." wrote:

> There are two different Stored Procedures insert data into a table, is it
> possible to catch what stored procedure name was that trigged the trigger
in
> the trigger?

q: SP name in the trigger

There are two different Stored Procedures insert data into a table, is it
possible to catch what stored procedure name was that trigged the trigger in
the trigger?Hi Jim
I don't know of an easy way, but you should be able to use the context_info
such as
CREATE TABLE MyTab ( id int not null identity, val int )
CREATE PROCEDURE sprInsert1
AS
BEGIN
DECLARE @.value varbinary(128)
SET @.value = CAST('sprInsert1' AS varbinary(128))
SET CONTEXT_INFO @.value
INSERT INTO MyTab ( val ) VALUES ( 1 )
END
CREATE PROCEDURE sprInsert2
AS
BEGIN
DECLARE @.value varbinary(128)
SET @.value = CAST('sprInsert2' AS varbinary(128))
SET CONTEXT_INFO @.value
INSERT INTO MyTab ( val ) VALUES ( 2 )
END
CREATE TRIGGER TrgMyTabInsert ON MYTab
FOR INSERT
AS
BEGIN
SELECT CAST(context_info AS varchar(10))
FROM SYSPROCESSES
WHERE SPID = @.@.SPID
END
EXEC sprInsert1
GO
EXEC sprInsert2
GO
EXEC sprInsert2
GO
EXEC sprInsert1
EXEC sprInsert2
EXEC sprInsert2
GO
John
"JIM.H." wrote:
> There are two different Stored Procedures insert data into a table, is it
> possible to catch what stored procedure name was that trigged the trigger in
> the trigger?