3

I am attempting to query SQLite to copy data into corresponding tables in SQL Server. This is the first stage of an ETL process I'm putting together.

Windows 10 Pro, SQL Server 2017 Developer Edition, SQLite 3.30.1 (installed via Chocolatey)

I have created a 64-bit system DSN for the SQLite database, created a Linked Server named NJ which points to it, and I can successfully query most tables, both via OPENQUERY and 4-part naming (after setting LevelZeroOnly for the MSADASQL provider). One table consistently throws out an error.

The table definition in SQLite:

CREATE TABLE LogMemo (lParent ,lLogId integer, lText default "");

Querying from within SQLite works.

sqlite> select lparent,llogid,lText from [LogMemo] order by lparent desc limit 4;
GCZZ2Q|834111942|Found it
GCZZ2Q|834111838|Tftc!
GCZZ2Q|833813811|On a quick girls getaway but first let me grab a cache. We pulled over by GZ, I didn't look for long before making the find. I signed the log and replaced the cache as found. TFTC
GCZZ2Q|833807936|Crossed the Delaware Bay on the  Cape May- Lewes Ferry (the New Jersey) with Lambmo, dukemom1, and  TBurket.  We had a wonderful trip,  found 19 new and interesting caches, and introduced TBurket to this great adventure. 
Ferry nice view was the first for the day, T's first find, and first NJ cache for Lambmo and dukemom.  Yes, it is a nice view of the ferry.

Querying this table via the Linked Server returns the following error:

Msg 7347, Level 16, State 1, Line 15 OLE DB provider 'MSDASQL' for linked server 'nj' returned data that does not match expected data length for column '[nj]...[logmemo].lText'. The (maximum) expected data length is 510, while the returned data length is 2582.

Thinking it was a problem with the long text field, I tried to give some hints about how much data should be expected coming back. I have tried the following queries:

select top 4  lparent,llogid,cast(ltext as nvarchar(4000)) as ltext from nj...logmemo order by lparent desc;
select top 4  lparent,llogid,substring(ltext,1,4000) as ltext from nj...logmemo order by lparent desc;
select top 4  lparent,llogid,substring(ltext,1,20) as ltext from nj...logmemo order by lparent desc;
select top 4  lparent,llogid,substring(ltext,1,200) as ltext from nj...logmemo order by lparent desc;
select top 4  lparent,llogid,ltext from nj...logmemo order by lparent desc;

All result in the same error. So I tried using OPENQUERY instead:

SELECT top 4 * FROM OPENQUERY([NJ], 'select lparent,llogid,cast(ltext as varchar(20)) as ltext from [LogMemo]    order by lparent desc limit 4')
SELECT top 4 * FROM OPENQUERY([NJ], 'select lparent,llogid,cast(ltext as varchar(4000)) as ltext from [LogMemo]  order by lparent desc limit 4')
SELECT top 4 * FROM OPENQUERY([NJ], 'select lparent,llogid,substr(ltext,1,8000) as lText from [LogMemo] order by lparent desc limit 4')
SELECT top 4 * FROM OPENQUERY([NJ], 'select lparent,llogid,substr(ltext,1,200) as lText from [LogMemo]  order by lparent desc limit 4')

The first three of these four queries return the first 3 of the expected 4 results, then the same error is thrown, with the exception that the reported returned data length is 728, not 2582. Note that the length of the long text associated with the last record in the original result set is 362 characters, which is 724 bytes (if we assume nvarchar).

The last query doesn't throw an error, but I only get the first 200 characters of the value in lText.

So, the question becomes...how can I extract the full text from this field in SQLite so I can insert it into my SQL Server table?

  • Is there a limit to the size of data that can be returned for one field via this method/driver?
  • Is there another setting I'm missing somewhere, or an extra parameter for OPENQUERY?
  • Should I be looking at OPENROWSET instead?

I'm close to abandoning this angle entirely and just dumping the table data to CSV from SQLite and bulk-importing it into SQL Server.

Edit in response to one comment:

SELECT LEN(ltext) FROM nj...logmemo ORDER BY LEN(ltext) DESC;

Results in an error:

Msg 7347, Level 16, State 1, Line 24 OLE DB provider 'MSDASQL' for linked server 'nj' returned data that does not match expected data length for column '[nj]...[logmemo].lText'. The (maximum) expected data length is 510, while the returned data length is 2582.

Doing similar with OPENQUERY:

select * from OPENQUERY([NJ], 'select length(ltext) as lText from [LogMemo] order by length(ltext)')

Msg 7356, Level 16, State 1, Line 26 The OLE DB provider "MSDASQL" for linked server "NJ" supplied inconsistent metadata for a column. The column "lText" (compile-time ordinal 1) of object "select length(ltext) as lText from [LogMemo] order by length(ltext)" was reported to have a "DBTYPE" of 130 at compile time and 3 at run time.

4
  • @cl. No, the source SQLite table cannot be altered.
    – alroc
    Jan 13 '20 at 22:28
  • When limiting to the first for (based on the specified ordering) I get 3997, 3996, 3995 and 3993
    – alroc
    Jan 15 '20 at 2:10
  • I suspect it’s a driver limitation or DSN configuration, not SQL Server. I can only get this to work if I use substr() to return the first 200 o4 so characters.
    – alroc
    Jan 15 '20 at 11:13
  • 1
    @alroc, I suspect you're right about a driver limitation/bug. A workaround for a linked server solution might be a 64-bit OLE DB provider for SQLite but I couldn't fine one with an internet search.
    – Dan Guzman
    Jan 26 '20 at 16:29
0

Have you attempted to insert the data into a temp table from your OPENQUERY, then from the temp table into your SQLServer table? I haven't worked in SQLite, but my company uses multiple linked servers pointed at old 1-off systems and this is the only way I can (know of) insert data into SQLServer from a couple of them.

1
  • This is happening before I even attempt an insert. Just a plain SELECT across the linked server.
    – alroc
    Feb 3 '20 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.