Database Administrators
Q&A for database professionals who wish to improve their database skills
Latest Questions
2
votes
1
answers
763
views
How does merge replication work with SQL Server CE
I have been trying out merge replication with SQL Server databases, and was just wondering how merge replication works on SQL Server CE? For instance merge replication with SQL Server databases normally relies on triggers, and SQL Server CE doesn't have any triggers. Also do tables such as `MSmerge_...
I have been trying out merge replication with SQL Server databases, and was just wondering how merge replication works on SQL Server CE?
For instance merge replication with SQL Server databases normally relies on triggers, and SQL Server CE doesn't have any triggers.
Also do tables such as
MSmerge_conflicts_info and sysmergesubscriptions etc exist with the SQL Server CE version of merge replication?
peter
(2187 rep)
Jun 6, 2012, 12:01 AM
• Last activity: Aug 5, 2022, 08:02 AM
2
votes
1
answers
105
views
Index that mimics full-text-index
I have a database that is retrieving information quite slowly, I asked a question and was told to use full-text-indexing, this worked, but now we switched over to a `.sdf` file and Sql Server Compact Edition 4.0 does not support full-text-indexing Is there any index that will mimic the way a full-te...
I have a database that is retrieving information quite slowly, I asked a question and was told to use full-text-indexing, this worked, but now we switched over to a
.sdf file and Sql Server Compact Edition 4.0 does not support full-text-indexing
Is there any index that will mimic the way a full-text-index works?
https://dba.stackexchange.com/questions/86321/update-and-insert-statement-taking-forever
This is the code I am using:
SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC
PredTable = PredTA.GetDataByPartialWord(combo)
If PredTable.Count = 1 Then PredTA.Update(PredTable(0).TimesUsed + 1, combo)
If PredTable.Count = 0 Then PredTA.Insert(combo, 1)
Dman
(133 rep)
Dec 24, 2014, 02:30 PM
• Last activity: Dec 20, 2021, 05:02 PM
0
votes
2
answers
650
views
Skip one row inside request using inner join (sql compact server 4.0)
I am using SQL server compact 4.0 and VB.net and Visual studio 2019. I am using this request to extract information from 4 tables : Table0, Table1, Table2 and Table3. This request is working fine before, but now I have one problem because one column in Table 3 (ID_C) is now duplicated, and this requ...
I am using SQL server compact 4.0 and VB.net and Visual studio 2019.
I am using this request to extract information from 4 tables : Table0, Table1, Table2 and Table3.
This request is working fine before, but now I have one problem because one column in Table 3 (ID_C) is now duplicated, and this request needs table 3 without this duplication.
One solution for my problem is to create a new table (table 3 without duplication) and the problem will be solved. But I am thinking because I am beginner in SQL, maybe there is a solution to skip one row in Table3 inside the request. I have searched, but I didn't find a solution for this problem.
"SELECT Table0.M " +
"FROM Table1 INNER JOIN " +
"Table2 ON Table1.ID = Table2.ID_V INNER JOIN " +
"LM ON Table2.ID_M = Table0.ID_M INNER JOIN " +
"Table3 ON Table2.ID_C = Table3.ID_C " +
"WHERE (Table1.IDX_V = #IDX_V#) AND (Table3.ID_C = '#ID_C#')"
In the code #IDX_V# and #ID_C# are replaced by some values.
all.west
(3 rep)
Oct 1, 2021, 03:12 PM
• Last activity: Oct 3, 2021, 01:25 AM
1
votes
0
answers
2361
views
Ok to remove SQL Server Compact 3.5 from a SQL 2008 R2 Server?
We still have one production SQL 2008 R2 Server. Yes, there are plans to upgrade soon, just not immediately. In the meantime, our Vulnerability reports are flagging the server as vulnerable because it has End of Life software: - EOL/Obsolete Software: Microsoft Visual Studio 2008 Detected - EOL/Obso...
We still have one production SQL 2008 R2 Server. Yes, there are plans to upgrade soon, just not immediately. In the meantime, our Vulnerability reports are flagging the server as vulnerable because it has End of Life software:
- EOL/Obsolete Software: Microsoft Visual Studio 2008 Detected
- EOL/Obsolete Software: Microsoft SQL Server Compact 3.5 Detected
So we want to see if we can remove these components to resolve the vulnerability.
However, doing due diligence, I noticed that the install date for these under Add/Remove Programs is the same as the install date for SQL Server 2008 R2 itself.
So now I'm thinking these are "part of" SQL Server 2008 R2, and maybe it's not safe to remove them. Googling doesn't reveal anything definitive, with a lot of the found articles no longer available.
My question is can the be removed without negative impact to SQL Server 2008 R2, and if not, can they be upgraded to a version that's not EOL?
So we want to see if we can remove these components to resolve the vulnerability.
However, doing due diligence, I noticed that the install date for these under Add/Remove Programs is the same as the install date for SQL Server 2008 R2 itself.
So now I'm thinking these are "part of" SQL Server 2008 R2, and maybe it's not safe to remove them. Googling doesn't reveal anything definitive, with a lot of the found articles no longer available.
My question is can the be removed without negative impact to SQL Server 2008 R2, and if not, can they be upgraded to a version that's not EOL?
Sandra
(111 rep)
Oct 1, 2020, 03:28 PM
• Last activity: Jul 9, 2021, 02:54 PM
0
votes
2
answers
1189
views
SQL Server compact- Query that return 2 constant columns even if query didn't return data
Please look at the following simple query: Select CustomerID From Customers c Where c.CustomerId = "1234" I want to write similar query that does the following: Always return 2 columns with constant string instead of CustomerID in this structure: If value was found then return 2 columns: Found Value...
Please look at the following simple query:
Select CustomerID
From Customers c
Where c.CustomerId = "1234"
I want to write similar query that does the following:
Always return 2 columns with constant string instead of CustomerID in this structure:
If value was found then return 2 columns:
Found Value in DB, CustomerId
Else return:
Didn't found value in DB, CustomerId
How can I do that? Especially in Microsoft compact SQL server limited syntax...
user29534
(13 rep)
Oct 19, 2013, 04:51 PM
• Last activity: Nov 12, 2019, 09:57 AM
0
votes
1
answers
144
views
Is the subscription of merge replication only for SQL Server Compact Edition?
While creating a publication I saw this [![enter image description here][1]][1] Does this mean the subscribers can't be other editions of SQL Server but Compact? Do other editions need to use Peer-to-Peer replication instead? Thanks [1]: https://i.sstatic.net/yC1CU.png
While creating a publication I saw this
Does this mean the subscribers can't be other editions of SQL Server but Compact?
Do other editions need to use Peer-to-Peer replication instead?
Thanks
Does this mean the subscribers can't be other editions of SQL Server but Compact?
Do other editions need to use Peer-to-Peer replication instead?
Thanks
igelr
(2162 rep)
Nov 7, 2018, 08:26 AM
• Last activity: Nov 7, 2018, 09:01 AM
0
votes
1
answers
621
views
How to convert a Microsoft SQL Server CE file (.sdf) to Postgres 9.6 /pgAdmin 4?
I have a database file `data.sdf` that I want to import into PgAdmin 4. I've considered using pgloader but the file format does not seem to be accepted. Does anybody have any hints on how I might go about making this work?
I have a database file
data.sdf that I want to import into PgAdmin 4. I've considered using pgloader but the file format does not seem to be accepted. Does anybody have any hints on how I might go about making this work?
Dijon Kock
(1 rep)
Nov 10, 2017, 12:17 PM
• Last activity: Nov 11, 2017, 10:08 AM
4
votes
0
answers
306
views
merge replication too big
I have multiple handhelds which sync their data via merge replication. From time to time, new core data is imported on the server-database. For various reasons I keep the old data in the databse and set a flag deactivated = 1. In a filter I only download rows where deactivated = 0. The core-data I'm...
I have multiple handhelds which sync their data via merge replication. From time to time, new core data is imported on the server-database.
For various reasons I keep the old data in the databse and set a flag deactivated = 1.
In a filter I only download rows where deactivated = 0.
The core-data I'm talking about are 6 tables. Each has hundreds of thousands of rows. About 30,000 rows are sent to the handhelds during syncing.
The .OUT-File in the MS SQL CE-folder on the server has about 10 MB (!).
On syncining via gprs I receive this error
>SQL CE error 28562 - Failed to create the Delete message to send to the Server.
Any ideas how to handle this problem?
Mobile DB: SQL Server CE 3.1 SQL Server 2008 R2
abc
(183 rep)
Apr 5, 2013, 06:14 AM
• Last activity: Apr 2, 2017, 09:38 AM
0
votes
1
answers
624
views
Storing a json string that is 10 kb in size in a SQL Server CE cell
I have a SQL Server CE database in which I have column that takes values of json strings. These json strings are above 9kb as a result of which I am unable to use `varchar` or `char`. I guess I could tackle this through the usage of `ntext`. But in SQL Server, this is marked to be removed in an upco...
I have a SQL Server CE database in which I have column that takes values of json strings. These json strings are above 9kb as a result of which I am unable to use
varchar or char. I guess I could tackle this through the usage of ntext.
But in SQL Server, this is marked to be removed in an upcoming edition. I am not sure of SQL Server CE. Also I am having where and having conditions / statements on other values stored in this column.
Therefore having ntext may not help as well. Are there any other data types that I could use to store this json string? Any help would be much appreciated.
AnOldSoul
(471 rep)
Mar 16, 2017, 05:05 AM
• Last activity: Mar 16, 2017, 01:10 PM
0
votes
1
answers
139
views
Can you Mirror an Microsoft SQL compact Edition database
We have a PLC inputting summary data into a Microsoft Compact SQlce Edition database. We would like to run queries to on this data, but are unable due to the type of database it is, compact. It, as far as I understand, exposes nothing to the network so we're unable to connect with ODBC connection, o...
We have a PLC inputting summary data into a Microsoft Compact SQlce Edition database. We would like to run queries to on this data, but are unable due to the type of database it is, compact. It, as far as I understand, exposes nothing to the network so we're unable to connect with ODBC connection, or anything of that sort. The company that put it in suggested a database mirror to an existing SQL server database we already have. Which seems plausible except for the extra overhead, which might cause some latency issues.
Is it possible to Do a database mirror with the SQLce as the principle database? I have seen nothing on-line that makes me think this can be done, in fact I've seen hints that it can't. I am not set on Database mirror we just need a way to get the information off this device.
Sam Orozco
(103 rep)
Aug 19, 2016, 03:13 PM
• Last activity: Aug 20, 2016, 02:46 PM
1
votes
1
answers
150
views
How to get SQL Server Compact 4.0 Database content query?
I need to migrate data to a SQL Server 2014 database from an existing SQL Server CE 4.0 database. I'm having difficulty because my computer runs SQL Server 2012 so I'm not able to port the data via a graphical wizard. I am able to run scripts on all databases. I just don't know how to perform a the...
I need to migrate data to a SQL Server 2014 database from an existing SQL Server CE 4.0 database. I'm having difficulty because my computer runs SQL Server 2012 so I'm not able to port the data via a graphical wizard.
I am able to run scripts on all databases. I just don't know how to perform a the data migration without a graphical interface.
Karlo A. López
(131 rep)
Sep 9, 2015, 10:28 PM
• Last activity: Mar 29, 2016, 12:06 PM
1
votes
2
answers
1929
views
Options for keeping two SQL Server Compact databases in sync?
Developer here. I have a system where there is a front machine creating rows in a moderately complex database, and a back machine that must sync with the front machine to run reports. Both run SQL Server Compact databases. Currently, we pull (and back up) the database to the back. But now it takes t...
Developer here. I have a system where there is a front machine creating rows in a moderately complex database, and a back machine that must sync with the front machine to run reports. Both run SQL Server Compact databases.
Currently, we pull (and back up) the database to the back. But now it takes too long due to network latency and database size, so we are evaluating our options. One of them is to add a timestamp/rowversion column to each table. We can then just pull updated/inserted records and apply them accordingly.
Is using the timestamp to grab all new/updated records from the front to update/add them to the back a viable solution? Will the timestamps stay in sync, or will the back get new timestamps when the records are applied?
Or is there another option I am not aware of?
Ripped Off
(129 rep)
Sep 12, 2012, 10:26 PM
• Last activity: Dec 8, 2015, 09:02 PM
3
votes
2
answers
841
views
How can I re-index a column using SQL Server Compact Edition?
We have a table `TemplateItem` that has a column `ListIndex` that stores the order in which we want the items to show in the UI. This table is self-referencing / hierarchical. The query requirement is that the column `ListIndex` has to start from 0 and be sequential for each parent / child(ren) rela...
We have a table
TemplateItem that has a column ListIndex that stores the order in which we want the items to show in the UI. This table is self-referencing / hierarchical.
The query requirement is that the column ListIndex has to start from 0 and be sequential for each parent / child(ren) relationship.
The query I came up with is as follows:
SELECT Id,
ParentId,
Sub.NewIndex AS ListIndex
FROM TemplateItem
JOIN (
SELECT Id,
ROW_NUMBER() OVER
(
PARTITION BY ParentId ORDER BY ListIndex
)
- 1 AS NewIndex
FROM TemplateItem
)
AS Sub ON TemplateItem.Id = Sub.Id
*( unnecessary bits of query removed for readability )*
This works perfectly fine in **SQL Server 2008 R2**.
However, I now need to use this same functionality in **SQL Server Compact Edition 4.0** but, as you may already know, it will fail.
It does so because there is no ROW_NUMBER() in **SQL Server Compact Edition 4.0**.
How can I accomplish this same functionality?
Code Maverick
(131 rep)
Oct 13, 2015, 06:10 PM
• Last activity: Oct 14, 2015, 02:14 PM
1
votes
1
answers
849
views
Resolving Cyclical Reference in Referential Relationships
I'm designing a small SQL Server Compact Edition database and am attempting to enforce referential integrity. The database is a simple backend for a small desktop application that functions as an account/password manager. Here are my relationships: ![enter image description here][1] Now, I'm attempt...
I'm designing a small SQL Server Compact Edition database and am attempting to enforce referential integrity. The database is a simple backend for a small desktop application that functions as an account/password manager.
Here are my relationships:
Now, I'm attempting to set CASCADE delete rules like so:
category.id ---> account.category_id
account.id ---> credential.account_id
category.id ---> field.category_id
field.id ---> credential.field_id
After adding the first 3 relationships, I get an error while adding the 4th (field ---> credential):
*The referential relationship will result in a cyclical reference that is not allowed.*
I'm struggling to determine why this is happening and how to fix it. Any ideas?
Now, I'm attempting to set CASCADE delete rules like so:
category.id ---> account.category_id
account.id ---> credential.account_id
category.id ---> field.category_id
field.id ---> credential.field_id
After adding the first 3 relationships, I get an error while adding the 4th (field ---> credential):
*The referential relationship will result in a cyclical reference that is not allowed.*
I'm struggling to determine why this is happening and how to fix it. Any ideas?
Tyler Daniels
(113 rep)
Mar 27, 2014, 07:22 PM
• Last activity: Aug 21, 2015, 05:46 PM
1
votes
0
answers
69
views
Will installing WebMatrix (and Consequently SQL Server CE) Break My Current Installation of SQL Server 2005?
We have a server that has (and needs to keep) SQL Server 2005. We are hosting intranet sites on one server (server 1), but have a database on another server (server 2). One of these sites uses AJAX to go server-side (server 1) and then queries databases on server 2. We are trying to cut out the midd...
We have a server that has (and needs to keep) SQL Server 2005.
We are hosting intranet sites on one server (server 1), but have a database on another server (server 2). One of these sites uses AJAX to go server-side (server 1) and then queries databases on server 2.
We are trying to cut out the middle man and we want to install WebMatrix (which comes with SQL Server CE) on the same server as the database (server 2), but this server already has SQL Server 2005 installed.
Will a simple WebMatrix install (i.e., SQL Server CE) break the SQL Server 2005 install?
My guess is that it won't, but the damage recovery would be considerable if it does.
I don't think it matters, but server 2 has Windows Server 2003.
VoidKing
(165 rep)
Mar 11, 2014, 03:30 PM
• Last activity: Jun 17, 2015, 10:25 AM
0
votes
3
answers
383
views
SQL Server Compact - Will it perform well with heavier traffic sites?
I'm using Orchard `CMS`, and on initial setup, the default setting is to use `SQL Server Compact`. I'm not too knowledgeable about different database types and performance. So, will `SQL Server Compact` be able to handle a website getting 1,000+ hits? Assuming all else with the hosting provider is e...
I'm using Orchard
CMS, and on initial setup, the default setting is to use SQL Server Compact. I'm not too knowledgeable about different database types and performance.
So, will SQL Server Compact be able to handle a website getting 1,000+ hits? Assuming all else with the hosting provider is exceptional (bandwidth, up-time, etc).
EdwardM
(103 rep)
Dec 5, 2014, 11:22 PM
• Last activity: May 13, 2015, 09:12 AM
0
votes
2
answers
122
views
Added a unique index, but cannot find it in TABLE_CONSTRAINTS
I'm running the following command in my DB: CREATE UNIQUE INDEX [UK_NAME1] ON [TABLE1] ([COLUMN1] ASC); Now, I'm running the following query: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS And yet I can't find my unique index listed in there. Am I using the wrong query? What other issue could th...
I'm running the following command in my DB:
CREATE UNIQUE INDEX [UK_NAME1] ON [TABLE1] ([COLUMN1] ASC);
Now, I'm running the following query:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
And yet I can't find my unique index listed in there.
Am I using the wrong query? What other issue could there be?
Mugen
(117 rep)
Feb 15, 2015, 06:40 AM
• Last activity: Feb 15, 2015, 08:02 AM
0
votes
1
answers
86
views
Locking the database to a single user so that other user wont change it
I am using SQL Server CE 4.0. In office the database (`.sdf`) file are on a server. When I want to change design of the database I have to copy it to local PC and access it since n/w connection to a compact database not allowed. But when I modify the design of tables I have to be sure that no other...
I am using SQL Server CE 4.0. In office the database (
.sdf) file are on a server. When I want to change design of the database I have to copy it to local PC and access it since n/w connection to a compact database not allowed.
But when I modify the design of tables I have to be sure that no other user in my office connects to that database and changes the same database. Because I have to again copy the changed database to my server PC and I don't want the design changes made by others. So how can I be sure that no one else connected to the database which I have been editing. Is there any method of locking the database to a single user when I edit the design of the database.
IT researcher
(3178 rep)
Jan 23, 2015, 02:37 PM
• Last activity: Jan 26, 2015, 12:16 PM
-1
votes
1
answers
760
views
SQL Server CE tools to modify database in network share
I am using SQL Server CE 4.0 SP1. I am using Web Developer 2010 Express to connect to database and perform operations like creating table, modifying column etc. But we cannot connect a database in a network share which is not allowed. I tried with the [SQL Server CE Toolbox][1] and [Visual Studio ad...
I am using SQL Server CE 4.0 SP1. I am using Web Developer 2010 Express to connect to database and perform operations like creating table, modifying column etc. But we cannot connect a database in a network share which is not allowed.
I tried with the SQL Server CE Toolbox and Visual Studio add-in these tools allow us to access database present in network path but does not allows us to edit columns directly. It only creates script which we need to run again. So it's not a simple tool.
Is there any other tool which allows us to directly edit columns and perform all database operation for the database (
.sdf) in network share ?
IT researcher
(3178 rep)
Jan 15, 2015, 06:38 AM
• Last activity: Jan 15, 2015, 03:33 PM
0
votes
1
answers
1555
views
SQL Server CE 4.0 network share access
In our application we are planning to use SQL Server CE 4.0 using OLE DB. The connection string is shown below. Dim strConn As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=\\pc137\d\desktop\mydb.sdf; SSCE:Database Password='password';" Dim conn As New OleDbConnection conn = New Ol...
In our application we are planning to use SQL Server CE 4.0 using OLE DB. The connection string is shown below.
Dim strConn As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=\\pc137\d\desktop\mydb.sdf; SSCE:Database Password='password';"
Dim conn As New OleDbConnection
conn = New OleDbConnection(strConn)
conn.Open()
This one worked for both the local and network share files (
.sdf)
We tried to connect using Visual Studio for the network share we get following error
> SQL Server Compact does not support opening database files on a network share.
We have referred this links
So is it allowed to connect SQL Server CE 4.0 using OLE DB in network share?
Does it cause any other problem?
IT researcher
(3178 rep)
Dec 17, 2014, 12:23 PM
• Last activity: Dec 18, 2014, 12:01 PM
Showing page 1 of 20 total questions