All posts by SuneethaKB

SPID showing High CPU Memory in SQL Server

Below is the query that tells the information about the SPID which has high Memory Usage in SQL Server.

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

Script for Publishing/Deploying Reports in ReportServer

Automation of Deploying Reports in Report Server

In this document we can see the automation of Deploying Reports in the Report Server.
Pre-requisites:

 PowerShell Script file (Step -1) / Batch file to call .rss file (Step – 3).
 .rss Script file (Step -2).
 RS Utility installed on the server.
 Powershell installed on the server.
Arguments hard coded in PowerShell / Batch script file:

 TargetURL
 ServerName
 DatabaseName
Arguments given by user in Poweshell/Batch script file:

 ReportName
 SourceReportFolder
 TargetFolder
 DataSourceFolder
 DataSourceName

Script Files
Step – 1: Powershell script to call .rss file

Copy the below code and save the file with .ps1 extension:
# ‘************************************************************************
# Created on :
# Titlte : Script to publish report
# Created By :
# Dependent File that need to be placed to run this script: (Enter the .rss script filename (see step2))
# ‘************************************************************************

# ************
# Clear Screen
# ************
Clear-Host

# *****************************
# Assigning values to Variables
# *****************************
$TargetURL = “http://localhost/reportserver”
$ServerName=””
$DataBaseName=””

# ***************************************
# Assigning values to Variables from user
# ***************************************
#$ReportName=”ReportName”
$ReportName = Read-Host ‘Enter the Report Name (Exclude Extension)’

#$SourceReportFolder=”D:\TestReports\Reports”
$SourceReportFolder = Read-Host ‘Enter the Source path for the Report ‘

#$TargetFolder=”/TestReports ”
$TargetFolder = Read-Host ‘Enter the Target path for the Report ‘

#$DataSourceFolder=”/ Data Sources”
$DataSourceFolder = Read-Host ‘Enter the SourceFolder for DataSources ‘

#$DataSourceName=” DSName ”
$DataSourceName = Read-Host ‘Enter the DataSource Name for the Report ‘

# **********************************************
# Assing values from the variables to rs Utility
# **********************************************
Write-Host ‘Please wait while the Report is deploying….’

rs -i TestReports.rss -s $TargetURL -v TargetFolder=$TargetFolder -v SourceReportFolder=$SourceReportFolder -v DataSourceFolder=$DataSourceFolder -v DataSourceName=$DataSourceName -v ServerName=$ServerName -v DataBaseName=$DataBaseName -v ReportName=$ReportName

Step – 2: .rss file which has the script for publishing Report in the server

Copy the below code and save the file with .rss extension:
‘******************************************************************
‘Created on:
‘Tittle: Script to publish report
‘Created By:
‘******************************************************************

‘Main method is used to call the methods to create folder, Reports, DataSource, and assign the datasource to the reports
Public Sub main()
Try
‘******************************************************************
‘Variables Declaration
‘******************************************************************
Dim ReturnValue As Integer = 0
Dim FileName As String
Dim Indexchar As Integer
Dim DataSourceFolderPath As String
Dim DataSourceFolderName As String
Dim TargetReportPath As String
Dim TargetReportFolderName As String
‘******************************************************************
‘Assigning DataSourceFolderName from DataSourceFolder variable
‘******************************************************************
Indexchar = DataSourceFolder.LastIndexOf(“/”)

‘Getting DataSourceFolderPath
DataSourceFolderPath = DataSourceFolder.Substring(0,Indexchar)

‘Getting DataSourceFolderName
DataSourceFolderName = DataSourceFolder.Substring(Indexchar + 1)
‘******************************************************************
‘Checking whether DataSource Folder Exists or not
‘******************************************************************
ReturnValue = CheckItemAvailability(DataSourceFolderPath, DataSourceFolderName, “Folder”)
If ReturnValue = 0
‘CreateFolder (DataSourceFolderName, DataSourceFolderPath, “”, “False”)
Else
Console.Writeline(“Information: DataSourceFolder ‘”+ DataSourceFolderName + “‘ already Exists..” )
End If
‘******************************************************************
‘Assigning TargetFolderName from TargetFolder variable
‘******************************************************************
Indexchar = TargetFolder.LastIndexOf(“/”)
‘Getting TargetFolderPath
If(Indexchar = 0)
TargetReportPath = TargetFolder.Substring(0,Indexchar+1)
Else
TargetReportPath = TargetFolder.Substring(0,Indexchar)
End If

