Copy rows from SQLCe to SQl Server .net
Posted on 2010-08-29
I'm having a bit of trouble copying table data from a SQLCe Database into a SQL Server DB(2005). The Tables are not identical and do not have identities. The Key is a created key. I want all records from the SQLCe table (dbRemote.Cache) but only inserted into certain columns of the Sql Server table(dbMaster.tableMaster). AFter the insert the dbRemote.Cache data will be removed or copied over to a backup table in the dbRemote so I will move all records each INSERT. The code is not finding the dbRemote.Cache table in the noted location below. I've tried several formats including a hard coded path to the db as "IN C:\path to db" like MS Access but no luck. Any ideas?
Specific Error is: SQLException was unhandled Invalid object name 'dbRemote.Cache'.
Dim strConn As String
strConn = "Data Source=MACENROE;Initial Catalog=dbMaster;Integrated Security=True"
Dim sqlCon As New SqlConnection(strConn)
Dim sqlCom As New SqlCommand()
If Not sqlce.State = ConnectionState.Open Then sqlce.Open()
sqlCom = New SqlCommand("INSERT INTO dbMaster.tableMaster SELECT RecordID, Col1, Col2, Col3, etc FROM dbRemote.Cache", sqlCon)
sqlCom.ExecuteNonQuery() <---Invalid object name 'dbRemote.Cache'.