Rebuild index on a heavy loaded table when my apps is running

I am using SQL Server for my application and one of the table is very active (insert/delete) and I have to rebuild the index for that table on a regular basis. However, I don't want to stop my application when the index rebuilding is going on. Ideally, I would like to have a script automatically kicked off on a scheduled time.

My question is:

Is there any risk from rebuilding index while my app is actively updating that table? Will my app becomes slow or fails due to DB not responding or is there any risk of DB hung?

Thanks,

Jeff

You app will just wait until the index rebuilding finished as soon as it will try to read or modify the index. Anyway, try to rebuild indexes when your database load is minimal.

Realtime record count for table...

Here's a little sql 2005 script I wrote:

1. Start by running this script....

declare @.x int

select @.x = 1

while ( @.x < 75000)
begin
insert into myTesttable values (@.x)
Select @.x = @.x + 1
end

2. While the script is still running, I want to know how many records are in the table. From the same query window as the script, I have run both of the following statements.

select count(*) from mytesttable
witn (nolock)

select count(*) from mytesttable
witn (tablock)

Instead of getting the answer immediately, they run only after the original script has completed. They seem to be "blocked". How can I get a near realtime count of the number of records in this table while the script populates the table?

Thanks,

Barkingdog

Barkingdog, just open another query window and run your "select count(*) from mytesttable". You'll get the row count while the other script is running.|||

If you use GridView it only displayed after the batch execution completed.

You can use different window or change the GridView to TextView to get the result immd.

|||

Yes, opening a new window did enable to the "select count(*) ...." to run but why is this the case? (After all, I couldn't run the "Select count(*) .." from the window that invoked the original script. Seems like the orignal script "blocks" the conneciton so I need to open a new conneciton (window). But why?

TIA,

barkingdog

|||

barkingdog wrote:

Yes, opening a new window did enable to the "select count(*) ...." to run but why is this the case? (After all, I couldn't run the "Select count(*) .." from the window that invoked the original script. Seems like the orignal script "blocks" the conneciton so I need to open a new conneciton (window). But why?

TIA,

barkingdog

The query window runs the statements sequentially and does not run the next statement until the prior one has finished, thus your select count(*)... will not run until the statements in the while loop complete. It's not "blocking" the connection, it just has not completed the prior statements...

If one of the replies solved your problem, please mark them as answered...

Really need urgent help in Insert statement

Hi,

I am creating a event registration system. Right now my program is able to insert user's registered event into the database. This is the code i wrote:

INSERT INTO EventRegistration(eventId,userId,status) VALUES('" + eid + "','" + id + "','" + status + "')

However I notice that same user are able to register the same event when i use this code. How should i improve my code in order to prevent same user from registering the same event. Which means my sql statement will not insert registered event into the database if same user register the same event. I will really appreciate the help all of you offer.

Thank you.

Regards,
ferDepending on your database tool, you probably just need a UNIQUE CONSTRAINT or a UNIQUE INDEX and things should be lovely.

-PatP|||As Pat.P infers good table design should preclude you users entering duplicate data.

What makes an event / entry unique in the real world and how do you represent that in your database?|||if you are providing the user a list of options to register, I assume you are pulling the list of things to register from the database too ?

only show them options to register for, that they have not registered for already.

for instance, if you have 10 time slots available for something and someone picks time slot #3, then the next user will only see 9 time slots to pick from. (#3 is no longer displayed)|||Hi Kropes2001,

Ya you actually get wat i mean. I am providing a list of options for user to register, and options(events) are retrieve from the database. I get wat u mean but i dun realli noe how to implement it as i am new to sql. Can you provide me a sample coding of wat u mean?

Thank you.

ferlina|||If you define the primary key, or alternatively a unique key correctly there can't be any duplicates.

If you handle any eror thrown by the db engine then you can gracefully handle the situation where a single user has tried to make more than one booking for the same event.
HTH|||Sorry... but
why "handle an error" when you can prevent it from happening to begin with ?
i see too many programmers that do that. let the user enter whatever they want and then try to deal with all the errors it generates.
the more logical design is to simply provide the necessary information in a way that prevents the user form making any errors like that to begin with. and its usually a lot easier too.

an ounce of prevention vs. a pound of cure.

ferlina,
i would really need to see your table designs to give you an absolute answer.

i am assuming that you have 1 table with all of the events that are available. plossible fields :
EventID
Description
StartDate
StartTime
Active
etc....
etc....

i assume that you then use a SELECT statement to build a recordset of all of the availalbe events. something like :
SELECT EventID, Description, StartDate, StartTime from EventList WHERE Active=true

i also assume that by the time you hit this page, you already know who your user is, or at least what their ID is ? (they already are defined in the database somelace, yes ?)

if so, then combine the SELECT of available events with an outer join. you want to retrieve a list of all events that are active in teh database, except for the ones that are already scheduled by this UserID.

take a look at this article

http://www.dev-archive.com/dbzone/Article/17403/0/page/4

instead of selecting all of the registered events that match the user's ID, you are selecting all of the ones that they did not register for.|||In a multi user environment you have to trap for errors and handle them gracefully. After a user has booked an event then you can exclude them from appearing in future combo select boxes, but untll they have booked you are running the risk of an error. Granted you could set a flag on the user to say they are in process of making a booking - but that doesn;t stp clients that have already loaded available users from attempting to make a duplicate booking.

It is always theoretically possible for a user to be booking an event from more than one session at the same time (either through user error or deliberate attempt to subvert the system.

Really basic question about inserting data into a SQL database

Hi everyone,

I'm new to programming, and trying to learn and can't seem to find a clear-cut answer on how to insert text from a textbox into a database as a new record. I have a textbox and a button that when the button_click even is fired, that it will insert the data from the textbox as a new record. Any help will be greatly appreciated!!

If it's SQL Server you can do something like this (in C#)

SqlConnection conn = new SqlConnection("Server=<servername>;Database=<dbName>;Integrated Security=true");

conn.Open();

string sql = "Insert into.... the rest of your insert statement";

SqlCommand cmd = new SqlCommand(sql, conn);

cmd.ExecuteNonQuery();

conn.Close();

Or you can modify that a bit and use a stored procedure if that suits your needs better. If you need to connect with a username/password just remove the integrated security part of the connection string and add the username/password.

Hope that helps.

|||

i'm using Visual Basic

to perform an insert

when a button add is pressed

its surpose to insert the data from a textbox to the database

the data type of the tables column is char(4)

its similar to the top code but u have to add some other stuff do u know what they are

Really basic question about inserting data into a SQL database

Hi everyone,

I'm new to programming, and trying to learn and can't seem to find a clear-cut answer on how to insert text from a textbox into a database as a new record. I have a textbox and a button that when the button_click even is fired, that it will insert the data from the textbox as a new record. Any help will be greatly appreciated!!

If it's SQL Server you can do something like this (in C#)

SqlConnection conn = new SqlConnection("Server=<servername>;Database=<dbName>;Integrated Security=true");

conn.Open();

string sql = "Insert into.... the rest of your insert statement";

SqlCommand cmd = new SqlCommand(sql, conn);

cmd.ExecuteNonQuery();

conn.Close();

Or you can modify that a bit and use a stored procedure if that suits your needs better. If you need to connect with a username/password just remove the integrated security part of the connection string and add the username/password.

Hope that helps.

|||

i'm using Visual Basic

to perform an insert

when a button add is pressed

its surpose to insert the data from a textbox to the database

the data type of the tables column is char(4)

its similar to the top code but u have to add some other stuff do u know what they are

REAL REAL Experts needed for pretending to be replication_agent

Hi All,
We know that, "NOT FOR REPLICATION" option makes it possible for
replication agents ,let's say, to insert rows without being
constrained by IDENTITY restriction or check constraints(And also it
does not change SEED value in the table).
That means, being a replication agent is like having a PASS CARD that
allows them to get rid of constraint checks. (But disabling
constraints is not a solution)
But, during conflict resolution coding, I need to INSERT some data to
some tables (related tables), but I am facing CONSTRAINTS that merge
agent does not face.
Is there any way of pretending to be like merge_agent for constraint
overcoming? THIS IS THE CORE QUESTION.
To be more specific, is there any way of setting sessionproperty for
replication_agent? That is: when you execute the following query in a
normal connection;
select sessionproperty('replication_agent')
you get "0".
but for replication_agent that function returns "1". Microsoft guys
somehow set connection property for agent, and how they do it is
scritly undocumented.
I tried setting context_info in sysprocesses table to "8" but it did
not work. That SP also is not enough by itself
:sp_MSsetcontext_replagent
Please help, if any way of pretending to be merge agent.
PS:I event wrote an application with ReplMerg.exe process name
guessing that SQL may know agent from its process name.
And also please don't suggest disabling constraints because it is not
enough, e.g. replication_agent does not affect SEED value for
inserts that it does..
I hope some REAL expert will hear my SCREAM.
Thanks alot in advance,
Nury SWORD
MCDBA - MCSD
Toronto
I suggest you contact PSS for an answer to your question.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Nury SWORD" <nurysword@.hotmail.com> wrote in message
news:16342dc6.0409170707.65ef87e1@.posting.google.c om...
> Hi All,
> We know that, "NOT FOR REPLICATION" option makes it possible for
> replication agents ,let's say, to insert rows without being
> constrained by IDENTITY restriction or check constraints(And also it
> does not change SEED value in the table).
> That means, being a replication agent is like having a PASS CARD that
> allows them to get rid of constraint checks. (But disabling
> constraints is not a solution)
> But, during conflict resolution coding, I need to INSERT some data to
> some tables (related tables), but I am facing CONSTRAINTS that merge
> agent does not face.
> Is there any way of pretending to be like merge_agent for constraint
> overcoming? THIS IS THE CORE QUESTION.
> To be more specific, is there any way of setting sessionproperty for
> replication_agent? That is: when you execute the following query in a
> normal connection;
> select sessionproperty('replication_agent')
> you get "0".
> but for replication_agent that function returns "1". Microsoft guys
> somehow set connection property for agent, and how they do it is
> scritly undocumented.
> I tried setting context_info in sysprocesses table to "8" but it did
> not work. That SP also is not enough by itself
> :sp_MSsetcontext_replagent
> Please help, if any way of pretending to be merge agent.
> PS:I event wrote an application with ReplMerg.exe process name
> guessing that SQL may know agent from its process name.
> And also please don't suggest disabling constraints because it is not
> enough, e.g. replication_agent does not affect SEED value for
> inserts that it does..
> I hope some REAL expert will hear my SCREAM.
> Thanks alot in advance,
> Nury SWORD
> MCDBA - MCSD
> Toronto
|||I finally found a merge replication guru guy. He is actually a
contractor in Toronto and selling a special component which is totally
able to pretend to be merge agent.
It is simple to use but may be a little bit expensive for start-up
companies. Since we desperately needed that feature the company just
paid for it instead of digging for weeks over weeks.
It gets SQL Server credentials as class properties and you call
ExecuteSQL method, it executes it as if it is merge agent.
For example I can execute the following SQL against my DB using that
component:
INSERT myTable (IdentityField, column1, column2) VALUES (5, 'test',
test')
and it works!!
you do not need to say SET IDENTITY_INSERT ON/OFF or disable any
constraints.
If you need to contact that merge replication consultant just send me
an email.
Nury Sword
NurySword@.hotmail.com
MCSD - MCDBA
Toronto
|||Nury,
as far as I understand, it is not possible to take the context of the merge
agent. Are you sure that this is how the component works?
Do you have to 'tell' the component what table you are working with? If so,
it might just be doing some dynamic SQL:
exec('set identity_insert tcompany on;insert into tcompany(id, companyname)
values(34,''test'');set identity_insert tcompany off')
Can you send me the details of this consultant as I'd like to ask him about
it.
Regards,
Paul Ibison (SQL Server MVP)
"Nury" <nurysword@.hotmail.com> wrote in message
news:1106863095.810873.7130@.z14g2000cwz.googlegrou ps.com...
> I finally found a merge replication guru guy. He is actually a
> contractor in Toronto and selling a special component which is totally
> able to pretend to be merge agent.
> It is simple to use but may be a little bit expensive for start-up
> companies. Since we desperately needed that feature the company just
> paid for it instead of digging for weeks over weeks.
> It gets SQL Server credentials as class properties and you call
> ExecuteSQL method, it executes it as if it is merge agent.
> For example I can execute the following SQL against my DB using that
> component:
> INSERT myTable (IdentityField, column1, column2) VALUES (5, 'test',
> test')
> and it works!!
> you do not need to say SET IDENTITY_INSERT ON/OFF or disable any
> constraints.
> If you need to contact that merge replication consultant just send me
> an email.
> Nury Sword
> NurySword@.hotmail.com
> MCSD - MCDBA
> Toronto
>

Real data type 2000 vs 2005

If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
In 2005, 56.26 is stored.
Does anyone know why?
Thx,
Don
That's just a difference in the way the particular user interface is
displaying the value. In both cases, the value that is actually stored if
you set a real variable or column to 56.26 is 56.259998321533203.
Real's cannot store every possible decimal value exactly, so SQL stores the
closest value that it can. Then it is up to the user interface to decide
how to display the value.
If you use the same user interface, (for example SQL 2000 QA) to query both
databases, you will get the same answer.
Tom
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:9807A147-795D-419B-825C-6002ADFB16A5@.microsoft.com...
> If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
> In 2005, 56.26 is stored.
> Does anyone know why?
> Thx,
> Don
>
|||> If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
> In 2005, 56.26 is stored.
> Does anyone know why?
Because this isn't about storage, I think. When you insert the data in
2000, are you selecting the data using Query Analyzer to verify what is
"stored"? And when you insert the data in 2005, are you selecting the data
using Management Studio to verify what is "stored"? What changed is the
client, how they approximate, how many decimal places are relevant, and when
and where they can and should truncate. REAL/FLOAT are labeled as
"approximate" and if you want to avoid little surprises like how your client
tools display / translate the stored value, then you should explicitly cast
to a DECIMAL with the appropriate scale and precision, or use DECIMAL in the
first place.
|||If I use QA from 2005 to query both the 2000 and the 2005 DB, the value is
56.26.
But, the real value is 56.259998321533203. So, why can't I see the real
value? Is this a feature of the 2005 QA?
Don
"Tom Cooper" wrote:

> That's just a difference in the way the particular user interface is
> displaying the value. In both cases, the value that is actually stored if
> you set a real variable or column to 56.26 is 56.259998321533203.
> Real's cannot store every possible decimal value exactly, so SQL stores the
> closest value that it can. Then it is up to the user interface to decide
> how to display the value.
> If you use the same user interface, (for example SQL 2000 QA) to query both
> databases, you will get the same answer.
> Tom
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:9807A147-795D-419B-825C-6002ADFB16A5@.microsoft.com...
>
>
|||Try
SELECT CONVERT(FLOAT, column) FROM table;
or
SELECT CONVERT(DECIMAL(12,10), column) FROM table;
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:91993EF7-EDF1-4D17-B8F1-CB3B4D996F68@.microsoft.com...
> If I use QA from 2005 to query both the 2000 and the 2005 DB, the value is
> 56.26.
> But, the real value is 56.259998321533203. So, why can't I see the real
> value? Is this a feature of the 2005 QA?
|||In 2005, if I output from QA to a txt file, the value is 56.26.
If it's stored as 56.259998 then it seems that's what should be in the txt
file.
I'm thinking that 56.26 is stored in 2005.
Don
"Aaron Bertrand [SQL Server MVP]" wrote:

> Because this isn't about storage, I think. When you insert the data in
> 2000, are you selecting the data using Query Analyzer to verify what is
> "stored"? And when you insert the data in 2005, are you selecting the data
> using Management Studio to verify what is "stored"? What changed is the
> client, how they approximate, how many decimal places are relevant, and when
> and where they can and should truncate. REAL/FLOAT are labeled as
> "approximate" and if you want to avoid little surprises like how your client
> tools display / translate the stored value, then you should explicitly cast
> to a DECIMAL with the appropriate scale and precision, or use DECIMAL in the
> first place.
>
|||Interesting. The FLOAT statement shows me the stored value is
56.259998321533203
I'll have to think about this. Thanks.
When we convert to 2005, I don't know what will happen to our applications
that use SELECT statements without FLOAT. Will have to test.
Don
"Aaron Bertrand [SQL Server MVP]" wrote:

> Try
> SELECT CONVERT(FLOAT, column) FROM table;
> or
> SELECT CONVERT(DECIMAL(12,10), column) FROM table;
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:91993EF7-EDF1-4D17-B8F1-CB3B4D996F68@.microsoft.com...
>
|||> When we convert to 2005, I don't know what will happen to our applications
> that use SELECT statements without FLOAT. Will have to test.
Is there any overwhelming reason you chose FLOAT? If you switch to DECIMAL
(with the proper scale and precision), this problem will go away. Have you
even tested your app against ? The truncation/rounding you are seeing is a
*CLIENT TOOL DISPLAY* issue. Your app should continue to retrieve the
correct value... as I have demonstrated, the correct value is what is
stored, but you are stuck on the idea that SQL Server 2005 is ruining your
data. It is not, it is just displaying a prettier version of your data.
In any case, your applications shouldn't need to change at all, since they
should be calling stored procedures, right?
|||When I'm working in SQL 6.5 or 7.0 or 2000 over the years, when I'm in Query
Analyzer, the results window shows the stored data value.
Now, suddenly, in 2005, I don't get the stored value. It makes life difficult.
I don't want a prettier version of the data. I want the stored value to show
up in Query Analyzer.
Maybe there's a way to configure Query Analyzer to show the stored values as
all other versions of SQL did?
Don
"Aaron Bertrand [SQL Server MVP]" wrote:

> Is there any overwhelming reason you chose FLOAT? If you switch to DECIMAL
> (with the proper scale and precision), this problem will go away. Have you
> even tested your app against ? The truncation/rounding you are seeing is a
> *CLIENT TOOL DISPLAY* issue. Your app should continue to retrieve the
> correct value... as I have demonstrated, the correct value is what is
> stored, but you are stuck on the idea that SQL Server 2005 is ruining your
> data. It is not, it is just displaying a prettier version of your data.
> In any case, your applications shouldn't need to change at all, since they
> should be calling stored procedures, right?
>
|||> I don't want a prettier version of the data.
I know that. I was telling you what you are getting.

> Maybe there's a way to configure Query Analyzer to show the stored values
> as
> all other versions of SQL did?
I don't know of any such configuration option. To prevent confusion for
other readers, you will want to avoid using the term "Query Analyzer" if you
are describing the query window tool within SSMS. It is no longer called
Query Analyzer, so without context, people will think you are talking about
the 2000 version of the tool.
You didn't answer any of my other questions...

Real data type 2000 vs 2005

If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
In 2005, 56.26 is stored.
Does anyone know why?
Thx,
DonThat's just a difference in the way the particular user interface is
displaying the value. In both cases, the value that is actually stored if
you set a real variable or column to 56.26 is 56.259998321533203.
Real's cannot store every possible decimal value exactly, so SQL stores the
closest value that it can. Then it is up to the user interface to decide
how to display the value.
If you use the same user interface, (for example SQL 2000 QA) to query both
databases, you will get the same answer.
Tom
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:9807A147-795D-419B-825C-6002ADFB16A5@.microsoft.com...
> If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
> In 2005, 56.26 is stored.
> Does anyone know why?
> Thx,
> Don
>|||> If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
> In 2005, 56.26 is stored.
> Does anyone know why?
Because this isn't about storage, I think. When you insert the data in
2000, are you selecting the data using Query Analyzer to verify what is
"stored"? And when you insert the data in 2005, are you selecting the data
using Management Studio to verify what is "stored"? What changed is the
client, how they approximate, how many decimal places are relevant, and when
and where they can and should truncate. REAL/FLOAT are labeled as
"approximate" and if you want to avoid little surprises like how your client
tools display / translate the stored value, then you should explicitly cast
to a DECIMAL with the appropriate scale and precision, or use DECIMAL in the
first place.|||If I use QA from 2005 to query both the 2000 and the 2005 DB, the value is
56.26.
But, the real value is 56.259998321533203. So, why can't I see the real
value? Is this a feature of the 2005 QA?
Don
"Tom Cooper" wrote:
> That's just a difference in the way the particular user interface is
> displaying the value. In both cases, the value that is actually stored if
> you set a real variable or column to 56.26 is 56.259998321533203.
> Real's cannot store every possible decimal value exactly, so SQL stores the
> closest value that it can. Then it is up to the user interface to decide
> how to display the value.
> If you use the same user interface, (for example SQL 2000 QA) to query both
> databases, you will get the same answer.
> Tom
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:9807A147-795D-419B-825C-6002ADFB16A5@.microsoft.com...
> > If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
> >
> > In 2005, 56.26 is stored.
> >
> > Does anyone know why?
> >
> > Thx,
> > Don
> >
> >
>
>|||Try
SELECT CONVERT(FLOAT, column) FROM table;
or
SELECT CONVERT(DECIMAL(12,10), column) FROM table;
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:91993EF7-EDF1-4D17-B8F1-CB3B4D996F68@.microsoft.com...
> If I use QA from 2005 to query both the 2000 and the 2005 DB, the value is
> 56.26.
> But, the real value is 56.259998321533203. So, why can't I see the real
> value? Is this a feature of the 2005 QA?|||In 2005, if I output from QA to a txt file, the value is 56.26.
If it's stored as 56.259998 then it seems that's what should be in the txt
file.
I'm thinking that 56.26 is stored in 2005.
Don
"Aaron Bertrand [SQL Server MVP]" wrote:
> > If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
> >
> > In 2005, 56.26 is stored.
> >
> > Does anyone know why?
> Because this isn't about storage, I think. When you insert the data in
> 2000, are you selecting the data using Query Analyzer to verify what is
> "stored"? And when you insert the data in 2005, are you selecting the data
> using Management Studio to verify what is "stored"? What changed is the
> client, how they approximate, how many decimal places are relevant, and when
> and where they can and should truncate. REAL/FLOAT are labeled as
> "approximate" and if you want to avoid little surprises like how your client
> tools display / translate the stored value, then you should explicitly cast
> to a DECIMAL with the appropriate scale and precision, or use DECIMAL in the
> first place.
>|||Interesting. The FLOAT statement shows me the stored value is
56.259998321533203
I'll have to think about this. Thanks.
When we convert to 2005, I don't know what will happen to our applications
that use SELECT statements without FLOAT. Will have to test.
Don
"Aaron Bertrand [SQL Server MVP]" wrote:
> Try
> SELECT CONVERT(FLOAT, column) FROM table;
> or
> SELECT CONVERT(DECIMAL(12,10), column) FROM table;
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:91993EF7-EDF1-4D17-B8F1-CB3B4D996F68@.microsoft.com...
> > If I use QA from 2005 to query both the 2000 and the 2005 DB, the value is
> > 56.26.
> >
> > But, the real value is 56.259998321533203. So, why can't I see the real
> > value? Is this a feature of the 2005 QA?
>|||> When we convert to 2005, I don't know what will happen to our applications
> that use SELECT statements without FLOAT. Will have to test.
Is there any overwhelming reason you chose FLOAT? If you switch to DECIMAL
(with the proper scale and precision), this problem will go away. Have you
even tested your app against ? The truncation/rounding you are seeing is a
*CLIENT TOOL DISPLAY* issue. Your app should continue to retrieve the
correct value... as I have demonstrated, the correct value is what is
stored, but you are stuck on the idea that SQL Server 2005 is ruining your
data. It is not, it is just displaying a prettier version of your data.
In any case, your applications shouldn't need to change at all, since they
should be calling stored procedures, right?|||When I'm working in SQL 6.5 or 7.0 or 2000 over the years, when I'm in Query
Analyzer, the results window shows the stored data value.
Now, suddenly, in 2005, I don't get the stored value. It makes life difficult.
I don't want a prettier version of the data. I want the stored value to show
up in Query Analyzer.
Maybe there's a way to configure Query Analyzer to show the stored values as
all other versions of SQL did?
Don
"Aaron Bertrand [SQL Server MVP]" wrote:
> > When we convert to 2005, I don't know what will happen to our applications
> > that use SELECT statements without FLOAT. Will have to test.
> Is there any overwhelming reason you chose FLOAT? If you switch to DECIMAL
> (with the proper scale and precision), this problem will go away. Have you
> even tested your app against ? The truncation/rounding you are seeing is a
> *CLIENT TOOL DISPLAY* issue. Your app should continue to retrieve the
> correct value... as I have demonstrated, the correct value is what is
> stored, but you are stuck on the idea that SQL Server 2005 is ruining your
> data. It is not, it is just displaying a prettier version of your data.
> In any case, your applications shouldn't need to change at all, since they
> should be calling stored procedures, right?
>|||> I don't want a prettier version of the data.
I know that. I was telling you what you are getting.
> Maybe there's a way to configure Query Analyzer to show the stored values
> as
> all other versions of SQL did?
I don't know of any such configuration option. To prevent confusion for
other readers, you will want to avoid using the term "Query Analyzer" if you
are describing the query window tool within SSMS. It is no longer called
Query Analyzer, so without context, people will think you are talking about
the 2000 version of the tool.
You didn't answer any of my other questions...|||> But, the real value is 56.259998321533203. So, why can't I see the real
> value? Is this a feature of the 2005 QA?
This is indeed a presentation issue. I.e., it is a question of the tool with which you are executing
the queries.
When the data leaves SQL Server to travel to the client application (Management Studio, your app
etc), it is binary data. Now, it isn't very user-friendly for such a tool to present 0s and 1s only.
So the tool does some formatting based on what data type of the data - in order to make that value
human-readable. So, what you have encountered is a design decision someone tool regarding how SSMS
is to display real. If you don't like it, you can suggest improvements changes, to
http://connect.microsoft.com/sqlserver.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:91993EF7-EDF1-4D17-B8F1-CB3B4D996F68@.microsoft.com...
> If I use QA from 2005 to query both the 2000 and the 2005 DB, the value is
> 56.26.
> But, the real value is 56.259998321533203. So, why can't I see the real
> value? Is this a feature of the 2005 QA?
> Don
> "Tom Cooper" wrote:
>> That's just a difference in the way the particular user interface is
>> displaying the value. In both cases, the value that is actually stored if
>> you set a real variable or column to 56.26 is 56.259998321533203.
>> Real's cannot store every possible decimal value exactly, so SQL stores the
>> closest value that it can. Then it is up to the user interface to decide
>> how to display the value.
>> If you use the same user interface, (for example SQL 2000 QA) to query both
>> databases, you will get the same answer.
>> Tom
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:9807A147-795D-419B-825C-6002ADFB16A5@.microsoft.com...
>> > If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
>> >
>> > In 2005, 56.26 is stored.
>> >
>> > Does anyone know why?
>> >
>> > Thx,
>> > Don
>> >
>> >
>>

Real beginners question

I have a datagrid that is used to insert values into a table. Beforethe insert occurs I want to be able to check that there are noidentical values already in the table. eg. I am inserting names, so amlooking for last name duplicates only.
If a duplicate is found, it should return an error message to the page.My question is, can I do this duplicate checking via SQL script ie. IFEXISTS etc... and have the error message returned from there, or do Ineed to grab a dataset of the table and look for duplicates before evengoing as far as the database with the new value to be inerted?
If the best approach is to catch the duplicate error after executingthe SQL script, how do I grab the error and return it to the page withthe datagrid? I'm sure this is pretty basic, but I am only just nowgetting into it.
SQL Server 2000 is being used here.
Thanks!

This was recently handled here, and a link to an article on AspAlliance was provided. It should help you too:

http://aspalliance.com/687