473,695 Members | 3,060 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Transferring Data from MSDE Database to SQL 2000 Database

Hi all,
Here is my situation.
I have an application that uses SQL 2000. The client has a program
that uses MSDE. The client MUST have the MSDE version of the Database.
What I have done is to create a SQL 2000 Database that is exactly like
the MSDE version. What I wish to do, is at regularly scheduled
intervals, update the SQL 2000 Data with the newest MSDE Data.

On my clients server, they have both MSDE and SQL 2000 installed
the msde has a user name and password to access it, whereas the SQL
2000 uses authenticated mode to access it.

What I'm trying to do is to write a bunch of SQL Queries, that I can
run that will copy over the new Data, but I'm having problems accessing
the msde database in query analyzer from the SQL Database

Can anyone give me hints on the best way to try this

Thanks

Scott

Jul 23 '05 #1
1 2189
[posted and mailed, please reply in news]

(sm*****@datexm edia.com) writes:
Here is my situation.
I have an application that uses SQL 2000. The client has a program
that uses MSDE. The client MUST have the MSDE version of the Database.
What I have done is to create a SQL 2000 Database that is exactly like
the MSDE version. What I wish to do, is at regularly scheduled
intervals, update the SQL 2000 Data with the newest MSDE Data.

On my clients server, they have both MSDE and SQL 2000 installed
the msde has a user name and password to access it, whereas the SQL
2000 uses authenticated mode to access it.

What I'm trying to do is to write a bunch of SQL Queries, that I can
run that will copy over the new Data, but I'm having problems accessing
the msde database in query analyzer from the SQL Database


You would have to set up a linked server to the MSDE server, and then
refere to it through four-part notation. Setting up a linked server
may as easy as saying "sp_addlinkedse rver 'SERVERNAME'"- If that does
not work out, check out the topic on sp_addlinkedser ver in Books Online
for details.

However, there may be other options. If want you want is a complete
copy of the database, using BACKUP/RESTORE sounds like a much easier
solution.

Yet alternative is replication. Now, an MSDE instance cannot be a
publisher, so you cannot use transactional replication. But merge
replication could be an alternative. I don't have any experience of
merge replication myself, though.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2045
by: Mike N. | last post by:
I am writing a database app which will run under MSDE on the user's systems. Other than limitations as to the number of concurrent users, are there any issues that I need to be aware of when programming the database using SQL Server 2000? i.e., are there T-SQL programming statements that will run under SQL Server 2000 as part of my development enviornemnt that won't run under MSDE on the end users machines, or will MSDE handle anything...
5
8423
by: Igor Solodovnikov | last post by:
Hi. I am trying to automatically backup transaction log when error 9002 happened. So i have created appropriate job and alert to catch this error. I have two instances of sql server under Windows 2000. One of them is full SQL Server, another is msde. When transaction log is full in full SQL Server error 9002 severity 17 state 2 is logged in sql server log and in Windows Application log. My alert firing my job. All is fine. But when...
2
3198
by: winshent | last post by:
Hi I am a novice with SQL server as i have experience with access. I need to do some front end development in access for a client who runs SQL 6.5 or so he tells me... (i have been told that data files under 6.5 are .dat and under 7.0 they are .mdf? is this correct?) I have taken a copy of the data file which is an mdf file with the intention of running it on my machine which has SQL 2000 Developer Ed installed on windows XP pro.
1
1497
by: Diana Finley | last post by:
Hi all, I just got SQL Server 2000, but I already have MSDE installed. Is there any reason I would want to keep MSDE around on my computer if I install SQL Server 2000? I use MS Visual Studio 2002 on Windows XP SP2, in case it makes any difference. I intend to deinstall MSDE before installing SQL Server 2000, but I get the impression from a few posts that some people have both installed and I'm not sure why that would be done. What...
1
1967
by: Sebastian Santacroce | last post by:
Is it posible to package MSDE with VB .net so that its part of the installation? If so, is there documentation somewhere? What about packaging the .net framework with the application as well? Thanks ss
5
1258
by: asdf | last post by:
Hi, Currently, I have one database for Intranet clients on the server behind the firewall. But I was asked to allow the extranet users to access the same data. From the security perspective what is the best way to make that data accessible. Should we have two databases one for Intranet and one for Extranet? In that case the second one would be a replica but that would cost us money for the extra MS SQL license. Maybe it is OK to have...
2
1554
by: isa | last post by:
Hello everyone, i want to transfer/copy data from MSDE to SQL Server 2000 and from SQL Server to MSDE, through Stored Procedures , not using a "Replication", kindly tell me how i make a SP for this as both datatbases are connetec through LAN, i mean MSDE on diff machine and SQL Server on other machine i make a new registration and connect with MSDE now how can i write a SP that copy data from table of 1 Database to other table of that...
2
2590
by: Bob | last post by:
Trying to transfer databases from MSDE to SQL. When I use SQL Enterprise Manager - DTS - import or export, it works for the first database, but then fails for the others. I select the to and from databases using DTS - Import - 'MS OLE DB Provider for SQL Server', then select 'Copy objects and data between SQL Servers'. It fails with the generic error message: 'Copy objects failed', nothing further. I tried to just copy data, and it...
11
6239
by: HC | last post by:
I posted this in one of the VB forums but I'm starting to think it might be more appropriate to have it here, since it really seems to be a SQL server (MSDE/Express 2005) problem: Hey, all, I have a problem with queries against a SQL server instance that I just found and is causing me fits. I hope someone can point me in the right direction, please. TIA. Basically, I got a Vista OS machine to test my VB6 app on it as some
1
8824
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8817
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7651
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6484
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4336
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4571
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2258
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1970
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.