Forgot or Reset SQL Authentication 'sa' password

To reset password for an SQL Authentication login user, execute the following code:

Say for example, you forgot 'sa' password, then you can execute the below code to assign new password for 'sa' user:

EXEC SP_PASSWORD @new='900%sec', @loginame='sa'.
 
Now try connecting to SQL server with your 'sa' login and new password.

Error - Unable to Copy Control Tasks from SSIS Package

Sometime we may not able to copy Control Tasks from SSIS Package to another package and an error message will be displayed, in that occassion execute the below command script:

SSRS Error Code - rsReportServerDatabase Unavailable

When we try to connect to some of the SSRS report server, the foolowing data connection error is displayed. It is because of improper Server and ReportDatabase configuretion. To overcome this perform the below steps:

1. Navigate to Programs --> MS SQL Server XXXX --> Configuration Tools --> Report Service Configuration Manager.

2. Click on Database menu.

3. Check whether SQL Server Name and proper ReportServer database is configured. If not click on Change Database button and configure your server name and ReportServer database. The connection will work now.

Pentaho JDBC and JTDS connection for SQL Server

Pentaho JTDS Connection:

Pentaho JDBC Connection

New T-SQL functions in SQL Server 2012

New T-SQL functions in SQL Server 2012:

--PARSE()

--Parse Currency Symbol

SELECT PARSE('$100' AS MONEY USING 'EN-US') AS Currency

--Result = 100.00

--Parse DATETIME

SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS US_Date

--Result = 2010-12-13 00:00:00.0000000

--CONCAT()

SELECT CONCAT('Firstname',' ','Surname') AS MyName

--Result = Firstname Surname

--CHOOSE()

DECLARE @Letter INT = 4

SELECT CHOOSE(@Letter, 'A','B','C','D', 'E','F')

--Result = D

--IIF()

DECLARE @Letter INT = 5

SELECT IIF(@Letter % 2 > 0,'ODD','EVEN')

--Result = ODD

--Get Date for the provided yy,MM,dd

SELECT DATEFROMPARTS(1999,2,3)

--GET LastDate of Month

SELECT EOMONTH(GETDATE(),0)

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.