SQL 2012 Snippet Feature to Create Syntax for Objects

SQL 2012 new feature includes Snippet to Create Syntax for adding new objects.
When a user click (Ctrl + K, Ctrl + X). The below snippets occurs providing option to select objects like Table, Stored Procedure, etc.

SQL 2012 FORMAT() Function

Formatting in T-SQL - SQL 2012

SELECT
 FORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO_Date],
 FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS [ISO_Date_time],
 FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [EN_DateFormat],
 FORMAT(GETDATE(), 'MMMM dd, yyyy', 'fr-FR') AS [French_Format],
 FORMAT(22.7, 'C', 'en-US') AS [US_Currency],
 FORMAT(22.7, 'C', 'en-GB') AS [UK_Currency],
 FORMAT(99 * 2.226, '000.000') AS [Decimal],
 FORMAT(12345678, '0,0') AS [Thousand Separator]
;

SQL 2012 Throw function

As like in C#, a new function THROW is introduced in SQL 2012 to make try and catch error method more simple instead of using RAISEERROR.

BEGIN TRY BEGIN TRANSACTION -- Start the transaction
DELETE FROM Customers
   WHERE EmployeeID = @EmpID -- Commit the change
COMMIT TRANSACTION
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
 THROW
END CATCH

Pagination OFFSET and FETCH Commands in SQL 2012

A new built-in functions for pagination is introduced in SQL 2012. Using this we can skip 'n' number of top rows and retrieve other rows.

CREATE TABLE dbo.Employee
(
ContactId int IDENTITY(1,1) NOT NULL,
FirstName varchar(60),
LastName varchar(60),
Phone varchar(60),
Email nvachar(100)
LocationID int
);

INSERT INTO dbo.Employee
Select firstname, lastname, phone, EmailId, LocationID
FROM dbo.EmployeeAddress

The below query skip top 100 rows  and retrieves the next 10 records:
SELECT ContactId, FirstName, LastName, Phone
FROM dbo.Employee
ORDER BY ContactId
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY;

Configure Change Data Capture Parameters in SQL Server

After enabling CDC in SQL Server (see: http://mahadevanrv.blogspot.in/2011/05/change-data-capture-in-sql-server.html). We can modify the retention period and the number of transactions that to be handled in Change Data Capture table.

Before configureing one should understand the basic terms in CDC Configuration:

 

Polling interval – (Default 5 seconds) the amount of time to wait between log scans

Maxtrans – (Default 500) the number of transactions to grab with each scan

Maxscans – (Default 10) the number of scans performed between each polling interval

Retention – (Default 72 hours, 4320 mins, 3 days). The period for which the new/updated/deleted data have to be retrieved and displayed in CDC tables. 

Execute the below query to get the CDC configured values:

SELECT * from msdb.dbo.cdc_jobs


Execute the below query to change capture instances:

EXEC sys.sp_cdc_change_job @job_type = 'capture'
       ,@maxtrans = 501
       ,@maxscans = 10
       ,@continuous = 1
       ,@pollinginterval = 5


Execute the below query to change retention period:

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'
                          ,@retention = 4320 -- Number of minutes to retain (72 hours)
                          ,@threshold = 5000


Using this method we can use CDC hold the required period of historical data, i.e., for last 1 month, last 1 year or last 10 days, etc.

Pentaho SQL SERVER 2012 Data Connection Issue

If you face any issue in connecting to SQL SERVER 2012/SQL SERVER by Pentaho Date Connection, then download JTDS driver for sql server (jtds-1.2.5-dist) and load it in the below location:

"~\biserver-ce\tomcat\webapps\pentaho\WEB-INF\jtds-1.2.5-dist"

Now restart the pentaho server, and create new sql server connection.

Use of RequiredFieldValidator and CompareValidator in ASP.NET

RequiredFieldValidator Control: On using this control we can raise an error message when a field in blank or null, i.e., we can define a field as mandatory.

CompareValidator Contro: This control help us to compare value of one field to another field and raise error message based on the condition we defined.

Below is the sample code where we validate StartDate and EndDate for null values using RequiredFieldValidator and compare Startdate with Enddate value using CompareValidator.

<tr>
<td colspan ="6" rowspan ="3" align ="left"><asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="StartDatePr" ErrorMessage="Start Date should not be null." Display ="Dynamic"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td colspan ="6" rowspan ="3" align ="left"><asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="EndDatePr" ErrorMessage="End Date should not be null." Display ="Dynamic"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td colspan ="6" rowspan ="3" align ="left"><asp:CompareValidator ID="CompareValidator1" runat="server" ControlToCompare="StartDatePr" ControlToValidate="EndDatePr" ErrorMessage="End Date should be greater than Start Date." Operator="GreaterThan" Type="Date" Display ="Dynamic"></asp:CompareValidator></td>
</tr>
</table>