‘Getting TargetFolderName
TargetReportFolderName = TargetFolder.Substring(Indexchar + 1)
‘******************************************************************
‘ create a folder to deploy the reports
‘******************************************************************
ReturnValue = 0
ReturnValue = CheckItemAvailability(TargetReportPath, TargetReportFolderName, “Folder”)
If ReturnValue = 0
CreateFolder (TargetReportFolderName, TargetReportPath, “”, “False”)
Else
Console.Writeline(“Information: TargetReportFolder ‘” + TargetReportFolderName + “‘ already Exists..” )
End If
‘******************************************************************
‘ Create the datasources if it does not exist
‘******************************************************************
ReturnValue = 0
ReturnValue = CheckItemAvailability(DataSourceFolder, DataSourceName, “DataSource”)
If ReturnValue = 0
CreateDataSource (DataSourceFolder, DataSourceName, ServerName, DataBaseName)
Else
Console.Writeline(“Information: DataSource ‘” + DataSourceName + “‘ already Exists..”)
End If
‘******************************************************************
‘Get the SourceReportFolder path to create the report
‘******************************************************************
ReturnValue = 0
ReturnValue = CheckItemAvailability(TargetFolder, ReportName, “Report”)
‘******************************************************************
‘ Delete the existing Report in the same path
‘******************************************************************
If ReturnValue = 1
DeleteItem (TargetFolder, ReportName)
Else
Console.WriteLine(“Information: No existing Report ‘” + ReportName + “‘ to delete”)
End If
‘******************************************************************
‘ Publish the report
‘******************************************************************
FileName = SourceReportFolder + “\” + ReportName + “.rdl”
PublishReports(FileName, ReportName, TargetFolder)
‘******************************************************************
‘ Set the DataSource for the published report
‘******************************************************************
ReturnValue = 0
ReturnValue = CheckItemAvailability(DataSourceFolder, DataSourceName, “DataSource”)
‘******************************************************************
‘ Set Datasource to the deployed report if the datasource exists
‘******************************************************************
If ReturnValue = 1
SetDataSource(TargetFolder, ReportName , DataSourceFolder, DataSourceName)
Else
Console.Writeline(“Information: DataSourceName ‘” + DataSourceName + “‘ DataSource does not exist”)
End If
Catch e As Exception
Console.Writeline (e.message)
End Try
End Sub
‘******************************************************************
‘Create the Folder to deploy the reports and Datasources
‘******************************************************************
Public Sub CreateFolder(ByVal folderName As String, ByVal folderPath As String, ByVal description As String, ByVal hidden As String)
‘******************************************************************
‘Common CatalogItem properties
‘******************************************************************
Dim descprop As New [Property]
Dim hiddenprop As New [Property]
Dim props(1) As [Property]
descprop.Name = “Description”
descprop.Value = description
hiddenprop.Name = “Hidden”
hiddenprop.Value = hidden
props(0) = descprop
props(1) = hiddenprop
Try
‘******************************************************************
‘Passing the Folder Name, Path of the Folder and Basic Properties
‘******************************************************************
rs.CreateFolder(folderName, folderPath, props)
Console.WriteLine(“Information: TargetReportFolder ‘” + folderName + “‘ Created Successfully”)
Catch e As Exception

Console.Writeline(e.Message)
End Try
End Sub

‘******************************************************************
‘Create Data sources to access the source
‘******************************************************************
Private Sub CreateDataSource (ByVal DataSourcePath As String, ByVal DataSourceName As String, ByVal ServerName As String, ByVal DatabaseName As String)
Dim dsDefinition As New DataSourceDefinition()
dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
dsDefinition.ConnectString = “Data Source=”& ServerName &”;Initial Catalog=” + DatabaseName
dsDefinition.Enabled = True
dsDefinition.EnabledSpecified = True
dsDefinition.Extension = “SQL”
dsDefinition.ImpersonateUser = False
dsDefinition.ImpersonateUserSpecified = True
dsDefinition.WindowsCredentials = False
Try
rs.CreateDataSource(DataSourceName, DataSourcePath, false, dsDefinition, Nothing)
Catch e As Exception
Console.Writeline(e.Message)
End Try
End Sub

