Database level lock: based

Showing posts with label based. Show all posts

Thursday, March 29, 2012

Database image not displaying

Hi,
In my report I want to dispaly a different image based on the parameter user
enters. For ex., the user enters a company name , then the image for that
company should be displayed.
Now in my database, the images are not stored using the complete path or a
complete URL, but are stored as Logo1.gif , Logo2.gif etc in the field IMAGE.
I have a virtual directory which contains these images.
Now when i create a databse image, an pull from the field IMAGE, the image
is not displayed. how to solve this problem? Is it necessary to install SP1
for this purpose too?
--
pmudYou are actually not using "Database" images here. You are using "External"
images from some webserver. It is just that the image path is dynamically
constructed from a dataset field, right?
In this case, you will need SP1 (or SP2) installed. You would need to set
the image type to "External" and the image value expression to something
like
="http://myserver/vroot/" & Fields!RelativeImagePath.Value
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:6B54439E-15CE-4769-A88A-5A58CCD287E4@.microsoft.com...
> Hi,
> In my report I want to dispaly a different image based on the parameter
> user
> enters. For ex., the user enters a company name , then the image for that
> company should be displayed.
> Now in my database, the images are not stored using the complete path or a
> complete URL, but are stored as Logo1.gif , Logo2.gif etc in the field
> IMAGE.
> I have a virtual directory which contains these images.
> Now when i create a databse image, an pull from the field IMAGE, the
> image
> is not displayed. how to solve this problem? Is it necessary to install
> SP1
> for this purpose too?
> --
> pmud|||HI Robert,
I understand now. I will install SP1 & try to do the external images.
Thanks..
--
pmud
"Robert Bruckner [MSFT]" wrote:
> You are actually not using "Database" images here. You are using "External"
> images from some webserver. It is just that the image path is dynamically
> constructed from a dataset field, right?
> In this case, you will need SP1 (or SP2) installed. You would need to set
> the image type to "External" and the image value expression to something
> like
> ="http://myserver/vroot/" & Fields!RelativeImagePath.Value
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:6B54439E-15CE-4769-A88A-5A58CCD287E4@.microsoft.com...
> > Hi,
> >
> > In my report I want to dispaly a different image based on the parameter
> > user
> > enters. For ex., the user enters a company name , then the image for that
> > company should be displayed.
> >
> > Now in my database, the images are not stored using the complete path or a
> > complete URL, but are stored as Logo1.gif , Logo2.gif etc in the field
> > IMAGE.
> > I have a virtual directory which contains these images.
> >
> > Now when i create a databse image, an pull from the field IMAGE, the
> > image
> > is not displayed. how to solve this problem? Is it necessary to install
> > SP1
> > for this purpose too?
> > --
> > pmud
>
>|||Hi Robert,
I installed SP1 and put the image as an external image. All my images are
stored in C:/Inetpub/wwwroot/IMAGES . this folder has 2 subfolders which have
different images. What value should I use for generating the dynamic image
URL? My server name is WEBSERVER and the image field is =Fields!LOGO.Value.
Thanks
--
pmud
"pmud" wrote:
> HI Robert,
> I understand now. I will install SP1 & try to do the external images.
> Thanks..
> --
> pmud
>
> "Robert Bruckner [MSFT]" wrote:
> > You are actually not using "Database" images here. You are using "External"
> > images from some webserver. It is just that the image path is dynamically
> > constructed from a dataset field, right?
> >
> > In this case, you will need SP1 (or SP2) installed. You would need to set
> > the image type to "External" and the image value expression to something
> > like
> > ="http://myserver/vroot/" & Fields!RelativeImagePath.Value
> >
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > news:6B54439E-15CE-4769-A88A-5A58CCD287E4@.microsoft.com...
> > > Hi,
> > >
> > > In my report I want to dispaly a different image based on the parameter
> > > user
> > > enters. For ex., the user enters a company name , then the image for that
> > > company should be displayed.
> > >
> > > Now in my database, the images are not stored using the complete path or a
> > > complete URL, but are stored as Logo1.gif , Logo2.gif etc in the field
> > > IMAGE.
> > > I have a virtual directory which contains these images.
> > >
> > > Now when i create a databse image, an pull from the field IMAGE, the
> > > image
> > > is not displayed. how to solve this problem? Is it necessary to install
> > > SP1
> > > for this purpose too?
> > > --
> > > pmud
> >
> >
> >|||Assuming you have a virtual root defined in IIS called "Images", the URL
should be similar to this:
http://WebServer/Images/Subfolder/ImageName.gif
Make sure you can access the images through IE, and then just build the same
URL using an expression for the image reportitem value. For instance
="http://WebServer/Images/Subfolder/" & Fields!LOGO.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:CA2277F0-DCEF-479D-BA70-6B9527B50B21@.microsoft.com...
> Hi Robert,
> I installed SP1 and put the image as an external image. All my images are
> stored in C:/Inetpub/wwwroot/IMAGES . this folder has 2 subfolders which
> have
> different images. What value should I use for generating the dynamic image
> URL? My server name is WEBSERVER and the image field is
> =Fields!LOGO.Value.
> Thanks
> --
> pmud
>
> "pmud" wrote:
>> HI Robert,
>> I understand now. I will install SP1 & try to do the external images.
>> Thanks..
>> --
>> pmud
>>
>> "Robert Bruckner [MSFT]" wrote:
>> > You are actually not using "Database" images here. You are using
>> > "External"
>> > images from some webserver. It is just that the image path is
>> > dynamically
>> > constructed from a dataset field, right?
>> >
>> > In this case, you will need SP1 (or SP2) installed. You would need to
>> > set
>> > the image type to "External" and the image value expression to
>> > something
>> > like
>> > ="http://myserver/vroot/" & Fields!RelativeImagePath.Value
>> >
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> >
>> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
>> > news:6B54439E-15CE-4769-A88A-5A58CCD287E4@.microsoft.com...
>> > > Hi,
>> > >
>> > > In my report I want to dispaly a different image based on the
>> > > parameter
>> > > user
>> > > enters. For ex., the user enters a company name , then the image for
>> > > that
>> > > company should be displayed.
>> > >
>> > > Now in my database, the images are not stored using the complete path
>> > > or a
>> > > complete URL, but are stored as Logo1.gif , Logo2.gif etc in the
>> > > field
>> > > IMAGE.
>> > > I have a virtual directory which contains these images.
>> > >
>> > > Now when i create a databse image, an pull from the field IMAGE, the
>> > > image
>> > > is not displayed. how to solve this problem? Is it necessary to
>> > > install
>> > > SP1
>> > > for this purpose too?
>> > > --
>> > > pmud
>> >
>> >
>> >

Monday, March 19, 2012

database encryption

Hi,

i am trying to encrypt my database using dbEncrypt software.The encryption provides role based access ot the database.In that case developer can not call any stored procedure of that database. These two are conflicting things. Can anyone suggest me a way so that i can access the stored procedure through my code by checking the roles from the front end. is it possible??

Please reply soon.

Application Security, INC. The manufacturer of that product, will most likely have specific methods and methodologies for connecting and querying a DB that has been encrypted using their software. I would recomend you ask them how to accomplish this.

http://www.appsecinc.com/support/index.shtml

Saturday, February 25, 2012

Database design question

Hi,
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact of
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> If yes, where they are saved?
> Thanks for you help!
>|||Thanks for the quick response. Can you send me the link to the online book on
Create Triggers topic?
"John Bell" wrote:
> Hi
> The choice of datawarehouse or datamining really need deciding by analysing
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possible
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact of
> the trigger on any oltp activity. You can also get third party applications
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
> > Hi,
> >
> > I am working on a Budgeting web based application using ASP.NET 2 and SQL
> > Server 2000. The budgeting data will build up over a course of period and
> > these historical data will be used for decision making in future budget.
> >
> > My questions:
> > 1. what design approach should I use to store the historical data? Data
> > Mining or Datawarehouse? or other options?
> >
> > 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> > If yes, where they are saved?
> >
> > Thanks for you help!
> >|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
2000 -
http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en
Then search for CREATE TRIGGER...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
>> Hi
>> The choice of datawarehouse or datamining really need deciding by
>> analysing
>> and producing the requirements for the future needs so can't really be
>> answered with the level of information you have given.
>> Although SQL Server 2000 has no automatic method of auditing, it is
>> possible
>> to implement something using triggers and there is a simple example in
>> the
>> CREATE TRIGGER topic in Books Online. I would recommend that you do the
>> mimimum amount of work require in the trigger and do any
>> aggregation/formatting... as a ofline process. This will reduce the
>> impact of
>> the trigger on any oltp activity. You can also get third party
>> applications
>> that implement auditing for you such as Lumigent's auditdb
>> http://www.lumigent.com/products/auditdb.html
>> HTH
>> John
>>
>> "Mindy" wrote:
>> > Hi,
>> >
>> > I am working on a Budgeting web based application using ASP.NET 2 and
>> > SQL
>> > Server 2000. The budgeting data will build up over a course of period
>> > and
>> > these historical data will be used for decision making in future
>> > budget.
>> >
>> > My questions:
>> > 1. what design approach should I use to store the historical data?
>> > Data
>> > Mining or Datawarehouse? or other options?
>> >
>> > 2. Is there any built in tool in sql 2000 to keep track of the audit
>> > trail?
>> > If yes, where they are saved?
>> >
>> > Thanks for you help!
>> >|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver.asp?frame=true
John
"Mindy" wrote:
> Thanks for the quick response. Can you send me the link to the online book on
> Create Triggers topic?
>
> "John Bell" wrote:
> > Hi
> >
> > The choice of datawarehouse or datamining really need deciding by analysing
> > and producing the requirements for the future needs so can't really be
> > answered with the level of information you have given.
> >
> > Although SQL Server 2000 has no automatic method of auditing, it is possible
> > to implement something using triggers and there is a simple example in the
> > CREATE TRIGGER topic in Books Online. I would recommend that you do the
> > mimimum amount of work require in the trigger and do any
> > aggregation/formatting... as a ofline process. This will reduce the impact of
> > the trigger on any oltp activity. You can also get third party applications
> > that implement auditing for you such as Lumigent's auditdb
> > http://www.lumigent.com/products/auditdb.html
> >
> > HTH
> >
> > John
> >
> >
> > "Mindy" wrote:
> >
> > > Hi,
> > >
> > > I am working on a Budgeting web based application using ASP.NET 2 and SQL
> > > Server 2000. The budgeting data will build up over a course of period and
> > > these historical data will be used for decision making in future budget.
> > >
> > > My questions:
> > > 1. what design approach should I use to store the historical data? Data
> > > Mining or Datawarehouse? or other options?
> > >
> > > 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> > > If yes, where they are saved?
> > >
> > > Thanks for you help!
> > >

Friday, February 24, 2012

Database Design Problem

I have to keep detail of detail gender based DB like this where there may be
a number of incidents per state
Create table gender
(
State_id
Male_killed
Male_injured
male_affected
Female_killed
female_affected
female_injured
Child_killed
Child_injured
child_affected
Date
Incident_type
remarks
)
All these are numbers and can be 2 male_killed and 2 female injured in the
same incident
so what is the best way to desing a DB for this type I cannot think anything
other than putting these all into one table.
Depending on your needs you might come up with something like this...
Create table gender (gender_cd char(1) not null, gender_name varchar(8) not
null) --m, male f, female
Create table persontype( person_cd char(1) not null, person_name varchar(8)
not null) -A, Adult C,Child
Create table incidenttype(incident_id int identity(1,1) not null,
incident_name varchar(24) not null) --whatever
Create table resulttype(result_cd char(1) not null, resultname varchar(12)
not null) -- K, Killed I, Injured A, Affected
Create table states(state_cd char(2) not null, statename varchar(24) not
null) --NC, North Carolina
Create table things(id int identity(1,1) not null,
state_cd char(2) not null references
states(state_cd),
incident_id int not null references
incidenttype(incident_id),
thing_dt datetime not null,
remarks varchar(2000) null)
Create table thingdetail (id int identity(1,1) not null,
thing int not null references things(id),
gender_cd char(1) not null references
gender(gender_cd),
person_cd char(1) not null references
persontype(person_cd),
result_cd char(1) not null references
resulttype(result_cd),
NumberOfPeople int not null)
Although I did this quick and dirty, I think it is in third normal form...
You may not need this much normalization, but this is a beginning..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:e$dztHBWEHA.2408@.tk2msftngp13.phx.gbl...
> I have to keep detail of detail gender based DB like this where there may
be
> a number of incidents per state
> Create table gender
> (
> State_id
> Male_killed
> Male_injured
> male_affected
> Female_killed
> female_affected
> female_injured
> Child_killed
> Child_injured
> child_affected
> Date
> Incident_type
> remarks
> )
> All these are numbers and can be 2 male_killed and 2 female injured in the
> same incident
> so what is the best way to desing a DB for this type I cannot think
anything
> other than putting these all into one table.
>
|||Based on your narrative and sample schema, one has to guess your conceptual
model, the attributes and the functional dependencies that exist among them
before suggesting something meaningful. In general, requesting solutions for
design problems in newsgroup may not be a worthwhile effort since there is a
fair chance that the problem could be misunderstood, miscommunicated or
misread and so are the suggested solutions.
With such a caveat, here is a try:
CREATE TABLE Incidents(
Incident_id INT NOT NULL PRIMARY KEY,
State CHAR(2) NOT NULL
REFERENCES States( State ),
Incident_date DATETIME NOT NULL,
Inident_type INT NOT NULL CHECK (...) -- or reference it
Remarks VARCHAR(8000) NOT NULL
DEFAULT '--NA--'
UNIQUE ( State, Incident_date, Inident_type) );
CREATE TABLE Details (
Incident_id INT NOT NULL
REFERENCES Incidents ( Incident_id )
Outcome VARCHAR(10) NOT NULL
CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
'Unknown' ) ) -- or reference it
Person_type VARCHAR(6) NOT NULL
CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
-- or reference it
Incident_count INT NOT NULL DEFAULT(0)
PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
The check constraint values, if are of a higher number, can be substituted
with a referenced table with corresponding foriegn key constraint.
Anith
|||thnaks
but the only problem is that I have a file with 1000 records that comes in
and I am importing through excel so any suggestions asto how to handle it if
I am to adopt the above design considerations
Anup Help is awaited
thanks
anup
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uRQ16uFWEHA.2928@.tk2msftngp13.phx.gbl...
> Based on your narrative and sample schema, one has to guess your
conceptual
> model, the attributes and the functional dependencies that exist among
them
> before suggesting something meaningful. In general, requesting solutions
for
> design problems in newsgroup may not be a worthwhile effort since there is
a
> fair chance that the problem could be misunderstood, miscommunicated or
> misread and so are the suggested solutions.
> With such a caveat, here is a try:
> CREATE TABLE Incidents(
> Incident_id INT NOT NULL PRIMARY KEY,
> State CHAR(2) NOT NULL
> REFERENCES States( State ),
> Incident_date DATETIME NOT NULL,
> Inident_type INT NOT NULL CHECK (...) -- or reference it
> Remarks VARCHAR(8000) NOT NULL
> DEFAULT '--NA--'
> UNIQUE ( State, Incident_date, Inident_type) );
> CREATE TABLE Details (
> Incident_id INT NOT NULL
> REFERENCES Incidents ( Incident_id )
> Outcome VARCHAR(10) NOT NULL
> CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
> 'Unknown' ) ) -- or reference it
> Person_type VARCHAR(6) NOT NULL
> CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
> -- or reference it
> Incident_count INT NOT NULL DEFAULT(0)
> PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
> The check constraint values, if are of a higher number, can be substituted
> with a referenced table with corresponding foriegn key constraint.
> --
> Anith
>
|||You have a couple of workarounds. Have the data Bulk inserted or DTS'ed to a
staging table with the same structure as the spreadsheet. Then you can write
up a simple script to populate the data into well normalized tables as
mentioned.
Another option is to use a client programming language like C or VB to read
the file & split the data accordingly. Then you can do the inserts directly
into the tables from the program.
Again, to reiterate the general sentiment, the table structures posted as
the response to your initial post is just a guildline and there is little
chance that your business model conforms to the assumptions we made while
posting such a schema.
Anith

