September 20, 2016 at 8:02 am
I have a SQL job that exports data to an Excel file using a stored procedure. It failed when an Insert Statement was added to the SP.
Here is my SP code without an insert statement, lets call it MyStoredProcedure1:
Create Procedure MyStoredProcedure1
as
Select * From myTable;
Here is my SP code with an insert statement, lets call it MyStoredProcedure2:
Create Procedure MyStoredProcedure2
as
begin
Insert Into logTable
Select * from myDataTable
end
begin
Select * From myTable;
end
My SQL job code looks like below:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'myProfile',
@recipients = 'myemai@mail.com',
@subject = 'new excel file',
@query = N'EXEC myStoredProcedure1', -- or MyStoredProcedure2
@attach_query_result_as_file = 1
Using MyStoredProcedure1, it exports an Excel file with expected data values. However, it only create an empty Excel file if using MyStoredProcedure2.
How can I add an insert statement without interfering my Excel export job?
Thank you in advance for your advises.
September 20, 2016 at 8:35 am
Your begins and ends may be confusing things. Try this:
CREATE PROCEDURE MyStoredProcedure2
AS
BEGIN
SET NOCOUNT ON;
INSERT logTable
SELECT *
FROM myDataTable;
SELECT *
FROM myTable;
END;
Note also that you should (almost) never use "SELECT *" in production code. Instead, list the column names explicitly.
The SET NOCOUNT ON will almost certainly be a valuable addition for you, to filter out some unnecessary 'noise' from the execution.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 20, 2016 at 9:25 am
Thanks,Phil. I've tried your code and it worked as expected. However, in my case, there are still some problems. In the original post, I over simplified the SP code. There are a few steps before querying the data. Below is the sample code:
Create Procedure MyStoredProcedure2
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
Insert Into logTable (CaseID)
Select CaseID From #dataTable
Select CaseID, CaseValue From #dataTable
End
For MyStoredProcedure1, everything is the same except that it didnot have that INSERT statement.
Please advise.
Thanks!
September 20, 2016 at 9:54 am
jay-125866 (9/20/2016)
Thanks,Phil. I've tried your code and it worked as expected. However, in my case, there are still some problems. In the original post, I over simplified the SP code. There are a few steps before querying the data. Below is the sample code:Create Procedure MyStoredProcedure2
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
Insert Into logTable (CaseID)
Select CaseID From #dataTable
Select CaseID, CaseValue From #dataTable
End
For MyStoredProcedure1, everything is the same except that it didnot have that INSERT statement.
Please advise.
Thanks!
You say that there are 'still some problems', but have not mentioned what those problems are. Please elucidate.
Here's a slightly tweaked version of your code:
CREATE PROCEDURE MyStoredProcedure2
AS
BEGIN
SET NOCOUNT ON;
-- Step 1: create a temp table to retrieve desired data
CREATE TABLE #dataTable
(
CaseID INT
,CaseValue VARCHAR(100)
);
INSERT #dataTable
(
CaseID
,CaseValue
)
SELECT s.CaseID
, s.CaseValue
FROM SourceTable s
WHERE s.Paramers = 'Something';
-- Step 2: check if #dataTable has any data
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO logTable
(CaseID)
SELECT CaseID
FROM #dataTable;
END;
SELECT CaseID
, CaseValue
FROM #dataTable;
END;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 20, 2016 at 10:01 am
Sorry, the problem is that it still generated an empty excel file. I'm going to try your new code to see if it works.
Thank you very much for your help!
September 20, 2016 at 10:14 am
I've tried it and got the same results, an empty Excel file with Column names only.
September 20, 2016 at 1:19 pm
If you execute the proc in SSMS, presumably no results are returned either?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 20, 2016 at 1:28 pm
When the stored procedure is executed inside SSMS, it does return the expected data value.
September 20, 2016 at 1:45 pm
You could try changing your temp tables to global temp tables and see if that makes a difference. What you are seeing can often happen with DBMail as it executes in a different session so doesn't have access to the temp table.
Sue
September 20, 2016 at 2:31 pm
Sue_H (9/20/2016)
You could try changing your temp tables to global temp tables and see if that makes a difference. What you are seeing can often happen with DBMail as it executes in a different session so doesn't have access to the temp table.Sue
Interesting. Shouldn't that result in an error, though?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 20, 2016 at 2:32 pm
Sue_H (9/20/2016)
You could try changing your temp tables to global temp tables and see if that makes a difference. What you are seeing can often happen with DBMail as it executes in a different session so doesn't have access to the temp table.Sue
You could also try using a table variable, rather than a temp table, to test out this idea.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 20, 2016 at 3:07 pm
Thanks, Sue, I'll give it a try.
September 20, 2016 at 3:08 pm
Phil Parkin (9/20/2016)
Sue_H (9/20/2016)
You could try changing your temp tables to global temp tables and see if that makes a difference. What you are seeing can often happen with DBMail as it executes in a different session so doesn't have access to the temp table.Sue
Interesting. Shouldn't that result in an error, though?
Not necessarily. I think it depends on the flow of the entire stored proc, procedure or whatever. I've been hit by this a few times in the past with no error, just empty results. I would suspect you get the same issues with table variables due to the same scoping issues when using temp tables and how dbmail spawns another session.
Sue
September 21, 2016 at 5:57 am
Sue_H (9/20/2016)
Phil Parkin (9/20/2016)
Sue_H (9/20/2016)
You could try changing your temp tables to global temp tables and see if that makes a difference. What you are seeing can often happen with DBMail as it executes in a different session so doesn't have access to the temp table.Sue
Interesting. Shouldn't that result in an error, though?
Not necessarily. I think it depends on the flow of the entire stored proc, procedure or whatever. I've been hit by this a few times in the past with no error, just empty results. I would suspect you get the same issues with table variables due to the same scoping issues when using temp tables and how dbmail spawns another session.
Sue
I'm looking forward to hearing the OP's findings ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 21, 2016 at 10:07 am
I've tried it with a global temp table, and still no luck.
As an alternative, I took out the insert statement from my stored procedure, and set it as a new step in SQL job.
Thank you both, Phil and Sue!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply