Querying ODBC databases via SQL Server

Question

I want to make selects, updates, inserts, etc in a database from a linked server in MS SQL Server.

I connected the linked server [SQLITE3] to a sqlite3 database with SQLite3 ODBC Driver (www.ch-werner.de/sqliteodbc).

I accomplished to run selects with:

SELECT * FROM OPENQUERY([SQLITE3],'select * from log');

This returned the desired results.

However, doing a update/delete/insert like this returns an error, e.g.:

DELETE OPENQUERY([SQLITE3],'select * from log');

Msg 7330, Level 16, State 2, Line 4
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "SQLITE3".

How may I do writting operations?
May I use selects with a syntax like the following?

SELECT * FROM [SQLITE3].log -- gives error: non-existing object

Answer 1

The SQLite3 ODBC Driver is not a Microsoft product. You need to contact the developers for questions such as that. The following however might help.

• Updating an SQLite database via an ODBC linked table in Access - Stack Overflow
• ms access - Preventing IIS from keeping a file lock on a SQLite database file - Stack Overflow
• sqlite - SQLite3 on ODBC Driver: string connection error - Stack Overflow

Answer 2

You could try

DELETE SQLLITE3...log

Note that you need three dots, SQLLITE3 is the server component.

Using linked servers can often be a bumpy ride. Error message are often cryptic, as there are several layers. The error message you had should be acompanied with an informational message that comes the from OLE DB provider. And if you are lucky, it origins from the ODBC driver. But it can still be cryptic.

Answer 3

Hi @JDias ,

Welcome to Microsoft Q&A!

You could try to define all column names in the log table, add where condition or use direct delete from statement as below examples:

DELETE FROM OPENQUERY([SQLITE3],'select * from log');  
  
DELETE FROM OPENQUERY([SQLITE3],'select COL1,COL2 from log');  
  
DELETE FROM OPENQUERY([SQLITE3],'select COL1,COL2 from log where id=1');  

DELETE OPENQUERY([SQLITE3], 'select * from log') where id=1  
  
DELETE FROM [SQLITE3].[DATABASENAME].[SCHEMA].[log]  

You could also try with below method mentioned in this forum which may be helpful.

1. In the ODBC Data Source Administrator panel on the server, configure your linked server from System DSN.
2. Under the Performance tab, uncheck the box 'Enable pre-fetch of data for queries'. OK, OK. Delete and re-create your linked server in SQL Management Studio.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.