SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to link SQL 2005 to a SQLite server


How to link SQL 2005 to a SQLite server

Author
Message
TKE402
TKE402
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 34
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
homebrew01
homebrew01
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51729 Visits: 9315
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
tke402-837787
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 15
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
Lowell
SSC Guru
SSC Guru (308K reputation)SSC Guru (308K reputation)SSC Guru (308K reputation)SSC Guru (308K reputation)SSC Guru (308K reputation)SSC Guru (308K reputation)SSC Guru (308K reputation)SSC Guru (308K reputation)

Group: General Forum Members
Points: 308123 Visits: 42385
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 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search