Additional INSERT, UPDATE, and DELETE Statements Disabled.

Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.
I have been looking for an answer to my problem, but unfortunately I couldn't find one. So I created a user here on www.asp.net just for making this post.

Before I continue I just want to apologies if there is another post where this question is already answered.

Please watch this Print Screen I just took: "http://www.bewarmaronsi.com/Capture.JPG "

As you can see the "INSERT, UPDATE, and DELETE Statements" are disabled, and that's exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.

The MDF file is located in the App_data folder and is called ASPNETDB.

And when I try to add custom SQL statements, it gives me Syntax error near "=". Something like that. I bought the Total Training Set1 package and it works perfect in their examples.

I just want to thank you for reading my post and I hope that you got some useful information for me.

By the way, I', from Sweden so you have to excuse me if my English is rusty.

Thanks!

PS: Can it be that I'm running windows Vista?

Try adding a primary key column to the the table and check.

If any issues, let me know.

Sri

|||

Thank you for your postsridhar.av, I don't exactly know what you mean, but I think this might be it… please look at this new print screen:

"

http://www.bewarmaronsi.com/Capture2.JPG

"

And if that's the case, it still doesnt work.

Thanks

|||Right click on the Column -->Set Primary key|||

Thank you so muchsridhar.a, it works perfect now!

I'm a designer so if you need any help in that area, dont hesitate and email me anytime you want.
From Graphics to 3D modeling and Animation.

Thanks!

Addition of a number to an INT column

Hi,
I have several INT columns in a table that I need to update.
For example, in column 'aa' I need to add 2 to all of the values in that column.
I'm using Query Analyzer - what update statement should I write?Hmm - no answers - should I be in a different forum for this question?

I guess I could do:

UPDATE table_name SET aa = 16 WHERE aa = 14
UPDATE table_name SET aa = 15 WHERE aa = 13
UPDATE table_name SET aa = 14 WHERE aa = 12

and keep going like this until I have all the values updated.

Note that I've done it from highest number first, otherwise all of the data would get adjusted to the two highest numbers.

I was looking for a more elegant solution If anyone can think of one as I have several columns to update all with slightly different increases.|||UPDATE table_name SET aa = aa + 2

:D|||Of course - thank you - how could I miss it??

Woods for the trees and all...

Adding rows to Oracle of MsAccess from T-SQL?

Hi everyone,

I'd like to know how to update/insert rows to sources different than Microsoft.

Should I use OpenDataSource(), OpenRowSet() functions or they are deprecated with Sql25k?

Thanks in advance for your time and for your inputs,

Both OpenDataSource and OpenRowSet can be used for inserting and updating data in data sources other than SQL Server. They are both supported by SQL Server 2005. You can also use linked servers.

Please tell me if you need further information.

Best regards,
Sami Samir

|||

If you are moving millions of rows of data, I found the fastest way to move from Access or SQL Server to ORACLE was via an intermediate text file.

