Transfer data between Server and local mdf

[CP60]

Because of a legacy program, and for now until I have the time to implement something else, I need to transfer data from a SQL Server database on a server to a local SQL Express 2014 database temporary table.


This was previously being done by importing data from a SQL Server DB to a JET 4/ACEDAO 2010 mdb, coded with in the application:

oConn.Connection = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=SomeDatabase.Mdb;"
oConn.Open

sql = "INSERT INTO " & _
        "[SomeTable] " & _
        "SELECT * FROM " & _
        "[ODBC;Driver=SQL Server; " & _
            "SERVER=MYSERVER\MYINSTANCE;DATABASE=MyDatabase;" & _
            ";Integrated Security=SSPI;]." & _
        "[LOH];"
oConn.Execute sql

Once the temporary user specific data is in "SomeDatabase.Mdb", which is in a Windows user profile AppData, it is processed further by a different program.

Now I want to upgrade "SomeDatabase.Mdb" to a local SQL Server Express database.

Is this possible?

 

[gmmastros]

If your two separate sql instances are on the same network, you should look in to using "linked servers".

If your instances are on separate networks, then you should use BCP.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

Well, if you are on the same network a local db isn't usefull at all. And just adding the central database as linked server to the local express db renders it almost useless.

You should tell more about the reasoning of the local data. Does it contain the same schema, just partial data from the central db? Does it have additional separate data? If the latter, do you need queries joining local and central data?

If it's there to pull data for offline access, as I think it is (travelling salesman with a notebook not always in the company network, not even via VPN) then a linked server is no solution, that's just a much more complicated way to connect to the central database via a local database connecting to it. The only advantage then would be a local db for each user for data you only need for that user. But then I'd also rather let an app have two connections for local and central data instead of chaining the two servers via linked server.

That setup also needs the client side to be in that LAN permanently, eg a docked off notebook has no access to central data not pulled into the local db anyway, so the linked server doesn't provide anything better than two connections, in my opinion.

As DBA you might make most of the SQL Server modules to work with data including replication, syncing, linked servers etc., also to move data, but I rather see an SQL Server as the "dumb" data store. Don't misunderstand me, I'm fine with having a server able to execute stored procedures and other stuff, but an n tier architecture have split application business logic partly in the database server and partly in the application itself is hardly a really modular application architecture.

You need more than pulling data offline and later pushing it back, the data has to be merged with intelligence, most probalbly even in both directions (not taking into account initial empty states). Something I'd not like to implement in T-SQL or even in an SSIS package.

Bye, Olaf.

 

[CP60]

Thank you.
I will check into these things and try to figure out what is best.