ODBC error when updating large table

[philrm]

I recieve the following error message when trying to set a field not to allow nulls.

Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Timeout expired

I have checked the ODBC section for configuration of the SQL driver, and I can find no time limit, only a section for logging large queries. I have used sp_configure to check SQL and it has a query wait value that is very high eg 200000000 or some such. The properties tab of the SQL server itself indicate that the query should have unlimited time to execute ( a value of 0 . Can anyone shed any light on what I have missed??

 

[foxdev]

What are you using as a client, Phil? There may be some ODBC settings being made automatically by your client software that are not shown in the ODBC DSN setup.

For example, Visual FoxPro's Remote Data page shows options for Idle Timeout and Query Timeout. It uses these values to adjust its handling of the ODBC connection. Perhaps your software also has some setting where this can be adjusted.

Is there are a reason you can't use Query Analyzer or the EM to change the nullability of the column? When setting an existing column's nullability to false, SQLS has to check all the rows to ensure that none of them are currently null, and in a large table this can take quite a while.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

I would suggest that you run Profiler to see what your query is sending to the server. In addition, I would run it to see if you are successlfully connecting to the database.

Tom

 

[philrm]

Sorry guys I left a bit out, foolish me. I am trying to modify the table using enterprise manager. I have managed to do what I wanted on my laptop but the server seems to have a different configureation ? The server is NT4 SP6a while the laptop is Win 2000 Pro SP1. Apart from that the installations were done exectly the same. I am new to SQL so I hope you do not think I am TOO slow. The table was originally from an Access 97 database which was upsized to SQL. But if memory serves the table did not complete the process. It also timed out, though the data is all present and correct. The same thing happend on the Laptop when the table was upsized ???