A Look At SQL Server Compact Edition

Question

A Look At SQL Server Compact Edition

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

I agree with steve and believe that it makes more sense to have support for stored procedures. Or atleast, there should be a provision to separete your TSQL code from the application code.

Many of the MS docs that support the SQLCE version suggest to start with SQLCE and when the application, requirements and data grow, upgrade to other versions of SQL Servers. But this upgrade process would be a nightmare. It may not be just an upgrade, but a re-writing phase which includes rewriting all the TSQL code into Stored procedures.

.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 2

Wait a minute. Not having stored procedures is a very effective way to separate the application code from T-SQL. Review the reasons for stored procedures. Remember that we are talking about something that is single user. Almost makes the security aspect moot.

I would much rather have views than procedures.

ATBCharles Kincaid

jacob sebastian SSChampion Points: 11822 More actions · Answer 3

Charles,

I am refering to the extra work involved while upgrading a single user SQLCE application to a multi-user SQL Server application. All the TSQL code that we write in our single-user SQLCE application will have to be converted into STORED PROCEDURES. Do you agree with this?

I agree that views are very powerful. So are stored procedures or functions. The person responsible to design and write the database code might be deciding which part of the code/logic should go to a view, stored procedure or a function. They must have their own reasons for putting a certain part of the code to a stored procedure or going for a function to perform a certain operation.

The point that I want to emphasis is the extra effort needed to create all the stored procedures, views and functions while upgrading to SQL Server Express, Enterprise or any other version.

If there were support for stored procedures and views, the upgrade process would have been much easier.

.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 4

I agree with you, Jacob. Originally this was device oriented. The fact that they got it to run on the desktop is cool. There had to some sacrifice to save space. There is no CLR integration.

I disagree with starting a desktop app with CE and migrating later. I can't think of a business app that would use SQL that I would not write as multi-user from the ground up. SQL Server Express to full version is much less of a tough upgrade.

ATBCharles Kincaid

Anipaul SSC-Insane Points: 24681 More actions · Answer 5

Anipaul

SSC-Insane

Points: 24681

June 5, 2008 at 6:31 am

#824742

Nice article. Good for freshers also.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 6

Robert O'Connell (7/10/2007)
The biggest downside to using SQL Server Compact Edition is that it does not allow the creation of stored procedures. This seems to be Access with a different name and the same problems. If MS was serious about making this a viable product it should have been able to create stored procedures.

Not having Stoed Procedures is a BIG show stopper. Too bad.

As far as Access not having them... you can call Access Queries with ADO in the same way you would call a SS proc.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Charles Kincaid SSChampion Points: 13593 More actions · Answer 7

SQL CE is effective on Mobile devices. There you have less memory, smaller processors, and a cut down operating system.

For desktop projects I would go with MDSE or SQL Server Express. This would address your scalability concerns. The fact that you can run CE on the desktop is very handy for debugging device applications.

ATBCharles Kincaid

umadhira Valued Member Points: 69 More actions · Answer 8

Not having SPs, views in CE means, I can not use them in a transaction, Is that right? May be not planning to update the existing SP but can I use it? Meaning a compiling code?

What about Triggers?

Thanks,

Charles Kincaid SSChampion Points: 13593 More actions · Answer 9

Views and stored procedures have nothing to do with transactions. Then again transactions are not supported either. That is OK as the Compact Edition is single user anyway.

ATBCharles Kincaid

micha 20897 Grasshopper Points: 13 More actions · Answer 10

I have some questions:

1) The article promises a follow up article with answers for the questions raised at the end of the article. I don't find this follow up article. Is that article, or just the answers for the questions available somewhere/

2) According to some documentation, SSCE yes supports transactions - simple, not distributed transactions. I saw some comments stating transactions are not supported. What is the case with transactions?

3) I assume I can create multiple database in SSCE, maybe each database is just a separate files. Can I run a select with join between tables from different databases?

4) According to the documentation, SSCE support multiple concurrent connections. the only limitation is connections must be local. Does SSCE support multiple local connections from different applications (aka .EXE)? I assume such multiple access might fail as multiple access to .MDB might fail (there is no real server to synchronize parallel access). Also what about multiple concurrent parallel access to an SSCE database from two different threads of the same executable?

Thanks in advance!

blapath