Upsizing Wizard - Sql Server locks problem.

[christheprogrammer]

Hopefully someone has had this problem before - I am using the upsizing wizard to upsize tables to SQL Server. A warning message comes up EVERY TIME I try to use the wizard. Sometimes I can ignore it, and it works, but sometimes it will not work. The message says: The upsizing wizard has detected that you need XXXX number of locks. The current setting is 0. The maximum is 2147987234 (or something big).Any ideas on how to set it? thanks
Chris

 

[JPep]

Chris,
Did you ever get around this error? If not, I wish I could help you out. Unfortunately, I am getting the same error. I have looked at the Locks/Process Id and I have 22 spids. Is this the same as locks? I have looked under the help menus and online and I have not seen any information on this error.

Is there anyone who has been able to overcome this error? :
Warning! The Upsizing Wizard has determined that you may exceed the current SQL Server 'locks' setting if you upsize now. Based on the sizes of your tables the wizard has computed that approximately 7 locks will be needed. The current SQL Server setting is 0. You can increase this as high as 2147483647. SQL Server must be stopped and restarted for the new setting to take effect.

Thank you. [sig][/sig]

 

[Husein]

Would you guys be kind to tell us the procedure (step by step) and explain what you did oneach screen. I've just upsized one database and it worked great. It displat report at the end to tell you what it did. I would like to simulate the same problem. One more thing which Access version are you using? [sig][/sig]

 

[JPep]

I found this same message from Chris on the SQL board as well and there was a tip how to increase the number of locks on a database and reconfigure without restarting the server. He said it did work for him and it also worked for me. I am not sure how or why this works but it did.

The SQL code is:
sp_configure "locks", 100000
Then run:
reconfigure with override

(I ran these statements in Query Analyzer) [sig][/sig]

 

[Husein]

I think that locks should not be the problem because the minimum value is 5000 locks. There is no way that upsizing wizard can use more than that, and if it does than it is a problem with the upsizing wizard. Access 2K upsizing wizard works just fine. Instead of all the fuss why not just using DTS package to transfer from Access-->SQL? [sig][/sig]