Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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'
share|improve this question

2 Answers 2

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.