Posted by Erland Sommarskog on 06/18/05 12:18

[posted and mailed, please reply in news]

(smahler@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, esquel@sommarskog.se

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

[Back to original message]