Intra Query Error

Hi
I seem to be getting the following error, when running a SP which populates
a table:
***********************************
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #62) to deadl
ock. Rerun the query without intra-query parallelism by using the query hint
option (maxdop 1).
************************************
The SP is the following:
****************************************
*****************
ALTER PROCEDURE sp_crosstab_NOV AS
DECLARE @.Year VARCHAR(5)
DECLARE @.Month VARCHAR(20)
DECLARE @.select VARCHAR(8000)
DECLARE @.sumfunc VARCHAR(100)
DECLARE @.pivot VARCHAR(100)
DECLARE @.table VARCHAR(100)
DECLARE @.sql VARCHAR(8000), @.delim VARCHAR(1)
SET @.Year = YEAR(GETDATE())
SET @.Month =MONTH(GETDATE()) - 1
SET @.select = 'INSERT INTO NUCosstab SELECT [Policy No], [Section Sequence],
Insured, [Effective Date], [Expiry Date], [Trans Code], Trade, [Reporting M
onth],[Reporting Year],SUM(GrossPremium) AS [Total Premium],
SUM(BrokerComm) AS [Broker Comm] , SUM(IPT) AS IPT FROM NUPremium WHERE [Re
porting Year] = ' + @.Year + ' AND [Reporting Month] = ' + @.Month + ' GROUP
BY [Policy No],[Section Sequence],Insured, [Effective Date],[Expiry Date],
[Trans Code], Trade, [Reporting Month],[Reporting Year]'
SET @.sumfunc = 'Sum(GrossPremium)'
SET @.pivot = '[Section Type Code]'
SET @.table = 'NUPremium'
TRUNCATE TABLE NUCosstab
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE
1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table +
' WHERE '
+ @.pivot + ' Is Not Null')
SELECT @.sql='', @.sumfunc=STUFF(@.sumfunc, LEN(@.sumfunc), 1, ' END)' )
SELECT @.delim=CASE SIGN( CHARINDEX('char', data_type)+CHARINDEX('date', data
_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @.sql=@.sql + '''' + CONVERT(VARCHAR(100), pivot) + ''' = ' +
STUFF(@.sumfunc,CHARINDEX( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + CONVERT(VARCHAR(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##
pivot
DROP TABLE ##pivot
SELECT @.sql=LEFT(@.sql, LEN(@.sql)-1)
SELECT @.select=STUFF(@.select, CHARINDEX(' FROM ', @.select)+1, 0, ', ' + @.sql
+ ' ')
EXEC (@.select)
SET ANSI_WARNINGS ON
*****************************
Any ideas?
Kind Regards
RickyHi Ricky
This article may help:
http://support.microsoft.com/?kbid=837983
--
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains - www.ciquery.com/articles/art_
04.asp
"Ricky" <MSN.MSN.com> wrote in message news:%23DOlz4CIGHA.240@.TK2MSFTNGP11.p
hx.gbl...
Hi
I seem to be getting the following error, when running a SP which populates
a table:
***********************************
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #62) to deadl
ock. Rerun the query without intra-query parallelism by using the query hint
option (maxdop 1).
************************************
The SP is the following:
****************************************
*****************
ALTER PROCEDURE sp_crosstab_NOV AS
DECLARE @.Year VARCHAR(5)
DECLARE @.Month VARCHAR(20)
DECLARE @.select VARCHAR(8000)
DECLARE @.sumfunc VARCHAR(100)
DECLARE @.pivot VARCHAR(100)
DECLARE @.table VARCHAR(100)
DECLARE @.sql VARCHAR(8000), @.delim VARCHAR(1)
SET @.Year = YEAR(GETDATE())
SET @.Month =MONTH(GETDATE()) - 1
SET @.select = 'INSERT INTO NUCosstab SELECT [Policy No], [Section Sequence],
Insured, [Effective Date], [Expiry Date], [Trans Code], Trade, [Reporting M
onth],[Reporting Year],SUM(GrossPremium) AS [Total Premium],
SUM(BrokerComm) AS [Broker Comm] , SUM(IPT) AS IPT FROM NUPremium WHERE [Re
porting Year] = ' + @.Year + ' AND [Reporting Month] = ' + @.Month + ' GROUP
BY [Policy No],[Section Sequence],Insured, [Effective Date],[Expiry Date],
[Trans Code], Trade, [Reporting Month],[Reporting Year]'
SET @.sumfunc = 'Sum(GrossPremium)'
SET @.pivot = '[Section Type Code]'
SET @.table = 'NUPremium'
TRUNCATE TABLE NUCosstab
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE
1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table +
' WHERE '
+ @.pivot + ' Is Not Null')
SELECT @.sql='', @.sumfunc=STUFF(@.sumfunc, LEN(@.sumfunc), 1, ' END)' )
SELECT @.delim=CASE SIGN( CHARINDEX('char', data_type)+CHARINDEX('date', data
_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @.sql=@.sql + '''' + CONVERT(VARCHAR(100), pivot) + ''' = ' +
STUFF(@.sumfunc,CHARINDEX( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + CONVERT(VARCHAR(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##
pivot
DROP TABLE ##pivot
SELECT @.sql=LEFT(@.sql, LEN(@.sql)-1)
SELECT @.select=STUFF(@.select, CHARINDEX(' FROM ', @.select)+1, 0, ', ' + @.sql
+ ' ')
EXEC (@.select)
SET ANSI_WARNINGS ON
*****************************
Any ideas?
Kind Regards
Ricky