Coexisting SQL Reader & UPDATE

In my current application, I have an administration form that fills in labels and checked states via data entered into the database using a similar user input field. What the admin page does is it first lists all the record names in a listview, then on select, it fills in the form based on what the records contain. This means labels text change, and check states change based on the string "True" or "False". This was done using the SQL Reader command.

Within the same form, the read checkboxes are editable via the admin. When the admin edits the controls, he will click the update button at the bottom and the database will UPDATE .. WHERE UserName = (Scalar for ListBox1.SelectedValue)

I've used the exact same UPDATE command in my form for the user, except the only difference was @. the WHERE clause-- I had it updating based on a GUID. I know my SQL statement is correct, but it just won't update the data.

Is it possible that the READER, which starts (and closes) on pageload cannot coexist within the same form as the UPDATE code?

My code is incredibly long, so for the purposes of a short post I'm not including any bit of it-- but if you would like to see it, just let me know.

Why do you need to use a GUID? Why not use an identity integer primary key? Update will then be a simple stored procedure and a bit of wrapper code.

|||

I thought that too, but the GUID's working fine on the other pages-- thats not what I have a problem with. I'm modifying an existing structure, and if the GUID scheme works, I'd rather not make more work for myself.

On the form in question, GUID doesn't even exist as an instance on it. ListBox1.SelectedValue is what I'm trying to get as my WHERE arguement-- and it does pass, no errors generated, but the form does not update.

|||

When you run it in debug mode with your breakpoints set, where does it stop at? When you click update does it call to execute your sql statement?

|||

when I set breakpoints, I have it set at the NonQuery, and everything inputs correctly: my SQL statement is properly formed down to the ListBox selected value translating into an EXISTING column name (eg, Current user is Test, Test is databound to listbox. Test's data is displayed, modified, then "updated". Break. SQL statement reads 'UPDATE Login SET ... WHERE UserName = 'Test', properly formed and existing), the ExecuteNonQuery goes through, there are no errors generated. and the redirect coded after the update processes. Display test's data again, and no changes were made.

|||

Figured it out.

I made two functions, "ReadMe()" and "UpdateMe()", and called them on load and on button click. However, although all my statements were correct, ButtonClick calls a postback, and with the reader executing onpostback, the database automatically rolled back the variables.

Solution: I changed the ReadMe() function to execute on ListBox1.SelectedIndex changed, and the update was successful.

Whoops!

Code Side Tower of Babel Update Problem.

I have some code that implements some business logic that sits on top of an
SQL Server Express 2005 DB.
The database is fine, but I have found a potential issue with the updates
that affects my code, and also the functionality of the .Net Dataset, that I
am looking for a way around.
My code is required to work with a large dataset of multiple related tables,
that are only committed on calling the Save() function. This functionality
works fine, but I have found an issue that is perfectly legal in the business
logic, but will fail when attempting to store to the database due to unique
restrictions.
I have a table:
ID Name
1 AAA
2 BBB
The column Name is a unique column.
In the business logic, I can do the following:
* Rename Item[1] to "--"
* Rename Item[2] to "AAA"
* Rename Item[1] to "BBB"
* Call Save()
This is completely valid at the Dataset side, as no unique restriction is
breached. However, at this point, the program will begin iterating through
the updated value, and the following SQL is generated by the dataset:
UPDATE tbl SET Name = 'BBB' WHERE ID = 1
UPDATE tbl SET Name = 'AAA' WHERE ID = 2
The first item executed will be
UPDATE tbl SET Name = 'BBB' WHERE ID = 1
On Table
ID Name
1 AAA
2 BBB
The attempt to update the table to state
ID Name
1 BBB
2 BBB
Will fail, regardless of the fact that the next update will restore the
unique value status.
Is there any way to enable a before and after test on a total commit? Say by
using transactions?
This is a critical issue for my system.
Regards
TrisTris
Do you have an unique constraint on (id,name) ?
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A5BD2ECD-557E-4EF1-A6E2-7208B03CD737@.microsoft.com...
>I have some code that implements some business logic that sits on top of an
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that
> I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related
> tables,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the
> business
> logic, but will fail when attempting to store to the database due to
> unique
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>|||"Uri Dimant" wrote:
> Tris
> Do you have an unique constraint on (id,name) ?
Yes, in this instance the column Name is unique.
Although this is only an example, it could be applied to any unique set of
fields where this situation could arise.|||> This is completely valid at the Dataset side, as no unique restriction is
> breached.
That's because your Dataset constraints don't match the database. If you
had the same constraints on both the Dataset and database, you would get the
error when the change was made to the dataset rather than when data is later
saved to the database.
> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
I believe you want a 'deferred constraint checking' feature so that
constraint's aren't enforced until COMMIT. Unfortunately, this feature does
not yet exist in SQL Server. If this is important to you, consider
submitting this as a feature request via Connect:
https://connect.microsoft.com/SQLServer
One workaround is to remove the database unique constraint and implement the
unique restriction in your Save method by querying to ensure modified values
are unique before you commit. Another approach is to first delete all
modified rows and then insert with the new values. However, that can be
problematic if you also have foreign key constraints.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A5BD2ECD-557E-4EF1-A6E2-7208B03CD737@.microsoft.com...
>I have some code that implements some business logic that sits on top of an
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that
> I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related
> tables,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the
> business
> logic, but will fail when attempting to store to the database due to
> unique
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>|||Tris
>Yes, in this instance the column Name is unique.
Pehaps you need something like that
IF NOT EXISTS (SELECT * FROM Table WHERE Name =@.Name)
UPDATE Table SET name =@.name
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:0EF2AC17-1F9E-4F7B-86DB-3A4B6A809DC1@.microsoft.com...
>
> "Uri Dimant" wrote:
>> Tris
>> Do you have an unique constraint on (id,name) ?
>
> Yes, in this instance the column Name is unique.
> Although this is only an example, it could be applied to any unique set of
> fields where this situation could arise.|||"Dan Guzman" wrote:
> > This is completely valid at the Dataset side, as no unique restriction is
> > breached.
> That's because your Dataset constraints don't match the database. If you
> had the same constraints on both the Dataset and database, you would get the
> error when the change was made to the dataset rather than when data is later
> saved to the database.
The unique restrictions are enforced in the dataset, but i use a tower of
babel switch,
X = 'X'
Y = 'Y'
Save
X = 'Q'
Y = 'X'
X = 'Y'
Save
The constraints on the dataset are never breached.
I can't delete the rows, it would cause far to many headaches. And
dissabling the restrictions would be a pain.
However, i suppose i could create a validate stored procedure...
What do you think of this:
SPValidateTable?()
#TempTable
(
VarChar Name,
Int Count
)
TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
Return
If the result != 0
Throw exception.
It's a hack i know... but it could work.
How could this be implemented before / After commiting a transaction, so
that it could fail a transaction if the method failed? And how can it's scope
be handled in relation to the transaction?
Tris|||Tris wrote:
> I have some code that implements some business logic that sits on top of an
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related tables,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the business
> logic, but will fail when attempting to store to the database due to unique
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>
Assuming you know the two (or more) ID's that you want to swap:
UPDATE tbl
SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> What do you think of this:
> SPValidateTable?()
> #TempTable
> (
> VarChar Name,
> Int Count
> )
> TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
> SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
> Return
If your table is small enough that you don't mind checking the entire table,
I would suggest that you ditch the temp table:
CREATE PROC SPValidateTable
AS
SELECT COUNT(*) AS DuplicateNames
FROM (
SELECT COUNT(*) AS Duplicates
FROM dbo.MyTable
GROUP BY Name
HAVING COUNT(*) > 1) AS Dups
RETURN
GO
Alternatively, you can selectively check only those names (or dataset
DataRows) that were changed:
CREATE PROC SPValidateName
@.Name varchar(100)
AS
SELECT COUNT(*) AS DuplicateNames
FROM (
SELECT COUNT(*) AS Duplicates
FROM dbo.MyTable
WHERE Name = @.Name
GROUP BY Name
HAVING COUNT(*) > 1) AS Dups
RETURN
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:06E8D3D0-C416-46E4-9369-21405864428C@.microsoft.com...
>
> "Dan Guzman" wrote:
>> > This is completely valid at the Dataset side, as no unique restriction
>> > is
>> > breached.
>> That's because your Dataset constraints don't match the database. If you
>> had the same constraints on both the Dataset and database, you would get
>> the
>> error when the change was made to the dataset rather than when data is
>> later
>> saved to the database.
> The unique restrictions are enforced in the dataset, but i use a tower of
> babel switch,
> X = 'X'
> Y = 'Y'
> Save
> X = 'Q'
> Y = 'X'
> X = 'Y'
> Save
> The constraints on the dataset are never breached.
> I can't delete the rows, it would cause far to many headaches. And
> dissabling the restrictions would be a pain.
> However, i suppose i could create a validate stored procedure...
> What do you think of this:
> SPValidateTable?()
> #TempTable
> (
> VarChar Name,
> Int Count
> )
> TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
> SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
> Return
>
> If the result != 0
> Throw exception.
> It's a hack i know... but it could work.
> How could this be implemented before / After commiting a transaction, so
> that it could fail a transaction if the method failed? And how can it's
> scope
> be handled in relation to the transaction?
> Tris|||Tracy McKibben wrote:
> Tris wrote:
>> I have some code that implements some business logic that sits on top
>> of an SQL Server Express 2005 DB.
>> The database is fine, but I have found a potential issue with the
>> updates that affects my code, and also the functionality of the .Net
>> Dataset, that I am looking for a way around.
>> My code is required to work with a large dataset of multiple related
>> tables, that are only committed on calling the Save() function. This
>> functionality works fine, but I have found an issue that is perfectly
>> legal in the business logic, but will fail when attempting to store to
>> the database due to unique restrictions.
>> I have a table:
>> ID Name
>> 1 AAA
>> 2 BBB
>> The column Name is a unique column.
>> In the business logic, I can do the following:
>> * Rename Item[1] to "--"
>> * Rename Item[2] to "AAA"
>> * Rename Item[1] to "BBB"
>> * Call Save()
>> This is completely valid at the Dataset side, as no unique restriction
>> is breached. However, at this point, the program will begin iterating
>> through the updated value, and the following SQL is generated by the
>> dataset:
>> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
>> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
>> The first item executed will be UPDATE tbl SET Name = 'BBB' WHERE ID = 1
>> On Table
>> ID Name
>> 1 AAA
>> 2 BBB
>> The attempt to update the table to state
>> ID Name
>> 1 BBB
>> 2 BBB
>> Will fail, regardless of the fact that the next update will restore
>> the unique value status.
>> Is there any way to enable a before and after test on a total commit?
>> Say by using transactions?
>> This is a critical issue for my system.
>> Regards
>> Tris
> Assuming you know the two (or more) ID's that you want to swap:
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
>
I forgot the WHERE clause, although on a small table, it should run fine
without one.
UPDATE tbl
SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
WHERE ID IN (1, 2)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Assuming you know the two (or more) ID's that you want to swap:
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Hi Tracy,
Thanks, that's interesting, but unfortunately, it's done using the MS
Dataset update mechanism, so if two elements are effectively swapped in code,
then they are not aware of each other.
Tris|||Hi Dan,
Thanks, yes that makes sense.
Can this be run inside a transaction to check before i call the Commit?
I'm not sure how the scope will affect things, i mean, a transaction can't
see what other transactions are doing, so if i ran two concurrent
transactions, and ran the test first, it passed and then i commitied, the
second one may fail when the test runs?
But as far as i can see it seems viable.
Tris|||> Can this be run inside a transaction to check before i call the Commit?
Yes.
> I'm not sure how the scope will affect things, i mean, a transaction can't
> see what other transactions are doing, so if i ran two concurrent
> transactions, and ran the test first, it passed and then i commitied, the
> second one may fail when the test runs?
You are wise to consider the potential concurrency issues. If multiple
users call Save at the same time, it is likely that a deadlock will occur.
Consider the following scenario with the default read committed transaction
isolation level (assuming the validation proc scans all rows).
- User 1 changes row A and saves
- User 2 changes row B and saves
- User 1 Save method updates row A
- User 2 Save method updates row B
- User 1 Save method executes validation proc, which is blocked when the
uncommitted row B is touched
- User 2 Save method executes validation proc, which is blocked when the
uncommitted row A is touched
- User 1 is blocked by User 2 and User 2 is blocked by User 1. SQL Server
chooses one of the users to be the deadlock victim
One way to address the problem is to specify a TABLOCK hint on the updates.
This will have the effect of serializing the updates and validation.
Whether or not the performance hit is acceptable depends on your situation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:C71FDF05-5E8B-42E8-955C-D17EC6BC6A42@.microsoft.com...
> Hi Dan,
> Thanks, yes that makes sense.
> Can this be run inside a transaction to check before i call the Commit?
> I'm not sure how the scope will affect things, i mean, a transaction can't
> see what other transactions are doing, so if i ran two concurrent
> transactions, and ran the test first, it passed and then i commitied, the
> second one may fail when the test runs?
> But as far as i can see it seems viable.
> Tris|||Hello, Tracy
Without the WHERE clause, your UPDATE statement would set the Name to
NULL for the other rows, so it's a very important clause (but I guess a
well designed table would not allow nulls in that column).
Razvan
Tracy McKibben wrote:
> Tracy McKibben wrote:
> >
> > Assuming you know the two (or more) ID's that you want to swap:
> >
> > UPDATE tbl
> > SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
> >
> >
> I forgot the WHERE clause, although on a small table, it should run fine
> without one.
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
> WHERE ID IN (1, 2)
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Code Side Tower of Babel Update Problem.

I have some code that implements some business logic that sits on top of an
SQL Server Express 2005 DB.
The database is fine, but I have found a potential issue with the updates
that affects my code, and also the functionality of the .Net Dataset, that I
am looking for a way around.
My code is required to work with a large dataset of multiple related tables,
that are only committed on calling the Save() function. This functionality
works fine, but I have found an issue that is perfectly legal in the busines
s
logic, but will fail when attempting to store to the database due to unique
restrictions.
I have a table:
ID Name
1 AAA
2 BBB
The column Name is a unique column.
In the business logic, I can do the following:
* Rename Item[1] to "--"
* Rename Item[2] to "AAA"
* Rename Item[1] to "BBB"
* Call Save()
This is completely valid at the Dataset side, as no unique restriction is
breached. However, at this point, the program will begin iterating through
the updated value, and the following SQL is generated by the dataset:
UPDATE tbl SET Name = 'BBB' WHERE ID = 1
UPDATE tbl SET Name = 'AAA' WHERE ID = 2
The first item executed will be
UPDATE tbl SET Name = 'BBB' WHERE ID = 1
On Table
ID Name
1 AAA
2 BBB
The attempt to update the table to state
ID Name
1 BBB
2 BBB
Will fail, regardless of the fact that the next update will restore the
unique value status.
Is there any way to enable a before and after test on a total commit? Say by
using transactions?
This is a critical issue for my system.
Regards
Tris"Uri Dimant" wrote:

> Tris
> Do you have an unique constraint on (id,name) ?
Yes, in this instance the column Name is unique.
Although this is only an example, it could be applied to any unique set of
fields where this situation could arise.|||> This is completely valid at the Dataset side, as no unique restriction is
> breached.
That's because your Dataset constraints don't match the database. If you
had the same constraints on both the Dataset and database, you would get the
error when the change was made to the dataset rather than when data is later
saved to the database.

> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
I believe you want a 'deferred constraint checking' feature so that
constraint's aren't enforced until COMMIT. Unfortunately, this feature does
not yet exist in SQL Server. If this is important to you, consider
submitting this as a feature request via Connect:
https://connect.microsoft.com/SQLServer
One workaround is to remove the database unique constraint and implement the
unique restriction in your Save method by querying to ensure modified values
are unique before you commit. Another approach is to first delete all
modified rows and then insert with the new values. However, that can be
problematic if you also have foreign key constraints.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A5BD2ECD-557E-4EF1-A6E2-7208B03CD737@.microsoft.com...
>I have some code that implements some business logic that sits on top of an
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that
> I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related
> tables,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the
> business
> logic, but will fail when attempting to store to the database due to
> unique
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>|||Tris
>Yes, in this instance the column Name is unique.
Pehaps you need something like that
IF NOT EXISTS (SELECT * FROM Table WHERE Name =@.Name)
UPDATE Table SET name =@.name
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:0EF2AC17-1F9E-4F7B-86DB-3A4B6A809DC1@.microsoft.com...
>
> "Uri Dimant" wrote:
>
>
> Yes, in this instance the column Name is unique.
> Although this is only an example, it could be applied to any unique set of
> fields where this situation could arise.|||"Dan Guzman" wrote:

> That's because your Dataset constraints don't match the database. If you
> had the same constraints on both the Dataset and database, you would get t
he
> error when the change was made to the dataset rather than when data is lat
er
> saved to the database.
The unique restrictions are enforced in the dataset, but i use a tower of
babel switch,
X = 'X'
Y = 'Y'
Save
X = 'Q'
Y = 'X'
X = 'Y'
Save
The constraints on the dataset are never breached.
I can't delete the rows, it would cause far to many headaches. And
dissabling the restrictions would be a pain.
However, i suppose i could create a validate stored procedure...
What do you think of this:
SPValidateTable?()
#TempTable
(
VarChar Name,
Int Count
)
TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
Return
If the result != 0
Throw exception.
It's a hack i know... but it could work.
How could this be implemented before / After commiting a transaction, so
that it could fail a transaction if the method failed? And how can it's scop
e
be handled in relation to the transaction?
Tris|||Tris wrote:
> I have some code that implements some business logic that sits on top of a
n
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that
I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related table
s,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the busin
ess
> logic, but will fail when attempting to store to the database due to uniqu
e
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say
by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>
Assuming you know the two (or more) ID's that you want to swap:
UPDATE tbl
SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> What do you think of this:
> SPValidateTable?()
> #TempTable
> (
> VarChar Name,
> Int Count
> )
> TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
> SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
> Return
If your table is small enough that you don't mind checking the entire table,
I would suggest that you ditch the temp table:
CREATE PROC SPValidateTable
AS
SELECT COUNT(*) AS DuplicateNames
FROM (
SELECT COUNT(*) AS Duplicates
FROM dbo.MyTable
GROUP BY Name
HAVING COUNT(*) > 1) AS Dups
RETURN
GO
Alternatively, you can selectively check only those names (or dataset
DataRows) that were changed:
CREATE PROC SPValidateName
@.Name varchar(100)
AS
SELECT COUNT(*) AS DuplicateNames
FROM (
SELECT COUNT(*) AS Duplicates
FROM dbo.MyTable
WHERE Name = @.Name
GROUP BY Name
HAVING COUNT(*) > 1) AS Dups
RETURN
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:06E8D3D0-C416-46E4-9369-21405864428C@.microsoft.com...
>
> "Dan Guzman" wrote:
>
> The unique restrictions are enforced in the dataset, but i use a tower of
> babel switch,
> X = 'X'
> Y = 'Y'
> Save
> X = 'Q'
> Y = 'X'
> X = 'Y'
> Save
> The constraints on the dataset are never breached.
> I can't delete the rows, it would cause far to many headaches. And
> dissabling the restrictions would be a pain.
> However, i suppose i could create a validate stored procedure...
> What do you think of this:
> SPValidateTable?()
> #TempTable
> (
> VarChar Name,
> Int Count
> )
> TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
> SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
> Return
>
> If the result != 0
> Throw exception.
> It's a hack i know... but it could work.
> How could this be implemented before / After commiting a transaction, so
> that it could fail a transaction if the method failed? And how can it's
> scope
> be handled in relation to the transaction?
> Tris|||Tracy McKibben wrote:
> Tris wrote:
> Assuming you know the two (or more) ID's that you want to swap:
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
>
I forgot the WHERE clause, although on a small table, it should run fine
without one.
UPDATE tbl
SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
WHERE ID IN (1, 2)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Assuming you know the two (or more) ID's that you want to swap:
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Hi Tracy,
Thanks, that's interesting, but unfortunately, it's done using the MS
Dataset update mechanism, so if two elements are effectively swapped in code
,
then they are not aware of each other.
Tris|||Hi Dan,
Thanks, yes that makes sense.
Can this be run inside a transaction to check before i call the Commit?
I'm not sure how the scope will affect things, i mean, a transaction can't
see what other transactions are doing, so if i ran two concurrent
transactions, and ran the test first, it passed and then i commitied, the
second one may fail when the test runs?
But as far as i can see it seems viable.
Tris

Code Problem - Updating parameter

I want to update a parameter in order to know whether a certain type of row
has been printed so that I can suppress it later. the code below generates
an error " Reference to a non-shared member requires an object reference" .
How do I pass in /reference my parameter so that I can set the value? Any
help greatly appreciated
Public Function PrintCensus() As Integer
if Parameters!PrintCensus.Value = 0 Then
Parameters!PrintCensus.Value = 1
Return 0
end if
return 1
End FunctionWhat you have to do is:
1. Define your function as:
> Public Function PrintCensus(PrintCensusValue as integer) As Integer
>
> if PrintCensusValue= 0 Then
> Return 1
> else
Return 0
> end if
> End Function
2. Call your function from expresion field as:
=Code.PrintCensus(Parameters!PrintCensus.Value)
Hope this helps,
Mónica
"Peter Feakins" <PeterFeakins@.discussions.microsoft.com> escribió en el
mensaje news:71D8DF3A-A67F-41CB-8B8B-CE71E0FF63E4@.microsoft.com...
>I want to update a parameter in order to know whether a certain type of row
> has been printed so that I can suppress it later. the code below
> generates
> an error " Reference to a non-shared member requires an object reference"
> .
> How do I pass in /reference my parameter so that I can set the value? Any
> help greatly appreciated
> Public Function PrintCensus() As Integer
> if Parameters!PrintCensus.Value = 0 Then
> Parameters!PrintCensus.Value = 1
> Return 0
> end if
> return 1
> End Function|||apparently you cannot change the value of a parameter after it is set prior
to report execution.
"Peter Feakins" wrote:
> I want to update a parameter in order to know whether a certain type of row
> has been printed so that I can suppress it later. the code below generates
> an error " Reference to a non-shared member requires an object reference" .
> How do I pass in /reference my parameter so that I can set the value? Any
> help greatly appreciated
> Public Function PrintCensus() As Integer
> if Parameters!PrintCensus.Value = 0 Then
> Parameters!PrintCensus.Value = 1
> Return 0
> end if
> return 1
> End Function

Coalesce is not working and Update is no updating

My code worked a few weeks ago and has since stop working, reasons are totally not clear to me as to what happended.

However, I need to get this thing up and running. It will not longer Coalesce data entry. Iran the debugger and the correct values are in the specified objects as if it is the first time I run the page for a person it will input data, but not of subsequent data entry attempts.

My code: ( I trully appreciate your help)

Ayo

'Using "With/End With" pass content to columns from text objects and datatime variables (see above)

With cmdCommentUpdate

.Parameters.Add(New SqlClient.SqlParameter("@.UserID", ddlEmployeeSuperCmt.SelectedValue))

.Parameters.Add(New SqlClient.SqlParameter("@.Today", bDate))

.Parameters.Add(New SqlClient.SqlParameter("@.Comments", dtToday &" " & UCase(userNamedbInsert) &" " & txtComment.Text &" " & vbCrLf))

.Parameters.Add(New SqlClient.SqlParameter("@.CommenterLogon", UCase(userNamedbInsert)))

.Parameters.Add(New SqlClient.SqlParameter("@.CommentDate", dtNow))

'Establish the type of commandy object

.CommandType = CommandType.Text

'Pass the Update nonquery statement to the commandText object previously instantiated

.CommandText ="UPDATE ATTTble" & _

" SET Comments = COALESCE(Comments, '') + @.Comments, CommenterLogon = @.CommenterLogon, CommentDate = @.CommentDate" & _

" WHERE (UserID = @.UserID) AND (Today = '" & lblDate.Text &"') "

EndWith

Looking at your code if there is an existing comment field for the record the comment field will never get updated. You might want to try,

set Comments =coalesce(@.comments, comments)

