Move database Diagrams between SQL Server 2005/2008

In SQL Server 7.0 and 2000 database diagrams information has been stored in table
dbo.dtproperties, you can use DTS to transfer diagrams.

Since SQL Server 2005, the system table dbo.sysdiagrams holds the information of database diagrams created for a database.

When transfer database diagrams between SQL Server 2008, the tables in the database referred in the diagrams should have the same structures in both SQL Servers or databases.

By using SQL server Import and Export data tool to copy data in dbo.sysdiagrams table, once done refresh Database Diagrams you will see the diagrams.

Use formatted lines in Send Mail Task in SSIS package

We often want to format the information in the emails. In Send Mail Task of a SSIS packages, I wanted, instead of sending out information in an English sentence like this:

"At $datetime on Machine $machinename SSIS packge $packagename imported $Rowcount rows from source file $filename."

I would rather like to put each piece of information on a different line. To do that, In Send Mail Task create an Expressions for MessageSource. In Expression Builder, write something like this:

"\nMachine Name: " + @[System::MachineName]  +
"\nPackage Name: " + @[System::PackageName] +
"\nExecuted Time: " +  (DT_WSTR, 30)@[System::StartTime] +
"\nSource File Name: " + @[User::FileName]  +
"\n\nRows Imported: "  + (DT_WSTR, 8)@[User::RowCount]  + "\n"

As you can see, "\n" is used to get line break.
Done.

The equivalent of Oracle CONNECT BY PRIOR in SQL Server

During converting Oracle statements into SQL Server T-SQL, there seems no direct equivalent of Oracle PL/SQL CONNECT BY PRIOR ... START WITH statement in SQL Server. The SQL standard way to implement recursive queries, is the WITH clause (recursive CTE). 

Oracle PL/SQL example:

    select UserID, FirstName, LastName
    from Users
    WHERE UserTypeID = 5
    CONNECT BY PRIOR UserID = ParentID  START WITH UserID = 2320
    ORDER BY LastName

SQL Server T-SQL code:

    WITH N (Userid, FirstName, LastName, UserTypeID)
    AS
   (
    SELECT UserID, FirstName, LastName
    FROM Users
    WHERE Userid = 2320
   
    UNION ALL
   
    SELECT  np.UserID, np.FirstName, np.LastName, np.UserTypeID

    FROM Users AS np JOIN N ON N.UserID = np.ParentID
)

    SELECT Userid, FirstName, LastName

    FROM N
    WHERE UserTypeID = 5
    ORDER BY Lastname

Be careful with the CONNECT BY ... PRIOR statement, if in Oracle:

CONNECT BY UserID = PRIOR ParentID  START WITH UserID = 2320  instead of

CONNECT BY PRIOR UserID = ParentID  START WITH UserID = 2320  

Then in SQL should be:

    WITH N (Userid, FirstName, LastName, UserTypeID, ParentID)
    AS
   (
    SELECT UserID, FirstName, LastName, UserTypeID, ParentID
    FROM Users
    WHERE Userid = 2320
   
    UNION ALL
   
    SELECT  np.UserID, np.FirstName, np.LastName, np.UserTypeID, np.ParentID

    FROM Users AS np JOIN N ON np.UserID = N.ParentID
    )

    SELECT Userid, FirstName, LastName

    FROM N
    WHERE UserTypeID = 5
    ORDER BY Lastname

For more info about using recursive queries with WITH, please refer to here.

T-SQL "ELSE IF" statement

In Transact-SQL, if we need to test multiple conditions, we can use case or multiple IF ... ELSE IF statement which I don't much in Microsoft SQL Server Books Online. The following codes have been tested in SQL Server 2008.

Using "ELSE IF" example:

/* print out value3 */

DECLARE @para INT = 12;

IF @para = 10
    PRINT 'value1'
ELSE IF @para = 11   
    PRINT 'value2'
ELSE IF @para = 12   
    PRINT 'value3'       
ELSE
    PRINT 'Not any value.';

Using "CASE" example:

/* print out value3 */ 

DECLARE @para INT = 12;
SELECT
    CASE @para
                WHEN 10 THEN 'value1'
                WHEN 11 THEN 'value2'
                WHEN 12 THEN 'value3'
                WHEN 13 THEN 'value4'
    ELSE 'Other'
    END;

It seems using "case" is more intuitive.

Get sample data from a SQL table

In TSQL, the TABLESAMPLE clause limits the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. For example:

/*Return a sample 10 percent of the rows of the result set. */

TABLESAMPLE (10 PERCENT)

/* Return a sample of 15 rows from the result set. */.
TABLESAMPLE (15 ROWS) 

You can use TABLESAMPLE to quickly return a sample from a large table when either of the following conditions is true:

•   The sample does not have to be a truly random sample at the level of individual rows.

•   Rows on individual pages of the table are not correlated with other rows on the same page.

TABLESAMPLE cannot be applied to derived tables, tables from linked servers, and tables derived from table-valued functions, rowset functions, or OPENXML. TABLESAMPLE cannot be specified in the definition of a view or an inline table-valued function.

Example:

Use AdventureWorks2008
GO

select *
from Sales.Customer
tablesample( 2 percent);
--REPEATABLE (205)

Use FileConnection as SQLSourceType in Execute SQL Task in SSIS package

When using an Execute SQL Task in a SSIS package, usually we do:

1.  Create a new Integration Services Project in Microsoft Visual Studio;

2.  Drag the Execute SQL Task from Toolbox, then set up its Connection either through Data Sources, Data Source Views or directly create a new connection in the Connection Managers, simple.

Let's say this Execute SQL Task will run some TSQL queries, for example, to create a few tables with PKs and indexes for the next Control Flow task. If the query is relatively short, say  less than 20 lines, we usually just write the query right in the SQLStatement field, simple and quick. However, we if the query we need to run is long, although we can still copy/paste to the SQLStatement field, but it will take some time to search something by scroll up and down. Another thought is to use a stored procedure, however the better solution here is to use a File Connection.

To use a File Connection, just right-click on the Connection Managers area, then choose "New File Connection...".

then choose the DDL query file - CreateTables.sql.

The next step is to config the Execute SQL Task, double-click on it:

Notice that, for SQL SourceType, we choose "File connection", and for FileConncetion, we choose the query file.

Happy SSISing.