A data load that would have taken around 10 hours to move one table via an ODBC connection from SQL Server to ORACLE took only a few minutes via text export from SQL Server, and SQL*Loader with ORACLE. (I don't know if you consider a few hundred megabytes a large amount of data. I consider it "typical" of a single table. I had many such tables to transfer.)

Another problem I ran into was mixed-case table names that could be created in ORACLE by uploads from SQL Server. My recollection is that you need to surround the names with double-quotes, ", to get ORACLE to recognize the tables with the mixed-case names.

I don't recall trying UPDATE or INSERT statements. I always logged in to ORACLE to drop the tables that I needed to create to perform the data uploads when I used an ODBC connection for "small" tables (with only tens of thousands of rows).

I hope others here may have more direct experience with the UPDATE and INSERT issues you are contemplating.

Dan

Adding rows to Oracle of MsAccess from T-SQL?

Hi everyone,

I'd like to know how to update/insert rows to sources different than Microsoft.

Should I use OpenDataSource(), OpenRowSet() functions or they are deprecated with Sql25k?

Thanks in advance for your time and for your inputs,

Both OpenDataSource and OpenRowSet can be used for inserting and updating data in data sources other than SQL Server. They are both supported by SQL Server 2005. You can also use linked servers.

Please tell me if you need further information.

Best regards,
Sami Samir

|||

If you are moving millions of rows of data, I found the fastest way to move from Access or SQL Server to ORACLE was via an intermediate text file.

A data load that would have taken around 10 hours to move one table via an ODBC connection from SQL Server to ORACLE took only a few minutes via text export from SQL Server, and SQL*Loader with ORACLE. (I don't know if you consider a few hundred megabytes a large amount of data. I consider it "typical" of a single table. I had many such tables to transfer.)

Another problem I ran into was mixed-case table names that could be created in ORACLE by uploads from SQL Server. My recollection is that you need to surround the names with double-quotes, ", to get ORACLE to recognize the tables with the mixed-case names.

I don't recall trying UPDATE or INSERT statements. I always logged in to ORACLE to drop the tables that I needed to create to perform the data uploads when I used an ODBC connection for "small" tables (with only tens of thousands of rows).

I hope others here may have more direct experience with the UPDATE and INSERT issues you are contemplating.

Dan

Adding parameters during .updating event handler

Not receiving any errors. The update runs perfectly on all fields, except for the added parameter during the sub. I need to change the hidden field's value after parsing out several fields in the form and generating a logfile entry of sorts. This needs to happen after all the form fields are updated, but before the update is executed.

aspx: (relevant parts only...)
<asp:SqlDataSourceID="TicketDetails"runat="server"ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
UpdateCommand="UPDATE Tickets SET TicketSuspense = @.TicketSuspense, TicketPriority = @.TicketPriority, TicketLastUpdated = CURRENT_TIMESTAMP, TicketStatus = 'Assigned', TicketTechnicianNotes = @.TicketTechnicianNotes WHERE (TicketID = @.TicketID)">
<UpdateParameters>
<asp:ParameterName="TicketPriority"Type="String"/>
<asp:ParameterName="TicketSuspense"Type="DateTime"/>
<asp:ParameterName="TicketID"Type="Int32"/>
</UpdateParameters>
</asp:SqlDataSource>

codebehind: (once again, the relevant parts only)
ProtectedSub TicketDetails_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles TicketDetails.Updating
TicketTechnicianNotesHiddenField.Value ="some text..."& TicketTechnicianNotesHiddenField.Value
TicketDetails.UpdateParameters.Add(New Parameter("TicketTechnicianNotes", TypeCode.String, TicketTechnicianNotesHiddenField.Value))
EndSub

Thanks,

- Brad

What about this:

ProtectedSub TicketDetails_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles TicketDetails.Updating
TicketTechnicianNotesHiddenField.Value ="some text..."& TicketTechnicianNotesHiddenField.Value

e.Command.Parameters("@.TicketTechnicianNotes").Value = TicketTechnicianNotesHiddenField.Value

EndSub

|||

I think that was the only variation I hadn't yet tried. It worked.

I have to ask... what caused the "Add" not to work?

Thanks,

- Brad

|||

"TheSqlDataSource control will also automatically create parameters based on values passed by a data-bound control... "

You can find more information from this link:

http://msdn2.microsoft.com/en-us/library/z72eefad.aspx

Adding Leading Zeros to a char value - TSQL Question

Hi all:

I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))

SELECT @.SchoolCode_Modified AS Col1

The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:

CREATE PROCEDURE [LAF].[uspSaveLoanApplication]

...more params here

@.SchoolCode char(6),

@.BranchCode char(2),

@.PK int OUTPUT,

@.PreviousLoanApplicationStatus char(3) OUTPUT

AS

SET NOCOUNT OFF

SET @.PreviousLoanApplicationStatus = 0

IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus

END

-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting

DECLARE @.SchoolCode_Modified char(6)

DECLARE @.BranchCode_Modified char(2)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))

SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))

IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

UPDATE [LAF].[LoanApplication] SET

....more here

[SchoolCode] = @.SchoolCode_Modified,

[BranchCode] = @.BranchCode_Modified,

...more here

WHERE

[LoanApplicationID] = @.LoanApplicationID

AND [UpdatedOn] = @.LastUpdated

SELECT @.LoanApplicationID PK

SET @.PK = @.LoanApplicationID

END

ELSE

BEGIN

INSERT INTO [LAF].[LoanApplication] (

.....more here

[SchoolCode],

[BranchCode],

.....more here

) VALUES (

....more here

@.SchoolCode_Modified,

@.BranchCode_Modified,

....more here

)

SET @.PK = SCOPE_IDENTITY()

SELECT @.PK PK

END

With a quick scan, your code appears correct.

How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?

|||

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

|||

DanR1 wrote:

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

Good catch...

Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.

|||

Arnie,

Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)

So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.

Dan

|||

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = '123'

SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified

SELECT @.SchoolCode_Modified AS Col1

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?
|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David
|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
--
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding comments to TEXT field...

Newbie question:

Aside from the single quote (i.e. chr(39)) what other characters can cause
MS-SQL
server to cough-up the insertion / update back in your face?

TIAA. Nonymous (someone@.hotmail.com) writes:
> Aside from the single quote (i.e. chr(39)) what other characters can cause
> MS-SQL
> server to cough-up the insertion / update back in your face?

None, what I can think of. Well if you use the double " as delimiter,
then this is the odd one out. But you can only use " with certain
settings.

Anyway, to include the string delimiter in the string, you double it:

SELECT 'Three o''clock'

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

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