Why choose SQL Express over Access?

[ljh]

Yep....but that defeats the whole ease-of-use thing that a no-install
solution like SQLite or SQL\e provides.

Especially when you have a hosted web-app.....SQL\e would be perfect for
that. Nothing to "install" (i.e. register) on the hosts servers.

It's just that MS wants you to pay to do a decent web-app. IMHO, it helps
them control competition somewhat (at least from the little guys) to thier
web-centric offerings. What other reasons could thier be to specifically
disable it under any IIS process? Greed?

As far as I can see....if you're small and have a great idea, MS SQL is
probably NOT the thing to use (at least SQLite is easier to deploy and has
greater capacity than SQL\e or SQL\x).

 

[ljh]

| Got confirmation from MS that they're deliberately breaking compatability
| with SQL\e running under IIS - they call it "soft-blocking".

Not sure how they would do that other then via license. I mean a sql
query
is not going to look any different coming from asp.net page or from your
own
host?

Not sure.....that's the word I got back from Steve Lasker when I emailed him
earlier today.

| Sounds all fluffy and sweet....but what it means is that you won't be
able
| to use SQL\e to easily deploy webservices (actually you can;t use it to
| deploy them at all - easy or not).

Again, I think you should at least be able to use SQL Express and it is
free. IMHO, that product is a sweet and generous gift from MS.

It is....for machines where you can install anything you want. For ASP.Net
hosted webservers, it sucks.

 

[JimD]

I'm trying it out. But, I can't even get it to create an empty db so that
I can use the VS.Net 2005 tools to create the tables, columns, etc.

I type "sqlite3 test.db" at the C:\ prompt (where I have a copy of
sqlite3.exe. Then, I type ".exit" and look for the empyt db....but there is
nothing there.

Any suggestions?

Just use the example C# code on the site:

http://adodotnetsqlite.sourceforge.net/

Click on "Documentation & Examples" and then click on "C# SourceCode
Example". I just copied-n-pasted the code into a Console App project
and ran it and the DB was created for me. The DB is created in the same
folder as your compiled exe. So if you do a debug build look in the
Debug folder.

Sqlite is very nice, very fast, and supports most of SQL92, databases up
to 2 terabytes, BLOBs and a lot more all either a 250KB dll or the
reduced featur 150KB dll. Best of all is that it is Open Source. No
restrictions on usage. SQL Server is great for a full DB. However, for
anything less, MS just doesn't have a good offering IMO. I don't want
to deal with proprietary restrictions on where/how I can include a
database file with my app.

Give Sqlite a shot, I think you will be pretty happy with it.

The steps to do a quick test console app:

Download Sqlite. Get version 3 without the TCL bindings:
http://www.sqlite.org/download.html

Download the ADO.Net data provider named Finisar.SQLite. Note, this
comes with a dll version of sqlite, however I use the version from the
official site listed above.
http://adodotnetsqlite.sourceforge.net/

Copy the sqlite dll to your system path, C:\windows or c:\windows\system32.

Create a new C# Console Application. Add a reference to the SQLite.Net
dll that you downloaded.

Now you can copy-n-paste the example code in your Main() method from here:
http://adodotnetsqlite.sourceforge.net/documentation/csharp_example.php

Happy hacking!

Jim
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
There's no place like 127.0.0.1
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
JimD
Central FL, USA, Earth, Sol

 

[JimD]

JimD wrote:

<snip>

I forgot to mention about the connection string in the example. The
connection string is:

Data Source=database.db;Version=3;New=True;Compress=True;

The New=True token says to create a new database. So every time you run
the test program, you are creating a new database, overwriting the old.
You probably don't want to do that with a real application. So you
would only use the New=True in a connection string when you want to
create a database for the first time. After that, you can either remove
New=True or change it to New=False.


Jim
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
There's no place like 127.0.0.1
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
JimD
Central FL, USA, Earth, Sol

 

[ljh]

I was looking at the site () and came across "Version 1.0.14 of the SQLite
..Net Data Provider for ADO.NET 2.0/VS 2005 is out and includes design-time
support. You can now create databases, design queries, and drag-and-drop
tables to create typed datasets within Visual Studio 2005. "

Is this capability still in there? I didn't see any examples of this. If
SQLite is as easy to use as this quote seems to make it (i.e. as easy to use
in the ide as SQL Express) we may just have a winner here.

 

[ljh]

oops! forgot the link to the page where I saw the quote......

http://sourceforge.net/forum/forum.php?forum_id=489095

 

[Sahil Malik [MVP C#]]

Okay .. why does SQL express suck? It doesn't suck .. !!! It runs on a full
fledged SQL engine, sure deployment is a pain, but migrating to a fullblown
SQL Server is relatively painless.


Also, can you elaborate -


?

- Sahil Malik
http://www.winsmarts.com
http://blah.winsmarts.com

 

[Cor Ligthert [MVP]]

Ljh,

Why would you choose SQL Express (which requires an installed application
to work) over the simplicity of an Access database which has no
dependencies?

I see not much reasons why you cannot use better the Jet Engine over SQL
Express, if you want:

That every user can remove the database file at any time he wants
That your data is processed relatively slow
That you cannot use it as an InterNet database withouth webservice or
other webpart
To tell the path at deployment time
Create extra program parts for as the user wants to relocate his
database
Not much security of the data.
To deploy an empty database (wich is great if a user reinstalls and
overwrites his exising database)

Just my thought,

Cor

 

[William \(Bill\) Vaughn]

Ah SQL Express can be used with an IIS web site. SQL Everywhere cannot. It's
not designed to do so but SQL Express (still free) is.
Microsoft has done more for small business than any company I know. It now
offers three versions of its database technology for free. SQL Everywhere,
SQL Express and SQL Express Advanced Services that includes the Reporting
Services engine as well as Full Text Search.

If you just want to bash Microsoft, find some other forum.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________

 

[ljh]

The fact that you can't control SQL Express (due to the fact that you
usually don't control the HOSTED servers) and that you could get more data
into SQL Everywhere (or SQLite for that matter) simply by using the
available disk space than you do with most hosted website's db plans (which
will include hosted SQL Express servers) sucks!

The fact that MS has an edition of Mobile SQL that they are making available
for everything except IIS use sucks!

The fact that Micrsoft added code to SQL Everywhere to PREVENT it from being
used in an IIS process sucks! What about that don't you get?

Microsoft again makes a valiant run downfield with the ball.....only to stop
and sit on the 1 yard line.

 

[ljh]

"INETA Speaker" - at least you're unbiased.

Ah SQL Express can be used with an IIS web site. SQL Everywhere cannot.
It's not designed to do so but SQL Express (still free) is.
Microsoft has done more for small business than any company I know. It now
offers three versions of its database technology for free. SQL Everywhere,
SQL Express and SQL Express Advanced Services that includes the Reporting
Services engine as well as Full Text Search.

If you just want to bash Microsoft, find some other forum.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________

 

[ljh]

The connector for VS 2005 (.Net 2.0) makes connecting to the SQLite db a
snap.

But, it lacks any means of database manipulation (i.e. adding/removing
tables or columns or anything) from within the IDE.

So, ease of use is definitely not up there with SQL Express.

 

[Guest]

I use the SQLiteQA application, which does a very nice job and acts much like
Query Analyzer.

A copy is included in the download for this article:

http://www.eggheadcafe.com/articles/20051119.asp


Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

 

[Cowboy \(Gregory A. Beamer\)]

Let's see:

Ability to do asynch apps (Service Broker)
Server model instead of file system model
Complete support for stored procedures
Full SQL Server security model
Ability to run queries as another user, completely isolating the database
from the user
Better support for types in SQL Server than Access (in .NET)
Ability to schedule backups within the engine
Support for more types than Access
Ability to use XML as a datatype with querying capabilities

Is that enough, or should I go on?

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

 

[Sahil Malik [MVP C#]]

The fact that Micrsoft added code to SQL Everywhere to PREVENT it from

being used in an IIS process sucks! What about that don't you get?

First of all "CALM DOWN".

The fact that you can't control SQL Express (due to the fact that you
usually don't control the HOSTED servers) and that you could get more data
into SQL Everywhere (or SQLite for that matter) simply by using the
available disk space than you do with most hosted website's db plans
(which will include hosted SQL Express servers) sucks!

Moving SQL Express to SQL Server is a peice of cake. I don't see what the
big unsolvable problem here is.

- Sahil Malik [MVP]
http://blah.winsmarts.com

 

[Mike Lowery]

http://msdn.microsoft.com/library/en-us/dnsse/html/sseoverview.asp

 

[ljh]

Just curious......can you tell me a situation where using XML as a datatype
would come into play in a database application?

Why would you store XML in its native format instead of breaking it down
into its component fields for storage and seaarch?

 

[Jos Roijakkers]

There is one more Access advantage: it'll run on Windows XP Home
Edition, while SQL Express will not (requires XP Pro). So if you are
targeting mom&pop shops or the home market, either do Access or stick
to MSDE.

Regards

This is not true. SQL Express runs under XP Home as well (at least it does
on my machine). However, it does not support remote connections by default.

 

[William \(Bill\) Vaughn]

Thanks Sahil. I thought it did but did not have a rig here to test it with.
Of course even the home system could host SQL Server as a service if you
throw the right switches.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________

Sahil Malik [MVP C#] wrote:

There is one more Access advantage: it'll run on Windows XP Home
Edition, while SQL Express will not (requires XP Pro). So if you are
targeting mom&pop shops or the home market, either do Access or stick
to MSDE.

Regards

This is not true. SQL Express runs under XP Home as well (at least it does
on my machine). However, it does not support remote connections by
default.

 

[_DD]

There is one more Access advantage: it'll run on Windows XP Home
Edition, while SQL Express will not (requires XP Pro). So if you are
targeting mom&pop shops or the home market, either do Access or stick to
MSDE.

There are lots of places where an app that normally runs on high-end
XP Pro machines would have to run on XP Home as well. Does this mean
that applications must fit the common denominator: MSDE? I didn't
think MSDE was still supported.

And what about XP Media Center? I'd love to find out about future
plans to adapt to MC and Home editions, assuming there is no way to
target them now.