Is there a repository for expressions

Forgive me if there's an obvious answer for this.
I was wondering if there was the facility to save expressions that
you would use in multiple reports. The ones we're looking at
specifically
create a value in a detail line by doing arithmetic on other detail
column values.The simplest way is to write a simple assembly and put the logic there.
Then you reference this assemby from your reports and use a
=myAssembly.myFunction(param1, param2) type syntax in the textbox.
If your assembly only performs calculations or other simple procedures,
you don't have to worry about code access security. There is
docmentation on this in MSDN.
jensisql

Is SQL Server CE really the solution?

Hi,

So far, with other programming platforms, I used to have the facility, embedded within application .exe, to:

1- Interface with already existing "multi-user" database (ex: Oracle, Access, etc.)

2- Create/access "local"/"Single-User" files for processing purposes. (try to minimize network trafic...)

So far, I've read a lot about SQL Server products (SQL Server, Sql Server Express, SQL Server CE), at least all articles I could find, and would like to have your opinion on what would be the best approach?

1- What SQL version to use for "multi-user" databases?

2- What SQL version to use for "single-user" databases/files?

3- Can SQL Express allow me to achieve both "multi-user" and "single-user" approaches within same app? If not, what would you rather suggest?

Thanks in advance,

Stphane

I include a long discussion of the architectural best practices in both of my new books that include the SQL Server Compact Edition as well as the other SQL Server SKUs.

SQL Server Compact Edition is clearly not a multi-used database--unless you consider using it as a replication subscriber-based DBMS. While this is certainly a way to share data between multiple users, it's not a "multiuser" database in the traditional sense. It's best use is as a single-user data repository--even in applications that connect to a shared database.

SQL Server Express and similar SKUs can be used as a single-user database but it might (just might) be overkill for some situations. Yes, SQL Server Express edition can be used in an application that serves one or many users. As I describe in my books (in great detail), if you intend to take this approach, both applications need to be built as "multiuser" to reduce development and support costs.

See www.hitchhikerguides.net for more information.

hth

|||

Hi,

First, many thanks for replying. I really appreciate. I was aware that SQL CE was not a "multi-users" database. As a reminder, the approach I'm looking for is to have:

1- tables found at a specific network location which are shared by several users at the same time. (where I thought using SQL Express...)

2- tables, owned by single/specific user, which could be localized either on network (especially when centralized backups are an issue) as well as locally. These tables are especially used for queries and calculations required by the user so there is no need to have them "shareable". (where I "first" thought using SQL CE...)

Considering this scenario, after reading all articles I could find on subject, I really scratched my head wondering if using SQL CE in this context would effectively complicate things for nothing, that I could rather simply use SQL Express for both multi-user tables as well as single-user tables at the same time. You seem to say yes.

Now, when you add "...but it might (just might) be overkill for some situations", I'm not too familiar with this "overkill" expression. Can you elaborate a little more about this point?

Accordingly, for my "single user" access needs. With Clarion (ref. SoftVelocity), I used to have in this case directories, one for each user, where I could find any single table needed for processing. In fact, each filename could be specified from within application including appropriate directory path. Now with SQL Express, how can I integrate this same scenario? Will/can I create many occurences of same "single-user" database, one per user, but each one localized at a different physical place? Would certainly like to have your opinion on this...

A last point. Many times, over past years, I had to support people over the phone. I would certainly appreciate to still be able to install any new version of application "off-site" by instructing users over the phone, after sending them everything they need over the Net, method I oftenly used so far with other programming platforms. But... I'm still wondering how I can manage this way any SQL table layout modifications... Let me explain by a concrete example.

Again with Clarion, platform I've used now for many years, I could integrate in my application a "filer" utility wich could compare version of files/tables directly on site and "re-structure" them accordingly if file/table layout was not up to date.

Now, with SQL Express/SQL CE, what is the process involved when my "new" application is now using any table's version which layout is now "newer/different" from the one within actual database thus requiring any re-structuring process? Any tools I must acquire and combine with my application or must this re-structuring process absolutely be done manually on site (like I had no choice to do for any Oracle tables)? Your opinion?

Thanks again,

Stphane

|||

1- tables found at a specific network location which are shared by several users at the same time. (where I thought using SQL Express...)

Yes, SQL Express makes a lot of sense here but you might want to upgrade to Workgroup instead for reasons I'm about to explain.|||

Hi again,

Really appreciate your time. I read carefully your reply and would like to bring a few more things:

