SQLTeam.com | Weblogs | Forums

Export to an excel file failed (empty file with column names only)


#1

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.