Reset Identity Column in SQL Server

To check the current value of an identity field use:

DBCC CHECKIDENT (tablename, NORESEED)

To set the next identity value to 100 for a table use:

DBCC CHECKIDENT (tablename, RESEED, 99)

Note that the next value will be whatever you reseed + 1.

Adding a Left Hand Navigation Tree to Confluence

To add a laft navigation tree to a confluence space:

Add a new page a the root of the space called TreeNavigation with the contents as follows.

{pagetree:sort=natural|excerpt=false|reverse=false|startDepth=1|expandCollapseAll=true|searchBox=false}

In Space Admin -> Look and Feel -> Layout , create a custom page layout

Replace:

<div class="wiki-content">
$body
</div>

With:

#if ($action.isPrintableVersion() == false)
<style>
.spacetree * ul{
padding-left:0px;
margin-left: 0px;
}
.spacetree * li{
margin-left: 5px;
padding-left:5px;
}


</style>

<table cellspacing="2" cellpadding="5">
<tr>
<td valign="top" align="left" width="22%" bgcolor="#F9F9F9" class="noprint treenav">
<div class="tabletitle">Table of Contents</div>
<div class="spacetree">
#includePage($helper.spaceKey "TreeNavigation")
</div>
</td>
<td valign="top" align="left" width="78%" class="pagecontent">
<div class="wiki-content">
$body
</div>
</td>
</tr>
</table>
#else
<div class="wiki-content">
$body
</div>
#end

Unix Screen Command

SCREEN - The screen program allows you to use multiple windows (virtual VT100 terminals) in Unix. And when you lost connection your sessions are still alive and you can reconnect to them. screen is instaled on maidenhead servers.
Some useful items:

Command Description
screen Start screen session
screen -r Resume your screen sessions (after crash)
Ctrl-a c Create new window (shell)
Ctrl-a k Kill the current window (if this is last window it kills/ends screen)
Ctrl-a w List all windows (the current window is marked with "*")
Ctrl-a 0-9 Go to a window numbered 0-9
Ctrl-a n Go to the next window
Ctrl-a Ctrl-a Toggle between the current and previous window
Ctrl-a [ Start copy mode
Ctrl-a ] Paste copied text
Ctrl-a ? Help (display a list of commands)
Ctrl-a Ctrl-\ Quit screen
Ctrl-a D (Shift-d) Power detach and logout
Ctrl-a d Detach but keep shell window open
Press the Spacebar or Enter to end a command.

Problem:
You can't re-attached to the session: screen -r
There is a screen on:
14426.pts-3.data4 (Attached)
There is no screen to be resumed.

Solution:
Screen -list

This will display a list of your current screen sessions. For instance, if you had one attached and one dead screen, you would see:

There are screens on:
25542.pts-28.hostname (Dead ???)
1636.pts-21.hostname (Attached)
Remove dead screens with 'screen -wipe'.
2 Sockets in /tmp/screens/S-username.

To detach an attached screen, enter:

screen -D

If you have more than one attached screen, you can specify a particular screen to detach. For example, to detach the screen in the above example, you would enter:

screen -D 1636.pts-21.hostname

Once you've done this, you can resume the screen by entering the screen -r command.

source: http://kb.iu.edu/data/acuy.htmlman screen

CSV to Table Conversion

DECLARE @string varchar(250), @delim char(1)

SET @string = '1,12,15,875,100,9'

SET @delim = ','

CREATE TABLE #result(Element int)

DECLARE @sql varchar(4000)

SELECT @sql = 'INSERT INTO #Result SELECT ' + REPLACE(@string, @Delim, ' UNION ALL SELECT ')

exec (@sql)

SELECT * FROM #result

DROP TABLE #result

Convert the first character of each word to upper case

CREATE Function init_caps

(

@pi_string varchar(max)

)

RETURNS varchar(max)

AS

BEGIN

DECLARE

@string varchar(max)

,@formatted_string varchar(max)

,@length int

SET @string = ltrim(rtrim(@pi_string))

SET @length = len(@string)

SET @formatted_string = upper(left(@string,1)) + lower(substring(@string,2,@length))

-- change first character after each space to upper

DECLARE @space int

SET @space = charindex(' ', @string)

WHILE @space > 0

BEGIN

SET @formatted_string = left(@formatted_string,@space)

+ upper(substring(@formatted_string,@space+1, 1))

+ substring(@formatted_string,@space+2, 2000)

SET @space = charindex(' ', @string, @space+1)

END

RETURN @formatted_string

END

GO

Powershell SMO Connection

# Load SMO libraries

[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, `

Version=9.0.242.0, Culture=neutral, `

PublicKeyToken=89845dcd8080cc91") > $null

[Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, `

Version=9.0.242.0, Culture=neutral, `

PublicKeyToken=89845dcd8080cc91") > $null

[Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, `

Version=9.0.242.0, Culture=neutral, `

PublicKeyToken=89845dcd8080cc91") > $null

[Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, `

Version=9.0.242.0, Culture=neutral, `

PublicKeyToken=89845dcd8080cc91") > $null

# define connection properties

[string] $server_name = 'myserver'

[string] $db_name = 'mydb'

[bool] $windows_login = $true

# Create server object

$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server_name

# if not using windows authentication then prompt for credentials

if ($windows_login -eq $false)

{

# prompt for connection details

$server.ConnectionContext.LoginSecure=$false;

$credential = Get-Credential

$username = $credential.UserName -replace("\\","")

$server.ConnectionContext.set_Login($username)

$server.ConnectionContext.set_SecurePassword($credential.Password)

}

$db = $server.Databases[$db_name]

# list all tables

foreach ($table in $db.Tables)

{

write-host $table.name

}