April 11, 2007 at 1:26 am
SELECT * INTO SecondDB.dbo.table2 FROM FirstDB.dbo.table1
Above query did not work. Both databases are running in the same machine under the single instance of the sql server.
Is there any way to copy multiple tables just by drag and drop from one db to another?
April 11, 2007 at 2:22 am
Hi,
table2 and table1 having same defination?
Can you post error message?
Regards,
Anil Kumar
April 11, 2007 at 11:07 am
AnilKumar,
Yes, table1 and 2 are of exactly same defintion. I tried without table2 too. INTO clause suppose to create the table. Nevertheless, following error message is the same whether I have preexisting table 2 or not.
Error: invalid database object FirstDB.dbo.table1 is not found.
I discovered something new. My query works if both databases are created from scratch but doesn't work if one or both db are attached from an existing files.
April 11, 2007 at 11:33 am
Do both databases have compatibility level 90 ? You can find it in database properties/ options.
April 11, 2007 at 1:01 pm
Mark,
I am using vs.net 2005 server explorer to access sql server 2005. I could not see compatability level 90 in properties of the db. But the SecondDB was created using sql server 2000 and now I attached it to sql server 2005. FirstDB is created in 2005. I no longer use 2000.
Error message doesn't say antying about compatability though.
April 11, 2007 at 2:35 pm
You can check database compatibily level by running this query:
select
name,
compatibility_level
from master.sys.databases
where name in ('FirstDb', 'SecondDb')
If any of them will have compatibility level 80 (which is 2000) I recommend you to upgrade it to 2005 by runnning this:
use master
exec sp_dbcmptlevel SecondDb, 90
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply