How to link SQL 2005 to a SQLite server

Question

How to link SQL 2005 to a SQLite server

TKE402

Old Hand

Points: 344
More actions
February 17, 2010 at 6:28 am

#138933

Hi all,
I am trying to link a SQL2005 server to a Sqlite server. I found an ODBC driver which I downloaded to the SQL2005 server. I created a SQLlite 3 system DSN to the server and DB. My question is how in the gui (or command) do I link the SQL2005 server to this SQLite server? In the gui I choose new linked server. Then "Other data source" but I do not see a Sqlite option in there. Am I missing a step?
Thanks,
TKE402

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

What are you trying to accomplish ?

I recently worked on a project converting a SQlite application to SQL 2005. I didn't try connecting the 2 though. I used SQL Maestro to export the SQLlite data to .csv or sql insert statements, then loaded into SQL 2005.

tke402-837787 SSC Enthusiast Points: 176 More actions · Answer 2

Thanks for the reply. To be honest I don't have much detail on this. It's a request from a SQL programmer. She does a lot of enterprise reporting for the company using our main SQL 2005 dbs. She needs the SQL server to be linked so that she can run her custom sps that need to query the SQLite db. The SQLite db houses the IT dept ticketing system.

Thanks,

TKE402

Lowell SSC Guru Points: 323518 More actions · Answer 3

TKE402 did you get a linked server to work yet? I've played with SQLite a little bit now; I've used this .NET provider for SQLite[/url] in VS 2005/2008 with no problem, other than the command not allowing complex CHECK constraints.

my Google-Fu is strong, but I have not found a single working example of a linked server to SQLite;

I've also used this OLEDB provider, which i can use with vb6,.net, and the import/export wizard in SSMS to get data.

logically, if i can use the oledb SQLite Provider witht he import/export wizard, i should be able to create a linked server with it as well, but I'm still getting an 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".

AFAIK, Msg 7302 is raised when there is a configuration issue with the linked server..bad provider string or whatever.

This is what I've tried so far, but no luck; have you gotten it to work?

--#################################################################################################

--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'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!