Database Design Problem

I have to keep detail of detail gender based DB like this where there may be
a number of incidents per state
Create table gender
(
State_id
Male_killed
Male_injured
male_affected
Female_killed
female_affected
female_injured
Child_killed
Child_injured
child_affected
Date
Incident_type
remarks
)
All these are numbers and can be 2 male_killed and 2 female injured in the
same incident
so what is the best way to desing a DB for this type I cannot think anything
other than putting these all into one table.Depending on your needs you might come up with something like this...
Create table gender (gender_cd char(1) not null, gender_name varchar(8) not
null) --m, male f, female
Create table persontype( person_cd char(1) not null, person_name varchar(8)
not null) -A, Adult C,Child
Create table incidenttype(incident_id int identity(1,1) not null,
incident_name varchar(24) not null) --whatever
Create table resulttype(result_cd char(1) not null, resultname varchar(12)
not null) -- K, Killed I, Injured A, Affected
Create table states(state_cd char(2) not null, statename varchar(24) not
null) --NC, North Carolina
Create table things(id int identity(1,1) not null,
state_cd char(2) not null references
states(state_cd),
incident_id int not null references
incidenttype(incident_id),
thing_dt datetime not null,
remarks varchar(2000) null)
Create table thingdetail (id int identity(1,1) not null,
thing int not null references things(id),
gender_cd char(1) not null references
gender(gender_cd),
person_cd char(1) not null references
persontype(person_cd),
result_cd char(1) not null references
resulttype(result_cd),
NumberOfPeople int not null)
Although I did this quick and dirty, I think it is in third normal form...
You may not need this much normalization, but this is a beginning..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:e$dztHBWEHA.2408@.tk2msftngp13.phx.gbl...
> I have to keep detail of detail gender based DB like this where there may
be
> a number of incidents per state
> Create table gender
> (
> State_id
> Male_killed
> Male_injured
> male_affected
> Female_killed
> female_affected
> female_injured
> Child_killed
> Child_injured
> child_affected
> Date
> Incident_type
> remarks
> )
> All these are numbers and can be 2 male_killed and 2 female injured in the
> same incident
> so what is the best way to desing a DB for this type I cannot think
anything
> other than putting these all into one table.
>|||Based on your narrative and sample schema, one has to guess your conceptual
model, the attributes and the functional dependencies that exist among them
before suggesting something meaningful. In general, requesting solutions for
design problems in newsgroup may not be a worthwhile effort since there is a
fair chance that the problem could be misunderstood, miscommunicated or
misread and so are the suggested solutions.
With such a caveat, here is a try:
CREATE TABLE Incidents(
Incident_id INT NOT NULL PRIMARY KEY,
State CHAR(2) NOT NULL
REFERENCES States( State ),
Incident_date DATETIME NOT NULL,
Inident_type INT NOT NULL CHECK (...) -- or reference it
Remarks VARCHAR(8000) NOT NULL
DEFAULT '--NA--'
UNIQUE ( State, Incident_date, Inident_type) );
CREATE TABLE Details (
Incident_id INT NOT NULL
REFERENCES Incidents ( Incident_id )
Outcome VARCHAR(10) NOT NULL
CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
'Unknown' ) ) -- or reference it
Person_type VARCHAR(6) NOT NULL
CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
-- or reference it
Incident_count INT NOT NULL DEFAULT(0)
PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
The check constraint values, if are of a higher number, can be substituted
with a referenced table with corresponding foriegn key constraint.
Anith|||thnaks
but the only problem is that I have a file with 1000 records that comes in
and I am importing through excel so any suggestions asto how to handle it if
I am to adopt the above design considerations
Anup Help is awaited
thanks
anup
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uRQ16uFWEHA.2928@.tk2msftngp13.phx.gbl...
> Based on your narrative and sample schema, one has to guess your
conceptual
> model, the attributes and the functional dependencies that exist among
them
> before suggesting something meaningful. In general, requesting solutions
for
> design problems in newsgroup may not be a worthwhile effort since there is
a
> fair chance that the problem could be misunderstood, miscommunicated or
> misread and so are the suggested solutions.
> With such a caveat, here is a try:
> CREATE TABLE Incidents(
> Incident_id INT NOT NULL PRIMARY KEY,
> State CHAR(2) NOT NULL
> REFERENCES States( State ),
> Incident_date DATETIME NOT NULL,
> Inident_type INT NOT NULL CHECK (...) -- or reference it
> Remarks VARCHAR(8000) NOT NULL
> DEFAULT '--NA--'
> UNIQUE ( State, Incident_date, Inident_type) );
> CREATE TABLE Details (
> Incident_id INT NOT NULL
> REFERENCES Incidents ( Incident_id )
> Outcome VARCHAR(10) NOT NULL
> CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
> 'Unknown' ) ) -- or reference it
> Person_type VARCHAR(6) NOT NULL
> CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
> -- or reference it
> Incident_count INT NOT NULL DEFAULT(0)
> PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
> The check constraint values, if are of a higher number, can be substituted
> with a referenced table with corresponding foriegn key constraint.
> --
> Anith
>|||You have a couple of workarounds. Have the data Bulk inserted or DTS'ed to a
staging table with the same structure as the spreadsheet. Then you can write
up a simple script to populate the data into well normalized tables as
mentioned.
Another option is to use a client programming language like C or VB to read
the file & split the data accordingly. Then you can do the inserts directly
into the tables from the program.
Again, to reiterate the general sentiment, the table structures posted as
the response to your initial post is just a guildline and there is little
chance that your business model conforms to the assumptions we made while
posting such a schema.
Anith

Database Design Problem

I have to keep detail of detail gender based DB like this where there may be
a number of incidents per state
Create table gender
(
State_id
Male_killed
Male_injured
male_affected
Female_killed
female_affected
female_injured
Child_killed
Child_injured
child_affected
Date
Incident_type
remarks
)
All these are numbers and can be 2 male_killed and 2 female injured in the
same incident
so what is the best way to desing a DB for this type I cannot think anything
other than putting these all into one table.Depending on your needs you might come up with something like this...
Create table gender (gender_cd char(1) not null, gender_name varchar(8) not
null) --m, male f, female
Create table persontype( person_cd char(1) not null, person_name varchar(8)
not null) -A, Adult C,Child
Create table incidenttype(incident_id int identity(1,1) not null,
incident_name varchar(24) not null) --whatever
Create table resulttype(result_cd char(1) not null, resultname varchar(12)
not null) -- K, Killed I, Injured A, Affected
Create table states(state_cd char(2) not null, statename varchar(24) not
null) --NC, North Carolina
Create table things(id int identity(1,1) not null,
state_cd char(2) not null references
states(state_cd),
incident_id int not null references
incidenttype(incident_id),
thing_dt datetime not null,
remarks varchar(2000) null)
Create table thingdetail (id int identity(1,1) not null,
thing int not null references things(id),
gender_cd char(1) not null references
gender(gender_cd),
person_cd char(1) not null references
persontype(person_cd),
result_cd char(1) not null references
resulttype(result_cd),
NumberOfPeople int not null)
Although I did this quick and dirty, I think it is in third normal form...
You may not need this much normalization, but this is a beginning..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:e$dztHBWEHA.2408@.tk2msftngp13.phx.gbl...
> I have to keep detail of detail gender based DB like this where there may
be
> a number of incidents per state
> Create table gender
> (
> State_id
> Male_killed
> Male_injured
> male_affected
> Female_killed
> female_affected
> female_injured
> Child_killed
> Child_injured
> child_affected
> Date
> Incident_type
> remarks
> )
> All these are numbers and can be 2 male_killed and 2 female injured in the
> same incident
> so what is the best way to desing a DB for this type I cannot think
anything
> other than putting these all into one table.
>|||Based on your narrative and sample schema, one has to guess your conceptual
model, the attributes and the functional dependencies that exist among them
before suggesting something meaningful. In general, requesting solutions for
design problems in newsgroup may not be a worthwhile effort since there is a
fair chance that the problem could be misunderstood, miscommunicated or
misread and so are the suggested solutions.
With such a caveat, here is a try:
CREATE TABLE Incidents(
Incident_id INT NOT NULL PRIMARY KEY,
State CHAR(2) NOT NULL
REFERENCES States( State ),
Incident_date DATETIME NOT NULL,
Inident_type INT NOT NULL CHECK (...) -- or reference it
Remarks VARCHAR(8000) NOT NULL
DEFAULT '--NA--'
UNIQUE ( State, Incident_date, Inident_type) );
CREATE TABLE Details (
Incident_id INT NOT NULL
REFERENCES Incidents ( Incident_id )
Outcome VARCHAR(10) NOT NULL
CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
'Unknown' ) ) -- or reference it
Person_type VARCHAR(6) NOT NULL
CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
-- or reference it
Incident_count INT NOT NULL DEFAULT(0)
PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
The check constraint values, if are of a higher number, can be substituted
with a referenced table with corresponding foriegn key constraint.
--
Anith|||thnaks
but the only problem is that I have a file with 1000 records that comes in
and I am importing through excel so any suggestions asto how to handle it if
I am to adopt the above design considerations
Anup Help is awaited
thanks
anup
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uRQ16uFWEHA.2928@.tk2msftngp13.phx.gbl...
> Based on your narrative and sample schema, one has to guess your
conceptual
> model, the attributes and the functional dependencies that exist among
them
> before suggesting something meaningful. In general, requesting solutions
for
> design problems in newsgroup may not be a worthwhile effort since there is
a
> fair chance that the problem could be misunderstood, miscommunicated or
> misread and so are the suggested solutions.
> With such a caveat, here is a try:
> CREATE TABLE Incidents(
> Incident_id INT NOT NULL PRIMARY KEY,
> State CHAR(2) NOT NULL
> REFERENCES States( State ),
> Incident_date DATETIME NOT NULL,
> Inident_type INT NOT NULL CHECK (...) -- or reference it
> Remarks VARCHAR(8000) NOT NULL
> DEFAULT '--NA--'
> UNIQUE ( State, Incident_date, Inident_type) );
> CREATE TABLE Details (
> Incident_id INT NOT NULL
> REFERENCES Incidents ( Incident_id )
> Outcome VARCHAR(10) NOT NULL
> CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
> 'Unknown' ) ) -- or reference it
> Person_type VARCHAR(6) NOT NULL
> CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
> -- or reference it
> Incident_count INT NOT NULL DEFAULT(0)
> PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
> The check constraint values, if are of a higher number, can be substituted
> with a referenced table with corresponding foriegn key constraint.
> --
> Anith
>|||You have a couple of workarounds. Have the data Bulk inserted or DTS'ed to a
staging table with the same structure as the spreadsheet. Then you can write
up a simple script to populate the data into well normalized tables as
mentioned.
Another option is to use a client programming language like C or VB to read
the file & split the data accordingly. Then you can do the inserts directly
into the tables from the program.
Again, to reiterate the general sentiment, the table structures posted as
the response to your initial post is just a guildline and there is little
chance that your business model conforms to the assumptions we made while
posting such a schema.
--
Anith