‘******************************************************************
‘Function to check the report availability
‘******************************************************************
Private Function CheckItemAvailability(ByVal ItemPath As String, ByVal ItemName As String, ByVal ItemType As String)
Dim folderItems As CatalogItem()
Dim folderItem As CatalogItem
Dim ReturnValue As Integer = 0
folderItems = rs.ListChildren(ItemPath, false)
For Each folderItem In folderItems
If ItemType = “Folder”
If folderItem.Name = ItemName And folderItem.Type = 1
ReturnValue = 1
End If
Else If ItemType = “Report”
If folderItem.Name = ItemName And folderItem.Type = 2
ReturnValue = 1
End If
Else If ItemType = “DataSource”
If folderItem.Name = ItemName And folderItem.Type = 5
ReturnValue = 1
End If
End If
Next
Return ReturnValue
End Function

‘******************************************************************
‘Function to publish the report to report server
‘******************************************************************
Private Sub PublishReports(ByVal FilePath As String, ByVal ReportName As String, ByVal TargetFolder As String)
Dim ReportDefinition As [Byte]() = Nothing
Dim warnings as Warning() = Nothing
Dim description As New [Property]
Dim properties(0) As [Property]
Try
‘******************************************************************
‘ Open rdl file
‘******************************************************************
Dim rdlfile As FileStream = File.OpenRead(FilePath)
ReportDefinition = New [Byte](rdlfile.Length – 1) {}
rdlfile.Read(ReportDefinition, 0, CInt(rdlfile.Length))
rdlfile.Close()
‘******************************************************************
‘Set Report Description
‘******************************************************************
description.Name = “Description”
description.Value = “”
properties(0) = description
‘******************************************************************
‘Create a Report
‘******************************************************************
warnings = rs.CreateReport(ReportName, TargetFolder, True, ReportDefinition,Properties)
Console.WriteLine(“Information: Report ‘” + ReportName + “‘ published successfully”)
Catch e as Exception
Console.Writeline (e.Message)
End Try
End Sub

‘******************************************************************
‘Function to assign the datasource for the report
‘******************************************************************
Private Sub SetDataSource(ByVal TargetFolder As String, ByVal ReportName As String, ByVal DataSourceFolder As String, ByVal DataSourceName As String)
Try
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim ReportPath As String = TargetFolder + “/” + ReportName
Dim DataSources(0) As DataSource
Dim DsRef As New DataSourceReference
Dim objDS As new DataSource
DsRef.Reference = DataSourceFolder + “/”+ DataSourceName
objDS.Item = CType (DsRef, DataSourceDefinitionOrReference)
objDS.Name = DataSourceName
DataSources(0) = objDS
rs.SetItemDataSources(ReportPath, DataSources)
Console.Writeline (“Information: DataSource ‘” + objDS.Name + “‘ is set to the report ‘” + ReportName + “‘”)
Catch e As Exception
Console.Writeline (e.Message)
End Try
End Sub
‘******************************************************************
‘Function to Delete the report from report server
‘******************************************************************
Private Sub DeleteItem(ByVal FolderName As String, ByVal ItemName AS String)
Try
rs.DeleteItem (FolderName + “/” + ItemName)
Console.WriteLine(“Information: Exisiting Report ‘” + ItemName + “‘ deleted Successfully”)
Catch e As Exception
Console.Writeline(e.Message)
End Try
End Sub
Step – 3: (Optional) Script of Batch file to call .rss file

Copy the below code and save the file with .bat extension:

REM ‘************************************************************************
REM Created on :
REM Titlte : Script to publish report
REM Created By :
REM Dependent File that need to be placed to run this script: (Enter the .rss script filename (see step2))
REM ‘************************************************************************

REM ************
REM Clear Screen
REM ************

cls

REM *********
REM @echo OFF
REM *********

REM *****************************
REM Assigning values to Variables
REM *****************************

set TargetURL=http://localhost/reportserver
Set ServerName=
Set DataBaseName=ReportServer

REM ***************************************
REM Assigning values to Variables from user
REM ***************************************
set ReportName= ReportName
REM set /P ReportName=Enter the Report Name (Exclude Extension) :

set SourceReportFolder=D:\TestReports\Reports
REM set /P SourceReportFolder=Enter the Source path for the Report :

set TargetFolder=/TestReports
REM set /P TargetFolder=Enter the Target path for the Report :

Set DataSourceFolder=/ Data Sources
REM Set /P DataSourceFolder=Enter the SourceFolder for DataSources :

Set DataSourceName=DSName
REM Set /P DataSourceName=Enter the DataSource Name for the Report :
REM **********************************************
REM Assing values from the variables to rs Utility
REM **********************************************

echo Please wait while the Report is deploying….

rs -i TestReports.rss -s %TargetURL% -v TargetFolder=”%TargetFolder%” -v SourceReportFolder=”%SourceReportFolder%” -v DataSourceFolder=”%DataSourceFolder%” -v DataSourceName=”%DataSourceName%” -v ServerName=”%ServerName%” -v DataBaseName=”%DataBaseName%” -v ReportName=”%ReportName%”

1 Comment

by SuneethaKB on November 12, 2013 • Permalink

Posted in SQL Server, SSRS, Technical

Tagged Automation for publishing reports, Batch file for publishing reports, Deploying Reports automatically, Powershell script for deploying reports, rss file for publishing reports

Posted by SuneethaKB on November 12, 2013

https://suneethasdiary.wordpress.com/2013/11/12/script-for-publishingdeploying-reports-in-reportserver/

Retreiving SP name using T-SQL Script

Some of us when executing a Store Procedure in SQL Server want to insert a record into the table with the current procedure name that is executed (this can especially useful when the procedure name is stored in the exception tables when there is an error in the execution of the SP)

Below is the query to find the current proc name using T-SQL Script:

SELECT ISNULL(OBJECT_NAME(@@PROCID), 'MYPROC')

In the SP , we can write as

INSERT INTO Exception_Table(ProcedureName)
SELECT ISNULL(OBJECT_NAME(@@PROCID), 'MYPROC')

Have a nice day !

Creating a Dynamic Query in Lookup Transformation in SSIS

We can create a Dynamic Query by using Lookup Transformation in SSIS. Below is the procedure for creating the Dynamic Query:

Create the tables in a database as below:
CREATE TABLE TEST_EMP(EID INT, ENAME VARCHAR(20), DEPTNO INT)
CREATE TABLE TEST_DEPT(DEPTNO INT, DNAME VARCHAR(20))

INSERT INTO TEST_EMP  VALUES(1, 'SUNEETHA', 10)
INSERT INTO TEST_EMP VALUES(2, 'NAGARAJU', 10)
INSERT INTO TEST_EMP VALUES(3, 'SUNIL', 20)
INSERT INTO TEST_EMP VALUES(4, 'GANGADHAR', 10)
INSERT INTO TEST_EMP VALUES(5, 'LAKSHMI', 30)
INSERT INTO TEST_EMP VALUES(6, 'BHARATHI', 20)
INSERT INTO TEST_EMP VALUES(7, 'SWARAJYA', 40)
INSERT INTO TEST_EMP VALUES(8, 'NAGESWAR', 30)

INSERT INTO TEST_DEPT VALUES(10, 'Data Group')
INSERT INTO TEST_DEPT VALUES(20, 'Purchasing')
INSERT INTO TEST_DEPT VALUES(30, 'Call Center')
INSERT INTO TEST_DEPT VALUES(40, 'Communication')

SELECT * FROM TEST_EMP
SELECT * FROM TEST_DEPT

Create a package in the SSIS and name the package as DynamicLookup:

Create two package variables as follows:
Right click on the area in the Control Flow tab, select Variables
Create two variables as follows:
Enter the value for DeptNo package variable as 10 and for Query package variable goto the properties by pressing F4.
In the properties window, make the EvaluateAsExpression property as “True”
In the Expression property select the Ellipses (…) button and enter as below
“Select Eid, Ename from Test_Emp where Deptno = ” + @[User::Deptno]
Click on Evaluated Value to cross verify whether you entered is correct or not, finally click on OK button

Drag and drop the Data Flow Task from Control Flow Items in the Toolbox to Control Flow tab.
Double click/ go to the Data Flow tab and Drag and Drop the OLEDB Source Task
Click on New button for creating the new connection to the SQL Server to connect to the database where the table is created and click on Test Connection to verify whether the connection is successful or not.

Select the table TEST_EMP from the list of tables in the database in the Connection Manager page

In the columns page check which columns are required as source and click on OK button.
Drag and drop the Lookup Transformation (named as LKP_DynamicQuery) from Data Flow Transformations in the toolbox to the Data Flow tab and connect the lookup to the OLEDB Source task that is already in the Data Flow tab.
In the Control Flow table right click on the Data Flow Task and go to the Properties window
Go to the Expressions property and click on Ellipse (…) button.
Select [LKP_DynamicQuery].[SqlCommand] from the Property Combo box in Property Expression Edition and click on Ellipse (…) button under expression column
From the Variables tree select the user variable Query in Expression Builder dialogue box, drag and drop the Query package variable in the Expression text field and click on Evaluate Expression for verification and click on OK button.
Click on OK button in the Property Expression Editor dialogue box.
Go to Menu bar –> select Build –> select Build SSIS Excercises (Here SSIS Excercises is my solution name.
In the above figure we can also see the properties of the Lookup which has SqlCommand property as empty value.
Once we build the solution we can see that SqlCommand property in the properties is filled with the dynamic query that we have given as expression in the properties for the Data flow task

Right click on the Lookup and select Show Advanced Editor for giving the connection to the database.

Select the Connection to the SQL Server from Connection Manager and Click on OK button.

Open the Lookup Task and in the General page select “Redirect rows to error output” from “Specify how to handle rows with no matching entries.

Now in the connection page we can see the dynamic Query as follows

Go to the columns page and select the mapping of the columns

And click on OK button

Drag and Drop the Flat File Destination from the Data Flow Destinations in the toolbox and link it with Lookup Task with Output as Lookup Match Output and click on OK button.

Double click on Flat File Destination Task and click on New for assigning the new connection to send the matched rows from the Lookup.

Click on New button to create new connection for the destination of the flat file.
Select the type of Format in the Flat File Format window. Here, we are selecting as Ragged Right and click on OK button.
Click on Browse button in the Flat File Connection Manager to enter the path of the file and filename that stores the data from the lookup.

Goto each and every page and verify the columns and at last click on OK button.
Go to the Mappings page in the Flat File Destination editor and select the Ename_LKP for selecting the ename that are matched in the Lookup and click on OK button.

Save and execute the package by pressing F5.

Now we can see the package is executed successfully.
If we open the Flat file that was created we can see as follows.

Hope this will be helpful….

11 Comments

by SuneethaKB on December 28, 2011 • Permalink

Posted in SQL Server, SSIS, Technical

Tagged Creating a Dynamic Query in Lookup Transformation in SSIS, Dynamic Lookup Transformation

Posted by SuneethaKB on December 28, 2011

https://suneethasdiary.wordpress.com/2011/12/28/creating-a-dynamic-query-in-lookup-transformation-in-ssis/

using the alias name in the update stmt using t-sql script

Today I have learnt that we can use the alias name in the Update statement in SQL Server. I thought it would be helpful if I post this. Below is the explanation for this

We have taken a table that need to be updated by joining multiple tables. Here, we can see that in the “Update table” statement we have used an alias name “emp”:

UPDATE emp SET 
       ename = temp.ename
FROM #WTemp_Emp temp
INNER JOIN dbo.TBL_Employee emp
   ON emp.EmpID=temp.EmpID

The above statement updates the ename in EMP table in the database by joining the two table emp and temp table (which contains the eid, ename information).

33 Comments

by SuneethaKB on December 12, 2011 • Permalink

Posted in DBA, SQL Server, Technical

Tagged Updating a table with the values from joining another table, using the alias name in the update stmt using t-sql script

Posted by SuneethaKB on December 12, 2011

https://suneethasdiary.wordpress.com/2011/12/12/using-the-alias-name-in-the-update-stmt-using-t-sql-script/

Search for:
Recent Posts
- 652,164 hits
Subscribe Here!!

Email Address:

Join 43 other subscribers
Archives
Archives
Categories
Categories
April 2024

M T W T F S S

1 2 3 4 5 6 7

8 9 10 11 12 13 14

15 16 17 18 19 20 21

22 23 24 25 26 27 28

29 30

« Feb

Suneetha's Blog

All posts by SuneethaKB

SPID showing High CPU Memory in SQL Server

Script for Publishing/Deploying Reports in ReportServer

Retreiving SP name using T-SQL Script

Creating a Dynamic Query in Lookup Transformation in SSIS

using the alias name in the update stmt using t-sql script

Recent Posts

Subscribe Here!!

Archives

Categories