Sql-server – Loading SQL Server from SQLite with image data

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.