How reliable the SQL Compact 3.5 is?
What I need is to make sure that in case if my Windows CE based device lost power supply my SQL Compact 3.5 database will not be corrupted and I can read data from it.
Has anybody any experience in this area?
This is critical requirement for our project and I want to know it is feasible or not.
I posted a more detailed technical question a couple of weeks ago
with Subject = "Compact Edition 2005 Subscribers - Merge Agent failed after detecting that retention-based cleanup has deleted metadata"
But I got no replies.
Now my question is simpler: Is SQL Server 2005 Merge Replication reliable with Compact Edition subscribers on WindowsXP tablets?
We deleted all users' local databases a couple of weeks ago, and republished two publications with retention = 60 days.
Now we have at least one user who can't sync with the server, and replication monitor shows the error below.
But if she e-mails her sdf file to a support person, they can successfully sync her database.
We're sure that it's not a permissions issue, which is a normal suspicion when one user can perform a task that another can not.
It doesn't seem like a retention problem, because she's well inside the retention period.
She can successfully sync with the publication with upload/download tablers, but not with the publication with download only tables. Both publications were created on the same day, by the same DBA, with nearly identical properties.
This problem user has operated in the past with no problem, and she's configured identically to all of the others. It just seems like random flakyness with merge replication for Compact Edition subscribers.
I feel stupid saying that, because it sounds like I believe in a magical ghost in the machine. So my question is - IS MERGE REPLICATION A LITTLE BIT FLAKY FOR COMPACT EDITION SUBSCRIBERS ON XP MACHINES?
We've applied SP2 to the server, but no subsequent HotFixes.
We're trying to sell users and management on developing a larger occasionally connected system with the same replication scheme, so we need to know if it deserves our confidence.
Error messages:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)
Get help: http://help/MSSQL_REPL-2147199402
SQL 7.0I have a form in ASP.NET and I want to write the values to the SQLServer tables.The tables are Customer and Address tables.There will be an insert into the Customer table, and I need to use theIdentity of this inserted record for the Foreign Key in the Addresstable insert.For exampleINSERT INTO Customer (CustomerName)VALUES (@CustomerName)Select @@identity as CustomerIDINSERT INTO Address (Address, CustomerID)VALUES (@Address, CustomerID)My question is this. If I put this into a single stored procedure canI absolutely GUARANTEE that the @@identity value will be from theCustomer table insert, or could it feasibly be from another, as itwere, colliding operation?TIAEdward--The reading group's reading group:http://www.bookgroup.org.uk
View 2 Replies View RelatedHi..
I have a SQL Server 2005 Database .. I develop for the customers a C# Application , that sends some specific Information to my central Database.
If I would create a connection over tcp/IP (in order to send data to central database) like
sqlconnection1.ConnectionString = "Data Source=192.XXX.Y.ZZ,1433;Initial Catalog=Muster;Integrated Security=True";
it would not be reliable. How can we make that safe?
Thanks...
I have set up two Maintenance Plans to do daily backups overnight for our two DBs that are on our SQL server.
As a separate strategy we want to have a second backup done nightly that would involve
#1 detach the db(s)
#2 copy the mdf(s) and ldf(s) to L:xxx
#3 attach the db(s) again
#4 zip up the db(s)
#5 copy the files to a different server for storage.
I created #1, and #3 in query analyzer and saved the script.
I want the process to run at say 2AM and don't know how to schedule them to run - that is #1 ... #2 ... #3. Anyone have sample scripts for this kind of a backup strategy?
This seems like a very simple process especially for a restore and especially since there would not be any trasaction logs involved. This way if we had a KRASH we could take the backup from the separate server and install it on a warm SQL backup server.
Apart from the new servers name then what other steps would I need to cover to get the apps up and running in the quickest time. The app software runs as a client install - Access 97DB] I'm especially curious if I need to have more than a fresh MS2000 SQL server install.
I'm know I'm asking a lot for a first timer.
It seems we want to handle disaster recovery BEFORE we even have our first system crash.
many thanks
Maybe I am just a lot better at this than I thought, but I figure that somewhere there is a mathematical rule that is being overlooked. When I run dbcc sqlperf (lrustats) on some of my production machines, I sometimes end up with a cache hit ratio (which is defined as a percentage, mind you) that is slightly over the limit:
Statistic Value
-------------------------------- ------------------------
Cache Hit Ratio 100.00898
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 9434.0
I suspect some counter somewhere is getting wrapped around its 4 byte limit. Is there any reliable source for getting statisics about SQL Server performance? Users tend be unreliable and say everything is slow.
I have a process that restores a backup from a primary server to a backup server daily. When doing the restore, sometime it fails (for various reasons).
I have coded a job to Set offline, set online, an then do the restore:
RESTORE DATABASE [xxx] FROM DISK = N'D:Backup Stagingxxx.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
Sometimes it fails to bring back online, other errors as well. Is there a reliable method of doing this?
Hi,
For this scenario, what is the best method of exporting data to sql 2005.
I want to export data from desktop app across internet to sql which can do on a row by row basis, but this is very slow and if the connection goes down halfway then pretty much buggered.
What is the best, reliable and fastest way to copy data across internet (several thousand rows), I have read about Bulk Insert etc... but also how would get around an upload and crashes half way, is there a way of uploading and until the whole upload goes through then the data is inserted into the database.
Would appreciate any guidance.
Richard
I have been tring to get this one line figured out for a few days now.
'Job2 Info
Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM '" & CompanyKey & "'" '<<-------HERE
Dim cmd2 As New SqlCommand(selectSQL2, con)
'Job2 Select
Try
con.Open()
reader = cmd2.ExecuteReader()
(I have the full code below.) So here is the problem, this code is not populating the datagrid. There is data in the table I am selecting from. When I log in, my CompanyKey value displays in the label as "21". When I take out the "CompanyKey" variable, and just type in 21, the grid is populated. It is confusing the heck out of me. I have tried it this way:
selectSQL2 = "SELECT * FROM [" & CompanyKey & "]"
-and this way:
selectSQL2 = "SELECT * FROM & CompanyKey
and all the other ways I could think of. I researched it and can just not get it to work any way I do it. Any suggestions? Full code below:
____________________________________
Imports System.DataImports System.Data.CommonImports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'Job1(Info)Dim currentUserID
currentUserID = Context.User.Identity.Name.ToString()
Label1.Text = currentUserID
Dim selectSQL1 As StringselectSQL1 = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')"
Dim cmd1 As New SqlCommand(selectSQL1, con) Dim reader As SqlDataReader
Dim CompanyKey
'Job1 Select
Try
con.Open()
reader = cmd1.ExecuteReader()Do While reader.Read()
CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
con.Close()
End Try
'Job2 Info
Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM [" & CompanyKey & "]"
Dim cmd2 As New SqlCommand(selectSQL2, con)
'Job2 Select
Try
con.Open()
reader = cmd2.ExecuteReader()
GridView1.DataSource = reader
GridView1.DataBind()
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
ReaderResults.Text = CompanyKey
con.Close()End TryEnd Sub
End Class
Hi
We are checking VB 9 (Orcas).
we connected to database created under with sql server 7. with this code
Public cn As New ADODB.Connection
Public Sub OpenDB()
cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.")
End Sub
this code worked well.
we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).
Rgds
Pramod
Hello,
we've got a SQL Server 2005 which replicates with an SQL Server Compact 3.5. Every 10 to 20 synchronisations we're getting the error mentioned above. A Soft reset of the device helps to make the synchronization working again.
Why is this error happening and how can we resolve this?
BTW, we've also running system that replicates with an SQL Server Compact 3.0 without having this problems.
Thanks,
Markus
Hi there,
everything is ok for first run. but i leave the program relogin
than that error occured ppc2003 second edition devices. Windows mobile 5.0 device works fine.
can anybody help me?
VS2005 ver 8.0.50727
SSCE31VSTools-ENU.exe loaded
SSCE31SDK-ENU.msi loaded
machine 1
SQL 2005 loaded
machine 2
http://192.168.20.22/ssce/sqlcesa30.dll
"Microsoft SQL Server Compact Edition Server Agent" looks fine
pocket pc side
C:Program FilesMicrosoft SQL Server Compact Editionv3.1SDKinwce400armv4
.net cf 2.0 sp2
sqlce30.dev.ENU.ppc.wce4.armv4.CAB
sqlce30.ppc.wce4.armv4.CAB
sqlce30.repl.ppc.wce4.armv4.CAB installed too.
my code
---------------------------------------------
Dim RdaStr As String = "Provider=SQLOLEDB; Data Source=" + Server + ";Initial Catalog=" + DataBase + ";Integrated Security=SSPI"
Dim rda As SqlCeRemoteDataAccess
Try
rda = New SqlCeRemoteDataAccess "THIS LINE GIVES ME THAT ERROR
Catch ex As Exception
MsgBox(ex.ToString)
Application.Exit()
End Try
Try
rda.InternetLogin = [String].Empty
rda.InternetPassword = [String].Empty
rda.InternetUrl = "http://" + IP_no + "/ssce/sqlcesa30.dll"
rda.LocalConnectionString = ConnectString
Catch ex As Exception
MsgBox("Bağlantı hatası..")
Application.Exit()
End Try
------------------------------------------------------------------------------
i added the following code to very beginning of my code too.
that code lock my device
Declare Function LoadLibrary Lib "coredll" Alias "LoadLibrary" (ByVal lpLibFileName As String) As IntPtr
Dim pt As IntPtr
pt = LoadLibrary("\windowssqlceca30.dll")
pt = LoadLibrary("\windowssqlceoledb30.dll")
pt = LoadLibrary(\windowssqlcecompact30.dll)
-----------------------------------------------------------------------------
No one answered in another group, please help.Using access 2000 /msdeI know how to compact the access project file (.adp), there's a menu choicefor that.How do I compact the tables and stored procedures on the server? ( The .MDFfile)I only have the DOS promp OSQL utility to work with.thanks
View 1 Replies View RelatedI'm trying to add an SQL Compact Edition database to my application running on a CE 6.0 platform that I am also developing. Unlike PB 5.0, I don't see sql CE listed as an option in the catalog and when I try to run my application I get a pInvoke sqlceme30.dll error. I have found a number of references to this same issue elsewhere but no real good solutions. Most seem to point to the cab files not being deployed. I am thinking that I should have the necessary support files deployed in the CE image and I have downloaded and installed every sql CE version/update that I can find but none of them seem to add support to PB 6.0.
I am trying to add the cab files manually but I would appreciate any suggestions if there is a better solution............ Thanks
Hi,
Is SQL Server Compact Editon 3.1 supported on Windows CE 6.0?
When I install the cab file sqlce30.wce5.armv4i.CAB in CE 6.0 it says that it might not be compatible. If I continue anyway it seems to work fine, but only the first time the application starts and accesses the database. When restaring the application it fails to connect to the database until I have reinstalled the cab file.
Any clues about this?
Thanks!
/ Joel
Hi,
I have an application where I need a desktop Application to manage the data and I have PDA application which talks to RFID and gathers the data. I am a bit confused if i can have only one database which can work both on PDA and Desktop. Can you please help me decide if i should use compact or express for this purpose. Also I might have to use both compact and Express but is there a way to have common database for both where they can be replication and easily synchronised.
Help is much appreciated.
Regards
Trushar
Hi,
I am wondering why a database that was 2Gb in size would come down to just 300Mb when I copy all the data from the origional database into a blank new one.
I ran a database shrink on the origional database but that only reduced it from 2.1 Gb to 2Gb.
I was shocked to see the size difference when I copied the data.
As far as I can tell the copied database still contains all the records of the origional.
I remember that MSAccess had a Compact function that literally copied all the data from one databse into a new empty one - is there the equivalent for SQL?
Any thoughts?
Thanks
Hi,I just deleted about 1 million records from a SQL Server 2000 database. Iaccess it using Access 2000.I have Compact and Repair available in the adp under Tools. Should I do thatfrom Access? Will it not mess anything up?Thanks!J~
View 2 Replies View RelatedIs there a stored procedure that allow the compact database programmaticallyin SQL Server?
View 2 Replies View RelatedCan SQL Server Compact Edition do RDA with SQL Express? or I need to have SQL Server 2005(standard) instead of SQL Express? I will use RDA to synchronize the Compact Edition database on the phone and SQL Server database on desktop.
Thanks!
I am trying to make a simple example like sql management studio but stuck in the get table names part :) in first step. are there any easy way to get just table names from sdf file ?
I have tried to use SMO but no success.
Regards.
I have VS2008 (9.0.21022.8 RTM) and I cannot get SQL Compact 3.5 to work with it. Whenever I attempt to open a compact edition database, I get a message that "The operation cannot complete, click OK on the "Package Load Failure" dialog, reinstall SQL Server Compact 3.5 and then from the command line run devenv /resetskippkgs. I've tried that twice and no joy. I can connect and work just fine with SSMS 2005, but I cannot get it to work in VS2008.
Also when in VS2008 and I go to Server Explorer | Data Connections | Add Connection, I do not have Microsoft SQL Server Compact 3.5 in the list.
Any suggestions? Thanks.
-John
I've downloaded and installed SQL Compact Edition. When I go to Access to set up a project (or to use the upsizing wizard) the software doesn't see the Server on my computer. No doubt I'm missing some very simple step. Although the compact server is installed, there's no program file to run, so I don't see how to get into the server software in order to set up a database or project that I can link to from Access.
I've also downloaded and installed Microsoft.net. As with server, the files are in place but there's no program file to execute.
I am trying to make an asp.net application that will run from the SQL Compact database.
To deploy the application we changed from a full path Data Source="C:inetpubappasdf.sdf"
to
Data Source="|DataDirectory|asdf.sdf"
This returns:
Illegal Character in Path
So I switched to having VS make the path for me
Data Source=".App_Dataasdf.sdf"
and got
The path is not valid. Check the directory for the database. [ Path = .App_DataPDQ_Test_CE.sdf
Is there ANYWAY to use a relative path for the SQL Compact Connection string?
Im using SQL Compact 3.1 and have created a desktop application
Can anybody tell me if there are any reporting fearures compatable
Here's the Active X Script I'm trying to migrate into an SSIS task:
Function Main()
Dim Engine, dbPath
dbPath = "c:path..."
Set Engine = CreateObject("JRO.JetEngine")
Main = DTSTaskExecResult_Success
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath &
"database_UnCompress.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath &
"testdatabase.mdb"
End Function
I have problems in migrating. Here are the questions:
1. Is the ShrinkDatabase task the SSIS equivalent of this script? If so, I'm having problems creating a new connection since it is an mdb file stored on local computer and not on a server. Is there a workaround on this?
2. If I have to use the script task, I use this codes:
Set Engine = CreateObject("DAO.DBEngine.35")
Engine.CompactDatabase "C:.path...database_UnCompress.mdb", "C:path... estdatabase.mdb"
or
Set Engine = CreateObject("JRO.JetEngine")
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pathdatabase.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pathcompacted_database.mdb"
Both produce the same error: Option Strict On disallows late binding. Any suggestions?
3. I also tried to use the Execute SQL Task, using dbcc shrinkdatabase. But I seem lost again in creating a connection. Should I consider "database_UnCompress.mdb" as flat file source?
Any help deeply appreciated. Thanks.
Hi,
Im working in a project that uses sql server compact. While migrating some data from another database I needed to "Turn off" the identity insert :
SET IDENTITY_INSERT <tablename> OFF
and set it back to "ON" after the migration was completed. Unfortunately SQL Server Compact is giving me the following error:
There was an error parsing the query. [ Token line number = 1,Token line offset = 5,Token in error = @@IDENTITY_INSERT ]
Im i doing something wrong or this instruction is not supported by the Compact edition.
Does anybody knows another way to do this?
Thank you
Alexander75
Hi everyone,
We are replicating SQL Server Compact and SQL Server 2008. My understanding is that SQL Server 2008 can only replicate with Compact 3.5 SP1, not Compact 3.5.
I have not been able to locate a download fromMicrosoft for SQL Compact 3.5 SP1. The download that is available is for Compact 3.5 (without the SP1).
Does anyone know where I can get the SP1 download?
Thanks
I am writing an application that is designed for the desktop, or small group (3 or less) desktops. The application is being deployed using the Cassini web server as a complete "embedded" solution. I wanted to use SQL Server Compact because of its small footprint and because of the synchronization capabilities with SQLServer. Also, it is very easy to deploy vs. SQL Server Express. The idea behind this APP is that someone might use it locally and then decide to subscribe to the service and then connect the same application to a web services application.
That being said, and with knowledge of Microsoft's stance on SQL Compact under ASP.NET, I have most everything working and it works great except for one thing. When I add a SQLDataSource to the page and set the connection string to my file and the DataProvider to System.Data.SqlServerCe and then bind a data grid to the SQLDataSource, it runs fine on my development machine, but when I deploy to my clean test machine, I get an error when the page loads saying:
Unable to find the requested .Net Framework Data Provider. It may not be installed.
I can create a dataset in code (in fact,in several other places I do) and it works fine. It is just the SQLDataSource(s) that are throwing this error. I have a copy of System.Data.SqlServerCe.dll in the bin folder and I have all of the compact DLL's in the bin folder as well. I can't figure out what I need to do. I also have a reference to the System.Data.SqlServerCe in the web.config file.
Can anyone help?
Hi,
We have just installed visual studio 2008 for a windows mobile application development. This application requires a database also. VS 2008 installs sql server 2005 express edition and the sql server compact edition 3.5. We installed sql server management studio but this is for the express edition only. Please let us know how to use a similar tool for compact edition, we could not find any suitable for the same.
Regards
you can, with a dataset do something like myDataSet.WriteXml(m_XmlFile, XmlWriteMode.IgnoreSchema) and get XML for a record....
is it possible to do something like that with SQLserverCeresultset?
i know i could probably loop thru the columns and create the xml manually... but using somethinglike writexml will give better performance?
right now im not using anything from the XML class... from my previous exp with using XML classes on the Compact framework gives slow performance...
I'm an Access man moving towards SQL Server and I'm interested in knowing if there is a utility for my SQL Server version 7 database that is similar to Access' Compact and Repair utility? I'd like a recommendation from a guru on what Prevenatative Maintenance measures I should be running daily/weekly/monthly etc...
Thanks in advance.
dog
I want to use function "Compact and repair database" in SQL Server2000 which like function Access 2003.
How can i do ?
Thank you very much.