XML – sp_xml_preparedocument replacement

*** Get TABLE ***
DECLARE @xml xml
 
SET @xml = ...
 
SELECT
T.c.value('Text[1]','varchar(35)') AS MyText
, T.c.value('Selected[1]','varchar(5)') AS Selected
FROM @xml.nodes('/AIRInfo/Platform/ArrayOfListItem/ListItem') T(c) -- ????
 
*** Get value directly ***
@ProjectInfo.value('(/ProjectInfo/project_type_id)[1]', 'int')
 
*** SELECT ***
vm.AfterTaxYrOfFirstContrib As "AfterTaxYrOfFirstContrib"
, m.ex_user_id As "PersonalInfo/MemberID"

*** Check to see if a value exists within the XML ***
@XML.exist('//Selected/../Text[. = "SQL"]') 

SQL Server – Hashing Sample

INSERT INTO Users
(UserName, UserPassword)
VALUES
(‘BillGates’, HashBytes(‘MD5′, ‘ThisIsMyPa$$w0rd’))

SQL Server 2005 – (Un)Encrypt data

CREATE ASYMMETRIC KEY Asym_WEB_MAIN
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = ‘password’

DECLARE @encryptedstuff NVARCHAR(100)
SELECT @encryptedstuff = EncryptByAsymKey(AsymKey_ID(‘Asym_WEB_MAIN’), N’Data to be encrypted’)
SELECT @encryptedstuff
SELECT CAST(DecryptByAsymKey(AsymKey_ID(‘Asym_WEB_MAIN’), @encryptedstuff, N’password’) AS NVARCHAR)

SQL Server – Sending Mail [2005]

EXEC msdb.dbo.sp_send_dbmail
    @recipients=N’danw@Adventure-Works.com’,
    @body=N’The master database is full.’ ;

http://msdn2.microsoft.com/en-US/library/ms187891.aspx

SQL Server – Enumerate all tables

declare @TableName sysname,@SQL Varchar(1000)
DECLARE Table_Name CURSOR FOR
select name from sysobjects where type = 'u' and objectproperty ( id, 'ismsshipped') = 0

OPEN Table_Name

FETCH NEXT FROM Table_Name INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'IF OBJECT_ID (''' + @TableName + ''') IS not NULL' + CHAR(13)
SELECT @SQL = @SQL + 'TRUNCATE TABLE '+quotename(@TableName)
exec(@SQL)
--SELECT (@SQL)
FETCH NEXT FROM Table_Name INTO @TableName
END
CLOSE Table_Name
DEALLOCATE Table_Name

SQL Server 2005 – Execute Role

/* CREATE A NEW ROLE */

CREATE ROLE db_executor
 

/* GRANT EXECUTE TO THE ROLE */

GRANT EXECUTE TO db_executor