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
OPENROWSETinstead?
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.
