ODBC Driver for SQL Server - SQlite3 interfacing with SQL Server

JDias 71 Reputation points
2021-03-10T15:47:14.87+00:00

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 @Testta = 'SQLITE3' , @provider= 'MSDASQL.1' , @provstr= 'DRIVER={SQLite3 ODBC Driver};DATABASE=C:\Users\me.me\Desktop\My Directory\mydb.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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.8K Reputation points MVP
    2021-03-10T22:47:51.663+00:00

    So what are the data types in that table log? If you take a simple table with just some int columns, what happens?

    Linked servers can be quite a battle. SQL Server talks to the OLE DB provider, and if the OLE DB provider does not support the operations SQL Server needs you are out of luck. Here you have an OLE DB provider plus an ODBC driver to make it even more complicated.

    1 person found this answer helpful.
    0 comments
  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-03-11T07:30:03.37+00:00

    Hi @JDias ,

    > The OLE DB provider "MSDASQL.1" for linked server "SQLITE3" returned an invalid column definition for table "log".

    Could you share us log table structure and some sample data for log table?

    Did you try below;

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

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

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    1 person found this answer helpful.
    0 comments
  3. Erland Sommarskog 101.8K Reputation points MVP
    2021-03-11T22:15:53.753+00:00

    I did some googling and found https://www.mssqltips.com/sqlservertip/3087/creating-a-sql-server-linked-server-to-sqlite-to-import-data/ which seems to be using the same driver as you. They only have OPENQUERY examples. There is also a list to SQLite data types and INT in SQLLite is like a variable int, but should maybe present itself as a signed 8-bit value, that is bigint. But maybe the driver says that it is unsigned, and that does not fit with SQL Server.

    I also found https://www.devart.com/odbc/sqlite/docs/microsoft_sql_server_manager_s.htm and they have an example with four-part notation. Note, though, that Devart is a different vendor, and this is not the driver you are using.

    In any case, don't expect a smooth ride.

    1 person found this answer helpful.
    0 comments
  4. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-03-15T08:17:20.487+00:00

    Hi @JDias ,

    The way it deals with data types is different between SQL server and SQLite. For text data type;

    In SQLite, all strings are Unicode, and it doesn't care about widths on TEXT columns.

    But in SQL server, text data type is for Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647).

    Refer to MS document ntext, text, and image (Transact-SQL) and the blog compare data type between SQL server and SQLite.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    1 person found this answer helpful.
    0 comments
  5. JDias 71 Reputation points
    2021-03-11T09:50:34.53+00:00

    @CathyJi-MSFT @Erland Sommarskog

    1. The table log is simply like this:

    CREATE TABLE log (s TEXT);

    Is the type TEXT the problem?

    1. I tried without success:

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

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

    1. With table t:

    CREATE TABLE t (i INT);

    I managed to execute:

    UPDATE OPENQUERY([SQLITE3],'select i from t') SET i=5

    but

    SELECT * FROM [SQLITE3]...t

    gave an error:

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

    Any hint?

    0 comments