Database

Create PROCEDURE [dbo].[sp_RestoreDatabase] <br />
<br />
	@dbname char(32), 	-- the database name to restore as<br />
	@filename char(32),<br />
	@Results int OUTPUT<br />
  <br />
AS<br />
<br />
set nocount on<br />
<br />
declare @path char(64)<br />
declare @dbmdf char(64)<br />
declare @dbldf char(64)<br />
set @path = 'C:\DATABASES\' 	-- the location of the backuped up database file	(on the SQL Server)<br />
	<br />
execute('sp_ClearDatabaseConnections ' + @dbname) --kill the existing connections to the current Database<br />
<br />
SET @filename = 'C:\DATABASES\'+ @filename<br />
set @dbmdf = 'C:\DATABASES\' + @dbname + '.mdf'<br />
set @dbldf = 'C:\DATABASES\' + @dbname +'_log.ldf'<br />
-- Restore the database<br />
RESTORE  DATABASE @dbname <br />
FROM DISK = @filename<br />
WITH MOVE 'TNGoedit_Data' TO  @dbmdf ,<br />
MOVE      'TNGoedit_Log' TO  @dbldf,<br />
REPLACE,RECOVERY;<br />
-- Was the command successful or was there a problem<br />
if ( (select @@Error) = 0 ) begin<br />
  SET  @Results = 1<br />
end<br />
else begin<br />
   SET @Results = 0<br />
end

DECLARE @Results INT
EXEC [dbo].[sp_RestoreDatabase] 'oBookingUFS','oBookingUFS',@Results out

<br />
Msg 3234, Level 16, State 2, Procedure sp_RestoreDatabase, Line 27<br />
Logical file 'TNGoedit_Data' is not part of database 'oBookingUFS                     '. Use RESTORE FILELISTONLY to list the logical file names.<br />
Msg 3013, Level 16, State 1, Procedure sp_RestoreDatabase, Line 27<br />
RESTORE DATABASE is terminating abnormally.

George_George wrote:

For single SQL insert/update/delete statement, are there any needs or benefits to wrap into a transaction

George_George wrote:

is it a must to call commit explicitly just before end try statement? Any means to commit transaction automatically after end try statement

George_George wrote:

if I do not start transaction explicitly, I could still catch the error and handles it

George_George wrote:

what do you mean client? No matther what kinds of API you are using or even using Management Studio directly, I think we are all "client" of database engine.

George_George wrote:

1. For single SQL insert/update/delete statement, are there any needs or benefits to wrap into a transaction?

George_George wrote:

Any means to commit transaction automatically

George_George wrote:

I want to confirm with you that if we call begin transaction, do you mean the only way to commit transaction is to explicit call commit?

George_George wrote:

means we could have chance to commit later in a batch manually explicitly?

select convert(varchar(50), datepart(hour, datesum) + (datepart(day, datesum) - 1) * 24)
       + ':' + convert(varchar(50), datepart(minute, datesum))
       + ':' + convert(varchar(50), datepart(second, datesum))
from (select SUM(convert(real, datecolumn)) as datesum
      from (select CONVERT(datetime, '03:18:46') as datecolumn
            union
            select CONVERT(datetime, '03:25:06') as datecolumn
            union
            select CONVERT(datetime, '16:23:46') as datecolumn
            union
            select CONVERT(datetime, '07:24:20') as datecolumn) alias1 
      ) alias2

monika_vasvani wrote:

I have tried to do one general procedure for insert,update,delete.