How To Move MSDE SQL Data To SQL Express

Nov 30, 2006

I have a 3 workstation peer to peer network with everything running Windows XP Pro. I need to replace the machine that hosts my SQL MSDE server. I have built a new system and all software is installed except the MSDE. I use a proprietary product called Time Matters for my law practice that uses the SQL server. Since I might want to run Vista eventually (not right now) I thought that I would try to migrate the MSDE to SQL Express. I assume that Time Matters will run on SQL Express. Can someone please give me some help on how to proceed. I'm thinking that I will install the SQL Express server on the new machine and then somehow migrate the database over to the new machine and shut down the old unit. Any help would be greatly appreciated.

View 1 Replies

We Use Merge Replication And Want To Move From MSDE To Express - Advice?

Apr 3, 2007

Hi,

Our current environment is: SQL Server 2000 backend; XP/Windows 2000 clients with MSDE loaded; a Microsoft Access 2002 app using DSN to connect to the local MSDE database; Merge replication which is a job within the MSDE engine that we execute in VBA code using the following DMO code:

' Find the Job name to execute

lngJobCount = oServer.JobServer.Jobs.Count

lngJobIndex = 1

blFoundJob = False

While lngJobIndex <= lngJobCount And blFoundJob = False

strJobName = oServer.JobServer.Jobs.Item(lngJobIndex).Name

Me.lstJobs.AddItem strJobName, 0

If InStr(1, strJobName, "LEXData", vbTextCompare) > 0 Then

If InStr(1, strJobName, strServerName, vbTextCompare) Then

blFoundJob = True

End If

End If

lngJobIndex = lngJobIndex + 1

Wend

' Execute the job

If blFoundJib Then

Set oServer1 = New SQLDMO.SQLServer

With oServer1

.LoginSecure = True

.Connect strServerName

End With

Set oJob = oServer1.JobServer.Jobs(strJobName)

End If

We are now planning a move to a SQL Server 2005 backend and Express edition on the clients and the app would remain as Access 2002. Any pointers to documents that will be useful would be appreciated but I have a couple of specific questions as well.

1) As I understand it we can load Express side by side with MSDE so we will probably go down that path and load the data from MSDE into an Express version of the database. Once that's done we will create a DSN pointing to the new database and name it the same as the DSN which is currently used for the MSDE database. If we do this will our Access app simply work as before (ignoring replication)?

2) When we have our app happily running against a DSN that points to an Express database would we expect our existing DMO code (as above) to work, e.g. find a particular job in a list jobs and executes it? I can see that the replication job itself may have to be rejigged for Server 2005.

TIA - Peter

View 9 Replies View Related

Upgrade MSDE To SQL Express Former Data Hidden New Data Visible

Dec 27, 2006

After my upgrade from MSDE SP 4 to SQL express (backup and restore) my application will not see the data any more.

If I use my application (VB Dot.NET 1.x ; DB= SQL Express) to add new data (rows) this new rows are visible.

Both old and new datas are seen in SQL management studio express.

If i add rows with the SQL management studio express the appliction will not see this rows.

In both cases I connect with sa - account. (Administrator even doesn't work). I search for any restrictions on row basis but i wonder why the management tool display all rows.

Any ideas!