SSIS 2008 / SQL 2008 / .NET Data Types

SQL Server	SSIS	.NET	SQL CLR (System.Data.SqlTypes namespace)
bigint	DT_I8	Int64	SqlInt64
binary	DT_BYTES	Byte[]	SqlBytes, SqlBinary
bit	DT_BOOL	Boolean	SqlBoolean
char	DT_STR	None	None
cursor	None	None	None
date	DT_DATE	DateTime	SqlDateTime
datetime	DT_DBTIMESTAMP	DateTime	SqlDateTime
datetime2	DT_DBTIMESTAMP2	DateTime	SqlDateTime
decimal	DT_DECIMAL	Decimal	SqlDecimal
float	DT_R8	Double	SqlDouble
image	DT_IMAGE	None	None
int	DT_I4	Int32	SqlInt32
money	DT_CY	Decimal	SqlMoney
nchar	DT_WSTR	String, Char[]	SqlChars, SqlString
ntext	DT_NTEXT	None	None
numeric	DT_NUMERIC	Decimal	SqlDecimal
nvarchar	DT_WSTR	String, Char[]	SqlChars, SqlString
real	DT_R4	Single	SqlSingle
rowversion		Byte[]	None
smalldatetime	DT_DBTIMESTAMP	DateTime	SqlDateTime
smallint	DT_I2	Int16	SqlInt16
smallmoney	DT_CY	Decimal	SqlMoney
sql_variant		Object	None
table		None	None
text	DT_TEXT	None	None
time	DT_DBTIME2	Timespan	TimeSpan
timestamp	DT_BYTES	None	None
tinyint	DT_UI1	Byte	SqlByte
uniqueidentifier	DT_GUID	Guid	SqlGuid
varbinary	DT_BYTES	Byte[]	SqlBytes, SqlBinary
varchar	DT_STR	None	None
xml	DT_WSTR	None	SqlXml

Sources :
Mapping SQL Server 2008 data types to SSIS
Mapping SQL Server 2008 data types to .NET / CLR

Off-topic : Social Media

I use RSS, Twitter etc to follow the SQL Server community.

Here is an interesting article on where social media is going...
Six Social Media Trends for 2010

Moving Unique Constraints & Indexes

Really good script for Moving Unique Constraints & Indexes across Partitions (subscriber only)

http://www.sqlservercentral.com/articles/Index+Management/64037/

Configuring MSDTC (Distributed Transaction Co-ordinator)

MSDTC is required if transactions span multiple servers (i.e. linked servers, ugh!)

Firstly, the SERVICE needs to be running >



Secondly, you need to allow communication (Allow Inbound and Allow Outbound tickboxes below) >

Dynamic Management Objects : sys.dm_exec_query_stats

Sys.dm_exec_query_stats is a dmv (dynamic management view) which stores summary information about queries in the query cache.

Sys.dm_exec_sql_text(sql_handle) is a function that returns the executed command from sql_handle.

Putting them together with CROSS APPLY (APPLY lets you join the output of a function), you can see what is being run and how often.

SELECT t.text , s.*
FROM sys.dm_exec_query_stats  s
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t CT * FROM(SELECT coalesce(object_name(s2.objectid)%'
ORDER BY execution_count DESC

This provides you 35 columns summarising query activity and includes counts, times, reads, writes statistics.

SQLDenis has provided a great query, which i've slightly adapted to order by the the most commonly executed queries.
It tells you where the sql is called from (ProcedureName) or replaces it with 'Ad-hoc' if not called from a procedure. >

SELECT * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcedureName,execution_count,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
     ( (CASE WHEN statement_end_offset = -1
        THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
        ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
ORDER BY execution_count DESC

SQLDenis's original post >http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/finding-out-how-many-times-a-table-is-be-2008

MSDN Reference : http://msdn.microsoft.com/en-us/library/ms189741.aspx