Linked Server Transaction error Msg 7395, Level 16, State 2, Line 273

If you are using nested transaction in your stored procedure you will get the same error. Solution to this error is to SET XACT_ABORT  TO ON;  in your stored procedure.

[264] An attempt was made to send an email when no email session has been established

1. In SQL Server Management Studio, right-click SQL Server Agent and then select "Properties."
2. Click "Alert System"
3. Click "Enable mail profile"
4. Click "OK"5. Restart "SQL Server Agent"

Now you can test your sql jobs to get a email notification.

Operating system error 112(error not found) encountered

When you RESTORE database if error message says 112 then you must look at the first point to check enough disk free space on specific drive where database is going to restore.

Message

Error: 17053, Severity: 16, State: 1.

E:\MSSQL \DATA\Adventrueres.mdf: Operating system error 112(error not found) encountered.

The service could not be started. Reason: Service 'MSSQLServerOLAPService' start request failed

Problem:When i try reinstall Microsoft SQL Server Analysis Services it's keep failing.

Error Message: The service could not be started. Reason:  Service 'MSSQLServerOLAPService' start request failed.
 Configuration file:C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121217_132025\ConfigurationFile.ini
Detailed results:
  Feature:                       Analysis Services
  Status:                        Failed: see logs for details
  MSI status:                    Passed
  Configuration status:          Failed: see details below
  Configuration error code:      0xD448CDD2
  Configuration error description: The service could not be started. Reason:  Service 'MSSQLServerOLAPService' start request failed.
  Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121217_132025\Detail.txt

Workaround :
Open the Event Viewer(Administrative Tools) and Clear out all logs for
Application
Security
System
Finally restart your SQL Server Analysis Services via SQL Server Configuration Manager

References :http://richbackbi.wordpress.com/2010/12/03/mssqlserverolapservice-start-request-failed/
http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/dd04e9d8-e613-44bf-bf4a-a446efdc64aa

Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this

Error:
Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.

-- Table design
CREATE TABLE [dbo].[Sales_Summary_Test](
 [SaleID] [int] IDENTITY(1,1) NOT NULL,
 [OrderDate] [datetime] NULL,
 [ProductLine] [nvarchar](2) NULL, 
 [SubTotal] [numeric](38, 6) NULL) ON [PRIMARY]
GO

-- Creating  a View
CREATE VIEW dbo.vSumSales_Summary_TestByProductLine
     WITH SCHEMABINDING 
AS
SELECT s.ProductLine AS ProductLine,
 COUNT_BIG(*) AS NumberOfProductLine, 
 SUM(s.SubTotal) AS SubTotal
FROM [dbo].[Sales_Summary_Test] AS s
WHERE ProductLine IS NOT NULL
GROUP BY s.ProductLine
go

SELECT * FROM vSumSales_Summary_TestByProductLine
go

-- Create a Index for view
CREATE UNIQUE CLUSTERED INDEX SumSales_Summary_TestIndex
 ON dbo.vSumSales_Summary_TestByProductLine(ProductLine)
go

Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.

Solution:
You must change NULL column to NOT NULL column
CREATE TABLE [dbo].[Sales_Summary_Test](
 [SaleID] [int] IDENTITY(1,1) NOT NULL,
 [OrderDate]   [datetime] NOT NULL,
 [ProductLine] [nvarchar](2) NOT NULL,
 [SubTotal]    [numeric](38, 6) NOT NULL) ON [PRIMARY]
GO

Names must be in two-part format and an object cannot reference itself

When you try to create a Indexed view for the query you will get this following error message in this case. if you include database on from clause.

CREATE VIEW dbo.vSales_Summary_ProductLine
WITH SCHEMABINDING
AS
SELECT S.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(S.SubTotal) AS SubTotal
FROM [AdventureWorks].[dbo].[Sales_Summary] AS S
GROUP BY S.ProductLine
go

Msg 4512, Level 16, State 3, Procedure vSales_Summary_TestByProductLine, Line 4
Cannot schema bind view 'dbo.vSSales_Summary_ProductLine' because name 'AdventureWorks.dbo.Sales_Summary' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Solution :  You need to remove the database name from the above script

CREATE VIEW dbo.vSales_Summary_ProductLine
WITH SCHEMABINDING
AS
SELECT S.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(S.SubTotal) AS SubTotal
FROM [dbo].[Sales_Summary] AS S
GROUP BY S.ProductLine
go

Index was outside the bounds of the array

An error occurred while executing batch. Error message is: Index was outside the bounds of the array.
Today i come up with the above error on development box. A table is tiny and only 89 thousands rows. I found the fowllowing two links from microsoft site.
http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/d30194ae-9f6a-46ab-b054-6dc2ed9195c2
http://support.microsoft.com/kb/2459027
I am running SQL Server version Microsoft SQL Server 2008 R2 (SP1) - 10.50.2789.0 (X64)

I try to reproduce the same from created test database on the same error but it did not show up the same erro.

Resolution: Create a replica table of issue table and export the data that is works fine.

[SQLSTATE 42000] (Error 10060) OLE DB provider "SQLNCLI10" for linked server

[SQLSTATE 42000] (Error 10060)  OLE DB provider "SQLNCLI10" for linked server

Error Message
Executed as user: MyServer\Myaccount. TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. [SQLSTATE 42000] (Error 10060)  OLE DB provider "SQLNCLI10" for linked server "" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "MYSERVER" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412).  The step failed.
Open Component Services via

 Run then dcomcnfg


To resolve this problem, follow these steps:

1. Click Start, point to All Programs, point to Administrative Tools, and then click Component Services.
2. In the Component Services Wizard, expand Component Services, and then double-click Computers.
3. Right-click My Computer, and then click Properties.
4. Click the MS DTC tab, and then click Security Configuration.
5. In the Security Configuration dialog box, click to select the Network DTC Access check box.
6. Under Network DTC Access, click Network Transactions.
   
   Note If you installed Service Pack 1 for Windows Server 2003, you can click Allow Inbound and Allow Outbound.
7. Make sure that DTC Logon Account is set to NT Authority\NetworkService.
8. Click OK.
9. In the message box, click Yes to continue.
10. In the DTC Console Message dialog box, click OK.
11. In the System Properties dialog box, click OK.
12. Reboot the computer for these changes to take effect.
    
    Note In some cases, you must start the DTC service before you start the SQL Server service so that the linked server distributed queries work well.
13. Add above DTC configuration on both server
14. You need  add XACT_ABORT ON on your stored procedure

SQLServer Error: 15404, Could not obtain information

Date  25/06/2012 08:40:01
Log  SQL Server Agent (Current - 25/06/2012 08:47:00)
Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'servrername\SQLAgent', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin),

If you ever clone sql server from other server. you might end up with this above error when you run any sql server agent jobs. Because name of the current sql server and oringally installed sql server is totally different.

An attempt was made to send an email when no email session has been established

When you get this error message on your sql server agent Error Logs
Make sure to enable profiles on sql server agent propertise

1) Right click SQL Server Agent ---> Propertise
2) Select Alert Systems
3) Checkbox Enable Mail profile

Unable restart SQL Server services

Error: 9954, Severity: 16, State: 1

Message
SQL Server failed to communicate with filter daemon launch service  (Windows error: The service did not start due to a logon failure.). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

Solution:
If you have recently changed SQL Server service account password , Please make sure to update the password and restart the services this apply to all SQL Server Services components below.

SQL Server
SQL Servr Agent
SQL server Integration Services
SQL Server Analysis Services
SQL Server Reporting Services
SQL Server Browser
SQL Full-text Filter Daemon Lanuncer

Msg 3201, Level 16, State 2, Line 1 Cannot open backup device Operating system error 53

When you are in the situation to restore the database if you wrongly typed path name of backup device you will come accross the following error.

USE master
go
RESTORE DATABASE Adventureworks
FROM DISK = '\\SQLSVR001\Backup\Adventureworks.BAK'
WITH RECOVERY, REPLACE, STATS = 5

--Error
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\SQLSVR001\Backup\Adventureworks.BAK'. Operating system error
53(error not found).

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Solution:
-- Make sure to include the correct path name of backup file
RESTORE DATABASE Adventureworks
FROM DISK = '\\SQLSVR001\MSSQL\Backup\Adventureworks.BAK'
WITH RECOVERY, REPLACE, STATS = 5

