I asked this in Sql Express but hoping to get a different answer here.

Here is my goal please let me know if it is possible.

I have installed sql express on clients machines. I don't want them to be able to view the sp's or the functions. I would like to go as far as not allowing them to see the tables. I tried with encrption but this is still breakable by the user using the DAC.

Does CE

Support transactionscope?

What would I use to create tables and sp's for my sdf database? Server Managment Studio or something else?

Thanks

Stored procedures are not available in SQL CE.

The next version of CE (3.5) will support transactionscope on the desktop (not of Windows Mobile)

Create tables from SSMS, yes, or use the CREATE TABLE / CREATE INDEX statements (in SSMS Query window)

You may find this useful: http://blogs.msdn.com/stevelasker/archive/2007/03/31/creating-your-sql-server-compact-edition-database-and-schema-in-code.aspx

|||

Is there any whitepapers on performance of Sql CE compared to Sql Express?

Is there a way to use profiler to see whats going on in SQL CE?

can I see the database in SMS if it has a passowrd on it and has been encrypted?

Is there a conversion tool to convert from Express or 2005 to CE db?

Thanks

|||

You have a lot of questions!! :-)

1: Yes, there is some info here: http://msdn2.microsoft.com/en-us/library/bb380177.aspx

2: You can use Management Studio - Query analyzer with SQL CE, to see which indexes are used etc. Profiler is not available

3: Yes, you will be prompted for the password (having a password == being encrypted)

4: Yes, there are 3rd party tools, and a data migration wizard is in the works in the Visual Studio "Orcas" timeframe.

|||

Is there any reason that varchar is not supported?

Is there a plan to support this in the next version?

Thank you for your help this is really looking like it will work well for me.

|||

Do you have any links for the 3rd party tools for the data migration?

I think I know the answer is No but I will still ask.

Are scalar functions available in CE?

|||

I will try to answer more questions for you:

> Is there any reason that varchar is not supported?

I am not sure in understand this. nvarchar() is supported (as SQL CE uses only unicode, varchar is not), but you can always convert from varchar til nvarchar. I doubt there are any plans to support varchar in a future version.

> Do you have any links for the 3rd party tools for the data migration?

DataPort wizard from www.primeworks-mobile.com

> Are scalar functions available in CE?

Yes, some functions are, for a full list see http://msdn2.microsoft.com/en-us/library/ms174077.aspx

|||

Thank you for the answers that product worked great. One thing I noticted and wondering if I am missing something. Is there no way to create a clustered index in ce?

|||One more question is there a way to scrip the whole db.|||

Is there any Microsoft Application blocks for CE?

|||

if i want to run 2 selects in the same call to the db is this possible or would I have to do 2 calls to and get 2 different SQLCeResultSets back.

|||

I will again try to answer some of your questions - I you find my answers useful, please be kind and mark them as being so, thanks.

> One thing I noticted and wondering if I am missing something. Is there no way to create a clustered index in ce?

No, only Non-clustered indexes are supported - see http://msdn2.microsoft.com/en-us/library/ms345331.aspx

> One more question is there a way to scrip the whole db.

Do you mean scripting an existing SDF file. If so, not without 3rd party tools (see reply above). You may find this useful:

http://blogs.msdn.com/stevelasker/archive/2007/03/31/creating-your-sql-server-compact-edition-database-and-schema-in-code.aspx

> Is there any Microsoft Application blocks for CE?

Sure is - the "Mobile Client Software Factory" contains a SQL CE Data Access block: http://www.microsoft.com/downloads/details.aspx?familyid=F9176708-9F57-4C0F-97FB-F9C65A9BBF22&displaylang=en

> if i want to run 2 selects in the same call to the db is this possible or would I have to do 2 calls to and get 2 different SQLCeResultSets back.

You must make two calls, only on SQL stament is supported per command.

Hope you found my reply useful.

|||

Select count(distinct(column)) from table

Any way to get this to work or will I have to just select out distinct values and manully count them in C# when I return it out.

|||

How can I do a select like this in CE?

SELECT
col1
FROM
table1 as A
JOIN
(SELECT
col1
FROM
table2
) B
ON
A.col1 = B.col1

Thanks

