 |
|
Hi,
Is it possible to convert a sql server compact db, i.e. a standalone file that ends in .sdf?
I could not figure out what to enter in the "SQL Server Address" for this, or if it was possible.
Thanks
Rael
|
|
|
|
|
The utility does not support this currently as I have no experience with the compact edition..
Sorry
Liron
|
|
|
|
|
|
Thanks very much...saved me a lot of time.
needed a quick conversion for a demo and found this 
|
|
|
|
|
|
i have download converter for converting SQ L Server DB to Sq Lite but it is not working.
please anyone can help how do i use my sq l server database without using web services.
|
|
|
|
|
Did you download the latest binary version ?
Liron
|
|
|
|
|
Hi,
I am using your tool for conversion from Sql Express 2005 to Sqlite, but it thorws an error. The error describes below..
DEBUG 2014-06-25 10:43:40,291 (C:\Documents and Settings\liron\My Documents\Downloads\SqlConverter_v1_16\SqlConverter_v1_16\DbAccess\SqlServerToSQLite.cs:871) - parsed view schema for [winserview2]
ERROR 2014-06-25 10:43:40,334 (C:\Documents and Settings\liron\My Documents\Downloads\SqlConverter_v1_16\SqlConverter_v1_16\DbAccess\SqlServerToSQLite.cs:74) - Failed to convert SQL Server database to SQLite database
System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.SQLite, Version=1.0.65.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139' or one of its dependencies. The system cannot find the file specified.
File name: 'System.Data.SQLite, Version=1.0.65.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139'
at DbAccess.SqlServerToSQLite.CreateSQLiteDatabase(String sqlitePath, DatabaseSchema schema, String password, SqlConversionHandler handler, FailedViewDefinitionHandler viewFailureHandler)
at DbAccess.SqlServerToSQLite.ConvertSqlServerDatabaseToSQLiteFile(String sqlConnString, String sqlitePath, String password, SqlConversionHandler handler, SqlTableSelectionHandler selectionHandler, FailedViewDefinitionHandler viewFailureHandler, Boolean createTriggers) in C:\Documents and Settings\liron\My Documents\Downloads\SqlConverter_v1_16\SqlConverter_v1_16\DbAccess\SqlServerToSQLite.cs:line 108
at DbAccess.SqlServerToSQLite.<>c__DisplayClass1.b__0(Object state) in C:\Documents and Settings\liron\My Documents\Downloads\SqlConverter_v1_16\SqlConverter_v1_16\DbAccess\SqlServerToSQLite.cs:line 68
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].
Kindly give me solution.
Thanks
|
|
|
|
|
Hey man i am Iranian and you are Israelite, but you are help me a lot and save my time, i love this world and thank U so much dear 
|
|
|
|
|
Hi - thanks for your kind words
Liron
|
|
|
|
|
Hi, I am actually new to this , I am using an RAD Studio for my android app development where I need an .s3db file format for sql lite database. But using the above file it convert it to the .db file format . So how to convert it to the .s3db format.Please help
|
|
|
|
|
I get this error after selecting tables dialog:
"Could not load file or assembly 'System.Data.SQLite, Version=1.0.65.0,
Culture=neutral, PublicKeyToken=db937bc2d44ff139' or none of its
dependencies. The system cannot find the file specified"
can u help pls ?
thank you
|
|
|
|
|
Please check if you've compiled the software using x64 build configuration. If so - change it to x86 and recompile.
Liron
|
|
|
|
|
thanks for the reply
Can I make the change without using visual studio ?
best regards
|
|
|
|
|
Which version did you download ?
Liron
|
|
|
|
|
|
Please try to use the precompiled version and tell me if it works.
Liron
|
|
|
|
|
It worked.
thank u so much
kind regards
|
|
|
|
|
you saved a lot of my lifetime
|
|
|
|
|
Thank for the great work.
Can you pls add datatype datetimeoffset.
One more issue with Timespan.
|
|
|
|
|
Also can't convert if there is geometry or geography present.
|
|
|
|
|
|
|
Thank you for this nice peace of software.
I noticed that doing a commit every 1000 recoed is very time-consuming and not neccessary when having the db exclusive. Removing this thill works and is more than 10x faster. 2000 inserts per second againt 30000 per second.
Although consider journal_mode (set to WAL?) and synchronouse (set to normal?)
Thanks, Hardy.
|
|
|
|
|
If you have the problem of using this converter and then not being able to access the resulting file afterwards with an error of "The process cannot access the file ... because it is being used by another process." when using a new version of System.Data.SQLite then you can solve this by wrapping a few of the Sqlite resources created in the converter in using clauses.
method CopySqlServerRowsToSQLiteDB:
using (SQLiteCommand insert = BuildSQLiteInsert(schema[i]))
method AddSQLiteView:
using (SQLiteTransaction tx = conn.BeginTransaction())
using (SQLiteCommand cmd = new SQLiteCommand(stmt, conn, tx))
method AddSQLiteTable:
using (SQLiteCommand cmd = new SQLiteCommand(stmt, conn))
method AddTableTriggers:
using (SQLiteCommand cmd = new SQLiteCommand(WriteTriggerSchema(trigger), conn))
|
|
|
|
|
any updates for VS 2013 - .net 4.5.1 ?
kiquenet.com
|
|
|
|
|
Excellent IDEA, SOLUTION AND SOFTWARE!
Thank you vey much, I really appreciate your contribution.
Have a great day!
|
|
|
|
|
Well organized program.
In order to convert sql server DB, I had to first "mount" the database to be converted using SQL Server Management Studio Express 2005, where I use the Object Explorer to Attach the database to be converted. Otherwise I do not see the database in the drop down combo-box.
Question #1:Is there is a way to use this converter to locate the database to be converted without using SQL Server Management Studio Express first.
Question #2: Is there a way to make this converter work without first installing the sql server express engine. Perhaps there is enough sql server components in the basic Windows 7 or 8 OS already.
Thanks.
|
|
|
|
|
Hi
AFAIK you can't do the conversion without first having SQL server installed and your database attached..
Having said that - I've heard of some SQL Server variant called local-db which does not dictate installing a full SQL Server engine.
If you want to tinker with the code then maybe you should check using local-db libraries to access SQL Server files. It shouldn't require you to actually change the code a lot - only to reference these new DLL files and to accept the path to the sql server database files via the GUI.
HTH
Liron
|
|
|
|
|
Liron: Thanks for your reply. If I can go back to the SQL Server Management Studio Express where I had to use it as a first step to attach to the database to be converted; is it easy to add code to your present converter to attach to the database using the "Select DB" combo-box in the GUI and adding browsing function to locate the database for conversion? This will improve the utility of the program to the point that the SQL Server Management Studio Express does not have to be installed (since Management Studio is installed optionally in SQL Server Express).
Thanks again.
|
|
|
|
|
Hi
This can be a very good idea. Unfortunately I'm in a very busy period right now..
I'll check this when I have some free time.
Thanks
Liron
|
|
|
|
|
Liron: I am glad that you think this addition to the converter is valid. The addition would be quite practical for one of my projects. Many thanks.
|
|
|
|
|
 makes me happy
|
|
|
|
|
Your code is so clean and easy to read. 0.o Ohhhh Yuh Yuhhhhh ohhhhhhh YUHHHHH *-*
|
|
|
|
|
|
Saved me a ton of time. Gracias!
|
|
|
|
|
I have a small database I plan to use in WinRT with SQLite that contains only 4 tables with foreign keys between 3 of them and around 10,000 records...
this converted it very quickly and all seems to be working very well.
the only thing that was strange for me (coming from MS SQL), was how foreign keys are stored.
from {111f24c0-75c1-40ff-a18f-0586ca473503} to X'C0241F11C175FF40A18F0586CA473503'
looks like some sub parts have values inverted or transposed somehow, though the references in other tables maintained correctly... so I guess it doesn't matter so much.
is there some thing I should have done differently to maintain uniqueidentifiers on the conversion?
|
|
|
|
|
Hi Troy
What you saw is the representation of the GUID bytes in SQLite BLOB literal format. It's ok and you don't need to do anything.
Liron
|
|
|
|
|
Hi,
Many thanks for your tool, it really saves my day every time I use it!
But could you please tell which versions is supported?
For sure 2005 and 2008 - I tried them myself
But it failed for SQL7  reason is the toll cannot find 'sp_tablecollatios' stored procedure
Is there any way to workaround it?
Regards,
Sergey
|
|
|
|
|
SQL7 is too early ... I've tested it on SQL Server 2005 so I presume it should be supported from this version going forward (SQL Server should be backward compatible).
HTH
Liron
|
|
|
|
|
So there is no chances to support SQL7?
pity if so =(
|
|
|
|
|
I am a newbie when it comes to db conversions. I was wondering that with all the hype over Mobile development, is it possible to edit this code so that it takes an input of sqlite db of Android platform and insert the data to a SQL Server db on website?  Would love an answer on if its possible and how to proceed with it.
|
|
|
|
|
You cannot simply edit the existing code to achieve what you want..
The problem is that the conversion is not symmetric.
For example - When doing the conversion from SQLite to SQL Server - I'm using meta data tables in SQL Server in order to know how the database tables are constructed and then translate this knowledge to SQLite schema commands.
In contrast - SQLite doesn't give you meta data tables like SQL Server (tables that describes database objects like tables, indices etc).
Instead - you'll have to deal with raw SQL CREATE statements (in SQLite dialect) that you'll need to parse yourself in order to know how to translate them to DDL commands in SQL Server (which are also different than those in SQLite BTW).
If you are interested in that then maybe you can take a look at another project I wrote (also in codeproject) called SQLite Compare.
There you'll find a parser module that builds an abstract syntax tree for your SQLite statements. From them you will be able to generate the appropriate SQL Server DDL statements.
All in all - this is not a very complex project. The problem is it is very time consuming and will require lots of code and testing.
HTH
Liron
|
|
|
|
|
oh ok  . Is there any other method in which we can simply read/import the SQLite file into SQL Server? something like using odbc connection and Link Server?
|
|
|
|
|
I have no idea, sorry
Liron
|
|
|
|
|
I have found a little error in CreateForeignKeySchema() method.
There are tables than have more then one ForeignKey constraint respect another table.
Es. :
TA.field_a1 to TB.field_b1
TA.field_a2 to TB.field_b2
In this case the query within CreateForeignKeySchema return all combination of the foreign key fields:
TA.field_a1 to TB.field_b1
TA.field_a1 to TB.field_b2
TA.field_a2 to TB.field_b1
TA.field_a2 to TB.field_b2
And that is a hard problem to remove from the created table.
My proposed correction is a modify of the query in :
SqlCommand cmd = new SqlCommand(
@"SELECT "
@" ColumnName = CU.COLUMN_NAME, "
@" ForeignTableName = PK.TABLE_NAME, "
@" ForeignColumnName = PT.COLUMN_NAME, "
@" DeleteRule = C.DELETE_RULE, "
@" IsNullable = COL.IS_NULLABLE "
@"FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C "
@"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME "
@"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME "
@"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME "
@"INNER JOIN "
@" ( "
@" SELECT i1.TABLE_NAME, i2.COLUMN_NAME, i2.Ordinal_Position "
@" FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 "
@" INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME "
@" WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' "
@" ) "
@"PT ON PT.TABLE_NAME = PK.TABLE_NAME "
@"INNER JOIN INFORMATION_SCHEMA.COLUMNS AS COL ON CU.COLUMN_NAME = COL.COLUMN_NAME AND FK.TABLE_NAME = COL.TABLE_NAME "
@"WHERE FK.Table_NAME='" ts.TableName "' "
@"AND CU.Ordinal_Position=PT.Ordinal_Position ", conn);
In this case the foreign key are correct.
However, Thanks for your good job... Very great
Alfonso Faiella
|
|
|
|
|
Nicely written program However, The program failed to process geometry columns in some of my tables.
The fix was to handle & avoid them within the code ReadSqlServerSchema method.
By the way, Let me know, if you have future plans to implementing the sqllite spatial.
http://blog.perrygeo.net/2008/04/15/spatial-data-in-sqlite/
Sandeep Kuniel
|
|
|
|
|
Hi
When I wrote this program there was no support for spatial types in SQLite, so these types couldn't be handled. I'll take a look in the link you've posted when I have some spare time.
Thanks
Liron
|
|
|
|
|
Can you post a commandline version, I would like to use this to backup small databases that I can send to remote dev person. I don't have any dev tools.
Love the app.
Please, Please ,Please
modified 12-Jul-13 15:42pm.
|
|
|
|
|
Thanks for the nice tool. Just wanted to check whether the tool support Cascade delete? Currently I dont see it supporting.
|
|
|
|
|
Submit an article or tip
![Rose | [Rose]](https://waybackassets.bk21.net/20140924064809im_/https://dj9okeyxktdvd.cloudfront.net/script/Forums/Images/rose.gif)
News 
Joke 
Rant 
Admin