At the end you will see
Msg: RESTORE DATABASE successfully processed 760350 pages in 144.614 seconds (43.071 MB/sec).

Error 3117 The log or differential backup cannot be restored because no files are ready to rollforward

-- Part 01
-- Check backup information
RESTORE FILELISTONLY   
  FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_FULL.bak'

-- Restore Database with FULL Backup
RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_FULL.bak'
WITH NORECOVERY

-- Part 02
RESTORE FILELISTONLY   
  FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_DIFF.bak'

-- Restore Database with  Differential Backup
RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_DIFF.bak'
WITH NORECOVERY

-- Part 03
RESTORE FILELISTONLY   
  FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_LOG_01.Log'

-- Restore Database with Log Backup 
RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_LOG_01.log'
WITH NORECOVERY

RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_LOG_02.log'
WITH NORECOVERY

-- Final log with Recovery
RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_LOG_03.log'
WITH RECOVERY

Error: 3041, Severity: 16, State: 1

Message:

BACKUP failed to complete the command BACKUP DATABASE db_name. Check the backup application log for detailed messages

Solution:  Make sure to take another full database backup.

Database RESTORE error Msg 3183, Level 16, State 2, Line 1

When you try to restore/verifyonly SQL backup you may be found this error some time because the backup may be corrupted.

Solution : Make sure to copy backup file into another device then restore, The issue is on tape/usb/disk

RESTORE VERIFYONLY FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'

Msg 3242, Level 16, State 2, Line 2
The file on device '\\websql2\SQLBackup\MYDBTEST_2011-06-21-2040.FUL' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 2
VERIFY DATABASE is terminating abnormally.


RESTORE DATABASE MYDBTEST
FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'
WITH RECOVERY,
MOVE 'MYDBTEST_Data' TO 'E:\\MSSQL\DATA\MYDBTEST.mdf',
MOVE 'MYDBTEST_Log' TO 'E:\ MSSQL\DATA\MYDBTEST_log.ldf',
STATS = 5


05 percent processed.
.
.
.
.

85 percent processed.
90 percent processed.

Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database "MYDBTEST" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Find all system and user-defined error messages in the Database Engine

There are 8866 error messages in SQL Server 2008

SELECT message_id, language_id, severity, is_event_logged, text
FROM sys.messages
WHERE language_id = 1033

A fatal error occurred during installation.

When you try to install SQL Server 2008 sample database AdventureWorks2008_SR4. If your sql server service not started before you will get the following error message

A fatal error occurred during installation.
Details: Object reference not set to an instance of an object.

Solution : Make sure SQL Server service is running

A network-related or instance-specific error occurred while establishing a connection to SQL Server

Check SQL Server Configuration Manger to SQL Services are running. If SQL Server Services are not running Please restart the services.

Error message 14421 that occur when you use log shipping in SQL Server

Error: 14421, Severity: 16, State: 1


The log shipping secondary database NY-SQLSRV001. AdventureWorks has restore threshold of 45 minutes and is out of sync. No restore was performed for 150 minutes. Restored latency is 1154 minutes. Check agent log and logshipping monitor information.
 
Troubleshooting Error Message 14421


By definition, message 14421 does not necessarily indicate a problem with Log Shipping. This message indicates that the difference between the last backed up file and last restored file is greater than the time selected for the Out of Sync Alert threshold.

Solution:
1. Disable LogShipping Settings on user Database AdventureWorks from Primary Server.
2. Take the Full backup of user database AdventureWorks from Primary Server
3. Copy the backup file to Secondary server Shared Network folder
4. On Secondary server open the Query Report to run the following statement
-- Recover a Database from a Backup Without Restoring Data
-- Restore database using WITH RECOVERY.

RESTORE DATABASE AdventureWorks WITH RECOVERY
5. Now the User database AdventureWorks allow you to restore with NORECOVERY mode
6. Restored the AdventureWorks on Secondary Server from Backup file
7.Setting up Logshipping on Primary Server to Secondary server

SQL Server Error Logs

View SQL Server Error Logs in Query
EXEC xp_readerrorlog   -- (current error log file)
EXEC xp_readerrorlog 3 -- (error log file number #3)

You can re-cycle the error log by executing the DBCC ERRORLOG command (or) the sp_cycle_errorlog system procedure.