October 28, 2007 at 2:40 pm
An outside vendor created this command and SQL files for internal software, using SQL Server Express, which is installed on every user machine
command.cmd
osql -S (local)\SQLEXPRESS -E -i DropDB.sql
DropDB.sql
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Caching')
DROP DATABASE [Caching]
This command was created for SQL Server Express, for an internal software package at my company, by an outside vendor. However, sometimes this command doesn't work 100% on all user computers. So I have two questions.
1. Is there any way to either enhance the command, or the database command, to provide error diagnostics, to find out the issue for an individual user computer, why it doesn't always work?
2. Can anyone think of any scenarios that would prevent this from working?
The key element here is that this caching database is supposed to allow individuals to use a particular piece of software, when they aren't connected to the network at work. But it doesn't always work on each computer - 100% of the time - even when the computer has SQL Server Express installed, and is running Windows XP.
Randy
October 28, 2007 at 7:01 pm
2.)You can't drop the caching database when the database is in use, hence you need to kill caching database connections and then try to drop the db. I think this is the one which is preventing the dropping of database sometimes as you mentioned
This is a case when the database is placed in centralized server where everyone try to access the db, hence if one user is accessing the db and another user tries to drop, then the db wont drop becuase its been used by user 1. Hence the connection from user1 to be killed before dropping the database
1.) There is nothing to improve in the script as its a drop database command, may be we can add additional first step to kill all caching database connections and then proceed to drop command.
Regards..Vidhya Sagar
SQL-Articles
October 28, 2007 at 8:28 pm
The osql utility has a -o switch which can be used to write any output generated by the command to a file. You could add this to the command and then check the file to see what error messages are generated
e.g. osql -S (local)\SQLEXPRESS -E -i DropDB.sql -o c:\temp\DropddLog.txt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply