Unable to execute SQLCE script in VS 2012 Express for Windows Desktop

Question

I am new to Entity Framework and SQL Server Compact.  I'm trying to use the "model first" method to create an Entity Framework 5.0 model and an SQLCE database.  After creating the model, I right-click the design surface and chose "Generate database from model" from the context menu. I was prompted for a database connection and selected SQL Server Compact and created a new database (the .sdf file was created with no tables and appears in Database Explorer). When I click Finish in the Generate Database wizard, a DDL script is created (Model1.edmx.sqlce) and opened in VS.  

The tutorial I am following says the next step is to right-click on the script and choose Execute SQL from the context menu.  The problem is that there is no Execute SQL choice in the menu. If I right-click on the open window for Model1.edmx.sqlce , my choices are:

• Breakpoint
• Run to Cursor
• Run Flagged Threads to Cursor (grayed out)
• Cut
• Copy
• Paste
• Outlining

I also see that the icon for Model1.edmx.sqlce in Solution Explorer is the one for an unknown file type. If I right-click on the item in Solution Explorer there is still no Execute SQL option.

It appears that I am missing an association between the sqlce filetype and some component that is supposed to run the script to create the SQLCE database. Does anyone know what I need to do to get this to work?

Answer 1

I think that SQL CE support may be missing from VS 2012 Express, you can use my standalone SQL Server Compact Toolbox to run the script. but you may need to fix it or use my repaired template: http://erikej.blogspot.com/2013/02/fixing-entity-framework-designer.html

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 2

Thanks for the reply Erik, I'll give it a try this evening.  However, unless I misunderstood your blog post, it seems to be describing a way to improve the content of the script.  My problem does not seem to be with the content of the script or whether it runs correctly, but that there appears to be no way to even attempt to run the script.  The only way I know attempt to run the script is to choose Execute SQL from a context menu.  I'm not sure if the context menu is supposed to be associated with the open script or with the filename of the script in the Solution Explorer, but neither context menu contains Execute SQL.  Is there some other way to force the script to run.

Perhaps, as you say, some support for SQL Server Compact is missing in VS 2012 Express, but I can use it to create SQL Server Compact databases, and Generate Database from Model allows me to select SQL Server Compact as the database type.  So far, the only thing I have found missing is the ability to execute a script with a file extension of .sqlce

Answer 3

If you can connect to the file in Data Explorer, you can run the script from there, using "New Query" (right click at table)

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 4

If you can connect to the file in Data Explorer, you can run the script from there, using "New Query" (right click at table)

I tried to follow your suggestion, but I may have misunderstood.

I found the name of database in Database Explorer and right-clicked on the empty Tables node.  I chose New Query from the context menu, which opened a new query window.  I copied the contents of the .sqlce file and pasted them in the pane of the query that contained the SQL command. Then I right-clicked on the SQL command and chose Execute SQL.  That resulted in an error message saying "The CREATE TABLE SQL construct or statement is not supported."

Answer 5

yes, just click ok and ignore the message... it is not true

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 6

I am following the same tutorial that Blackwood is and am experiencing the same issue.  I click ok as Erik suggests but now get.

---------------------------

Microsoft Visual Studio
---------------------------
SQL Execution Error.

Executed SQL statement: 

-- --------------------------------------------------

-- Entity Designer DDL Script for SQL Server Compact Edition

-- --------------------------------------------------

-- Date Created: 02/22/2013 10:59:23

-- Generated from EDMX file: C:\Plasti...
Error Source: SQL Server Compact ADO.NET Data Provider
Error Message: There was an error parsing the query. [ Token line number = 4,Token line offset = 1,Token in error = CREATE ]
---------------------------
OK   Help   
---------------------------

So I attempted to run the application and I now see I have 1 table created.  There should be two.  But the app fails.  So the Getting Started Video from http://msdn.microsoft.com/en-us/data/jj205424 needs updated for sure.

And I guess we have to download the CE tool Erik mentions to actually run a script?

And I have VS2012 Premium.

Answer 7

Steven:

The MSDN link does not refer to SQL Server Compact in anmy way, only SQL Server. The issues you have relate to SQL Server Compact. Follow the instructions in my proposed answer, and thing will work (let me know if they don't)

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 8

Erik,

I see you are correct they just use SQL.  So I switched to SQL and it seems to work.  Also downloaded your template and testing with CE.  Thanks for that. If there is something you would like me to do to help get that officially updated faster, please let me know.

Answer 9

The Designer is not currently open source, (the EF team is working on it), but you can vote for the Work item here: http://entityframework.codeplex.com/workitem/824

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog