Transferring Data from MSDE Database to SQL 2000 Database

[smahler]

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

[Erland Sommarskog]

[posted and mailed, please reply in news]

(sm*****@datexmedia.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_addlinkedserver 'SERVERNAME'"- If that does
not work out, check out the topic on sp_addlinkedserver 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp