none
convert .mdf to .sdf

    Question

  • how can i convert .mdf to .sdf using SSIS and using code in c#

    please help me

    thx alot

    Tuesday, November 28, 2006 11:23 PM

Answers



  • Are all your data types present in the mdf supported in Sql Compact, or at least convertable? For example, the xml data type is not supported in .sdf files, whereas there is an xml data type in SQL 2K5 .mdf files.

    Will your .mdf databases files fit into 4Gb, the file size limitation on Sql Compact .sdf files?

    If so, then .mdf files are convertable to .sdf files with SSIS, using the OLEDB provider or the Sql Mobile Destination.  And I suppose, if you have an .mdf file with more than 4GB, you can always create multiple .sdf files.

    What version of SQL Compact are you using? (3.1 or 3.5)

    3.5 OLEDB connection strings look like the following (note the OLEDB provider is different than 3.1)
    Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source='c:\IsRoot\Cache\LookupCache.sdf';Persist Security Info=False;Mode=ReadWrite|Share Deny None;SSCE:Max Database Size=4000;SSCE:Temp File Directory='c:\IsRoot\Cache'

    3.1 OLEDB connection string

    Provider=microsoft.sqlserver.mobile.oledb.3.0;Data Source='c:\IsRoot\Cache\LookupCache.sdf';Persist Security Info=False;Mode=ReadWrite|Share Deny None;SSCE:Temp File Directory='c:\IsRoot\Cache'

    Thursday, July 19, 2007 11:58 PM

All replies

  • I have the same problem. I am using SQL Server 2005. This convertion is very important.
    Thursday, July 19, 2007 6:50 PM
  • A couple questions:

     

    1. What is .SDF???
    2. What do you mean when you say convert the .mdf? Are you trying to move all the data in the database to some other database/File/etc?

     

     

    Thursday, July 19, 2007 7:37 PM
  • An .sdf is a SQL Compact database file.
    Thursday, July 19, 2007 9:18 PM


  • Are all your data types present in the mdf supported in Sql Compact, or at least convertable? For example, the xml data type is not supported in .sdf files, whereas there is an xml data type in SQL 2K5 .mdf files.

    Will your .mdf databases files fit into 4Gb, the file size limitation on Sql Compact .sdf files?

    If so, then .mdf files are convertable to .sdf files with SSIS, using the OLEDB provider or the Sql Mobile Destination.  And I suppose, if you have an .mdf file with more than 4GB, you can always create multiple .sdf files.

    What version of SQL Compact are you using? (3.1 or 3.5)

    3.5 OLEDB connection strings look like the following (note the OLEDB provider is different than 3.1)
    Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source='c:\IsRoot\Cache\LookupCache.sdf';Persist Security Info=False;Mode=ReadWrite|Share Deny None;SSCE:Max Database Size=4000;SSCE:Temp File Directory='c:\IsRoot\Cache'

    3.1 OLEDB connection string

    Provider=microsoft.sqlserver.mobile.oledb.3.0;Data Source='c:\IsRoot\Cache\LookupCache.sdf';Persist Security Info=False;Mode=ReadWrite|Share Deny None;SSCE:Temp File Directory='c:\IsRoot\Cache'

    Thursday, July 19, 2007 11:58 PM
  • Hey Everyone,

    I know that this is an old thread, but i'm having the same problem; only I cant find the OLE DB Provider for SQL Mobile Destination to create the connection string.

    I've created a new Integration Services Project and Im adding new data sources.  Im assuming that I need to create one data source for the source db and one for the destination db.  The source is a mdf database and that was easy to create.  Now im trying to add a data source for the sdf database.  when i create a new data source, it give me 3 providers to choose from;

    .Net Providers
    .Net Providers for OleDb
    Native OLD DB

    Unfortunately, I dont see
    OLE DB Provider for SQL Mobile Destination in either of the three categories. 

    Is that where is should be or am i barking up the wrong tree?  Any and all help would be appreciated.  Thanks!
    Wednesday, November 12, 2008 1:11 PM
  •  pwquigle wrote:
    Hey Everyone,

    I know that this is an old thread, but i'm having the same problem; only I cant find the OLE DB Provider for SQL Mobile Destination to create the connection string.

    I've created a new Integration Services Project and Im adding new data sources.  Im assuming that I need to create one data source for the source db and one for the destination db.  The source is a mdf database and that was easy to create.  Now im trying to add a data source for the sdf database.  when i create a new data source, it give me 3 providers to choose from;

    .Net Providers
    .Net Providers for OleDb
    Native OLD DB

    Unfortunately, I dont see
    OLE DB Provider for SQL Mobile Destination in either of the three categories. 

    Is that where is should be or am i barking up the wrong tree?  Any and all help would be appreciated.  Thanks!

     

    if i'm not mistaken, you need to install the Microsoft SQL Server 2005 Mobile Edition Device SDK to get the sql server mobile providers.

     

    hth

    Thursday, November 13, 2008 6:48 AM
    Moderator