How should I store an image into my SQL database in order to be able to read
it using Reporting Services Image control? I have a table with an image
field (datatype of the field is 'Image').
I have tried several ways and the report is always rendered with the image
showing a red cross inside it (image broken). However if I read the table
using MS Access ADP and double click on the image field, MS Paint opens and
shows up the image (this is a bmp image). I have also set the MIMEType of
the rs image field to 'image/bmp' (filling this field this is compulsory
with database images).
It seems that, even though the database field contents ('Image' field) are
properly stored, Reporting Services does not know how to handle it. May I
need to do a CONVERT(Binary, MyImageField) or the database field be of
another type? How should I store the image inside the database so that RS
could read it?
Regards.
PS: This is RS SP2.This sounds like the images are stored as OLE images in the database (e.g.
Access would convert images into OLE images). You can try the following
expression to get rid of the OLE chunk:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
> How should I store an image into my SQL database in order to be able to
> read it using Reporting Services Image control? I have a table with an
> image field (datatype of the field is 'Image').
> I have tried several ways and the report is always rendered with the image
> showing a red cross inside it (image broken). However if I read the table
> using MS Access ADP and double click on the image field, MS Paint opens
> and shows up the image (this is a bmp image). I have also set the MIMEType
> of the rs image field to 'image/bmp' (filling this field this is
> compulsory with database images).
> It seems that, even though the database field contents ('Image' field) are
> properly stored, Reporting Services does not know how to handle it. May I
> need to do a CONVERT(Binary, MyImageField) or the database field be of
> another type? How should I store the image inside the database so that RS
> could read it?
> Regards.
> PS: This is RS SP2.
>|||Thanks for your reply but it seems that there should be another reason. Your
explanation is on the good road, but
I have tried your expression, with that 105 varying from 100 to 110 with the
same results. The red cross is still there.
Any other suggestion? Regards.
PS: Just for further debugging, I have an output of the first 950 bytes
generated by the expression:
=System.Convert.ToBase64String(Fields!Picture.Value)
Here they follow:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
> This sounds like the images are stored as OLE images in the database (e.g.
> Access would convert images into OLE images). You can try the following
> expression to get rid of the OLE chunk:
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
> 105))
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read the
>> table using MS Access ADP and double click on the image field, MS Paint
>> opens and shows up the image (this is a bmp image). I have also set the
>> MIMEType of the rs image field to 'image/bmp' (filling this field this is
>> compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field be
>> of another type? How should I store the image inside the database so that
>> RS could read it?
>> Regards.
>> PS: This is RS SP2.
>|||More information on the subject:
I have been doing more tests with this sample bitmap. I have sent it to
myself via email just to read the source code of the message and extract the
base64 of the bitmap. The header of attachment and some hundreds the
begining bytes are here:
--_=_NextPart_001_01C55532.6E34D77F
Content-Type: image/bmp;
name="PHOTO.BMP"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="PHOTO.BMP"
Qk1gSAAAAAAAAHYAAAAoAAAAzwAAALEAAAABAAQAAAAAAAAAAAAQFwAAEBcAAAAAAAAAAAAAAAAA
AP///wD6+voA8/PzAOfn5wDb29sAzMzMALm5uQCjo6MAi4uLAHNzcwBZWVkAQkJCAC8vLwAfHx8A
Dw8PABERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERARERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
etc...
If I copy this base64 chunk of data and set the Reporting Services image
field to:
=System.Convert.FromBase64String("the chunk")
The image is shown perfecly on the report.
However, if I compare some bytes of the begining of this chunk (which is
shown to be correct) and try to find it somewhere on the string returned by:
=System.Convert.ToBase64String(Fields!Picture.Value)
which is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
I can't find it anywhere. There is no 'Qk1gS' substring in it. Now my
question are: Are there multiple ways of converting a file into a base64
string? How can I remove the OLE header of the database if I cannot find the
matching of the image REAL data using this 'comparing' approach?
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>|||Instead of doing tests with the final image, I have created a 5x5 pix bitmap
to work/test with.
The Base64 encoding of it (grabbed from an email sourcecode is):
Qk2GAAAAAAAAADYAAAAoAAAABQAAAAUAAAABABgAAAAAAFAAAAAAAAAAAAAAAAAAAAAAAAAA////////////////////AP///wAA/////////wAA/wD///////////////////8A////////AAD/////AAD/AAAA/////////////////wA=
It works perfectly if I set the value property of the rs image to:
=System.Convert.FromBase64String("the former base64 string")
Now, the database version of the same file: I set a texbox in the RS report
and set its value to =System.Convert.ToBase64String(Fields!Picture.Value) so
that I could retrieve the complete base64 of the image stored in the
database. Here it is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAoAAAAEJNhgAAAAAAAAA2AAAAKAAAAAUAAAAFAAAAAQAYAAAAAABQAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////////wD///8AAP////////8AAP8A////////////////////AP///////wAA/////wAA/wAAAP////////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBQAAAAAAAIqtBf4=
By other means, I have been able to revert this base64 string back to binary
and here are the results:
9 + ÿÿÿÿImagen de mapa de bits Paint.Picture PBrush
BM? 6 ( P ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿ
ÿÿÿÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿ ÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿ
S þ
Of course, I have some rubbish here. But not everything is lost: Now I can
see that a localized language string is included in the OLE chunk before the
'BM' indicating the begining of the bitmap file. And now the final question:
Should I use other string than
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
when the locale of the systems/servers/software is not english? In my case
I'm using spanish and since the string 'Imagen de mapa de bits' is not as
long as 'Bitmap image file', I think that 105 is not valid/correct when
other languages are used.
After some calculations I think that the OLE chunk size for spanish (which
is my case) is 121 instead of 105. However the red cross is still there!!!!!
:(((
Another aproach, instead of letting RS cropping the image ole data, is to
retrieve the image from the database without it. Is it feasible to retrieve
just a 'substring' of the image?
Please help. I really need it. Thaks.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>
Showing posts with label control. Show all posts
Showing posts with label control. Show all posts
Thursday, March 29, 2012
Friday, February 24, 2012
Database design Help
hi
Actually i have been assigned a project in Sql server 2000 with front end in vb 6, and its an inventory control system of a computer retail company. i dont know how to make its design. it must show inventory items and new purchases and when they are made they must update the inventory and when the items in the inventory are taken to prepare orders recieved from the customers the inventory must be updated. it must also show customers back orders, daily expenditurers, monthly expenditures, staff handling the inventory, different brands ND JOURNAL AND LEDGER Accounts, journal accounts show daily expenses of a month and ledger shows monthly expenses and it must also show yearly expenses and net profit and the transactions made to the company accounts by the customers and when items are bought from the supplier it must automatically update the company's account when the comapny makes payment to the supplier.
Thanks.........Hi riz
It sounds like you have a brief to build a system from the ground up and you so far have got out a blank piece of paper and perhaps a pencil.
This is far too broad a question for a forum - this is more like the very first steps towards an invitation to tender.
Have you been assigned this in house or is this an academic exercise for a hypothetical retail company?
Actually i have been assigned a project in Sql server 2000 with front end in vb 6, and its an inventory control system of a computer retail company. i dont know how to make its design. it must show inventory items and new purchases and when they are made they must update the inventory and when the items in the inventory are taken to prepare orders recieved from the customers the inventory must be updated. it must also show customers back orders, daily expenditurers, monthly expenditures, staff handling the inventory, different brands ND JOURNAL AND LEDGER Accounts, journal accounts show daily expenses of a month and ledger shows monthly expenses and it must also show yearly expenses and net profit and the transactions made to the company accounts by the customers and when items are bought from the supplier it must automatically update the company's account when the comapny makes payment to the supplier.
Thanks.........Hi riz
It sounds like you have a brief to build a system from the ground up and you so far have got out a blank piece of paper and perhaps a pencil.
This is far too broad a question for a forum - this is more like the very first steps towards an invitation to tender.
Have you been assigned this in house or is this an academic exercise for a hypothetical retail company?
Tuesday, February 14, 2012
Database corrupt on power loss
We are planning to use SQL Compact for an industrial control application.
We selected this database based on the assuption that it will survive when power is lost in the middle of a transaction.
We setup a test where we open a connection, write to database and randomly shut of the power while writing to database.
We are running on XPE with EWF enabled for C drive. Database is on D drive.
We cycle the power every ~ 20 seconds.
When the power comes back on, we Verify the database and if return is FALSE we Repair.
The database gets corrupted after 15 to 24 hours.
Looking at the log, for the first 12 hours there is no Repair going on. Every time the Verify returns True. After that, we start seeing Repair going on. The messages in the log are one or more of the following:
"Page with valid ordinal not found - idPage: 1, iPage: 3151"
"Block page not found - pwszTable: EventLogTable, idPage: 4678"
"Selected page not found - idPage: 4951, iPage: 3935"
After a couple more hours of repairing , the database corrupts completely and our application does not start anymore. The database file is always 20K when it gets corrupted.
Is there any setup to be done for the OS or the SQL to be able to survive this kind of test?
Is there anything to do to the database to prevent the corruption?
First of all, are you using transactions to submit your changes?
If yes, did you check file system to see if it got corrupted? Which file system is that? Do you have write cache disabled on HDD?
|||Ilya,
Thanks for the reply!
We use the database as follows:
We open a connection using SqlCeConnection.Open()
We prepare a command using SqlCeCommand.Prepare()
We write to database using SqlCeCommand.ExecuteNonQuery()
We keep same connection open all the time while we are writing.
At this time we don't read from database, we just write.
The operating system is XP Embedded. The file system is NTFS.
I am not sure if there is any corruption in the file system but I assume it is not since I just delete the bad database and restart the test and everything goes back to normal. How would I check to see if there is any file system corruption?
I will check your suggestion about disabling HDD write cache. I doubt that it is disabled.
|||Ilya,
I disabled the cache write for the drive.
I will restart test and hopefuly this is the solution.
Thanks again!
|||Hold on, not just yet… You have to use transactions for all changes to the database to engage ACID and it looks like you’re not using them. Please see this on how to use transactions.
|||
Thanks.
We will change the code to use transactions.
|||Ilya,
We changed the code to use transaction and the database still fails.
Also, the write cache for the hdd is disabled.
This time it failed after 10 hours.
The behaviour is the same as before, the only difference is that now I did not get a lot of pages not found before the program stopped working.
This is the last entry in the database log before it crashed:
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : n/a
Verifying - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:19.821
Selected page not found - idPage: 1, iPage: 3007
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : C:\DOCUME~1\husky\LOCALS~1\Temp\sql1.tmp
Repairing - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:20.190
Selected page not found - idPage: 1, iPage: 3007
Here is the code where we are accessing the database:
#region Database Access
private void InitializeDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Determine if database file exists
bool databaseExists = File.Exists(_DatabaseFilePath);
if (_CreateEmptyDatabase || !databaseExists)
{
// Remove old database file if we are forcing creating of an empty database
if (databaseExists)
{
File.Delete(_DatabaseFilePath);
}
// Create an empty database if it does not exist yet
CreateEmptyDatabase();
OpenConnection();
CreateTables();
}
else
{
VerifyAndRepairDatabase();
OpenConnection();
}
PrepareInsertEventCommand();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void VerifyAndRepairDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " VerifyAndRepairDatabase Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
LogFile.Log("Event Log Database", "Verified Started");
if (sqlCeEngine.Verify())
{
stopwatch.Stop();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify Completed in " + stopwatch.ElapsedMilliseconds + " msec");
LogFile.Log("Event Log Database", "Verified OK");
}
else
{
LogFile.Log("Event Log Database", "Verified Failed");
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Corrupted --> Repair in Progress");
sqlCeEngine.Repair(null, RepairOption.DeleteCorruptedRows);
stopwatch.Stop();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify and Repair Completed in " + stopwatch.ElapsedMilliseconds + " msec");
LogFile.Log("Event Log Database", "Repair Complete");
}
}
}
private void CreateEmptyDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
sqlCeEngine.CreateDatabase();
}
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void OpenConnection()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
_Connection = new SqlCeConnection(_ConnectionString);
_Connection.Open();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void CreateTables()
{
const string sqlCreateTableCommand =
@."CREATE TABLE " + TableName + " " +
@."(" +
@."[TimeStamp] bigint IDENTITY(1,1) PRIMARY KEY," +
@."[Name] nvarchar(255)," +
@."[DateTime] datetime," +
@."[EventType] integer," +
@."[SourceText] nvarchar(255)," +
@."[MessageText] nvarchar(255)" +
@.");";
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeCommand sqlCommand = new SqlCeCommand(sqlCreateTableCommand, _Connection))
{
sqlCommand.ExecuteNonQuery();
}
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void PrepareInsertEventCommand()
{
const string sqlInsertCommand =
@."INSERT INTO " + TableName + @." " +
@."([Name], [DateTime], [EventType], [SourceText], [MessageText]) " +
@."VALUES (@.Name, @.DateTime, @.EventType, @.SourceText, @.MessageText)";
_InsertEventCommand = _Connection.CreateCommand();
_InsertEventCommand.CommandText = sqlInsertCommand;
_InsertEventCommand.Parameters.Add("@.Name", SqlDbType.NVarChar, 255);
_InsertEventCommand.Parameters.Add("@.DateTime", SqlDbType.DateTime, 8);
_InsertEventCommand.Parameters.Add("@.EventType", SqlDbType.Int);
_InsertEventCommand.Parameters.Add("@.SourceText", SqlDbType.NVarChar, 255);
_InsertEventCommand.Parameters.Add("@.MessageText", SqlDbType.NVarChar, 255);
_InsertEventCommand.Prepare();
}
public void UpdateEventHistory(Event machineEvent)
{
// Start transaction
SqlCeTransaction transaction = _Connection.BeginTransaction();
_InsertEventCommand.Transaction = transaction;
try
{
// Update command parameters and write values to database
_InsertEventCommand.Parameters[0].Value = machineEvent.Name;
_InsertEventCommand.Parameters[1].Value = machineEvent.TimeStamp;
_InsertEventCommand.Parameters[2].Value = machineEvent.EventType;
_InsertEventCommand.Parameters[3].Value = machineEvent.SourceText;
_InsertEventCommand.Parameters[4].Value = machineEvent.MessageText;
_InsertEventCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception exception)
{
transaction.Rollback();
_Connection.Close();
throw exception;
}
}
#endregion
Thanks.
|||Maybe you should try changing the CommitMode as per http://msdn2.microsoft.com/en-us/library/esdw1h9d.aspx
(default CommitMode is deferred)
// Commit the changes to disk if everything above succeeded;
// Use Deferred mode for optimal performance; the changes will
// be flashed (sic!) to disk within the timespan specified in the
// ConnectionString 'FLUSH INTERVAL' property; ( = 10 seconds)
//
tx.Commit(CommitMode.Deferred);
// Alternatively, you could use:
// tx.Commit(CommitMode.Immediate);
|||
Have you looked at this: http://support.microsoft.com/kb/296076/en-us ?
|||
Erik,
I changed the code to use commit immediate and the test still fails after about 12 hours.
In my case the database is completely corrupt, since it has only 20k. I think trying to compact will do nothing.
After reading the article you pointed to, looks like I cannot use this database for this kind of application.
I might try just another test by limiting the size. Right now I keep writing to the database at a rate of about 2Mb per hour.
This means my database is about 24Mb when it becomes corrupted. I will try to copy the file and start over when I reach a certain size.
Any other suggestion other than 'don't reset during a transaction'?
Thanks.
|||What is the behavior of the SQL Express database when you power down during a transaction commit? Does SQL Express have the same possible issue of getting the database corrupt as the Compact does?
|||That likely indicates file system corruption- your file is just plain gone. Consider running chkdsk.exe before running database fix. That might restore the file to pre commit state.
|||Hi, Ilya;
I have database corruption problem as well.
I found a very strange issue: my application will write some log to a log file "log.txt", but I found a corrupted .sdf database file has my log information with plain text !!!
How can I tell there is file system corruption? How can this happen? How can I find chkdsk.exe?
Cheers.
|||Is this corruption problem still valid with 3.5 version ?Arnaud.
Database corrupt on power loss
We are planning to use SQL Compact for an industrial control application.
We selected this database based on the assuption that it will survive when power is lost in the middle of a transaction.
We setup a test where we open a connection, write to database and randomly shut of the power while writing to database.
We are running on XPE with EWF enabled for C drive. Database is on D drive.
We cycle the power every ~ 20 seconds.
When the power comes back on, we Verify the database and if return is FALSE we Repair.
The database gets corrupted after 15 to 24 hours.
Looking at the log, for the first 12 hours there is no Repair going on. Every time the Verify returns True. After that, we start seeing Repair going on. The messages in the log are one or more of the following:
"Page with valid ordinal not found - idPage: 1, iPage: 3151"
"Block page not found - pwszTable: EventLogTable, idPage: 4678"
"Selected page not found - idPage: 4951, iPage: 3935"
After a couple more hours of repairing , the database corrupts completely and our application does not start anymore. The database file is always 20K when it gets corrupted.
Is there any setup to be done for the OS or the SQL to be able to survive this kind of test?
Is there anything to do to the database to prevent the corruption?
First of all, are you using transactions to submit your changes?
If yes, did you check file system to see if it got corrupted? Which file system is that? Do you have write cache disabled on HDD?
|||Ilya,
Thanks for the reply!
We use the database as follows:
We open a connection using SqlCeConnection.Open()
We prepare a command using SqlCeCommand.Prepare()
We write to database using SqlCeCommand.ExecuteNonQuery()
We keep same connection open all the time while we are writing.
At this time we don't read from database, we just write.
The operating system is XP Embedded. The file system is NTFS.
I am not sure if there is any corruption in the file system but I assume it is not since I just delete the bad database and restart the test and everything goes back to normal. How would I check to see if there is any file system corruption?
I will check your suggestion about disabling HDD write cache. I doubt that it is disabled.
|||Ilya,
I disabled the cache write for the drive.
I will restart test and hopefuly this is the solution.
Thanks again!
|||Hold on, not just yet… You have to use transactions for all changes to the database to engage ACID and it looks like you’re not using them. Please see this on how to use transactions.
|||
Thanks.
We will change the code to use transactions.
|||Ilya,
We changed the code to use transaction and the database still fails.
Also, the write cache for the hdd is disabled.
This time it failed after 10 hours.
The behaviour is the same as before, the only difference is that now I did not get a lot of pages not found before the program stopped working.
This is the last entry in the database log before it crashed:
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : n/a
Verifying - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:19.821
Selected page not found - idPage: 1, iPage: 3007
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : C:\DOCUME~1\husky\LOCALS~1\Temp\sql1.tmp
Repairing - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:20.190
Selected page not found - idPage: 1, iPage: 3007
Here is the code where we are accessing the database:
#region Database Access
private void InitializeDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Determine if database file exists
bool databaseExists = File.Exists(_DatabaseFilePath);
if (_CreateEmptyDatabase || !databaseExists)
{
// Remove old database file if we are forcing creating of an empty database
if (databaseExists)
{
File.Delete(_DatabaseFilePath);
}
// Create an empty database if it does not exist yet
CreateEmptyDatabase();
OpenConnection();
CreateTables();
}
else
{
VerifyAndRepairDatabase();
OpenConnection();
}
PrepareInsertEventCommand();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void VerifyAndRepairDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " VerifyAndRepairDatabase Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
LogFile.Log("Event Log Database", "Verified Started");
if (sqlCeEngine.Verify())
{
stopwatch.Stop();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify Completed in " + stopwatch.ElapsedMilliseconds + " msec");
LogFile.Log("Event Log Database", "Verified OK");
}
else
{
LogFile.Log("Event Log Database", "Verified Failed");
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Corrupted --> Repair in Progress");
sqlCeEngine.Repair(null, RepairOption.DeleteCorruptedRows);
stopwatch.Stop();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify and Repair Completed in " + stopwatch.ElapsedMilliseconds + " msec");
LogFile.Log("Event Log Database", "Repair Complete");
}
}
}
private void CreateEmptyDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
sqlCeEngine.CreateDatabase();
}
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void OpenConnection()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
_Connection = new SqlCeConnection(_ConnectionString);
_Connection.Open();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void CreateTables()
{
const string sqlCreateTableCommand =
@."CREATE TABLE " + TableName + " " +
@."(" +
@."[TimeStamp] bigint IDENTITY(1,1) PRIMARY KEY," +
@."[Name] nvarchar(255)," +
@."[DateTime] datetime," +
@."[EventType] integer," +
@."[SourceText] nvarchar(255)," +
@."[MessageText] nvarchar(255)" +
@.");";
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeCommand sqlCommand = new SqlCeCommand(sqlCreateTableCommand, _Connection))
{
sqlCommand.ExecuteNonQuery();
}
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void PrepareInsertEventCommand()
{
const string sqlInsertCommand =
@."INSERT INTO " + TableName + @." " +
@."([Name], [DateTime], [EventType], [SourceText], [MessageText]) " +
@."VALUES (@.Name, @.DateTime, @.EventType, @.SourceText, @.MessageText)";
_InsertEventCommand = _Connection.CreateCommand();
_InsertEventCommand.CommandText = sqlInsertCommand;
_InsertEventCommand.Parameters.Add("@.Name", SqlDbType.NVarChar, 255);
_InsertEventCommand.Parameters.Add("@.DateTime", SqlDbType.DateTime, 8);
_InsertEventCommand.Parameters.Add("@.EventType", SqlDbType.Int);
_InsertEventCommand.Parameters.Add("@.SourceText", SqlDbType.NVarChar, 255);
_InsertEventCommand.Parameters.Add("@.MessageText", SqlDbType.NVarChar, 255);
_InsertEventCommand.Prepare();
}
public void UpdateEventHistory(Event machineEvent)
{
// Start transaction
SqlCeTransaction transaction = _Connection.BeginTransaction();
_InsertEventCommand.Transaction = transaction;
try
{
// Update command parameters and write values to database
_InsertEventCommand.Parameters[0].Value = machineEvent.Name;
_InsertEventCommand.Parameters[1].Value = machineEvent.TimeStamp;
_InsertEventCommand.Parameters[2].Value = machineEvent.EventType;
_InsertEventCommand.Parameters[3].Value = machineEvent.SourceText;
_InsertEventCommand.Parameters[4].Value = machineEvent.MessageText;
_InsertEventCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception exception)
{
transaction.Rollback();
_Connection.Close();
throw exception;
}
}
#endregion
Thanks.
|||Maybe you should try changing the CommitMode as per http://msdn2.microsoft.com/en-us/library/esdw1h9d.aspx
(default CommitMode is deferred)
// Commit the changes to disk if everything above succeeded;
// Use Deferred mode for optimal performance; the changes will
// be flashed (sic!) to disk within the timespan specified in the
// ConnectionString 'FLUSH INTERVAL' property; ( = 10 seconds)
//
tx.Commit(CommitMode.Deferred);
// Alternatively, you could use:
// tx.Commit(CommitMode.Immediate);
Have you looked at this: http://support.microsoft.com/kb/296076/en-us ?
|||Erik,
I changed the code to use commit immediate and the test still fails after about 12 hours.
In my case the database is completely corrupt, since it has only 20k. I think trying to compact will do nothing.
After reading the article you pointed to, looks like I cannot use this database for this kind of application.
I might try just another test by limiting the size. Right now I keep writing to the database at a rate of about 2Mb per hour.
This means my database is about 24Mb when it becomes corrupted. I will try to copy the file and start over when I reach a certain size.
Any other suggestion other than 'don't reset during a transaction'?
Thanks.
|||What is the behavior of the SQL Express database when you power down during a transaction commit? Does SQL Express have the same possible issue of getting the database corrupt as the Compact does?
|||That likely indicates file system corruption- your file is just plain gone. Consider running chkdsk.exe before running database fix. That might restore the file to pre commit state.
|||Hi, Ilya;
I have database corruption problem as well.
I found a very strange issue: my application will write some log to a log file "log.txt", but I found a corrupted .sdf database file has my log information with plain text !!!
How can I tell there is file system corruption? How can this happen? How can I find chkdsk.exe?
Cheers.
|||Is this corruption problem still valid with 3.5 version ?Arnaud.
Database corrupt on power loss
We are planning to use SQL Compact for an industrial control application.
We selected this database based on the assuption that it will survive when power is lost in the middle of a transaction.
We setup a test where we open a connection, write to database and randomly shut of the power while writing to database.
We are running on XPE with EWF enabled for C drive. Database is on D drive.
We cycle the power every ~ 20 seconds.
When the power comes back on, we Verify the database and if return is FALSE we Repair.
The database gets corrupted after 15 to 24 hours.
Looking at the log, for the first 12 hours there is no Repair going on. Every time the Verify returns True. After that, we start seeing Repair going on. The messages in the log are one or more of the following:
"Page with valid ordinal not found - idPage: 1, iPage: 3151"
"Block page not found - pwszTable: EventLogTable, idPage: 4678"
"Selected page not found - idPage: 4951, iPage: 3935"
After a couple more hours of repairing , the database corrupts completely and our application does not start anymore. The database file is always 20K when it gets corrupted.
Is there any setup to be done for the OS or the SQL to be able to survive this kind of test?
Is there anything to do to the database to prevent the corruption?
First of all, are you using transactions to submit your changes?
If yes, did you check file system to see if it got corrupted? Which file system is that? Do you have write cache disabled on HDD?
|||Ilya,
Thanks for the reply!
We use the database as follows:
We open a connection using SqlCeConnection.Open()
We prepare a command using SqlCeCommand.Prepare()
We write to database using SqlCeCommand.ExecuteNonQuery()
We keep same connection open all the time while we are writing.
At this time we don't read from database, we just write.
The operating system is XP Embedded. The file system is NTFS.
I am not sure if there is any corruption in the file system but I assume it is not since I just delete the bad database and restart the test and everything goes back to normal. How would I check to see if there is any file system corruption?
I will check your suggestion about disabling HDD write cache. I doubt that it is disabled.
|||Ilya,
I disabled the cache write for the drive.
I will restart test and hopefuly this is the solution.
Thanks again!
|||Hold on, not just yet… You have to use transactions for all changes to the database to engage ACID and it looks like you’re not using them. Please see this on how to use transactions.
|||
Thanks.
We will change the code to use transactions.
|||Ilya,
We changed the code to use transaction and the database still fails.
Also, the write cache for the hdd is disabled.
This time it failed after 10 hours.
The behaviour is the same as before, the only difference is that now I did not get a lot of pages not found before the program stopped working.
This is the last entry in the database log before it crashed:
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : n/a
Verifying - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:19.821
Selected page not found - idPage: 1, iPage: 3007
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : C:\DOCUME~1\husky\LOCALS~1\Temp\sql1.tmp
Repairing - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:20.190
Selected page not found - idPage: 1, iPage: 3007
Here is the code where we are accessing the database:
#region Database Access
private void InitializeDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Determine if database file exists
bool databaseExists = File.Exists(_DatabaseFilePath);
if (_CreateEmptyDatabase || !databaseExists)
{
// Remove old database file if we are forcing creating of an empty database
if (databaseExists)
{
File.Delete(_DatabaseFilePath);
}
// Create an empty database if it does not exist yet
CreateEmptyDatabase();
OpenConnection();
CreateTables();
}
else
{
VerifyAndRepairDatabase();
OpenConnection();
}
PrepareInsertEventCommand();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void VerifyAndRepairDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " VerifyAndRepairDatabase Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
LogFile.Log("Event Log Database", "Verified Started");
if (sqlCeEngine.Verify())
{
stopwatch.Stop();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify Completed in " + stopwatch.ElapsedMilliseconds + " msec");
LogFile.Log("Event Log Database", "Verified OK");
}
else
{
LogFile.Log("Event Log Database", "Verified Failed");
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Corrupted --> Repair in Progress");
sqlCeEngine.Repair(null, RepairOption.DeleteCorruptedRows);
stopwatch.Stop();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify and Repair Completed in " + stopwatch.ElapsedMilliseconds + " msec");
LogFile.Log("Event Log Database", "Repair Complete");
}
}
}
private void CreateEmptyDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
sqlCeEngine.CreateDatabase();
}
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void OpenConnection()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
_Connection = new SqlCeConnection(_ConnectionString);
_Connection.Open();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void CreateTables()
{
const string sqlCreateTableCommand =
@."CREATE TABLE " + TableName + " " +
@."(" +
@."[TimeStamp] bigint IDENTITY(1,1) PRIMARY KEY," +
@."[Name] nvarchar(255)," +
@."[DateTime] datetime," +
@."[EventType] integer," +
@."[SourceText] nvarchar(255)," +
@."[MessageText] nvarchar(255)" +
@.");";
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeCommand sqlCommand = new SqlCeCommand(sqlCreateTableCommand, _Connection))
{
sqlCommand.ExecuteNonQuery();
}
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void PrepareInsertEventCommand()
{
const string sqlInsertCommand =
@."INSERT INTO " + TableName + @." " +
@."([Name], [DateTime], [EventType], [SourceText], [MessageText]) " +
@."VALUES (@.Name, @.DateTime, @.EventType, @.SourceText, @.MessageText)";
_InsertEventCommand = _Connection.CreateCommand();
_InsertEventCommand.CommandText = sqlInsertCommand;
_InsertEventCommand.Parameters.Add("@.Name", SqlDbType.NVarChar, 255);
_InsertEventCommand.Parameters.Add("@.DateTime", SqlDbType.DateTime, 8);
_InsertEventCommand.Parameters.Add("@.EventType", SqlDbType.Int);
_InsertEventCommand.Parameters.Add("@.SourceText", SqlDbType.NVarChar, 255);
_InsertEventCommand.Parameters.Add("@.MessageText", SqlDbType.NVarChar, 255);
_InsertEventCommand.Prepare();
}
public void UpdateEventHistory(Event machineEvent)
{
// Start transaction
SqlCeTransaction transaction = _Connection.BeginTransaction();
_InsertEventCommand.Transaction = transaction;
try
{
// Update command parameters and write values to database
_InsertEventCommand.Parameters[0].Value = machineEvent.Name;
_InsertEventCommand.Parameters[1].Value = machineEvent.TimeStamp;
_InsertEventCommand.Parameters[2].Value = machineEvent.EventType;
_InsertEventCommand.Parameters[3].Value = machineEvent.SourceText;
_InsertEventCommand.Parameters[4].Value = machineEvent.MessageText;
_InsertEventCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception exception)
{
transaction.Rollback();
_Connection.Close();
throw exception;
}
}
#endregion
Thanks.
|||Maybe you should try changing the CommitMode as per http://msdn2.microsoft.com/en-us/library/esdw1h9d.aspx
(default CommitMode is deferred)
// Commit the changes to disk if everything above succeeded;
// Use Deferred mode for optimal performance; the changes will
// be flashed (sic!) to disk within the timespan specified in the
// ConnectionString 'FLUSH INTERVAL' property; ( = 10 seconds)
//
tx.Commit(CommitMode.Deferred);
// Alternatively, you could use:
// tx.Commit(CommitMode.Immediate);
Have you looked at this: http://support.microsoft.com/kb/296076/en-us ?
|||Erik,
I changed the code to use commit immediate and the test still fails after about 12 hours.
In my case the database is completely corrupt, since it has only 20k. I think trying to compact will do nothing.
After reading the article you pointed to, looks like I cannot use this database for this kind of application.
I might try just another test by limiting the size. Right now I keep writing to the database at a rate of about 2Mb per hour.
This means my database is about 24Mb when it becomes corrupted. I will try to copy the file and start over when I reach a certain size.
Any other suggestion other than 'don't reset during a transaction'?
Thanks.
|||What is the behavior of the SQL Express database when you power down during a transaction commit? Does SQL Express have the same possible issue of getting the database corrupt as the Compact does?
|||That likely indicates file system corruption- your file is just plain gone. Consider running chkdsk.exe before running database fix. That might restore the file to pre commit state.
|||Hi, Ilya;
I have database corruption problem as well.
I found a very strange issue: my application will write some log to a log file "log.txt", but I found a corrupted .sdf database file has my log information with plain text !!!
How can I tell there is file system corruption? How can this happen? How can I find chkdsk.exe?
Cheers.
|||Is this corruption problem still valid with 3.5 version ?Arnaud.
|||Sqlce is a standalone file . so if the file was corrupt.
It looks like no good way to repaire it .
Database corrupt on power loss
We are planning to use SQL Compact for an industrial control application.
We selected this database based on the assuption that it will survive when power is lost in the middle of a transaction.
We setup a test where we open a connection, write to database and randomly shut of the power while writing to database.
We are running on XPE with EWF enabled for C drive. Database is on D drive.
We cycle the power every ~ 20 seconds.
When the power comes back on, we Verify the database and if return is FALSE we Repair.
The database gets corrupted after 15 to 24 hours.
Looking at the log, for the first 12 hours there is no Repair going on. Every time the Verify returns True. After that, we start seeing Repair going on. The messages in the log are one or more of the following:
"Page with valid ordinal not found - idPage: 1, iPage: 3151"
"Block page not found - pwszTable: EventLogTable, idPage: 4678"
"Selected page not found - idPage: 4951, iPage: 3935"
After a couple more hours of repairing , the database corrupts completely and our application does not start anymore. The database file is always 20K when it gets corrupted.
Is there any setup to be done for the OS or the SQL to be able to survive this kind of test?
Is there anything to do to the database to prevent the corruption?
First of all, are you using transactions to submit your changes?
If yes, did you check file system to see if it got corrupted? Which file system is that? Do you have write cache disabled on HDD?
|||Ilya,
Thanks for the reply!
We use the database as follows:
We open a connection using SqlCeConnection.Open()
We prepare a command using SqlCeCommand.Prepare()
We write to database using SqlCeCommand.ExecuteNonQuery()
We keep same connection open all the time while we are writing.
At this time we don't read from database, we just write.
The operating system is XP Embedded. The file system is NTFS.
I am not sure if there is any corruption in the file system but I assume it is not since I just delete the bad database and restart the test and everything goes back to normal. How would I check to see if there is any file system corruption?
I will check your suggestion about disabling HDD write cache. I doubt that it is disabled.
|||Ilya,
I disabled the cache write for the drive.
I will restart test and hopefuly this is the solution.
Thanks again!
|||Hold on, not just yet… You have to use transactions for all changes to the database to engage ACID and it looks like you’re not using them. Please see this on how to use transactions.
|||
Thanks.
We will change the code to use transactions.
|||Ilya,
We changed the code to use transaction and the database still fails.
Also, the write cache for the hdd is disabled.
This time it failed after 10 hours.
The behaviour is the same as before, the only difference is that now I did not get a lot of pages not found before the program stopped working.
This is the last entry in the database log before it crashed:
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : n/a
Verifying - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:19.821
Selected page not found - idPage: 1, iPage: 3007
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : C:\DOCUME~1\husky\LOCALS~1\Temp\sql1.tmp
Repairing - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:20.190
Selected page not found - idPage: 1, iPage: 3007
Here is the code where we are accessing the database:
#region Database Access
private void InitializeDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Determine if database file exists
bool databaseExists = File.Exists(_DatabaseFilePath);
if (_CreateEmptyDatabase || !databaseExists)
{
// Remove old database file if we are forcing creating of an empty database
if (databaseExists)
{
File.Delete(_DatabaseFilePath);
}
// Create an empty database if it does not exist yet
CreateEmptyDatabase();
OpenConnection();
CreateTables();
}
else
{
VerifyAndRepairDatabase();
OpenConnection();
}
PrepareInsertEventCommand();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void VerifyAndRepairDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " VerifyAndRepairDatabase Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
LogFile.Log("Event Log Database", "Verified Started");
if (sqlCeEngine.Verify())
{
stopwatch.Stop();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify Completed in " + stopwatch.ElapsedMilliseconds + " msec");
LogFile.Log("Event Log Database", "Verified OK");
}
else
{
LogFile.Log("Event Log Database", "Verified Failed");
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Corrupted --> Repair in Progress");
sqlCeEngine.Repair(null, RepairOption.DeleteCorruptedRows);
stopwatch.Stop();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify and Repair Completed in " + stopwatch.ElapsedMilliseconds + " msec");
LogFile.Log("Event Log Database", "Repair Complete");
}
}
}
private void CreateEmptyDatabase()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
sqlCeEngine.CreateDatabase();
}
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void OpenConnection()
{
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
_Connection = new SqlCeConnection(_ConnectionString);
_Connection.Open();
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void CreateTables()
{
const string sqlCreateTableCommand =
@."CREATE TABLE " + TableName + " " +
@."(" +
@."[TimeStamp] bigint IDENTITY(1,1) PRIMARY KEY," +
@."[Name] nvarchar(255)," +
@."[DateTime] datetime," +
@."[EventType] integer," +
@."[SourceText] nvarchar(255)," +
@."[MessageText] nvarchar(255)" +
@.");";
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Started");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlCeCommand sqlCommand = new SqlCeCommand(sqlCreateTableCommand, _Connection))
{
sqlCommand.ExecuteNonQuery();
}
Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Completed in " + stopwatch.ElapsedMilliseconds + " msec");
}
private void PrepareInsertEventCommand()
{
const string sqlInsertCommand =
@."INSERT INTO " + TableName + @." " +
@."([Name], [DateTime], [EventType], [SourceText], [MessageText]) " +
@."VALUES (@.Name, @.DateTime, @.EventType, @.SourceText, @.MessageText)";
_InsertEventCommand = _Connection.CreateCommand();
_InsertEventCommand.CommandText = sqlInsertCommand;
_InsertEventCommand.Parameters.Add("@.Name", SqlDbType.NVarChar, 255);
_InsertEventCommand.Parameters.Add("@.DateTime", SqlDbType.DateTime, 8);
_InsertEventCommand.Parameters.Add("@.EventType", SqlDbType.Int);
_InsertEventCommand.Parameters.Add("@.SourceText", SqlDbType.NVarChar, 255);
_InsertEventCommand.Parameters.Add("@.MessageText", SqlDbType.NVarChar, 255);
_InsertEventCommand.Prepare();
}
public void UpdateEventHistory(Event machineEvent)
{
// Start transaction
SqlCeTransaction transaction = _Connection.BeginTransaction();
_InsertEventCommand.Transaction = transaction;
try
{
// Update command parameters and write values to database
_InsertEventCommand.Parameters[0].Value = machineEvent.Name;
_InsertEventCommand.Parameters[1].Value = machineEvent.TimeStamp;
_InsertEventCommand.Parameters[2].Value = machineEvent.EventType;
_InsertEventCommand.Parameters[3].Value = machineEvent.SourceText;
_InsertEventCommand.Parameters[4].Value = machineEvent.MessageText;
_InsertEventCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception exception)
{
transaction.Rollback();
_Connection.Close();
throw exception;
}
}
#endregion
Thanks.
|||Maybe you should try changing the CommitMode as per http://msdn2.microsoft.com/en-us/library/esdw1h9d.aspx
(default CommitMode is deferred)
// Commit the changes to disk if everything above succeeded;
// Use Deferred mode for optimal performance; the changes will
// be flashed (sic!) to disk within the timespan specified in the
// ConnectionString 'FLUSH INTERVAL' property; ( = 10 seconds)
//
tx.Commit(CommitMode.Deferred);
// Alternatively, you could use:
// tx.Commit(CommitMode.Immediate);
Have you looked at this: http://support.microsoft.com/kb/296076/en-us ?
|||Erik,
I changed the code to use commit immediate and the test still fails after about 12 hours.
In my case the database is completely corrupt, since it has only 20k. I think trying to compact will do nothing.
After reading the article you pointed to, looks like I cannot use this database for this kind of application.
I might try just another test by limiting the size. Right now I keep writing to the database at a rate of about 2Mb per hour.
This means my database is about 24Mb when it becomes corrupted. I will try to copy the file and start over when I reach a certain size.
Any other suggestion other than 'don't reset during a transaction'?
Thanks.
|||What is the behavior of the SQL Express database when you power down during a transaction commit? Does SQL Express have the same possible issue of getting the database corrupt as the Compact does?
|||That likely indicates file system corruption- your file is just plain gone. Consider running chkdsk.exe before running database fix. That might restore the file to pre commit state.
|||Hi, Ilya;
I have database corruption problem as well.
I found a very strange issue: my application will write some log to a log file "log.txt", but I found a corrupted .sdf database file has my log information with plain text !!!
How can I tell there is file system corruption? How can this happen? How can I find chkdsk.exe?
Cheers.
|||Is this corruption problem still valid with 3.5 version ?Arnaud.
|||Sqlce is a standalone file . so if the file was corrupt.
It looks like no good way to repaire it .
Subscribe to:
Posts (Atom)