Bookmark : Concatenating row values in TSQL

A thorough article examining how to concatenate row values :

Concatenating row values in TSQL

Powerpivot ???

I was recently privileged to see MS BI Architect Donald Farmer demonstrate Project Gemini (now PowerPivot) at SqlBits V.

Basically PowerPivot is Excel on Steroids, enabling you to define datasets as tables on the fly and join them.

Empowering you to manipulate millions of data rows on a desktop pc, it achieves this using the same compression technology used in SQL 2008.

I'll certainly be looking out for this in SQL 2008 R2 / Office 2010.


The best analogy for PowerPivot so far...

SQLBits V

I attended the excellent (and free) SQLBits conference this weekend.
A lot of great speakers, and an overload of information! I also got in for free on the SQL/Windows 2008 R2 day , thanks to Andrew Fryer @ Microsoft throwing a free ticket my way.

It was interesting to see the current fad that is twitter heavily in use, with attendees using the hashtag #sqlbits to provide instant feedback and meet up. Iphones and tweeting have infiltrated the sql community far more than i had realised.

I saw a range of speakers and have come away with multiple notes to add to my task list and future goals. The majority of sessions were filmed so hopefully the material will be available shortly at the sqlbits website. The speakers I saw are listed below with links to their blogs.

Keynote and Powerpivot demo
Donald Farmer

When a query plan goes wrong
Simon Sabin

SSIS in SQL Server 2008
Allan Mitchell

T-SQL Tuning
Brent Ozar

Powershell – old tricks for a new dog
Martin Bell

Optimistic Concurrency Internals
James Rowland-Jones

SQL Server optimization stuff you won’t find on Google (yet)
Andre Kamman

In addition to the SQL & BI content there were lunchtime sponsor sessions by many vendors. On both Friday and Saturday I attended 2 excellent sessions presented by Mark Whitehorn, explained much better here by Ash.

SQL Service Account Settings Summary

A quick note and screenshot to remind me of those all important permissions I've granted to my SQL Service account in Active Directory.

"Act as part of the operating system"
"Adjust memory quotas for a process"
"Bypass traverse checking"
"Lock pages in memory"
"Log on as a batch job"
"Log on as a service"
"Perform volume maintenance tasks"
"Replace a process level token"
"Allow log on locally"

Bookmark : Top 10 Free Windows tools for IT pros

The Top 10 Free Windows tools for IT pros
http://www.infoworld.com/d/windows/top-10-windows-tools-it-pros-792?page=0,1

1) Sysinternals - Advanced Windows Diagnostics
2) HWiNFO32 - CPU Information
3) CCleaner - Registry and file system cleaner
4) Recuva - Recover deleted files
5) FileZilla - FTP Client & FTP Server
6) Virtual CloneDrive - Mounts ISO images as drives
7) Defraggler - Disk Defragmentation tool
8) ImgBurn - Burn ISO Images to disk
9) 7-Zip - Free Open Source alternative to Winzip
10) VirtualBox - Free Virtualisation Software

Changing Windows Password in Windows 2008 RDP Session

(because Alt-Ctrl-Del operates on the host!)

Start > ‘ Windows Security ’

Then you are presented with ‘ Lock / Log off / Change a password... / Task Manager ’


Simples...

Noteworthy : Career Links (December 2009)

Career :

How to survive in IT (the soft evil skills)

Understanding your manager

Agile :

Chickens, Pigs, and Really Inappropriate Terminology

SQL 2008: Modifying Resource Governor's workload classification

How to change Resource Governor Classifier function on the fly...
Here's the TSQL ...

-- 1) Disable Resource Governor
ALTER RESOURCE GOVERNOR  DISABLE 
GO

-- 2) Remove the classifier function from Resource Governor so that you can change it
ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = NULL)
GO

-- 3) Change the function itself
ALTER FUNCTION ResourceGovClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
DECLARE @classification VARCHAR(32)

IF SUSER_SNAME() LIKE 'Dev_%'
SET @classification = 'Workload_Developer'

IF SUSER_SNAME() LIKE 'Adm_%'
SET @classification = 'Workload_DBA'

IF SUSER_SNAME() = 'applogin' 
SET @classification = 'Workload_App'


RETURN @classification 
END
GO

-- 4)  Set Resource Governor to use the revised function
ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.ResourceGovClassifier)
GO

-- 5) Re-enable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

TSQL : Row count & Total Rows by OVER

Demonstrating OVER, PARTITION BY and ROW_NUMBER to return total number of rows and a incrementing row count.

USE AdventureWorks
GO
SELECT 
COUNT(*) OVER(PARTITION BY NULL) AS TotalRowCount 
,ROW_NUMBER() OVER (ORDER BY [TransactionID] DESC) AS [ASC_ROWNUMBER]
,[TransactionID]
,[ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionDate]
,[TransactionType]
,[Quantity]
,[ActualCost]
,[ModifiedDate]
FROM [AdventureWorks].[Production].[TransactionHistory]
GO 

VB.NET / SQL CLR / Decode encoded URL

Immersing myself in VB for SQL CLR functionality I found this to Decode an encoded URL.

I've made one minor change from chr$ to chr to make it complient for .NET 3.5

