Archive

Archive for the ‘T SQL’ Category

Error Msg 8672, MERGE statement attempted to UPDATE or DELETE the same row more than once – MSDN TSQL forum

August 5, 2015 Leave a comment

–> Question:

I am trying to do update/insert operation using merge statement, but getting below error:

“The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.”

I am using SQL Server 2014. I knew there was a bug in older version of SQL Server (like version 2008). Does this issue still persist in latest version of SQL Server?
 

–> My Answer:
There might be 2 reasons:

1. Your source/target tables might be having DUPLICATES.

2. There might NO DUPLICATES, but you might be using NOLOCK hint? If yes, then your source table might be very big and going through constant changes (INSERT/UPDATE/DELETE), which might be causing PAGE-SPLITS and causing to read single source row TWICE. Try removing NOLOCK and run your MERGE stmt.
 

–> Another Answer by Naomi:

You need to fix the source of the problem, not apply patches. If your source has duplicate rows for the same key used to update, when how would you know which row will be used to update? The simple solution is to eliminate duplicates by either using correct combinations of keys to join or creating an extra key with ROW_NUMBER() approach.
 

Ref Link.

Categories: SQL Errors, SQL Server Questions Tags: ,

SQL Error – Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option

June 26, 2015 Leave a comment

 
Error Message:

Executed as user: DOMAIN\Account. Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option. [SQLSTATE 42000] (Error 6263). The step failed.
 

–> Resolution:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable clr enabled
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- check if it has been changed
EXEC sp_configure 'clr enabled'
GO

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Categories: SQL Errors Tags:

SQL Error – SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’

June 25, 2015 2 comments

 
Error Message:

Executed as user: DOMAIN\Account. SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Database Mail XPs’ by using sp_configure. For more information about enabling ‘Database Mail XPs’, search for ‘Database Mail XPs’ in SQL Server Books Online. [SQLSTATE 42000] (Error 50000). The step failed.
 

–> Resolution:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable Database Mail XPs
EXEC sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

-- check if it has been changed
EXEC sp_configure 'Database Mail XPs'
GO

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Categories: SQL Errors Tags:

SQL Error – SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’

June 24, 2015 Leave a comment

 
Error Message:

Executed as user: DOMAIN\Account. SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online. [SQLSTATE 42000] (Error 50000). The step failed.
 

–> Resolution:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

-- check if it has been changed
EXEC sp_configure 'xp_cmdshell'
GO

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Categories: SQL Errors Tags:

SQL Tip – How to get size of all Tables in a Database?

June 16, 2015 Leave a comment

 
You can use SQL Server system Stored Procedure sp_spaceused to get the details of a table like rows and size. Just pass the table name to this SP as a parameter:

USE [AdventureWorks2014]
GO

EXEC sp_spaceused N'Person.Person'
GO

name		rows	reserved	data		index_size	unused
Person.Person	19972   85840 KB	30504 KB	53192 KB	2144 KB

 

But if you want to get details of more than one or all tables of a Database then you can iterate this SP for all tables. To iterate for all tables there is an undocumented function in SQL Server i.e. sp_msForEachTable, and you can provide your sp_spaceused query as a parameter to this SP:

CREATE TABLE #TableSize (
	 name		NVARCHAR(128)
	,rows		CHAR(20)
	,reserved	VARCHAR(18)
	,data		VARCHAR(18)
	,index_size VARCHAR(18)
	,unused		VARCHAR(18)
)

sp_msForEachTable 'INSERT INTO #TableSize (name, rows, reserved, data, index_size, unused) EXEC sp_spaceused [?]'

SELECT * FROM #TableSize 

DROP TABLE #TableSize
GO

 

There is one more way to get the details by using system (sys) views, query below:

SELECT 
     t.name AS TableName 
    ,s.name AS SchemaName 
    ,p.rows AS RowCounts 
    ,SizeInKB = SUM(a.total_pages) * 8
    ,UsedSpaceInKB = SUM(a.used_pages) * 8
    ,UnUsedSpaceInKB = (SUM(a.total_pages) - SUM(a.used_pages)) * 8
    ,SizeInMB = (SUM(a.total_pages) * 8)/1024 
    ,SizeInGB = (SUM(a.total_pages) * 8)/(1024*1024) 
FROM sys.tables t 

INNER JOIN sys.indexes i 
ON t.object_id = i.object_id 

INNER JOIN sys.partitions p 
ON i.object_id = p.object_id and i.index_id = p.index_id 

INNER JOIN sys.allocation_units a 
ON p.partition_id = a.container_id 

INNER JOIN sys.schemas s 
ON t.schema_id = s.schema_id 

WHERE t.is_ms_shipped = 0 
AND i.object_id > 255 
--AND t.name IN ('tbl1', 'tbl2', 'tbl3', 'tbl4') 
--AND t.name LIKE 'pattern%'  
GROUP BY t.name, s.name, p.rows 
ORDER BY SizeInMB DESC

To get details of selective tables just apply the Table names above in the IN() clause or LIKE operator.

Categories: SQL Tips