Database concurrent access issue

Dear All,

I have this .NET application that inserts lucky draw entries into SQL server. Each entry may have a range of values that the winning number will be drawn from. For example,

Entry 1: [1, 2]
Entry 2: [3, 8]
Entry 3: [9, 10]
Entry 4: [11, 11]
Entry 5: [12, 20]

The winning number will be picked from [1, 20]. Given this scenario, what is the best design that can handle the concurrency issue? If two entries are input at the same time, how to make sure it won't have the same starting value? Anyway to lock a table when one is accessing, disallowing other to run select query?

Thanks.You'll have to explain that better.|||For example, Entry 3: [9, 10] means Entry 3 has the number range starting from 9 and ending at 10.

The winning number will be drawn from the number range.

You'll have to explain that better.|||Clear as mud.

If you want to ensure two entries do not share the same starting value, then make the starting value the primary key or a unique index.|||blindman: qxz wants to validate new entries to ensure that it does not conflict with any existing entry and wants to enforce this validation in case of multiple users updating the Db same time...

qxz: a table lock is needed to ensure proper insert. remember explicit locks can have negative effect on performance

create procedure CheckIt (@.StVal int, @.EndVal int)
as

BEGIN TRANSACTION
declare @.CheckFailed char(1)
set @.CheckFailed = 'N'
if exists (select * from MyTable with (tablockx) where @.StVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if exists (select * from MyTable where @.EndVal between StartVal and EndVal)
set @.CheckFailed = 'Y'

if @.CheckFailed = 'Y'
begin
ROLLBACK TRANSACTION
return 0
end
else
begin
insert into MyTable .....
COMMIT TRANSACTION
return 1
end

Database as stripchart recorder - is it feasible?

We want to use SqlServer Express as a data recorder for a piece of equipment. The purpose is to store all possible data values the equipment generates for a length of time so that if a problem occurs, we can search through the data to see what happened. The data is floating point numbers, like temperatures, etc.

For example, there are 200 sensors on the equipment. Every second, we want to store the 200 sensor values. The database would be one big table with 200 columns for the sensors and each second we write a row of data. Every day, the equipment would delete data older than 30 days, so that the database doesn't grow past a certain size.

Questions:

1. Any obvious reason we can't do this?

2. 30 days * 200 values * 4 bytes/value creates a 2 GB database. SqlServer Express should be able to handle that, right?

3. The equipment is running at a customer site. If the customer has a problem, we would like to be able to say to them something like, "Retrieve 3 hours of data starting last Monday at noon for Sensors A, B, and C and email it to us." We plan to give them an application that will let them put in a time range and select which sensors; it will search the database, collect the resulting data and put it in a file to send to us. Any recommendations on what format the file be in? Text? XML? Is there an obvious format that one uses to store a chunk of data from a database in?

The number and types of sensors will be different on each piece of equipment, so we don't have a predefined table or report format, we have to create it on the fly.

Thanks in advance for your thoughts.

1. I've done this many times.

2. SQL Express is limited to a 4GB database. With indexes, your data size should fit -but may be close. You will want to index the datetime column -make it the primary key.

3. Transfer files 'should' be easy to use by the recipient. xml is good, csv is good; both are easy to create and transfer. Some folks think that xml is the panacea.

Will you be using Kepware/Linkmaster?

|||

1. Good - you give me hope!

2. Why will I want to index the datetime column? Does it make it faster to search?

3. Wouldn't XML add a lot of overhead to the size of the file?

I never heard of Kepware/Linkmaster, but I'm going to look them up right now.

|||

You indicated that you would be searching for data from a datetime range. Searching a 2GB table will be quite 'slow' without the indexing. However, you will need to examine the trade-offs, less insertion overhead in index maintenance vs. slower query responses. If the queries are a 'rare' occurrance, then you may choose to forgo the indexing and live with slow query responses.

xml does add to file size, but the resulting files can be easily opened in Excel. In your situation, where you are the only recipient of the transfer file, csv may be a good solution. (Even the Fixed field table output may work for you too.)

|||Time to create a table and play around. Thank you for your advice.

Data Warehouse Nulls

Hello..

I was wondering if anyone out there could tell me how they deal with
NULL values in a data warehouse? I am looking to implement a warehouse
in SQL 2005 and have some fields which will have NULL values and I
would like some further ideas on how to deal with them. At my last job
in dealing with Oracle we were just going to leave the fields NULL, but
in SQL how would you best recommend cleaning the data? I greatly
appreicate your help and look forward to your reponses.

Thank youVery interesting question. The answer is "it depends."

As a general rule, I'll leave money as null usually. My logic is that
if a dollar amount is unknown, that is different then the dollar amount
zero, and i probably need to deal with the unknowns wherever and
whenever the amounts are shown to teh end user. This leads to
interesting discussions with the users, as you get to explain to them
the issues, and ask them what they want the defaults to be, or whether
they want to skip the data, and how they want the reports to be
documented.

For text fields, I will usually convert to ' '. Olap likes that
better. Sometimes I will convert nulls to 'BLANK'. Just kind of
depends.
Dates need to stay null. A null date is the easiest thing to deal
with.

Does this answer your questions? Are you doing OLAP? You might try
creating a cube with your denormalized data. OLAP is pretty neat for
datawarehouses where users want to extract data.
Regards,
Doug|||Thanks so much Doug! Your answer is of great help!

Data Values Validation

Hello all...I am trying to validate that the new work date that is being imported from the text file does not exist in the table. So in other words I do not want duplicate data. This is what I haveSqlDataReader dr =newSqlDataReader(); SqlParameter sp =newSqlParameter("@.WorkDate", Data.SqlDbType.datetime, 8, Data.ParameterDirection.Input); if (dr.HasRows) sp ="@.WorkDate"; else *How does that look? Am I in the ball part…

Try using a stored procedure but instead of

INSERT INTO tablename (WorkDate) VALUES (@.WorkDate)

have

IF NOT EXISTS(SELECT * FROM tablename WHERE WorkDate =@.WorkDate)
INSERT INTO tablename (WorkDate) VALUES (@.WorkDate)

That way you can call the stored procedure passing in the WorkDate value, but a record will only be inserted if the value does not already exist.

|||

Thanks TAT for your help... question, what if a record in there with the same date how would I let the user know?

|||

To let the user know, you need an additional parameter so your stored procedure would look like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: Add record if not already on file
-- =============================================
CREATE PROCEDURE dbo.usp_TableNameAdd
@.WorkDate DateTime,
@.Exists BIT OUTPUT
AS
SET NOCOUNT ON;
SET @.Exists = 0
IF NOT EXISTS(SELECT * FROM tablename WHERE WorkDate = @.WorkDate)
INSERT INTO tablename (WorkDate) VALUES (@.WorkDate)
ELSE
SET @.Exists = 1
GO

|||

Another way could be to raise user friendly error message from within your SP. Read BOL for RAISERROR.

|||

I have a few more questions. When calling the sp I am getting an error message also in the SP the staring lines

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

are not working for me, is there something I am not seeing.

namespace WindowsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
//Pull from Text File in from Network Drive
string Source = @."\.\J-1119.txt";
string Date = "";
string O1 = "";
string A1;
string P1;
string R1;
string LineIn;
StreamReader InFile = new StreamReader(Source);

***TRYING TO CALL MY SP****
SqlCommand SC = new SqlCommand("SP_PromoNoDups", sConnect);

SC.Parameters.Add(new SqlParameter("@.WorkDate", WorkDate));
// SqlCommand SC = new SqlCommand("Select * from [tblname]", sConnect);
SC.CommandType = CommandType.StoredProcedure;

LineIn = InFile.ReadLine();
// pos is the location of the cursor
int pos = LineIn.IndexOf("WORK DATE:");
if (pos > 0)
{
Date = LineIn.Substring(pos + 10, 10).Trim();
}
while (LineIn != null)
{
if (LineIn.IndexOf("O1:") == 2)
{
if (LineIn.Length > 10)
{
O1 = LineIn.Substring(10).Trim();
}
else
{
O1 = "";
}
LineIn = InFile.ReadLine();
LineIn = InFile.ReadLine();
while (LineIn != null && LineIn.Trim() != "")
{
P1 = LineIn.Substring(2, 5).Trim();
A1 = LineIn.Substring(19, 20).Trim();
R1 = LineIn.Substring(41, 6).Trim();
string Adjustment = LineIn.Substring(57).Trim(); LineIn = InFile.ReadLine();
if (LineIn.IndexOf("mane") >= 0)
{
break;
} SC.CommandType = LineOut;(Erroring out here)
SC.ExecuteReader();
// if page header - look for next O1
}
LineIn = InFile.ReadLine();
LineIn = InFile.ReadLine();
}
InFile.Close();
}
}
}
}

data type question

Would float be the best choice for a data type using latitude and longitude values...i.e. " 33.606379" " -86.50249"
Thanks,
-D-The FLOAT datatype is a logical choice for representing Latitude and Longitude measurements, as long as your database sees them as "measurements" instead of exact values. If you need to treat them as counts instead of measures, you'll probably want to use NUMERIC instead of FLOAT.

-PatP|||Hi Dman100

I hope this is valuable to the thread and not a hijack. I am interested in appropriate use of approximate floating point numbers (well - a bit).

I have pretty well eliminated float and real datatypes from all my databases. This is because the majority of the applications I support and produce require absolute values. As far as I can tell - if the application of a numeric field requires some sort of mathematical manipulation (especially to a high degree of precision) and the number concerned is absolute then float and real are poor choices for the field data type. The exception would be a numbers that cannot be absolutely represented by a fixed number of digits (one third, pi etc).

I am not mathematically trained to the sort of standard many of the SQl gurus are. I am not certain the above is correct. I am happy to be corrected. I am happy to be told that the above is entirely irrelevent to the question in hand. I am happy to be told to bog off. I am, in fact, happy.|||It is good to be happy!

You hit the nail pretty much on the head, saying the same thing that I said in a bit more roundabout way.

Numbers can be looked at two different ways by most computer languages, and SQL can deal with them either way.

One way to think about a number is as a count... It is exact, repeatable, and can be "proved" in some way. The data types INT, BIGINT, DECIMAL, and NUMERIC are well suited for counts.

The other way to think about numbers is as a measurement... A measurement can be quite exact (to N decimal places), but it can't be "proved" like a count can. The data types REAL and FLOAT are well suited for measurements.

Values that can be derived from computation (such as speeds, accelerations, and many forms of location) are inherantly measurements. While they can be quite precise, there isn't a way to derive them from a count (other than to use one or more counts to mathematically derive the measurement). Speed as such is relative, so there isn't a direct way to count it... The best you can do is measure or count the distance traveled and the time used to compute the speed. While you might be able to count units of distance and time, there is no way to count units of speed (contrary to the belief of my neighbors in college).

The problem is that sometimes you need to deal with people that don't understand the difference between a count and a measurement. They think you should be able to store a measurement to N digits, and always have the exact same value come back. This isn't unreasonable from their perspective, and it makes perfect sense to them... They see nothing silly about the assertion that 3.141592654 is the value of pi, because to them that is a true statement.

I'm going to cut my blither short here... I've probably blabbered far more than anyone wanted to read already. The short answer boils down to REAL and FLOAT are for measurements. Most people prefer to think in counts, so most databases use INT or NUMERIC.

-PatP|||You hit the nail pretty much on the head, saying the same thing that I said in a bit more roundabout way.That pretty much sums up my entire career to date :D

Thanks Pat - you've firmed up my understanding.|||you guys are good

if i write a query likeselect x * 3.141592654 ...what datatype is that, DECIMAL or FLOAT?

and wouldn't it be better to use select x * ( select value from constants where name='pi' ) ...to allow you to define the value of pi in one spot, so that all queries could use it, so that, you know, in case the value ever changes, you wouldn't have a ton o' queries to change...|||you guys are good

if i write a query likeselect x * 3.141592654 ...what datatype is that, DECIMAL or FLOAT?

and wouldn't it be better to use select x * ( select value from constants where name='pi' ) ...to allow you to define the value of pi in one spot, so that all queries could use it, so that, you know, in case the value ever changes, you wouldn't have a ton o' queries to change...While I've heard that there is one state that has changed the value of pi to meet biblical requirements, I don't see how that would justify creating a table of constants to cope with that kind of problem. There are too many variables that I couldn't predict to make that practical. Even if we considered creating such a table as an option, it wouldn't help with the data type, only the value being used.

Its a good idea Rudy, and one that I wouldn't expect from you, but I just don't see it as practical in this particular case. ;)

-PatP|||oh my god, pat, can't you tell when someone is kidding

"in case the value of pi ever changes" -- you thought i was serious??

that's hilarious

:)|||oh my god, pat, can't you tell when someone is kiddingYou have to watch for those smilies... Sometimes they sneak in at the end!

It still wouldn't change the data type.

-PatP

Data Transpose - need help

Hi all,
Help me out, i am trying to get all values in Table A and insert into table B, i though of writing cursor. see endof the message

Table A:
CREATE TABLE [dbo].[M_SCANNEDSURVEY_AP] (
[M_CustomerSurveyID] [bigint] NULL ,
[SurveyID] [bigint] NULL ,
[LoadStatus] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[1] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[4] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[5] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[6] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[7] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[8] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[9] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[10] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[11] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[12] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO

Table B:
CREATE TABLE [dbo].[M_RESPONSE] (
[M_CustomerSurveyID] [bigint] NOT NULL ,
[SurveyID] [bigint] NOT NULL ,
[SeqNumber] [bigint] NOT NULL ,
[Response] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO

I want to insert everything in table A to table B
M_CustomerSurveyID -> M_CustomerSurveyID
SurveyID -> SurveyID
I will hardcode insert 1,2,3,4,5,6,7,8,9,10,11,12 for sequence number
values of 1,2,3,4,5,6,7,8,9,10,11,12 -> Response
DateSubmitted -> DateSubmitted

Declare @.Count_Scan INT

BEGIN
-- Get the count of scanned data in scanned data table with LoadStatus = N
SELECT @.Count_Scan = COUNT(*) from M_SCANNEDSURVEY_APTEST where
LoadStatus = 'N'

IF @.Count_Scan > 0
BEGIN
DECLARE ScanData_Cursor CURSOR FOR
SELECT * FROM M_SCANNEDSURVEY_AP WHERE LoadStatus = 'N'
OPEN ScanData_Cursor
FETCH NEXT FROM ScanData_Cursor
While (@.@.Fetch_Status <> -1)
Begin
If (@.@.Fetch_Status = -2)
Begin
FETCH NEXT FROM ScanData_Cursor
Continue
End
CLOSE ScanData_Cursor
DEALLOCATE ScanData_Cursor
END

SET @.CountSuccess = 'Y'
ENDUse your script which created TableA to create TableB. Export data from TableA and import it to TableB.|||I want to do it automatically once in a day using dts, how can i insert the data into table B|||INSERT INTO TableB(Field1, Field2, Field3, ...)
SELECT Field1, Field2, Field3, ... FROM TableA|||And never use SELECT * in code...