CODEWITHSUNDEEP
sql-servert-sql
Melinda
Melinda

Reputation: 1531

Already an object named in the database issue

I am trying to execute an email that contains the results of a stored procedure. I was looking at other posts in stackoverflow but cannot seem to get past an error that states "Msg 2714, Level 16, State 1, Procedure CompareConfirm_FraudRules, Line 38 There is already an object named '##returnInactiveRules' in the database." I've looked in the DB and there is no object that exists with this name already. Any suggestions on how to fix this issue would be appreciated. Thanks.

Here is the my SP:

BEGIN

CREATE TABLE ##returnInactiveRules (
    ProductName varchar(100),
    ChannelName varchar(100),
    StrategyCode varchar(100),
    StrategyName varchar(100),
    RuleCode varchar(100),
    RuleName varchar(100),
    On_Off varchar(5)
);

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
-- SELECT R.RuleCode, R.Name as RuleName, S.StrategyCode, S.Name as StrategyName, R.IsActive
SELECT DISTINCT
    CASE
    WHEN PC.ProductIdentifier='2000' THEN 'GP'
    WHEN PC.ProductIdentifier='1000' THEN 'MB'
    END as ProductName, C.Name as ChannelName, S.StrategyCode, S.Name as StrategyName, R.RuleCode, R.Name as RuleName, 
    CASE
    WHEN R.IsActive = 1 THEN 'On'
    WHEN R.IsActive = 0 THEN 'Off'
    END as On_Off
INTO ##returnInactiveRules
FROM dbo.[Rule] R
INNER JOIN dbo.Strategy S  
on S.KnockoutRuleSet = R.KnockoutRuleSet 
INNER JOIN dbo.RFAI P 
on R.RFAIId = P.RFAIId
INNER JOIN dbo.DecisionStatus D 
on D. StatusId = R. StatusId
LEFT OUTER JOIN dbo.NOAA N 
on N.NOAAId = R.NOAAId
INNER JOIN dbo.RuleQuestionsXRef Q 
ON Q.RuleId = R.RuleId
INNER JOIN ProductChannelStrategyRuleXref X 
ON X.RuleId = R.RuleId 
INNER JOIN ProductChannelStrategy CS 
ON CS.ProductChannelStrategyId = X.ProductChannelStrategyId
INNER JOIN ProductChannel PC
ON PC.ProductChannelId = CS.ProductChannelId
INNER JOIN dbo.Channel C
ON C.ChannelId = PC.ChannelId
WHERE R.IsActive = 0
AND R.RuleCode IN ('F06',
'F07',
'F11',
'F12',
'F14',
'F15',
'F16',
'F17',
'F19',
'F23',
'F25',
'F26',
'F10'
)  
-- ORDER BY R.RuleCode, R.Name;
ORDER BY ProductName, C.Name, S.StrategyCode, S.Name, R.RuleCode, R.Name;

-- SELECT * FROM @returnValue;

-- Email the results in the @returnValue table -- 
    EXEC msdb.dbo.sp_send_dbmail
    @execute_query_database='Prod-XXX',
    @recipients=N'[email protected]',
    @body='Attached please find a file with the results.', 
    @subject ='Compare Fraud Rule Results',
    @profile_name ='Reports',
    @query ='EXEC CompareConfirm_Rules',
    @attach_query_result_as_file = 1,
    @query_attachment_filename ='CompareRuleResults.txt'
END
DROP TABLE ##returnInactiveRules;
GO

Upvotes: 0

Views: 1688

Answers (3)

PP006
PP006

Reputation: 709

If exists Drop it, then create the table

IF Object_id('tempdb..##returnInactiveRules') IS NOT NULL
  DROP TABLE ##returnInactiveRules

CREATE TABLE ##returnInactiveRules
  (
     ProductName  VARCHAR(100),
     ChannelName  VARCHAR(100),
     StrategyCode VARCHAR(100),
     StrategyName VARCHAR(100),
     RuleCode     VARCHAR(100),
     RuleName     VARCHAR(100),
     On_Off       VARCHAR(5)
  );

Upvotes: 3

CoOl
CoOl

Reputation: 2797

Temp tables which start with ## are global temp tables, so if you have 2 connection opened, and one of them created a table called ##Temp, you would not be able to create the same table from connection 2, until it is dropped by any of the 2 connections.

Best thing would be to use #returnInactiveRules and check for existence before creating it.

IF OBJECT_ID('tempdb..#returnInactiveRules') IS NOT NULL 
   DROP TABLE #returnInactiveRules
GO

Upvotes: 2

Duncan Burtenshaw
Duncan Burtenshaw

Reputation: 1

Have the drop statement at the beginning and if it's a global temp table ensure that only one process is creating it at any one time as two cannot exist simultaneously..

Also, unless you require other sessions to access the data, consider using a temp table rather than a global temp table by replacing the ## with #

Upvotes: 0

Related Questions