This will first see if the value passed in is null, if so it just keeps the existing value otherwise it updates.

Let me know if this helps, or if i am not understanding your question.

|||

set Comments =coalesce(@.comments, comments)

The code continues to over write what is in the database instead of appending the comments.

Ayo

|||

Possibly a date conversion problem:

With cmdCommentUpdate

.Parameters.Add(New SqlClient.SqlParameter("@.UserID", ddlEmployeeSuperCmt.SelectedValue))

.Parameters.Add(New SqlClient.SqlParameter("@.Today", bDate))

.Parameters.Add(New SqlClient.SqlParameter("@.Comments", dtToday &" " & UCase(userNamedbInsert) &" " & txtComment.Text &" " & vbCrLf))

.Parameters.Add(New SqlClient.SqlParameter("@.CommenterLogon", UCase(userNamedbInsert)))

.Parameters.Add(New SqlClient.SqlParameter("@.CommentDate", dtNow))

.Parameters.Add("@.Today",SqlDbType.DateTime).Value=lblToday.Text

'Establish the type of commandy object

.CommandType = CommandType.Text

'Pass the Update nonquery statement to the commandText object previously instantiated

.CommandText ="UPDATE ATTTble" & _

" SET Comments = COALESCE(Comments, '') + @.Comments, CommenterLogon = @.CommenterLogon, CommentDate = @.CommentDate" & _

" WHERE (UserID = @.UserID) AND (Today = @.Today) "

EndWith

|||

If that does not work, then you can try (Although, this should have absolutely no effect):

SET Comments = ISNULL(Comments,'') + @.Comments

|||

For some strange reason, unknown to me, the oroginal code started working.

Thanks for all your suggestions.

Ayomide

Clustered Index Update

