manjunathcbhat's blog

Migration/Restoration of Microsoft SQL Server ReportServer Database

Microsoft SQL Server Reporting Services is one of the most powerful reporting tools available in the Market. Many of the small scale to Large scale enterprise organisation use Microsoft SQL Server Reporting Services. I am not going to describe more on reporting services and its features as tons of resources are available online.

ReportServerError: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.Invalid object name ''. Could not use view or function 'ExtendedCatalog' because of binding errors.

There will be often Business Needs to Have your Report Server Migrated Moved or Restored onto Different Environment. You May come up with Different Errors Post Restoration. Here i am going to describe one such Error. You May get the below error when you have your ReportServer Databases Restored

Backup Database Error SQL Server 2012: This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally

Microsoft SQL Server 2012 has introduced a new feature called AlwaysOn High Availability. There are numerous resources online to get information on AlwaysOn High Availability. So here i am not going in depth with features of this.

Will be coming up with some interesting post on this topic later. But for those you have already configured this feature, you might run into below error while running a backup command.

"This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally"

"dbcc checkdb('') with no_infomsgs" failed with the following error: "The In-row data USED page count for object "", index ID , partition ID , alloc unit ID (type In-row data) is incorrect. Run DBCC UPDATEUSAGE

As most of us are aware that DBCC CheckDB is one of the most important DBCC Command which is used to check for database integrity and corruption and consistency.

DBCC CheckDB may come up with many Error but today i will be going through one such error.

The error is as mentioned below.

"dbcc checkdb('') with no_infomsgs" failed with the following error: "The In-row data USED page count for object "", index ID , partition ID , alloc unit ID (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

Below mentioned are the some of the possibilities for this error.

SSAS Deployment Failed - Error 3 The project could not be deployed to the '' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

In one of my Old Post, I posted a SSAS Deployment failed because of insufficient rights for a User to deploy the cube/project to SSAS Server.

Now is this Post we will see another scenario where the SSAS Deployment Fails.

When you are done with developing your Cube/project you provide all your server setting, user credentials etc. and when you click on deploy bang.. You get this error.

SSAS Deployment Failed - Error 3 Either the '' user does not have permission to create a new object in '', or the object does not exist. 0 0

This is one of the Most common error a user might face during deploying a SSAS(SQL Server Analysis Services) Cube.

"Error 3 Either the '' user does not have permission to create a new object in '', or the object does not exist. 0 0"

This error occurs because the user who is deploying a cube is not having access to SQL Server Analysis Services(SSAS)

You can follow the below step by step Guide to provide access to SSAS to a User.

Step 1.

Connect to SSAS Server using SQL Server Management Studio.

Step 2.

Upgrade SQL Server 2000 Database to SQL Server 2012

Even though we are now into SQL Server 2012, 2008R2 etc, but still many organizations have their SQL Server running on 2000 and may want to Move some databases to SQL Server 2012.

This may seem a bit normal, yes but there is a little Trick.

When you Try to Take a Backup of a Database which is running on SQL Server 2000 version and try to Restore on to SQL Server 2012 Version you may encounter below error.

TSQL to Get all details of who created the SQL Server Login on a SQL server Instance

As a DBA, One of the most important task is to audit the security.

Very Often, we may come across situation where logins are created on SQL server Instance but we are not aware of any details on who created it, when it was created etc.

So here a small simple query to all such details.

Firstly we need to get the path of default Trace which is running on SQL Server Instance,
The below query will assist you in getting that path.
 

T-SQL to backup all databases or selected databases to specific location

Very often we come across situation where we need to backup database in bulk. Sometime when you are migrating a server or upgrading to different version or applying patch and to be on safer side you tend to take backup of all database. imagine you have more than 100 databases on server and backing up each database would be tedious task

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup

Script to change collation of multiple columns in multiple table for all tables in database

Various time we come across situation where in lower environment the collation will be different and in production environment the collation will be different. This might hamper you work in many ways. Your development work may come to halt or slowdown making these changes.

So here is a small script to change collation of various columns for all table. If you want for some columns and some table the select statement in cursor needs to tweaked.

Pages