5

There is no multi value CASE statement in T-SQL. However, you could do a join to a constant value row source like this: SELECT * FROM dbo.Statuses AS S LEFT JOIN (VALUES(1,'Success','AStatus','Expected:1'), (0,'Failure','AStatus','Received:0') ) AS SV(AStatus, Status, Reason, Message) ON S.AStatus = SV.AStatus;


4

You compare the length of the string with and without the text you're searching for: select ObjectID ,value ,(len(value) - len(replace(value,'love',''))) / 4 as occurs from exampleTable where value like '%love%'


4

After OP's Edit to the question : Try to use Visual Studio 2010 SP1 + VS Tools for SQL CE 4 -- or -- SQL Server Management Studio 2008 R2 supports for SQL Server Compact 3.5 database. (ATTENTION from SSMS version 2012 and newer, Sql Server Compact databases are no longer supported by SSMS; You must use SSMS 2008 R2 or OLDER) Note : SQL Server Management ...


3

Since you are using SQL Server Compact Edition, which doesn't support triggers that could solve this issue, my recommendation is either: write your own manual DELETE statements in the proper order, rather than use CASCADE change the design use Express / LocalDB instead of Compact Edition, which is extremely limited and has been deprecated


3

You cannot rename columns in SQL Server Compact edition you must use ALTER TABLE DROP column and ALTER TABLE ADD column


3

With the aid of a Numbers table: CREATE TABLE Numbers ( i INT NOT NULL PRIMARY KEY ) ; INSERT INTO Numbers (i) VALUES -- fill the table up to 10 (1), (2), ... (10) ; INSERT INTO Numbers (i) -- fill the table up to 100 SELECT m.maxi * a.i + b.i FROM ...


3

One problem with your proposed design is that instead of simple JOINs you have to run dynamic SQL every time you want to see the actual value of any entities. Furthermore, you will have complicated procedures (or triggers) for inserting data into ProjectContents. Stick with the ProjectEntity1, ProjectEntity2, ... solution if you can have more that one ...


2

From the development perspective, you could try the Microsoft Sync Framework. For further information about Microsoft Sync Framework, refer to the following links: http://msdn.microsoft.com/en-us/library/bb726015(v=sql.105).aspx https://stackoverflow.com/questions/581110/using-ms-sync-framework-to-synchronize-two-sql-ce-dbs


2

I can only guess that the problem is related to some sort of index corruption - maybe it gets out of sync with the actual data store. You could isolate index issues by just dropping the index. That will slow down some or all of the queries that access that table, but if the problem goes away, you know for certain that the index was the source of the ...


2

SQL Server Compact is mostly used for building standalone and occasionally connected applications for mobile devices, desktops, and Web clients. There are lots of features not supported in SQL Server Compact check differences and with lack of these features hosting provider with exceptional bandwidth, up-time, etc will not help in terms of performance and ...


2

I have not used SQL Server Compact, but because of its design, there are warnings in the SQL Server 2012 documentation about concurrency issues. See: http://technet.microsoft.com/en-us/library/ms171845(v=sql.110).aspx for warnings on: Lost updates. Inconsistent analysis (nonrepeatable reads). Phantom reads. If you need a free and much more functional ...


2

No, this is not supported. And using the Oledb provider from a .net application is not supported either, use the Ado.net provider (System.Data.SqlServerCe)


2

For your case and reasons to upgrade merge replication is available in SQL Server CE 3.5 Stored procedures, views, triggers is not available in SQL Server CE 3.5 for a basic comparison between the 2 editions in your case working on a net book 1. SQL Server CE 3.5 can Runs as in-process with application SQL Server Express runs as a service 2. SQL CE ...


2

SQL Server Compact does not support CTE or temp tables. Why not simply implement the solution in code, since the SQL Compact database runs in process anyway?


2

Since you can't generate the ID artificially with a windowing function, you will need to add a step and create it as an IDENTITY field in another table. Something like this --temp table to store all the new data CREATE TABLE #Hierarchy ( HId INT IDENTITY , ParentID INT , ListIndex INT ) --distinct ...


1

This only means that if you are using SQL Server Compact edition, that this edition can only be a subscriber, not a publisher. Here is more information on setting up merge replication between Microsoft SQL Server and SQL Server Compact 3.5. Here is an example of a different subscriber than SQL Server Compact edition, a blogpost which sets up a Merge ...


1

You can use my tools to script data and schema as SQL Server compatible scripts, and if required tweak those to run with Postgress: http://erikej.github.io/SqlCeToolbox/


1

Just because something is marked as deprecated doesn't meant that it's disappearing soon - there are features that have been marked deprecated for several versions which haven't been removed yet. That said, you're right to move away from deprecated features if the opportunity presents itself (but don't make changes simply for the sake of change), and you ...


1

You can copy the database file to the desktop and use a tool like SQL Compact Toolbox to query the data Or you could place a copy of the file on the SQL Server disk drive, and acces it via some SQLCLR code (this will allow you to mix with SQL server data) Or you can use some synchronization mechanism to transfer the data to SQL server (requires programming/...


1

You can use my ExportSqlCe command line tools to export the schema and data, and then use sqlcmd to run the resulting script against a SQL Server 2014 database https://exportsqlce.codeplex.com/wikipage?title=Command%20line%20utilities&referringTitle=Documentation


1

Use this syntax, which also allows you to name the constraint: ALTER TABLE Persons ADD CONSTRAINT UQ_PersonID UNIQUE (P_Id,LastName)


1

I am a SQL Server DBA - I am biased towards SQL Server. However if the budget is limited, I would suggest trying a mature open source database, perhaps MySQL, PostGreSQL or MariaDB. Performance isn't your real issue here. The security of your data and the safety of strong ACID transactions are what you should really prioritise. How are you going to backup ...


1

Derived tables and Case statements are supported in SQL CE, so you can use this script Select case when s.custs = 0 then 'Didnt found value in DB' else 'found value in DB' end ,'customer id' from (select COUNT(*) as 'custs' from Customers c Where c.CustomerId = 1234) s for reference check what can and what cannot be used in SQL CE


1

SQL Server Management Studio 2008 R2 should work as shown above. PLEASE NOTE Sql Server Management Studio 2012 and newer NO LONGER SUPPORTS managing Sql Server Compact files.


1

I have a similar system in place to populate a database on a SQL Server Express instance with a subset of data from a SQL Server Enterprise instance. For simplicity, I created TIMESTAMP fields on the source database, and modified the field definition on the destination database to the syntactically-equivalent BINARY(8) field type. I created a LINKED SERVER ...


1

I'd use ProjectContents approach with some modifications to avoid polymorphic associations. First, create a common parent table for all Entity. : CommonEntity(common_entity_id PK, entity_type varchar(20) not null, CONSTRAINT UQ_COMMON_ENTITY UNIQUE(id,entity_type), -- seems redundant, but now other tables can have FK to (id,entity_type) CONSTRAINT ...


1

SQL Server Compact has a built-in change tracking mechanism (similar to SQL Server Change Tracking), and when a database file is merge replicated, a number of system tables are added to the database, see http://blogs.msdn.com/b/sqlservercompact/archive/2007/04/18/merge-replication-system-tables.aspx - conflicts are only handled on ther server side. In ...


Only top voted, non community-wiki answers of a minimum length are eligible