"In this case keeping user-specific tables stored in SQLCe might make sense if the application is run without a network connection at all at some time(s)."

Must I understand that I can't have an app using both SQL Express, for accessing "network" tables, and SQL CE, for accessing "local" tables, at the same time?

"Since you're depending on a shared database connection to SQL Express anyway, I would probably keep the user-specific data in the central SQL Express DB with a UserID as a secondary key"

I agree with this kind of structure. My only concerns are if I have, as an example, over a hundred of users running simultaneously reports then generating a lot of data (scenario I've already seen in the past on a tuesday afternoon after administration and production woke up...), won't I face a performance issue taking into account that this kind of data management generates a lot of insert/update/delete statements on same tables, a lot of network traffic in a short period of time? This kind of situation made me decide to go for localized resources which improved a lot the time response for all users. Your opinion?

"Yes, you could cache this on the client with SQLCe to improve startup performance"

I lost you on this one. I'm not sure to understand what you suggest to cache at startup?

"Another approach would be to let the Workgroup Edition publish some or all of this data..."

I took a few minutes and got on Microsoft's website to get some more infos on SQL Server 2005 Workgroup Edition. Seems in fact to be the appropriate tool. Unfortunately, if I'm targetting small/medium businesses as well, I'm quite sure that some customers will tell me to find something less expensive... (Please don't laugh. It's unfortunately true as I had to face this situation many times...) Any other reliable but cheaper approach?

BTW, thanks for taking time to explain the term "overkill". This is not an expression we use around here ;)

"When using a replication architecture, the schema is also propagated to the clients"

Any articles I could read on this replication process? I've never found/read anything on the subject so far... Are your books giving a detailed explanation on this subject? This issue is very important for me as I have to do it most of the times when people are asking for new features.

A last point, your books. BTW, I'm developping using VC++2005 (C++/CLI). I must admit that I'm quite worrying about not being able to find more reliable examples in C++ when I search MSDN or any other related resources, books. I can always convert from C# examples which are not that different but really have a hard time to find my way out with VB examples. In the books you mentionned, what is the language used for the examples?

Having discussed with many persons on this last issue so far, I can't keep asking myself why there is still so few resources available for C++... So much for C# and VB... People, even from Microsoft, told me many times that C++ was here to stay. But... why leaving us, especially when learning, with so few?

Thanks again for your help.

Stphane

|||

Must I understand that I can't have an app using both SQL Express, for accessing "network" tables, and SQL CE, for accessing "local" tables, at the same time?

Sure you can. It's a perfectly viable alternative. I discuss this strategy in the books. I think using SQLCe to store client-specific data or ever-morphing business rules in a local data cache (like SQLCe) makes a lot of sense. This way the application can come up (without having to connect to the server), process data and close down without having to connect at all. Using the SQLCe engine to buffer data that's eventually replicated up to the server also makes sense in some situations. That's what the SQL Mobile/SQLCe engine was designed to do.|||

Hi again,

Where are you that you have never heard of "overkill"? Cleveland?

I'm simply from nearly Montral, in Qubec Canada. Even if I work with English people on a daily basis, it is an expression I didn't hear quite often let's say.

"It's not the language that you use to ask the question that affects the answer--it's the question itself."

You can't hit the target better than that! How true it is!

I will definitely look forward reading your books/ebooks. Thanks again for your support. Again really appreciated.

Case closed for me!

Stphane

|||I (think) I'm speaking in Montreal in May. DevTeach? Look me up then and we can see how well you were able to implement your design.|||

Hi again,

Many thanks for this invitation. Very nice of you. I really hope I will be able to attend DevTeach.

Maybe a little question concerning install of CE. The version I have installed on my machine was done from files downloaded on Dec 15th 2006, which I think was the RC1 version. I noticed while reading this friday issue of "Microsoft Download Notifications" that there seemed to be new versions of CE available for download. I followed links supplied:

SQL Server 2005 Compact Edition:

http://www.microsoft.com/downloads/details.aspx?familyid=85E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&mg_id=10096&displaylang=en

SQL Server 2005 Compact Edition Tools for VS2005 SP1

http://www.microsoft.com/downloads/details.aspx?familyid=877C0ADC-0347-4A47-B842-58FB71D159AC&mg_id=10096&displaylang=en

SQL Server 2005 Compact Edition Books Online

http://www.microsoft.com/downloads/details.aspx?familyid=E6BC81E8-175B-46EA-86A0-C9DACAA84C85&displaylang=en

