[RESOLVED] MSDE same as using MDB?

[ober0330]

We've been using local Access databases for keeping track of local stuff, but we now have a SQL 2000 server running most of our stuff. We have one app that runs on various PCs that writes to a local Access database.

What I'd like to do is run a local version of a MSSQL database on those PCs so it's easier to update the global SQL server as I see fit.

My question is:

How do I go about creating a local MSDE file and writing to it/interacting with it?

I plan to use ADO and I know how to connect to a database and all that, I just don't know if I need to have something like SQL express running on each local machine or not. I was hoping the process would be similar to using a MDB file.

Help?

[si_the_geek]

What I would do is create tables on the server instead, and put the data into them with an extra column to say which PC/User they came from.

That would remove the need to install extra software etc on each PC, and would make the next steps simpler - as all the data is easily accessible to you (instead of having to connect to each PC individually).

[ober0330]

Well, the problem is that the software running locally will often be running without a connection to the global server, hence the need for the local version. It's not a question of global vs. local, because the local versions will be syncronized on a regular basis with the global for reporting needs.

So my question goes back to the how-to.

[ober0330]

Anyone?

[salvelinus]

ober, you're not in PA anymore!
What's the problem with using SQL Server Express? It's free, has an available visual IDE, is closer to SQL Server in design, etc.

[ober0330]

Sup sal?

Well, I guess the thing is that I don't want to have a server running in the background all the time, unless you can tell me how to start and stop it. I just want a database that I can write to that isn't Access, but has the same features as the runtime version (I thought that's the way the MSDE format was, but I guess I'm wrong).

[salvelinus]

Yeah, MSDE is closer to SQL Server than Access. Express I know how to start & stop using the Configuration Manager, but not programmatically. We're converting from Filepro & Access to SQL Server as well, but hoping to rewrite our apps to just use SS.
You might try here as well.

[ober0330]

Thanks for the input. I assume connecting to and dealing with Express is the same as Enterprise?

EDIT: And I changed my location

[salvelinus]

Can't say, we haven't rolled anything out yet. I believe the T-SQL's the same, connections here.

[Dave Sell]

I am using MSDE for a client-side project (no access to server) and am quite pleased with the results. Where are you with all this?

[ober0330]

The project is 90% done, but I'm doing some database re-write and upgrade from Access to MSDE. I downloaded and installed SQL Server Express yesterday and I'm about to test the connection to the local server/database from VB.

Did you have to install the server or how do you deal with it?

[Dave Sell]

Isn't SQL server express a light version of SQL Server 2005? That's not what I am using. I have posted some comments on the forums regarding how I deal with it. I use the SQL Server client tools to do some of the work.

[ober0330]

Yes, that's correct. Can you point me to where you made these comments?

[Dave Sell]

You can do an advanced search of the forums. Choose my name as the poster's name and MSDE as the search criteria.

[Dave Sell]

OK don't use MSDE as a search criteria. Looks like not much came up. Try my name and DBCC or other database words.

[ober0330]

OK.... I will in a bit. S*** just hit the fan here.

[ober0330]

Unless you're referring to the "Linked Servers" thread, I don't see what you're getting at.

Besides, I plan to do the update of the global server through VB, since I don't really have control over the production server and I won't be able to go in and do all the setup for linked servers.

If there is a specific post you're referring to, can you point it out?

[Dave Sell]

Well I wasn't able to find too much:

http://www.vbforums.com/showthread.p...light=database

This might be of use:

<INSTALL.BAT>

.\SETUP.EXE SAPWD="msde" INSTANCENAME="MSDE" DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL DATADIR="C:\Temp\" TARGETDIR="C:\Program Files\MSDE\"

When MSDE extracts into a folder, place this file in the folder next to the SETUP.EXE and run it.

This installs MSDE to the Program Files folder, creates a named instance of MSDE called MSDE and a "sa" password of msde, and uses C:\Temp as the location of the datafiles.

Do you have the SQL Server client tools?

[ober0330]

Hmm... and you can use that database just like you were running it off of the full SQL Server (queries of all types?).

What setup.exe file are you talking about? And I have the Enterprise Manager, if that's what you're referring to.

[Dave Sell]

Hmm... and you can use that database just like you were running it off of the full SQL Server (queries of all types?).

Yes, but there are some limitations, like I think you don't get stored procedures. I have been using it for a while for high-performance logging and am quite pleased with the performance.

What setup.exe file are you talking about?

