Migrate data from one SQL table to another database table

Sometimes you may have to migrate data from one SQL database to another SQL database on another environment, e.g. Live to test and dev environment and vice versa.

You may argue the easiest solution is a migration of the backup of database. Unfortunately it does not work well in environments where data in Live is classified as sensitive and are not allowed to be copied across into a dev / test machine where the security is not as strict as a live machine. There are some data from live which are not classified as sensitive or restricted and might have to be migrated to Test and DEV for testing or development purposes, for example list of values.

A copy of the data can be exported as a csv or dat file from the SQL Table using “select * from table_value” statement from source database.

The data can be bulk imported into a temp table and MERGE statement can be used to insert missing records and update records. The sample script which you can use is below.

The script can be downloaded from TechNet.

Rebuild and Reorganize indexes on all tables in MS database

As part of database maintenance, indexes on databases have to be rebuilt or reorganised depending on how fragmented the indexes are. From the article Reorganize and Rebuild Indexes, the advice is to reorganise index if avg_fragmentation_in_percent value is between 5 and 30 and to rebuild index if it is more than 30%.

The script below queries all fragmented indexes more than 5 percent and using a cursor a loop is performed on the results to rebuild or reorganise  indexes depending on the percentage of fragmentation using dynamic SQL, i.e.

The script can be downloaded from technet gallery  , i.e. if avg_fragmentation_in_percent value is between 5 and 30 then reorganise else rebuild.


declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)


declare FragmentedTableList cursor for
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE
-- indexstats.avg_fragmentation_in_percent , e.g. >30, you can specify any number in percent
indexstats.avg_fragmentation_in_percent > 5
AND ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC


OPEN FragmentedTableList
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment


WHILE @@FETCH_STATUS = 0
BEGIN
print 'Processing ' + @indexName + 'on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
if(@percentFragment<= 30)
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REBUILD; ')
print 'Finished reorganizing ' + @indexName + 'on table ' + @tableName
END
ELSE
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REORGANIZE;')
print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName
END
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment
END
CLOSE FragmentedTableList
DEALLOCATE FragmentedTableList

SQL script batch execution using sqlcmd in PowerShell

There is often a mismatch between needs of the development team (multiple discreet T-SQL files for separate concerns) and the release team (the requirement  for one step automated deployment) . The script bridges the requirement by using sqlcmd.exe to run a batch of SQL scripts.

A text file is used listing all sql files that need to run in a particular order to avoid errors which may occur if there are dependencies between the scripts. Instead of using a text file a number can be prefixed to the scripts based on the order they need to run.

The script expects two parameters –

  • Path of folder containing the set of T-SQL files (and the manifest file, see below)
  • Connection string

The script can be downloaded from technet gallery.

## Provide the path name of the SQL scripts folder and connnection string
##.\SQLBatchProcessing.ps1 -SQLScriptsFolderPath "C:\Sql Batch Processing\SQLScripts" -ConnectionString "DEV-DB-01"
Param(
[Parameter(Mandatory=$true)][String]$ConnectionString ,
[Parameter(Mandatory=$true)][String]$SQLScriptsFolderPath
)
Set-ExecutionPolicy -ExecutionPolicy:Bypass -Force -Confirm:$false -Scope CurrentUser
Clear-Host
#check whether the SQL Script Path exists
$SQLScriptsPath = Resolve-Path $SQLScriptsFolderPath -ErrorAction Stop
#a manifest file will exisit in the SQL scripts folder detailing the order the scripts need to run.
$SQLScriptsManifestPath = $SQLScriptsFolderPath + "\Manifest.txt"
#Find out whether the manifest file exists in the the SQL Scripts folder
$SQLScriptsManifestPath = Resolve-Path $SQLScriptsManifestPath -ErrorAction Stop


#if manifest file found iterate through each line , validate if corresponding SQL script exists in file before running each of them
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
Resolve-Path $SQLScriptsPath -ErrorAction Stop
}
$SQLScriptsLogPath = $SQLScriptsFolderPath + "\" + "SQLLog.txt"
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "Started processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
$text = "Running script " + $_.ToString();
Add-Content -Path $SQLScriptsLogPath -Value $text
sqlcmd -S "DEV-DB-01" -i $SQLScriptsPath | Out-File -Append -filepath $SQLScriptsLogPath
}
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "End processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""

Add SSRS report to Project Detail Page Project Server

Often clients want to be able to drill down to SSRS reports with the project pre selected when they are viewing project information from project detail pages. This post describes how to embed SSRS report in a project detail page.

Add SSRS Report to Project Detail Page in Project Server

1. Navigate to a PDP (Project Detail Page) , e.g. Schedule PDP.
2. Edit Page
3. Click “Add a Web Part”. Select the “SQL Server Reporting Services Report Viewer” web part.

SQLReportViewerWebPart

4. Click “Add a Web Part”. Select the “SQL Server Reporting Services Report Viewer” web part.

ToolPane_SelectReport

5. Click “Click here to open the tool pane”.

SelectReport

6. Click on the “…” button to select a SSRS report and click apply. In the example below the “Project Overall Status” has been selected with one parameter capable of accepting ProjectUID.

Report_ProjectOverallStatus_NoParam_Passed_edited.png

7. Click “Add a Web Part” to add “Query String (URL) filter”.

QueryString(URL)Filter

8. Click “Open the tool pane” to configure “Query String (URL) filter”.

QueryString(URL)Filter_OpenTheToolPane

9. Add “projuid” into the “Query String Parameter Name” property.

QueryString(URL)Filter_ProjUid

10. Click on the “Arrow Down” on the webpart and click on “Connections”-”Send Filter Values To” – .

SendFilterTo

11. Click on the “Arrow Down” on the webpart and click on “Connections”-”Send Filter Values To” – .
Pick the parameter defined in the SSRS report into “Filtered Parameter”.

FilteredParam

12. Click on “Stop Editing”.

StopEditing

13. The report is rendered displaying only information of the project

Create a TimeLine View for Milestones and Project Duration using Matrix in SSRS

A range chart in SSRS can be used to produce a Gantt chart.of project. Below is a link to a great post that explains how to do it.

http://pnarayanaswamy.blogspot.co.uk/2010/09/range-bar-chart-gantt-chart-using-ssrs.html

Unfortunately I have found the following limitations

  1. The width and height of the range chart does not grow dynamically when run.
  2. If too much data is displayed, the chart is unreadable

To counteract the above limitations, I  have used the matrix control which can grow dynamically in terms of width and height to fit the data which remains readable.

The article describes how to display the timeline of projects with milestones using the Matrix control in SSRS. At the end of step by step guide you will end up with a report which looks like

MilestonesTimeLineView_Report

The diamond shapes represent milestones while the blue color represents the duration of the project.

 Create Milestones Timeline View Report

1. In your Web browser, type the URL for your report library in the address bar.

2. Click ‘New Document’> ‘Report Builder Report’. (Please note if it is the first time you are trying to access report builder, it might prompt to download report builder).

ReportBuilderReport

(Please note if it is the first time you are trying to access report builder, it might prompt to download report builder).
3. Click “Blank Report”
4. Add reference to a Shared Data Source.

To create a Shared Data Source , Click “New Document”> ‘Report Data Source’

NewDataSource

Fill in the following details:
• Name
• Data Source Type: If source is SQL server database, select ‘Microsoft SQL Server’
Connection String: Example of connection string is
Data Source=<SERVERNAME>;Initial Catalog=<DATABASENAME>
Replace the SERVERNAME tag with the database server name instance and DATABASENAME tag with the database name in the server.
The final connection string will look like
“Data Source=SUPPORTSQL2K8R2;Initial Catalog=VM470_PWA_Reporting”
3. Click ‘Test Connection’. If the connection to the data source is successful, the message ‘Connection created successfully’ appears below the ‘Test Connection’ button.

DB_Conn_Success

4. Click ‘Apply’ to save the data source.

5. Right-Click on Datasets Folder , click “Add Dataset”
Update the Name to “MilestoneDataSet”.
Pick the Data Source created earlier.
Select “Text” Option under “Query type”

DataSetProperties

Under Query, copy and paste the following query

DECLARE @workingDate DateTime
SET @workingDate = @inputStartDate


-- create temporary table of unique days for given start date and end date
DECLARE @days TABLE (
DayByDay datetime,
MonthYr nvarchar(25),
WeekMonthYr nvarchar(25)
)


-- calculate each day within number of week range
WHILE (@workingDate <= @inputEndDate)
BEGIN
INSERT INTO @days (
DayByDay,
MonthYr,
WeekMonthYr
) VALUES (
@workingDate
,convert(char(3),(@workingDate),0) + ' ' + convert(nvarchar(6), year(@workingDate))
,convert(nvarchar(3), DATEPART(wk, @workingDate)) + ' ' + convert(char(3),(@workingDate),0) + '-' + convert(nvarchar(6), year(@workingDate))
)
SET @workingDate = DateAdd(d, 1, @workingDate)
END


--Create temporary table to store ProjectUID
DECLARE @TProject TABLE(
ProjectUID uniqueidentifier
)


INSERT INTO @TProject
SELECT DISTINCT PROJECTUID
FROM dbo.MSP_EpmProject_UserView P
INNER JOIN @days D ON CONVERT(DATE,P.ProjectStartDate) <= D.DayByDay AND CASE WHEN ISNULL(CONVERT(DATE,P.ProjectActualFinishDate),CONVERT(DATE,'1900-01-01')) > CONVERT(DATE,P.ProjectFinishDate) THEN CONVERT(DATE,P.ProjectActualFinishDate) ELSE CONVERT(DATE,P.ProjectFinishDate) END >= D.DayByDay
AND P.ProjectUID in (@ProjectUId)


-- CREATE INDEX TmpDelProj ON @TProject(ProjectUID)


--select * from #TProject
-- create temporary table of unique days for given start date and end date
DECLARE @PMilestoneTmp TABLE (
ProjectUID uniqueidentifier,
DayByDay datetime,
MonthYr nvarchar(25),
WeekMonthYr nvarchar(25),
MilestonesName nvarchar(max)
)
--in case there are two milestones finishing in same week
INSERT INTO @PMilestoneTmp
SELECT P.ProjectUID
, d.DayByDay
, d.[MonthYr]
, d.WeekMonthYr
,(STUFF((
SELECT ' ,' + convert(nvarchar(3), day(TaskFinishDate)) + ' ' +
+ convert(char(3), TaskFinishDate , 0) + ' ' +
+ convert(nvarchar(6), year(TaskFinishDate))
+ ' - ' + T.TaskName
FROM dbo.MSP_EpmTask_UserView T
WHERE T.ProjectUID = P.ProjectUID
AND convert(nvarchar(3), DATEPART(wk, T.TaskFinishDate)) + ' ' + convert(char(3),(T.TaskFinishDate),0) + '-' + convert(nvarchar(6), year(T.TaskFinishDate)) = d.WeekMonthYr
AND T.TaskIsMilestone = 1
ORDER BY T.TaskFinishDate ASC
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'),1,3,'')
) AS MilestonesName
FROM @TProject P
CROSS JOIN @days d


--create table for milestones ending per day per project
SELECT MP.DayByDay
, MP.MonthYr MonthYear
, 'Wk' + MP.WeekMonthYr WeekMonthYr
, MP.MilestonesName
,P.ProjectName
,P.ProjectUID
,P.ProjectFinishDate
,P.ProjectStartDate
,P.ProjectActualFinishDate
,P.ProjectBaseline0FinishDate
,(CASE WHEN CONVERT(DATE,P.ProjectStartDate) <= MP.DayByDay AND CASE WHEN ISNULL(CONVERT(DATE,P.ProjectActualFinishDate),CONVERT(DATE,'1900-01-01')) > CONVERT(DATE,P.ProjectFinishDate) THEN CONVERT(DATE,P.ProjectActualFinishDate) ELSE CONVERT(DATE,P.ProjectFinishDate) END >= MP.DayByDay THEN 1
ELSE 0 END ) ProjectInProgress
FROM @PMilestoneTmp MP
LEFT OUTER JOIN dbo.MSP_EpmProject_UserView P ON P.ProjectUID = MP.ProjectUID
ORDER BY MP.DayByDay ASC

6. Click on “Ok”.
Three parameters are created. Expand the Parameters folder to view @inputStartDate,@inputEndDate and @ProjectUId parameters

Parameters

7. Right-click on @inputStartDate and select “Parameter Properties”.Update as follows
Change the “Prompt” to “Start Date”.
Update “Data type” to “Date/Time”.

inputStartDateProp

8. Right-click on @inputEndDate and select “Parameter Properties”.Update as follows
Change the “Prompt” to “End Date”.
Update “Data type” to “Date/Time”.

inputEndDateProp

9. Right-Click on Datasets Folder , click “Add Dataset”
Update the Name to “Param_ProjectList”.
Select “Data Source”
Select “Text” under “Query type”
Copy and paste the SQL query under “Query”

SELECT
MSP_EpmProject.ProjectUID
,MSP_EpmProject.ProjectName
FROM
MSP_EpmProject
ORDER BY MSP_EpmProject.ProjectName

Project_Dataset_Prop

Click on Ok to create the dataset
10. Right-click on @ProjectUId parameter and select “Parameter Properties”.Update as follows
Click on “General” tab and check “Allow Multiple values”

Project_Multi_Select

Click on “Available Values” tab and select option “Get values from a query”. Pick the Dataset “Param_ProjectList” and Select “ProjectUID” in value field and “ProjectName” in Label field.

ProjectParamProp_AvailableValues

11. Add and edit the Matrix
• Right–Click on the body of the report and choose Insert>Matrix.
• Right-Click on the Matrix and select Tablix Properties.
• Select the “MilestoneDataSet” into the Dataset name.

Matrix_Properties

• Select the “MilestoneDataSet” into the Dataset name and click “Ok”
• Right click on the “RowGroup” and select “Group Properties”. Select [PROJECT_UID] in “Group On:” field and update name to ProjectUID.

Group_On_Project

• Right click on the “ColumnGroup” and select “Group Properties”. Select [WeekMonthYr] in “Group On:” field and update name to WeekMonthYr.

WeekMonthYr

• Right click on “WeekMonthYr” and select “Add Group” > “Parent Group”.

WeekMonthYr_ParentGroup

• Select [MonthYear] in “Group by” and click on Ok.

MonthYear_Group

• Right click on the “MonthYear” and select “Group Properties”. Select ”Sorting” tab and click on “Add”. Select [DayByDay] in “Sort by” and Order “A to Z”. Click on Ok.

Group_Sort_DayByDay

The Matrix will look like

Matrix_Display

• Select the second row and delete row by picking option “Delete rows only”.

DeleteGroup

• Select field [ProjectName] in first column. The header is automatically updated to “Project Name”.
• In the second column, right click and pick Text Box properties

TextBox_Prop

 

Enter expression in Value

=iif(Fields!MilestonesName.Value<>"" OR NOT (Fields!MilestonesName.Value is nothing),"t",nothing)

Enter [MilestonesName] into ToolTip.
Click on “Font” tab

TextBox_Font_Prop

Click on “fx” button under Font and enter

=iif(Fields!MilestonesName.Value<>"" OR NOT (Fields!MilestonesName.Value is nothing),"Wingdings","Arial")

Click on “Fill” tab

Fill

Click on Fx Button under Fill Color and enter


=iif(Fields!ProjectInProgress.Value = 1,"LightBlue",nothing)

Click on OK.
• Decrease the width of the second column

MilestonesTimeLineView

12. Save and Run the report after selecting parameters. The diamond shape is displayed in cells where Milestone exist.

MilestonesTimeLineView_Report

Hovering over the diamond shape displays information regarding the milestone

Capture

 

SQL Get Financial Year (April to March)

The following query will return the financial year starting April to March

YEAR(DATEADD(Month,-((DATEPART(Month,StartDate)+8) %12),StartDate))

SELECT YEAR(DATEADD(Month,-((DATEPART(Month,’2014-03-31′)+8) %12),’2014-03-31′))
SELECT YEAR(DATEADD(Month,-((DATEPART(Month,’2014-04-01′)+8) %12),’2014-04-01′))
SELECT YEAR(DATEADD(Month,-((DATEPART(Month,’2014-12-31′)+8) %12),’2014-12-31′))
SELECT YEAR(DATEADD(Month,-((DATEPART(Month,’2015-03-31′)+8) %12),’2015-03-31′))
SELECT YEAR(DATEADD(Month,-((DATEPART(Month,’2015-04-01′)+8) %12),’2015-04-01′))

The results are

 

FinancialYearAprilMarch.JPG

Passing Date Parameter To SubReport SSRS SharePoint Integrated Mode

When SSRS is rendered in SharePoint Integrated Mode if there is a drop down parameter of type DateTime and it is passed to a parameter of DateTime in the SubReport, the subreport fails to load when rendering in browser though it loads correctly from reportbuilder.

Again much pain, I decided to convert the date parameters into Text as the parameters were drop down displaying in dd/MM/yyyy format. The SQL query to populate the parameter

SELECT CONVERT(nvarchar(10),Min(TimeByDay),103) DisplayDate
,Convert(date,Min(TimeByDay)) StartTime
,CONVERT(VARCHAR(10), Min(TimeByDay), 112) FormatStartTime
FROM [dbo].[MSP_TimeByDay]
where Year(TimeByDay) = 2015
GROUP BY MONTH(TimeByDay), Year(TimeByDay)
order by StartTime Asc

Results of query
DisplayDate StartTime FormatStartTime
01/01/2015 2015-01-01 20150101
01/02/2015 2015-02-01 20150201
01/03/2015 2015-03-01 20150301
01/04/2015 2015-04-01 20150401
01/05/2015 2015-05-01 20150501
01/06/2015 2015-06-01 20150601
01/07/2015 2015-07-01 20150701
01/08/2015 2015-08-01 20150801
01/09/2015 2015-09-01 20150901
01/10/2015 2015-10-01 20151001
01/11/2015 2015-11-01 20151101
01/12/2015 2015-12-01 20151201

The SQL function CONVERT(VARCHAR(10), Min(TimeByDay), 112) returns date in yyyyMMdd format which can be passed to a date parameter of dataset without converting to a date object.

The date parameter of the main report and sub report is set in a similar fashion

DateParameter

And date parameters are just passed to the subreport without any conversion.

SubreportDt

The main report is rendered in SharePoint Integrated mode and report builder without any issues.