SQL SERVER 2000 WILL NOT UPDATE A RECORDSET

[janpetrucci]

I am having a very unusual problem with SQL Server 2000. Maybe someone can help me with this.

I am running an ASP site. The server is Windows NT 4.

I was using Access as the database and just upgraded to SQL Server 2000. When I had 2000 records in the SQL table, my asp page updated the records perfectly.

When I added 10000 more records to the table I now get Script timeout messages and the page is halting on the Update statements.

strSQL = "UPDATE Orders SET ReceivedDate = '" & strNO6 & "' WHERE GenOrder = '" & strNO & "'"
objCmd.ActiveConnection = strConnect
objCmd.CommandType = adCmdText
objCmd.CommandText = strSQL
objCmd.Execute , ,adCmdText

The strange thing is that the page works with 12000 records in the Access table...it runs well and updates the recordsets pretty quickly. I thought SQL server would be faster.

I thought maybe I was getting locked on a record, so it would not update, but I am closing the recordsets and I get the same results.

Has any one had a problem like this???

 

[link9]

Yes, SQL Server will definitely provide you with much better performance when compared w/ Access. Try it this way, and see if it clears it up. I'm just passing a SQL command directly to the connection object that I'm creating, which in my opinion, is the cleanest way to take care of many simple database tasks such as UPDATE, INSERT, & DELETE.

dim con, strSQL
set con = server.createObject("ADODB.Connection")
strSQL = "UPDATE Orders SET ReceivedDate = '" & strNO6 & "' WHERE GenOrder = '" & strNO & "'"
con.open strConnect
con.execute strSQL

lemme know how it works out.


paul

 

[janpetrucci]

Paul,

Thanks for you response. I tried your code, but I got the same results. The page hangs up on the con.execute strSQL.
It won't let me update the record.

 

[cfk]

You could try sending parameters to a stored procedure.
The problem may be the script timeout in IIS.
The default is something like 90 seconds.
How many fields where being updated?
Is GenOrder a primary key?

 

[cmmrfrds]

Do you have Query Analyzer installed. It comes with the client tools for SQL Server. Query Analyzer is a robust interface to sql server and would be useful for you to determine if the problem is with your sql string. Take your exact sql string and paste it into query analyzer. If the query fails at this point you can do the debugging from this interface which is easy to work with. If the query does not fail then you have elminated sql server as the problem.

 

[cfk]

WHAT format is strNO6 in. I assume its a datetime.
try remouving the single quotes arround it and
using this format:

{ts 'yyyy-mm-dd hh:mm:ss'}

 

[janpetrucci]

Thanks everyone for your responses.

Only one field is being updated on one record on a table of 12000 rows...about 12mb I think. GenOrder is the Primary key.

The query works in Query Analyzer. The strNo6 is a variable that is filled in from the previous page by the request object. It is a date field, but I am inserting it into the table as a text field for extraction purposes.

One small change I just made seems to affect the Update.

When I set the primary key for my table, SQL Server defaulted it to be a 'clustered' key. From what I just read that means every time there is an update, the entire table gets reordered. Maybe that was fine on a table of 2000 and not for a table of 12000?

Also, I had my three other indexes under IX_Orders. Does this put them into a grouping??? I split them up under different IX headings and that seemed to help also.

As you can tell I am new to SQL Server. It's confusing to use a new tool sometimes. Thanks againg for the help.

JP