MSDE Advice Please

Hi I am planning a desktop application I want to develop in C# and I was thinking about using MSDE instead of an Access database. I have never used of even looked into MSDE before. Do you simply create a database on SQL Server add your tables and Sp's, setup your conection and program against this as normal, then with the install of the software setup MSDE and install the tables and SP's etc into the instance of MSDE?

TIAFrom my experience MSDE has some limitations (db size, number of connections, etc.) but it is real database engine with all features. You can manage it by ISQL or OSQL (if you have installed somewhere in NET enterprise manager - you can use it for MSDE).|||Hi,

The database size is limited to 2GB. Once the DB gets to this size MSDE will not allow the program to use it. You can still go into the DB with Enterprise Manager or Query Analyzer or shareware tools. But nothing will run.

As for connections I beleive the limit is 30.

As for your install.

Create the database on the SQL server and write your program against it. Then back the database up when you are finished debugging it. Include it with your install packaging (I am not a developer so I won't tell you how).

When the install process starts have the MSDE install first (See MS Knowledge Base or google "MSDE install"). Then restore the database to the MSDE ( I suggest osql). Then create your ODBC connection. And finish with your program.

Have fun !|||Thanks for all your comments and advise :)

MSDE 2000 which version to use?

Hi,
I'm planning to develop a VB.NET application using MSDE but came across
these sites... so I'm sort of confused which version of MSDE 2000 to use.
http://www.microsoft.com/downloads/d...displaylang=en
http://www.microsoft.com/downloads/d...displaylang=en
After checking the above two sites, please try to answer the following
questions:
1. What's the difference between these two MSDE 2000?
2. Which one to use for development? (VB.NET)
3. Which one to re-distribute with my custom application?
I was thinking that I have to use the 70MB MSDE file. BUT after comparing
the dates, I'm a bit confused. The 70MB file is older.
Thank you for you time.
Sukhdev.
hi Sukhdev,
"Sukhdev" <Sukhdev@.discussions.microsoft.com> ha scritto nel messaggio
news:73B0850D-01E3-41C3-83D5-CF1ED3DAE36B@.microsoft.com
> Hi,
> I'm planning to develop a VB.NET application using MSDE but came
> across these sites... so I'm sort of confused which version of MSDE
> 2000 to use.
>
http://www.microsoft.com/downloads/d...displaylang=en
>
http://www.microsoft.com/downloads/d...displaylang=en
> After checking the above two sites, please try to answer the following
> questions:
> 1. What's the difference between these two MSDE 2000?
the final result, after installation, is actually the same... both install
MSDE at the 3a service pack level..
but there's both a technical and an EULA difference..
MSDE Web Release A can only install fresh instances of MSDE 2000, while the
other package can both install new fresh instances and upgrad existing
instance at the service pack 3a level
as regard the EULA, the second package must be licensed by an appropriate
license of Visual Studio, Fox Pro, MSDN, SQL Server and so on, while MSDE
release A is really free for download, and you only have to register for
redistribution rights if you want to at
http://www.microsoft.com/sql/msde/ho...tregister.asp, but not for
use...

> 2. Which one to use for development? (VB.NET)
I strongly suggest to buy the Developer edition of SQL Server (about $50),
which includes all the server tools like Enterprise Manager, Query Analyzer,
Profiler and all the wizards like Index Tuning to troubleshoot development
issues...

> 3. Which one to re-distribute with my custom application?
chose the one you prefer... redistribution, in this case, is not a
problem...(as long as you are entitled for both)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE 2000 or SQL Server 2005 Express (Beta) for new project?

Hello guys! I have to develop a new web based project. It's a webportal for events. At the moment it's runnung on an Access database.The new version should be running on a SQL server - the desicion is:MSDE 2000 or SQL Server 2005 Express (Beta). What do you recommend? Thewebpages are developed under .NET 1.1.
SQL Server 2005 Express is not going to be as stable and is not aproven technology as is MSDE. But if your release date somewhatdistant (several months), you may want to use it (hopefully the RTMversion will be relased before you finish.) You'll still be anearly adopter, though, and may have to deal with bugs. In theend, it's a question of: What features do you need in Express that MSDEdoesn't provide? And do these features counter-balance the issuesof working with a beta?
|||Well ... it's a company philosophy to work always on the newsestplattform. If you are common with the MSDE and the SQL Express 2005edition you can surly say if there are some performance improvements.The project leaders think that the MSDE version is not so fast. Specialfeatures are not requested, only SQL querys and stored procedures (...and performance ). Is a change of database engine (from MSDEto SQL 2005) a problem in the future?
Thanks a lot!!!
|||

Here my first look at SQL Server 2005, an events web site needs Time Interval, it is built in SQL server 2005 and index Column include without Composite index. It also schreds and decomposes XML native. Hope this helps.

SQL SERVER 2005 Development

What is new for ASP.NET

The most important is CLR integration write your stored procedures in C#.NET and VB.NET.

INDEX COLUMN INCLUDE, NEW DRI(Declarative Referential Integrity) CASCADE DELETE SET NULL AND CASCADE UPDATE SET NULL, When used with Portal Templates allow the user to browse before login. This is almost like Denormalization with Composite index without the Primary Key restriction of NOT NULL. ANSI NULL enabling on index columns.

Varchar (max), Nvarchar(max) Varbinary(max) Variable length data that may exceed 8000 and 4000 with 2gig limit, in the past these are stored in Text and Image, SQL Server creates an

Arithmetic pointer to the data because it is not table row based.

DTS is now Integration Service with pipeline.

Service Broker can be used to Queue shipping data while SQL Server will process sales Transactions.

XML DATATYPE

XML native Datatypes including XML indexes and XML decomposition.

XML QUERY EXAMPLE using XML PATH

SELECT CustomerID as "@.CustomerID",

(SELECT OrderID as "@.OrderID"

FROM Orders

WHERE Orders.CustomerID = Customers.CustomerID

FOR XML PATH('Order'), TYPE),

(SELECT DISTINCT LastName as "@.LastName"

FROM Employees

JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID

WHERE Customers.CustomerID = Orders.CustomerID

FOR XML PATH('Employee'), TYPE)

FROM Customers

FOR XML PATH('Customer')

COMMON TABLE EXPRESSIONS CTE VIRTIUAL VIEWS

VIEWS are Query Rewrites in ANSI SQL

ANSI SQL 99 with Recursive QUERY Implementation some restrictions.

Tables must be equal INNER JOIN

CONVERT clause can be used to meet the UNION operator requirement.

But multiple Recursive members can only be connected by UNION ALL not UNION because UNION removes duplicates just like SELECT DISTINCT .

Recursion Control

Default server wide - 100

Configurable maximum iterations

OPTION MAXRECURSION <value>

1-32767

Recursive Member Restrictions

No operations leading to DISTINCT

Only UNION ALL allowed between anchor and recursive

SELECT DISTINCT

GROUP BY

HAVING

Scalar Aggregation

TOP

Outer Joins

1 CTE reference per recursive member

The example below in the executing statement, the CTE is referenced twice; that is the reason for the OUTER JOIN, the OUTER JOIN is not allowed with Recursive CTE because in ANSI SQL OUTER JOIN has a default NULL condition and is limited to four iterations while a Recursive CTE can run to 100.

USE AdventureWorks;

GO

WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,

E.ManagerID, OM.NumberOfOrders, OM.MaxDate

FROM HumanResources.Employee AS E

JOIN Sales_CTE AS OS

ON E.EmployeeID = OS.SalesPersonID

LEFT OUTER JOIN Sales_CTE AS OM

ON E.ManagerID = OM.SalesPersonID

ORDER BY E.EmployeeID;

GO

Recursive CTE using CONVERT clause to satisfy the UNION ALL operator requirement.

USE AdventureWorks ;

Go

WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)

AS (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),

e.Title,

e.EmployeeID,

1,

CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)

FROM HumanResources.Employee AS e

JOIN Person.Contact AS c ON e.ContactID = c.ContactID

WHERE e.ManagerID IS NULL

UNION ALL

SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +

c.FirstName + ' ' + c.LastName),

e.Title,

e.EmployeeID,

EmployeeLevel + 1,

CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +

LastName)

FROM HumanResources.Employee as e

JOIN Person.Contact AS c ON e.ContactID = c.ContactID

JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID

)

SELECT EmployeeID, Name, Title, EmployeeLevel

FROM DirectReports

ORDER BY Sort ;

GO

The following example is using MAXRECURSION to cancel a statement.

USE AdventureWorks ;

GO

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS

(

SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel

FROM HumanResources.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1

FROM HumanResources.Employee e

INNER JOIN DirectReports d

ON e.ManagerID = d.EmployeeID

)

SELECT *

FROM DirectReports

OPTION (MAXRECURSION 4) ;

GO

First look at SQL Server 2005 development. Comments appreciated.