SSRS - Page Break

Customize Page Break
1)Right Click the data row, choose Add Group -> Row Group - Parent Group.

SSIS Package Protection Level

Each SSIS component designates that an attribute is sensitive by including Sensitive="1" in the package XML.
An OLE DB Connection Manager specifies that the database password is a sensitive attribute as follows:
<DTS:Password DTS:Name="Password" Sensitive="1"></DTS:Password>

An DYNAMICS-CRM Connection Manager specifies that the database password is a sensitive attribute as follows:
<ServerPassword Sensitive="1"></ServerPassword>

SQL Server Reporting Service (SSRS)

SQL Server Reporting Service (SSRS)

1. SSRS is a bigger software application. SSRS is part of SQL server since SQL SERVER 2005. 
2. The SSRS installation doesn’t need to have SQL Database in one box, but SSRS still needs to connects to an existing SQL server database instance. 
3. All the SSRS configurations and reports definitions are eventually saved in the database, the configuration database for SSRS by default named as “ReportServer” and “ReportServerTempDB”. 
4. Good practice to keep SSRS Report engine / Report definitions & web pages are at web server, i.e., n-tier enterprise solution.
5. In SQL Server 2005, SSRS does need IIS to be together in one box, because it needs IIS to host its web service / portal interface. 
6. In SQL 2008, SSRS has its own embedded web server, and doesn’t need IIS anymore.
7. SSRS can be setup as totally independent server, no AD/Domain at all, although AD/Domain setup can bring it to normal enterprise security level.

SSRS web management portal is for admin works (such as setup data source, online folders, security, adjust reports parameters, schedules etc.).  More info SSRS Web Management Portal - User Roles and Permissions

Display SSRS Report in ASP.NET Web Page

1)Open VS 2010 and Create a ASP.NET Web Application project.

2)Add a ScriptManager (AJAX Externsions), ReportViewer (Reporting) and Button (Standard) control from the toolbox in the Default.aspx page.

3)Double Click on the button, to add the following code in the button event.

protected void Button1_Click(object sender, EventArgs e)
{
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://localhost/ReportServer");
ReportViewer1.ServerReport.ReportPath = "/Report Project2/Report4";
ReportViewer1.ServerReport.Refresh();
}

Deploying a SSRS report to the Server, SSRS Web Management Portal - User Roles and Permissions

1)Right click on the project. Select properties and there you will get to see the Deployment properties and you should see the TargetServerURL property as shown ihe below image. Set the TargetServerURL value (In the Reporting Services Configuration Tools dialog box, click Web Service URL in the left pane, the URLs below Report Server Web Service URLs just is the URL you should use as the TargetServerURL.)

2)Click on the ‘solution explorer project name’ and right click to select the Deploy option to publish the report to the server.

Configuring SQL Server Reporting Services (SSRS) in Windows Server 2008

To deploy/access the report on a server, need to have the Report Server up and running. For this purpose, do some configuration for the report server.

1.Go to All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> Reporting Services Configuration Manager(RSCM), RSCM will open as shown in the screen below. Select the appropriate Server and Instance Name and click on Connect as shown in the screen below.

2.Once connected successfully, you can see the Reporting Services Status as shown in the screen below.

SQL Server Reporting Services (SSRS) - Design Report

Designing a report using Report Wizard

1)Programs -> Microsoft SQL Server 2008/2008 R2 -> SQL Server Business Intelligence Development Studio.

2)Create a new Project
Open File -> New -> Project (a list of templates will be display) -> Report Server Project Wizard -> Press OK button to create the project.

SQL Server 2008 R2 Maintenance Plan

This maintenance plans to clean up the database, make backups (data and log), and do some performance optimization.

Weekly Database Maintenance Plans

1.Start the Microsoft SQL Server Management Studio and log in as the ‘sa’ user. Open the “Management” -> “Maintenance Plans” -> Select “Maintenance Plan Wizard.”

2.On the “Select Plan Properties” page give it the name “Weekly Maintenance Plan”. Select “Single schedule for the entire plan or no schedule” and click the “Change” button to set schedule. Click Next.

SQL Server - BLOB Import and Export

SQL scripts to insert File to BLOB field and export BLOB to File

1. Using SQL server 2008, you can saved images / files to BLOB binaries and retrieve back them to file system.
2. Import
   Test table structure:
   
   CREATE TABLE [dbo].[TestBlob](
   [tbId] [int] IDENTITY(1,1) NOT NULL,
   [tbName] [varchar](50) NULL,
   [tbDesc] [varchar](100) NULL,
   [tbBin] [varbinary](max) NULL
   ) ON [PRIMARY]
3. Insert file to BLOB test table is fairly easy. Open Microsoft SQL server management studio, run the below script, script is inserting one pdf, doc, image and exe fil.
   
   Insert TestBlob(tbName, tbDesc, tbBin) Select '81.pdf','PDF file', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\81.pdf', Single_Blob) as tb
   
   Insert TestBlob(tbName, tbDesc, tbBin) Select 'mountain.jpg','Image jpeg', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\mountain.jpg', Single_Blob) as tb
   
   Insert TestBlob(tbName, tbDesc, tbBin) Select 'Questionnaire.docx','Doc Question', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\Questionnaire.docx', Single_Blob) as tb
   
   Insert TestBlob(tbName, tbDesc, tbBin) Select 'txpeng542.exe','Texpad Exe', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\txpeng542.exe', Single_Blob) as tb

HierarchyID Data type, sp_spaceused

HierarchyID Data type - enables a tree structure, modeling an organizational structure, representing of a file system, representing a set of tasks in a project, modeling a graph of links between web pages.

Methods
child.GetAncestors(n) - returns a hierarchyid representing the nth ancestors
parent.GetDescendant(child1, child2) - returns a child node
node.GetLevel() - returns an integer value representing the depth of the node in the tree.
HierarchyID::GetRoot() - returns the root of the herarchy
parent.IsDescendant(child) - is used to find out if a node is descendant of another node
HierarchyID::Parse(input) - converts a string representation of a herarchyid to a herarchyid value
node.Reparent(oldRoot, newRoot)
node.ToString()

create table tblFolders(OrgNode HierarchyID, FolderName VARCHAR(MAX))

--insert root
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(HierarchyID::GetRoot(),'C:\')

--insert second row-child of root folder
DECLARE @Root HierarchyID;
SELECT @Root = HierarchyID::GetRoot() FROM tblFolders;
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Root.GetDescendant(NULL, NULL),'C:\Program Files')

--insert third row-child of root folder
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Root.GetDescendant(CAST('/1/' AS HierarchyID), NULL),'C:\windows')

--insert a child for the 'C:\Program Files' folder
DECLARE @Parent HierarchyID;
SELECT @Parent = CAST('/1/' AS HierarchyID)
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Parent.GetDescendant(NULL, NULL),'C:\Program Files\Microsoft SQL Server')

select OrgNode.ToString(), * from tblFolders

/ C:\
/1/ 0x58 C:\Program Files
/2/ 0x68 C:\windows
/1/1/ 0x5AC0 C:\Program Files\Microsoft SQL Server

CREATE TABLE SimpleDemo (Level hierarchyid NOT NULL, Location nvarchar(30) NOT NULL, LocationType nvarchar(9) NULL);

INSERT SimpleDemo
VALUES ('/1/', 'Europe', 'Continent'), ('/2/', 'South America', 'Continent'), ('/1/1/', 'France', 'Country'),
('/1/1/1/', 'Paris', 'City'), ('/1/2/1/', 'Madrid', 'City'), ('/1/2/', 'Spain', 'Country'),
('/3/', 'Antarctica', 'Continent'), ('/2/1/', 'Brazil', 'Country'), ('/2/1/1/', 'Brasilia', 'City'),
('/2/1/2/', 'Bahia', 'State'), ('/2/1/2/1/', 'Salvador', 'City'), ('/3/1/', 'McMurdo Station', 'City');

SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;

INSERT SimpleDemo VALUES ('/1/3/1/', 'Kyoto', 'City'), ('/1/3/1/', 'London', 'City');
SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;

INSERT SimpleDemo
VALUES ('/', 'Earth', 'Planet');


exec sp_spaceused 'Sales.Customer'
exec sp_spaceused 'Sales.Store'