Saturday, 30 January 2010

Friday, 29 January 2010

Bookmark : Brad's sure DBA checklist (updated)

Great as a sanity check...

SQL 2008 : Management Studio Error : Saving Changes in not permitted.

The error >

Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

How to resolve >

Link : SQLServerPedia

Monday, 25 January 2010

Bookmark : Top 10 Features of SQL 2008 R2

Don Schlichting sumarises his 'Top 10 Features of SQL 2008 R2' and explains briefly what they are.

1. Report Builder 3.0

2. SQL Server 2008 R2 Datacenter

3. SQL Server 2008 R2 Parallel Data Warehouse

4. StreamInsight

5. Master Data Services

6. PowerPivot for SharePoint

7. Data-Tier Application

8. Unicode Compression

9. SQL Server Utility

10. Multi Server Dashboards

Saturday, 23 January 2010

SSIS Data Flow : Conditional Split examples

Examples and syntax of how to use the Conditional Split Transform in a data flow >

Example 1 :

Formatting a condition goes like this>

[Action] == "New Configuration"
[Action] == "Deleted Configuration"
[Action] == "Changed Configuration"

(Action is a column name in this data flow)

Example 2:

Condition in use here is LTRIM(City) == "" || LTRIM(State) == ""
(where || means OR)

Monday, 18 January 2010

TSQL : Using NTILE to divide a results set up.

NTILE is a ranking function which can be used to divide up results into groups of equal volume.

To demonstrate I use NTILE (4) to distribute the Adventureworks Contact table into 4 groups.
, [NameStyle]
, [Title]
, [FirstName]
, [MiddleName]
, [LastName]
, NTILE (4)  OVER ( ORDER BY [ContactID] )  As Process
FROM [AdventureWorks].[Person].[Contact]

In reality I'm using it to divide a job queue between different paths in a SSIS package, to achieve a level of parallel execution.
I'm sure there are better ways, which I'll blog when I find them.

Sunday, 17 January 2010

SSIS : Passing variables to sql

Creating Sql strings to pass variables through to Sql Server >

"SET FMTONLY OFF; SELECT TOP "+ (DT_STR, 8,1252) @[User::RowBatchSize] +" * FROM [dbo].[sourceData] WHERE loadId > " + (DT_STR, 8,1252) @[User::LastProcessedID]

Executing a Stored Procedure
"SET FMTONLY OFF; EXEC dbo.dataLoad @rows = "+ (DT_STR, 8,1252) @[User::RowBatchSize] +",@loadId = "+ (DT_STR, 8,1252) @[User::LastProcessedID]

To use this method >
1) Create a variable
2) Set the variable property 'EvaluateAsExpression' to 'True'
3) Set the expression with your sql statement (see examples above)

You can use this in either the Control flow or Data Flow.

In a Control Flow >

i. Add an Execute SQL Task
ii. Set the 'SQL Source Type' to 'Variable'
iii. Set the SourceVariable to the variable name


In a Data Flow >

i. Add a OLE DB Source and open the OLE DB Source Editor.
ii. Set 'Data Access Mode' to 'SQL Command from Variable'.
iii. Set the 'Variable Name' to ooh.... the name of the variable containing the expression!

Friday, 15 January 2010

Enabling Data Compression in Scripts generated from Management Studio 2008

1) On the Management Studio menu bar, navigate to 'Tools > Options'

The following menu then appears >

2) Expand 'SQL Server Object Explorer' , click 'Scripting'
3) Scroll down to 'Object Scripting Options' and change 'Script Data Compression Options' to TRUE

Useful link :

SQL 2008 Management Studio - Scripting Improvements

Saturday, 9 January 2010

SSIS Control Flow : Precedence Constraints

Example & syntax of how to use utilise Precedence Constraints in the control flow >

Checking a condition goes like this >

@[User::ProcessChanges] == 1

(ProcessChanges is a user defined variable in the above package).

Saturday, 2 January 2010

Goals for 2010

Improve my web presence -
  • Tidy sqlsolace, improve tagging and searchability
  • Review (& potentially post) my backlog of scripts and notes
  • Review and remove old/unprofessional material from my personal web site
Community -
Learning –
  • Continue with SSIS development to further my ETL skills.
  • Obtain Microsoft BI certifications
  • Tackle the mountain of books, whitepapers and printed articles in my office.