SQL Server 2005 stored procedure to create a SQL Compact database

• sqlnzl

  SSC Journeyman

  Points: 79

  More actions

  March 26, 2008 at 11:25 pm

  #183646

  How can I use a SQL Server 2005 stored procedure to read tables and create them in a SQL Compact database?

• Grant Fritchey

  SSC Guru

  Points: 399047

  More actions

  March 27, 2008 at 6:58 am

  #794551

  I don't think that's possible. Unless there's a way to make the Compact database a linked server, there's no way to open a connection within TSQL. You could write CLR code to do it, but then why not just do the work within VB.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• sqlnzl

  SSC Journeyman

  Points: 79

  More actions

  March 27, 2008 at 4:09 pm

  #794918

  Thanks Grant,

  I thought that might be the case but sometimes with RDBMS toolsets there are exotic features rarely known about. Yes, I can do it in vb, I hoped there was a way within a sproc that would maybe be more efficient.

  Cheers

• Grant Fritchey

  SSC Guru

  Points: 399047

  More actions

  March 28, 2008 at 6:10 am

  #795104

  You can use the SQL Server Management Studio to connect to and work with Compact databases though, if that helps. It's not stored procedures but it is a bit easier & more elegant than otherwise. You can use scripts with that. I created a table like this:

  create table Whoopsie

  (Id int

  ,Hey nvarchar(50))

  You can't script out the whole database though. I was hoping to do that.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• sqlnzl

  SSC Journeyman

  Points: 79

  More actions

  March 28, 2008 at 6:01 pm

  #795534

  What I'm doing is consuming a webservice from a mobile device, to build a dataset in the sqlserver database, using a sproc to create temporary tables, then create a compact database and populate it from the temp tables. Finally, transfer the compact database to the device. If the sproc could have created the compact database directly, then I would have no need for temporary tables.