none
Transfer data between servers from client RRS feed

  • Question

  • VB.Net, ADO.Net, .Net 2.0, VS2005

    I need to replicate the ability of MS Access to link to multiple servers. I have a client side SQL Server Express 2008 DB that supports a subset of SQL Server 2000 data. The Express DB is on a client that is a Tablet PC that is disconected and taken into the field.

    With Access MDB on the client I could add links to the SQL Server 2000 to extract the data in one SQL command. In ADO.Net I need two seperate connections, one for each server, so I can not longer reference both servers in the same SQL statement.

    While connected how to I 'refresh' my client side data using ADO.Net?
    Wednesday, December 23, 2009 6:31 PM

Answers

  • If I understand you correctly you want to be able to keep client applications/databases in sync with a remote SQL Server database. If I were you I would focus on the overall problem, not the implementation used by an outdated technology--attempting to reproduce it on newer systems.

    Visual Studio has implemented a particularly elegant way to build a disconnected database (that uses SQL Server Compact edition on the clients) that can (with very little code) keep a client in sync with a host DBMS like SQL Server. While this approach can be implemented with virtually any host DBMS, it's far easier to do with SQL Server 2008 (even Express) as the extra overhead needed to coordinate the changes to the tables is built into the infrastructure. This is called "Local Data Cache". I've written about it any number of times (but it's more recent than my latest books) so look for articles on my blog and in SQL Server magazine (and elsewhere).

    I'm convinced that this will help get you off of Access/JET and on to a more robust and more secure disconnected DBMS architecture.

    hth
    William (Bill) Vaughn -- Mentor, Author, Dad and MVP Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) http://betav.com http://betav.com/blog/billva
    Sunday, February 28, 2010 9:28 PM
    Moderator

All replies

  • ADO.Net is a disconnected Env. There are chances when u fetch the data and while working on it, some rows were already outdated or deleted. This will cause concurrency violations when you write back to the DB. This situation can well handled in CuncurrencyManager object.

    In your case, you just need to refresh the content of DataTable/DataSet? . If yes, you need to call Adapter.Fill(dsObject). This will execute initial Select Query which you are attached with DataAdapter.

    You need to fill it again to update data  in DataSet/DataTable


    Thanks Mike --------Please mark as answer if it is useful----------
    Friday, January 22, 2010 6:47 AM
  • I have no intention of using the DB on the client to update the host DB using data objects.

    That is the design is to snapshot the host DB to the client tablet PC, take the tablet into the field, updates in the field are flagged in the DB, when the tablet is connected to the LAN a program runs that discovers the add/change/deletes via the flags (actually a transaction table) and it updates the host DB.

    As far as I can tell I can't use ADO.Net and SQL Server to do what Access does - use SQL queries that accesses data in two servers.

    I'm leaning towards just doing a backup on the host, copying the backup file to the client, and restoring the data into an SQL Express instance.
    Thursday, January 28, 2010 4:24 PM
  • If you don't use ADO.net, then how will able to automate this process?. As you said, it should detect automatically!.  You need to have a program running in your tablet PC which looks for successful connection to the Main DBServer. Once a successful connection found, using DataSet you can merge the changes to the Actual DB. this much simpler in Ado.net rather than going to backup and restore.
    Thanks Mike --------Please mark as answer if it is useful----------
    Friday, February 5, 2010 11:39 AM
  • Sorry I took so long to get back to you...

    In Access I can do this:

        CurrentDb.Execute "DELETE * FROM tAC_Application"
        SQL = "INSERT INTO tAC_Application SELECT AC_Application.* FROM t_Records_Needed2 INNER JOIN AC_Application ON CInt(t_Records_Needed2.PK) = AC_Application.Facility_ID"
        SQL = SQL & " WHERE t_Records_Needed2.Tablename='Facility'"
        CurrentDb.Execute SQL
    

    tAC_application table to a local Access MDB table on th elocal PC. AC_Application is a SQL Server table that is in a LAN based server. The SQL uses another local table to determine which subset of records is needed from the SQL Server. This can be accomplished in Access since the Jet Engine can host both local tables and links to other DB tables. Both tables can be used in a single SQL Statement.

    I don't think the same thing is possible under ADO.Net since each connection can only connect to one server at a time and there isn't a way I know for a single SQL statement to span multiple connections.

    In addition to the above I also need to make 100% copies of some of the tables.

    I'm guessing that to use ADO.Net I'd have to make local datasets filled from the LAN and then somehow connnect to the local DB to move the records into the local table. I have no idea how to take a filled dataset and insert the records into another DB. In my case the table defintions in each DB will be identical but the SQL Server editions may be different. Both would be SQL Server DBs at least.

    My guess is things will go much faster if I just make a backup of the LAN SQL Server data and restore it into the PC base SQL Server. We can wait until both SQL Servers are the same which should make this possible. I'll get more records than I need but I'd guess the bulk operation will go pretty quickly.
    Friday, February 26, 2010 9:25 PM
  • If I understand you correctly you want to be able to keep client applications/databases in sync with a remote SQL Server database. If I were you I would focus on the overall problem, not the implementation used by an outdated technology--attempting to reproduce it on newer systems.

    Visual Studio has implemented a particularly elegant way to build a disconnected database (that uses SQL Server Compact edition on the clients) that can (with very little code) keep a client in sync with a host DBMS like SQL Server. While this approach can be implemented with virtually any host DBMS, it's far easier to do with SQL Server 2008 (even Express) as the extra overhead needed to coordinate the changes to the tables is built into the infrastructure. This is called "Local Data Cache". I've written about it any number of times (but it's more recent than my latest books) so look for articles on my blog and in SQL Server magazine (and elsewhere).

    I'm convinced that this will help get you off of Access/JET and on to a more robust and more secure disconnected DBMS architecture.

    hth
    William (Bill) Vaughn -- Mentor, Author, Dad and MVP Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) http://betav.com http://betav.com/blog/billva
    Sunday, February 28, 2010 9:28 PM
    Moderator
  • I'll look into this when we update the SQL Server to 2008 R2 (or whatever the next version to be released is...)

    What is the next version and when is it expected?
    Tuesday, March 2, 2010 5:00 PM
  • 2008 R2 is the next version and I could tell you the ship date, but then I've have to kill you... ;)
    Giving out a ship date that hasn't been published is like predicting the weather.

    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, March 2, 2010 9:13 PM
    Moderator