When you download the MSDE executable it extracts itself to a folder on your hard drive. In that new folder is a setup.exe

And I have the Enterprise Manager, if that's what you're referring to.

Yes that, and the Query analyser can be used with MSDE. I used them to create new databases, handle minor user privies, and run SQL statements.

[ober0330]

Ok... and I guess the MSDE executable is something I can download from microsoft.com?

And I don't need stored procedures... I just need a few tables that I can grab data from, update, and insert new.

[ober0330]

Ok, nevermind... I found it and I'm installing right now. Thanks for your help... it's been invaluable!

[ober0330]

OK, new question... how do I use Enterprise Manager to connect to a MSDE instance? Do I register a new server???

[Dave Sell]

Just like you would register any SQL server. I had zero problems with this it was straightforward. Post if you get stuck.

[ober0330]

***... Ok, I downloaded it, put the batch file in the folder, changed the locations, and it ran like it installed it. The files were created and I have the master database, etc. When I try to register the server, I guess I'm not sure what to put in for the name of the server. No matter what I put in, it says it can't find it.

And it's not on the list of servers. Do I need to restart or start a service or something?

[ober0330]

Ok, I rebooted and it's all up and functional now. Nevermind. Thanks for the help!

[szlamany]

I'm a little confused - maybe I needed to read these posts a bit more...

But are you considering using MSDE over SQL SERVER EXPRESS?

Look at this link:

http://www.vbforums.com/showpost.php...28&postcount=5

It's from the sticky at the top of the DB forum here about SQL 2005 stuff...

This link points to an MSDN article on how to use SQL SERVER EXPRESS more like you would ACCESS - as opposed to loading the database into the server, like full-blown MS SQL SERVER requires...

Using Visual Basic 6 to Retrieve Information from SQL Express 2005 by File Path

Dim cn As ADODB.Connection
Set cn = New Connection
cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"AttachDBFileName=" & App.Path & "\northwnd.mdf;Data Source=server1\sqlexpress"
cn.Open

Dim rs As Recordset
Set rs = New Recordset
rs.Open "Select * from orders", cn
Set MSHFlexGrid1.DataSource = rs

This is revolutionary functionality. This means that you can simply ship your SQL server database as an MDF file with your application. You don’t need to do anything to install the database. You just put the path to the database in the connection string, and you can select, insert, update, delete, call stored procedures, and access any other database functionality. This is also great functionality for Web applications, which can now just keep a copy of the MDF file in the web application directory, and use it as easily as an Access database.

You can see that the path to the database is specified through the AttachDBFileName value in the connection string. You’ll also notice that this connection string is not using the typical SQL OLE-DB provider. When you install SQL Express, it installs a new OLE-DB provider, called the “SQL Native Client”, that gives you access to some of the new SQL 2005 functionality, specifically, the ability to attach to a database by file name. This is specified in the connection string with the statement “Provider=SQLNCLI”. Once the connection string is specified, you can retrieve recordsets, perform updates, and do any other database operations exactly as you would normally do with SQL server.

[ober0330]

So let me get this straight:

With that method, all I have to do is create the MDF file in SQL Server express and then include the MDF in the install of my VB app? And I have full functionality to it? What about the OLE-DB Provider? Where do I get that to install on my computers that run the app without the server?

[Dave Sell]

Sounds like you (a) must supply a working .MDF file with your distribution, and (b) require the target machine already has SQL Express installed for this to work.

Seems like more trouble for less results than a single distributable install of MSDE?

[szlamany]

MSDE is being replaced by SQL SERVER EXPRESS...

The new "connect" MDB method allows you to have a functional database experience without SERVER manipulation of the file.

MSDE and SQL SERVER express install the same way - they are the same thing!

[Dave Sell]

You don't need to supply an existing database with MSDE which is based on SQL Server 2000. Is the same true for SQL Server Express, or is the .MDF file manditory?

What about performance differences between MSDE and SSX with MDB file?

I thought SSX was based on SQL Server 2005?

[szlamany]

Comparison with MSDE
SQL Server Express replaces Microsoft SQL Server Desktop Engine (MSDE) in SQL Server 2005 and provides numerous ease-of-use features that enable it to be used by a nonprofessional developer or hobbyist. MSDE is based on SQL 2000 technology and is recommended for use with Windows 9x platforms, while SQL Server Express is based on SQL Server 2005 technology. SQL Server Express has features such as Application XCopy, Robust Setup UI, CLR support, GUI tools, and Visual Studio Integration that are not present in MSDE. However, some features are removed from SQL Server Express compared to MSDE. These features include DTS, replication publishing, and SQL Agent.

The use of merge modules for deployment has been a servicing problem for MSDE, and this functionality is not available in SQL Server Express. The workload throttle in MSDE was sometimes difficult to understand and use. In SQL Server Express, the throttle is removed and instead the engine uses CPU, RAM, and database size limitations to differentiate it from the other editions. The table below shows the comparison of these products.

Regardless of what product - MSDE or SSX - the data (MDB) had to get on the machine (if the goal was to include data). Otherwise you can use SSX or MSDE to create a new database - it's identical in that regard.

One good change was that MSDE relied on MDAC - and that's a problem with other installs of other apps. All the SQL 2005 products can use a new native client - SNAC...

http://msdn.microsoft.com/library/de...seoverview.asp

[edit] for clarity...

[szlamany]

Not sure if this will post cleanly...

Table 2. Comparison of MSDE with SQL Server Express
 
SQL Server Express2005                  MSDE 2000 
User Instance support               Feature not present 
DTS in separate download                  DTS runtime present 
Easy deployment because of no MDAC      MDAC is part of install 
MSI only, good servicing story MSI and MSM, servicing of MSM hard 
Subscriber Replication for Transactional, Merge, and snapshot Merge/snapshot publication supported in addition to replication subscription 
Robust setup UI Basic setup UI 
No agent                                  Agent present 
Supports Windows 2000 SP4, Windows XP SP1, and Windows 2003 Supports Windows 98, Windows Me, Windows 2000, Windows XP, Windows NT4, and Windows 2003 
CLR support                          No CLR support 
GUI tools available                             No GUI tools 
Database size limit: 4 GB                    Database size limit: 2 GB 
1 CPU, 1 GB RAM                             2 CPU, 2 GB RAM 
No throttle Workload throttle enforced for 5 concurrent workloads 
Deep integration with Visual Studio Basic integration with Visual Studio

[Dave Sell]

From the read it looks like SSX doesn't work with VB6, only ADO.NET?

[szlamany]

From the read it looks like SSX doesn't work with VB6, only ADO.NET?

That's not true at all - please be careful what you post as others might get the idea that it's a real limitation

http://msdn.microsoft.com/vbrun/vbfu...ingsqlexpress/

[szlamany]

Using Visual Basic 6 to Retrieve Information from SQL Express 2005 by File Path
 
    Dim cn As ADODB.Connection
    Set cn = New Connection
    cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "AttachDBFileName=" & App.Path & "\northwnd.mdf;Data Source=server1\sqlexpress"
    cn.Open
    
    Dim rs As Recordset
    Set rs = New Recordset
    rs.Open "Select * from orders", cn
    Set MSHFlexGrid1.DataSource = rs
This is revolutionary functionality. This means that you can simply ship your SQL server database as an MDF file with your application. You don’t need to do anything to install the database. You just put the path to the database in the connection string, and you can select, insert, update, delete, call stored procedures, and access any other database functionality. This is also great functionality for Web applications, which can now just keep a copy of the MDF file in the web application directory, and use it as easily as an Access database

[Dave Sell]

That's not true at all - please be careful what you post as others might get the idea that it's a real limitation

http://msdn.microsoft.com/vbrun/vbfu...ingsqlexpress/

From the URL above:

There are some interesting dynamics based on the interactions between MDAC providers and the SQL Native Client providers. For instance, MDAC 2.5, 2.7, or 2.8 cannot connect to SQL Server 2005 using Shared Memory. This affects any native application using SQL OLE DB or SQL ODBC and includes not only existing native applications, but also the managed OLE DB or ODBC applications since they use MDAC internally. Typically for SQL Server, if the shared memory connection fails, networking protocols such as TCP/IP are used. However, for SQL Server Express, since the networking is turned off by default, these applications would simply fail to connect. The workaround is to change the apps to use the SQL Native Client provider or enable networking Transmission Control Protocol (TCP) and start SQL Browser.

[szlamany]

That just says turn on TCPIP (off by default since SSX is intended for local use, but can be networked) or use SNAC. SNAC is not required - MDAC is fine - just turn on TCPIP.

[Dave Sell]

roger - thanks for all that.

[ober0330]

Then why are they 2 different products? Seems kind of ridiculous.

Besides, why should I install SQL Server Express when it requires .NET 2, MSXML6 and XP SP2 to even function? MSDE works without all those requirements and I get the same basic functionality!