The SQL Server and .NET Blog

Selected Tweets of Year 2011!

2011-12-31T14:57:00.000+02:00

With just a few hours remaining for entering the new year 2012, I could do nothing else than preparing this blog post! In the end of every year, I gather up all my tweets for that year and post it here!

It is a privilege to communicate with the SQL Server Community via all possible channels like blogs, forums, offline and online events, conferences and of course, Twitter!

You can find 2011's tweets below:

Blog Posts/Articles and Announcements
[Blog] Dynamic T-SQL Generator v1.0 is now on CodePlex! - http://bit.ly/rqG5F2 #in #fb #MVPBuzz #SQLServer #DynamicSQLGen

[Blog] Useful SQL Server Knowledge - Set 1 - http://bit.ly/sa3qqv #in #fb #MVPBuzz #SQLServer

[Blog] My #SQLServer utility Snippets Generator goes open source on CodePlex! - http://bit.ly/vtDMC6 #MVPBuzz #in #fb

[Blog] Webcast on #SQLServer Denali RC0 - Poll - http://bit.ly/uzwbHR #in #fb #MVPBuzz

[Blog] Creating an instance of the COM component with CLSID ... http://bit.ly/uACL8X #sqlserver #fb #in #MVPBuzz

[Blog] Retrieving Only the Date from a Datetime Value in #SQLServer - http://bit.ly/piFTjf #in #fb #MVPBuzz

[Blog] Sequence Objects in #SQLServer - http://bit.ly/qRckwS #in #fb #MVPBuzz

[Blog] Using Proxy Accounts in #SQLServer Agent Jobs - http://bit.ly/vX8dkh #in #fb #MVPBuzz

[Blog] Updating #SQLServer Tables Without Causing Blocking - http://bit.ly/umv3ZQ #in #fb #MVPBuzz

[Blog] Backing up a Database in a Network Folder - http://bit.ly/oiWuWJ #MVPBuzz #in #fb #SQLServer

[Blog] Tool that generates dynamic T-SQL! - http://bit.ly/l7jxkm #in #fb #MVPBuzz

[Blog] Executing Heavy Set-Based Operations Against VLDBs in #SQLServer - http://bit.ly/iFHKMR #in #fb #MVPBuzz

[Blog] Using Unicode in #SQLServer - http://bit.ly/lgoLRj #MVPBuzz #in #fb

[Blog] Microsoft MVP for the third year in a row! - http://bit.ly/gmUb0U #in #fb #MVPBuzz #SQLServer

[Blog] Using the NOLOCK Table Hint - http://bit.ly/hqqQ9F #fb #in #MVPBuzz #SQLServer

[Blog] Database [Database_Name] cannot be upgraded because it is read-only or has read-only files - http://bit.ly/ftJhw8 #in #MVPBuzz #fb

[Blog] No global profile is configured. Specify a profile name in the @profile_name parameter - http://bit.ly/hdFcHA #MVPBuzz #in #fb

[Blog] Free Utility for SQL Server 2011 CTP1 - Snippets Generator v1.0 - http://bit.ly/hnxImn #in #fb #MVPBuzz #SQLServer

[Blog] Webcast: Introducing SQL Server "Denali" (CTP1) - http://bit.ly/hN9srP #fb #in #MVPBuzz #INETA_EU #CDNUG

Miscellaneous
Generating T-SQL snippets for SQL Denali with ease: Snippets Generator Project - http://bit.ly/rKZFYS #MVPBuzz #SQLSnippetsGen #SQLServer

Preparing my session on #SQLServer Denali CTP 3! - The exciting part is yet to come: The demos!!! #MVPBuzz

On a domain pc/server, in a cmd prompt you can type "echo %logonserver%" to find out which domain controller authenticates you.

I am celebrating 100.000 (+) visits on my blog and I am giving away a utility I developed! The tool is: Dynamic T-SQL Generator! #MVPBuzz

Today my blog has reached 100.000 visits! Thank you for your support!!! - http://bit.ly/PKBi9 #in #fb #MVPBuzz

I am a #Microsoft #SQLServer MVP for a third year in a row! A huge thanks to you, the Community, for supporting my efforts! #in #fb #MVPBuzz

RT @CDNUG: The #Microsoft Cloud Power Event in Cyprus! - http://bit.ly/emiPUh - Save the date: March 10, 2011 #MVPBuzz #fb

[Personal Website Updated] New section added: Webcasts & Screencasts - http://bit.ly/hXPnMZ #in #MVPBuzz

Just finished recording my Webcast on #SQLServer Denali! I will upload it tomorrow! Make sure you check out my blog at: http://bit.ly/PKBi9

A really cool feature! RT @mssqlserver: SEQUENCE in SQL Server 2011 http://bit.ly/gMfC68 #SQLServer

Preparing a webcast for introducing #SQLServer "Denali"! More info soon! #in #fb #MVPBuzz

I really like the T-SQL code snippets enhancement in #SQLServer Denali! #MVPBuzz #in

AdventureWorks Sample Database for #SQLServer "Denali" CTP 1 - http://bit.ly/9hBGub #in #fb

Wishing a Happy New Year 2012 full of health and of course ... SQL Server!

Dynamic T-SQL Generator v1.0 is now on CodePlex!

2011-12-21T18:05:00.000+02:00

Earlier this year, in one of my blog posts, I presented Dynamic T-SQL Generator: a tool I developed that generates dynamic T-SQL based on a given static T-SQL script and vice versa.

Dynamic T-SQL can be used in many cases when you develop SQL Server routines. An example is embedding T-SQL scripts in SQL Server functions and stored procedures (i.e. sp_send_dbmail).

As i believe that the Technical Community might find the tool useful, I decided to create a relevant open source project on CodePlex.

Feel free to visit my project's page on CodePlex and provide your valuable feedback!

Here's a screenshot of the tool:

Here's the link to Dynamic T-SQL Generator.

Useful SQL Server Knowledge - Set 1

2011-12-14T16:57:00.002+02:00

A few days ago I started writing an article that it would provide a set of useful tips on SQL Server topics that are met in the everyday life of DBA/Database Developer.

The article is now completed and you can find it below! It is organized in the following categories:

Solutions to common issues
Basic string functions
Performance-related tips
Maintenance
Miscellaneous

--
-- A: Solutions to common issues
--

--1: Resolving the "Divide by zero" error (by example)
DECLARE @denominator INT
SET @denominator = 0
SELECT 1 / ISNULL(NULLIF(@denominator, 0), 1)

--2: Handling NULL and empty values
----Step 1: Create the IsEmpty user-defined function
CREATE FUNCTION IsEmpty
(
@input AS VARCHAR(250),
@newValue VARCHAR(250)
)
RETURNS VARCHAR(250)
AS
BEGIN
-- First handle the case where the input value is a NULL
DECLARE @inputFiltered AS VARCHAR(250)
SET @inputFiltered = ISNULL(@input, '')

-- The main logic goes here
RETURN (CASE RTRIM(LTRIM(@inputFiltered)) WHEN '' THEN RTRIM(LTRIM(@newValue)) ELSE RTRIM(LTRIM(@inputFiltered)) END)

END
GO
----Step 2: Usage
SELECT dbo.IsEmpty(@column_to_check, @new_value);

--3: Handling the error "A transport-level error has occurred when sending the request to the server"
If the problem occurs in a SSMS Query Window, just open a new one and run the T-SQL statements again. In a different case, re-initiate the request to the SQL Server instance.

--4: Handling the error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"
Change the default language to "us_english" for the given SQL Server login:

USE [master];
ALTER LOGIN "LOGIN_NAME" WITH DEFAULT_LANGUAGE = us_english;

Best practice: Always use the ISO date format in your data applications/T-SQL scripts: YYYY-MM-DD

--5: Handling the error "The multi-part identifier ... could not be bound"
Be careful with the use of subqueries and table aliases. Don't forget to reference the correct table aliases in your T-SQL code.

Also, keep in mind that subqueries can only provide their results to their outer queries and not references to the subqueries' tables.

--6: Handling the error "String or binary data would be truncated"
Either use an adequate size for the table columns in which the data is inserted or cast the data by removing redundant characters. I suggest the first approach.

--7: Handling the error "Error converting data type varchar to float"
----Step 1: Create the Varchar2Float user-defined function
CREATE FUNCTION [dbo].[Varchar2Float]
( @inputString VARCHAR(50) )
RETURNS FLOAT
AS
BEGIN
--Prepare the string for casting/conversion
SET @inputString = REPLACE(@inputString, '.', '')
SET @inputString = REPLACE(@inputString, ',', '.')

--Perform the conversion and return the result
RETURN CAST(@inputString AS FLOAT)
END;
----Step 2: Usage
SELECT dbo.Varchar2Float(@value)

--8: Handling the error "Database [Database_Name] cannot be upgraded because it is read-only or has read-only files"
Make sure that the user account on which the SQL Server instance database engine is running has full access to the database files.

--
-- B: Basic string functions
--

--1: Returns @length characters from @expression starting from @start_index
SELECT SUBSTRING(@expression, @start_index, @length)

--2: Finds the given @pattern in the @string and replaces it with the @replacement_string
SELECT REPLACE(@string, @pattern, @replacement_string)

--3: Returns the size of @string in terms of number of characters
SELECT LEN(@string)

--4: Returns the first @num_chars characters of the @string counting from the left
SELECT LEFT(@string, @num_chars)

--5: Returns the first @num_chars characters of the @string counting from the right
SELECT RIGHT(@string, @num_chars)

--6: Removes the leading blank spaces
SELECT LTRIM(@expression)

--7: Removes the trailing blank spaces
SELECT RTRIM(@expression)

--
-- C: Performance-related tips
--

--1: Avoiding locking when reading data (however, dirty reads are allowed)
SELECT [columnName]
FROM [tableName] WITH (NOLOCK)

--2: Rebuilding indexes in SQL Server 2005 or later
----Rebuild a specific index with using parameters
USE [DATABASE_NAME];
ALTER INDEX [INDEX_NAME] ON [SCHEMA.TABLE]
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_VALUE_BETWEEN_0_100], ONLINE=[ON|OFF]);

----Rebuild all indexes in a table with using parameters
USE [DATABASE_NAME];
ALTER INDEX ALL ON [SCHEMA.TABLE]
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_VALUE_BETWEEN_0_100], ONLINE=[ON|OFF]);

--3: Rebuilding all the indexes in a database
----Rebuild all indexes online with keeping the default fill factor for each index
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON)";

----Rebuild all indexes offline with keeping the default fill factor for each index
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)";

----Rebuild all indexes online with specifying the fill factor
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=ON)";

----Rebuild all indexes offline with specifying the fill factor
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=OFF)";

--4: Updating database tables without causing blocking
UPDATE [TABLE_NAME] WITH (READPAST)
SET ...
WHERE ...

--
-- D: Maintenance
--

--1: Shrinking an entire database
DBCC SHRINKDATABASE([DBName],[PercentageOfFreeSpace]);

--2: Truncating an entire database
DBCC SHRINKDATABASE([DBName],TRUNCATEONLY);

--3: Shrinking a data/log file
USE [DBName];
DBCC SHRINKFILE ([Data_Log_LogicalName],[TargetMBSize]);

--4: Truncating a data/log file
USE [DBName];
DBCC SHRINKFILE ([Data_Log_LogicalName],TRUNCATEONLY);

--5: Renaming a Windows login
ALTER LOGIN "[Domain or Server Name]\[Windows Username]"
WITH NAME="[New Domain or New Server Name]\[Windows Username]";

--6: Renaming a SQL Server login
ALTER LOGIN "[SQL Server Login Name]"
WITH NAME="[New SQL Server Login Name]";

--7: Creating Logins for orphaned SQL Server users
USE [DBName];
EXEC sp_change_users_login 'Auto_Fix', '[UserName]', NULL, '[Password]';

--8: Changing the Database Owner in a SQL Server Database (SQL Login)
USE [DBName];
EXEC sp_changedbowner '[SQL_Login_Name]';

--9: Changing the Database Owner in a SQL Server Database (Windows Login)
USE [DBName];
EXEC sp_changedbowner '[DomainName\UserName]';

--10: Backing up a Database in a Network Folder (creating the destination media)
USE [master];
EXEC sp_addumpdevice 'disk', 'NetworkDeviceName','\\serverName\backupFolder\BackupFileName.bak';
--Now you can backup databases onto the network device created above

--
-- E: Miscellaneous
--

--1: Gets basic information on the current SQL Server instance
SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY ('ProductLevel') AS ProductLevel,
SERVERPROPERTY ('Edition') AS Edition,
SERVERPROPERTY('MachineName') AS ServerName,
SERVERPROPERTY('ServerName') AS Server_and_Instance_Names

--2: Gets basic table index information
EXEC sp_helpindex 'schema.table_name'

--3: Connecting to Windows Internal Database (SSEE)
From within SSMS (Express Edition works as well):

Server Type: Database Engine
Server Name: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Authentication: Windows Authentication

* Make sure that the Named Pipes protocol is enabled!

I hope you found this article useful!

Cheers!

Snippets Generator is now on CodePlex!

2011-12-12T19:49:00.000+02:00

Early this year, in one of my blog posts, I presented Snippets Generator: a tool I developed that generates snippets for SQL Server Denali.

Snippets can become quite handy when you want to write code fast, without having to repeat/type certain sets of T-SQL statements that are often used.

As the purpose of this blog and generally the purpose of my entire Community activity is to exchange knowledge with fellow Community members and support the technical Community, I decided to take it one step further and publish Snippets Generator as an open source project on CodePlex. CodePlex is Microsoft's open source project hosting web site and there you can find thousands of great open source projects/applications that you can freely use.

Feel free to visit my project's page on CodePlex and provide your valuable feedback!

Here's the link to Snippets Generator.

Webcast on SQL Server Denali RC0 - Poll

2011-12-06T21:42:00.001+02:00

With SQL Server Denali RC0 released a few weeks ago, I am thinking of recording a webcast with topic its Database Engine features mainly focusing on the programmability area.

To this end, I am conducting a poll in order to see if you are interested in attending such a webcast.

If you are interested you just need to complete the following poll!

Loading...

When the webcast is available, you will receive an email notification!

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed...

2011-11-18T16:57:00.003+02:00

If you ever encounter issues when trying to create, add, or edit steps for a SQL Server Agent job in SQL Server Management Studio 2008 R2 don't worry!

The error popup you might get is the following:

There is a fix for this issue. You can find it here.

The fix applies to the following products:

Microsoft SQL Server 2008 R2 Developer
Microsoft SQL Server 2008 R2 Enterprise
Microsoft SQL Server 2008 R2 Workgroup
Microsoft SQL Server 2008 R2 Datacenter
Microsoft SQL Server 2008 R2 Standard

Retrieving Only the Date from a Datetime Value in SQL Server

2011-10-10T20:42:00.003+03:00

There are many times where you might need to only retrieve the date from a datetime value.

For example, you may have today's datetime (2011-10-10 20:30:28.230) and you want to only get the following value: 2011-10-10.

In SQL Server 2008 or later, you can easily do this by casting/converting the datetime value to the datatype DATE.

A typical example is the following:

--Datetime variable declaration

DECLARE @dateTimeValue as datetime

SET @dateTimeValue=GETDATE()

--Cast the datetime value to the DATE datatype

SELECT CAST(@dateTimeValue as DATE) as OnlyDate

However, in earlier versions of SQL Server the DATE type is not available.
So, if you have a SQL Server 2005 instance or earlier and you want to get the date value from a datetime value you can just create and use a simple scalar-valued function like the following:

---------------------------------

-- Create scalar-valued function -

---------------------------------

CREATE FUNCTION dateOnly

(

@dateInput datetime

)

RETURNS varchar (10)

BEGIN

declare @tempRes as varchar(10)

declare @tempYear as varchar(4)

declare @tempMonth as varchar(2)

declare @tempDay as varchar(2)

set @tempYear=(select cast ((datepart(yyyy,@dateInput)) as varchar(4)))

set @tempMonth=(select cast ((datepart(MM,@dateInput)) as varchar(4)))

set @tempDay=(select cast ((datepart(dd,@dateInput)) as varchar(4)))

set @tempRes=@tempYear+'-'+@tempMonth+'-'+@tempDay

RETURN @tempRes

END

-----------------------------

Now that the function is created, the first example changes to:

--Datetime variable declaration

DECLARE @dateTimeValue as datetime

SET @dateTimeValue=GETDATE()

--Get only the date value by calling the scalar-valued function

SELECT dbo.dateOnly(@dateTimeValue) as OnlyDate

.... and that's it! :)

I hope you found the article useful!

Until next time!

Sequence Objects in SQL Server

2011-09-30T23:08:00.002+03:00

SQL Server "Denali" among other introduces Sequence Objects. I am sure that sequence objects are widely known but let's provide their definition in plain words:

A sequence object allows you to set a global counter of values within the scope of a DBMS instance.
Whenever you call the sequence object it returns a unique value based on its configuration (i.e. the numbers 1,2,3,4, etc.).
Even though you can use this feature in SQL Server "Denali", what about if you need to use it in an earlier version of SQL Server?

It is a fact that sequence objects are not available in earlier versions of SQL Server but guess what; there are many workarounds that can be used instead! :)

Further below I'm describing one such workaround. The main idea is the following:

1. Create a table with two columns:

An identity column of the type bigint.
A value column of the type bigint.

2. Create a stored procedure that will be doing the following upon its execution:

Insert a value in the respective value column (this will be causing the identity column to be automatically increased).
Delete the previously inserted record and always keep the last one.

3. Whenever you need a unique value, you simply call the stored procedure and then you retrieve the identity column's value from the respective table.

4. That's it! You are now using a sequence-like object!

Here's the T-SQL for the above logic:

--Select the proper database
USE [DB_Name]
GO

--Step 1: Create the table for storing the data (once-off)
CREATE TABLE [tblSeq](
    [SeqID] [int] IDENTITY(1,1) NOT NULL,
    [SeqVal] [bigint] NULL
)
GO

--Step 2: Create the stored procedure for manipulating the data (once-off)
CREATE PROCEDURE [GetNewSeqVal_tblSeq]
AS
BEGIN
      insert into tblSeq (SeqVal) values (1)
    if ((select max(seqid) from tblSeq))!=1
        delete from tblSeq where seqid < (select max(seqid) from tblSeq)
END
GO

--
-- Whenever a new, unique number is required, you just run the following two T-SQL statements:
--

-- Step 1: Execute the stored procedure for increasing the identity by 1
EXEC [GetNewSeqVal_tblSeq]
GO

-- Step 2: Get the new, unique value
SELECT MAX(seqid) FROM [tblSeq]
GO

I hope you found the article useful!

Using Proxy Accounts in SQL Server Agent Jobs

2011-08-30T22:20:00.000+03:00

When using SQL Server, in many cases you will might need to set up a SQL Server Agent job that will be accessing a resource within the domain.

For example, you want to include a step in a SQL Server Agent job that based on some logic, will be handling a Windows service on a server within the domain by using the Operating System (CmdExec) SQL Server subsystem.

In order for the job to be successfully executed, the specific job's execution context should be allowed access to the target resource in the domain.

If you are using a domain user as a service account for the SQL Server Agent in the specific instance, you can assign the necessary access rights to that user account.

However, there is also another way which I personally prefer; using a Proxy Account for executing the specific job step :)

In order to be able to do this you must perform the following actions within the instance of SQL Server:

Create a credential
Create a Proxy Account that uses the credential you created in the first step

For creating a credential, in SSMS you navigate to: Security -- Credentials
You can then create the credential by providing an identity (i.e. a domain user) along with its password.

For creating a Proxy Account, in SSMS you navigate to: SQL Server Agent -- Proxies

You can then create a new Proxy Account by giving it a name and performing the following:
- Provide the credential you earlier created
- Enter a description (optional)
- Set the subsystems for which the Proxy Account will be active. These are:
------- ActiveX Script
------- Operating system (CmdExec)
------- Replication Distributor
------- Replication Merge
------- Replication Queue Reader
------- Replication Snapshot
------- Replication Transaction-Log Reader
------- SQL Server Analysis Services Command
------- SQL Server Analysis Services Query
------- SQL Server Integration Services Package

That's it!

You can now proceed and set up the SQL Server Agent job along with its steps, and in the step you want to use the Proxy Account you select it in the "Run as" drop down box.

Whenever the specific job step runs, it will be executed in the context of the provided Proxy Account.

* Note that in order to be able to use a Proxy Account in a specific job step, the Proxy Account needs to be activated for the specific subsystem (i.e. Operating system - CmdExec).

I hope you found this post useful!

Updating SQL Server Tables Without Causing Blocking

2011-08-27T10:19:00.000+03:00

Even though the SQL Server Database Engine automatically sets the best possible locking hints on the underlying database objects of the various T-SQL operations that executes, there are cases
where we need to manually control locking as a part of the business logic in our T-SQL script.

A popular locking hint is the NOLOCK as it is being used many times in environments with high concurrency. By using the NOLOCK hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. Of course, this means that the query may see inconsistent data (dirty reads), that is data not yet committed, etc. The NOLOCK hint can only be used in SELECT statements.

Now imagine the following scenario: You need to design a special UPDATE statement that will be updating an unknown number of records in a table which is being concurrently accessed by several other T-SQL statements (mostly UPDATE statements). The above statement will be updating the table with non-critical information meaning that if it skips some records the first time, it can update them the second time and so on.

If even one of the other UPDATE statements has locked a row that needs to be modified by your UPDATE statement, this will cause the latter to wait (blocking). However, in the case where waiting is not a very "desired" option what should you do? That leads us to the following question: What table hint can be used?

Answer: The READPAST locking hint :)

The READPAST locking hint when used, instructs the SQL Server Database Engine to skip row-level locks.
This means that the UPDATE statement using READPAST will only update the table rows that are not locked by another operation. In the opposite case, the Database Engine would block the UPDATE statement's execution until the rest of the target rows' locks are released. A typical UPDATE statement with the READPAST locking hint would look like this:

UPDATE [TABLE_NAME] WITH (READPAST)
SET ...
WHERE ...

Even if the above locking hint can become quite handy, as well as the rest of the locking hints, you always need to have in mind that you should use them very carefully as you might cause locking issues in the database. SQL Server Query Optimizer typically selects the best execution plan for a query, so it is not recommended for inexperienced developers and administrators to make use of the locking hints.

As a last note, the READPAST hint can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels.

Backing up a Database in a Network Folder

2011-08-12T21:55:00.003+03:00

Hi! I hope you are all doing well and having a great summer time!

It's been a while since the last time I have posted an article but hey, besides the heavy workload, it's summer time and the beaches in Cyprus are really great! :)

This post discusses about a quite simple task in SQL Server: backing up a database in a network folder / remote server.

To cut the long story short, imagine the following scenario:

You have a SQL Server Instance located on a server with only one local drive (!) and you urgently need to backup a database somewhere! Well, the first thing that comes on my mind, is to backup the database on a remote location (yep, you do not have physical access to the server and cannot mount a USB flash drive :)

In order to do this you first need to mount the remote location (network folder) as a backup device.

To this end, let's say you have the network folder \\serverName\backupFolder and you want to mount it as a backup device. For doing that, you have to run the following stored procedure:

USE master

EXEC sp_addumpdevice 'disk','NetWorkDeviceName','\\serverName\backupFolder\BackupFileName.bak'

Then from SSMS you can select to backup the database on the mounted backup device and that's it!

However, there is one consideration; the service account which runs the SQL Server Instance needs to have read/write permissions on the network folder (shared permissions).

In the opposite case you will receive the following error message:

I hope you found this post useful!

Until next time!

Tool that generates Dynamic T-SQL!

2011-06-01T17:20:00.019+03:00

A few days ago, my blog reached 100.000 visits. Given this opportunity I decided to try to do something special, for example write a pretty cool article or even better, build a SQL-Related tool and give it as a freeware via my blog!

Before talking about the tool, first of all I would like to say a huge THANK YOU for your support. The 100.000 visits did not take place by machines (oh well, some visits might came from bots :)) but real persons. Persons that are passionate about technology, persons just like me.

I have always believed in technical communities and the exchange of knowledge within them. To share technical knowledge is a different way of communication; a communication based on the same passion for technology. You want to know something; you just ask about it. You want to share something; you just answer a question or write about it. You want to discuss something; you leave a comment. The communication within a technical community is quite simple. I think that I have just described its main principles :) I believe that this is the reason that there are so many technical communities worldwide as well as so many distinguished community leaders.

So, I would just like thank you again for your support and jump immediately to the description of the tool!

First, here’s a screenshot of the tool:

As you can see the tool’s name is “Dynamic T-SQL Generator”.

The rationale behind this tool is quite simple. There is many times where you might need to embed a T-SQL statement for dynamic execution. For example you may have the following query:

SELECT * FROM tCustomer WHERE NAME=’JOHN’

Then let's say you want to include the above query to a Database Mail send statement:

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'email_address@goes.here',
@importance='Normal',
@body='Email body goes here.',
@subject ='Email subject goes here',
@profile_name ='Profile_name',
@query ='SELECT * FROM tCustomer WHERE NAME=''JOHN''',
@attach_query_result_as_file = 1,
@query_attachment_filename ='Query_Results.txt'

As you can see in the above example, the query needed to be enclosed in single quotation marks as well as the name value "JOHN" in the WHERE clause needed two additional single quotation marks. Now, if your query is a very simple one, it is not a hassle to manually add the single quotation marks. However, most of the times this is not the case :) You usually have a really large query that you may need to embed it as a parameter to a stored procedure, etc.

To this end, wouldn’t it be cool to have a tool that transforms a static T-SQL statement into a dynamic one and vice versa? Guess what; this is what exactly “Dynamic T-SQL Generator” does!

Analytically the functionality of the tool is the following:

Given a static T-SQL statement it generates the corresponding dynamic T-SQL statement.
Given a dynamic T-SQL statement it generates the corresponding static T-SQL statement.
Ability to restore the original T-SQL statement.
Ability to convert the T-SQL statement to uppercase.
Ability to convert the T-SQL statement to lowercase.
Ability to copy the T-SQL statement to the clipboard.
Ability to paste the contents of the clipboard into the text box.

Prerequisites
The only prerequisite for executing the tool is having installed Microsoft .NET Framework 3.5 SP1.

Downloading the Tool
If you like to get the tool, you can download it from here!

I hope you find it useful!

By the way, drop me a line if you liked the tool!

Cheers!

Executing Heavy Set-Based Operations Against VLDBs in SQL Server

2011-05-20T18:31:00.001+03:00

Recently I had to design and execute some heavy set-based T-SQL operations against a large number of very large databases (VLDBs) within a SQL Server 2005 instance.

I won't enter the debate of Row-Based vs. Set-Based processing right now, as I plan to write an article on this in the near future, but I will rather discuss some concerns that need to be taken into account when performing such operations.

First of all, in terms of hardware resources you will need enough RAM and of course, more than one processor (remember to check the Processor affinity setting in your SQL Server instance).

Now, the most important aspect when executing such operations (set-based) against large databases is the log space.

As the execution of the operation continues, a significant amount of transaction log space is being reserved thus requiring more storage. The reason this is happening is that the operation is not committed until it completes its execution against the entire data set. So, if your target database's recovery model is set to "FULL" it means that any changes performed by the set-based operation will be logged up to the latest detail thus requiring more storage.

So, how can you ensure that the set-based operation will be completed without any problems such as full disk spaces, etc. and thus avoid the possibility of having the operation abnormally terminated?

Well, there are two approaches (if not more); the easy one and the complex one.

The complex approach is to break-up the set-based operation thus targeting smaller data sets within the database. Not so good right? I don't like it either! In my opinion, this should be the very last option as “segmenting” the target data set is not a trivial task, mainly in terms of data consistency.

And here’s the simple approach:

1. Find a date and time where there are not operations targeting the database (this is actually a downtime :)

2. Set the database's recovery model to SIMPLE as in the following example:

ALTER DATABASE [Database_Name] SET RECOVERY SIMPLE
GO

3. Execute the set-based operation.

4. Set the database's recovery model to FULL as in the following example:

ALTER DATABASE [Database_Name] SET RECOVERY FULL
GO

When your database uses the Full Recovery Model, SQL Server preserves the transaction log until you back it up. By doing this, it allows you to recover the database to a specific point of time based on the entries in the transaction log. That’s why when this recovery model is selected SQL Server logs everything in the database’s log file. Of course this requires more disk space and slightly affects performance but it enables you to fully recover your database in the case of a disaster.

When you use the Simple Recovery Model, SQL Server keeps only a minimal amount of information in the log file. Also the file is automatically truncated by SQL Server whenever the database reaches a transaction checkpoint. Even though this is not the best choice for disaster recovery purposes, it allows the processing on the database to be faster.

So, as I had to execute some really heavy set-based T-SQL operations I changed the recovery model to Simple in order for the processing to be faster and to ensure (up to the possible limit) that I wouldn’t experience any disk space issues due to the size increase of the transaction log files.

Hope this helps!

Using Unicode in SQL Server

2011-05-19T15:27:00.002+03:00

Unicode is the standard used in the computing industry for encoding and representing any text in the most written languages (…).

SQL Server supports Unicode, thus allowing the easy storage and manipulation of data in the most languages.

As I wanted to test this functionality I performed a simple experiment with the Cyrillic alphabet.

To this end I created two tables in SQL Server 2005, the one in non-Unicode and the other one in Unicode:

CREATE TABLE tStandard(
[name] varchar(100)
)
GO

CREATE TABLE tUnicode(
[name] nvarchar(100)
)
GO

As you can see, in the tStandard table the “name” field’s data type is varchar and in the tUnicode table the “name” field’s data type is nvarchar.

The difference between the varchar and nvarchar data types is that the former uses 1 byte for representing characters where the later uses 2 bytes thus supporting Unicode.

Then I created a text file in Unicode containing three records with my name in Russian:

The next step was to import the data into the two tables using the SQL Server Import and Export Wizard. As you can see from the screenshot below, when I tried to import the data into the tStandard table the process failed because of the fact that the data did not match the target code page (GREEK_CI_AS):

But what about if we want to represent any language without relying on the target database’s code page/collation?

The answer is easy; Use nvarchar(s)!

Let’s see what happened with the rest of my experiment.

Here’s the result of trying to import the data into the tUnicode table:

Yep, the data was imported successfully!
And here’s the SELECT * query’s results when executed against the two tables:

Conclusion
The conclusion out of this experiment is that by using nvarchar, SQL Server can easily support the storage and representation of data in most written languages.

The only thing you need to have in mind, is that when using Unicode data types such as the nvarchar, the required storage will be doubled in comparison with using non-Unicode data types (i.e. varchar).

Microsoft MVP for the third year in a row!

2011-04-01T23:21:00.003+03:00

Today is a very special day! I just got re-awarded with the Microsoft MVP Award on SQL Server for the third year in a row!

------------------------------------------

Dear Artemakis Artemiou,

Congratulations! We are pleased to present you with the 2011 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in SQL Server technical communities during the past year.

------------------------------------------

I feel really honored and happy by receiving once again the MVP Award. The strongest motivation for contributing within the SQL Server Community is the Community itself. It is the willingness to share knowledge with fellow Community members, the joy of belonging to a broader technical group of people who share the same passion for technology and above all, the love and enthusiasm for SQL Server!

I would like to say a huge thank you to the SQL Server Community for being just awesome! It is a privilege to be a part of this Community.

Also, many thanks to Microsoft for strongly and continuously supporting the technical communities around the globe!

I would also like to thank the SQL Server Product Group for developing such great technologies and everyone involved in the MVP Award Program.

On my end, as I did in the last two years, I promise that I will keep on doing my best regarding my involvement in the SQL Server community and keep on sharing knowledge!!!

Using the NOLOCK Table Hint

2011-03-21T21:23:00.003+02:00

Each time you write and execute a T-SQL Query in SQL Server, the Database Engine selects the most optimized query plan for executing the query.

The SQL Server Database Engine component that undertakes this task is the Query Optimizer. Given the T-SQL query itself as well as the underlying database(s) design, the Query Optimizer selects the best execution plan for the query and then it executes the query.

Now, in SQL Server you can make use of several query hints, such as locking hints. Though, as SQL Server Query Optimizer typically selects the best execution plan for a query, it is not recommended for inexperienced developers and administrators to make use of them.

Nevertheless, today I will talk about a special table hint, that is the NOLOCK hint. This hint is equivalent to the READUNCOMMITTED hint.

A typical example of using the NOLOCK hint is the following:

SELECT *
FROM TABLE_NAME WITH (NOLOCK)
GO

Similar examples:

SELECT *
FROM TABLE_NAME A WITH (NOLOCK)
GO

SELECT *
FROM TABLE_NAME (NOLOCK)
GO

SELECT *
FROM TABLE_NAME A (NOLOCK)
GO

For environments where many concurrent queries are executed against the same database objects (i.e. Web Applications), the NOLOCK hint can become quite handy. By using this hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. Of course, this means that the query may see inconsistent data, that is data not yet committed, etc. So, if this is not a problem for you, it might solve many concurrency issues.

Though note that in general is not a good idea to apply the above practice as a rule.

I hope you found this post useful!

Until next time!

Database [Database_Name] cannot be upgraded because it is read-only or has read-only files

2011-03-20T09:56:00.000+02:00

A few days ago I was trying to attach some databases on a SQL Server 2005 instance. The database files were copied over the network and located on a drive on the DBMS server.

Though, while I was trying to attach the databases I was getting an error message of the following type:

Msg 3415, Level 16, State 3, Line 1

Database [database_name] cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.

Msg 1813, Level 16, State 2, Line 1

Could not open new database [database_name]. CREATE DATABASE is aborted.

As the error message was saying, I checked the permissions of the database files and ensured that they were not read-only. Also, the service user account running the SQL Server instance had full access on the files.

As I did not have much time for fully troubleshooting the issue, I provided full access to "Everyone" on all the database files I wanted to attach, and tried again.

Guess what? It worked :)

After the databases were successfully attached, I removed the full access from the "Everyone" entity and so everything was back to normal!

No global profile is configured. Specify a profile name in the @profile_name parameter

2011-02-22T17:46:00.000+02:00

Yesterday I set up a maintenance plan in one SQL Server 2005 Instance for rapidly tuning up a set of databases with some simple tasks such as index rebuild, etc.

Whenever you set up a maintenance plan is always a good idea to add in the end the “Notify Operator” Task in order to get notified on the outcome of the maintenance plan’s execution.

Here’s a screenshot of my simple maintenance task:

Even though the first three tasks were executed successfully, when I checked the history of the job that undertakes the execution of the maintenance plan, there was an error message on the last task that is the “Notify Operator” Task.

A part of the error message is the following:

"No global profile is configured. Specify a profile name in the @profile_name parameter.”

So, what does the above error message tell us? It actually says that upon the task’s execution, it cannot find a Database Mail profile in order to use it for sending the email notification.

To overcome this problem, you can set one of the available Database Mail profiles as the default one.

In SQL Server 2005 you can do this in the following way through SSMS:

Management -- Database Mail (double-click or right-click - Configure Database Mail).
Click on Next.
Manage profile security.
Click on Next.
Then you will see a list with all the available database mail profiles. You have the option to select one of these profiles to be the Default Profile (be selecting ‘Yes’ in the combo box).
Click on Next.
Click on Finish.

That should do it!

At least in my scenario I tried it and worked perfectly! :)

Of course, the above assumes that you have at least one Database Mail profile up and working! :)

Until next time!

Free Utility for SQL Server 2012 CTP1 - Snippets Generator v1.0

2011-01-08T13:59:00.002+02:00

T-SQL code snippets is a very handy new feature in SQL Server 2012 CTP1 (codenamed "Denali").

Using code snippets you can save time when you write code and you just need to enter code that does common tasks. Code reuse has always been useful and it really increases productivity!

In SQL Server 2012 CTP1 you can import your own snippets. However, prior to that, you need to create a snippet.

Each snippet is an XML file that specifies some header information regarding the snippet and then it includes the T-SQL code.

A sample T-SQL code snippet for SQL Server 2012 CTP1 is illustrated in the following image:

Yesterday while I was recording my Webcast on Denali, I thought to myself: "By the time the XML structure is common for all T-SQL code snippets for SQL Server Denali why not creating a Windows Forms Application that can server as T-SQL code snippets generator?"

Today I implemented my idea and the outcome is "Snippets Generator v1.0" :)

Using this tool you can generate T-SQL code snippets for SQL Server "Denali", save it into a directory and then add the directory, and consequently your custom snippets, into SQL Server "Denali" via the Code Snippets Manager! The directory's name will be the snippet category name in Code Snippet Manager and its contents will be the snippets!

You can download the tool from here:

Prerequisites:

.NET Framework 3.5 SP1
SQL Server 2012 CTP1 (for importing the generated snippets(s) :)

Drop me a line if you like the tool! Your valuable feedback is always welcome and much appreciated!

* Please note that this is a freeware. I will not be responsible for illegal actions or damage caused by the misuse of this software.

Webcast: Introducing SQL Server "Denali" (CTP1)

2011-01-07T15:40:00.004+02:00

Hello and Happy New Year 2011! Wishing all the best, always in good health!

Microsoft has recently released CTP1 of SQL Server 2011 codenamed "Denali".

This new version of SQL Server Server introduces many new features that increase productivity and reduce the development and administration effort.

One of my main observations is that there is a convergence of technologies. For example, SSMS is now powered by Visual Studio and that means a lot!

