Loading SQL Server from SQLite with image data

Question

I am trying to load a SQL Server database table from a SQLite database. I have been unable to transfer the data between the two databases since there are:

1. Difficulties in accessing the data in the SQLite database
2. Difficulties in accessing the individual columns within that database.

How do I perform this image data transfer, using T-SQL?

Attempts so far:

The source database is a linked server (SQLiteIDImagerThumbs) in SQL Server. This was created from the SQLite database. This linked server has one table, idThumbs.

The target database is a SQL Server database (idimagerthumbs). This DB has only one table, idThumbs defined with similar columns to SQLIDImagerThumbs.

So far I have:

1. Scripted a SELECT statement from [SQLiteIDImagerThumbs]...[idThumbs].

   SELECT * FROM [SQLiteIDImagerThumbs]...[idThumbs]

Running this SELECT statement results in the following error:

Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' for linked server 'SQLiteIDImagerThumbs' returned data that does not match expected data length for column '[SQLiteIDImagerThumbs]...[idThumbs].idThumbs.idThumb'. The (maximum) expected data length is 255, while the returned data length is 10627.

2. Manually creating a SELECT statement on SQLiteIDImagerThumbs.

   SELECT 
   GUID,
   ImageGUID, 
      ...other columns, 
   idThumb
   FROM [SQLiteIDImagerThumbs]...[idThumbs];
   

   Running this SELECT statement results in the following errors:

   Msg 207, Level 16, State 1, Line 1
     Invalid column name 'GUID'.
     Msg 207, Level 16, State 1, Line 2
     Invalid column name 'ImageGUID'.
     ... other column errors
     Msg 207, Level 16, State 1, Line 8
     Invalid column name 'idThumb'.
   

I have also conducted many web searches to try and find the answer to this. These have produced so much information that I am completely lost. Most of the information I have found concerns inserting a single file into an image colum; using BULK_INSERT, READTEXT, OPENROWSET(BULK) etc. I have not managed to find any information that exactly matches my problem.

Context

I am trying to do a substantial data recovery and conversion of data produced by an application which is no longer supported, and for which I do not have any source code. The application is a very small shell on what is substantially a database application. The permissions and users on the SQLite database have been set by the application. The database I am trying to convert is about 8GB in size. Accessing the data is a trivial step in a much larger data recovery and conversion exercise, but is proving intractable.

The schema (created from the SQLite DB using DB Browser) of the SQLite database is:

CREATE TABLE idThumbs (
  GUID varchar(50) PRIMARY KEY, 
  ImageGUID varchar(50), 
  ... other columns
  idThumb BLOB)

The schema of the SQL Server DB (created from a script available in the application) is:

CREATE TABLE 
  [dbo].[idThumbs](
  [GUID] [nvarchar](50) NOT NULL,
  [ImageGUID] [nvarchar](255) NULL,
  ... other columns
  [idThumb] [image] NULL,
PRIMARY KEY CLUSTERED 

Issues arising from comments/answers

Additional information

Using SQL Server to script Update/Insert/Delete Statements creates error:

-- [SQLiteIDImagerThumbs]...[idThumbs] contains no columns that can be inserted or the current user does not have permissions on that object.

Data checking

The following queries:

    SELECT <column_name> FROM IDTHumbs 
    SELECT <column_name>, LENGTH(<column_name>) 
    WHERE LENGTH(<column_name>) > <column_name_declared_length>

work correctly for all columns when called from DB Browser. There is no data longer than the declared_length. When called from SSMS via the linked server I get the errors from action 2 above.

Approach/Tools Used

Lazy schema validation does not appear on the properties of the linked server. I do not understand how installing developer on my PC (which I have) gives a capability that you say is only available under the Enterprise version.

All files are on my PC. I have the latest version of SQLite, DB Browser, and the SQLite ODBC driver. For a variety of reasons I am stuck with SQL Server 2005 Developer's edition. This is the database environment which the application used.

As part of the larger project, the first thing I did was to set up a development environment, with the tools necessary to re-initialise the databases. This removes the possibility of trashing the original files.

I would be quite happy to use something like excel. I can obtain the data in a CSV file from DB Browser. I do not know how to handle image data in a CSV file, as whatever delimiter is chosen, it is possible that it will appear as part of the data; and there is no way of escaping delimiters within the data.

Answer 1

The problem you have is caused by sqlite not enforcing its own field definitions. This means a field defined as varchar(50) might have more than 50 characters in it.

Run some select statements over your data to check field lengths. For example:

Select field1, length(field1)
Where length(field1) > 50

One possible way around it is to use lazy schema validation. Look at your linked server properties.

Lazy schema validaton is an enterprise only feature. If you dont have enterprise you could install developer on your PC to do the extraction.

Another option might be to edit your target table to have bigger fields and then use an openquery statement and substring each field to be less than target field length. Maybe even use varchar(max).

When i had this same problem, i got around it using excel, as excel isnt as fussy with field lengths. But im not sure how it would handle images, or the volume of data.

As i now have sql 2016 developer on my pc i would using that.