Private Shared Function URLDecode(ByVal txt As String) As String
Dim txt_len As Integer
Dim i As Integer
Dim ch As String
Dim digits As String
Dim result As String

result = ""
txt_len = Len(txt)
i = 1
Do While i <= txt_len
' Examine the next character.
ch = Mid$(txt, i, 1)
If ch = "+" Then
' Convert to space character.
result = result & " "
ElseIf ch <> "%" Then
' Normal character.
result = result & ch
ElseIf i > txt_len - 2 Then
' No room for two following digits.
result = result & ch
Else
' Get the next two hex digits.
digits = Mid$(txt, i + 1, 2)
result = result & Chr(CInt("&H" & digits))
i = i + 2
End If
i = i + 1
Loop

URLDecode = result
End Function

Bookmark : How To Create tables in Blogger via Excel

This link shows how to apply a style to a DIV and prevent tables expanding.
http://www.mt-soft.com.ar/2008/12/06/how-to-create-tables-in-blogger/

I found the easiest way to generate a clean table structure (i.e. without the styling info that a MS Word export provides) is to create a table in Excel by making up the row information in a formula.

for example >

=""&B1&"" - for each cell
=""&F1&G1&H1&I1&"" - for the enclosing row

SSIS : Expression Operators

Cast (MSDN)	Data Type Conversion
()	Parentheses, Evaluation order of calculation
+	Addition when Numeric
+	Concantenation when Strings
-	Subtraction
-	Negation (if signed data type)
*	Multiplication
/	Division
%	Modulo
||	Logical OR
&&	Logical AND
!	Logical Not
|	Bitwise Inclusive OR
^	Bitwise Exclusive OR
&	Bitwise AND
~	Bitwise Not
==	Equal
!=	Unequal
>	Greater Than
<	Less Than
>=	Greater Than or Equal To
<=	Less Than or Equal To
(condition) ? (true_value) : (false_value) (MSDN)	Boolean Condition

SSIS 2008 / SQL 2008 / .NET Data Types

SQL Server	SSIS	.NET	SQL CLR (System.Data.SqlTypes namespace)
bigint	DT_I8	Int64	SqlInt64
binary	DT_BYTES	Byte[]	SqlBytes, SqlBinary
bit	DT_BOOL	Boolean	SqlBoolean
char	DT_STR	None	None
cursor	None	None	None
date	DT_DATE	DateTime	SqlDateTime
datetime	DT_DBTIMESTAMP	DateTime	SqlDateTime
datetime2	DT_DBTIMESTAMP2	DateTime	SqlDateTime
decimal	DT_DECIMAL	Decimal	SqlDecimal
float	DT_R8	Double	SqlDouble
image	DT_IMAGE	None	None
int	DT_I4	Int32	SqlInt32
money	DT_CY	Decimal	SqlMoney
nchar	DT_WSTR	String, Char[]	SqlChars, SqlString
ntext	DT_NTEXT	None	None
numeric	DT_NUMERIC	Decimal	SqlDecimal
nvarchar	DT_WSTR	String, Char[]	SqlChars, SqlString
real	DT_R4	Single	SqlSingle
rowversion		Byte[]	None
smalldatetime	DT_DBTIMESTAMP	DateTime	SqlDateTime
smallint	DT_I2	Int16	SqlInt16
smallmoney	DT_CY	Decimal	SqlMoney
sql_variant		Object	None
table		None	None
text	DT_TEXT	None	None
time	DT_DBTIME2	Timespan	TimeSpan
timestamp	DT_BYTES	None	None
tinyint	DT_UI1	Byte	SqlByte
uniqueidentifier	DT_GUID	Guid	SqlGuid
varbinary	DT_BYTES	Byte[]	SqlBytes, SqlBinary
varchar	DT_STR	None	None
xml	DT_WSTR	None	SqlXml

Sources :
Mapping SQL Server 2008 data types to SSIS
Mapping SQL Server 2008 data types to .NET / CLR

SSIS : Credential & Proxy

1) Setting up a credential -

USE [master]
GO
CREATE CREDENTIAL [SSIS_Credential] WITH IDENTITY = N'Domain\SQLServiceAgent', SECRET = N'longcomplicatedpassword'
GO

2) Setting up a proxy -

USE [msdb]
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSIS_Proxy',@credential_name=N'SSIS_Credential', 
  @enabled=1

GO

3) Assigning proxy to be available to schedule SSIS jobs -

USE [msdb]
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SSIS_Proxy', @subsystem_id=11
GO

NB : A subsystem_id of 11 is for SSIS package execution.


Database Journal : Proxy Accounts in SQL Server


Or all steps in one go -

USE [master]
GO

CREATE CREDENTIAL [SSIS_Credential] WITH IDENTITY = N'Domain\SQLServiceAgent', SECRET = N'longcomplicatedpassword'
GO

USE [msdb]
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSIS_Proxy',@credential_name=N'SSIS_Credential', 
@enabled=1
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SSIS_Proxy', @subsystem_id=11
GO

Off-topic : Social Media

I use RSS, Twitter etc to follow the SQL Server community.

Here is an interesting article on where social media is going...
Six Social Media Trends for 2010

Moving Unique Constraints & Indexes

Really good script for Moving Unique Constraints & Indexes across Partitions (subscriber only)

http://www.sqlservercentral.com/articles/Index+Management/64037/