Is it possible to open a SQLite database from within Microsoft SQL Server Management Studio

comparisonsqlsql-management-studiosql-server-2008sqlite

Is there a way to open a .db file (SQLite database file) from within Microsoft SQL Server Management Studio?

Right now we have a process that will grab the data from a Microsoft SQL Server database and put it into a SQLite database file that will be used by an application later on.

Is there a way to open the SQLite database file so that it can be compared to the data inside the SQL Server database… using only one SQL query?

Is there a plug-in for microsoft sql management studio? Or maybe there is another way to do this same task using only one query.

Right now we have to write two scripts… one for sql server database and one for sqlite database … then take the output from each in the same format and put them each in their own OpenOffice spreadsheet file. Finally, we compare the two files to see if there are any differences. Perhaps there's a better way to do this.

Edit: The table columns and layouts are different so using the combo of http://www.codeproject.com/KB/database/convsqlservertosqlite.aspx and http://www.sqlitecompare.com/ will not work here.

P.S. A lot of applications use SQLite internally: Well-Known Users Of SQLite

Best Answer

Hi Yes it is possible to open any sql server from within management studio when you have the correct odbc driver to do so. Create an ODBC connection to the *.db3 file and call it something like SQLite then try this is a query window

-- needs to be a system odbc connection not user

EXEC sp_addlinkedserver 
   @server = 'SQLite', -- the name you give the server in studio 
   @srvproduct = '',
   @provider = 'MSDASQL', 
   @datasrc = 'SQLite' -- the name of the system odbc connection you created
GO

This is how you get the data, you can create views using this sql as well if you like

SELECT * FROM OPENQUERY(SQLite, 'SELECT * FROM tbl_Postcode')

Related Question