How to Link an SQL Server 2008 Server to an SQLite Server?

Question

I want to "Link" an SQLite server with an SQL Server 2008 server. E.g. using the sp_addlinkedserver command. How do I do this?

I have searched far and wide, but found no solution for this problem. The closest attempt I have found is here:

http://www.sqlservercentral.com/Forums/Topic866972-149-1.aspx

--#################################################################################################
--Linked server Syntax for SQLite 
--using OLE provider C:\Program Files\Cherry City Software\SQLiteProvider\SQLitePV.dll
--from http://cherrycitysoftware.com/ccs/Download/Download.aspx
--#################################################################################################
DECLARE @server     sysname,
        @srvproduct nvarchar(256),
        @provider   nvarchar(256),
        @datasrc    nvarchar(100),
        @location   nvarchar(100),
        @provstr    nvarchar(100),
        @catalog    sysname,
        @sql        varchar(1000)
--add an SQLite Database as a linked server
SET @server = N'mySQLite'
SET @srvproduct = N'SQLite Provider'
SET @provider = N'OleSQLite.SQLiteSource.1'
SET @datasrc = N'C:\Data\LowellSSC.db3'
set @provstr    = ''
EXEC sp_addlinkedserver  @server,@srvproduct,@provider,@datasrc,NULL,@provstr
exec sp_addlinkedsrvlogin @rmtsrvname='mySQLite', 
@useself = N'false',
@locallogin = NULL,
@rmtuser = N'Admin',
@rmtpassword = NULL

--list all the tables and their names
EXEC sp_tables_ex 'mySQLite'
--above fails with this error:
--Msg 7302, Level 16, State 1, Procedure sp_tables_ex, Line 41
--Cannot create an instance of OLE DB provider "OleSQLite.SQLiteSource.1" for linked server "mySQLite".
GO
EXEC dbo.sp_DropServer 'mySQLite', 'DropLogins'

Answer 1

You may have better luck with an ODBC driver. There are a few on Google, one of which being http://www.patthoyts.tk/sqlite3odbc.html.

Theoretically, if you can install the ODBC driver, you can create a system DSN. If you can create a system DSN, you can add it as a linked server.

Of course, both OLEDB and ODBC tend to fall over if the OLEDB/ODBC drivers are 32-bit and the SQL Server is 64-bit; I don't believe you can instantiate a 32-bit OLEDB/ODBC driver in 64-bit SQL (I think we had similar trouble wih Excel)

HTH

J.

Answer 2

Do you have OleSQLite.SQLiteSource.1 in your list of Linked Server providers? If not, you should make sure you've installed those drivers on your machine (and restarted the SQL Server service at least to make sure it picks them up).