SQLite Forum

SQLite3 interfacing with MS SQL Server
Login

SQLite3 interfacing with MS SQL Server

(1) By anonymous on 2021-03-09 12:01:55 [link] [source]

I have the sqlite3 installed in my Windows 10 64-bits. In order to have communication between sqlite3 and other Windows apps, I installed the SQLite3 ODBC Drivers (from www.ch-werner.de/sqliteodbc).

Until now I have accomplished to connect the sqlite3 database to Excel and to SQL Server.

However, in the SQL Server, some errors arise. To register the sqlite3 server I run:

EXEC sp_addlinkedserver @server= 'SQLITE3' , @provider= 'MSDASQL.1' , @provstr= 'DRIVER={SQLite3 ODBC Driver};DATABASE=C:Usersme.meDesktopMy Directorymydb.db;Persist Security Info=True;StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=100000;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;Initial Catalog=log;'
;

EXEC sp_testlinkedserver [SQLITE3]; -- completed successfully!

Now, the following runs ok:

SELECT s FROM OPENQUERY([SQLITE3],'select s from log') AS t;

But, I can't make an update:

UPDATE OPENQUERY([SQLITE3],'select s from log') SET s='hello'

OLE DB provider "MSDASQL.1" for linked server "SQLITE3" returned message "Este fornecedor não suporta acessores nulos.". (This provider doesn't support null accessors...) OLE DB provider "MSDASQL.1" for linked server "SQLITE3" returned message "Este fornecedor não suporta acessores nulos.". Msg 7330, Level 16, State 2, Line 27 Cannot fetch a row from OLE DB provider "MSDASQL.1" for linked server "SQLITE3".

Also, the following doesn't run:

SELECT s FROM [SQLITE3]...[log] AS t

Msg 7318, Level 16, State 1, Line 29 The OLE DB provider "MSDASQL.1" for linked server "SQLITE3" returned an invalid column definition for table "log".

Could anyone help me on these two issues?

(2) By Simon Slavin (slavin) on 2021-03-10 13:08:33 in reply to 1 [link] [source]

These errors come from SQL Server, or from the ODBC you're using between it and SQLite. SQLite doesn't have 'linked servers'. In fact it doesn't have servers at all. SQLite doesn't have accessors. SQLite has no form where you can combine these two things

select s from log SET s='hello'

in one statement. I'm forced to tell you to ask on a forum about the ODBC or SQL Server. Sorry.

(3) By anonymous on 2021-03-10 15:37:43 in reply to 2 [link] [source]

Ok, I'll follow your suggestion of asking elsewhere.

But, I am not combining select with set. In fact, I am trying to do an update:

UPDATE OPENQUERY([SQLITE3],'select s from log') SET s='hello'

equals

UPDATE log SET s='hello'

in SQLite3.... It's a tweak of SQL Server....

(4) By Larry Brasfield (larrybr) on 2021-03-10 16:56:01 in reply to 3 [link] [source]

As I understand your inquiry, now that you have clarified it, your post is not really off-topic. (It just looks that way. ;-)

If you study the UPDATE SYNTAX, you will see exactly how to do what you wish to accomplish in SQLite.

(5) By Simon Slavin (slavin) on 2021-03-11 14:17:00 in reply to 3 [link] [source]

If you use the

UPDATE log SET s='hello'

form, does it work properly ?

(6) By anonymous on 2021-03-11 16:37:58 in reply to 5 [source]

Yes, but only in SQLite3 app (sqlite3)!

In MS SQL Server with SQLite3 as linked server, no!

The problem is not the SQLite3 syntax!!!!