-- START SCRIPT 2.CreateTheDb.sqlDECLARE @DataPath nvarchar(200) set @DataPath = N'$(DataPath)'
SELECT 'USING THE FOLLOWING DataPath ' + @DataPath
DECLARE @DbName nvarchar(200) set @DbName = N'$(DbName)'
SELECT 'USING THE FOLLOWING DbName ' + @DbName
DECLARE @CurrentDate nvarchar(200) set @CurrentDate = N'$(CurrentDate)'
SELECT 'USING THE FOLLOWING CurrentDate ' + @CurrentDate
DECLARE @StrSql nvarchar(max)
set @StrSql = '
/****** Object: Database [' + @DbName + '] Script Date: ' + @CurrentDate + ' ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N''' + @DbName + ''')
DROP DATABASE [' + @DbName + '] ;' SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql set @StrSql = '/****** Object: Database [' + @DbName + '] Script Date: ' + @CurrentDate + ' ******/
CREATE DATABASE [' + @DbName + '] ON PRIMARY ( NAME = ''[' + @DbName + ']'', FILENAME = ''' + @DataPath + @DbName + '.mdf'' , SIZE = 34816KB ,
MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON ( NAME = ''[' + @DbName + ']_log'',
FILENAME = ''' + @DataPath + @DbName + '.ldf'' ,
SIZE = 164544KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) ;'
SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql set @StrSql = 'IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))
begin
EXEC [' + @DbName + '].[dbo].[sp_fulltext_database] @action = ''enable''
end ;'
SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql set @StrSql = 'ALTER DATABASE [' + @DbName + '] SET ANSI_NULL_DEFAULT OFF ;
ALTER DATABASE [' + @DbName + '] SET ANSI_NULLS OFF ;ALTER DATABASE [' + @DbName + '] SET ANSI_WARNINGS OFF ;ALTER DATABASE [' + @DbName + '] SET ARITHABORT OFF ;' SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql set @StrSql = 'ALTER DATABASE [' + @DbName + '] SET AUTO_CLOSE ON ;
ALTER DATABASE [' + @DbName + '] SET AUTO_CREATE_STATISTICS ON ;ALTER DATABASE [' + @DbName + '] SET AUTO_SHRINK ON ;ALTER DATABASE [' + @DbName + '] SET AUTO_UPDATE_STATISTICS ON ;'SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql set @StrSql = 'ALTER DATABASE [' + @DbName + '] SET CURSOR_CLOSE_ON_COMMIT ON ;
ALTER DATABASE [' + @DbName + '] SET CURSOR_DEFAULT GLOBAL ;ALTER DATABASE [' + @DbName + '] SET CONCAT_NULL_YIELDS_NULL OFF ;ALTER DATABASE [' + @DbName + '] SET NUMERIC_ROUNDABORT OFF ;'SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql set @StrSql = 'ALTER DATABASE [' + @DbName + '] SET QUOTED_IDENTIFIER OFF ;
ALTER DATABASE [' + @DbName + '] SET RECURSIVE_TRIGGERS ON ;ALTER DATABASE [' + @DbName + '] SET DISABLE_BROKER ;ALTER DATABASE [' + @DbName + '] SET AUTO_UPDATE_STATISTICS_ASYNC ON ;'SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql set @StrSql = 'ALTER DATABASE [' + @DbName + '] SET DATE_CORRELATION_OPTIMIZATION OFF ;
ALTER DATABASE [' + @DbName + '] SET TRUSTWORTHY OFF ;ALTER DATABASE [' + @DbName + '] SET ALLOW_SNAPSHOT_ISOLATION OFF ;ALTER DATABASE [' + @DbName + '] SET PARAMETERIZATION SIMPLE ;'SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql set @StrSql = 'ALTER DATABASE [' + @DbName + '] SET READ_COMMITTED_SNAPSHOT OFF ;
ALTER DATABASE [' + @DbName + '] SET READ_WRITE ;ALTER DATABASE [' + @DbName + '] SET RECOVERY FULL ;ALTER DATABASE [' + @DbName + '] SET MULTI_USER ;'SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql set @StrSql = 'ALTER DATABASE [' + @DbName + '] SET PAGE_VERIFY CHECKSUM ;
ALTER DATABASE [' + @DbName + '] SET DB_CHAINING OFF ; 'SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql -- END SCRIPT 2.CreateTheDb.sql-- START SCRIPT 1.KillConnectionsAndDropTheDb.sql use master ; declare @DbName varchar(100)
-- Set the name of the database to restoreset @DbName = '$(DbName)'
/****** Object: Database [CAS_DEV] Script Date: 03/14/2010 11:35:15 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = @DbName)
begin --if there exists a
declare @backupFileName varchar(100), @restoreDirectory varchar(100),
@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
@databaseDataFile varchar(100), @databaseLogFile varchar(100)
, @execSql nvarchar(1000)
set @execSql = ''
-- Create the backup file name based on the restore directory, the database name and today's date--@backupFileName = @restoreDirectory + @DbName + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'
--declare @DBFileName varchar(256)
--set @DBFileName = @DbName + '_' + replace(convert(varchar, getdate(), 112), '-', '.') + '.bak'
-- Get the data file and its pathselect @databaseDataFile = rtrim([Name]),@databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
inner join
master.dbo.sysfilegroups as groupsonfiles.groupID = groups.groupID
where DBID = (select dbidfrom master.dbo.sysdatabaseswhere [Name] = @DbName)
-- Get the log file and its pathselect @databaseLogFile = rtrim([Name]),@databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
where DBID = (select dbidfrom master.dbo.sysdatabaseswhere [Name] = @DbName)
andgroupID = 0
--KILL ACTIVE CONNECTIONS START
SELECT 'Killing active connections to the "' + @DbName + '" database'
-- Create the sql to kill the active database connectionsset @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' ; '
from master.dbo.sysprocesseswhere db_name(dbid) = @DbNameandDBID <> 0
andspid <> @@spidexec (@execSql)SELECT @execSql AS 'execsql'
--select @execSql--SELECT * FROM master.dbo.sysprocesses where db_name(dbid)='CAS_DEV'
--==================================================================
-- kill ACTIVE CONNECTIONS END --===================================================================
--==================================================================
-- START -- DROP DATABASE --===================================================================
SELECT 'Dropping the ' + @DbName + ' database'
select @execSql = '
IF EXISTS (SELECT name FROM sys.databases WHERE name = N''' + @DbName + ''')
DROP DATABASE ' + @DbName + ' ;'SELECT 'RUNNING THE DROP DB QUERY :'
SELECT @execSqlexec sp_executesql @execSql--==================================================================
-- END -- DROP DATABASE --===================================================================
end --eof if
--SELECT SPID FROM master.dbo.sysprocesses
--where db_name(dbid) = 'CAS_DEV'
--'kill ' + convert(char(10), spid) + ' '
--from master.dbo.sysprocesses--where db_name(dbid) = @DbName--and--DBID <> 0
--and--spid <> @@spid-- END SCRIPT 1.KillConnectionsAndDropTheDb.sql -- START SCRIPT 0.BackUpAllMyDatabases.SQLSELECT 'RUNNING BACKUP'
DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE @DbBackupsDirectory nvarchar(1000)set @DbBackupsDirectory = N'$(DbBackupsDirectory)'
DECLARE DATABASES_CURSOR CURSOR FOR
selectDATABASE_NAME = db_name(s_mf.database_id)
fromsys.master_files s_mf
where-- ONLINEs_mf.state = 0 -- Only look at databases to which we have accessand has_dbaccess(db_name(s_mf.database_id)) = 1 -- Not master, tempdb or model--and db_name(s_mf.database_id) not in ('Master','tempdb','model')
group by s_mf.database_id
order by 1
OPEN DATABASES_CURSORFETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGINdeclare @DBFileName varchar(256)
set @DBFileName = @DbName + '_' + replace(convert(varchar, getdate(), 112), '-', '.') + '.bak'
--REMEMBER TO PUT HERE THE TRAILING \ FOR THE DIRECTORY !!!
exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''' + @DbBackupsDirectory+ '' +
@DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' +
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
ENDCLOSE DATABASES_CURSORDEALLOCATE DATABASES_CURSOR-- END SCRIPT 0.BackUpAllMyDatabases.sql:: START CMD FILE Deploy_DbName.cmd
:: DOCS AT THE EOF FILE
@ECHO OFF
ECHO BEFORE PROCEEDING WITH DEPLOYMENT DID YOU ENSURE
ECHO THAT YOU CHANGE THE CONFIGURATION ENTRIES IN THIS
ECHO CMD FILE IF NOT PRESS CTRL + C AND EDIT THE FILE
ECHO IF YES PRESS A KEY TO CONTINUE
PAUSE
::CHANGE HERE THE APPROPRIATE VARIABLES
SET DbName=DbName
::THIS IS WHERE THE DB FILES WILL BE SITUATED NOT THE \A AT THE END !!!
SET DataPath=D:\cas\Sql_dev\GenericSingleClickMsSqlDbDeployment\DATA\
:: CREATE THE DataPath DIRECTORY IF WE DO NOT HAVE IT
MKDIR %DataPath%
::CHANGE HERE THE APPROPRIATE VARIABLES
SET CurrentDate=%Date%
::the Configuration entry of the
SET DbBackupsDirectory=D:\cas\Sql_dev\GenericSingleClickMsSqlDbDeployment\DbBackups\
:: CREATE THE DbBackupsDirectory DIRECTORY IF WE DO NOT HAVE IT
MKDIR %DbBackupsDirectory%
ECHO CREATE FIRST BACKUP OF ALL DATABASES ON THE DEFAULT INSTANCE ONES:
ECHO CREATING THE LOG FILES
echo THIS IS THE ERROR LOG OF THE UPDATE OF THE %DbName% ON %DATE% >error.log
echo THIS IS THE INSTALL LOG OF THE UPDATE OF THE %DbName% ON %DATE% >install.log
ECHO STARTTING BACKUP
CD .\0.BackUp
ECHO FOR EACH SQL FILE DO RUN IT THIS WILL TAKE A WHILE
ECHO SINCE WE ARE GOING TO MAKE A BACKUP FOR ALL THE DATABASES ON THE CURRENT HOST
for /f %%i in ('dir *.SQL /s /b /o') DO ECHO %DATE% --- %TIME% RUNNING %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d MASTER -t 30000 -w 80 -u -p 1 -b -i %%i -r1 1>> "..\install.log" 2>> "..\error.log"
::PAUSE
ECHO GO ONE FOLDER UP
ECHO SLEEP FOR 1 SECOND
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH BACKUP GOING UP
cd ..
ECHO THE BACKUPS ARE IN THE FOLDER
ECHO %DbBackupsDirectory%
ECHO CLICK A KEY TO CONTINUE
ECHO ========================================================================================================================
PAUSE
ECHO STARTING INSTALLING FUNCTIONS
CD ".\1.Functions"ECHO FOR EACH SQL FILE DO RUN IT
ping -n 1 127.0.0.1 >NUL
for /f %%i in ('dir *.SQL /s /b /o') DO ECHO %DATE% --- %TIME% RUNNING %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log"
ECHO DONE WITH STORED PROCEDDURES GOING UP
cd ..
ping -n 1 127.0.0.1 >NUL
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO START TO EXECUTE THE MIXED FILES
CD .\1.Mixed
ECHO CREATING THE LOG FILES
echo. >>"..\error.log"echo. >>"..\install.log"ECHO FOR EACH SQL FILE DO RUN IT
for /f %%i in ('dir *.SQL /s /b /o') DO ECHO %DATE% --- %TIME% RUNNING %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i %%i -r1 1>> "..\install.log" 2>> "..\error.log"
ECHO GO ONE FOLDER UP
cd ..
ECHO SLEEP FOR 1 SECOND
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH MIXED GOING UP
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO STARTING INSTALLING TABLES
CD .\2.Tables
ECHO FOR EACH SQL FILE DO RUN IT
ping -n 1 127.0.0.1 >NUL
for /f %%i in ('dir *.SQL /s /b /o') DO ECHO %DATE% --- %TIME% RUNNING %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log"
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH TAbles GOING UP
cd ..
ping -n 1 127.0.0.1 >NUL
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO STARTING INSTALLING Views
CD ".\3.Views"ECHO FOR EACH SQL FILE DO RUN IT
ping -n 1 127.0.0.1 >NUL
for /f %%i in ('dir *.SQL /s /b /o') DO ECHO %DATE% --- %TIME% RUNNING %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log"
ECHO DONE WITH Views GOING UP
cd ..
ping -n 1 127.0.0.1 >NUL
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO STARTING INSTALLING stored procedures
CD ".\5.StoredProcedures"ECHO FOR EACH SQL FILE DO RUN IT
ping -n 1 127.0.0.1 >NUL
for /f %%i in ('dir *.SQL /s /b /o') DO ECHO %DATE% --- %TIME% RUNNING %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log"
ECHO DONE WITH STORED PROCEDDURES GOING UP
cd ..
ping -n 1 127.0.0.1 >NUL
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO STARTING INSTALLING Triggers
CD ".\6.Triggers"ECHO FOR EACH SQL FILE DO RUN IT
ping -n 1 127.0.0.1 >NUL
for /f %%i in ('dir *.SQL /s /b /o') DO ECHO %DATE% --- %TIME% RUNNING %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log"
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH Triggers GOING UP
cd ..
ping -n 1 127.0.0.1 >NUL
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO Please , Review the log files and sent them back to Advanced Application Support
set mailadd= yordan.georgiev^@consultingcompany.fi
:: WE USE THE "%cd%\bin\bmail.exe".EXE UTILITY TO SEND OURSELF AN E-MAIL CONTAINING THE TEXT FILE :: ALTERNATIVE SMTP MIGHT BE esebe107.NOE.company.com, UNCOMMENT THE NEXT LINE FOR ALTERN
::"%cd%\bin\bmail.exe" -s smtp.company.com -m install.log -t yordan.georgiev@consultingcompany.fi -a "POC 1.2 install log" -h
::"%cd%\bin\bmail.exe" -s smtp.company.com -m error.log -t yordan.georgiev@consultingcompany.fi -a "POC 1.2 error log" -h
CMD /c start /max INSTALL.LOG
CMD /C start /MAX ERROR.LOG
echo DONE !!!
ECHO HIT A KEY TO EXIT
PAUSE
:: WE GO TROUGH ALL THE FOLDERS AND RUN THE SQL FILES IN ALPHABETIC ORDER
:: START CMD FILE Deploy_DbName.cmd
:: PURPOSE :
:: THIS FILE DOES RUN ALL SQL SCRIPT FILES IN ORDER TO DEPLOY THE DbName
:: DATABASE.
:: ENVIRONMENT -- THIS SCRIPT NEEDS SQLCMD IN YOUR PATH + SQL SERVER 2005 / 2008
:: USAGE: EDIT THE CONFIGURATION ENTRIES AND DOUBLE CLICK. REVIEW THE
--CHECK THAT A TEMPORARY TABLE EXISTS
IF OBJECT_ID('tempdb..#ViewsList ') IS NOT NULL
drop TABLE #ViewsList
DROP TABLE #ViewsList
SELECT IDENTITY(INT,1,1) AS ID,
Name
INTO #ViewsListFROM sys.views --Only Views
WHERE name in ( 'view1' , 'view2) )
--WHERE OBJECTPROPERTY(OBJECT_ID(Name),'IsMSShipped') =0--AND ROUTINE_TYPE='View'
ORDER BY Name
DECLARE
@Loopid INT,
@MaxId INT,
@ViewName VARCHAR(50)
--This is the user that will get the execute/select permissions
SELECT @ViewName = 'SomeUser' --obsolete--Grab start and end values for the loop
SELECT @Loopid = 1,
@MaxId = MAX(ID)
FROM #ViewsList
DECLARE
@SQL VARCHAR(max),
@CurrentViewName VARCHAR(400)
--This is where the loop starts
WHILE @Loopid <= @MaxId BEGIN
--grab the function name and type
SELECT @CurrentViewName = Name
FROM #ViewsList
WHERE ID = @Loopid
--construct the statement
SELECT @SQL = 'use db ; SELECT Cols.TABLE_SCHEMA, Cols.TABLE_NAME, Cols.COLUMN_NAME, Cols.ORDINAL_POSITION, Cols.DATA_TYPE, Cols.NUMERIC_PRECISION, Cols.NUMERIC_SCALE, Cols.IS_NULLABLE, Cols.CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(Cols.TABLE_NAME), Cols.COLUMN_NAME, ''IsIdentity'') AS IsIdentity, ( SELECT COUNT(KCU.COLUMN_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.TABLE_NAME = TC.TABLE_NAME AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = ''PRIMARY KEY'' WHERE KCU.TABLE_NAME = Cols.TABLE_NAME AND KCU.COLUMN_NAME = Cols.COLUMN_NAME ) AS IsIndex FROM [INFORMATION_SCHEMA].[COLUMNS] Cols where Table_Name = ''' + @CurrentViewName + ''' AND Cols.TABLE_SCHEMA = ''schemaName'' ORDER BY Cols.TABLE_NAME, Cols.COLUMN_NAME , Cols.table_schema , Cols.ORDINAL_POSITION'
--exec (@SQL) --Action !!!
--print @sql
--increment counter
declare @bcpsql varchar(8000)
select @bcpsql = 'BCP "' + @sql+ '" queryout D:\data\dbName\xml\' + @CurrentViewName + '.xml -c -t, -T '
exec master..xp_cmdshell @bcpsql--print @bcpsqlSET @Loopid = @Loopid + 1END --eof while
--clean up
DROP TABLE #ViewsList
SELECT Cols.TABLE_SCHEMA,
Cols.TABLE_NAME,
Cols.COLUMN_NAME,
Cols.ORDINAL_POSITION,
Cols.DATA_TYPE,
Cols.NUMERIC_PRECISION,
Cols.NUMERIC_SCALE,
Cols.IS_NULLABLE,
Cols.CHARACTER_MAXIMUM_LENGTH,
COLUMNPROPERTY(object_id(Cols.TABLE_NAME), Cols.COLUMN_NAME, 'IsIdentity') AS IsIdentity,
( SELECT COUNT(KCU.COLUMN_NAME)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCUINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.TABLE_NAME = TC.TABLE_NAME
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAMEAND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE KCU.TABLE_NAME = Cols.TABLE_NAMEAND KCU.COLUMN_NAME = Cols.COLUMN_NAME) AS IsIndexFROM [INFORMATION_SCHEMA].[COLUMNS] Colswhere Table_Name in ( 'view1' , 'view2' )
--AND Cols.TABLE_SCHEMA = ??ORDER BY Cols.TABLE_NAME, Cols.COLUMN_NAME , Cols.table_schema , Cols.ORDINAL_POSITION
1: using System;
2: 3: namespace Gaf.Controller
4: {5: /// <summary>
6: /// Summary description for ControllerBase
7: /// </summary>
8: public abstract class ControllerBase
9: { 10: 11: 12: /// <summary>
13: /// Loads data from the database
14: /// </summary>
15: public abstract void LoadData();
16: 17: 18: /// <summary>
19: /// Stores the data from the UI to the db
20: /// </summary>
21: public abstract void StoreData();
22: 23: 24: 25: /// <summary>
26: /// Builds the userinterface of the view it represeents
27: /// </summary>
28: public abstract void BuildUI();
29: 30: 31: /// <summary>
32: /// Handles the response from the UI
33: /// </summary>
34: public abstract void UiResponse();
35: 36: 37: } //eof class
38: } //eof namespace
1: IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EventsLog_EventDate]') AND type = 'D')
2: BEGIN
3: ALTER TABLE [dbo].[DdlLog] DROP CONSTRAINT [DF_EventsLog_EventDate]
4: END
5: 6: GO
7: 8: /****** Object: Table [dbo].[DdlLog] Script Date: 04/09/2010 23:15:32 ******/
9: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DdlLog]') AND type in (N'U'))
10: DROP TABLE [dbo].[DdlLog]
11: GO
12: 13: /****** Object: Table [dbo].[DdlLog] Script Date: 04/09/2010 23:15:33 ******/
14: SET ANSI_NULLS ON
15: GO
16: 17: SET QUOTED_IDENTIFIER ON
18: GO
19: 20: CREATE TABLE [dbo].[DdlLog](
21: [LogId] [int] IDENTITY(1,1) NOT NULL,
22: [DatabaseName] [varchar](256) NOT NULL,
23: [EventType] [varchar](50) NOT NULL,
24: [ObjectName] [varchar](256) NOT NULL,
25: [ObjectType] [varchar](25) NOT NULL,
26: [SqlCommand] [varchar](max) NOT NULL,
27: [EventDate] [datetime] NOT NULL,
28: [LoginName] [varchar](256) NOT NULL
29: ) ON [PRIMARY]
30: 31: GO
32: 33: EXEC sys.sp_addextendedproperty @name=N'0.0.2.201003119', @value=N'Adding metadata for versioning(ysg)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
34: GO
35: 36: EXEC sys.sp_addextendedproperty @name=N'CurrentVersion', @value=N'0.0.2.201003119' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
37: GO
38: 39: EXEC sys.sp_addextendedproperty @name=N'IsPlatform', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
40: GO
41: 42: EXEC sys.sp_addextendedproperty @name=N'LastEditor', @value=N'Yordan Georgiev' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
43: GO
44: 45: EXEC sys.sp_addextendedproperty @name=N'Mar 10 2010 4:12PM', @value=N'Adding metadata for versioning(ysg)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
46: GO
47: 48: ALTER TABLE [dbo].[DdlLog] ADD CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()) FOR [EventDate]
49: GO
50: 51: 1: /****** Object: StoredProcedure [dbo].[procUtils_Debug] Script Date: 04/09/2010 23:17:23 ******/
2: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_Debug]') AND type in (N'P', N'PC'))
3: DROP PROCEDURE [dbo].[procUtils_Debug]
4: GO
5: 6: /****** Object: StoredProcedure [dbo].[procUtils_Debug] Script Date: 04/09/2010 23:17:23 ******/
7: SET ANSI_NULLS ON
8: GO
9: 10: SET QUOTED_IDENTIFIER ON
11: GO
12: 13: 14: 15: CREATE PROCEDURE [dbo].[procUtils_Debug]
16: -- BEGIN DEFAULT SQL API
17: @UserSessionId [bigint],18: @PageTypeId [int],
19: @Msg [varchar](200) OUTPUT,
20: @DebugMsg [varchar](200) OUTPUT,
21: @Ret [int] OUTPUT ,
22: -- END DEFAULT SQL API
23: @Time datetime= NULL,
24: @NiceTime varchar(200)= NULL,
25: @Domain_User varchar(200),
26: --@Msg varchar(4000),
27: @LogLevel int= NULL,
28: --@DebugMsg varchar(4000),
29: @PageURL varchar(2000)= NULL,
30: @ClassName varchar(200)= NULL,
31: @MethodName varchar(200)= NULL,
32: @MethodNameGui varchar(4000)= NULL,
33: --@Ret int,
34: @LineNumber int= NULL,
35: @ProcedureName varchar(200)= NULL,
36: @ProcedureStep varchar(4000) = NULL,
37: @AlNameId int,
38: @DbNameId int
39: 40: 41: AS
42: BEGIN -- proc start
43: SET NOCOUNT ON;
44: 45: declare @ThisProcName varchar(200)
46: declare @ProcStep varchar(4000)
47: 48: set @ThisProcName = ( SELECT OBJECT_NAME(@@PROCID))
49: set @Msg = ''
50: set @DebugMsg = ''
51: set @ProcStep = ''
52: 53: 54: BEGIN TRY --begin try
55: 56: set @Msg = ISNULL( @msg , '')
57: set @DebugMsg = ISNULL ( @DebugMsg , '')
58: set @Ret = ISNULL ( @Ret , 1 )
59: 60: BEGIN TRAN
61: 62: INSERT INTO [dbo].[DebugLog] ([Time], [NiceTime], [Domain_User], [Msg], [LogLevel], [DebugMsg], [PageURL], [ClassName], [MethodName], [MethodNameGui], [Ret], [LineNumber], [ProcedureName], [ProcedureStep], [AlNameId], [DbNameId], [UserSessionId], [PageTypeId])
63: SELECT @Time, @NiceTime, @Domain_User, @Msg, @LogLevel, @DebugMsg, @PageURL, @ClassName, @MethodName, @MethodNameGui, @Ret, @LineNumber, @ProcedureName, @ProcedureStep, @AlNameId, @DbNameId, @UserSessionId, @PageTypeId
64: 65: -- Begin Return Select <- do not remove
66: SELECT [DebugLogId], [TimeStamp], [Time], [NiceTime], [Domain_User], [Msg], [LogLevel], [DebugMsg], [PageURL], [ClassName], [MethodName], [MethodNameGui], [Ret], [LineNumber], [ProcedureName], [ProcedureStep], [AlNameId], [DbNameId], [UserSessionId], [PageTypeId]
67: FROM [dbo].[DebugLog]
68: WHERE [DebugLogId] = SCOPE_IDENTITY()
69: -- End Return Select <- do not remove
70: 71: COMMIT
72: 73: set @Ret = 0
74: 75: 76: return @Ret
77: 78: END TRY --end try
79: 80: BEGIN CATCH
81: PRINT 'In CATCH block.
82: Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '
83: Error message: ' + ERROR_MESSAGE() + '
84: Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '
85: Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '
86: XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));
87: 88: set @Msg = 'Failed to retrieve Report '
89: set @DebugMsg = ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) +
90: 'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) +
91: 'Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + 'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10))
92: 93: --record the error in the database
94: set @DebugMsg = @DebugMsg + @Msg
95: 96: 97: set @Ret = 1
98: 99: END CATCH
100: 101: 102: return @Ret
103: END --PROC END
104: 105: /* 106: <ProcName> procUtils_Debug </ProcName> 107: <doc>108: Generates the Update Procedure on any table a
109: </doc> <use>
110: EXEC [dbo].[procUtils_GenerateUpdateProc] @TableName = N'Whatever'
111: </use> */
112: 113: GO
114: 115: GRANT EXECUTE ON [dbo].[procUtils_Debug] TO [public] AS [dbo]
116: GO
117: 118: /* START wrap long lines */
white-space: pre-wrap; /* css-3 */
white-space: -moz-pre-wrap; /* Mozilla, since 1999 */
white-space: -pre-wrap; /* Opera 4-6 */
white-space: -o-pre-wrap; /* Opera 7 */
word-wrap: break-word; /* Internet Explorer 5.5+ */
/* END wrap long lines */
@ECHO OFF
ECHO RunAll.cmd
ECHO THIS BATCH FILE RUNS RECURSIVELY ALL THE *.SQL FILES UNDER THE CURRENT DIRECTORY
ECHO AND OOUTPUTS OK MSGS TO AND INSTALL.LOG IN THE UPPER FOLDER
ECHO AND OUTPUTS THE ERROR MESSAGES IN THE ERROR.LOG IN THE UPPER FOLDER
ECHO MAKE SURE SQLCMD IS IN YOUR PATH !!!
ECHO PRESS A KEY TO START RUNNING THE BATCH FILES
PAUSE
for /f %%i in ('dir *.SQL /s /b /o') DO ECHO %DATE% --- %TIME% RUNNING %%i 1>>"..\install.log"&SQLCMD -U SqlLoginName -P Password -H HostName -d DataBaseName -t 30000 -w 80 -u -p 1 -b -i %%i -r1 1>> "..\install.log" 2>> "..\error.log"
ECHO DONE. PRESS KEY TO VIEW THE ERROR.LOG AND THE INSTALL.LOG
start /max ..\install.log&start /max ..\error.log
PAUSE
--use CAS_DEV ;
/****** Object: StoredProcedure [dbo].[procUtils_ExampleProc] Script Date: 03/23/2010 14:31:38 ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_ExampleProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[procUtils_ExampleProc]
GO
/****** Object: StoredProcedure [dbo].[procUtils_ExampleProc]
See doc bellow
Script Date: 03/23/2010 14:31:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[procUtils_ExampleProc]
-- BEGIN DEFAULT SQL API
@UserSessionId [bigint],
@PageTypeId [int],
@Msg [varchar](200) OUTPUT,
@DebugMsg [varchar](200) OUTPUT,
@Ret [int] OUTPUT
-- END DEFAULT SQL API
AS
BEGIN -- proc start
SET NOCOUNT ON;
declare @ThisProcName varchar(200)
declare @ProcStep varchar(4000)
set @ThisProcName = ( SELECT OBJECT_NAME(@@PROCID))
set @Msg = ''
set @DebugMsg = ''
set @ProcStep = ''
DECLARE @WinNewLine nvarchar(2)
set @WinNewLine = CONVERT ( nvarchar(2) , CHAR(13) + CHAR(10))
DECLARE @Tab nvarchar(1)
set @Tab = CONVERT(NVARCHAR(1) , CHAR(9))
BEGIN TRY --begin try
set @Ret = 1 --assume false from the beginning
set @Msg = 'The Msg To Show to the user'
set @Ret = @@ERROR
-- action !!! put code hrere
return @Ret
END TRY --end try
BEGIN CATCH
PRINT 'In CATCH block.
Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '
Error message: ' + ERROR_MESSAGE() + '
Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '
Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '
XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));
set @Msg = 'Failed to retrieve Report '
set @DebugMsg = ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + 'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10))
--record the error in the database
set @DebugMsg = @DebugMsg + @Msg
set @Ret = 1
END CATCH
return @Ret
END --PROC END
/*
<ProcName> procUtils_ExampleProc </ProcName>
<doc>
Generates the Update Procedure on any table a
</doc> <use>
EXEC [dbo].[procUtils_GenerateUpdateProc] @TableName = N'Whatever'
</use> */
GO
-- START PERMISSIONS
GRANT EXECUTE ON [dbo].[procUtils_ExampleProc] TO [ReplaceHereDbRole] AS [dbo]
GO
-- END PERMISSIONS
/****** Object: StoredProcedure [dbo].[procUtils_KillAllSqlServerConnections] Script Date: 03/23/2010 14:31:38 source:I HEART MICROSOFT BLOG ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_KillAllSqlServerConnections]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[procUtils_KillAllSqlServerConnections]
GO/****** Object: StoredProcedure [dbo].[procUtils_KillAllSqlServerConnections] See doc bellow
Script Date: 03/23/2010 14:31:38 ******/SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE PROCEDURE [dbo].[procUtils_KillAllSqlServerConnections]
@DbName varchar(200) AS BEGIN -- proc start
SET NOCOUNT ON;
declare @ThisProcName varchar(200)
declare @ProcStep varchar(4000)
declare @msg nvarchar(max)
declare @debugmsg nvarchar(max)
declare @ret int
-- use this variable while generating code DECLARE @WinNewLine nvarchar(2)set @WinNewLine = CONVERT ( nvarchar(2) , CHAR(13) + CHAR(10))
-- this is a horizontal tabDECLARE @Tab nvarchar(1)set @Tab = CONVERT(NVARCHAR(1) , CHAR(9))
set @ThisProcName = ( SELECT OBJECT_NAME(@@PROCID))
set @Msg = ''
set @DebugMsg = ''
set @ProcStep = ''
BEGIN TRY --begin try
set @Ret = 1 --assume false from the beginning
set @Msg = 'The Msg To Show to the user'
set @Ret = @@ERROR
declare @sqlCode nvarchar(max)
set @sqlCode = ''
--================================================
-- START SHOW ALL THE CONNECTIONS set @sqlCode = @sqlCode +
'USE MASTER ;SELECT * FROM sysprocesses WHERE dbid = DB_ID(''' + @DbName + ''') ;'-- END SHOW ALL THE CONNECTIONS --================================================
--================================================
-- START KILL ALL THE CONNECTIONS set @sqlCode = @sqlCode +
'USE master ;DECLARE @dbname varchar(30), @spid varchar(10), @start datetime
SELECT @start = current_timestamp, @dbname = ''' + @DbName + '''
-- Timeout after 5 mts
while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND
datediff(mi, @start, current_timestamp) < 5)
begin
DECLARE spids CURSOR FOR
SELECT convert(varchar, spid) FROM sysprocesses
WHERE dbid = db_id(@dbname)
OPEN spids
while(1=1)
BEGIN
FETCH spids INTO @spid
IF @@fetch_status < 0 BREAK
exec(''kill '' + @spid)END
DEALLOCATE spids
END ; '
-- END KILL ALL THE CONNECTIONS --================================================
SELECT @sqlCode
EXEC sp_executesql @sqlCode
SET @sqlCode = ''
return @Ret END TRY --end try
BEGIN CATCH PRINT 'In CATCH block.
Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '
Error message: ' + ERROR_MESSAGE() + ' Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '
Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '
XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));
set @Msg = 'An Error occured '
set @DebugMsg = ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + @WinNewLine +
'Error message: ' + @WinNewLine + ERROR_MESSAGE() +'Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + @WinNewLine +
'Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + @WinNewLine +
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10)) + @WinNewLine
--record the error in the database
set @DebugMsg = @DebugMsg + @Msg set @Ret = 1 END CATCH return @Ret END --PROC END
/*
<ProcName> procUtils_KillAllSqlServerConnections </ProcName>
<doc>
Generates the Update Procedure on any table a
</doc> <use>exec procUtils_KillAllSqlServerConnections @DbName = 'CAS_DEV'
</use> */ GO-- START PERMISSIONS--GRANT EXECUTE ON [dbo].[procUtils_KillAllSqlServerConnections] TO [ReplaceHereDbRole] AS [dbo]
GO-- END PERMISSIONS
Posts
All Comments
