Search This Blog

2010-04-29

how-to copy paste formatting in Excel 2007

- Select the cells
- Ctrl + C to copy all the contents and formatting
- Alt + E , S , T , Enter - to paste only the formatting

2010-04-28

how-to export my putty settings into a reg file

regedit /e "C:\Documents and Settings\userName\desktop\putty-registry.reg" HKEY_CURRENT_USER\Software\Simontatham

how-to run a command on a remote host with putty

putty -load userName@hostName -pw pass -m "C:\Documents and Settings\yogeorgi\Desktop\command.txt"

2010-04-27

how-to kick out users from rdp session

query session /server:123.123.123.123

echo check the session id you want to kick
reset session session_id_you_want_to_kick

2010-04-26

2.CreateTheDb.sql



-- START SCRIPT 2.CreateTheDb.sql
 
DECLARE @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

2.CreateTheDb.sql

-- START SCRIPT 2.CreateTheDb.sql

DECLARE @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

1.KillConnectionsAndDropTheDb.sql



-- START SCRIPT 1.KillConnectionsAndDropTheDb.sql 
use master ; 
 
 
 
 
declare @DbName varchar(100) 
-- Set the name of the database to restore
set @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 path
select @databaseDataFile = rtrim([Name]),
@databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
inner join
master.dbo.sysfilegroups as groups
on
 
files.groupID = groups.groupID
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @DbName
)
 
-- Get the log file and its path
select @databaseLogFile = rtrim([Name]),
@databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @DbName
)
and
groupID = 0
 
 
 
 
 
 
--KILL ACTIVE CONNECTIONS START 
 
SELECT 'Killing active connections to the "' + @DbName + '" database'
 
-- Create the sql to kill the active database connections
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' ; '
from master.dbo.sysprocesses
where db_name(dbid) = @DbName
and
DBID <> 0
and
spid <> @@spid
exec (@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 @execSql
exec 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 

0.BackUpAllMyDatabases



-- START SCRIPT 0.BackUpAllMyDatabases.SQL
 
SELECT 'RUNNING BACKUP' 
 
DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE @DbBackupsDirectory nvarchar(1000)
set @DbBackupsDirectory = N'$(DbBackupsDirectory)'
 
 
DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME   = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
-- ONLINE
s_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_CURSOR
 
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
 
WHILE @@FETCH_STATUS = 0
BEGIN
declare @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
END
 
CLOSE DATABASES_CURSOR
DEALLOCATE 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 

Why CodePlex sucks ?!

Let's be frank !!!
Microsoft tries to lure developers always ... , but they STILL do not know exactly what to do to open source

There might be some internal forces trying to create some change , but ... the push is weak ... Why ?

Well here is my experience .


I have IMHO greate ideas. As a MS developer I want to submit them to CodePlex and ...

First you start with the somewhat clumsy interface ... Ok I can bear that ...
Than the navigation is somewhat peacky ... go forth go back ... did you remember to check that

Than you succeed to create the project ... but hey ... now you have to download either the Tourtoise or whatever it's name is client which WILL break your Explorer and force you to reinstall the whole Windows with the whole environment you have spent some 3 days to set up or start using some command line tools with some mystical error messages and freacky logic ....

I gave up. This is why all the my crappy code will be published using Google's Blogger. I don't care that noone will download / read them ... I am just happy that I CAN publish them without much of a hassle.

how-to start / stop the javahost process on Unix for OBIEE

Under UNIX, use the command line for starting the Javahost service:
./run.sh [-h] [-service] [-SawConfigRoot configrootdir] [-DefaultCordaRoot
configrootdir] [-Config instanceconfig]
■ Under UNIX, use the command line for stopping the Javahost service:
./shutdown.sh [-h] [-Config instanceconfig] [-SawConfigRoot configrootdir] [-Port
port] [-Host host]

how-to start stop Oracle BI Scheduler on Solaris

Solaris run-sch.sh { start | start64 | stop | forcestop |
autorestart [ ] | autorestart64 [
] }

how-to start OBIEE - Oracle BI Server

run-sa.sh { start | start64 | stop | forcestop |
autorestart [ ] | autorestart64 [
] }

2010-04-24

The first afordable electric car

and why does it mattter

why focus fusion will be the killer in energy business

source: Focus Fusion Society's site Q&A

Since Edison’s time there has been one main way to produce electricity. A heat source boils water to produce high temperature steam. The steam is fed under pressure to a turbine. The spinning of the turbine feeds power to a spinning electric generator producing electric power. Whether the source of heat is coal, oil, gas, or nuclear fission the basic process is the same. The majority of the cost of a modern power station comes from the turbine, electric generator, and the associated plumbing to handle the steam and water. So replacing the heat source cannot produce cheap electricity.

A focus fusion reactor would produce electricity very differently. The energy from fusion reactions is released mainly in the form of a high energy pulsed beam of helium nuclei. Since the nuclei are electrically charged, this beam is already an electric current. All that is needed is to capture this electric energy into an electric circuit. This can be done by allowing the pulsed beam to generate electric currents in a series of coils as it passes through them. This is much the same way that a transformer works, stepping electric power down from the high voltage of a transmission line to the low voltage used in homes and factories. It is also like a particle accelerator run in reverse. Such an electrical transformation can be highly efficient, probably around 80-90%. What is most important is that it is exceedingly cheap and compact. The whole apparatus of steam turbine and electrical generator are eliminated. A 20MW focus fusion reactor may cost around $500,000 and produce electricity for 1/20th of a cent per kWh. This is a hundred times less than current electric costs. Fuel costs will be negligible because a 20MW plant will require only twenty pounds of fuel a year.

2010-04-21

How-to check that a temporary table exists and drop it if yes in mssql server



--CHECK THAT A TEMPORARY TABLE EXISTS 
IF OBJECT_ID('tempdb..#ViewsList ') IS NOT NULL 
drop TABLE #ViewsList 

how-to copy the meta data of views into xml files per view on mssqlserver



DROP TABLE #ViewsList
 
 
SELECT IDENTITY(INT,1,1) AS ID,
Name
INTO #ViewsList
FROM 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 @bcpsql
 
SET @Loopid = @Loopid + 1
 
END --eof while
 
--clean up
DROP TABLE #ViewsList
 

how-to use bcp to run queries and save them into files

BCP "SELECT * FROM dbname.schema_name.TAbleName" queryout D:\Data\test.csv -c -t, -T

how-to get the meta data of a list of views in sql server ( mssql)



 
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 in ( 'view1' , 'view2' )
--AND Cols.TABLE_SCHEMA = ??
ORDER BY Cols.TABLE_NAME, Cols.COLUMN_NAME , Cols.table_schema , Cols.ORDINAL_POSITION

2010-04-19

ksh keyboard shortcuts

Forward One Character —-> —-> [ESC]-l
Backward One Character —-> [ESC]-h
Delete One Character —-> [ESC]-x
Replace One Character —-> [ESC]-r
Forward End of Word —-> [ESC]-e
Backward Beginning of Word —-> [ESC]-b
Delete to End of Line —-> [ESC]-D
Insert Text (current space) —-> [ESC]-i
Insert Text (adjacent space) —-> [ESC]-a
Move Cursor to Beginning of Line —-> [ESC]-^
Move Cursor to End of Line —-> [ESC]-$

2010-04-10

Testing enclosures

URL

/* 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 */

2010-04-09



   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 

how-to debug effectively in mssql server 2008



   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:   

How-to wrap long files in css to all browsers



/* 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 */

how-to run all the sql files under the current dir recursively in mssql server



@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

A good template for mssql server stored procedure



--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

2010-04-07

how-to kill all the connections to an sql server in tsql via a stored procedure



/****** 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
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 tab
DECLARE @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

Labels

perl (41) Cheat Sheet (25) how-to (24) windows (14) sql server 2008 (13) linux (12) oracle (12) sql (12) Unix (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) cygwin (7) meta data mssql (7) metadata (7) bash (6) code generation (6) Informatica (5) cheatsheet (5) energy (5) tsql (5) utilities (5) excel (4) future (4) generic (4) git cheat sheet (4) html (4) perl modules (4) programs (4) settings (4) sh (4) shortcuts (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) logging (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) python (3) sftp (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) architecture (2) ascii (2) awk (2) batch (2) cas (2) chrome extensions (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) excel shortcuts (2) export (2) file (2) free programs (2) informatica sql repository (2) linux cheat sheet (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nz netezza cheat sheet (2) nzsql (2) ora (2) prediction (2) publish (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) .virmrc vim settings configs (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) IDEA (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) JavaScript anchor html jquery (1) Khan Academy (1) LINUX UNIX BASH AND CYGWIN TIPS AND TRICKS (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Scala (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture input output (1) archive (1) arguments (1) avatar (1) aws cheat sheet cli (1) aws cli (1) aws cli amazon cheat sheet (1) aws elb (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash perl search and replace (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) chrome (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy paste (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) db2 cheat sheet (1) db2 starter ibm bash Linux (1) debt (1) diagram (1) dictionaries (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) freaky (1) functions (1) fusion research (1) german (1) git gitlab issues handling system (1) google cli (1) google code (1) google command line interface (1) gpg (1) ha (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ibm db2 cognos installation example db deployment provisioning (1) ideas (1) image (1) informatica oracle sql (1) informatica repo sql workflows sessions file source dir (1) informatica source files etl (1) install (1) isg-pub issue-tracker architecture (1) it management best practices (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash shell grep xargs (1) linux bash tips and t ricks (1) linux bash unix cygwin cheatsheet (1) linux bash user accounts password (1) linux bash xargs space (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql cheat sheet (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz table count rows (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) openssl (1) oracle PL/SQL (1) oracle Perl perl (1) oracle installation usability (1) oracle number formatting format-model ora-sql oracle (1) oracle templates create table (1) oracle trigger generic autoincrement (1) oracle vbox virtual box cheat sheet (1) oracle virtual box cheat sheet (1) outlook (1) parser (1) password (1) paths (1) perl @INC compile-time run-time (1) perl disk usage administration Linux Unix (1) perl modules configuration management (1) permissions (1) php (1) picasa (1) platform (1) postgreSQL how-to (1) powerShell cmd cygwin mintty.exe terminal (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scala ScalaFmt (1) scp (1) scripts (1) scsi (1) search and replace (1) sed (1) sendEmail (1) sh stub (1) shortcuts Windows sql developer Oracle (1) sidebar (1) silicon (1) smells (1) smtp (1) software development (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) sshd cygwin (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) system design (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tmux .tmux.conf configuration (1) tmux efficiency bash (1) tool (1) ui code prototyping tips and tricks (1) umask Linux Unix bash file permissions chmod (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vbox virtual box cheat sheet (1) vim perl regex bash search for string (1) vim recursively hacks (1) vim starter (1) vim-cheat-sheet vim cheat-sheet (1) vimeo (1) visual stuio (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows 8 (1) windows programs (1) windows reinstall (1) windows utility batch perl space Windows::Clipboard (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List