Tuesday, October 30, 2012

The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
When you try to run ssis package  you will get this above error only if you are not started Distributed transaction coordinator.

Make sure to start the Distributed Transaction Coordinator via services

Wednesday, October 17, 2012

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




Saturday, October 13, 2012

How to find index usage against table

If you want to find out index usage for user table then  sys.indexes and sys.dm_db_index_usage_stats that give you more details. You should look for user seeks counts on this result

Select OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,i.type_desc,
ius.user_seeks,ius.last_user_seek
From sys.indexes i
Inner Join sys.dm_db_index_usage_stats ius
on i.object_id = ius.object_id
and i.index_id = ius.index_id
and ius.database_id = DB_ID()
where ius.object_id = OBJECT_ID('dbo.Customers')
go

Tuesday, October 09, 2012

Monday, October 08, 2012

SQL Books Collection





SQL 2000 Books

SQL Server 2000 is best friend for DBAs

SQL 7.0 Books

The version SQL Server 7.0 are new era for SQL Server life for DBAs, I was one fortunate to read this books

SQL 6.5 Books

This is one of my favourite SQL Server 6.5 Book and SQL Server Performance Optimization and Tuning Handbook

Friday, October 05, 2012

SQL Server Connections 2012 at the Bellagio in Las Vegas

Oct 29-Nov 1 at the Bellagio in Las Vegas  for the hottest industry event fo this year 2012:

SQL Server Connections is packed with sessions from expert speakers providing you with valuable information that you can take back to work and put into practice immediately.

http://www.devconnections.com/shows/fall2012/default.aspx?s=191