Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
next78
Starting Member
9 Posts |
 Posted - 2008-06-30 : 05:20:06
|
Hi, I have 2 DB with the same tables. I need to copy data table1 in DB1 to table table1 in DB2.I need to do this from SQL query from VB.NET app. I tried this:insert into DB2.table1 select * from DB1.table1 but it displays error:quote:
"System.Data.SqlClient.SqlException: the name of the object 'DB1.table1' is not valid"
But if try this query in SQL Server express IDE, it runs ok but from my app VB.NET not.Already I tried this too: insert into DB2.dbo.table1 select * from DB1.dbo.table1 but it displays the same error:quote:
"System.Data.SqlClient.SqlException: the name 'DB1.dbo.table1' is not valid"
Thanks in advance for any help.regards |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-30 : 06:44:47
|
| Are your connection strings pointing to correct db? |
 |
|
|
next78
Starting Member
9 Posts |
Posted - 2008-06-30 : 07:32:06
|
| My connection string is pointing to main DB. For example: I have these databasesDB, DB1, DB2, DB3, DB4.....DB is main database. Other databases are copies of the DB.My conn string:"Data Source=IPServer,numberPort;Network Library=DBMSSOCN;Initial Catalog=DB;User Id=user;Password=pass;"regadrs |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-30 : 07:46:50
|
quote:
Originally posted by next78
My connection string is pointing to main DB. For example: I have these databasesDB, DB1, DB2, DB3, DB4.....DB is main database. Other databases are copies of the DB.My conn string:"Data Source=IPServer,numberPort;Network Library=DBMSSOCN;Initial Catalog=DB;User Id=user;Password=pass;"regadrs
and they are all in same sever? |
|
|
|
next78
Starting Member
9 Posts |
Posted - 2008-06-30 : 08:10:30
|
| Yes. But I solved the problem. I was reusing a query that I use to copy data from DB access and I must to put DB names between [] . But in SQL Server I dont need to put DB names between []. Sorry.But now I have other problem. I have tables with identity fields. When I'm trying to copy data to these tables, it appears this error:Cannot insert explicit value for identity column in table 'Table1' regards |
|
|
|
next78
Starting Member
9 Posts |
Posted - 2008-06-30 : 08:19:16
|
| Ok I think that I can solve this using SET IDENTITY_INSERTEdited:I tried "SET IDENTITY_INSERT DB1.table1 ON"[query to insert data]"SET IDENTITY_INSERT DB1.table1 OFF"but it continues displaying the same error |
|
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2008-06-30 : 22:09:28
|
| You will need to specify the columns you are inserting into:INSERT INTO table1(col1, col2)SELECT col1, col2FROM table2 |
|
|
|
next78
Starting Member
9 Posts |
|
|
|
|
|
|
|
Copy data table1 in DB1 to table1 in DB2