Trying to use UPSIZING wizard from Access to SQL Server

[christheprogrammer]

Hi all,
I am using the upsizing wizard to insert 2 tables from access 97 to Sql Server 7.0 and I keep getting a message saying I need XXXX locks to ensure the upsize. The current SQL Server setting is 0. It can be set as high as 2,147,789,123 or something huge like that. HOW DO I SET THIS LOCK SETTING?? HELP!
Thanks a bunch
Chris

 

[Jimrv6]

Are you sure it won't do it anyway, if you ignore the warning? I know this was a settable option in 6.5 but I'm not sure where in 7.0 to change this. I have used the Access upsize wizard to do what you are attempting and actually recall seeing the same type of message. From what I remember, it was a "warning" that I ignored without a problem.

 

[christheprogrammer]

Yes, Jim thanks for the reply. It usually doesn't matter but for this particular upsize, it will not work. I hate that little red X that pops up... Oh well I will keep looking.
When you said it was a settable option in 6.5, could you tell me how? Maybe it is similar in 7.0
Books Online gives no clue to this.... the mystery continues...
cheers

Chris

 

[Jimrv6]

Yes, It was a GUI screen containing properties of the server. Memory, lock, thresholds, logins and a bunch of other setting that I can't remember. You could set the value in the gui and then restart the server and the change was made. You could also change the setting with system stored procedures. I can't remember the name of the procedure but you should be able to search technet for locks near sql near procedure and find it.

We just shut down our 6.5 server the other day or I would give you the exact information. Hope this helps.

 

[Ruairi]

Chris,
In 6.5 the stored procedure is sp_configure. Typing just sp_configure into the querey tool and executing it returns a list of configuration options along with their min, max, configured, and current run values. to change one of the options type its name in quotes, then a comma and the new value. Ex: sp_configure "locks", 5500
this will fet the config value. then stop and restart the server to make this the run value.
Good Luck
Ruairi

 

[christheprogrammer]

THANKS!!!!!!! It worked after a little fiddling around. Here is exactly how you do it in SQL Server 7.0 (There is no need to stop and start the server):
Click Tools->SQL Server Query Analyzer-> (Choose your server)
Now, enter this into the window:
sp_configure "locks", 100000
Press the play button
wait until it finishes (note: you can use up to 2 billion and something for the value)
Now, erase the text in the window and type:
reconfigure with override
Press the play button
You're done!!

Lots of thankyou's to both Jim and Ruairi. Cheers
Talk to ya again
Chris