Selected Tweets of Year 2010!

Well, I guess that's it. It's time to say goodbye to 2010 and welcome a brand new year: 2011!

Like last year, in this post I am presenting a set of selected tweets I did in 2010 grouped into different categories.

Again, it was a great experience communicating with the SQL Server Community among other, via Twitter. I hope you find my tweets useful!

The categories of my tweets are:

• SQL Server Performance Tips
• Events-Related – Screencasts – Webcasts
• Blog Announcements
• Microsoft DevDays 2010 - Cyprus
• Regional CEE MVP Open Days 2010
• Miscellaneous

OK! Enough "talking!" Here are the tweets!

[SQL Server Performance Tips]
When running "heavy" T-SQL batches free the transaction log space often by using: DBCC SHRINKFILE (log_file_name, target_size) #in #MVPBuzz

How to troubleshoot #SQLServer performance issues - http://bit.ly/4Uav3u


[Events-Related – Screencasts – Webcasts]
RT @CDNUG: [Website Updated]: Review of #CDNUG event on #SQLServer 2008 Clustering / Microsoft and Openness - http://bit.ly/ha2AF1 #in #fb

Upcoming #CDNUG Event on #Microsoft and Openness / Clustering in #SQLServer 2008: http://bit.ly/9nu83E #in #fb #MVPBuzz

RT @CDNUG: Save the date: November 24, 2010. CDNUG is back with its regular series of Community events! More info soon! #in #fb #CDNUG

RT @CDNUG: [Website Updated]: Review of #CDNUG event on Windows 7 Development and #SQLServer 2008 R2 - http://bit.ly/9MJnRJ #in #fb #MVPBuzz

RT @CDNUG: [Website Updated]: Review of #CDNUG event on ASP.NET 4.0 - http://bit.ly/bSN39L #in #fb #MVPBuzz

RT @CDNUG: Review of #Microsoft DevDays 2010 in #Cyprus - #CDNUG participated with 2 of its members! - http://bit.ly/aPpjXc #in #fb #MVPBuzz

Join us to the last #CDNUG event before summer holidays! http://bit.ly/aBaCkR #INETA_EU #Cyprus #in #fb #MVPBuzz

RT @CDNUG: Upcoming #CDNUG Event: ASP.NET 4.0! - http://bit.ly/9ILxKz #in #INETA_EU


[Blog Announcements]
[Blog] Dynamically Generating T-SQL Statements! http://bit.ly/h9ddGC #in #fb #MVPBuzz #SQLServer

[Blog] Segmenting Strings in #SQLServer - http://bit.ly/eq8DPU #in #fb #MVPBuzz

[Blog] Upcoming CDNUG Event - #Microsoft and Openness / Clustering in #SQLServer 2008 - http://bit.ly/gYFKZy #fb #in #MVPBuzz #CDNUG

[Blog] Software Systems in the 21st Century: Integration is the Key - http://bit.ly/96YS6D #in #fb #MVPBuzz

[Blog] Categorization of Blog Posts - http://bit.ly/cJInGi #in #fb #MVPBuzz

[Blog] Eliminating Blank Spaces in #SQLServer Tables - http://bit.ly/bzeZX8 #MVPBuzz #in #fb

[Blog] Security Changes in #SQLServer 2008 - http://bit.ly/9A331U #in #fb #MVPBuzz

[Blog] #DevReach 2010! - http://bit.ly/a3XzqA #in #fb #INETA_EU #MVPBuzz

[Blog] Changing the Database Owner in a #SQLServer Database - http://bit.ly/aanHcO #MVPBuzz #in #fb

[Blog] #CDNUG Event Review - July 15, 2010 - #Windows7 Development / #SQLServer 2008 R2 - http://bit.ly/dD4eJY #MVPBuzz #in #fb

[Blog] Data Access and Consumption with WCF Data Services and #PowerPivot - http://bit.ly/bxTDAM #MVPBuzz #in #fb

[Blog] Product Review: SQL Search - Searching for SQL Text Within SQL Server DB Objects - http://bit.ly/dBi2zx #MVPBuzz #in #fb

[Blog] The #PowerPivot Experiment - Importing 100+ Million Records into Excel in 8 minutes! - http://bit.ly/5VrCRp #MVPBuzz #in #fb

[Blog] Policy-Based Management in SQL Server 2008 - http://bit.ly/aljD4X #SQLServer #MVPBuzz #in #fb

[Blog] The Significance of Technical Communities - http://bit.ly/clZIKp #in #MVPBuzz

[Blog] #Microsoft DevDays 2010 in Cyprus! - http://bit.ly/9rNbMc #in #MVPBuzz #DevDays

[Blog] Building Data Relationships in PowerPivot - http://bit.ly/deqgWO #PowerPivot #in

[Blog] Using PowerPivot’s Copy-Paste Support for Importing Data from Word - http://bit.ly/9nDXRn #in

[Blog] Creating Logins for Orphaned SQL Server Users - http://bit.ly/b6i0TZ #in #SQLServer

[Blog] Cleaning up Backup and Restore History Logs in MSDB - http://bit.ly/a1slKQ #SQLServer #in

[Blog] The "PowerPivot Experiment" - http://bit.ly/5VrCRp #PowerPivot #MVPBuzz #in



[Microsoft DevDays 2010 - Cyprus]
#DevDays 2010 in Cyprus was a big success! A huge thanks to @ikarld for organizing this great event for Cypriot software developers!!

See you at #Microsoft #DevDays 2010 in #Cyprus! - http://bit.ly/aGA85p #in #MVPBuzz

Session testing completed within time. All set for tomorrow!

My presentation for #Microsoft DevDays 2010 is ready! Heading for the demos tomorrow! #in #CDNUG

Preparing my session for #Microsoft DevDays - It's all about data! - http://bit.ly/9G8qhs #MVPBuzz #in

All set! Now let's talk about Data Access!

OK, I think it is time for some Greek frappe coffee and a final test of my session for tomorrow's #DevDays 2010 in Cyprus! #in

Tomorrow it will be a great day for software developers in #Cyprus! #Microsoft #DevDays 2010 will be taking place! #in

Less than a day left for MS #DevDays 2010 in #Cyprus! #in

I guess that's it! My demos for #Microsoft #DevDays 2010 are fully completed! Who knows? I'll might prepare a bonus demo as well! :-) #in

Playing around with Fiddler2 and my WCF data service, i really like this tool! :) #in

@dgkanatsios Totally agree. Fiddler is a great tool! I always use it in my demos when WCF data services (and not only) are involved :)

When testing a WCF data service in IE8 remember to uncheck "Turn on feed reading view" in Internet Options-Content-Feeds and Web Slices.

Great Visual Studio 2010 Theme Pack for Windows 7 http://bit.ly/bCEcNA

@Damir Great idea! I am sure they will like it! :-)

WCF Data Service completed! "Consuming" the service in IE8 :)

LINQ to Entities demos completed. It is time to build a WCF data service...

Demos set 1 completed. Already 1 am. Tomorrow I will complete the rest of my demo sets: LINQ to Entities and WCF Data Services!

Working on my first set of demos on Entity Framework for #DevDays 2010. Working with Visual Studio 2010 is so cool!

#Microsoft #DevDays 2010 in Cyprus! Save the date: April 15, 2010 #in


[Regional CEE MVP Open Days 2010]
RT @jacquesdp: World Bank frees statistics. http://bit.ly/9HyMav. (VM: Another good dataset to use with #PowerPivot: http://bit.ly/bUQisU)

Another great #Microsoft event completed! A huge thanks to @alead for making this possible! #in #ceemvp10 #mvpbuzz

some really long discussions are taking place! #in #ceemvp10 #mvpbuzz

It's time for Community Roundtables! #in #ceemvp10 #MVPBuzz

@ikarld delivered a great session on Windows Phone 7 #in #ceemvp10

Learning some more on Windows identity foundation #ceemvp10 #in

@alead Feeling great! Excitement is very high! Great people, great event! #ceemvp10 #in

time for dinner! #in #ceemvp10

listening to Luka talking... #CEEMVP10

Flying to Athens in 2 hours. See you there! #CEEMVP10

Packing completed! #CEEMVP10 #in

Too tired but so excited! Started packing for #CEEMVP10 !!! #MVPBuzz #in


[Miscellaneous]

My interview on MicrosoftFeed - http://bit.ly/c7ctNP #in #MVPBuzz #INETA_EU

My second year as a #SQLServer MVP started today! A huge thanks to the SQL Server Community and #Microsoft! #MVPBuzz #in

I have just passed #Microsoft Exam 70-451. I am now a MCITP: Database Developer 2008!!! #MVPBuzz #in #fb

SELECT 'ALTER DATABASE ' + name + ' SET READ_WRITE WITH ROLLBACK IMMEDIATE' FROM SYS.DATABASES | The gen. T-SQL switches the DBs to R/W mode

A simple advise: always double-check your T-SQL code which is to be used for manipulating multiple databases! #MVPBuzz

Wishing a Happy New Year 2011 full of health and of course ... SQL Server!
Read more on this article...

Dynamically Generating T-SQL Statements!

So you have many databases, objects, etc. and you want to massively administer it right?

In some of my previous posts I explained the undocumented stored procedures "sp_msforeachdb" and "sp_msforeachtable" that allow you to massively perform changes on all databases and tables within a SQL Server instance.

But isn't it handier to generate dynamic T-SQL for doing that? I believe it is!

By using some of the catalog views in SQL Server 2005 or later you can easily do that.

The idea is to use a basic SELECT statement and then dynamically build the T-SQL expression that eventually will generate the desired T-SQL code.

Here are some examples (before running the code, right-click in the query window, then select "Results To" and finally "Results to Text"):

Example 1: See the physical files used for each database

SELECT

'USE '+ name +';'+' SELECT name,physical_name FROM SYS.DATABASE_FILES' FROM SYS.databases

Example 2: Change the schema owner for all tables within a database

SELECT

'ALTER SCHEMA [NEW_SCHEMA_NAME] TRANSFER ' + s.Name + '.' + t.Name FROM sys.Tables t INNER JOIN

sys.Schemas s on t.schema_id = s.schema_id WHERE s.Name = ['OLD_SCHEMA_NAME']

Example 3: Change the schema owner for all stored procedures within a database

SELECT

'ALTER SCHEMA [NEW_SCHEMA_NAME] TRANSFER ' + s.Name + '.' + sp.Name FROM sys.Procedures sp INNER JOIN

sys.Schemas s on sp.schema_id = s.schema_id WHERE s.Name = ['OLD_SCHEMA_NAME']

Example 4: See all the views for each database

SELECT

'USE '+ s.name +';'+' SELECT * FROM sys.views' FROM SYS.databases s

Example 5: Change the compatibility level for all the databases to 100 (SQL Server 2008)

SELECT

'ALTER DATABASE '+ name + ' SET COMPATIBILITY_LEVEL = 100' FROM SYS.DATABASES

Example 6: Set all the databases to READ ONLY mode

SELECT

'ALTER DATABASE '+ name + ' SET READ_ONLY WITH ROLLBACK IMMEDIATE' FROM SYS.DATABASES

Example 7: Set all the databases to READ/WRITE mode

SELECT

'ALTER DATABASE '+ name + ' SET READ_WRITE WITH ROLLBACK IMMEDIATE' FROM SYS.DATABASES

... and the list goes on!

When using this technique along with the information retrieved from the catalog views, the possibilities are endless!

Yes, you still need to manually execute each generated T-SQL statement but the good thing is that the statements are being generated dynamically and by manually executing them, you have more control over your SQL Server instance.

I hope this helps!

Until next time!

P.S. The above sample code is intended only for demo purposes. Do not use it on Production systems. Whenever you massively modify database objects you need to be very careful with your data. Always backup your data!

Read more on this article...

Segmenting Strings in SQL Server

Lately I have been dealing a lot with SQL Server development that involves among other, segmenting strings.

So, I decided to post an example-based article that discusses some simple ways of segmenting strings with T-SQL mainly by using the SUBSTRING built-in function and some other built-in functions such as CHARINDEX, LEFT, RIGHT and LEN.

Additionally, in one of my previous posts, I described the basic String built-in functions in SQL Server.

Enough theory, let’s see some examples!

Baseline

DECLARE @expression AS VARCHAR(20)

SET @expression='Hello World'

Original Expression

SELECT @expression

Requirement 1: Get the first word only

-- Methodology 1: Using the SUBSTRING built-in function

SELECT SUBSTRING(@expression,0,CHARINDEX(' ',@expression))

-- Methodology 2: Using the LEFT and CHARINDEX built-in functions

SELECT LEFT(@expression,CHARINDEX(' ',@expression))

Requirement 2: Get the second word only

-- Methodology: Using the SUBSTRING built-in function

SELECT SUBSTRING(@expression,CHARINDEX(' ',@expression)+1,(LEN(@expression)-CHARINDEX(' ',@expression)))

Requirement 3: Get the first letters of each word

-- Methodology: This is a little bit complex requirement and SUBSTRING is not enough.
-- We also need to use the CHARINDEX, LEN and LEFT built-in functions.

SELECT LEFT(@expression,1)+LEFT(SUBSTRING(@expression,CHARINDEX(' ',@expression)+1,(LEN(@expression)-CHARINDEX(' ',@expression))),1)

This post provided simple examples on how strings can be segmented in SQL Server. I did not get into complex scenarios as if I did that I am sure that I would need many hours for finishing the article as the scenarios can vary a lot!

If you have another string segmentation scenario and/or its solution feel free to add it by commenting this post!

I hope you found this post useful!
Read more on this article...