Copying records from 7.0 2000 with problems

Hi.

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"

thanks in advance,

-jakedo 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 am only moving the data from select tables. The database names are different too.

I've pretty much ruled out the possibility of a backup/restore senario.|||have u tried import/export ??|||It was a communication issue.

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.|||sorry in that case can't help you much
hopefully somebody else can help you on this|||Wow. That was like phone tag in a message board. Don't think I've ever experienced that.

Copying database to same machine?

Hi all,
I've read a lot of posts about copying a SQL database to a separate machine;
but, what about to the same machine.
In practice for others who have done what is the simplest reliable method?
Also, is there any reason not to detach the database, copy the physical
files (mdf and log) to a different name and reattach it under that other name?
Thanks!
gman997 wrote:
> Hi all,
> I've read a lot of posts about copying a SQL database to a separate
> machine; but, what about to the same machine.
> In practice for others who have done what is the simplest reliable
> method?
> Also, is there any reason not to detach the database, copy the
> physical
> files (mdf and log) to a different name and reattach it under that
> other name?
> Thanks!
If you need this totally scripted, you should create the new database and
use backup/restore. If you don't mind taking the database offline and making
a copy, you can use detach/attach. Detach is probably faster if your
database can be taken offline. In any case, I would perform a full backup
before doing anything, and in that respect, it might just pay to restore to
a new database...
David Gugick
Quest Software

Copying database to same machine?

Hi all,
I've read a lot of posts about copying a SQL database to a separate machine;
but, what about to the same machine.
In practice for others who have done what is the simplest reliable method?
Also, is there any reason not to detach the database, copy the physical
files (mdf and log) to a different name and reattach it under that other nam
e?
Thanks!gman997 wrote:
> Hi all,
> I've read a lot of posts about copying a SQL database to a separate
> machine; but, what about to the same machine.
> In practice for others who have done what is the simplest reliable
> method?
> Also, is there any reason not to detach the database, copy the
> physical
> files (mdf and log) to a different name and reattach it under that
> other name?
> Thanks!
If you need this totally scripted, you should create the new database and
use backup/restore. If you don't mind taking the database offline and making
a copy, you can use detach/attach. Detach is probably faster if your
database can be taken offline. In any case, I would perform a full backup
before doing anything, and in that respect, it might just pay to restore to
a new database...
David Gugick
Quest Software

Copying database to same machine?

Hi all,
I've read a lot of posts about copying a SQL database to a separate machine;
but, what about to the same machine.
In practice for others who have done what is the simplest reliable method?
Also, is there any reason not to detach the database, copy the physical
files (mdf and log) to a different name and reattach it under that other name?
Thanks!gman997 wrote:
> Hi all,
> I've read a lot of posts about copying a SQL database to a separate
> machine; but, what about to the same machine.
> In practice for others who have done what is the simplest reliable
> method?
> Also, is there any reason not to detach the database, copy the
> physical
> files (mdf and log) to a different name and reattach it under that
> other name?
> Thanks!
If you need this totally scripted, you should create the new database and
use backup/restore. If you don't mind taking the database offline and making
a copy, you can use detach/attach. Detach is probably faster if your
database can be taken offline. In any case, I would perform a full backup
before doing anything, and in that respect, it might just pay to restore to
a new database...
--
David Gugick
Quest Software