One of my colleagues wants to execute multiple sql script files
without human intervention in MS SQL server, I recommended ISQL for
I've used it before. Unfortunately, her case is a special one, the
database name has four words such as "My own database name", which
ISQL seems doesn't support.
My first response was to rename the database, though we later
recognized that MS Enterprise Manager doesn't support database
renmaing either. I turned to Google and have this stored procedure
written by Rahul Sharma,
/*This proc will rename the database. Takes in two parameters: Old
database name and
the new -database name. Puts the database in single user mode and
then after re-naming
the database, sets it back to multi-user again.*/
CREATE PROCEDURE usp_RenameDB
@OldDatabaseName varchar(50),
@NewDatabaseName varchar(50)
WITH ENCRYPTION
AS
BEGIN
DECLARE @OldTranCount INTEGER;
SET @OldTranCount = @@TRANCOUNT;
WHILE (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
EXEC sp_dboption @OldDatabaseName, 'single user', 'TRUE'
EXEC sp_renamedb @OldDatabaseName, @NewDatabaseName
EXEC sp_dboption @NewDatabaseName, 'single user', 'FALSE'
WHILE (@@TRANCOUNT < @OldTranCount)
BEGIN TRANSACTION
END
GO
I don't like this but seemly have to accept it. Now, to my surprise,
some people on www.csdn.net have us good news, ISQL does support such
database names, the correct syntax is as follows,
isql -S ComputerName -U UserName -P Password -d DatabaseName -i
YourSQL.sql >> error_log.txt
When your database name has more than one word, embrace them with
double quotes and square brace, say, "[My own database name]". Worked
like a snap.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment