Tuesday, November 24, 2009

Bookmark : Concatenating row values in TSQL

A thorough article examining how to concatenate row values :

Concatenating row values in TSQL

Monday, November 23, 2009

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

Saturday, November 21, 2009

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.

Tuesday, November 17, 2009

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"

Monday, November 16, 2009

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

Sunday, November 15, 2009

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

Friday, November 13, 2009

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 

Thursday, November 12, 2009

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

Monday, November 9, 2009

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

Saturday, November 7, 2009

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 ServerSSIS.NETSQL CLR (System.Data.SqlTypes namespace)
bigintDT_I8Int64SqlInt64
binaryDT_BYTESByte[]SqlBytes, SqlBinary
bitDT_BOOLBooleanSqlBoolean
charDT_STRNoneNone
cursorNoneNoneNone
dateDT_DATEDateTimeSqlDateTime
datetimeDT_DBTIMESTAMPDateTimeSqlDateTime
datetime2DT_DBTIMESTAMP2DateTimeSqlDateTime
decimalDT_DECIMALDecimalSqlDecimal
floatDT_R8DoubleSqlDouble
imageDT_IMAGENoneNone
intDT_I4Int32SqlInt32
moneyDT_CYDecimalSqlMoney
ncharDT_WSTRString, Char[]SqlChars, SqlString
ntextDT_NTEXTNoneNone
numericDT_NUMERICDecimalSqlDecimal
nvarcharDT_WSTRString, Char[]SqlChars, SqlString
realDT_R4SingleSqlSingle
rowversion
Byte[]None
smalldatetimeDT_DBTIMESTAMPDateTimeSqlDateTime
smallintDT_I2Int16SqlInt16
smallmoneyDT_CYDecimalSqlMoney
sql_variant
ObjectNone
table
NoneNone
textDT_TEXTNoneNone
timeDT_DBTIME2TimespanTimeSpan
timestampDT_BYTESNoneNone
tinyintDT_UI1ByteSqlByte
uniqueidentifierDT_GUIDGuidSqlGuid
varbinary DT_BYTESByte[]SqlBytes, SqlBinary
varcharDT_STRNoneNone
xmlDT_WSTRNoneSqlXml



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

Friday, November 6, 2009

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

Thursday, November 5, 2009

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

Wednesday, November 4, 2009