I have a SQL job that exports data to an Excel file using a stored procedure. It produces an empty file, with column names only, when an insert statement is used in the SP.
Here is the code for SP:
Create Procedure MyStoredProcedure
as
BEGIN
SET NOCOUNT ON;
-- Step 1: create a temp table to retrieve desired data
Create Table #dataTable (CaseID int, CaseValue varchar(100))
Insert Into #dataTable
Select CaseID, CaseValue From SourceTable Where Paramers = 'Something'
-- Step 2: check if #dataTable has any data
Declare @Counts int = (Select Count(CaseID) From #dataTable)
if @Counts > 0
--store retrieved data to a log table
Insert Into logTable (CaseID)
Select CaseID From #dataTable
-- using this select statement to generate data to Excel file
Select CaseID, CaseValue From #dataTable
End
My SQL job code is similar to this:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'myProfile',
@recipients = 'myemai@mail.com',
@subject = 'new excel file',
@query = N'EXEC myStoredProcedure',
@attach_query_result_as_file = 1
When the job is run,it produced an empty Excel file, with Column names only, no data. However, if I commented out the Insert statement, then everything works as expected.
What is my problem(s) here?
Thank you in advance for your advises.