Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Copy rows from SQLCe to SQl Server .net

Posted on 2010-08-29
11
Medium Priority
?
684 Views
Last Modified: 2013-12-05
Hello,

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)
        sqlCon.Open()
        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'.

Thanks
0
Comment
Question by:zipnotic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 

Author Comment

by:zipnotic
ID: 33557813
For development both dbs are on the same machine but the SQLCE will be on a sometimes connected laptop and the Master will be on a server.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 33584516
You could not use combined command. You should separate SELECT from dbRemote and INSERT into dbMaster.
Use command parameters on INSERT command and fill it by another SELECT command on different connection to dbRemote database.
0
 

Author Comment

by:zipnotic
ID: 33589321
Thanks for your response.  A clarification:
Will the new CMD execute based on a SQLCE dataadapter or do I somehow need to transfer/copy the data to a SQLDataAdapter Before creating the SQL Insert command?

Dim cmd As SqlCeCommand = Nothing
Dim adp As SqlCeDataAdapter = Nothing



Try
    adpCE = New SqlCeDataAdapter()
   
    Dim conn As New SqlCeConnection("Data Source = MyCEDatabase.sdf")
    Dim ConnEX As New SqlConnection("Data Source = MySQL2008Expressdatabase.ldf")

    ' Create the SelectCommand
    '
    cmd = conn.CreateCommand()
    cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees"
    adp.SelectCommand = cmd

    ' Populate the dataset with the results from the SELECT statement
    '
    Dim ds As New DataSet()
    adp.Fill(ds)

    ' Create the InsertCommand
    '
    cmd = connEX.CreateCommand()
    cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)"

    Dim p As SqlParameter = Nothing

    p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
    p.SourceVersion = DataRowVersion.Original

    p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
    p.SourceVersion = DataRowVersion.Original

    adp.InsertCommand = cmd
    '
    adp.Update(ds.Tables(0))
CATCH
END TRY
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 12

Expert Comment

by:patrikt
ID: 33590731
You have to change connection between two steps. It is not possible with one adapter.
Simpliest solution is to copy data to new dataset and save it with the new adapter.
0
 

Author Comment

by:zipnotic
ID: 33591492
I'm trying to install SP1 and SQL 2008 at the moment so I can't test this now but would this work then:
Thanks for your help.


Dim cmd As SqlCeCommand = Nothing
Dim adp As SqlCeDataAdapter = Nothing



Try
   
   
    Dim conn As New SqlCeConnection("Data Source = MyCEDatabase.sdf")
    Dim ConnEX As New SqlConnection("Data Source = MySQL2008Expressdatabase.ldf")

    ' Create the SelectCommand
    '
    cmd = conn.CreateCommand()
    cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees"
    adp.SelectCommand = cmd

    ' Populate the dataset with the results from the SELECT statement
    '
    Dim ds As New DataSet()
    adp.Fill(ds)
    Dim dsCopy as dataset = ds.Copy()


    Dim adpSQL as New SQLDataAdapter()
    adpSQL.Fill(dsCopy)
    'Can I go straight from original ds like:  adpSQL.Fill(ds) ?????


    ' Create the InsertCommand
    '
    cmd = connEX.CreateCommand()
    cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)"

    Dim p As SqlParameter = Nothing

    p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
    p.SourceVersion = DataRowVersion.Original

    p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
    p.SourceVersion = DataRowVersion.Original

    adpSQL.InsertCommand = cmd
    '
    adpSQL.Update(ds.Tables(0))
CATCH
END TRY
0
 
LVL 12

Expert Comment

by:patrikt
ID: 33594316
.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 33594325
See // comments in code.
Dim conn As New SqlCeConnection("Data Source = MyCEDatabase.sdf")
    Dim ConnEX As New SqlConnection("Data Source = MySQL2008Expressdatabase.ldf")

    ' Create the SelectCommand
    '
    cmd = conn.CreateCommand()
    cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees"
    adp.SelectCommand = cmd

    ' Populate the dataset with the results from the SELECT statement
    '
    Dim ds As New DataSet()
    adp.Fill(ds)
	//No need to copy.
    //Dim dsCopy as dataset = ds.Copy()


    Dim adpSQL as New SQLDataAdapter()
	//No need to Fill. You already have full dataset.
    //adpSQL.Fill(dsCopy)
    'Can I go straight from original ds like:  adpSQL.Fill(ds) ?????


    ' Create the InsertCommand
    '
    cmd = connEX.CreateCommand()
	//If Emploee ID has to be saved too add it there.
    cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)"

    Dim p As SqlParameter = Nothing

    p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
    p.SourceVersion = DataRowVersion.Original

    p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
    p.SourceVersion = DataRowVersion.Original

    adpSQL.InsertCommand = cmd
    '
	//You have to set RowState for all rows to Added. In other case no Insert command will be issued by DataAdapter.
    adpSQL.Update(ds.Tables(0))

Open in new window

0
 

Author Comment

by:zipnotic
ID: 33594497
OK, Think I got it.  I only need 1 dataset filled by the SELECT against the CE Database but then reissue/create the new CMD for the SQL EXpress database using parameters.  

Is the RowState automatically set for all rows to be added or is there another command for that?  (Installing SQL Express 2008 has been quite the project....)

Dim conn As New SqlCeConnection("Data Source = MyCEDatabase.sdf")
    Dim ConnEX As New SqlConnection("Data Source = MySQL2008Expressdatabase.ldf")

    ' Create the SelectCommand
    '
    cmd = conn.CreateCommand()
    cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees"
    adp.SelectCommand = cmd

    ' Populate the dataset with the results from the SELECT statement
    '
    Dim ds As New DataSet()
    adp.Fill(ds)

    ' Create the InsertCommand
    '
    cmd = connEX.CreateCommand()
      //If Emploee ID has to be saved too add it there.
    cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)"

    Dim p As SqlParameter = Nothing

    p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
    p.SourceVersion = DataRowVersion.Original

    p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
    p.SourceVersion = DataRowVersion.Original

    adp.InsertCommand = cmd
    '
      //You have to set RowState for all rows to Added. In other case no Insert command will be issued by DataAdapter.
    adp.Update(ds.Tables(0))
;
0
 
LVL 12

Accepted Solution

by:
patrikt earned 2000 total points
ID: 33594863
RowState is set to Unmodified after Fill operation. It will be automaticaly set in case of change in Table, but you do no change to data so you have to set it manualy.
Before Update operation you should iterate all rows and set it Added (will cause InsertCommand) or Modified (will cause UpdateCommand). It is needed to force datatable to thing that rows are new/changed.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question