Unanswered: Copying records from 7.0 2000 with problems
I need to copy data from one database to another, on separate servers. I've got an old production database on "server1" and its SQL Server 7.0 on NT4. This data needs to move from there over to a database on "server2" which is SQL2000/Windows2000server.
Im trying to just run test queries from "server2" to read tables on "server1" and all I can get is 'invalid object name.'
Server1 is a linked server under Server2 and is being logged into as sa.
The computername for server1 happens to be "DATABASE"
Is this a problem? From what I understand I should be able to select from <server name>.<dbname>.dbo.<tablename>
I have tried typing <server name> as [DATABASE] and "DATABASE" and any other delimiting under the sun but all I get is 'invalid object name'
I have also tried just trying to select max(helptext) from "DATABASE".master.dbo.helpsql and I still get 'invalid object'
All of my queries will work if run from query analyzer on "server1" but not from query analyzer on "server2"
do you intend to move all the data in the database??
if you are maybe you can use the dts tools or if possible restore the full backup to the server2.
I can't really remember if sql 7's backup file can be restore to sql 2k version as haven't been using for quite a long time but you can try.
I Turned Mixed Protocol OFF on the old 7.0 box and turned it ON the 2K box. Go Figure. The most confusing part of this ordeal was that the login for linked server was working from both servers and you could see the master tables/views under linked server.