Sunday, February 19, 2012

Database Design

I am designing a WEB BASED Accounting Software with ASP and SQL
Server. For this I need some help for the Database design. My design
is as follows.

I)User table: User_id, UserName....
Users (e.g. John Smith) Each User would contain a following Group of
tables

a)Customers
b)Suppliers
c)Bank Accounts
d)Transactions

Tables under :
User_FinYear_Customers (e.g JohnSmith_02_03_Customers)
User_FinYear_Suppliers (e.g JohnSmith_02_03_Suppliers)
User_FinYear_BankAccounts (e.g JohnSmith_02_03_BankAccounts)
User_FinYear_Transactions (e.g JohnSmith_02_03_Transactions)

As new user is created all the above tables are created at run time.
These tables are created for each and every user. There can be more
than 4 tables (as mentioned above) for one user. These tables will
increase as more users are added. Only thing in support of this design
is that, the record fetching time for a particular user would be
minimum and the table for a particular user will only load in Memory.

IS IT FEASIBLE TO CREATE ABOUT 20 TABLES FOR EACH NEW USER ADDED TO
THE DATABASE? WHICH MEANS IF THERE ARE 1000 USERS THERE WOULD BE 20000
TABLES IN THE DATABASE. THIS CASE CAN GO WORSE IF THERE ARE MORE THAN
1000 USERS. WHAT IS BETTER DATABASE DESIGN, MORE TABLES WITH LESS
RECORDS OR LESS TABLES WITH MORE NO.OF RECORDS?

An alternative design can be as follows

Tables:
Users, Customers, Suppliers, BankAccounts, Transactions ....and so
on.

User: User_Id, UserName, .....
Customers: User_Id, Customer_Id,.....
Suppliers: User_Id, Supplier_Id,....
BankAccounts: User_Id, BankAc_Id,....
Transactions: User_Id, Trans_Id.....
..
..
..
..

All these tables would be created at the design time only and as a new
user is created a record is added to the users table. When the user
adds Customer the record is added to the Customers table... and so
on... The problem with this design is that Customers,Suppliers,
BankAccounts... etc tables would contain records for all the users
and thus the record fetching time for a particular user increases as
many times as there are users in the Database. Another problems with
this design is that more than one user would be connected at run time
will access the same tables, and for even a single user the complete
table will be loaded in memory.

WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
PLEASE HELP WITH CONVINCING REASONS.Clearly #2, less maint and if you ever need over all queries or a customer
gets combined it should be a lot easier. Also use Oracle not the other one
since this is an Oracle newsgroup.
Jim
"Rushikesh" <rbaiwar@.sify.com> wrote in message
news:2b29906c.0307142123.1d020801@.posting.google.c om...
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.
>
> I)User table: User_id, UserName....
> Users (e.g. John Smith) Each User would contain a following Group of
> tables
> a)Customers
> b)Suppliers
> c)Bank Accounts
> d)Transactions
>
> Tables under :
> User_FinYear_Customers (e.g JohnSmith_02_03_Customers)
> User_FinYear_Suppliers (e.g JohnSmith_02_03_Suppliers)
> User_FinYear_BankAccounts (e.g JohnSmith_02_03_BankAccounts)
> User_FinYear_Transactions (e.g JohnSmith_02_03_Transactions)
> As new user is created all the above tables are created at run time.
> These tables are created for each and every user. There can be more
> than 4 tables (as mentioned above) for one user. These tables will
> increase as more users are added. Only thing in support of this design
> is that, the record fetching time for a particular user would be
> minimum and the table for a particular user will only load in Memory.
> IS IT FEASIBLE TO CREATE ABOUT 20 TABLES FOR EACH NEW USER ADDED TO
> THE DATABASE? WHICH MEANS IF THERE ARE 1000 USERS THERE WOULD BE 20000
> TABLES IN THE DATABASE. THIS CASE CAN GO WORSE IF THERE ARE MORE THAN
> 1000 USERS. WHAT IS BETTER DATABASE DESIGN, MORE TABLES WITH LESS
> RECORDS OR LESS TABLES WITH MORE NO.OF RECORDS?
>
> An alternative design can be as follows
> Tables:
> Users, Customers, Suppliers, BankAccounts, Transactions ....and so
> on.
> User: User_Id, UserName, .....
> Customers: User_Id, Customer_Id,.....
> Suppliers: User_Id, Supplier_Id,....
> BankAccounts: User_Id, BankAc_Id,....
> Transactions: User_Id, Trans_Id.....
> .
> .
> .
> .
> All these tables would be created at the design time only and as a new
> user is created a record is added to the users table. When the user
> adds Customer the record is added to the Customers table... and so
> on... The problem with this design is that Customers,Suppliers,
> BankAccounts... etc tables would contain records for all the users
> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database. Another problems with
> this design is that more than one user would be connected at run time
> will access the same tables, and for even a single user the complete
> table will be loaded in memory.
> WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
> PLEASE HELP WITH CONVINCING REASONS.|||"Rushikesh" <rbaiwar@.sify.com> wrote in message
news:2b29906c.0307142123.1d020801@.posting.google.c om...
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.

You may need a lot more than just these tables.

> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database.

Oh no, it doesn't!

> will access the same tables, and for even a single user the complete
> table will be loaded in memory.

It is ridiculous if you write your code to do that.

> WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
> PLEASE HELP WITH CONVINCING REASONS.

Second.
Read a few texts about database design and normalization.

--
Cheers
Nuno Souto
wizofoz2k@.yahoo.com.au.nospam|||Rushikesh (rbaiwar@.sify.com) writes:
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.

As whether you should use SQL Server or Oracle, I don't have an opinion.
I come from the SQL Server side, but these questions have the same answer
for any enterprise DBMS.

> I)User table: User_id, UserName....
> Users (e.g. John Smith) Each User would contain a following Group of
> tables
> a)Customers
> b)Suppliers
> c)Bank Accounts
> d)Transactions

This is a completely unacceptable solution, and in completely violation
of the relational model. Just forget about it.

> All these tables would be created at the design time only and as a new
> user is created a record is added to the users table. When the user
> adds Customer the record is added to the Customers table... and so
> on... The problem with this design is that Customers,Suppliers,
> BankAccounts... etc tables would contain records for all the users
> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database. Another problems with
> this design is that more than one user would be connected at run time
> will access the same tables, and for even a single user the complete
> table will be loaded in memory.

Your assumptions here are entirely correct. Or to be less polite: they
are flat wrong in places.

An enterprise DBMS are built for implementing this kind of solution.
With proper indexes, the difference in access time to a certain row
if you have 100 rows or million rows in the table is neglible. Or if
you for that matter have 100 million rows.

Neither does an enterprise DBMS load an entire table into memory, because
there is an access to a single row. I cannot speak for Oracle, but SQL
Server will read the pages you access into memory, and if one user is
very active, all his pages may be in cache, whereas the pages for a user
who is on vacation are only on disk. Pages per users? Ah, didn't I mention
indexes? It does seem reasonable from you mentioned to have clustered
indexes on user ids.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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.

"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 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)

|||

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.

"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 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)

|||

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.

"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 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)

|||

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 .

Thursday, March 29, 2012

Database image not displaying

Monday, March 19, 2012

database encryption

Saturday, February 25, 2012

Database design question

Database design question

Database design question

Friday, February 24, 2012

Database Design Problem

Database Design Problem

Database Design Problem

Sunday, February 19, 2012

Database Design

Tuesday, February 14, 2012

Database corrupt on power loss

Database corrupt on power loss

Database corrupt on power loss

Database corrupt on power loss

Database level lock

Blog Archive

About Me