DECLARE @xml as XML;
SELECT @xml = xmlField
From YourTable
Where
XmlId = '855041E’;
SELECT
X.value('OrderId[1]', 'VARCHAR(25)') As [orderid],
X.value('Status[1]', 'VARCHAR(10)') As [status]
FROM @xml.nodes('head/body/response/orders/order') AS T(X);SQL Server : Using $partition Function
Recently, I needed to validate date-range value on each partition on a partitioned table. To do this, I utilized the SQL Server "$Partition" function. Per MSDN, "$Partition returns the partition number into which a set of partitioning column values would be mapped for any specified partition function."
The syntax is:
[ database_name. ] $PARTITION.partition_function_name(expression)
This handy function can also be used to obtain column information, row counts, and other information you might want to know per partition:
–STEP ONE:
–Get the name of your partition function:
SELECT NAME FROM SYS.partition_functions
|
NAME |
|
TableAPartitionFunc |
–STEP TWO:
–Get the name of the column from which your table is partitioned
SELECT c.name AS PartitionedColumnName
FROM sys.tables t
JOIN sys.indexes i
ON (i.object_id = t.object_id
AND i.index_id < 2)
JOIN sys.index_columns ic
ON(ic.partition_ordinal > 0
AND ic.index_id = i.index_id and ic.object_id = t.object_id)
JOIN sys.columns c
ON (c.object_id = ic.object_id
AND c.column_id = ic.column_id)
WHERE t.object_id = LOWER(object_id(‘yourtablename’))
|
PartitionedColumnName |
|
DatePort |
–STEP THREE:
–Use acquired data to query your partition values:
SELECT DISTINCT $partition.TableAPartitionFunc(DatePort) AS PartitionNumber,
CONVERT(VARCHAR(8), DatePort, 1) AS RangedDate, ‘yourtablename’ AS PartitionedTable
from yourtablename
ORDER BY $partition. TableAPartitionFunc(DatePort)
|
PartitionNumber |
RangedDate |
PartitionedTable |
|
1 |
5/13/2013 |
yourtablename |
|
2 |
5/14/2013 |
yourtablename |
|
3 |
5/15/2013 |
yourtablename |
|
4 |
5/16/2013 |
yourtablename |
SQL Server : Difference Between SP_SEND_DBMAIL & XP_SENDMAIL
Recently, I decided to send email notifications via SQL Server’s XP_SENDMAIL() if a stored procedure were unable to complete its processing. For the same stored procedure, I was ALSO rolling back the transaction in the event of failure. However, I noticed that whenever the transactions failed, then I would not get the email.
After some research, I discovered that email messages sent using XP_SENDMAIL() would be rolled back (not sent) if the transaction is rolled back. However, I learned that if I were to use another SQL Server method, SP_SEND_DBMAIL(), then the email would be sent regardless of the end result of the transaction.
Therefore; in sum, if you want the email message to be sent regardless of the end result of the transaction you’ll need to use SP_SEND_DBMAIL(). Also, since XS_SENDMAIL() is apparently going to be deprecated, SP_SEND_DBMAIL may be the way to go for you.
It appears that other important differences between the two email methods are:
1. XP_SENDMAIL requires a MAPI client installed, such as Outlook, on the server. This is the only option for SQL Server 2000 and before.
2. SP_SEND_DBMAIL is an SMTP solution, added for SQL Server 2005 and later.
Happy coding.
Master Chief : “Behold! It is Monday & I have cake!”
SQL Server : Query For A Quick Overview Of Your Partitions
— =============================================
— This is a basic query intended to provide
— a quick look at partition info.
— =============================================
SELECT b.name as ‘table_name’, a.partition_number,
a.[rows] as ‘row_count’, a.partition_id,
a.object_id, a.hobt_id,
b.schema_id, c.name as ‘schema_name’,
a.index_id, b.create_date, b.modify_date
FROM sys.partitions AS a INNER JOIN
sys.objects AS b ON a.object_id = b.object_id
INNER JOIN sys.schemas AS c On c.schema_id = b.schema_id
WHERE LOWER(b.name) = ‘yourPartitionedTableName’
–pull only first index for a simpler view
and a.index_id < 2
ORDER BY b.name, partition_number;
— =============================================
–Helpful info for a deeper dive
— =============================================
SELECT * FROM SYS.partition_range_values ;
SELECT * from sys.partitions;
SELECT * FROM SYS.partition_functions;
SELECT * FROM sys.partition_schemes;
SELECT * from sys.schemas;
SELECT * FROM sys.filegroups;
–query sys.partitions to view and verify that the table contains partitions as expected
SELECT * FROM sys.partitions where OBJECT_NAME(Object_Id)=‘yourPartitionedTableName’;
SELECT * FROM sys.objects where OBJECT_NAME(Object_Id)=‘yourPartitionedTableName’;