A solid example is the fact that the debugging functionality in SSMS was significantly enhanced with the introduction of breakpoint conditions, hit counts and many more! Debugging now seems even more similar to the Debugging functionality of Visual Studio. Another example are the T-SQL code snippets.

By having a glimpse on these features, the following major enhancements can be listed:

Advanced debugging of T-SQL scripts with: breakpoint conditions,hit counts,filter limits, actions and editing of breakpoint locations.
The Watch window and Quick Watch support watching T-SQL Expressions.
Breakpoint validation.
T-SQL code snippets.
Contained Databases.
Metadata Discovery.
WITH RESULT SETS argument.
Ad-hoc Query Paging Implementation.
Sequence Objects.
New Permissions.
New Role Management: You can now created user-defined server roles.
New Hashing Algorithms.
Object Impact and Data Lineage Analysis.
... and much more!

However, as this is my first blog post for 2011, I decided to publish something even more exciting instead of an article. So I thought to myself: "Why not recording a Webcast for SQL Server Denali?" And so I did! :)

In this Webcast I am introducing the major new features shipped with SQL Server "Denali". Along with the presentation I am performing live demos presenting some of these features in action!

OK, enough talking! Here's the Webcast:

Well, I hope you enjoyed the Webcast as I enjoyed creating it! :)

Please remember to complete the evaluation form for the Webcast by clicking on this link.

Your feedback is always valuable and highly appreciated!

Selected Tweets of Year 2010!

2010-12-31T14:51:00.004+02:00

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!

Dynamically Generating T-SQL Statements!

2010-12-16T23:50:00.000+02:00

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!

Segmenting Strings in SQL Server

2010-12-16T19:26:00.002+02:00

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!

Upcoming CDNUG Event - Microsoft and Openness / Clustering in SQL Server 2008

2010-11-24T00:35:00.009+02:00

Cyprus .NET User Group (CDNUG) brings another event to you, by/for the Community!

This month's event features two sessions:

Microsoft and Openness
Clustering in SQL Server 2008

The first session, with speaker Andreas Hadjipanayi (Technical Account Manager at the local Microsoft Office in Cyprus) discusses how Microsoft has become more open over time.

The major discussion points during this session are:

Microsoft’s contribution in standards and open source.
Microsoft’s commitment to listen to customers who want to deploy cool new solutions that combine Microsoft and other technologies.
Microsoft’s effort to make the transition to the cloud in an open manner.

The second session, with speaker Michael Theodoulou (MCITP: SQL Server 2008 Database Administrator and DBA team leader) discusses the several methods that SQL Server 2008 (and R2) offers for High-Availability (HA) and Disaster Recovery (DR) and how these can be used to maximize application uptime, data availability and reduce data-loss.

Among other, the session will cover installation of a SQL Server 2008 clustered instance:

Overview of SQL Server clustering. What it is and what it is not.
Possible configurations of clustered instances.
Requirements for Windows cluster.
Requirements for SQL Server 2008 cluster.
Domain & network requirements.
Possible issues and troubleshooting steps for AD environments.
Installation demo.

Event details:
Topic 1 : Microsoft and Openness
Speaker: Andreas Hadjipanayi (Technical Account Manager at Microsoft-Cyprus)

Topic 2 : Clustering in SQL Server 2008
Speaker: Michael Theodoulou (Leader of the DBA team at a private international organization, MCITP: SQL Server 2008 Database Administrator)

When: Wednesday, November 24, 2010
Time: 19:00

Duration: 2 Hours
Where: Microsoft Office (11 Limassol Avenue, Nicosia)

Registration: Please sign-up on the following CDNUG Yahoo poll.
-------------------
For more details check our website at http://www.cdnug.net.

So, if it happens and you are in Cyprus, join us for our November's event and learn exciting new things!

Software Systems in the 21st Century: Integration is the Key

2010-10-16T14:28:00.000+03:00

As this is my 100th post on my blog, I decided to write an article which is rather different than the previous ones. It was quite difficult to decide the topic for this post as there are so many aspects of the Database Technology that can be discussed. Finally, I decided to write on the significance of integration in software systems of today and specifically on database systems as I find it a very trending topic.

---
Before proceeding with the article, I would like to express my gratitude to all of you that support my Community efforts by visiting my blog and expressing your ideas via your comments. It is really a great experience when technical discussions are initiated via your comments.

After all, this is the purpose of this blog: to exchange knowledge with fellow members of the broader Technical Community.

Thank you very much for your support!

I hope you enjoy the article!
---

In these modern times we live in, there are thousands (if not millions) of Software Systems for almost anything that can be automated using different sets of algorithms. These can vary from simple systems that can just be forms allowing data input and reporting, to sophisticated enterprise systems that perform complex tasks such as monitoring other systems and services, reporting on their health, taking corrective actions and so on. In this post I am going to talk about Enterprise Systems and specifically Relational Database Management Systems (RDBMs) within the Enterprise.

In the Enterprise, there is always the need for integration. Integration can be defined as the set of practices and procedures that are used for building large systems that consist of other smaller systems that can be called subsystems.

Having several "isolated" systems in the enterprise cannot be so efficient and can be quite costly both in terms of resources and money, as it increases the administration and maintenance effort. In such cases different professionals deal with each system separately thus making the entire process quite complex as there is always the requirement for collaboration between these systems.Additionally, each system lives in its own world making it difficult to collaborate with other systems.

It is not random the fact that the majority of large software systems in the market, provide Application Programming Interfaces (APIs) allowing their integration with other systems. Furthermore, along with these APIs, you can also find in the market ready-made API implementations for many of these systems. These implementations can be found under different flavors such as: middleware, web services, source code libraries, etc.

In the RDBMS world you can often find these API implementations as Data Providers. To this end, if you are using RDBMS A you can interconnect it with RDBMS B via a provider. The significance of Data Providers is huge!

Some examples of such integrations include: creating a linked server in SQL Server targeting another RDBMS in order to access the data from within SQL Server, retrieving data from different RDBMSs in PowerPivot, building an SSIS package that retrieves and transforms data from several data sources and so on.

With Data Providers, you can do many things. Using these API implementations, the system that supports their usage (host) can never be isolated in the Enterprise, as you can always interconnect it with other systems (guest), in our case, other RDBMSs.

Someone might say: “But why integrating SQL Server with another RDBMS?” Here’s a simple explanation. Consider the scenario where you host a Data Warehouse in SQL Server. Data warehouses typically contain vast volumes of data in order to be able to perform analysis against this data, that is Business Intelligence (BI). An Enterprise environment can consist of hundreds of systems. It is highly likely that some of these systems are built on different technologies and their data is organized in different RDBMs than SQL Server. If you want to load this data and perform BI on it using the SQL Server Data Warehouse engine, how can you achieve that without a set of proper Data Providers? The answer is simple; you cannot do this without a Data Provider! Without Data Providers you will not be able to retrieve data from other RDBMSs. Of course, in real life, most Enterprise systems have their own Data Providers thus allowing access to other systems via API implementations. These providers are developed by either the vendor that owns the “host” RDBMS or the vendor that owns the “guest” RDBMS or both. Most of the times, Data Providers are developed by all the involved vendors.

So, a professional with the required knowledge and by using the Data Providers, can always integrate these systems thus allowing the Enterprise’s operations to run on a more efficient way. This professional needs to have a broad range of database skills, not only restricted on a specific RDBMS, but rather have a breadth of knowledge on all the involved RDBMSs. Of course, the professional can always have an expertise on a specific RDBMS and basic knowledge on other RDBMs in order to be able to perform the integration. After all, besides the additional functionality (which is what that actually makes the difference), the standard RDBMS concepts are implemented in most RDBMSs.

To this end, when evaluating different software systems (and in this case, an RDBMS) that are to be used in an Enterprise environment, it is always vital to ensure that these systems provide API implementations or at least an API that can allow integrating the system with other critical systems within the Enterprise.

As we move forward, we see that the Enterprises become even more demanding on the efficiency of their Software Systems as these are the backbone that supports the everyday operations of the Enterprise. The lack of collaboration between Software Systems within the Enterprise can negatively affect the everyday operations and that is why it is vital that these systems should interact between them via API implementations.

That is why Integration is the Key!

Categorization of Blog Posts

2010-09-25T16:34:00.000+03:00

Sometimes I get asked; "Hey Artemis, are you a SQL Server Administrator or a Developer?" I then answer: "none of the two" :)

I would rather prefer calling myself a Data Architect as I believe it is more accurate. In a relevant article I posted a year ago, I talked about the responsibilities of database developers and administrators and how these responsibilities are often interconnected. The key point of that article was that by the time you are professionally involved with Database Systems you cannot only just know one of the two aspects. So, we often meet Database professionals that are administrators with development skills and vice versa.

If we take a look in Wikipedia, we can see that the role of a Data Architect is described as follows: "A data architect is a person responsible for ensuring that the data assets of an organization are supported by an architecture supporting the organization in achieving its strategic goals." I fully agree.

As you can see, on my blog I post articles having to do with SQL Server administration, development, SQL, security, Business Intelligence, etc. I don't like restricting my quest for knowledge in just one aspect of SQL Server and that's why I mainly focus on Administration as well as on Development and some other topics.

Anyways, after this introduction, I would like to mention that I've just finished updating my personal website and guess what; I've added a page for SQL Server there as well! :)

This page, will be providing a list of most of my blog posts categorized in order for presenting my posts in a more structured way. It will actually be an index for all my articles posted here.

So, here's the link!

Have a nice weekend!

P.S. This is my 99th post. I am wondering; what topic should my 100th post be featuring? :)

Eliminating Blank Spaces in SQL Server Tables

2010-09-20T21:57:00.008+03:00

Blank spaces always needed special handling in many programming languages.

This is not an exception in the case of the SQL programming language.

In SQL Server, a variety of built-in string functions exist for assisting, among other, the developer to solve data-related problems in the case of undesired blank spaces.

However, the blank spaces scenarios vary and sometimes it is necessary to use a combination of various built-in string functions for solving the problem.

For example, consider having one or more columns within a record as the following:

An approach could be the following, using just the REPLACE built-in function like this:

Also, you could try this:

The above two approaches do not fully solve the problem of the specific blank spaces scenario.

As you can see, it started getting a little bit tricky regarding how it is possible to efficiently handle the specific blank spaces problem.

Further below I am suggesting a workaround on how to resolve such issues.

To this end, first we need to create a sample data set with a column containing blank spaces:

Now let's take a look at the data:

OK, it's time to implement the actual logic for addressing the blank spaces problem.
As it will be too complex to include the logic within a single T-SQL statement, I preferred to create a function because it will be much easier to use it again in the future and the code looks more structured.

Here's the DDL for my function:

--Function Name: NoBlankSpaces

--Purpose: Eliminate blank spaces (up to three words)

--Author: Artemakis Artemiou

--Input Parameter: Varchar(250)

--Output: Varchar(250)

CREATE FUNCTION [dbo].[NoBlankSpaces]

(

@string varchar(250)

)

RETURNS varchar(250)

BEGIN

declare @part1 as varchar(100)

declare @part2 as varchar(100)

declare @part3 as varchar(100)

declare @part4 as varchar(100)

declare @temp as varchar(100)

set @part1=(rtrim(substring(@string,0,(charindex(' ',ltrim(@string))))))

set @temp=ltrim(substring(@string,(charindex(' ',@string)),len(@string)+1))

set @part2=SUBSTRING(@temp,0,CHARINDEX(' ',@temp))

set @temp=SUBSTRING(@temp,0,250)

set @part3=ltrim(SUBSTRING(@temp,CHARINDEX(' ',@temp),250))

set @string=rtrim(ltrim(@part1+' '+@part2+' '+@part3))

RETURN @string

END

-------------End of Function DDL-----------------

As you can see in the above DDL code, I am using a combination of the SUBSTRING, LTRIM, RTRIM and the CHARINDEX SQL Server built-in string functions for achieving my goal.

Now, let's use the function and see what the outcome will be:

Cool! No more blank spaces! :)

The combination of the SQL Server built-in functions worked great towards the solution of the blank spaces issue.

You can download the code I used for this example from here.

I hope you found this post useful.

Drop me a line if you have any questions or comments!

Cheers!

Security Changes in SQL Server 2008

2010-09-08T21:40:00.003+03:00

SQL Server 2008, among other introduced significant security changes that enhance the database administrators applying an even stricter security policy on the SQL Server instances.

Besides the changes in the Windows local groups that are created during the SQL Server setup process, the changes in surface control tools where now you can use the Policy-Based Management feature that is much more powerful than the Surface Area Configuration tool, and the Kerberos support for named pipes and shared memory protocols, the change that has a strong effect on the way that many of us used to work, is that the local Windows Group BUILTIN\Administrator is no longer by default included in the SQL Server sysadmin fixed server role on new SQL Server 2008 and R2 installations.

So, what does this mean? It means that if you try to access a SQL Server 2008 (or later) instance using a local administrator user account without explicitly granting him the sysadmin server role on the instance, you will not be able to have administrative rights on the instance. Actually, if this user has not any permissions on the specific instance, he will not be able to access the instance at all!

Someone might say that this makes things more complicated but the truth is that it does not. It is an excellent security enhancement that actually separates Windows administrator accounts from SQL Server administrators.

And by the way be careful when you install and perform the initial setup of a SQL Server 2008 (or later) instance because if you don’t include at least one user in the sysadmin role, you will be locked out of that instance :)

For more information on the security changes in SQL Server 2008 and SQL Server 2008 R2 you can visit this MSDN Library article. Make sure that you read it before configuring the security of your new SQL Server instance!

DevReach 2010!

2010-08-23T21:44:00.002+03:00

DevReach 2010, the premier developer conference on Microsoft technologies in Central and Eastern Europe will be taking place on October 18 and 19 in Sofia, Bulgaria.

This year, DevReach celebrates its fifth anniversary. To this end it has a new fifth track!
The event includes 60 unique sessions with more than 30 rock star speakers!

DevReach 2010 features a wide variery of topics such as:

Web development (HTML 5 in ASP.NET)
Windows Workflow
jQuery, Visual Studio 2010 and ASP.NET
SQL Server 2008 R2 development
Agile development methodologies
Silverlight 4.0
Sharepoint 2010
Windows Phone 7 Series development
... and much more!

The full list of topics can be found here!

Some highlights of the event include:

Exclusive keynote from Scott Stanfield (CEO Vertigo Software), Richard Campbell (.NET Rocks! and RunAs Radio) and Beth Massi (Senior Program Manager Visual Studio Team).
Early Bird 20% discount is available until 15 Sept. All Early Bird attendees enter drawing for XBOX 360.
All MVPs and INETA UG members receive special discount. Register through the MVP or UG lead.
The VIP pass gives you access to all video recordings and exclusive party with all speakers!

To see how #DevReach 2009 looked like you can visit this link.

Additionally, On September 9, 2010, DevReach 2010 Online, a FREE online event will be taking place with speakers from DevReach 2010.

You can join DevReach Facebook page and follow DevReach on Twitter and get the chance to win a free pass!

So if you have the chance to attend the event, you will sure be able to deepen your knowledge on the latest Microsoft technologies and learn many new ways of being more productive with less effort when it comes to software development!

Changing the Database Owner in a SQL Server Database

2010-08-04T22:03:00.001+03:00

In SQL Server 2000, the dbo (Database Owner) is a special user that has all the necessary permissions to perform all activities in the database. This user exists in all SQL Server databases.

Usually, there are SQL Server or Windows logins mapped to the dbo user, thus "inheriting" the permissions of the database's dbo user and being able to perform all the activities in the database that the dbo user can perform.

In some cases, due to various changes a DBA might perform in SQL Server, you may discover that the dbo user is orphaned, meaning that there is not any login mapped to this user.

If you would like to change this, thus mapping a login to the dbo user you can make use of the "sp_changedbowner" system stored procedure.

The syntax as provided in the T-SQL reference library is the following:

sp_changedbowner [ @loginame= ] 'login'

[ , [ @map= ] remap_alias_flag ]

For example, if you want to make the SQL Server Login 'Tom' the owner of the database "TestDB" you can use the following T-SQL script:

USE TestDB

EXEC sp_changedbowner 'Tom'

Additionally, if you want to make a Windows Login, for example 'SampleDomain\TestUser' the owner of the database "TestDB" you can use the following T-SQL script:

USE TestDB

EXEC sp_changedbowner 'SampleDomain\TestUser'

Please also note that only members of the sysadmin fixed server role can execute the stored procedure sp_changedbowner.

In SQL Server 2005 or later, you can make use of the "ALTER AUTHORIZATION" command as sp_changedbowner will be removed in a future version of SQL Server.

CDNUG Event Review - July 15, 2010 - Windows 7 Development / SQL Server 2008 R2

2010-07-31T00:16:00.005+03:00

A great CDNUG event took place on July 15, 2010! As it was the last event before summer holidays, it was a celebratory one of course!

After having some sweets and Greek frappé coffee, the event started!

Due to the nature of this event, we had two sessions. The first session, dedicated to Windows 7 Development was presented by my good friend and CDNUG member Ioannis Stavrinides (MCP, MCTS) - (Blog, Twitter). The second session was presented by me and it was dedicated to SQL Server 2008 R2, where I introduced the main features shipped with this release both for Developers and IT Pros.

Ioannis started his presentation by first introducing to us WPF. Then he showed with many live demos how developers can use WPF and the Windows API Code Pack for Microsoft .NET Framework through Visual Studio 2010, thus being able to build Jump Lists, Progress Bars, Tabbed Thumbnails and Tabbed Toolbars. Also, we saw how we can make use of the Icon Overlay feature.

Right after Ioannis's great session, my session started taking place. During my session I talked about some of the amazing new features shipped with SQL Server 2008 R2.

Main Features

Database Engine - Manageability Enhancements

Connectivity to SQL Azure.
Data-tier Application Upgrade.
SQL Server Utility.
The VIA protocol has been deprecated.

Service Broker

Option to turn on and off poison message handling on a queue.
A queue that has poison message turned off will not be disabled after consecutive transaction rollbacks.

Master Data Services

The technology, tools, and processes required to create and maintain consistent and accurate lists of master data.

StreamInsight

A powerful platform that you can use to develop and deploy complex event processing (CEP) applications.

Reporting Services - Features

“Grab and Go” reporting experience enabled through the Report Part Gallery.
Support for maps and geospatial visualizations with integration to Bing maps and support for SQL spatial data types.
Additional visualizations including indicators, sparklines and data bars.
Support for consuming SharePoint lists and PowerPivot models as data sources.
Support for Madison and SQL Azure data sources.
Render any report as a data source to PowerPivot or other applications through ATOM data feeds.

Report Builder 3

Integration with Bing maps.
...and much more!

SQL Server PowerPivot

