March 28, 2006 at 2:44 am
When running stored proc in restricted access single user I receive this message if the database is polled from another user when the polling stops the database continues possibly missing steps in my proc. Microsoft access seems to be the worst offender.
The stored proc code doesn't seem to matters but it’s in an upgrade to a database application that it happens most often.
37000:[Microsoft][SQL Native Client][SQL Server]Database 'NCTEST3' is already open and can only have one user at a time.
SQL Developer
James
Access Accounting Ltd
March 28, 2006 at 8:46 am
I'm not sure I understand. Is this what happens?
1. Start server in single user mode
2. connect and run proc.
3. Another user attempts to connect and gets the errir
4. You think your proc misses steps?
I don't think #4 happens. The proc will be transactionally protected. You might get the msg, but it shouldn't affect your upgrade. If you are trying to connect again from one of your programs, it might cause issues.
March 28, 2006 at 8:52 am
I always get this message when I am upgrading (or test-upgrading) the database application when I have Enterprise Manager open and the upgrade script is running. That is why each of my upgrade plans has a step: "Disconnect from the server in EM and reconnect, click only on processes in the Current Activity window" to make sure your EM is not focused on the database. or close EM altogether. I am talking not about the upgrade from 2000 to 2005, but when the application during its upgrade is modifying the database and needs single user access.
Also before the upgrade I send at least 2 emails to users not to connect to the server. Another option is to unplug a network cable for the upgrade.
Regards,Yelena Varsha
March 29, 2006 at 2:47 pm
Just my $0.02 ... Unplugging the network cable can have unwanted affects like jostling another server cable in the same rack - whether it be power, network, crossover or KVM. I would opt for changing the TCP port for the instance as a safer alternative.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 29, 2006 at 4:05 pm
Rudy,
Changing the port is cool!
I also had cases not upgrade related but requiring Single User like for database restore: the single user condition could not be reached because the web server was sending requests for connection every second, it was a job or something running there (guessing by the Processes window) and the developer did not have admin access to the web server, we had to request its reboot. In another case the web app was trying to connect, we had to stop the website every time we had to restore or do application upgrades.
Regards,Yelena Varsha
April 3, 2006 at 6:33 am
Thnk you for your help I have opted for the moment to have the Network administrators at the various sights stop the browser service in sql 2005 to stop the error. We will then advise users to ugrade over night (a 6 hour process at times) then start the browser service back up. In my little world this is a bodge not a solution. If anyone finds a way yto control sql server s error message please let me know. I would just use a try catch blook and control it from there but I working with another programming language oh well.
SQL Developer
James
Access Accounting Ltd
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply