Bookmark : MySql vs MS SQL

Another link sent to me by a Microsoft hating colleague.
Good to know what I'm up against though (Hint : COST!!!)

http://www.mssqltips.com/tip.asp?tip=1920&home

Bookmark : Brad's sure DBA checklist (updated)

Great as a sanity check...

http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/

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

Execute a SQL Agent job from TSQL

exec msdb.dbo.sp_start_job 'jobname'

(does what it says on the tin!)

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

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)

Bookmark : Free Tools for the SQL DBA

Excellent Link on SSC today, 'Free Tools for the SQL Server DBA'
http://www.sqlservercentral.com/articles/Tools/64908/

As a result I'm now using 'SQL Heartbeat'...
http://sqlsolutions.com/products/Heartbeat/index.html

...and am trying out 'Microsoft XML Notepad'
http://www.microsoft.com/downloads/details.aspx?familyid=72d6aa49-787d-4118-ba5f-4f30fe913628&displaylang=en



update Nov 2010 :
Both now added to my 'Free SQL Server Stuff' page.

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.

SELECT 
[ContactID]
, [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.

SSIS : Passing variables to sql

Creating Sql strings to pass variables through to Sql Server >

T-SQL
"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

or

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!

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

Free Training on SQL Server DMVs

On Wednesday 3rd March , Courtesy of Quest ... http://www.vconferenceonline.com/shows/spring10/quest/register/multireg.asp

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


http://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/

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 -

• Publish further scripts on other sites (September 2009 : had 1st published on SSC)
• Publish an article
• Attend more SQL Server events
• Network more

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.