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)