|||Nested FROM queries are not supported by SQL CE. You will have to rephrase or use 2 calls. Nested queries will be available in the next version of SQL CE (3.5)

Hyperthreading an issue?

Hi!
Has anyone experienced troubles with machines that have hyperthreading
enabled? Troubles concerning speed and data consistency issues?
We have three machines (one for testing, one at a client and another one
at a friend's place who agreed to try it as well) with hyperthreading and
all machines suffer from performance problems with MSDE (but not only with
MSDE).
They are all running WinXP Professional.
Any pointers appreciated.
Stefan
On Mon, 12 Jul 2004 12:05:14 +0200, Stefan M. Huber <looseleaf@.gmx.net>
wrote:

> Hi!
> Has anyone experienced troubles with machines that have
> hyperthreading enabled? Troubles concerning speed and data consistency
> issues?
> We have three machines (one for testing, one at a client and another one
> at a friend's place who agreed to try it as well) with hyperthreading
> and all machines suffer from performance problems with MSDE (but not
> only with MSDE).
> They are all running WinXP Professional.
...with only one CPU, I should mention.
Stefan
|||I have heard rumors about hyper-threading, but I have an Intel 875 P4
motherboard and have not experienced any issues--that I'm aware of. MSDE can
use multiple processors (up to two) and hyperthreading exposes the same
processor as a second thread so it should be able to help. Are you getting
errors in the event log to indicate that the governor is kicking in? I can
see how some applications written on a single processor might not be
prepared for operations on dual processor boxes where race conditions can
cause operations to occur out of the usual serialized order.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Stefan M. Huber" <looseleaf@.gmx.net> wrote in message
news:opsa0r3kscs9ddfw@.news.individual.de...
> On Mon, 12 Jul 2004 12:05:14 +0200, Stefan M. Huber <looseleaf@.gmx.net>
> wrote:
>
> ...with only one CPU, I should mention.
> Stefan
|||On Mon, 12 Jul 2004 11:14:09 -0700, William (Bill) Vaughn
<billvaRemoveThis@.nwlink.com> wrote:

> processor as a second thread so it should be able to help. Are you
> getting errors in the event log to indicate that the governor is kicking
> in? I can
> see how some applications written on a single processor might not be
> prepared for operations on dual processor boxes where race conditions can
> cause operations to occur out of the usual serialized order.
I haven't yet seen any event log entries. Or other log file entries that
indicate troubles. It's just slow :/ But thanks for your reply.
Stefan
|||There are a dozen dozen reasons that an application can be slow. Blaming
MSDE is like blaming your car's engine when you're hauling a ton of sand up
and towing a horse trailer up Mt. Rainer. If you're not seeing events, then
it's likely your database configuration, the amount of RAM in your system
(consider that SQL Server builds its performance model around cached pages),
or the what your application is asking the engine to do...
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Stefan M. Huber" <looseleaf@.gmx.net> wrote in message
news:opsa4h2fhjs9ddfw@.news.individual.de...[vbcol=seagreen]
> On Mon, 12 Jul 2004 11:14:09 -0700, William (Bill) Vaughn
> <billvaRemoveThis@.nwlink.com> wrote:
can
> I haven't yet seen any event log entries. Or other log file entries that
> indicate troubles. It's just slow :/ But thanks for your reply.
> Stefan
|||On Wed, 14 Jul 2004 12:25:53 -0700, William (Bill) Vaughn
<billvaRemoveThis@.nwlink.com> wrote:

> There are a dozen dozen reasons that an application can be slow. Blaming
> MSDE is like blaming your car's engine when you're hauling a ton of sand
> up and towing a horse trailer up Mt. Rainer. If you're not seeing
> events, then
> it's likely your database configuration, the amount of RAM in your system
> (consider that SQL Server builds its performance model around cached
> pages),
> or the what your application is asking the engine to do...
I am not directly blaming MSDE, I just hoped that somebody else has
troubles. Alas, we'll have to look elsewhere. The problem is that we don't
know where to start :/
The DB configuration is identical on (nearly) all our customers' places.
At least as identical as it can be.
We'll be trying our setup on a real dual processor machine shortly and see
if we can find similar issues. If so, I'd say Delphi or we ourselves are
to blame.
Thanks for your time,
Stefan