and downloaded corresponding files but didn't install anything yet. I would have some questions:

1- Is the version obtained from those links the final version of CE?

2- As mentionned, considering that RC1 downloaded on Dec 15th is the version actually installed on my machine, should I uninstall everything before install these new files downloaded today.

3- Considering finally that VS2005 has been upgraded to SP1 on same machine, am I right thinking that I must also install "SQL Server 2005 Compact Edition Tools for VS2005 SP1" or is this already include in "SQL Server 2005 Compact Edition" version I downloaded?

Thanks again for your help, really appreciated.

Stphane

|||

1) The "official" announcment won't come until (at least) Monday. It should detail what's been exposed. I ended up uninstalling VS (completely) and reinstalling VS, SP1 and then the new SDK. I don't think the BOL has changed since the last drop, but I expect there will be further updates. In my opinion (and that of some others), it's pretty thin. That's why I wrote the EBook...

2) I did.

3) BHOM (Beats the Heck Out of Me)... ;) dunno. When I went into VS, after installing the new bits and did see the data providers had been renamed but the SQL Server Management Studio bits don't seem to have caught up. They still refer to the provider as "SQL Mobile".

perhaps we'll hear more next week.

|||

Hi again,

Didn't think you would be online today ;-). Glad to hear from you! As a conclusion to what you wrote, I really think that it would be smarter to wait for upcoming events.

"The "official" announcment won't come until (at least) Monday"

Is there any special website which would be the best source for any news or big titles coming from Microsoft especially for what may concern VS2005? I must admit that for CE, as an example, I browsed throught several websites in order to make my mind about what was really going on. Still no infos on that final version except the bits and bytes I can catch here and there on the Net...

I could understand that you had to reinstall VS. Is this a required step when uninstalling CE?

Must I understand that when upgrading from CE RC1 to CE final, I would probably have to face this reinstall? Yak!!!

BTW, should we receive any announcements or notice from Microsoft when SQL CE would be really at its final version and fully ready for download?

Thanks again,

Stphane

|||"Must I understand that when upgrading from CE RC1 to CE final, I would probably have to face this reinstall? Yak!!!"

I just uninstalled RC1 then installed final - no problem
|||

Hi Brian,

I understand that you had to uninstall RC1. After uninstall was completed, did you encounter any problem with VS2005 like William seems to highlight forcing you to reinstall anything?

(recall of William's reply: "I ended up uninstalling VS (completely) and reinstalling VS, SP1")

I ask you this question because I've myself seen this kind of scenario so many times in the past with many softwares unfortunately...

I presume you are using VS 2005 SP1. What install sequence would you recommend? Is the following one OK?

1. Uninstall SQL Server 2005 CE RC1 and any corresponding BOL installed.

2. Restart computer

3. Install SQL Server 2005 Compact Edition:

http://www.microsoft.com/downloads/details.aspx?familyid=85E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&mg_id=10096&displaylang=en

4. Install SQL Server 2005 Compact Edition Tools for VS2005 SP1

http://www.microsoft.com/downloads/details.aspx?familyid=877C0ADC-0347-4A47-B842-58FB71D159AC&mg_id=10096&displaylang=en

Note: as I already mentionned in earlier post of this thread, is this step required if I'm using VS2005 SP1 or is this install already included in package downloaded from link specified in step 3.

5. Install SQL Server CE BOL

http://www.microsoft.com/downloads/details.aspx?familyid=E6BC81E8-175B-46EA-86A0-C9DACAA84C85&displaylang=en

Thanks for sharing,

Stphane

|||

Hi Stephane:

Your order of installation is incorrect.

The correct order of installation is in the Overview section on the SQL Server 2005 Compact Edition Tools for VS2005 SP1 page at

http://www.microsoft.com/downloads/details.aspx?familyid=877C0ADC-0347-4A47-B842-58FB71D159AC&mg_id=10096&displaylang=en

Robert Wishlaw

|||

Hi Robert,

Many thanks for follow-up and highlighting my mistakes. I followed link you supplied and concluded for what I could read, that install sequence should rather be the following:

1. Uninstall SQL Server 2005 CE RC1 and any corresponding BOL installed.

2. Restart computer

3. Install SQL Server 2005 Compact Edition Tools for VS2005 SP1

4. Install SQL Server 2005 Compact Edition:

5. Install SQL Server CE BOL

Is it OK now?

Also any established facts about the possibility of corrupting VS 2005 SP1 when uninstalling SQL 2005 CE?

Many thanks again for your help,

Stphane