Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16

    Unanswered: Sending data from one database to another

    DBMS = SQL server 2005
    Web programming language = ASP.NET

    Hi

    I have database 1 sat on server 1.

    I would like to move selected tuples from specific tables into database 2 sat on server 2.

    What do i need to research to try and find a method of doing this?

    Is the correct approach to move the selected data into a 'recordset' and if so how can i send this record set to database 2?

    many thanks
    Bil
    Last edited by billy_boi; 01-18-07 at 11:17.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if there are millions of records, I prefer to use bcp.exe for this. see "bcp utility" in BOL.

    if there are merely thousands, you could create a linked server on the destination and do this:

    insert into LocalTable
    select a,b,c from RemoteLinkedServer.SomeDatabase.dbo.RemoteTable
    where <criteria>


    EDIT: it's NOT a good idea to pull all the rows down to your client app and then send them to the destination. That puts unnecessary load on your network, because you have to fetch rows from server1, pull to client, then push them to server2. Better just to pull from server1 to server2 directly using a linked server call. or use bcp if the amount of data is huge. SSIS is available too but I never use it so can't speak to that possibility.

    Yet another possibility is to use the SqlBulkCopy class in .NET - since you are using asp.net you could do this. It's quite efficient if you pass it an IDataReader. Just pass it the result of SqlCommand.ExecuteReader() and it will move the rows quite rapidly.
    Last edited by jezemine; 01-18-07 at 13:06.

  3. #3
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    many thanks

    looking into the .NET option using the SQLBulkCopy

    found this link if anyone else is trying to do the same thing:
    http://davidhayden.com/blog/dave/arc...3/08/2877.aspx

    goin to try it out 2morrow and i'll let u know how i got on

    bil

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    the comments here might be useful as well, some are mine. *blush*

    http://weblogs.sqlteam.com/mladenp/a.../26/11368.aspx

  5. #5
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    Hi

    As a ASP.NET novice im still having problems.

    I decided to make it simpler and try to copy data from one table to another in the same database:

    http://msdn2.microsoft.com/en-us/lib...lbulkcopy.aspx

    Howevery i'm still getting lots of errors and im not sure if the set up is correct, i;ve edited the code from the site as follows:

    __________________________________________________ ______________
    Imports System.Data.SqlClient
    Module Module1
    Sub Main()
    Dim connectionString As String = GetConnectionString()

    ' Open a connection to the AdventureWorks database.
    Using sourceConnection As SqlConnection = _
    New SqlConnection(connectionString)
    sourceConnection.Open()

    ' Perform an initial count on the destination table.
    Dim commandRowCount As New SqlCommand( _
    "SELECT COUNT(*) FROM dbo.des;", _
    sourceConnection)
    Dim countStart As Long = _
    System.Convert.ToInt32(commandRowCount.ExecuteScal ar())
    Console.WriteLine("Starting row count = {0}", countStart)

    ' Get data from the source table as a SqlDataReader.
    Dim commandSourceData As SqlCommand = New SqlCommand( _
    "SELECT fname" & _
    "FROM declaration;", sourceConnection)
    Dim reader As SqlDataReader = commandSourceData.ExecuteReader

    ' Open the destination connection. In the real world you would
    ' not use SqlBulkCopy to move data from one table to the other
    ' in the same database. This is for demonstration purposes only.
    Using destinationConnection As SqlConnection = _
    New SqlConnection(connectionString)
    destinationConnection.Open()

    ' Set up the bulk copy object.
    ' The column positions in the source data reader
    ' match the column positions in the destination table,
    ' so there is no need to map columns.
    Using bulkCopy As SqlBulkCopy = _
    New SqlBulkCopy(destinationConnection)
    bulkCopy.DestinationTableName = _
    "dbo.des"

    Try
    ' Write from the source to the destination.
    bulkCopy.WriteToServer(reader)

    Catch ex As Exception
    Console.WriteLine(ex.Message)

    Finally
    ' Close the SqlDataReader. The SqlBulkCopy
    ' object is automatically closed at the end
    ' of the Using block.
    reader.Close()
    End Try
    End Using

    ' Perform a final count on the destination table
    ' to see how many rows were added.
    Dim countEnd As Long = _
    System.Convert.ToInt32(commandRowCount.ExecuteScal ar())
    Console.WriteLine("Ending row count = {0}", countEnd)
    Console.WriteLine("{0} rows were added.", countEnd - countStart)

    Console.WriteLine("Press Enter to finish.")
    Console.ReadLine()
    End Using
    End Using
    End Sub

    Private Function GetConnectionString() As String
    ' To avoid storing the sourceConnection string in your code,
    ' you can retrieve it from a configuration file.
    Return "Data Source=(local);" & _
    "Integrated Security=true;" & _
    "Initial Catalog=AdventureWorks;"
    End Function
    End Module
    __________________________________________________ _____________

    Screen shot: http://uk.geocities.com/dbillinghamuk/dbprofile/db.jpg

    any help would be greatly appreciated.

    many thanks

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    would you mind telling us what errors you are getting? I don't want to pick through your VB code and try to guess.

    EDIT: I can't open the screenshot your provided - my browser just hangs when I hit that link.
    Last edited by jezemine; 01-19-07 at 13:15.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Doesn't matter, that's not the way to do it.

    You should write a sproc
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Brett Kaiser
    Doesn't matter, that's not the way to do it.

    You should write a sproc
    I assume you mean to use a linked server call. If so then I disagree, unless the size of data is relatively small. That's not the way to go if there are 10's of millions of records.

    bulk insert will be much more efficient, and besides there's no way to extract the rows to be inserted from a sproc (unless you use bcp via xp_cmdshell from within your sproc, which I don't like, simply because then you have to enable xp_cmdshell).

  9. #9
    Join Date
    Dec 2006
    Posts
    7
    there is an easy way if u are working on linux
    then just write
    "cat dbname > dbname.sql"
    and then scp this file to other machine
    then on mysql prompt
    just create db first then write source dbname.sql

  10. #10
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    Hi

    the error i am getting is on the SQLconnection.

    It states 'Format of the initialization string does not conform to specification starting at index 0.'

    Basic setup:
    - Putting the data in a datatable
    - uploading the datatable to another database table
    - DB1 = declaration / table = fname
    - DB2 = des / table = fname


    Here are a couple of screen shots:
    http://uk.geocities.com/dbillinghamu...ile/dbprob.jpg
    http://uk.geocities.com/dbillinghamu...le/dbprob2.jpg



    PLus code:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace WindowsApplication1
    {
    public partial class Form1 : Form
    {
    public Form1()
    {
    InitializeComponent();
    }

    private void btnCreate_Click_Click(object sender, EventArgs e)
    {
    // Get data in a DataTable
    DataTable table = new DataTable();
    string testdatabase1ConnectionString = "...db1...";

    using (SqlConnection connection =
    new SqlConnection(testdatabase1ConnectionString))
    {
    using (SqlCommand command = connection.CreateCommand())
    {
    command.CommandText = "SELECT * FROM declaration";

    connection.Open();
    using (IDataReader dr = command.ExecuteReader
    (CommandBehavior.CloseConnection))
    {
    table.Load(dr);
    }
    }
    }
    // Upload DataTable to a Database Table
    string testdatabase2ConnectionString = "...db2...";

    using (SqlBulkCopy copy =
    new SqlBulkCopy(testdatabase2ConnectionString))
    {
    copy.DestinationTableName = "des";
    copy.WriteToServer(table);
    }
    }
    }
    }


    many thanks

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    It states 'Format of the initialization string does not conform to specification starting at index 0.'

    this means there is a problem with your connection string. what does it look like? Certainly "...db1..." isn't going to cut it.

    EDIT: I just looked at your screenshot and you ARE using "...db1..." as a connection string, which makes me wonder: have you ever worked with sql server before?

    There are example connection strings here: http://www.connectionstrings.com. On that page, you'll want to look at SQL Server 2005, and SqlConnection specifically.
    Last edited by jezemine; 01-22-07 at 11:02.

  12. #12
    Join Date
    Feb 2003
    Posts
    2

    Smile

    Since you're on SQL2005 ... you may want to try Service Broker (=SQL2005 to SQL2005 messagequeueing with possible payload)

  13. #13
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    hi

    No im not as familiar with MSSQL or .NET even, as i should be.
    Problem today is everything is taught through the GUI and students like myself are finding it hard to get to grips with certain areas of code specially when it comes to connecting to a database.

    OK, so connection strings, i do rememneber creating one previously in PHP + MYSQL.

    SO this would go in this section of the code, correct ???:

    // Get data in a DataTable

    DataTable table = new DataTable();
    string testdatabase1ConnectionString = "db1";

    using (SqlConnection connection = new SqlConnection(Data Source =*****;Initial Catalog=testdatabase1;User Id="";Password=""; ))
    {
    using (SqlCommand command = connection.CreateCommand())
    {


    many thanks

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    yes, except for you need to supply values for Data Source, User Id, and Password. And the whole thing needs to be a string.

    your most recent code would not even compile, let alone work properly.

    something like this:

    Code:
    using (SqlConnection connection = new SqlConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"))
    {
        // use it here...
    }

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    btw are you familiar with msdn? there you can find documentation and examples for all classes in the framework. for example:

    http://msdn2.microsoft.com/en-us/lib...qlcommand.aspx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •