Tag Archives: Transact-SQL

Using TIMEFROMPARTS() – SQL Server 2012

This new function in SQL server 2012 helps in converting time parts to time. The syntax of this function is as follows

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

The range of the parameters for TIMEFROMPARTS is as follows :
Hour -> 0-23.
Minutes -> 0-59.
Seconds -> 0-59.
Fractions -> 0-9999999.
Precision -> 0-7.

Let us understand this with an example.

DECLARE @hour INT, @min INT,@Sec INT,@frac INT;
SET @hour = 13
SET @min = 24
SET @Sec = 22
SET @frac = 45
SELECT TIMEFROMPARTS(@hour,@min,@Sec,@frac,2)

time1

This function requires a valid value for the Hour,Minute, Seconds, Fractions, Precision parameters. If any invalid value is passed then this function will return an error. If a Null value is passed on for the Precision parameter then it generates an error. For other parameters if a Null is passed then the output is also Null.

For example in the below code we pass an invalid value for the Hour parameter

time2

Now lets see what happens when we pass a Null value to the Fraction parameter

time3
 
 
 
 
 
 
   
   
   
      
If we fail to pass any one of the parameters then the following error message is returned.
time4

TRY_CONVERT function in SQL Server 2012

TRY_CONVERT is one of the new conversion function introduced in SQL SERVER 2012. It returns a value converted to the specified data type if the conversion succeeds. Otherwise,it returns returns NULL value when it fails to convert to a requested data type. TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted

The syntax for TRY_CONVERT is as follows

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Let us now implement this function and see how it works with different examples

SELECT TRY_CONVERT(xml, 'Manchester United')
SELECT TRY_CONVERT(DATETIME, '02/18/2014 05:30',111)
SELECT TRY_CONVERT(INT, '40')

The output of the above as follows
TryCon1

Now let us see examples where this function might fail or throw an exception

SELECT TRY_CONVERT(xml, 40)

And the output is
TryCon2

SELECT TRY_CONVERT(DATETIME, '22/18/2014 05:30',111)

And the output is
TryCon3The first error is self explanatory. The second code gives an output of NULL because the date is an invalid date.

Now the question is how is TRY_CONVERT different from CONVERT function?

Lets understand with this example

SELECT CONVERT(DATETIME, 'ABC')

TryCon4CONVERT will give an error message stating the conversion failed.

SELECT TRY_CONVERT(DATETIME, 'ABC')
AS 'Result'

TryCon5TRY_CONVERT will give a NULL.

 

Replication – Script – Dropping a table from an existing publication in SQL Server

In this article I will share the script that I use while dropping a table from an existing publication.
Below is the script that helps us achieve the same.

use [databasename]--Name of the DB in replication
go
exec sp_dropsubscription @publication=N'NameOfPublication',
@article=N'TestTable',--name of the table to be dropped
@subscriber=N'all'
go
use [databasename]
go
exec sp_droparticle @publication = N'NameOfPublication',
@article = N'TestTable',--name of the table to be dropped
@force_invalidate_snapshot = 1
go

Technical reference:
The details for the stored procedure sp_dropsubscription can be found in the below link
http://technet.microsoft.com/en-us/library/ms184385.aspx

Replication – Script – How to add a table to an existing publication in SQL Server

In this article i will share the script that I use when I add a table to an existing publication in SQL Server.
The below script would help us to add any table to an existing publication. The most important thing about adding a table to replication is that the table must have a primary key.

Below is the TSQL script to achieve the same.

use [databasename]--Name of the table involved in Replication
Go
exec sp_addarticle @publication = N'NameOfPublication',
@article = N'TestTable', @source_owner = N'dbo',
@source_object = N'TestTable', 
@type = N'logbased',@description = null,
@creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x0000000008035DDB,
@force_invalidate_snapshot = 1,
@identityrangemanagementoption = N'manual',
@destination_table = N'TestTable',
@destination_owner = N'dbo', @status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_dboTestTable',
@del_cmd = N'CALL sp_MSdel_dboTestTable',
@upd_cmd = N'SCALL sp_MSupd_dboTestTable'
Go

More details on the used stored procedure [sp_addarticle] can be found on the below mentioned technet article

http://technet.microsoft.com/en-us/library/ms173857.aspx

%d bloggers like this: