3

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.

  1. 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.

  • Personally id try selecting just the text data first. And once you get past that step id try selecting 1 image at a time. I suspect that it is the text fields causing your problem. Work through a process of elimination to find which field is causing the problem. It may be more than 1. – Sir Swears-a-lot Mar 6 '17 at 8:07
  • I would also reccomend copying the files to your pc, install the latest version of sqlite and drivers and install a newer version of SQL server on your pc too. Old sqlite, old drivers, old sql server is just going to be a headache. – Sir Swears-a-lot Mar 6 '17 at 8:19
  • Another advantage is that if you mess things up locally you dont don't screw up the originals. – Sir Swears-a-lot Mar 6 '17 at 8:22
  • Image data will be of Varbinary datatype in MS SQL refer this [post] (stackoverflow.com/questions/5613898/…) – MarmiK Mar 7 '17 at 14:18
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.

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.