Problem

After connecting to a SQL Server 2012 instance using SQL Server Management Studio 2008 or 2008 R2, and trying to display different objects (views, tables, etc) the following error is displayed:

Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)

Solution

The problem is generated due to an error in SQL Server Management Studio 2008 & 2008 R2.

Full story »

Problem

When trying to see the execution plan from SQL Server 2008 R2 SP1 version of Management Studio, the plan is displayed in XML instead of graphical format.

For example, using the below DMVs, when trying to see the execution plan by clicking on the results, you will see the XML execution plan:

SELECT top 10 p.query_plan
   FROM sys.dm_exec_query_stats AS s
   CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p

Note that this happens only when using SQL Server 2008 R2 version of Management Studio, after applying SP1.

Solution

Method 1

You can save the XML as a .SQLPLAN file, open it using the Management Studio and you will see the graphical representation of the execution plan.

Method 2

This problem is solved in the next versions of SQL Server, so if you have somewhere else installed SQL Server 2012, you can replace the showplanxml.xsd file from the SQL Server 2008 R2 SP1 installation with a newer version of the file from the SQL Server 2012 installation.

Full story »

Problem

When trying to restore a backup over an existing database, the following error appears:

RESTORE DATABASE is terminating abnormally. The tail of the log for the database "DATABASE_NAME" has not been backed up. 

Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose.

Solution

 Check if the database is in full recovery model. If it is, the message warns that if the database is restored, there are transactions that will be lost.

Full story »

Problem

When trying to create a login the following error appears:

USE [master]
GO
CREATE LOGIN [Domain\LoginName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

 Msg 15025, Level 16, State 2, Line 1

The server principal ?Domain\LoginName? already exists.

Solution

First check if the login is already created:

select * from sys.server_principals where name LIKE '%Domain\LoginName%'

(0 row(s) affected)

In my situation the login wasn?t already created so the only thing left was to check if there was some other login created on this instance with the same SID.

Full story »

Problem

When running an SSIS package from a SQL Server job the following error appears:

?The task "Script Task" cannot run on installed (64-bit) of Integration Services. It requires Standard Edition (64-bit) or higher?

Solution

Usually this error appears when the SSIS service is not installed on the machine running the package. Although you have Workstation Components / Client Tools installed and you can create, debug and run packages using BIDS (Business Intelligence Development Studio), to execute the same packages from SQL Agent you need to have the SSIS service installed.

Full story »