?
Solved

Sql Compact Beginner

Posted on 2007-10-09
10
Medium Priority
?
674 Views
Last Modified: 2013-11-17
How do I do stuff with this thing.  

The main two things I want to know how to do is how to work with it in the designer.  I have all my tables in sql express.  I would like to just import them in, but the designer in .net isn't letting me, and it wouldn't let me open the sdf file in Management Studio Express.  Am I going to have to create the tables in the designer again....

When I get it there, the thing that I'm wanting to do with it is sync it with sql express in a windows program without wireless.  Any articles or examples of this would be greatly appreciated.  

Tony
0
Comment
Question by:picsnet
  • 6
  • 4
10 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 20041753
0
 
LVL 1

Author Comment

by:picsnet
ID: 20042083
there is something in this statement sql compact doesn't support.  I always create my tables in a designer so the syntax is a little odd to me... Does it not support primary keys, or does it not like one of the extra things or what.  I got this code from doing generate scripts...

CREATE TABLE [Users](
      [Code] [nvarchar](50) NOT NULL,
      [Password] [nvarchar](24) NOT NULL,
      [FirstName] [nvarchar](50) NULL,
      [MiddleName] [nvarchar](50) NULL,
      [LastName] [nvarchar](50) NULL,
      [JobDescription] [nvarchar](100) NULL,
      [Active] [bit] NOT NULL,
      [ManageUsers] [bit] NOT NULL,
      [ManageAssets] [bit] NOT NULL,
      [ManageOptions] [bit] NULL,
      [ViewReports] [bit] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
      [Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 20042283
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 1

Author Comment

by:picsnet
ID: 20042462
Are you sure, that article never mentions that it can't accept primary keys.  It gives the definition and everything for primary keys
0
 
LVL 1

Author Comment

by:picsnet
ID: 20042526
Here is the exact error message:

Major Error 0x80040E14, Minor Error 25505
> CREATE TABLE [Users](
      [Code] [nvarchar](50) NOT NULL,
      [Password] [nvarchar](24) NOT NULL,
      [FirstName] [nvarchar](50) NULL,
      [MiddleName] [nvarchar](50) NULL,
      [LastName] [nvarchar](50) NULL,
      [JobDescription] [nvarchar](100) NULL,
      [Active] [bit] NOT NULL,
      [ManageUsers] [bit] NOT NULL,
      [ManageAssets] [bit] NOT NULL,
      [ManageOptions] [bit] NULL,
      [ViewReports] [bit] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY Clustered
(
      [Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
The constraint specified is not valid.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 20042565
I dont think there is statistics
0
 
LVL 1

Author Comment

by:picsnet
ID: 20042572
i can get this statement to work:

CREATE TABLE [Users](
      [Code] [nvarchar](50) NOT NULL Primary Key,
      [Password] [nvarchar](24) NOT NULL,
      [FirstName] [nvarchar](50) NULL,
      [MiddleName] [nvarchar](50) NULL,
      [LastName] [nvarchar](50) NULL,
      [JobDescription] [nvarchar](100) NULL,
      [Active] [bit] NOT NULL,
      [ManageUsers] [bit] NOT NULL,
      [ManageAssets] [bit] NOT NULL,
      [ManageOptions] [bit] NULL,
      [ViewReports] [bit] NULL)

I'm just wondering what I am missing by not having the other statements.  I tried to add clustered right after primary key in this statement and it said it wasn't valid.  I took clustered out of the other statement, but it didn't like it either.  Its a little confusing
0
 
LVL 48

Accepted Solution

by:
Mikal613 earned 2000 total points
ID: 20042605
Yes DDL is supported. If you have the schema script, you can apply it on a SQL Server Compact Edition (SSCE) db.

Plz note that since SSCE supports a subset of the complete T-SQL syntax, not everything which is supported in SQL Server will be supported in SSCE. You might need to do some cleanup in the schema before applying it. E.g. clustered indexes, views or schemas, some data types such as xml/varchar are not supported etc.

A previous post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1300136&SiteID=1  could be relevant too.

0
 
LVL 1

Author Comment

by:picsnet
ID: 20042617
ok this works too.  It apparently doesn't like indexes, and the stuff inside the with statement.  
0
 
LVL 1

Author Comment

by:picsnet
ID: 20042619
CREATE TABLE [Users](
      [Code] [nvarchar](50) NOT NULL,
      [Password] [nvarchar](24) NOT NULL,
      [FirstName] [nvarchar](50) NULL,
      [MiddleName] [nvarchar](50) NULL,
      [LastName] [nvarchar](50) NULL,
      [JobDescription] [nvarchar](100) NULL,
      [Active] [bit] NOT NULL,
      [ManageUsers] [bit] NOT NULL,
      [ManageAssets] [bit] NOT NULL,
      [ManageOptions] [bit] NULL,
      [ViewReports] [bit] NULL,
  CONSTRAINT [PK_User] PRIMARY KEY
(
      [Code]
))
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question