In my estimated execution plan for a UPDATE it says I have
a 55% cost to do a "Clustered Index Update/Update". What
is odd is that I am not updating either column in the
PK/Clustered Index. Now I know this is the estimated
execution plan, but why does it say this? The real truth
will be told when I run the update statement, but I'm just
wondering about this mis-read of the execution.Can you post the update? Sounds interesting. Also, can you post the pre-run
plan and the post run plan?
Remember that any update to any column in the table requires an update to
the clustered index, since all columns are part of the index.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Mets Fan" <anonymous@.discussions.microsoft.com> wrote in message
news:126401c54102$af790090$a601280a@.phx.gbl...
> In my estimated execution plan for a UPDATE it says I have
> a 55% cost to do a "Clustered Index Update/Update". What
> is odd is that I am not updating either column in the
> PK/Clustered Index. Now I know this is the estimated
> execution plan, but why does it say this? The real truth
> will be told when I run the update statement, but I'm just
> wondering about this mis-read of the execution.|||There is not data presently so the statistics reflect
that, perhaps that could be the issue. But as you asked,
here is the resultset of SET SHOWPLAN_ALL. I exported it
to excel and then saved as CSV. You will have to import
and set the delimiter to a comma.
"UPDATE a SET IncntvRevWAncil =
b.totalQualRevOrg , IncntvRevWOAncil =
b.totalQualRevNew FROM dbo.CustomerProfileMonthly
a JOIN DB2.dbo.t_Detail b ON
a.CustNumber = b.CustNumber AND
a.ControlingDate = b.ControlingDate AND
a.ControlingDate = CAST('20040701' AS
DATETIME)" ,5,1,0,NULL,NULL,1,NULL,1,NULL,NULL,NULL
,3.81E-
02,NULL,NULL,UPDATE,0,NULL
" |--Clustered Index Update(OBJECT:([DB1].[dbo].
[CustomerProfileMonthly].[MerchantProfileMonthly_PK]), SET:
([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
[CustomerProfileMonthly].[IncntvRevWAncil]=
[Expr2860]))",5,2,1,Clustered Index Update,Update,"OBJECT:
([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK]), SET:
([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
[CustomerProfileMonthly].[IncntvRevWAncil]=
[Expr2860])",NULL,1,1.68E-02,0.000001,61,3.81E-
02,NULL,NULL,PLAN_ROW,0,1
" |--Compute Scalar(DEFINE:([Expr2860]=Convert
([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
([t_detail_2004_07].[totalQualRevNew])))",5,3,2,Compute
Scalar,Compute Scalar,"DEFINE:([Expr2860]=Convert
([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
([t_detail_2004_07].[totalQualRevNew]))","[Expr2860]
=Convert([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]
=Convert([t_detail_2004_07].
[totalQualRevNew])",1,0,0.0000001,53,2.13E-02,"[Bmk1000],
[Expr2860], [Expr2861]",NULL,PLAN_ROW,0,1
|--Top(ROWCOUNT est
0),5,4,3,Top,Top,NULL,NULL,1,0,0.0000001,53,2.13E-
02,"[Bmk1000], [t_detail_2004_07].[totalQualRevOrg],
[t_detail_2004_07].[totalQualRevNew]",NULL,PLAN_ROW,0,1
|--Sort(DISTINCT ORDER BY:([Bmk1000]
ASC)),5,5,4,Sort,Distinct Sort,DISTINCT ORDER BY:
([Bmk1000] ASC),NULL,1,1.13E-02,1.00E-
04,53,0.02130264,"[Bmk1000], [t_detail_2004_07].
[totalQualRevOrg], [t_detail_2004_07].
[totalQualRevNew]",NULL,PLAN_ROW,0,1
" |--Compute Scalar(DEFINE:
([t_detail_2004_07].[totalQualRevOrg]=[t_detail_2004_07].
[totalQualRevOrg], [t_detail_2004_07].[totalQualRevNew]=
[t_detail_2004_07].[totalQualRevNew]))",5,6,5,Compute
Scalar,Compute Scalar,"DEFINE:([t_detail_2004_07].
[totalQualRevOrg]=[t_detail_2004_07].[totalQualRevOrg],
[t_detail_2004_07].[totalQualRevNew]=[t_detail_2004_07].
[totalQualRevNew])","[t_detail_2004_07].[totalQualRevOrg]=
[t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
[totalQualRevNew]=[t_detail_2004_07].
[totalQualRevNew]",1,0,0.0000001,53,9.94E-03,"[Bmk1000],
[t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
[totalQualRevNew]",NULL,PLAN_ROW,0,1
" |--Nested Loops(Inner Join,
OUTER REFERENCES:([a].[CustNumber]))",5,7,6,Nested
Loops,Inner Join,OUTER REFERENCES:([a].
[CustNumber]),NULL,1,0,0.00001254,554,9.94E-03,"[Bmk1000],
[t_detail_2004_07].[totalQualRevNew], [t_detail_2004_07].
[totalQualRevOrg]",NULL,PLAN_ROW,0,1
" |--Clustered Index S
(OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
[ControlingDate]='Jul 1 2004 12:00AM') ORDERED
FORWARD)",5,8,7,Clustered Index S,Clustered Index
S,"OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
[ControlingDate]='Jul 1 2004 12:00AM') ORDERED
FORWARD","[Bmk1000], [a].[CustNumber]",1,3.20E-03,7.96E-
05,107,3.28E-03,"[Bmk1000], [a].
[CustNumber]",NULL,PLAN_ROW,0,1
" |--Clustered Index S
(OBJECT:([DB2].[dbo].[t_detail_2004_07].
[PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
[CustNumber]=[a].[CustNumber]) ORDERED
FORWARD)",5,9,7,Clustered Index S,Clustered Index
S,"OBJECT:([DB2].[dbo].[t_detail_2004_07].
[PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
[CustNumber]=[a].[CustNumber]) ORDERED
FORWARD","[t_detail_2004_07].[totalQualRevNew],
[t_detail_2004_07].[totalQualRevOrg]",1,3.20E-03,7.96E-
05,456,6.65E-03,"[t_detail_2004_07].[totalQualRevNew],
[t_detail_2004_07].[totalQualRevOrg]",NULL,PLAN_ROW,0,3
,,,,,,,,,,,,,,,,,

>--Original Message--
>Can you post the update? Sounds interesting. Also, can
you post the pre-run
>plan and the post run plan?
>Remember that any update to any column in the table
requires an update to
>the clustered index, since all columns are part of the
index.
>--
>----
--
>Louis Davidson - drsql@.hotmail.com
>SQL Server MVP
>Compass Technology Management - www.compass.net
>Pro SQL Server 2000 Database Design -
>http://www.apress.com/book/bookDisplay.html?bID=266
>Blog - http://spaces.msn.com/members/drsql/
>Note: Please reply to the newsgroups only unless you are
interested in
>consulting services. All other replies may be ignored :)
>"Mets Fan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:126401c54102$af790090$a601280a@.phx.gbl...
have
What
truth
just
>
>.
>|||I would guess that might be the thing. Since there is no data, there is
very little cost to do the other stuff, but I would hold off worry about
optimzing until you have data :) Seriously, as long as you are careful to
realize that your join criteria must be a 1-1 relationship between table A
and table B, it is probably fine.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Mets Fan" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3e01c5411d$25d96b70$a401280a@.phx.gbl...
> There is not data presently so the statistics reflect
> that, perhaps that could be the issue. But as you asked,
> here is the resultset of SET SHOWPLAN_ALL. I exported it
> to excel and then saved as CSV. You will have to import
> and set the delimiter to a comma.
>
> "UPDATE a SET IncntvRevWAncil =
> b.totalQualRevOrg , IncntvRevWOAncil =
> b.totalQualRevNew FROM dbo.CustomerProfileMonthly
> a JOIN DB2.dbo.t_Detail b ON
> a.CustNumber = b.CustNumber AND
> a.ControlingDate = b.ControlingDate AND
> a.ControlingDate = CAST('20040701' AS
> DATETIME)" ,5,1,0,NULL,NULL,1,NULL,1,NULL,NULL,NULL
,3.81E-
> 02,NULL,NULL,UPDATE,0,NULL
> " |--Clustered Index Update(OBJECT:([DB1].[dbo].
> [CustomerProfileMonthly].[MerchantProfileMonthly_PK]), SET:
> ([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
> [CustomerProfileMonthly].[IncntvRevWAncil]=
> [Expr2860]))",5,2,1,Clustered Index Update,Update,"OBJECT:
> ([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK]), SET:
> ([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
> [CustomerProfileMonthly].[IncntvRevWAncil]=
> [Expr2860])",NULL,1,1.68E-02,0.000001,61,3.81E-
> 02,NULL,NULL,PLAN_ROW,0,1
> " |--Compute Scalar(DEFINE:([Expr2860]=Convert
> ([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
> ([t_detail_2004_07].[totalQualRevNew])))",5,3,2,Compute
> Scalar,Compute Scalar,"DEFINE:([Expr2860]=Convert
> ([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
> ([t_detail_2004_07].[totalQualRevNew]))","[Expr2860]
> =Convert([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]
> =Convert([t_detail_2004_07].
> [totalQualRevNew])",1,0,0.0000001,53,2.13E-02,"[Bmk1000],
> [Expr2860], [Expr2861]",NULL,PLAN_ROW,0,1
> |--Top(ROWCOUNT est
> 0),5,4,3,Top,Top,NULL,NULL,1,0,0.0000001,53,2.13E-
> 02,"[Bmk1000], [t_detail_2004_07].[totalQualRevOrg],
> [t_detail_2004_07].[totalQualRevNew]",NULL,PLAN_ROW,0,1
> |--Sort(DISTINCT ORDER BY:([Bmk1000]
> ASC)),5,5,4,Sort,Distinct Sort,DISTINCT ORDER BY:
> ([Bmk1000] ASC),NULL,1,1.13E-02,1.00E-
> 04,53,0.02130264,"[Bmk1000], [t_detail_2004_07].
> [totalQualRevOrg], [t_detail_2004_07].
> [totalQualRevNew]",NULL,PLAN_ROW,0,1
> " |--Compute Scalar(DEFINE:
> ([t_detail_2004_07].[totalQualRevOrg]=[t_detail_2004_07].
> [totalQualRevOrg], [t_detail_2004_07].[totalQualRevNew]=
> [t_detail_2004_07].[totalQualRevNew]))",5,6,5,Compute
> Scalar,Compute Scalar,"DEFINE:([t_detail_2004_07].
> [totalQualRevOrg]=[t_detail_2004_07].[totalQualRevOrg],
> [t_detail_2004_07].[totalQualRevNew]=[t_detail_2004_07].
> [totalQualRevNew])","[t_detail_2004_07].[totalQualRevOrg]=
> [t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
> [totalQualRevNew]=[t_detail_2004_07].
> [totalQualRevNew]",1,0,0.0000001,53,9.94E-03,"[Bmk1000],
> [t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
> [totalQualRevNew]",NULL,PLAN_ROW,0,1
> " |--Nested Loops(Inner Join,
> OUTER REFERENCES:([a].[CustNumber]))",5,7,6,Nested
> Loops,Inner Join,OUTER REFERENCES:([a].
> [CustNumber]),NULL,1,0,0.00001254,554,9.94E-03,"[Bmk1000],
> [t_detail_2004_07].[totalQualRevNew], [t_detail_2004_07].
> [totalQualRevOrg]",NULL,PLAN_ROW,0,1
> " |--Clustered Index S
> (OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
> [ControlingDate]='Jul 1 2004 12:00AM') ORDERED
> FORWARD)",5,8,7,Clustered Index S,Clustered Index
> S,"OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
> [ControlingDate]='Jul 1 2004 12:00AM') ORDERED
> FORWARD","[Bmk1000], [a].[CustNumber]",1,3.20E-03,7.96E-
> 05,107,3.28E-03,"[Bmk1000], [a].
> [CustNumber]",NULL,PLAN_ROW,0,1
> " |--Clustered Index S
> (OBJECT:([DB2].[dbo].[t_detail_2004_07].
> [PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
> [CustNumber]=[a].[CustNumber]) ORDERED
> FORWARD)",5,9,7,Clustered Index S,Clustered Index
> S,"OBJECT:([DB2].[dbo].[t_detail_2004_07].
> [PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
> [CustNumber]=[a].[CustNumber]) ORDERED
> FORWARD","[t_detail_2004_07].[totalQualRevNew],
> [t_detail_2004_07].[totalQualRevOrg]",1,3.20E-03,7.96E-
> 05,456,6.65E-03,"[t_detail_2004_07].[totalQualRevNew],
> [t_detail_2004_07].[totalQualRevOrg]",NULL,PLAN_ROW,0,3
> ,,,,,,,,,,,,,,,,,
>
> you post the pre-run
> requires an update to
> index.
> --
> interested in
> message
> have
> What
> truth
> just

Clustered Index and PK on GUID

Table has two columns
CODE varchar(10)
GUID uniqueidentifier,Rowguid (must be there for replication purposes)
When we update a row the CODE column is checked (CODE is unique)
What is better:
- to put primary key on GUID, clustered index and unique index on CODE
- to put primary key on CODE (with clustered index)
Thanx in advance.
½½½½
Martin Bajc> What is better:
That depends. What do you consider "better"?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:2FEF2DBA-3766-4FCC-86BA-034E90781AE5@.microsoft.com...
> CODE column, even better, make the CODE column CHAR(10). Fixed with
columns
> are better for indexes.
In what way?|||...better cosidering type of replication, number of rows in table... and
also developer's effort
(visio and EM puts automaticly clustered index on PK, so second choice seems
better to me uless I've missed something)
****
Martin Bajc
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eKcdWdKoEHA.132@.TK2MSFTNGP14.phx.gbl...
> > What is better:
> That depends. What do you consider "better"?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Hi
CODE column, even better, make the CODE column CHAR(10). Fixed with columns
are better for indexes.
A GUID is not bad for an index, but not optimal.
"Martin Bajc" wrote:
> Table has two columns
> CODE varchar(10)
> GUID uniqueidentifier,Rowguid (must be there for replication purposes)
> When we update a row the CODE column is checked (CODE is unique)
>
> What is better:
> - to put primary key on GUID, clustered index and unique index on CODE
> - to put primary key on CODE (with clustered index)
> Thanx in advance.
> ½½½½
> Martin Bajc
>
>
>
>
>

Clustered Index and PK on GUID

Table has two columns
CODE varchar(10)
GUID uniqueidentifier,Rowguid (must be there for replication purposes)
When we update a row the CODE column is checked (CODE is unique)
What is better:
- to put primary key on GUID, clustered index and unique index on CODE
- to put primary key on CODE (with clustered index)
Thanx in advance.
Martin Bajc
Hi
CODE column, even better, make the CODE column CHAR(10). Fixed with columns
are better for indexes.
A GUID is not bad for an index, but not optimal.
"Martin Bajc" wrote:

> Table has two columns
> CODE varchar(10)
> GUID uniqueidentifier,Rowguid (must be there for replication purposes)
> When we update a row the CODE column is checked (CODE is unique)
>
> What is better:
> - to put primary key on GUID, clustered index and unique index on CODE
> - to put primary key on CODE (with clustered index)
> Thanx in advance.
> ????
> Martin Bajc
>
>
>
>
>
|||> What is better:
That depends. What do you consider "better"?
http://www.aspfaq.com/
(Reverse address to reply.)
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:2FEF2DBA-3766-4FCC-86BA-034E90781AE5@.microsoft.com...
> CODE column, even better, make the CODE column CHAR(10). Fixed with
columns
> are better for indexes.
In what way?
|||...better cosidering type of replication, number of rows in table... and
also developer's effort
(visio and EM puts automaticly clustered index on PK, so second choice seems
better to me uless I've missed something)
****
Martin Bajc
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eKcdWdKoEHA.132@.TK2MSFTNGP14.phx.gbl...
> That depends. What do you consider "better"?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>

Clustered columns confusion

Hi,
I am having a primary key in my table as Clustered.
But when I fire an SQL Update (I am updating an Image type column!) Query th
rough my Java (JDBC) code,I get an exception.
If the same column was not clustered then the exception doesnt appear and up
date fires properly.
Any ideas why something like this should happen.
Thanks in Adv,
ChinmayHi
What kind of exeption did you get?
Do you have PRIMARY KEY on image type column?
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> Hi,
> I am having a primary key in my table as Clustered.
> But when I fire an SQL Update (I am updating an Image type column!) Query
through my Java (JDBC) code,I get an exception.
> If the same column was not clustered then the exception doesnt appear and
update fires properly.
> Any ideas why something like this should happen.
> Thanks in Adv,
> Chinmay|||I got it some weeks back.
Yes and i dont have a Primary Key on Image column.
The Primary Key is on a normal varchar column.
The thing is now I dont get it.
Also the exception that I got was comething like "Could not prepare a Query
Plan for clustered....."
Also I found this artical on the Microsoft site...(but I m confused still)
"http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729"
Thanks,
Chinmay
-- Uri Dimant wrote: --
Hi
What kind of exeption did you get?
Do you have PRIMARY KEY on image type column?
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> Hi,
> I am having a primary key in my table as Clustered.
> But when I fire an SQL Update (I am updating an Image type column!) Query
through my Java (JDBC) code,I get an exception.
> If the same column was not clustered then the exception doesnt appear and
update fires properly.
> Any ideas why something like this should happen.
> Chinmay|||Hi
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/reskit/sql7res/part9/sqc13.asp
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com...
> I got it some weeks back.
> Yes and i dont have a Primary Key on Image column.
> The Primary Key is on a normal varchar column.
> The thing is now I dont get it.
> Also the exception that I got was comething like "Could not prepare a
Query Plan for clustered....."
> Also I found this artical on the Microsoft site...(but I m confused
still)
> "http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729"
> Thanks,
> Chinmay
>
> -- Uri Dimant wrote: --
> Hi
> What kind of exeption did you get?
> Do you have PRIMARY KEY on image type column?
> "Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
Query
> through my Java (JDBC) code,I get an exception.
appear and
> update fires properly.
>
>|||thanks Uri for the link...
But have u or anybdy have come across succha problem bfor.
I just realised that I have 2 Image columns being updated and the clustered
column is in the where clause.
The whole issue is that we are not able to regenerate it again.
thanks,
Chinmay
-- Uri Dimant wrote: --
Hi
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/reskit/sql7res/part9/sqc13.asp
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com...
> Yes and i dont have a Primary Key on Image column.
> The Primary Key is on a normal varchar column.
> The thing is now I dont get it.
> Also the exception that I got was comething like "Could not prepare a
Query Plan for clustered....."
still)
> Chinmay
> What kind of exeption did you get?
> Do you have PRIMARY KEY on image type column?
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
Query
> through my Java (JDBC) code,I get an exception.
appear and
> update fires properly.

Clustered columns confusion

Hi
I am having a primary key in my table as Clustered
But when I fire an SQL Update (I am updating an Image type column!) Query through my Java (JDBC) code,I get an exception
If the same column was not clustered then the exception doesnt appear and update fires properly
Any ideas why something like this should happen
Thanks in Adv
ChinmayHi
What kind of exeption did you get?
Do you have PRIMARY KEY on image type column?
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> Hi,
> I am having a primary key in my table as Clustered.
> But when I fire an SQL Update (I am updating an Image type column!) Query
through my Java (JDBC) code,I get an exception.
> If the same column was not clustered then the exception doesnt appear and
update fires properly.
> Any ideas why something like this should happen.
> Thanks in Adv,
> Chinmay|||I got it some weeks back
Yes and i dont have a Primary Key on Image column
The Primary Key is on a normal varchar column.
The thing is now I dont get it
Also the exception that I got was comething like "Could not prepare a Query Plan for clustered.....
Also I found this artical on the Microsoft site...(but I m confused still
"http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729
Thanks
Chinma
-- Uri Dimant wrote: --
H
What kind of exeption did you get
Do you have PRIMARY KEY on image type column
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in messag
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com..
> Hi
> I am having a primary key in my table as Clustered
> But when I fire an SQL Update (I am updating an Image type column!) Quer
through my Java (JDBC) code,I get an exception
> If the same column was not clustered then the exception doesnt appear an
update fires properly
> Any ideas why something like this should happen
>> Thanks in Adv
> Chinma|||Hi
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
ol/sql/reskit/sql7res/part9/sqc13.asp
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com...
> I got it some weeks back.
> Yes and i dont have a Primary Key on Image column.
> The Primary Key is on a normal varchar column.
> The thing is now I dont get it.
> Also the exception that I got was comething like "Could not prepare a
Query Plan for clustered....."
> Also I found this artical on the Microsoft site...(but I m confused
still)
> "http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729"
> Thanks,
> Chinmay
>
> -- Uri Dimant wrote: --
> Hi
> What kind of exeption did you get?
> Do you have PRIMARY KEY on image type column?
> "Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> > Hi,
> > I am having a primary key in my table as Clustered.
> > But when I fire an SQL Update (I am updating an Image type column!)
Query
> through my Java (JDBC) code,I get an exception.
> > If the same column was not clustered then the exception doesnt
appear and
> update fires properly.
> > Any ideas why something like this should happen.
> >> Thanks in Adv,
> > Chinmay
>
>|||thanks Uri for the link...
But have u or anybdy have come across succha problem bfor
I just realised that I have 2 Image columns being updated and the clustered column is in the where clause
The whole issue is that we are not able to regenerate it again
thanks
Chinma
-- Uri Dimant wrote: --
H
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
ol/sql/reskit/sql7res/part9/sqc13.as
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in messag
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com..
>> I got it some weeks back
> Yes and i dont have a Primary Key on Image column
> The Primary Key is on a normal varchar column
> The thing is now I dont get it
> Also the exception that I got was comething like "Could not prepare
Query Plan for clustered.....
>> Also I found this artical on the Microsoft site...(but I m confuse
still
>> "http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729
>> Thanks
> Chinma
>> -- Uri Dimant wrote: --
>> H
> What kind of exeption did you get
> Do you have PRIMARY KEY on image type column
>> "Chinmay" <anonymous@.discussions.microsoft.com> wrote in messag
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com..
>> Hi
>> I am having a primary key in my table as Clustered
>> But when I fire an SQL Update (I am updating an Image type column!
Quer
> through my Java (JDBC) code,I get an exception
>> If the same column was not clustered then the exception doesn
appear an
> update fires properly
>> Any ideas why something like this should happen
>> Thanks in Adv
>> Chinma
>>