September 11, 2015 at 7:33 am
Dear All,
Having set up a linked server as a SQLite DB, I am able to select and sometimes insert to it, with T-SQL.
SELECT * from openquery(sw , 'select * from list')
or
INSERT INTO OPENQUERY(sw, 'select start, end, threshold from type')
SELECT '08:00', '15:00', 999
are fine. But if I try a simple example to insert into what is a TEXT field in SQLLite, like this:
INSERT OPENQUERY(sw, 'select character from shifttype')
VALUES ('Y')
I see an error:
OLE DB provider "MSDASQL" for linked server "sw" returned message "Query-based insertion or updating of BLOB values is not supported.".
Msg 7343, Level 16, State 2, Line 294
The OLE DB provider "MSDASQL" for linked server "sw" could not INSERT INTO table "[MSDASQL]".
For the Insert example that works, the columns are NOT TEXT (they are datetime and int). So it seems I can INSERT fine, unless it's into a TEXT. After some googling, I find myself stuck, so wondered if any of you have an experience of this?
Regards,
Greg.
September 15, 2015 at 7:54 am
I've given up with openquery, I think there might be a problem with the ODBC driver for SQLite.
Am working around this using command line export of preformatted SQL INSERT statements (XP_cmdshell and echo to file) and the SQLite3 command line importer. All working nicely.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply