SSRS 2008R2-Tablix report header not repeating

Error: Tablix report header not repeating

Solution: Follow the below steps to show report header in the tablix reports.

1. Go to the Column Groups and right click the triangle ->Then check the Advanced Mode.

2. Next Come to the Row Groups and Click F4 to see the properties.

3. Set the following properties in the tablix member as per the highlighted green colour box.

·         Fixed data to ‘True’

·         Keep with Group to 'After'

·         Repeat on New Page to 'True'

5. Then Save the report and Preview.

As expected, the report features like Repeat headers on Each Page and header visible while scrolling is working fine!

Remove characters from left of string-SSRS 2008R2-Expression-Tablix Report

Scenario: I am looking to remove the characters value ‘SQL Server’ from the ‘SQL Server 9.00.4060.00’   in one column on the tablix report in SSRS report. How do we achieve that output?

Solution: Go to Business Intelligence (SSRS) ->File->New Project->Report server project.

Then Drag and Drop table control ->Drag and drop the required columns from the dataset (Set Query for fetching data) to show in the table report.

Next right click the data value of the specific column to change the expression as like below.

Then enter the expression values as like below.

=Right( Fields!products.Value, len(Fields!products.Value) - 10)

·         Fields!products.Value is the datavalue in the tablix column.

·         Right is used to remove the left side characters based on the second parameters value.

·         10 is the number of characters removed from the actual value.

Finally click ok.

Then preview the report as shown below. In the below tablix report output, SQL server is removed and only ‘2005 Service pack3 ‘is coming.

Output:

SQL Server 2008R2:Database Snapshot uses --Database Mirroring

How to verify the data is mirrored from principal database to mirror database in the existing DATABASE MIRRORING (use of DATABASE SNAPSHOT).

Step1: Create a new table in the principal server database and insert data into it by the following statement.

USE [Kumar]

GO

/****** Object: Table [dbo].[Tbl_mirror_test] Script Date: 02/14/2013 19:48:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Tbl_mirror_test](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Event] [nvarchar](50) NULL

) ON [PRIMARY]

GO

INSERT INTO [Kumar].[dbo].[ Tbl_mirror_test]

([Event])

VALUES

(‘Kabbadi Match’)

GO

Step 2: Then create a database snapshot in the mirror server (Ensure your SQL server supports database snapshot feature) by the following statement.

CREATE DATABASE SnapshotDB007 ON

( NAME = Kumar_Data, FILENAME = 'G:\ Kumar _Data_22.ss' )

AS SNAPSHOT OF Kumar

GO

/************

SnapshotDB007--> the snaphshot name

Kumar_Data-->logical file name for the mdf file of Kumar database.

G:\ Kumar _Data_22.ss-->you can mention any filename in any path to store snapshot.

SNAPSHOT OF Kumar -->specifies which database needs to be snapshot**********/

Step 3: Fetch the updated data by executing the following statement in the mirror server.

select * from SnapshotDB007.dbo.Tbl_ mirror _Test

Output:

Kabbadi Match

If you try to drop the database 'Kumar',it wont allow to drop until you remove the associated database snapshot by the below method.

DROP DATABASE Kumar

Error:

Msg 3709, Level 16, State 2, Line 2

Cannot drop the database while the database snapshot "SnapshotDB" refers to it. Drop that database first.

USE [master]

GO

/****** Object: Database [SnapshotDB] Script Date: 02/14/2013 13:32:28 ******/

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SnapshotDB')

DROP DATABASE [SnapshotDB]

GO

Note:

We have no options to create database snapshot by wizard in the Sql Server Management Studio.(Refer the below image).

Hope you can verify the database mirroring data by the above method.If you know any other method,please Submit a comment!!!!!!!

SQL Server:Best practice to set Database Owner name.

/*************How to find the owner of all the databases to match with database creation standards i.e. it should be in non expiry sql logins.

If we give any employee windows login, then the validity of the login will come to an end once the employee leave his job. In order to avoid those issues in future, we need to set owner of the database to 'SQL Login'*****************/

--'00000007' IS THE SQL AUTHENTICATED LOGIN for database owner. You can replace your login with ‘00000007'.

SELECT name AS DATABASE_NAME,owner_sid FROM SYS.databases

WHERE name IN(

SELECT SD.NAME AS DATABASE_NAME

--SL.loginname AS OWNERNAME

FROM MASTER..SYSLOGINS SL JOIN

SYS.databases SD

ON SL.SID=SD.OWNER_SID WHERE SL.loginname='00000007')

/***********How to find the owner of all the databases which doesn't match with database creation standards

i.e it should be in non expiry sql logins.*************/

SELECT name AS DATABASE_NAME,owner_sid FROM SYS.databases

WHERE name NOT IN(

SELECT SD.NAME AS DATABASE_NAME

--SL.loginname AS OWNERNAME

FROM MASTER..SYSLOGINS SL JOIN

SYS.databases SD

ON SL.SID=SD.OWNER_SID WHERE SL.loginname='00000007')

'00000007' IS THE SQL AUTHENTICATED LOGIN for database owner. You can replace your sql login with ‘00000007'.

/*************To find the non-standard database owner name by passing the above script output to below sid parameter value.

OWNER_SID SHOULD BE LIKE-'0x0105066666600051500000100'*************/

select name as owner_name from MASTER..syslogins where sid='input the parameter value'

/********How to change the owner of the database into database creation standards owner name? **************/

USE DATABASE_NAME

GO

select name as owner_name from MASTER..syslogins where sid=0x01

GO

--SP_CHANGEDBOWNER 'NEW_OWNER_NAME'

SP_CHANGEDBOWNER '00000007'

Hope you can maintain the owner name for all database as one sql login to follow the database creation standards by the above method.

SQL2008R2:Steps to Restore the principal database in mirroring!!!!!!!!!!

Error: Msg 3104, Level 16, State 1, Line 1

RESTORE cannot operate on database 'Mirroringdatabase' because it is configured for database mirroring.

Use ALTER DATABASE to remove mirroring if you intend to restore the database.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Solution:

Follow the below steps to restore the principal database

1. Switch of the mirroring

ALTER DATABASE [Mirroringdatabase]

SET PARTNER OFF

2. Restore the principal database.Once restore is completed Then Take the Backup of the Principal Database and Transaction Log copy over to Mirror Server

RESTORE DATABASE [Mirroringdatabase] FROM DISK = N'X:\Mirroringdatabase\Mirroringdatabase_Full_TSQL.bak'

WITH FILE = 1,

MOVE N'Mirroringdatabase_log' TO N'H:\Microsoft SQL Server\MSSQL.INST01\Data\Mirroringdatabase_1.ldf',

RECOVERY, NOUNLOAD, STATS = 10

GO

3. Set Partner on Mirror server

--Run below script in mirror server

ALTER DATABASE Mirroringdatabase

SET PARTNER = 'TCP://PRINCIPALSERVER.ENVIRONMENT.DOMAIN.NET:5022'

--Mirror database changed into recovery from restoring process..

4. Set Partner on Master server

--Run below script in principal server

ALTER DATABASE Mirroringdatabase

SET PARTNER = 'TCP://MIRRORSERVER.environment.domain.net:5022'

I have got the below error while running the above script.

Error : Msg 1408, Level 16, State 0, Line 1

The remote copy of database "Mirroringdatabase" is not recovered far enough to enable database mirroring.

Because the transaction Log file that you restore on your mirror database is not new and fresh enough to catch the primary database. So, backup the full and log from the primary database and restore it on the mirror database in the possible shortest time and try starting the database mirroring.

RESTORE DATABASE [Mirroringdatabase] FROM DISK = N'X:\Mirroringdatabase\Mirroringdatabase_Full_TSQL.bak'

WITH FILE = 1,

MOVE N'Mirroringdatabase_log' TO N'H:\Microsoft SQL Server\MSSQL.INST01\Data\Mirroringdatabase_1.ldf',

NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE LOG [Mirroringdatabase] FROM DISK = N'X:\Mirroringdatabase\Mirroringdatabase_TLog_TSQL.trn'

WITH FILE = 1,

NORECOVERY, NOUNLOAD, STATS = 10

GO

5. Repeat the steps 3 and 4 to set partners for mirroring.

Hope you can restore the principal database without no issues.!!!