An Add-on for Excel 2010 / SharePoint 2010.
Brings the full power of SQL Server Analysis Services right into Excel.
In-memory column based compression.
Millions of rows of data can now be stored, sorted & aggregated.
Support for a wide variety of sources.
Rich set of mathematical functionality.

Existing functions already in Excel
+
Data Analysis eXpressions (DAX)

Along with the presentation I performed many demos, showing live how developers / IT Pros can take advantage of these new features and be more productive with even less effort. Some of the live demos included SQL Server Utility Explorer, Data-Tier Applications design and deployment, Master Data Services, and of course, the series of demos could not be considered as completed without showing a live demo of SQL Server PowerPivot!

PowerPoint Presentation
Please find below my PowerPoint presentation for this session:

SQL Server 2008 R2: Features for Developers and IT Pros

View more presentations from artemakis.

The event was a success and we really enjoyed it!

See you at another event soon!

Data Access and Consumption with WCF Data Services and PowerPivot

2010-06-23T17:38:00.002+03:00

It’s been a while since my last post but I’ve changed job a few weeks ago and I was in the process of adapting to the new environment :)

OK! It is time to sit back, relax a bit, have a cold beer, put some music on, and start writing!

A few weeks ago the number of visits on my blog exceeded the number of 50.000! So I decided to write a rather big article with a “celebratory” flavour :)

Though, before proceeding with the article, I would like to say a huge thank you to the SQL Server community and to each of you for supporting my efforts by visiting my blog, reading and referencing my articles, and commenting my posts.

As Data Access and PowerPivot are my favourite topics, in this article I will talk about a combination of two great technologies; WCF Data Services and PowerPivot.

To this end I am going to build a WCF Data Service that will be exposing data from a SQL Server 2008 R2 database as an RSS feed, then consume the data using PowerPivot and see how the data in PowerPivot is updated when the RSS feed is updated too.

Let’s begin by taking a look at the database schema that is used for this post:

Now let’s start building that WCF Data Service!

We launch Visual Studio 2010 and we start building an ASP.NET Web Application:

We then add a new item to our solution of the type "ADO.NET Entity Data Model":

We choose to generate the data model directly from the database:

We set up the data connection:

We then select the database objects to be included in the data model that will be generated:

The data model is ready:

We then add another new item to the solution of the type "WCF Data Service":

We also modify the source code as follows (this is only for demo purposes, in real-life you should be careful with the level of access you provide to your data):

Well, that’s it! We run the solution and this is what we get in Internet Explorer:

The data is exposed as an RSS feed!
The data service is now ready.

We copy the URL to the clipboard:
http://localhost:1336/dataService.svc/

Now, for consuming the data service we are going to use PowerPivot.

We launch Excel 2010, and then the PowerPivot dialog. We then click on the “From Data Feeds” button on the ribbon and we use as the Data Feed URL the URL we earlier copied to the clipboard (that is the URL for the Data Service):

We test the connection and we see that everything works well; our RSS Feed is up and running:

We click on “Next” and we then select the tables to be imported:

We click on “Finish” and we see that all the records were imported successfully:

We then click on “PivotTable” on the ribbon and we select to build a “Chart and a table (vertical)":

And this is what we get after visually building a simple query in the Excel Workbook using PowerPivot:

Now here comes the fun part. I am going to modify the contents of the “Customers” table so that most of the customers have as location the value “NIC” (yes, this stands for Nicosia :).

So 16 out of the 20 customers will now have “NIC” set as their location and each of the rest of the customers will have a different, unique location. After the data modification this is what we get in SSMS:

But how are these data modifications reflected in PowerPivot? It is actually very easy. We just enter into the PowerPivot dialog again and click on the “Refresh” button:

Then back in our Excel Workbook, we perform again the aggregation and we see that the chart is also updated:

What exactly happened when we clicked on the “Refresh” button was that PowerPivot communicated with the database in SQL Server via the RSS feed and tracked that the data contained in the “Customers” table was modified. To this end, it re-imported this data into the workbook. This is quite cool, isn’t it :)

Summing up, in this post we saw how we can build a simple WCF Data Service for exposing data via an RSS Feed, how can this data be imported into PowerPivot, and how can PowerPivot track any data changes thus reflecting them on its charts.

Until next time!

Product Review: SQL Search - Searching for SQL Text Within SQL Server DB Objects

2010-05-07T15:44:00.007+03:00

Some time ago Red Gate released SQL Search, a SSMS add-in that allows the user to search for fragments of text within database objects in a SQL Server instance! Yes, I know, this is cool! :)

There are many scenarios where the tool can come in handy. For example, imagine trying to perform a modification in a table and there is a view using the "WITH SCHEMABINDING" option which binds the view to the schema of the specific table. So, in order to be able to perform the table schema modification you will first need to either modify or delete the view. So what can you do for finding any views, functions or any other objects that reference the table? You can search for such database objects referencing the table name in their definition.

Below I am presenting some screenshots illustrating the usage of the tool.

After installing SQL Search and starting SQL Server Management Studio, you will notice that there is a new add-in added:

When clicking on the SQL Search add-in you are presented with the following screen:

The following screenshot explains the main controls of the tool:

For this example, I typed the text "customer" and searched for all the database objects, in all the databases within the instance that reference it. To this end I was presented with the following screen:

When clicking on a programmability item, i.e. a stored procedure, you are also presented with a preview of its T-SQL code that among other references the database objects having the provided text fragment:

Then, by double-clicking on the item, or by clicking on the "Select object in Object Explorer" hyperlink, you are able to access the object via SSMS Object Explorer:

Another important thing is that the tool is free and you can download it from here :)

I hope you found this product review useful!

Policy-Based Management in SQL Server 2008

2010-04-27T15:21:00.004+03:00

Some time ago I promissed publishing a post on Policy-Based Management feature in SQL Server 2008. Well, it's time to fulfil my promise! :) This is a rather large post, I hope you find the information it contains useful!

Policy-Based Management is indeed one of the greatest administration features in SQL Server 2008. With this feature you can easily manage one or more instances of SQL Server!

The main concept in Policy-Based Management, is that Policy Administrators can set up policies that are evaluated upon certain events within the scope of the monitored SQL Server instance(s). After setting up the policies, based on certain scenarios, the policies are evaluated and specific actions are automatically performed as set up in the active Policies.

To this end, there are four evaluation modes:

On demand - This mode evaluates the policies when explicitly requested by the user.
On change: prevent - This mode uses DDL triggers in order to prevent Policy violations.
On change: log only - This mode allows Policy violations but it logs each violation using event notification. It logs the violations to the SQL Server logs and Windows Application logs.
On schedule - This mode allows the user to schedule (using a SQL Agent job) when the policies will be evaluated.

Let’s get a first look and feel at the Policy-Based Management set up dialogs in SSMS. In the following screenshot you can see the Policy Management module in the Object Explorer in SSMS:

When right-clicking on Policies you can create a new Policy.
When right-clicking on Conditions you can create a new condition and then assign it to an existing or a new Policy.
The Facets is a collection of SQL Server objects that allow you to set up conditions and policies against their properties. By right-clicking on a Facet you can create a condition or a Policy.

Note that the Policy Management module falls under Management as its scope is for the entire SQL Server instance. Of course, via the Policies you create, you can evaluate multiple SQL Server instances, certain databases, even certain database objects!

OK! Now let's proceed with an example, showing how we can set up policies based on the abovementioned evaluation modes. For this post, I will be using the "AdventureWorks2008" sample database.

On demand
We right-click on Policies and we select New Policy. We are presented with the following dialog where we provide a name for the Policy and then we either select the check condition or we create a new one. In this case we will be creating a new condition:

The next screenshot shows the newly created condition:

For creating a new condition, you must provide a name, select the Facet (in this example) the Stored Procedure, and set the expressions for evaluating the properties you like. In this case we want the condition to track all the stored procedures that their names start with 'sp_'.

We click on the OK button and we are returned to the Policy:

You can see that the Policy now contains the "SPNames" condition we created in the previous step. Also, I set up the Evaluation Mode to "On Demand". Though, this type of condition allows you to select any Evaluation Mode.

The available Evaluation Modes are dependent on the type of the condition. For example there are conditions for which a DDL trigger cannot be executed so the “On change: prevent” mode cannot be selected in such case, etc.

Also, in the “Against targets” list you can target specific databases against which the condition will be evaluated. Additionally, in the “Server restriction” you can select against which SQL Server instances the condition will be executed. Remember: Policy-Based Management allows managing multiple instances of SQL Server!

Now, let’s set the Policy to only evaluate the “AdventureWorks2008” database by clicking on the “In Every - Database” list item. Here’s the new condition for restricting the Policy’s evaluation on the specific database:

We click on OK and we are returned back to the Policy properties dialog:

I will not add a condition for the Server restriction as I am only using a single SQL Server instance for this post.

That’s it! We click on OK and the Policy is ready! As this Policy’s Evaluation Mode has been set to “On demand” we cannot enable it. It wouldn’t make any sense after all. We can only use it by right-clicking on it and selecting “Evaluate”. This is the purpose of the On Demand evaluation mode after all! :)

After running the Policy we get its evaluation results:

From the above screenshot we can conclude that at the time being all stored procedures within the “AdventureWorks2008” database comply with the condition set up in the Policy.

Now, what about adding a new procedure on the “AdventureWorks2008” database having the name “sp_SampleSP”? Let’s do so, re-run the Policy and check the evaluation results!

Here are the results:

Oops, we got an error message by the Policy. Let’s click on [Details] View… to see what’s wrong:

We can see that the Policy reported that the stored procedure “sp_SampleSP” does not comply with its condition of not having stored procedures with names starting with 'sp_'.

The above is a very simple example of what a Policy can do. Imagine what else you can do with Policy-Based Management thus making your life easier! :)

Let’s delete now the sample stored procedure we earlier created. We need to comply after all! :)

On change: prevent
If we want to change the Evaluation Mode of the above Policy to “On change: prevent”, we can double-click on the Policy, change it and then enable the Policy:

Now let’s try to create again the same stored procedure:

As you can see from the above screenshot, the Policy prevented the Database Engine from creating the stored procedure as it does not comply with the naming condition specified by the Policy!

On change: log only
Similarly, we can change the Evaluation Mode of the Policy to “On change: log only”. This will allow the stored procedure to be created but it will also add a log entry in SQL Server Logs and Windows Application Log reporting the violation of the Policy:

On schedule

Last but not least, you can set up a schedule for when the Policy should be executed. This will create a SQL Server Agent job that will execute the Policy at the selected time.

Summary

This post was a very basic example on how you can use Policy-Based Management in SQL Server 2008. You can build really advanced Policy scenarios where you can have multiple Policies and conditions, evaluating a large set of actions in more than one SQL Server instances.

The Policy-Based Management feature in SQL Server 2008 is extremely powerful and allows setting up strict Compliance scenarios ensuring the health and desired structure of the monitored SQL Server instances and the databases that exist within them.

The best way to learn more about Policy-Based Management in SQL Server is to get experimented with it in order to fully realize its potentials.

You can also check out this link for more information on Policy-Based Management in SQL Server.

Until next time!

The Significance of Technical Communities

2010-04-21T00:46:00.002+03:00

So here I am thinking of the next event. Should it be a topic on a new .NET Technology? A topic on a SQL Server 2008 R2 feature maybe? When should I deliver my next webcast? Which is the next local or regional event am I going to attend?

As I am thinking the above, I am also feeling the need to say a few words on Technical Communities. I guess that all people sometimes feel the need to philosophize things a bit.

A few days ago I had the pleasure to attend the Microsoft Regional CEE MVP Summit in Athens, Greece. I had the chance to meet in person with many people whom I only knew through emails and several social media on which we exchanged knowledge, ideas, and generally interacted on technical topics of common interest.

I must say, attending the Summit in Athens, was one of the best experiences of my life. When attending such events, you fully realize what the Technical Community is all about. You interact with great people sharing the passion for technology. You discuss with people sharing common interests on certain areas of technology. You share knowledge and you are glad that you are able to help a person learning a bit more on a specific topic. You are also glad that this person helps you learning something more on the technology topic of his/her expertise.

So what a Technical Community really is? Is it a mailing list on which you post questions on various technical issues you might be dealing with and wait for answers? Is it a platform on which you collaborate by answering various technical questions? Is it a forum, a blog maybe?

Well, a Technical Community is all the above plus much more. First of all, I believe that it is not just the platform that makes a Technical Community. The platforms and related tools make it easier to exchange knowledge and they have a significant role within a Community. Though, what really makes a Technical Community is the people. It is the people who share a passion for technology and have the willingness to exchange knowledge without requesting anything in return. It is the people who spend hours from their free time for helping others and feels really good for doing so. It is the people who constantly get more educated on their area of expertise and present in events, trying to help other people who are interested on those topics for learning even more, answering their questions and discuss with them.

A Community is actually an “environment” that helps its members to develop their skills through knowledge exchange via presentations, hands-on labs, online and offline discussions and many other activities. It helps its members to catch up with the latest technologies of common interest easier and more efficiently. It is a “place” where each individual puts effort and time for helping others because he believes in people. A Community is all about people and their need for belonging to a broader group of people within which they can share knowledge, exchange ideas, and share the passion and excitement on technology.

I feel that this is what a Technical Community is all about.

Microsoft DevDays 2010 in Cyprus!

2010-04-08T00:51:00.004+03:00

A few weeks ago I got asked to present on Microsoft DevDays 2010 in Cyprus. DevDays is the biggest event in Cyprus for Software Architects and Developers.

This year, the event features many hot topics such as:

Visual Studio 2010
Windows Azure and Windows Identity Framework
Interoperability and how to run PHP Applications on Microsoft Platform
...and much more!

I will be presenting the session with topic: "An Introduction to the ADO.NET Entity Framework and Data Services". This session is all about data! It discusses how data can be easily accessed and manipulated using the ADO .NET Entity Framework and then how this data can be exposed to the Web using the powerful WCF Data Services.

So if it happens and you are in Cyprus on April 15, make sure you register for the event here and stop by for learning more on state of the art Microsoft technologies! The weather in cyprus is quite good as well! :)

Of course, if you are in another country in Central or Eastern Europe, a DevDays event might be taking place near you! Check out this link for more information!

See you at the event!

Congratulations 2010 Microsoft MVP!‏

2010-04-01T23:43:00.004+03:00

Today is a very special day! I am proud to announce that I received the MVP award for the second time in a row!

-------------------------------------------------------------------

Dear Artemakis Artemiou,

Congratulations! We are pleased to present you with the 2010 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in SQL Server technical communities during the past year.

-------------------------------------------------------------------

Once again, I would like to thank all the SQL Server community members for making this community a great place for exchanging knowledge and exploring the real potentials of SQL Server and its related technologies! It is awesome being part of this community!

Also, many thanks to Microsoft for strongly supporting the technical communities around the globe!

I would also like to thank the SQL Server Product Group for developing such great technologies and everyone involved in the MVP Award Program.

On my end, as I did last year, I promise that I will keep on doing my best regarding my involvement in the SQL Server community and keep on sharing knowledge!!!

Building Data Relationships in PowerPivot

2010-03-12T21:45:00.002+02:00

This post is a follow-up to the previous post titled: “Using PowerPivot’s Copy-Paste Support for Importing Data from Word”.

In this post we will see how we can import data from Microsoft Word using PowerPivot’s copy-paste feature, create relationships on the data and perform aggregations that is BI! :)

For example, consider the two following tables which exist in a sample Word document:

The business query is to evaluate the sales activity for all the salesmen and decide possible adjustments to their commissions.

To do this in PowerPivot, we are going to use the copy-paste feature, create a relationship for the two tables’ data, and visually build our business query using the user-friendly interface and tools of PowerPivot.

The first step is to import the data from Word right into Excel using PowerPivot. So first, we have to launch Excel 2010 and then start the PowerPivot environment:

Then, we copy the first table from the Word document into clipboard and we click on the “To New Table” button.

After previewing the data and clicking on the “OK” button, we see that the first table’s data are imported into PowerPivot:

Similarly, the next step is to import the data of the second table into PowerPivot as well. To this end, we copy the second table into clipboard and in the PowerPivot dialog we click again on the “To New Table” button.

After the preview, we can see that the second table’s data are imported into PowerPivot:

Now it’s time to create the relationship between the two tables! We click on the “Table” menu, then on the “Create Relationship” button, and we set the following relationship between the two tables:

That’s it! If we now click on the “Manage Relationships” button we can see that the relationship has been created:

Now, back to the “Home” menu, we click on the “PivotTable” button and we select a PivotTable chart preset, for this example I selected the “Single PivotChart”. We select to import the PivotChart into a new worksheet and we have our data ready for processing:

For answering the business query we can check out the total sales amount per salesman and also see the employment date of each salesman. Then, based on the sales amount of each salesman, we can propose possible adjustments to the commissions. For this purpose, I created the two following charts:

Based on the sales activity of each salesman and on the results of the aggregations as illustrated in the two above charts, we derive to the conclusion that Salesman 3 should get the larger commission rate, then Salesman 1 followed by Salesman 2 and last Salesman 4 who has the less sales activity.

This post provided a simple example on how we can import data right into Excel using PowerPivot’s copy-paste feature, create relationships on the data and perform BI with ease by using the powerful engine of SQL Server PowerPivot.

I hope you found this post useful!

Using PowerPivot’s Copy-Paste Support for Importing Data from Word

2010-03-10T19:38:00.001+02:00

One of the great features of SQL Server PowerPivot is that it supports importing data directly from the clipboard. This is quite handy in cases you want to import and analyze data fast.

An example of using the above feature is importing data from Microsoft Word. Let’s say you have a Word document containing the following table:

The scenario here is to import the data from Word and perform a simple analysis.

The first step is to select the entire table and copy its contents to the clipboard.

Then you have to launch Excel 2010 and open the PowerPivot window:

Click on “To New Table” and you will be presented with a Paste Preview dialog in which you will be able to see the contents of the clipboard and how they are “translated” by the PowerPivot engine:

At the bottom of the dialog you can choose to use the first row as column names and that’s what I did in this example.

You then click on “OK” and you are presented with the data as imported into PowerPivot:

Next, by clicking on the “PivotTable” button you are presented with various options regarding what type(s) of pivot tables you can create:

For this example I chose to use a “Single PivotChart”.

Then, the PowerPivot dialog closes and the Excel worksheet is presented containing the pivot table:

You can then easily perform aggregations using the tools provided by PowerPivot:

Well, I guess that’s it for today! I hope you found this post useful!

Another post on copy-paste and PowerPivot is coming where we will see how we can copy-paste two tables from Word and build relationships on the data.

Creating Logins for Orphaned SQL Server Users

2010-02-11T17:07:00.001+02:00

There are cases where you might need to restore an entire SQL Server database (i.e. in the case of loss of data, etc.). A set of objects that are restored from the backup set and are included in the restored database are the database users.

Though, when speaking about SQL Server logins, these users will might be orphaned as the corresponding SQL Server logins might not exist anymore. A similar case is when restoring a database on another SQL Server Instance.

In this case, you can use the following stored procedure for fixing such issues: sp_change_users_login

Example
USE AdventureWorks
GO
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'Password';
GO

The above example will create a new SQL Server Login for the given user name and use as a password the given password string.

You can also assign existing logins to orhaned users.

For more information you can visit SQL Server Books Online.

Cleaning up Backup and Restore History Logs in MSDB

2010-01-31T20:45:00.000+02:00

Sometimes, there are cases, where it is reported to me that the MSDB database is huge and needs to be shrinked again back to "normal" sizes.

One case which affects the size of MSDB, is the backup history. Every time a database backup/restore operation takes place in SQL Server, the relevant information is kept into certain system tables in the MSDB database.

SQL Server stores database backup and restoration information into the following tables that exist in the MSDB database:

backupfile
backupfilegroup
restorefile
restorefilegroup
restorehistory
backupset

Let's examine an example for better understanding the process.

In this example I am using a database in SQL Server 2008 called "DEMODB2008". To this end, I performed some backup and restore operations for allowing us to check out the data stored in MSDB.

The following queries retrieve information regarding the backup and restore operations that took place on the specific database:

-- This query returns information about the backup set

SELECT * FROM msdb.dbo.backupset

WHERE [database_name]='DEMODB2008'

-- This query returns information about the backup files including

-- the logical and physical file names, drive, etc.

SELECT bf.*

FROM msdb.dbo.backupfile bf

INNER JOIN msdb.dbo.backupset bs

ON bf.[backup_set_id]=bs.[backup_set_id]

AND bs.[database_name]='DEMODB2008'

-- This query returns information about the filegroups that were backed up.

SELECT bg.*

FROM msdb.dbo.backupfilegroup bg

INNER JOIN msdb.dbo.backupset bs

ON bg.[backup_set_id]=bs.[backup_set_id]

AND bs.[database_name]='DEMODB2008'

-- This query returns information about when a database was restored.

SELECT * FROM msdb.dbo.restorehistory

WHERE [destination_database_name]='DEMODB2008'

-- This query returns information about the physical files involved in the restoration process.

SELECT rf.*

FROM msdb.dbo.restorefile rf

INNER JOIN msdb.dbo.restorehistory rh

ON rf.[restore_history_id]=rh.[restore_history_id]

AND rh.[destination_database_name]='DEMODB2008'

-- This query returns information about the restored filegroups.

SELECT rg.* FROM msdb.dbo.restorefilegroup rg

INNER JOIN msdb.dbo.restorehistory rh

ON rg.[restore_history_id]=rh.[restore_history_id]

AND rh.[destination_database_name]='DEMODB2008'

In the case where the MSDB database is huge you can try removing any unnecessary backup and restore history logs.

Instead of manually deleting these logs from the above tables, you can use a SQL Server system stored procedure instead.

This stored procedure is called "sp_delete_backuphistory" and exists in the MSDB system database.

The usage for the above stored procedure is:

sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

Example:

exec msdb.dbo.sp_delete_backuphistory '2009-01-01'

The above command will clean up the backup and restore history logs up to 01/01/2009.

For more information you can visit SQL Server Books Online.

The PowerPivot Experiment

2010-01-05T17:52:00.013+02:00

Hello and Happy New Year 2010!

Wishing you all the best, a new year full of health, happiness, success and even more SQL Server knowledge!!! :)

As this is my first blog post in 2010, I thought that it should focus on a really hot topic! I hope you will enjoy it!

I called this post “The PowerPivot Experiment”. Here’s a little background information on how I decided writing this article.

When PowerPivot (formerly known as "Gemini") was released, I really wanted to test its real capabilities by trying to import a really large amount of information right into excel and perform aggregations. To this end, I designed a simple relational database with five tables, populated it with data and performed my experiment using PowerPivot. For the record, the total amount of data was more than 100 Million Rows.

In this post I will explain step-by-step the experiment, starting from the database design, then explaining the import process and finally, provide some examples on how I processed this huge amount of data.

Before describing the steps, I would like to list the several software technologies used, as well as the hardware and O/S configuration of my infrastructure.

Hardware –O/S Configuration:

OS Name: Microsoft Windows Server 2008 R2 Enterprise
OS Version: 6.1.7600 Build 7600
System Type: x64-based PC
Processor: Intel(R) Core(TM)2 Duo CPU T7250 @ 2.00GHz, 2001 Mhz, 2 Core(s), 2 Logical Processor(s)
Installed Physical Memory (RAM): 4.00 GB
Total Physical Memory: 4.00 GB
Available Physical Memory: 2.47 GB
Total Virtual Memory: 8.00 GB
Available Virtual Memory: 6.10 GB
Page File Space: 4.00 GB

Software tools/technologies used:

MS SQL Server 2008 R2
MS SQL Server PowerPivot
MS Excel 2010
Red Gate’s SQL Data Generator (for populating my database with sample data)

Database Design
The following database diagram displays the tables participating in the DB Schema used in my experiment along with the table sizes in terms of total number of records:

Database size and other statistics:

Importing the Data Using PowerPivot

Step 1: Starting up Excel 2010 and launching PowerPivot Window

Step 2: Establishing a connection to a SQL Server Instance

Step 3: Selecting SQL Server Database Tables for importing it into Excel 2010

Step 4: Start Importing Data (Time: 15:46)

Let’s take a look on the status of the import process as it is occurring:

Step 5: Import completed! Total Time: 15:54 – 15:46 = 8 minutes!!!

It only took 8 minutes for the entire import process to complete! I personally believe that this is awesome! :)
Total number of records imported: 40,000 + 60,000 + 500,000 + 100,000,000 + 5,000 = 100,605,000

Working with the pivot tables
Right after importing the data, I proceeded with creating two pivot tables (by clicking on the “PivotTable” button in the PowerPivot window) for being able to easily perform aggregations:

The following two screenshots are examples of some basic processing I did against the data I imported into Excel using PowerPivot. Using the PivotChart tools, I visually built OLAP queries answering business questions like: (i) What is the total sales amount by product, and (ii) What is the total sales amount by customer:

Note that you can also modify the charts as you could also do in earlier versions of Excel. For example, in the below screenshot you can see that the chart “Total Sales by Customer ID” was modified in order to illustrate the data using a pie chart:

Saving the workbook
After I finished with aggregating the data using the PivotChart tools, I saved the workbook. Note that by saving the workbook, the data that were imported earlier into the workbook using PowerPivot, are stored IN the workbook and you can access it anywhere as they are contained in the workbook file as a binary object.

In my example, the database size was around 2300 MB. If you take a look at the following screenshot which presents the properties of the saved workbook, you can see that the file size is 732 MB:

Remarks
After performing the above practice, I was able to see in action and test some of the powerful capabilities of PowerPivot.

The data compression, the speed of the import process, the ease of creating rich Pivot Tables and OLAP queries are some of the great features of PowerPivot that take Business Intelligence a step further!

Well, that was a description of my “PowerPivot Experiment” :)

I hope you found this post useful!

There is more to come on PowerPivot so make sure you check back my blog soon!

Selected Tweets of Year 2009!

2009-12-24T00:30:00.007+02:00

This is a rather different post than the usual. As the end of year 2009 approaches, I decided to publish a selected number of tweets I did, grouped into different categories.

It was a great experience to communicate with the Community, among other, by using Twitter. Throughout tweeting it was possible to easily and directly publish short but useful SQL Server tips, links to articles, links to reviews of SQL Server tools and much more!

I hope you find my tweets useful! :)

Further below you can find my tweets grouped into the following categories:

T-SQLTips
SQL Server Performance Tips
Events-Related – Screencasts – Webcasts
Blog Announcements
Other SQL Server-Related Tips, Articles and Tools
Miscellaneous

[T-SQLTips]

[T-SQL Tip] You can find information on foreign key constraints in sys.foreign_keys and sys.foreign_key_columns catalog views
[T-SQL Tip] You can find information on primary keys and unique constraints in sys.key_constraints and sys.indexes catalog views
[T-SQL Tip] The last digit in a DATETIME instance can only be 0, 4 or 7. You can use DATETIME2 for more precision.
[T-SQL Tip] A Best Practice for avoiding deadlocks, is to keep transactions in a single batch
[T-SQL Tip] When you rollback a transaction, some functionalities are not reset (i.e. seed values for identity columns)
[T-SQL Tip] An option for Data Archiving in SQL Server is using the OUTPUT clause - http://bit.ly/QpNIf
[T-SQL Tip] SELECT GROUPING(ColumnName): Value '1' indicates an aggregate/summary row, Value '0' indicates a detail row
[T-SQL Tip] If you want all values to be included in an aggregation, make sure you replace NULL values with 0's using ISNULL(ColumnName,0)
[T-SQL Tip] When using 'COUNT(ColumnName)' it returns the number of rows containing data in that column. NULL values are ignored
[T-SQL Tip] Order of operations in a WHERE clause: NOT, AND, OR - When not sure, use parentheses
[T-SQL Tip] To use in the WHERE clause of a query 'value<>NULL' you first need to 'SET ANSI_NULLS OFF' - By default is set to ON
[T-SQL Tip] SET XACT_ABORT OFF: Only the T-SQL stmt (in some cases) that raised the error is rolled back and the txn continues processing.
[T-SQL Tip] SET XACT_ABORT ON: If a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

[SQL Server Performance Tips]

[MSSQL Perf. Tip] When perf. counters indicate a large number of page splits, consider rebuilding the index and decreasing the fill factor
[MSSQL Perf. Tip] For optimal performance, avoid using functions in the WHERE clause
[MSSQL Perf. Tip] The Query Optimizer cannot use indexes when leading wildcard characters are used in the LIKE clause of a WHERE clause
[MSSQL Perf. Tip] The Query Optimizer cannot use indexes when a NOT operator is used in the WHERE clause of a query
[MSSQL Perf. Tip] Regarding joins - Minimize the number of join clauses. Outer joins are more expensive than inner joins.
[MSSQL Perf. Tip] How to optimize queries: re-write query, add/remove indexes, de-normalize/normalize referenced tables
[MSSQL Perf. Tip] With using "SET STATISTICS TIME ON" you are able to get the execution time in ms for each query execution
A great SQL Server DMV for identifying various bottlenecks and other performance issues - sys.dm_os_wait_stats - http://bit.ly/158Iot
[MSSQL Perf. Tip] Want a fast way to check your query's performance (page reads, etc)? Check out "SET STATISTICS IO" - http://bit.ly/4BNUDG
With "SET STATISTICS IO" you can also easily check the performance of table scans when experimenting with indexes.
Covered Index: An index that contains all the columns referenced in the query (in any clause)
Nonclustered Index: Its bottom level (B-tree leaf nodes) contains only columns included in its key and pointers to the actual data rows
Clustered Index: Its bottom level (B-tree leaf nodes) contains all the table rows (with all the columns)
[SQL Server Performance Tip] Always use a covered index in the cases you have READ operations that return a large number of rows.
Using a covered index will improve performance a lot for queries that return a large number of rows.
When changing the collation of a col. in a WHERE clause, the col. index won't be used as the data is sorted based on another collation

[Events-Related – Screencasts – Webcasts]

I have just updated CDNUG's website with a review and photos of our recent event on VS2010 and Windows Azure! - http://bit.ly/2SBLWA
New Screencast Available! - "Object Dependencies in SQL Server 2008" - http://tinyurl.com/m7pduc
My Webcast on SQL Server 2008 T-SQL Enhancements Now Publicly Available! - http://tinyurl.com/n5ztph
Summary of the CDNUG special event which took place on June 18th, 2009: http://tinyurl.com/nmtfdx
New screencast - Using Inheritance in the ADO .NET Entity Framework: http://tinyurl.com/ofxnb3
My first screencast on SQL Server 2008! http://tinyurl.com/oz7ph9
Just finished updating CDNUG's website with last Tuesday's event. Entity Framework and LINQ to Entities! http://tinyurl.com/d9ynh2
Reviews for my two latest Live Webcasts on 24 Hours of PASS and MY-TG SQL Server Community Series: http://bit.ly/O8LIE, http://bit.ly/MqRDD
[Blog] Introduction to #SQLServer #PowerPivot - MS Momentum 2009 Session Review: http://bit.ly/8wjE7t
[Blog] CDNUG Event Review - October 27, 2009 - Visual Studio 2010 Beta / Windows Azure Platform - http://bit.ly/1zIEaN
The review of my session (Sync Framework) on MY-TG SQL Server Community Series now available on my blog - http://bit.ly/MqRDD
Finally! The review of my session (18) on 24 Hours of PASS is now available on my blog - http://bit.ly/O8LIE - Enjoy!
Published a review on my blog for my session (http://bit.ly/RhVer) of 24 Hours of PASS.

[Blog Announcements]

[Blog] Using the OUTPUT Clause: A simple data archiving example - http://bit.ly/8HSfd8 #in #SQLServer
[Blog] #Microsoft Momentum 2009 - http://bit.ly/48laSb
Blog article on Windows Internal Database - http://bit.ly/4dYhyz
Posts on my blog about SQL Azure - http://bit.ly/274OC6
Blog post on a way of rebuilding all the indexes of a database in SQL Server - http://bit.ly/tIiho
Blog post on index fragmentation in SQL Server (Tips for all the different versions of SQL Server) - http://bit.ly/2wOTT2
Blog post on getting basic table index information in SQL Server - http://bit.ly/ntLO3
Blog post about Sync Framework - http://bit.ly/MqRDD | Blog post about ADO .NET Data Services - http://bit.ly/O8LIE
[New blog post]: Using Computed Columns in SQL Server - http://bit.ly/PGyxi
[New Blog Post]: Mysore PASS Chapter SQL Server Webcasts Series - http://bit.ly/c85gB
[New blog post]: Accessing SQL Azure Using ADO.NET - http://bit.ly/bBGHf
New Blog Post: Using the "GO" Command in SQL Server - http://tinyurl.com/ovvs92
New blog post: New Date-Related Functions in SQL Server 2008 - http://tinyurl.com/mmacns
New blog post - SQL Server 2008 Collation Alignment with Windows Server 2008: http://tinyurl.com/nphd38
New blog post - Error converting data type varchar to float: http://tinyurl.com/lgj8zv
New blog post - Windows Internal Database (SSEE): http://tinyurl.com/mjq7eu
New blog post - How to rebuild all the indexes of a database in SQL Server: http://tinyurl.com/lr87bo
New Blog post - Renaming Windows Logins in SQL Server: http://tinyurl.com/l5jysa
New blog post: Installing 32-bit SQL Server 2005 Reporting Services on a 64-bit machine/Windows OS - http://tinyurl.com/o88nbq
New blog post - "The Entity Framework - Part 2 - Inheritance": http://tinyurl.com/qs7grx
"The Entity Framework - Part 1 - Introduction" is ready! http://tinyurl.com/coz3uq
Have you ever wondered where are temporary tables stored in SQL Server? - http://bit.ly/34GaBx
Comparing DATETIME with DATETIME2 - http://bit.ly/4ySjVq
Oh well, here it is! http://bit.ly/4ys37Y - A little tip related to Maintenance Plans in SQL Server 2005!
Date Functions in #SQLServer 2008 - http://bit.ly/itRCx
New blog post - SQL Azure: A First Contact - http://bit.ly/19U98Q
Discussion: A DBA or a Database Developer? - Your comments are welcome! - http://bit.ly/16G3CI

[Other SQL Server-Related Tips, Articles and Tools]

Business intelligence made easy: #PowerPivot - http://bit.ly/XJYEh
Great article on how to create a #SQLServer Management Studio Add-in - http://bit.ly/1FmpUM
When you use MS Sync Framework, you do not have to worry about data conflicts; it is efficiently handled! And yes, it's parameterizable!
A "Local Database Cache" in Visual Studio 2008 SP1, is actually a SQL Server Compact 3.5 database.
The easiest way to build an occasionally connected application in Visual Studio 2008 SP1, is to add a new item called "Local Database Cache"
Microsoft Sync Framework 2.0 CTP2: http://bit.ly/EDkJ2 - Yummy! :)
A handy ADO .NET Data Services (v1) cheat sheet: http://bit.ly/2VTUJj
An interesting tool (on CodePlex)! - SQL Azure Explorer - http://bit.ly/1OmhR8
Snippet Editor on CodePlex: A great tool! - http://bit.ly/wV12A
Good morning! Very interesting project on codeplex - SQL Azure Migration Wizard - http://bit.ly/17k4s0
extremely useful when migrating SQL Server instances: http://tinyurl.com/648c6c
It's time to generate a huge amount of sample data for use with my #PowerPivot demos. I will use @RedGate's SQL Data Generator for this
[Blog Post] Product Review: @redgate's SQL Object Level Recovery Native - http://bit.ly/45dqL5
During today's session I also used Red Gate's (@redgate) tool "SQL Prompt 4" (http://bit.ly/8kAlW); a great tool!
Using Computed Columns in #SQLServer - http://bit.ly/PGyxi
Change Data Capture (CDC): Determines and tracks the data that has changed- How it is implemented in #SQLServer 2008: http://bit.ly/5s17UI
GROUPING SETS can be also combined with ROLLUP and CUBE operations. Isn't this cool? :)
GROUPING SETS in #SQLServer 2008 - http://bit.ly/7gm0DK - In many cases they can even be equivalent to ROLLUP or CUBE operations
[MSSQL DBA Tip] How to shrink the tempdb database in SQL Server - http://bit.ly/81bd3s
MSDN BOL on Master Data Services in SQL Server 2008 R2 Nov. CTP - http://bit.ly/2qbScq
Always keep in mind the following upgrade scenarios supported in #SQLServer 2008 - http://bit.ly/UWd3O
#SQLServer 2008 R2 November CTP available to the general public on November 11th - http://bit.ly/QMrA8
Taking #SQLServer DBs offline: ALTER DATABASE [DB_NAME] SET OFFLINE WITH ROLLBACK [IMMEDIATE | x seconds]
need to copy the DB files from an instance to another but I can't stop the source MSSQL instance. Oh well, I'll just take the DBs offline!
Useful: Deprecated Database Engine Features in SQL Server 2008 - http://bit.ly/47jrPz
So it is preferable to use sp_send_dbmail when needed to send mail from MSSQL Database Engine. For more information: http://bit.ly/2g6KtR
Comparing xp_sendmail to sp_send_dbmail: They both send mail from SQL Server but the former will be removed in a future version of MSSQL.
[MSSQL Error Handling Tip] ERROR_PROCEDURE - http://bit.ly/2Pl9JQ
By the way, it always useful to understand the SQL Server Error Message Severity Levels - It helps for debugging! - http://bit.ly/32CORI
By the way, to get a list of all the schemas in a database you can try this: USE [DatabaseName]; SELECT * FROM sys.schemas;
More on Capitalization Styles - http://bit.ly/27lJNt
Naming guidelines for when creating DB tables/columns: use Pascal Casing, avoid abbreviations, use self-explanatory names.
REVOKE just "cancels" any previously given permissions (including DENY).
Do not get confused with DENY and REVOKE. DENY is a permission which just denies access to a securable (i.e. a database table).
When you are using the REVOKE statement in SQL Server, you can either use the clause TO or FROM for specifying the grantee principal
How to transfer logins and passwords between instances of SQL Server: http://bit.ly/2KguZr - Actually I am just doing this!
Just got the error message: Unable to load DLL 'sqlceme35.dll' - To fix it just (re)install SQL Server CE 3.5 SP1! - http://bit.ly/5lhDc
How to identify your SQL Server version and edition: http://bit.ly/5lYtO
SQL Server 2008 R2 – Application and Multi-Server Management - http://tinyurl.com/qd8n7m
What's New in SQL Server 2008 R2 November CTP - http://bit.ly/1dy0S2
A great article on @mssqltips website; Scheduling a SQL Server Profiler Trace - http://bit.ly/VW3Ke

[Miscellaneous]

Want to convert Live Meeting wmv successfully? Use Windows Media Encoder! It's great! - http://tinyurl.com/g37zo
Differences between Windows XP Mode and MED-V: http://tinyurl.com/dhovnf
Windows 7 Learning Portal: http://tinyurl.com/dftebp

More tweets about SQL Server to come in 2010!

Using the OUTPUT Clause: A simple data archiving example

2009-12-15T00:29:00.017+02:00

The OUTPUT Clause in T-SQL as described in MSDN SQL Server BOL, "returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement."

The functionality provided by the OUTPUT clause is very powerful and can be used in many scenarios. One such a scenario is Data Archiving.

Consider the following example based on which there is a "CustomerData" table containing customer IDs and status codes indidicating whether a customer is Active or Inactive.

A data archiving scenario would be to archive all the customers having the status "INACTIVE".

For this example I am going to use two tables called "CustomerData" and "CustomerArchive".

OK, let's see some code!

--Creates the schema
CREATE SCHEMA Test
GO

--Creates the data table for customers
CREATE TABLE Test.CustomerData
(
ID int IDENTITY(1,1) NOT NULL,
StatusCode VARCHAR(10) NOT NULL
)
GO

--Creates the data table for customers archive
CREATE TABLE Test.CustomerArchive
(
ID int NOT NULL,
StatusCode VARCHAR(10) NOT NULL
)
GO

--Populates data table with some sample data (Using Row Constructors)
INSERT INTO Test.CustomerData (StatusCode)
VALUES    ('ACTIVE'),
        ('ACTIVE'),
        ('INACTIVE'),
        ('ACTIVE'),
        ('ACTIVE'),
        ('INACTIVE'),
        ('ACTIVE'),
        ('INACTIVE'),
        ('ACTIVE'),
        ('ACTIVE')

Now, let's check out the contents of the two tables:

CustomerData

CustomerArchive

Now, let's archive the CustomersData table using the OUTPUT clause in the below query:

--Archive inactive customers and store the records in the "CustomerArchive" table
DELETE FROM Test.CustomerData
OUTPUT DELETED.* INTO Test.CustomerArchive
WHERE StatusCode='INACTIVE'

CustomerData

CustomerArchive

As you can see from the above results, with just a single T-SQL statement, we managed to delete the desired data from the CustomerData table and also archive it into the CustomerArchive table.

The above scenario was just a simple example on how data archiving can be performed using the OUTPUT clause in SQL Server. You can build more complex data archiving logic using the OUTPUT clause as you can also use the INSERTED keyword and perform multiple joins in the query which uses the OUTPUT clause.

Note: Always be careful when deleting data! Always keep a backup of your data.

MCTS: SQL Server 2008, Database Development

2009-12-11T21:38:00.000+02:00

Today I managed to pass the Microsoft Exam 70-433 - TS: Microsoft SQL Server 2008, Database Development and thus I got my third MCTS certification on SQL Server :)

This exam was all about database development using SQL Server 2008. While studying for the exam, I had the chance to try and learn more on the various programmability features of SQL Server 2008 which I did not have the chance to heavily use it yet.

What I think, is that while it is very important studying for certification, the entire process also provides you with the opportunity of learning things which you can use when working with SQL Server and get things work with intelligent techniques.

It was a great experience, especially when I saw the "Congratulations" message on the screen of the PC at the Exam Center where I took the exam! :)

For more information on Microsoft SQL Server certifications, check out this link!

Introduction to SQL Server PowerPivot - MS Momentum 2009 Session Review

2009-12-02T23:55:00.008+02:00

On November 26th 2009, Microsoft-Cyprus launched Momentum 2009; a New Efficiency Community Launch event.

The event took place in Nicosia, Cyprus, Europe, and had at around 400 attendees. Two tracks were featured: one for Developers, and another one for IT-Pros.

I had the pleasure to participate as one of the Cyprus .NET User Group (CDNUG) speakers.

The topic of my session was “Self-Service Business Intelligence with SQL Server 2008 R2”. Actually, I talked about PowerPivot :)

Though before the deep dive into PowerPivot and the demonstration of the unlimited power it offers, I provided a short introduction to the great SQL Server 2008 R2 features along with a first demo showcasing these features.

Some of the exciting new features in SQL Server 2008 R2 (November 2009 CTP) are:

Connectivity to SQL Azure
Data-tier Application Upgrade
SQL Server Utility
Master Data Services
StreamInsight
A large number of enhancements on Reporting Services

You can find more information on the new features of SQL Server 2008 R2 Nov. CTP on this link.

After the above introduction to SQL Server 2008 R2, my session got focused on the Analysis Services set of features in SQL Server 2008 R2 and more specifically on SQL Server PowerPivot for Excel 2010.

So what exactly PowerPivot is?

PowerPivot (formerly known as “Gemini”) is an add-on for Excel 2010 / SharePoint 2010 which brings the full power of SQL Server Analysis Services right into Excel.

Its engine called “Vertipaq” uses in-memory column based compression allowing millions of rows of data to be stored, sorted and aggregated.

PowerPivot supports a wide variety of sources and provides the Business User with a rich set of mathematical functionally featuring the existing functions already in Excel and the powerful Data Analysis eXpressions (DAX).

You can access PowerPivot by clicking on the “PowerPivot” button on the Ribbon in Excel 2010:

Then, you are presented with the PowerPivot dialog:

Within the PowerPivot dialog, under the “Home” menu, you can select from a huge variety of data sources such as:

Several providers to DBMSs
Text files
Excel workbooks
Reporting services
RSS feeds

After you select the data source(s), you can import data which is imported in tabular format.
You can even import data from the clipboard with copy-paste!

Under the “Table” menu, you are able to manage tables’ properties along with managing the various relationships between the tables. You can also create new relationships:

Under the “Column” menu, you are able to manage the columns’ properties such as data types and format. Also, you can add new columns:

After importing the data, by returning under the “Home” menu and by clicking on the “PivotTable” button, you are able to create Pivot Tables based on several presets. Then PowerPivot automatically switches back to the workbook (it lets you either create new sheets or use the existing ones) where you are able to easily perform aggregations and many other mathematical calculations on the data you have previously imported.

This is a very basic introduction to PowerPivot as it is within the context of the review regarding my session on Microsoft Momentum 2009. In subsequent posts, I will thoroughly explain and demonstrate all the exciting features of PowerPivot!

Throughout my session many demos were performed including the following scenarios:

Import data from the clipboard with copy-paste
Import data from Excel workbooks
Import data from a SQL Server database containing 5+ million rows
Perform a mashing-up of data using various data sources (clipboard, excel workbooks, SQL Server database)

After importing data based on the different scenarios, aggregations were performed against the data for answering business queries.

Here are some pictures taken during my session:

PowerPoint Presentation
Please find below the PowerPoint presentation of my session at Momentum 2009:

Self-Service Business Intelligence With SQL Server 2008 R2

View more documents from artemakis.

CDNUG Presence
CDNUG presence on the event was strong! We had a special “CDNUG Area” where developers could sign-up with the User Group. Many people expressed interest on the activities of our User Group. The feeling was great! :)

Remarks
The event was definitely a huge success thanks to the great organization of Microsoft and the rich and high-quality content delivered by all the speakers. CDNUG participated by delivering three sessions on the Developers track. You can find the full review of our User Group’s participation on the CDNUG website along with a description of the entire Developers track.

The “PowerPivot Experiment”
I have to admit that I was really looking forward to test the capabilities of SQL Server PowerPivot. Thus being a SQL Server fan, in one of my tweets, I mentioned that I would not rest until I tried the real capabilities of PowerPivot :)

To this end, after the event, I tried importing 100+ Million rows from a SQL Server Database into Excel 2010 using PowerPivot. Guess what? I did it! :)

After designing a database and populating it with a total of 100+ Million rows, I imported the data from its five tables into excel. It only took 7 minutes to import the data! Also, performing aggregations on the data was very fast!

On one of my subsequent posts on PowerPivot, I will provide the schema of my database and a full description of entire process!

Make sure you check back my blog soon for newer posts on this topic!

Microsoft Momentum 2009

2009-11-17T22:50:00.002+02:00

In November 26th, Microsoft Cyprus will be hosting "Momentum 2009", this year's biggest event in Cyprus.
The event features two tracks; (i) A track for IT Professionals, and (ii) A track for Developers.

On the IT Professionals track, attendees can learn on how to increase productivity using innovations in Windows 7, Windows Server 2008 R2 and Microsoft Exchange Server 2010.

On the Developers track, attendees can learn about Windows 7 application development, IE 8 tips and tricks for developers, Self-service BI with SQL Server 2008 R2, SharePoint 2010 Developer Platform and the Microsoft Azure Cloud application platform.

Cyprus .NET User Group (CDNUG) is actively participating to this event with three of its members presenting sessions on the Developers track!

To this end, I am happy to be participating by presenting the session with topic "Self-Service Business Intelligence with SQL Server 2008 R2".

In my session I will talk about SQL Server PowerPivot for Excel 2010 (formerly codenamed “Gemini”) and show how business users can take advantage of this great feature and fulfill their analytical requirements efficiently and iteratively, without the need to be based on a predefined model. During the session, many live demonstrations will take place in order to see PowerPivot in action!

Looking forward to seeing you all at the event!

Product Review: SQL Object Level Recovery Native

2009-11-06T12:54:00.009+02:00

One of the many privileges of being a Friend of Red Gate, is that you have the chance to evaluate the company's products and provide feedback and suggestions. In this post, I provide my review for one of Red Gate's latest tools; SQL Object Level Recovery Native.

SQL Object Level Recovery Native enables you to recover individual database objects from a native SQL Server backup file. Yes, that’s right! You do not have to restore the entire database, but you can just restore individual objects you select when “browsing” the backup files using the tool.

I am sharing the experience I had with using the tool through several screenshots along with explanation of the steps involved in my test case scenario. OK, let's start!

On the first screen by clicking on the “Select backup source” hyperlink, you are presented with a screen where you can select one or more SQL Server native backup files to be included in the backup sources:

On this screen by clicking on the “Add Files” button you can select the SQL Server native backup files for choosing objects to restore from:

In my test case scenario I used the “master” and “bikes” database backup files, as you can see on the next screen. After adding the backup files you want, in the “Available Backups” table you are presented with the backups you can use and which exist within the selected native SQL Server backup files (more than one backup may exist in the backup file as you might have appended more than one within the same backup file).

At this point you have to note that you can only select a single backup at-a-time for restoring objects from. In my example, I chose the bikes backup taken on October 1, 2009:

Then, it is the most important screen of all; selecting the objects for recovery! In this example I selected the “Category” table. On the right of the screen, you are presented with schema and data (where available) previews for each object you focus on. This helps you easily understand what object you are looking for (if you do not know already! :)

So as said, I chose the “Category” table. This screen shows the schema preview for this table:

And this screen shows the data preview for the same table:

At the bottom of the above screen there are two buttons. If you click on the “View Recovery Script” button you will be provided with a generated script which creates a DDL script for the object(s) you selected for recovery and also a DML script for recovering the data contained within the object (in the case the selected object is a table).

If you click on the “Recover Objects” button, you are presented with the following screen. The screen contains the object(s) to be recovered (you can select more than one object), and the necessary fields for specifying the destination database. To this end, you have to complete the SQL Server/Instance name, the authentication method/credentials, and the destination database. In this example I am using an instance of SQL Server 2008 on my localhost named “SQL2008”, windows authentication and I chose to restore the “Category” table into the “SampleDB” database.

When clicking on the “Recover” button, the object recovery process takes place and after it finishes, you are presented with a summary of the process outcome (note: if the specific object already exists on the destination you will get a relevant error message).

OK, it is time to check within SQL Server Management Studio to see if the object exists on the database and it is operational. As you can see on the following screenshot, of course it is! :)

Remarks
I am quite sure that many times in the life of a DBA or Database Developer, specific objects were needed to be restored from a backup file. SQL Object Level Recovery Native allows recovering individual database objects without needing to restore the entire database backup.

I find this useful and cool!

The SQL Server native backup files supported by the tool are these of SQL Server 2000, 2005 and 2008.

For more information on SQL Object Level Recovery Native please visit this link.

I hope you find this review useful!

CDNUG Event Review - October 27, 2009 - Visual Studio 2010 Beta / Windows Azure Platform

2009-11-01T21:14:00.002+02:00

Another great CDNUG event took place last Tuesday, October 27, 2009 at Microsoft-Cyprus.
This event was "celebratory" as it was the first offline event after summer holidays.

Of course summer was not an idle time. It was rather a great period of online events, and more specifically Live Webcasts!

Among other, in July 29, I delivered a talk on the new Programmability features in SQL Server 2008 and more specifically about the T-SQL Enhancements (Review).

Additionally I had the pleasure to participate to the 24 Hours of PASS, and MY-TG SQL Server Community Series as a speaker, delivering Live Webcasts on ADO .NET Data Services (Review) and Sync Framework respectively (Review) and thus representing Cyprus .NET User Group (CDNUG)!

Due to the nature of this event, we had two great sessions delivered by two great speakers! In the first session, Karl Davies-Barrett, Developer Platform Evangelist at Microsoft Malta & Cyprus and great friend of CDNUG, talked about Microsoft Visual Studio 2010 Beta 2 and demonstrated many of its great new features. In the second session, Luka Debeljak, CEE DPE Regional Technical Lead at Microsoft Corporation, introduced to us the Microsoft Windows Azure Platform from a developer’s perspective.

Microsoft Visual Studio 2010 provides the .NET developer with a huge variety of new enhancements in areas such as: Application Lifecycle Management, Developer Experience, Cloud Development, Web Development and last but not least, Parallel Programming.

In his session, Karl talked about all these features and with the use of some really interesting demos, showed us how the developer can take advantage of these features and thus, have a fantastic development experience!

Right after Karl's session, we moved on with the introduction to the Windows Azure Platform by Luka!

Windows Azure is a new, cloud services Microsoft Operating System that serves as the development, service hosting and service management environment for the Windows Azure platform. The Windows Azure platform offers an intuitive, reliable and powerful platform for the creation of web applications and services. The Windows Azure platform is comprised of Windows Azure: an operating system as a service; SQL Azure: a fully relational database in the cloud; and .NET Services: consumable web-based services that provide both secure connectivity and federated access control for applications.

Luka talked about the Microsoft Windows Azure Platform and also provided information regarding all the services currently offered for hosting/management on the platform. To this end, we were presented many live demos of SQL Azure and .NET Services. Luka also demonstrated how we can develop .NET applications for hosting on the Windows Azure Platform using the Windows Azure tools for Visual Studio. The development environment for the demos was Microsoft Visual 2010 something which proved that it was a really great idea hosting these two sessions within a single event!

A BIG thank you to Karl Davies-Barrett and Luka Debeljak for delivering these two really interesting sessions to our User Group. Thanks for supporting CDNUG!

For event pics and other information please visit CDNUG website!

Upcoming CDNUG Event, October 27, 2009 - Visual Studio 2010 Beta / Windows Azure Platform

2009-10-19T00:14:00.008+03:00

Time for another Cyprus .NET User Group (CDNUG) offline event!

This is the first offline event right after the summer holidays and we decided to make it special; it features two great speakers and two great topics! To this end we have the pleasure to host two hourly sessions on two very interesting technologies.

In the first session, Karl Davies-Barrett, Developer Platform Evangelist at Microsoft Malta & Cyprus and great friend of CDNUG, will talk about Microsoft Visual Studio 2010 Beta and will demonstrate all of its great new features.

In the second session, Luka Debeljak, CEE DPE Regional Technical Lead at Microsoft Corporation, will introduce to us the Microsoft Windows Azure Platform from a developer’s perspective.

See below for event-specific details:

------------------------------------------------------
Topic 1: Introducing Visual Studio 2010 Beta
Speaker: Karl Davies-Barrett (Developer Platform Evangelist, Microsoft Malta & Cyprus)

Topic 2: An introduction to the Windows Azure Platform
Speaker: Luka Debeljak (CEE DPE Regional Technical Lead, Microsoft Corporation)

When: Tuesday, October 27, 2009.
Time: 19:00 (GMT + 2) - Cyprus Local Time
Duration: 120 minutes
Where: Microsoft Office (11 Limassol Avenue, Nicosia)

Registration: Click on this link.
For more details check our website at http://www.cdnug.net
------------------------------------------------------

If it happens and you are in Cyprus, you are more than welcome to attend this special event!!!

Saving maintenance plan failed

2009-10-11T23:44:00.008+03:00

If you encounter the above error message when trying to save a maintenance plan you created using the the wizard in SQL Server 2005 Management Studio (SSMS), the first (obvious) thing you should try, is to check if the latest service pack is installed and if it is not then install it!

You can check out one of my past posts on how to find out which service pack is currently installed on your SQL Server 2005 instance and on how to get the latest service pack.

However, if you are still experiencing the same problem, even in the case you have installed the latest service pack, then you can try running the sql script called "sysdbupg.sql" which can be found in the SQL Server installation directory under the subfolder "install".

This script upgrades the system database stored procedures from the RTM level. Databases which may be upgraded are:

master
model
msdb

Make sure before trying to execute the above script, to backup the aforementioned system databases!

A big thanks to my good friend Ioannis for bringing this hint to my attention!

MY-TG SQL Server Community Series‏ - Session Review (Sync Framework)

2009-10-01T22:01:00.016+03:00

On September 19, 2009, I delivered a session with topic the Microsoft Sync Framework, during the Mysore SQL Server Community Series. The exact topic was: “Flexible data synchronization with Microsoft Sync Framework”.

Further below I provide a short review for my session and I also provide the PowerPoint presentation along with the source code (C#) of my demos.

The problem domain
In the modern age of Information Technology many businesses have operations demanding a large number of their employees to work outside the office in remote locations but still being able to access corporate data. To this end, the ability to support mobile and remote workers is becoming more and more important for organizations every day.

It is very critical that organizations ensure users have access to the same information they have when they are in the office. Usually these workers have laptops, smartphones or PDAs. From these devices, users need to have access to their organization’s data which is usually stored in a central Database Server.

The old approach
The traditional way based on which mobile and remote workers access information is usually by using a VPN connection or a similar technique. Of course, in the case the connection is broken or it is not available, then there is also a disruption to the operation the worker performs. Someone may argue with that and say that there is always a way of establishing a network connection to the company. That would be great but common practice proves the opposite. It is not rare at all for communications to face problems thus leaving remote workers without access to their organization’s data. Even a short disruption to an established connection between a remote/mobile worker and the organization’s central Database can cause data integrity problems.

Introducing Microsoft Sync Framework
Microsoft Sync Framework offers a solution to the problem of supporting mobile and remote workers to continue to access their data, even in the absence of a direct connection to the corporate database. To this end, Occasionally Connected Applications (OCAs) are introduced. An OCA stores the data is on the user’s device. Data is usually stored in a SQL Server Compact Edition database. This technology, allows remote workers to continue accessing their data regardless the case where a network connection is present or not.

In order to populate the user’s local database, an OCA will need to include some Data Synchronization capabilities. Data Synchronization consists of the ability to periodically take information that is stored in the client database (such as SQL Server Compact) and synchronize changes with a server database (such as SQL Server).

The main advantage of a synchronization-based solution is that users are not longer required to have a constant network connection to corporate Database. In addition to this, the speed on which users can access the data is not any more relied on the network speed but only on the local device’s processing speed which of course, is much higher than any network speed.

Why use the Microsoft Sync Framework?
Someone might ask; “Why use a framework for this purpose?”

The answer to this question is relatively easy. First of all, synchronization is a difficult task. It introduces the not so trivial tasks of data conflicts detection and resolution when data is communicated from the corporate Database to the local data store on the user’s device and vice versa. Also when data synchronization is used, interruptions and restarts have to be handled in an efficient way. Furthermore, deleted items and synchronization loops are need to be managed as well.

Microsoft Sync Framework takes care of all the data synchronization issues and among many other enhancements, it allows the developer to easily parameterize an OCA implementation in order to control how data conflicts are detected and resolved.

Sync Framework Runtime and Main Components
Sync Framework introduces two basic components:

Synchronization providers (source & destination)
Synchronization session

The source and destination synchronization providers determine the source replica and the destination replica.

The synchronization session is the component which actually controls and coordinates the entire data synchronization process between the two providers.

It is important to note that Sync Framework not only allows performing data synchronization between databases, but also allows synchronizing files/folders as well as RSS and ATOM feeds.

The supplied synchronization providers are:

Sync Services for ADO.NET
Sync Services for File Systems
Sync Services for FeedSync (RSS & ATOM feeds)

You can also author your own provider, but it is recommended to use the supplied providers whenever possible thus reusing tested code and easier implementing your data synchronization solution.

The following figure illustrates the Runtime of Sync Framework:

Explanation of the Runtime components:

Data Source: The location that stores all the information that needs to be synchronized. It can be a relational database, a file system, a Web Service, or even a custom data source.
Metadata: Information about the data store and the objects within that data store with respect to state and change information. It can be stored anywhere. For making it easy for the developer, Microsoft Sync Framework offers a complete implementation of a metadata store.
Sync Service Provider: A synchronization provider for ADO.NET, File Systems, FeedSync (RSS & ATOM feeds) or even a custom provider.
Sync Session: Creates a link from the source to the provider and controls the entire synchronization flow.

Synchronization Flow
The synchronization flow is the following:

Sync Session Initiated with Destination
Destination Prepares and Sends Knowledge
Destination Knowledge used to Determine Changes to be sent
Change Versions and Source Knowledge sent to Destination
Local Version Retrieved for Change Items and Compared against Source Version and Knowledge
Conflicts are Detected and Resolved or Deferred
Destination Requests Item Data from Source
Source Prepares and Sends Item Data
Items are applied at Destination

For more information regarding the Sync Framework Runtime please visit the following MSDN Link.

Synchronization Scenarios
There are many synchronization scenarios which can be easily implemented using the MS Sync Framework. To this end you can do the following:

Synchronize Databases
Synchronize two file system locations (i.e. Multi-master file sync between multiple PCs, Synchronization between PCs using a USB Drive, Taking a network share offline, Maintaining a backup copy of files)
Synchronize Web Feeds (RSS and ATOM)

You can also implement many other scenarios as the Sync Framework is very extensible and allows you to develop and use your own components such as: Synchronization Providers, metadata stores, custom data stores, etc.

PowerPoint Presentation
Please find below my PowerPoint presentation for this session.

Flexible Data Synchronization with Microsoft Sync Framework

View more documents from artemakis.

Source Code for Demos
Please find below the source code for my demos.

Development Environment/Prerequisites:

Visual Studio 2008 SP1
.NET Framework 3.5 SP1
SQL Server 2008 SP1 (used for hosting the Central Database)
SQL Server Compact 3.5 SP1 (used for hosting the client’s local data store)
Sync Framework 2.0 CTP2

Demo 1: Creating a Sync Ecosystem from Scratch (Databases)
Summary: In this demo, my source replica is a SQL Server 2008 database called “SyncServerDB” which represents the Central Database. The destination replica is a Windows Forms Application (role: client) developed in Visual Studio 2008 SP1 which used SQL Server 3.5 Compact Edition SP1 for hosting the local data store. The Windows Forms Application allows the user to synchronize the client with the central database via a GUI form.

Download link for source database.
Download link for demo 1 source code.

* Before using the code, you have to reinitialize it. You can follow the instructions in this document for doing this.

Demo 2: Synchronizing Files
Summary: In this demo I used the Sync Framework for synchronizing the contents of two folders on my local hard drive in a bidirectional manner.

Details regarding the folders: Make sure you create two folders called “source” and “dest” in the “Debug” folder for this Visual Studio project. Also before running the executable project, make sure you place a sample file in one of the folders in order to see after the program’s execution that the two folders were successfully synchronized.

Download link for demo 2 source code.

* Disclaimer: The source code for the demos is intended to be used only for demo purposes. Do not use it for Production systems as it is simplified for demo purposes.

Summary
This session gave me the chance to use again the Microsoft Sync Framework. All I can say is that it was a great experience. I am actually thinking of implementing some tools using the Framework and place it to CodePlex. I will be posting updates to my blog regarding this as well.

Summing up this review, I would like to say that by using the MS Sync Framework it is very easy to implement Data Synchronization for a large variety of scenarios. With all the built-in functionality you are enabled to do more with less! Additionally, you can create your own custom components and cover even more synchronization scenarios.

Sync Framework enables the developer to easily built Occasionally Connected Systems and Sync Ecosystems by providing among other efficient conflict handling and a flexible Synchronization Algorithm. Furthermore, Sync Framework Supplied Providers can be widely used for different scenarios.

I see the future regarding Sync Framework as just great! In plain words: Sync Services on the Cloud! The project codename "Huron" enables a Sync Enabled Cloud Data Hub! Find out more on the following link.

I would like to thank the Mysore SQL Server PASS Chapter committee for inviting me to present to this great SQL Server community series! It was a pleasure to present and discuss about SQL Server and its related .NET technologies.

Drop me a line if you have any comments!

See you at another event soon!

24 Hours of PASS – Session Review (18 - ADO .NET Data Services)

2009-10-01T18:11:00.017+03:00

On September 2, 2009, I had the pleasure to deliver a session on ADO .NET Data Services, during that great SQL Server Marathon; 24 Hours of PASS! This great event was a series of 24 non-stop live, free webcasts dedicated to pure SQL Server knowledge. The exact topic of my session was: “Building Flexible Data Services for the Web Using the ADO .NET Data Services Framework”. Yes, it is true; it was the longest titled session of the event :)

Further below I provide a short review for my session and I also provide the PowerPoint presentation along with the DB scripts and source code (C#) of my demos.

The problem domain
Many applications, and especially web applications, face a set of problems in today’s environments. For example, it is difficult to identify the different types of clients and users accessing these web applications. Each different client has its own particularities which make it difficult, if not impossible, for web applications to perfectly work with all these types of clients. This requires different web methods for each client, different paging and filtering techniques and so on.

Furthermore, during the last few years, the wide usage of the Internet enabled the massive spreading of Web Applications. To this end, a new form of web applications has emerged; the well known “Connected” applications. This new type of web applications includes Rich AJAX web applications, Flash & Silverlight applications, Mashups, etc. These applications need continuous feeding of data in dedicated data channels in order to be fast and efficient. They somehow require a separation of the data and presentation layers.

The old approach
The traditional way for enabling web applications to efficiently work with the different types of clients was to include additional logic in their code in order to cover most of the different scenarios. Though, this is not anymore an efficient way for handling the uniformity of clients which access these applications as the different types of clients are highly increased.

How about using a framework that helps you develop and access data services in a uniform way?
Such case could be really interesting…

Introducing the Microsoft ADO .NET Data Services Framework
The ADO.NET Data Services framework consists of a combination of patterns and libraries which enable the creation and consumption of data services for the web. Using the framework, it is easy to create flexible data services that are naturally integrated with the web and maintain the REST style. To this end, URIs are used to point to pieces of data and well-known formats such as JSON and XML are used for representing that data.

Interacting with your data is now uniform and easy as by using the HTTP verbs GET, PUT, POST and DELETE, you can perform any operation you like on your data.

The mappings between the above HTTP verbs and the SQL language are:

If you use the web, and specifically HTTP to provide and consume your data, you will find ADO.NET Data Services very interesting as by utilizing the enormous amounts of infrastructure that already exists to support HTTP due to the wide spreading of the Internet, you are able to easily build flexible and powerful data services fully supporting “Connected” applications which is nowadays the trend in web applications design.

The following figure illustrates the different types of clients that access data through HTTP:

You can see that there is a plethora of different clients accessing the same data through HTTP.

Why Use the ADO .NET Data Services Framework?
The answer to this question is quite simple; as “Connected” applications (Rich AJAX web applications, Flash & Silverlight applications, Mashups, etc.) are the trending type of web applications, they need the corresponding data services in order to feed them with data, without affecting their presentation layer. ADO .NET Data Services allows these applications to have a dedicated data channel which allows it to be fed with data by an ADO .NET Data Service. Usually the only line of sight from these applications, is HTTP, so using the framework makes things really simple.

With this technology, the data is moved out-of-band with the presentation and behavior information thus allowing the web application on the one hand not have any delays on the presentation layer, and on the other hand to have continuous access to its data source.

The architecture of an ADO .NET Data Service
The following figure illustrates the architecture of a data service:

At the very bottom of this architecture there are the relational and non-relational sources.
Next there is the ADO .NET Entity Framework which is used for handling the relational sources and a custom LINQ provider for handling the non-relational sources. The Data Services Runtime exists as a DLL. Next, there is the Hosting/HTTP Listener for which the out of the box implantation is WCF hosted within an ASP .NET web page (though, you can author your own listener if you like). On the very top of this architecture, there is the HTTP stack, against which you can issue HTTP commands (using the abovementioned HTTP verbs) in order to access and process your data.

Consuming a Data Service
The following figure illustrates the various ways available for consuming a data service implemented within the ADO .NET Data Services framework:

As you can see, actually an HTTP stack is enough! Of course, if you like to access your data service using higher levels of abstractions you can use JSON & AtomPub clients and even easier; Windows Data Services Clients such as .NET Framework, Silverlight 2 or 3, AJAX, and so on.

Securing a Data Service
There are several ways available for securing a Data Service within the ADO .NET Data Services Framework.

With respect to Visibility, you can use:

Per-container visibility control
Read, Query and Write options

With respect to Authentication you can use:

Integration with hosting environment
ASP.NET, WCF or custom authentication schemes

With respect to Interceptors you can use:

Execution before HTTP GET/PUT/POST/DELETE
Enabling of validation and custom row-level security policies in order to maintain the REST style

With respect to Service Operators you can use:

Custom entry points, e.g. /MyTags?startDate='2009-09-02'
Composable, follow URI scheme

PowerPoint Presentation
Please find below my PowerPoint presentation for this session.

Building Flexible Data Services for the Web Using the ADO .NET Data Services Framework

View more documents from artemakis.

Source Code for Demos
Please find below the source code for my demos.

Development Environment/Prerequisites:
• Visual Studio 2008 SP1
• .NET Framework 3.5 SP1
• SQL Server 2008 SP1 (used as the relational data source)
• ADO.NET Data Services v1.5 CTP1

* For all the demos, you will have to modify the connection string in the Web.config file, connection string entry in order to point to your SQL Server 2008 instance. To this end, please replace ".\sql2008" with your SQL Server 2008 instance name. Also I used Windows Authentication (trusted connection) for connecting to the SQL Server Instance.

Download link for source database.

Demo 1: Examining an ADO .NET Data Service
Summary: In this demo, we examine a pre-built data service and see how we can interact with it using a Web Browser and an HTTP debugging tool.

Download link for demo 1 (and 2) source code.

Demo 2: Building a Data Service from Scratch
Summary: In this demo I build a data service from scratch using Visual Studio 2008 SP1.
Procedure: There is not sample code for this demo, as by completing the demo, you end up to a source code identical to the source code of demo 1.

Demo 3: Consuming a Data Service
Summary: In this demo, in addition to the data service which was build during demo 2, I also create a .NET client (Windows Console Application in C#) within the same project for consuming the service using LINQ to Data Services (this is actually LINQ to Entities).

Download link for demo 3 source code.

* Disclaimer: The source code for the demos is intended to be used only for demo purposes. Do not use it for Production systems as it is simplified for demo purposes.

Summary
Summing up this review, I would like to say that using ADO.NET Data Services makes it really easy to expose your data over HTTP and thus build powerful and flexible data services.

Using URIs (entities and associations --> resources and links), HTTP Verbs for data manipulation, and the well-known and simple formats JSON and AtomPub for data representation makes it easy to build RESTful data services with all the benefits derived by using the REST style.

ADO .NET Data Services are very useful for developing AJAX & Silverlight Applications, Online Data Services, Mashups and much more.

Of course, I could not end this review without mentioning the Cloud. Many of the Microsoft cloud services expose data using the same REST conventions as used by ADO .NET Data Services. This enables using the ADO .NET Data Services Framework not only for on premises services, but also with services hosted on the cloud!

This session’s topic falls under one of my favorite Microsoft Technologies; ADO .NET - Data Access! I plan to deliver even more webcasts in the future, again dedicated to ADO .NET.

I would like to thank PASS for inviting me to actively participate to this great SQL Server event and congratulate all the people who made this event a smashing success!

Drop me a line if you have any comments!

Using Computed Columns in SQL Server

2009-09-20T14:33:00.011+03:00

When it comes to database design in SQL Server, it is always recommended to create the database as flexible as possible. SQL Server provides several features for developers in order to be able to do so.

An example of such a feature are the "computed" and "persisted" columns.
As explained on MSDN BOL, computed columns' values are computed by expressions that can use other columns in the same table.

Also, when using the PERSISTED keyword with a COMPUTED column, the data of the computed column is physically stored in the table.

Let's see some code and try out this great feature!

First let's create two tables representing product information:

--Note the column with name "totalPrice": It is a computed column which computes
--the total price (price + VAT)
CREATE TABLE Product1
(
id int, code varchar(50),
price float,
vatPerc float,
totalPrice as (price+(price*(vatPerc/100)))
)
GO

--Note again the column with name "totalPrice": It is the same computed column as in table
--Product 1 with the difference that it uses the PERSISTED keyword.
CREATE TABLE Product2
(
id int,
code varchar(50),
price float,
vatPerc float,
totalPrice as (price+(price*(vatPerc/100))) PERSISTED
)
GO

------------ Testing Table Product1 --------------
INSERT INTO Product1 (id,code,price,vatPerc)
SELECT 1, 'PRODUCT01',100,15
UNION ALL
SELECT 2, 'PRODUCT02',200,15
GO

* I know, I could use a Table Value Constructor (Row Constructors) but I would prefer that my examples to be compatible with all versions of SQL Server as Table Value Constructors is one of the new programmability features in SQL Server 2008 :)

Note that in the above INSERT statement I am not inserting any values for the "totalPrice" column. Though, when taking a look at the generated results, the totalPrice values are calculated on the fly!

SELECT *
FROM Product1
GO

Results
-------

Similarly, testing table Product2 will work the same way with the only difference that this time,
the calculated values for the "totalPrice" column will be physically stored in the table:

------------ Testing Table Product2 --------------
INSERT INTO Product2 (id,code,price,vatPerc)
SELECT 1, 'PRODUCT03',300,15
UNION ALL
SELECT 2, 'PRODUCT04',400,15
GO

SELECT *
FROM Product2
GO

Results
-------

* Note: By the time you are using a Computed column you cannot explicitly insert data into it. The data is computed based on the computed column's definition (expression).

Friends of Red Gate

2009-09-19T23:26:00.006+03:00

Red Gate is a software company based in the university town of Cambridge, UK, specializing in MS SQL Server, .NET and email archiving tools.

Red Gate is one of the strong supporters of the SQL Server, .NET and Exchange communities. A great example of this, are Red Gate's well-known community web sites www.simple-talk.com and www.sqlservercentral.com.

Red Gate, takes their relationship with the SQL Server, .NET and Exchange communities very seriously. A solid example of this, is the "Friends of Red Gate" program. The program is aimed at active and influential community members who are experts and gurus in their relevant fields.

Through the program, the members have the chance to use and review Red Gate's tools thus being able helping others who are having problems which could be solved using these tools.

As a new member of the program, I am very excited to participate as I am given the chance to use these great tools, review them and provide my feedback! I am already using SQL Prompt 4 and SQLRefactor and loving it!

Subsequent posts will follow, reviewing and discussing Red Gate's tools mainly having to do with SQL Server and .NET Development.

A big thanks to Red Gate for being a true supporter of the technical community!

Mysore PASS Chapter SQL Server Webcasts Series

2009-09-13T18:46:00.006+03:00

Newsflash: More free SQL Server training is on the way!

The Mysore/Mandya Technology Group (MY-TG) organizes a series of SQL Server Webcasts which started on Saturday, September 12th, 2009 and will be taking place every Saturday for the next few weeks. To this end, every Saturday, two sessions are presented.

Topics include SQL Server 2008 Administration, Development and Data Access. This is another great opportunity to learn more about trending SQL Server topics.

I have the pleasure to participate as a speaker to this event on Saturday, September 19th, 2009, 12:30 - 13:30 (UTC) where I will be presenting the session with topic: "Flexible data synchronization with Microsoft Sync Framework".

In this session, among other, we will talk about the fundamentals of Data Synchronization and how we can easily build from scratch a flexible Sync Ecosystem using Microsoft Sync Framework 2.0 CTP2, thus allowing the smooth operation of an Occasionally Connected System.

Click here to register for this event and for more details please visit http://positivekingdom.info.

See you online!

Accessing SQL Azure Using ADO.NET

2009-09-08T23:51:00.010+03:00

In my first post on SQL Azure (SQL Azure: A First Contact) I talked about how you can access SQL Azure using SQL Server 2008 Management Studio (SSMS).

In this post, the journey continues with “walking” on the cloud using ADO .NET :)

Here’s the recipe!

Ingredients

A valid SQL Azure CTP account
Visual Studio 2008 SP1
.NET Framework 3.5 SP1
C# (or respective VB.NET) namespace: System.Data.SqlClient

Objects used from System.Data.SqlClient namespace:

SqlConnection
SqlCommand
SqlDataReader
SqlDataAdapter

Process

Step 1: Add namespace for using SqlClient
Step 2: Set up connection to database
Step 3: Open database connection
Step 4: Set the T-SQL command(s) - Use the "SqlCommand" object
Step 5: Open the SqlDataReader - Execute the T-SQL command(s)
Step 6: Display the results (if any)
Step 7: Close the SqlDataReader
Step 8: Close the database connection

Source Code (for obvious reasons I removed the connection string from the source code file)

You can find the sample source code for this post here.
*Note: This sample code is for demo purposes and does not represent production code.

Considerations

My example is based on the example in my previous post on SQL Azure (Database Name: sqlazure, table name: CLOUD_MSGS)
For this post I used a new Visual C# “Console Application” project named ADO .NET – SQL Azure like in the following screenshot:

Screenshot of the output

Have fun!

Drop me a comment if you need any help implementing the ADO.NET connection to SQL Azure!

SQL Azure: A First Contact

2009-09-04T10:43:00.017+03:00

SQL Azure… Yep, that is SQL Server on the Cloud!

A few days ago I received my SQL Azure (CTP) invitation code. The first thing I naturally did right after this was to create and access a database on the Cloud! I was very curious to see how I could interact with a database hosted on the cloud.

After getting access to the SQL Azure Service (you can register for the CTP here), you can easily create a database by clicking on the “Create Database” button.

The default database is the well-known “master” database.
I called the database I created “sqlazure”.

So, an empty database is created, what next? One of the options is to connect to the database using SQL Server 2008 Management Studio (SSMS)!

I will provide the procedure I followed (with screenshots) in steps:

Step 1
Select the database hosted on the cloud in SQL Azure and click on the “Connection Strings” button:

When clicking on that button you will get three connection strings for the following:

ADO .NET
ODBC
OLE DB

In all the connection strings, you can find the same server value in the form of Server=tcp:[full_server_name]. Note down the full server name or just copy it to your Windows clipboard.

Step 2
Start SQL Server 2008 Management Studio (SSMS).
On the connection dialog that appears when starting SSMS, click on the “Cancel” button.

Step 3
In SSMS click on “New Query”. The connection dialog appears again.

On the connection dialog enter the following information:

Server Name: The full server name as provided by the SQL Azure connection string.
Authentication: SQL Server Authentication.
Login: The SQL Azure server admin username.
Password: The SQL Azure server admin password.

Also, before clicking the “Connect” button, click on “Options” and under the “Connection Properties” tab, in the “Connect to database” field, enter the database name which is hosted on the cloud and you want to access*.

* In this case you have to note that if you do not specify the database in the connection properties, you will be automatically connected to the “master” database. Though, once connected to a database on SQL Azure, it is not yet possible to use the “USE” command for switching to another database. The only way for switching to another database, is to establish a new connection on that database.

OK, after selecting the database on which you want to connect (in this example sqlazure) click on the “Connect” button.

By the way, you will get an error message about ANSI_NULLS but you can ignore this and continue.

That’s it! You are now connected to your database hosted on the cloud and you can issue queries using SSMS query window!

Step 4
Finally, let’s run some queries against our database:

-- Create a table
CREATE TABLE CLOUD_MSGS(
msgID int,
msgDescr varchar(100)
)
GO

-- Create unique clustered index
CREATE UNIQUE CLUSTERED INDEX msgID_ind
ON CLOUD_MSGS (msgID)
GO

-- Populate table with data ... using Row Constructors! :)
INSERT INTO CLOUD_MSGS(msgID,msgDescr)
VALUES (1,'Welcome to SQL Azure'), (2,'Hello World!'), (3,'SQL is on the Cloud!')
GO

-- Display the contents of the "CLOUD_MSGS" table
SELECT *
FROM CLOUD_MSGS

Resources

Below you can find some useful resources regarding SQL Azure. Note that this is pre-release documentation and is subject to change in future releases:

A DBA or a Database Developer?

2009-08-25T22:16:00.011+03:00

I often think if the above two roles can be completely separated. From my point of view I would say that this is not 100% possible.

Of course, DBAs are not developers and developers are not DBAs.

The DBA is responsible for the environmental aspects of a DBMS like the databases integrity, backup, security, installation of new DBMS software, etc.

The developer is the person who designs databases and other related database objects. Is the person who writes application code against DBMSs using T-SQL and other data access technologies.

There are many cases where the developer cannot perform the tasks the DBA can. Examples of such tasks are: Implementing High Availability solutions, DBMS security and so on.

Also, there are cases where the DBA cannot perform tasks that the developer can. Such tasks can be: Implementing T-SQL queries and other database objects (i.e. stored procedures and functions) that access and modify data.

Though, as the below figure graphically illustrates, there is an area of responsibility which is common for all the DBMS-related professionals in terms of common tasks or tasks which require the interaction of both DBMS professionals:

As an example, consider the task of Performance Tuning. In this case the DBA will monitor the DBMS instance for identifying bottlenecks on the memory, disk, CPU and network subsystems which are might caused by the operation of the databases under that DBMS instance. If the DBA discover a bottleneck on the pre-mentioned subsystems then is able to proceed with optimizing the DBMS by upgrading the subsystems causing bottlenecks (i.e. add more RAM) or addressing the issue relatively independently.

Though, doing only this is not really enough for performing an efficient and comprehensive performance tuning. Imagine the case where the DBAs runs a trace and finds that a specific database query which exists in a stored procedure and manipulates data has slow performance and needs to be rewritten. In this case the DBA will discuss this with the designer (developer) of the stored procedure in order for the latter to perform the required analysis and then redesign/optimize the stored procedure accordingly.

The above example indicates that performance tuning is one of the DBMS tasks which require the cooperation of the DBA and the Database Developer in some of its aspects.

Another example within the above context is writing T-SQL queries. Sure, the DBA writes T-SQL queries for administrating the DBMS instance whereas the developer writes T-SQL queries for manipulating data but still, in many cases they both write similar code. So under this context, the DBA should be capable of writing T-SQL queries with ease, not for manipulating data within the databases (this is the developer’s job) but for performing automated, administrative tasks. An example for performing automated tasks in a DBMS is to design and implement a stored procedure which can be executed on a scheduled-time basis.

Lately, Business Intelligence (BI) became very popular. To this end, now there is another DBMS role; the BI Developer.

The BI developer is the person who works with business intelligence technologies, in the case of SQL Server, like Reporting Services, Analysis Services, and Integration Services. Is the person who analyzes data in large Data Warehouses and transforms row data into knowledge.

Yet again, as the below figure graphically illustrates, there are still common tasks among the DBA, Database and BI Developer roles which require the interaction of all roles:

Like in the first example, a comprehensive performance tuning would require all three DBMS professionals working together in some of its aspects. Also, all three professionals must be capable of “reading” and writing T-SQL queries. Additionally, all three roles should work together in the case of data archiving and so on.

My conclusion is that even though each DBMS role has its own, genuine areas of responsibility where only professionals under that specific role can perform the related tasks, there will always be some tasks which will need the cooperation of all three (or even more) DBMS roles.

Of course, if a person has expertise on all the aspects of a DBMS operation (Administration, Development, Business Intelligence, etc.), this would be the best case scenario! :)

Using the "GO" Command in SQL Server

2009-08-11T23:56:00.005+03:00

This post discusses the "GO" command in SQL Server.
Before proceeding with explaining GO, it is first necessary to understand what a "Batch" is.

As described in MSDN BOL, a Batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into an execution plan.

GO is a command used for signaling the end of a batch. Note that it is not a T-SQL statement.
This command is recognized by the following SQL Server utilities:

sqlcmd
osql
SQL Server Management Studio Code editor

Using GO, you can specify multiple batches within your T-SQL code.
So, for an example, instead of having different files containing T-SQL code and separately executing each of it, you can just use a single file/code and separate the batches using GO. This is useful in many cases.

An example of some T-SQL code (in SQL Server Management Studio Code Editor) featuring batches is the following:

------------ Example 1------------
--BATCH 1
USE [AdventureWorks]
GO

--BATCH 2
DECLARE @STRING1 AS VARCHAR(50)
SET @STRING1='BATCH 2'
SELECT @STRING1 AS RESULT
GO

--BATCH 3
DECLARE @STRING2 AS VARCHAR(50)
SET @STRING2='BATCH 3'
SELECT @STRING2 AS RESULT
GO
---------------------------------

The above T-SQL code features three batches.
The first batch instructs SQL Server Database Engine to use the "AdventureWorks" database.
The second batch declares a varchar variable, sets a value for it, and prints its value, and the third one does exactly the same with another variable.

So, in this example, the SQL Server Management Studio Code Editor will separately send the three bathes to SQL Server Database Engine for processing.

Note that by the time there are no dependencies between the batches, the above code is executed without any problems.

Now, consider the following code:

------------ Example 2------------
--BATCH 1
USE [AdventureWorks]
GO

--BATCH 2
DECLARE @STRING1 AS VARCHAR(50)
DECLARE @STRING2 AS VARCHAR(50)

SET @STRING1='BATCH 2'
SET @STRING2='BATCH 3'

SELECT @STRING1 AS RESULT
GO

--BATCH 3
SELECT @STRING2 AS RESULT
GO
---------------------------------

Again, the above code features three batches. Though you can see that the variable @STRING2 is declared in Batch 2 but the SELECT statement from within Batch 3 also tries to access it for printing its value. Though, by the time @STRING2 is defined in Batch 2, consequently it will be only available for T-SQL Statements belonging to the scope of Batch 2.

Any other T-SQL statement trying to access it will result to a runtime error because @STRING2 is only visible within Batch 2.

Based on the above example, if you try to execute the entire T-SQL code you will get the following error message:

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@STRING2".

GO is very useful in SQL Server. Though, you need to use it only when is really needed.
So, you need to keep in mind that along with defining a batch using the GO command, you define the scope of that specific piece of T-SQL code.

24 Hours of PASS!

2009-08-03T23:06:00.003+03:00

I got asked to present a one-hour SQL Server development session during the "24 Hours of PASS" event which starts on September 2, 2009, 00:00 GMT.

The "24 Hours of PASS" is a series of live one-hour webcasts broadcast over a 24 hour period that focus on various SQL Server topics.

The event is free and it is an excellent opportunity for learning about Data Warehousing, SQLCLR, SQL Server security, Performance Tuning, SQL Server Development and much more!

You can check out all all the great sessions at the following link.

I will be presenting Session 18, with topic: "Building Flexible Data Services for the Web Using the ADO .NET Data Services Framework". For more info click on this link.

During my session, you can learn how to easily build flexible and scalable data services that are naturally integrated with the Web, by using the ADO .NET Data Services Framework. Also, you can see how to easily build a data service from scratch, map it to a SQL Server database, and expose database data to the Web. Learn how to use URIs and HTTP verbs such as GET, POST, PUT, and DELETE for accessing and manipulating specific pieces of the data. Also, see how Data Services fully support the REST protocol by using well-known formats for representing data, such as JSON and plain XML.

See you all online!

Screencast: Object Dependencies in SQL Server 2008

2009-08-03T01:03:00.002+03:00

SQL Server 2008 introduces among other new features and enhancements for accurately tracking Object Dependencies.

This is very useful especially in cases where the user wishes to programmatically modify database objects having dependencies and therefore, he/she needs to resolve these dependencies before being able to proceed with the database object modification.

For efficiently tracking dependencies in SQL Server 2008, a new System View (sys.sql_expression_dependencies) and two Dynamic Management Functions (DMFs - sys.dm_sql_referenced_entities, sys.dm_sql_referencing_entities) are introduced.

For more information you can watch the Screencast below!

Webcast on SQL Server 2008 T-SQL Enhancements Now Publicly Available!

2009-08-01T14:13:00.010+03:00

A few days ago I delivered a Live Webcast on Microsoft SQL Server 2008 Programmability and more specifically on T-SQL Enhancements.

During the Webcast I talked about the following SQL Server 2008 Programmability features:

Variables Declaration and Initialization
Compound Assignment Operators
Large UDTs
DATE, TIME, DATETIME2
New & Enhanced Functions supporting DATETIME2 Precision
Table Value Constructors (Row Constructors)
Table Types and Table-Valued Parameters (TVPs)
The MERGE Statement
Grouping Sets
Object Dependencies
HIERARCHYID Data Type
DDL Trigger Enhancements
Collation Alignment with Windows

You are now able to watch this Webcast here!

New Date-Related Functions in SQL Server 2008

2009-07-30T16:09:00.006+03:00

Along with the new data types DATE, TIME and DATETIME2 in SQL Server 2008, new and enhanced functions are also included.

These functions are:

SYSDATETIME
SYSUTCDATETIME
SYSDATETIMEOFFSET
SWITCHOFFSET
TODATETIMEOFFSET

A more thorough explanation follows:

SYSDATETIME
------------------
This function is like the GETDATE function with the difference that it returns the current date and time with the precision of DATETIME2.

SYSUTCDATETIME
-----------------------
This function returns the current date and time in UTC again with the precision of DATETIME2.

SYSDATETIMEOFFSET
-------------------------
This function returns the current date and time along with the system time zone as a DATETIMEOFFSET value.

For all the above functions, if desirable, you can cast its values to DATE or TIME.

SWITCHOFFSET
------------------
This function adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC vaslue. In plain words it returns the date and time along with the current time offset.

TODATETIMEOFFSET
------------------------
Even though it might seem similar to the SWITCHOFFSET function, the TODATETIMEOFFSET function has a different purpose.
It actually converts types that are not time zone aware to DATETIMEOFFSET by the given time zone offset. So, if for an example you execute the following T-SQL statement:

SELECT TODATETIMEOFFSET('2009-07-29 20:00', '+03:00') AS CURRENT_DATE_TIME_OFFSET

you will get a DATETIMEOFFSET value saying that the date is: 2009-07-29 and time: 20:00 and the time zone offset is +03:00.

The exact result of the above T-SQL statement is: 2009-07-29 20:00:00.0000000 +03:00

Another difference between SWITCHOFFSET and TODATETIMEOFFSET is that the latter is not restricted to a DATETIMEOFFSET as an input value rather it accepts any date and time value (like in the above example).

------------------------------------------------------
T-SQL code examples using the above functions
------------------------------------------------------

--SYSDATETIME (Returns the System date and time with DATETIME2 precision)
SELECT GETDATE() AS CURRENT_DATETIME_OLD, SYSDATETIME() AS CURRENT_DATETIME_NEW
SELECT (CAST (GETDATE() AS DATE)) AS CURRENT_DATE_OLD, (CAST (SYSDATETIME() AS DATE)) AS CURRENT_DATE_NEW
SELECT (CAST (GETDATE() AS TIME)) AS CURRENT_TIME_OLD, (CAST (SYSDATETIME() AS TIME)) AS CURRENT_TIME_NEW

--SYSUTCDATETIME (Returns the UTC date and time with DATETIME2 precision)
SELECT SYSUTCDATETIME() AS CURRENT_UTC_DATETIME
SELECT (CAST (SYSUTCDATETIME() AS DATE)) AS CURRENT_UTC_DATE
SELECT (CAST (SYSUTCDATETIME() AS TIME)) AS CURRENT_UTC_TIME

--SYSDATETIMEOFFSET (Returns the Date and time along with the current time offset)
SELECT SYSDATETIMEOFFSET() as CURRENT_DATE_TIME_OFFSET

--SWITCHOFFSET (Adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC value)
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+03:00') AS CURRENT_DATE_TIME_OFFSET

--TODATETIMEOFFSET (Converts types that are not time zone aware to DATETIMEOFFSET by the given time zone offset)
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+03:00') AS CURRENT_DATE_TIME_OFFSET
SELECT TODATETIMEOFFSET(GETDATE(), '+03:00') AS CURRENT_DATE_TIME_OFFSET
SELECT TODATETIMEOFFSET(SYSDATETIME(), '+03:00') AS CURRENT_DATE_TIME_OFFSET
SELECT TODATETIMEOFFSET('2009-07-29 20:00', '+03:00') AS CURRENT_DATE_TIME_OFFSET

Live Webcast Announcement - SQL Server 2008 Programmability

2009-07-21T14:42:00.007+03:00

One of the things that inspires me the most in SQL Server, is the continuous enhancement of its programmability features like Data Access (Entity Framework and Data Services support), Visual Studio Integration, LINQ, Sync Framework, Project "Velocity" and of course, T-SQL Enhancements.

So I think that it is time for a Live Webcast!

On Wednesday, July 29, 8:00 PM Cyprus Local Time (GMT+2), I will be delivering a public Live Meeting Webcast on SQL Server 2008 Programmability and more specifically on T-SQL Enhancements.

See below for event specific details:

-----------------------------------------------
Title: Microsoft SQL Server 2008 Programmability: T-SQL Enhancements
Type: Live Meeting Webcast
Language: English
Product: Microsoft SQL Server
Audience: Developer
Duration: 60 Minutes
Start Date: Wednesday, July 29, 2009 8:00 PM Cyprus Local Time (GMT+2)
Presenter: Artemakis Artemiou [Microsoft SQL Server MVP]
Level: 200-300
Registration (*Required): Click on this link.
-----------------------------------------------

* Note: The GMT offset above does not reflect daylight saving time adjustments.

The event was also announced on CDNUG's website.
The official announcement can be found here.

See you all online!

SQL Server 2008 Collation Alignment with Windows Server 2008

2009-07-21T13:58:00.006+03:00

SQL Server 2008 is fully aligned with the collations in Windows Server 2008.
To this end, it has 80 new collations which are denoted by *_100 version references.

The total number of variations for these collations exceeds 1400.
The purpose of this enhancement is to provide users with the most up-to-date and linguistically accurate cultural sorting conventions.

In order to see the new collations along with their variations in SQL Server 2008 you can use the following T-SQL query:

--Get new collation details
SELECT [name], [description]
FROM sys.fn_helpcollations()
WHERE [name] LIKE '%100%'

For more information on SQL Server 2008 Collation and Unicode Support you can check out the following link.