Error when executing Proc with non default parameters 1

[SQLBI]

Hi,

I have the following code which makes up my SP:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER PROC dbo.pr_Missing_Sales 
		@Year int = 2005,
	 	@Mail int = 0

AS

SET NOCOUNT ON
SET DATEFORMAT DMY

BEGIN TRAN
	CREATE TABLE #Dates ([Date] smalldatetime,
				PracticeID int)
COMMIT TRAN

BEGIN TRAN
	DECLARE @Dte smalldatetime
	DECLARE @Practice int
	
	SET @Practice = 101
	
		WHILE @Practice <= (SELECT MAX(PracticeID)
					FROM Vets4Pets_Info..[List - Practices])
		BEGIN
			
			SET @Dte = (SELECT P.RxWorksDate 
					FROM Vets4PEts_Info..[List - Practices] AS P
					WHERE P.PracticeID = @Practice)
		
			WHILE @Dte < GETDATE()-2
			BEGIN
				SET @Dte = DATEADD(dd, 1, @Dte)
				
				INSERT #Dates ([Date], PracticeID)
				VALUES(@Dte, @Practice)
			END
		
		SET @Practice = @Practice + 1
		END

COMMIT TRAN

BEGIN TRAN
	CREATE TABLE #Missing (Practice int,
				[Date] smalldatetime)
COMMIT TRAN


BEGIN TRAN
	INSERT #Missing (Practice, [Date])
	
	(SELECT D.PracticeID AS 'Practice', CONVERT(char(10), D.[Date], 103) AS 'Date' 
	
	FROM #Dates AS D
		
		INNER JOIN Vets4Pets_Info..[List - Practices] AS P
		ON D.PracticeID = P.PracticeID
	
	WHERE NOT EXISTS (SELECT R.[Date]
				FROM RxWorks.RxWorks.Result AS R
				WHERE R.[Sub Group Code 1] = 99 AND
					R.[Result Code] = 1 AND
					R.[Period Code] = 1 AND
					D.[Date] = R.[Date] AND
					D.PracticeID = R.[Subscriber Code]) AND
					DATENAME(dw, D.[Date]) <> 'Sunday' AND
					D.[Date] NOT IN (SELECT [Date]
								FROM Vets4Pets_Info..[Info - Bank Holidays]) AND
					YEAR(D.[Date]) = @Year)

COMMIT TRAN

BEGIN TRAN

	IF @Mail = 0
		BEGIN
			SELECT Practice, [Date] FROM #Missing ORDER BY Practice, [Date]
		END
	ELSE

	IF (SELECT COUNT(*) FROM #Missing) > 0
		BEGIN 
			
			DECLARE @Msg Varchar(255)
			DECLARE @Sub Varchar(255)
			DECLARE @Qry Varchar(255)

			SET @Sub  = 'SQL Server - Missing RxWorks Sales Data' + '  ' + CONVERT(char(10), GETDATE(), 103)
			SET @Msg = 'The following days sales are missing from the RxWorks Database.'
			SET @Qry = 'SELECT Practice, [Date] FROM #Missing ORDER BY Practice, [Date]'
			
			EXEC master.. xp_sendmail @Recipients = 'leigh.moore@vets4pets.com',
					-- 	   @copy_recipients = 'greg.robinson@rxworks.com',
						   @subject = @Sub,
						   @message = @Msg,
						   @Query = @Qry			
		END
COMMIT TRAN


BEGIN TRAN
	DROP TABLE #Dates
	DROP TABLE #Missing
COMMIT TRAN


SET NOCOUNT OFF



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

When I run;

 EXEC pr_Missing_Sales

Everything works fine and i get my expected results, however when i run

EXEC pr_Missing_Sales 2005, 1

in an attempt to override the default parameters, i get the following error message:

ODBC error 208 (42S02) Invalid object name '#Missing'.

This morning i added the @Mail parameter and the 1st IF statement to test the value of @Mail, if its 0, then i just want to return the result set, if its > 0 i want xp_sendmail to email the result set.

Since this change however, i've been getting the error.

Can anyone suggest why this is occuring and how i can resolve it please?

Thanks in advance.

Cheers,
Leigh

You're only as good as your last backup!

 

[NoCoolHandle]

What does the new sp look like? (the one with the where if @mail)

 

[jegaby]

This is a guess...

Your #Missing table is a local temporary table, which is only accessible to your connection. xp_sendmail uses a bound connection to execute the query - which may mean that the local temporary table is not accessible within xp_sendmail. Try using a global temporary table (start with "##" instead of "#") and see if that makes a difference.

 

[mrdenny]

I'd have to agree with jegaby.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBI]

Jegaby, it appears that it was a temp table problem, changed it to a global temp table and it worked.

Can you think why it worked before i added the @Mail var as my xp_sendmail code is unchanged?

Thanks to all anyway.


Cheers,
Leigh

You're only as good as your last backup!