sql serversqlite
I have a database with about 50 tables with use together about 1 GB space.
The SQLite database will be readonly.
This copying will be done once each month.
EDIT:
I see one possible solution using linked server via SQLite 3 ODBC Driver which results in rather simple t-sql scripts. I don't know about its performance.
I guess that other solution require more work, but obtain better performance.
Step by step:
select sysdate from dual@dblink. A firewall is in place between both databases. Source DB opens the connection, retrieve data and leave connection opened. After 15mins the firewall drop the connection. At minute 20 another user performs the same query. The database will reopen the connection. Apart from this I understand that you get the connection dropped and you have to way to re-establish it without drop and recreate the database. Metalink (formerly My Oracle Support) suggest to check if HS=OK is correctly set in the tnsnames.ora. Attention: the HS=OK argument needs to be outside of the CONNECT_DATA portion of the entry. Here an example:
MSSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = tcp)
(HOST = 192.168.36.18)
(PORT = 1522)
)
)
(CONNECT_DATA =
(SID = MSSQL)
)
(HS=OK)
)
Moreover I suggest you to check (and eventually post) logs on both sides. You can also increase the level of HS_FDS_TRACE_LEVEL parameter.
In addition MOS reports some issue with DG4ODBC and suggest to debug them (and fill a bug if necessary) using trace on the ODBC layer.
In addition to setting Local DTC Properties, you need to open the firewall on both machines to allow program Distributed Transaction Coordinator to go thru.
When testing T-SQL in Query Editor Window, make sure to turn on XACT_ABORT as suggested by Microsoft.
XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.
In my case, for the Local DTC Properties, I only need these:
Best Answer
I have successfully used SQL Server Integration Services (SSIS) to setup a job to "archive" data to SQLite databases. A quick way to build a SSIS job, is to do a Task->Export Data task. At the very end you will see a checkbox on the top left asking you if you want to save this as a SSIS job.
Make sure that you have installed the SQLite provider from http://sqlite.phxsoftware.com/
You can schedule this SSIS package once it is saved.