Problem

When trying to execute a remote stored procedure through a linked server called "server_name"  the following error is received:

EXEC server_name.mydatabase.dbo.mysproc

Msg 7411, Level 16, State 1, Line 1
Server 'server_name' is not configured for RPC.

Solution

The problem is that RPC is not configured for your linked server (is not a default option). You can see the configured settings for the linked servers by executing exec sp_helpserver on the host  server. 

Full story »

Problem

After stopping a database backup taking to long to complete (over the network) the associated spid remained in killed/rollback status for several hours.

SPID XX: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0

Solution

As a general rule it is best to not attempt to kill any spid which is performing a database backup.

It seems that, sometimes, operating system level operation initiated from SQL Server can't be rolledback.

After spending quite a few time trying to get rid of the blocked spid the only solution that got me out of this situation was to restart the SQL Server Service.

Full story »

Problem

When trying to send emails from SQL Server using using the sp_send_dbmail stored procedure the following errors can appear:

Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 107

No global profile is configured. Specify a profile name in the @profile_name parameter.

or

Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62

Could not obtain information about Windows NT group/user ?DOMAIN\user_name?, error code 0x5.

This happens on SQL Server 2005 or 2008 even if the Database Mail feature is installed and configured (the Database Mail feature is enabled and the necessary account and profile is created).

Cause

This happens because there is no database mail profile set as "Global Profile".

Full story »

Connecting to a Remote Integration Services Server

When attempting to connect to a SQL 2005, 2008 or 2012 Integration Services instance with a user account that is not an windows administrator on the machine running the SSIS services, you receive the following message:

"Cannot connect to SSIS Server
Additional information: Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Connect to SSIS Service on machine "SSIS Server" failed: Access is denied."

OR:

When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

Full story »

Problem

"Generate Activation Context failed for C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\msmdctr90.DLL."

and

"Resolve Partial Assembly failed for Microsoft.VC80.CRT.mui. Reference error message: Access is denied."

Solution

To resolve this issue you have to give the "Performance Log Users" List Folder Contents permissions, which is on the Security tab of both of the following folder locations:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin

Full story »