4 minor clarification
source | link

Since using sp_send_dbmail is an option here, I don't see why you need to export anything given that sp_send_dbmail can run a query and include the results, either in the body or as an attachment. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @query_result_separator, @query_no_truncate, and @query_result_no_padding parameters. Please keep in mind this note from that MSDN page (i.e. the link above) regarding the query to run:

Since using sp_send_dbmail is an option here, I don't see why you need to export anything. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @query_result_separator, @query_no_truncate, and @query_result_no_padding parameters. Please keep in mind this note from that MSDN page (i.e. the link above) regarding the query to run:

Since using sp_send_dbmail is an option here, I don't see why you need to export anything given that sp_send_dbmail can run a query and include the results, either in the body or as an attachment. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @query_result_separator, @query_no_truncate, and @query_result_no_padding parameters. Please keep in mind this note from that MSDN page (i.e. the link above) regarding the query to run:

3 added 66 characters in body
source | link
  • add a WAITFOR DELAY '00:00:10.000'; to add a 10-second delay before the calling session ends.
  • instead of using a global temporary table, use NEWID() to construct a unique table name for the purpose of dumping that data into tempdb in a real table that will exist for as long as you need it to. Then you can drop it at the end of the query that you submit for the report. The bigger issue is that now Dynamic SQL is needed for the Table name, yet that won't work with a Table Variable since that can't be passed into Dynamic SQL. We need a known and consistent object name to insert the rows from the Table Variable into the real Table. Thankfully, a SYNONYMSYNONYM can provide a consistent name in the current context that points to the real Table in tempdb since they can be created in Dynamic SQL. That gives us the following (and you can run the following to see that it works, though I did not implement the final DROP of the real Table in tempdb):

    SET NOCOUNT ON;
    
    DECLARE @TableName NVARCHAR(70) = N'tempdb.dbo.[Report_'
                                      + CONVERT(NVARCHAR(36), NEWID()) + N']';
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = N'CREATE TABLE ' + @TableName + N' ([Col1a] INT);';
    EXEC (@SQL);
    
    SET @SQL = N'CREATE SYNONYM dbo.TempDump FOR ' + @TableName + N';';
    EXEC (@SQL);
    
    DECLARE @SomeTableVar TABLE ([Col1b] INT);
    INSERT INTO @SomeTableVar ([Col1b]) VALUES (1), (20), (34), (4444);
    
    INSERT INTO dbo.TempDump ([Col1a])
      SELECT Col1b
      FROM   @SomeTableVar;
    
    SELECT * FROM dbo.TempDump; -- only needed for debug
    
    SET @SQL = N'DROP SYNONYM dbo.TempDump;';
    EXEC (@SQL);
    
    
    -- create the report query, ending with the DROP statement
    DECLARE @ReportSQL NVARCHAR(MAX) = N'';
    SET @ReportSQL += N'my report, sent as @query, using ' + @TableName + N';';
    SET @ReportSQL += NCHAR(0x0D) + NCHAR(0x0A) + N'DROP TABLE ' + @TableName + N';';
    print @ReportSQL;
    
    -- EXEC  sp_send_dbmail ..., @query = @ReportSQL, @attach_query_result_as_file = 1,
    --                      @query_result_separator = N',', @query_no_truncate = 1, ...;
    

    If the process fails after creating the Table but before the DROP statement is called, no problem: tempdb is created from the empty model Database every time the SQL Server service restarts (which is why I am suggesting to create the Tables in tempdb!).

  • add a WAITFOR DELAY '00:00:10.000'; to add a 10-second delay before the calling session ends.
  • instead of using a global temporary table, use NEWID() to construct a unique table name for the purpose of dumping that data into tempdb in a real table that will exist for as long as you need it to. Then you can drop it at the end of the query that you submit for the report. The bigger issue is that now Dynamic SQL is needed for the Table name, yet that won't work with a Table Variable since that can't be passed into Dynamic SQL. We need a known and consistent object name to insert the rows from the Table Variable into the real Table. Thankfully, a SYNONYM can provide a consistent name in the current context that points to the real Table in tempdb since they can be created in Dynamic SQL. That gives us the following (and you can run the following to see that it works, though I did not implement the final DROP of the real Table in tempdb):

    SET NOCOUNT ON;
    
    DECLARE @TableName NVARCHAR(70) = N'tempdb.dbo.[Report_'
                                      + CONVERT(NVARCHAR(36), NEWID()) + N']';
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = N'CREATE TABLE ' + @TableName + N' ([Col1a] INT);';
    EXEC (@SQL);
    
    SET @SQL = N'CREATE SYNONYM dbo.TempDump FOR ' + @TableName + N';';
    EXEC (@SQL);
    
    DECLARE @SomeTableVar TABLE ([Col1b] INT);
    INSERT INTO @SomeTableVar ([Col1b]) VALUES (1), (20), (34), (4444);
    
    INSERT INTO dbo.TempDump ([Col1a])
      SELECT Col1b
      FROM   @SomeTableVar;
    
    SELECT * FROM dbo.TempDump; -- only needed for debug
    
    SET @SQL = N'DROP SYNONYM dbo.TempDump;';
    EXEC (@SQL);
    
    
    -- create the report query, ending with the DROP statement
    DECLARE @ReportSQL NVARCHAR(MAX) = N'';
    SET @ReportSQL += N'my report, sent as @query, using ' + @TableName + N';';
    SET @ReportSQL += NCHAR(0x0D) + NCHAR(0x0A) + N'DROP TABLE ' + @TableName + N';';
    print @ReportSQL;
    
    -- EXEC  sp_send_dbmail ..., @query = @ReportSQL, @attach_query_result_as_file = 1,
    --                      @query_result_separator = N',', @query_no_truncate = 1, ...;
    

    If the process fails after creating the Table but before the DROP statement is called, no problem: tempdb is created from the empty model Database every time the SQL Server service restarts (which is why I am suggesting to create the Tables in tempdb!).

  • add a WAITFOR DELAY '00:00:10.000'; to add a 10-second delay before the calling session ends.
  • instead of using a global temporary table, use NEWID() to construct a unique table name for the purpose of dumping that data into tempdb in a real table that will exist for as long as you need it to. Then you can drop it at the end of the query that you submit for the report. The bigger issue is that now Dynamic SQL is needed for the Table name, yet that won't work with a Table Variable since that can't be passed into Dynamic SQL. We need a known and consistent object name to insert the rows from the Table Variable into the real Table. Thankfully, a SYNONYM can provide a consistent name in the current context that points to the real Table in tempdb since they can be created in Dynamic SQL. That gives us the following (and you can run the following to see that it works, though I did not implement the final DROP of the real Table in tempdb):

    SET NOCOUNT ON;
    
    DECLARE @TableName NVARCHAR(70) = N'tempdb.dbo.[Report_'
                                      + CONVERT(NVARCHAR(36), NEWID()) + N']';
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = N'CREATE TABLE ' + @TableName + N' ([Col1a] INT);';
    EXEC (@SQL);
    
    SET @SQL = N'CREATE SYNONYM dbo.TempDump FOR ' + @TableName + N';';
    EXEC (@SQL);
    
    DECLARE @SomeTableVar TABLE ([Col1b] INT);
    INSERT INTO @SomeTableVar ([Col1b]) VALUES (1), (20), (34), (4444);
    
    INSERT INTO dbo.TempDump ([Col1a])
      SELECT Col1b
      FROM   @SomeTableVar;
    
    SELECT * FROM dbo.TempDump; -- only needed for debug
    
    SET @SQL = N'DROP SYNONYM dbo.TempDump;';
    EXEC (@SQL);
    
    
    -- create the report query, ending with the DROP statement
    DECLARE @ReportSQL NVARCHAR(MAX) = N'';
    SET @ReportSQL += N'my report, sent as @query, using ' + @TableName + N';';
    SET @ReportSQL += NCHAR(0x0D) + NCHAR(0x0A) + N'DROP TABLE ' + @TableName + N';';
    print @ReportSQL;
    
    -- EXEC  sp_send_dbmail ..., @query = @ReportSQL, @attach_query_result_as_file = 1,
    --                      @query_result_separator = N',', @query_no_truncate = 1, ...;
    

    If the process fails after creating the Table but before the DROP statement is called, no problem: tempdb is created from the empty model Database every time the SQL Server service restarts (which is why I am suggesting to create the Tables in tempdb!).

2 added clarification re: send_dbmail being asynchronous
source | link

I believe sp_send_dbmail uses Service Broker and is asynchronous. This would seem to allow for the possibility of the global temporary table not existing when the query in @query finally gets executed, if the session that executed sp_send_dbmail ends before the query in @query starts. In this case, you can do either:

  • add a WAITFOR DELAY '00:00:10.000'; to add a 10-second delay before the calling session ends.
  • instead of using a global temporary table, use NEWID() to construct a unique table name for the purpose of dumping that data into tempdb in a real table that will exist for as long as you need it to. Then you can drop it at the end of the query that you submit for the report. The bigger issue is that now Dynamic SQL is needed for the Table name, yet that won't work with a Table Variable since that can't be passed into Dynamic SQL. We need a known and consistent object name to insert the rows from the Table Variable into the real Table. Thankfully, a SYNONYM can provide a consistent name in the current context that points to the real Table in tempdb since they can be created in Dynamic SQL. That gives us the following (and you can run the following to see that it works, though I did not implement the final DROP of the real Table in tempdb):

    SET NOCOUNT ON;
    
    DECLARE @TableName NVARCHAR(70) = N'tempdb.dbo.[Report_'
                                      + CONVERT(NVARCHAR(36), NEWID()) + N']';
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = N'CREATE TABLE ' + @TableName + N' ([Col1a] INT);';
    EXEC (@SQL);
    
    SET @SQL = N'CREATE SYNONYM dbo.TempDump FOR ' + @TableName + N';';
    EXEC (@SQL);
    
    DECLARE @SomeTableVar TABLE ([Col1b] INT);
    INSERT INTO @SomeTableVar ([Col1b]) VALUES (1), (20), (34), (4444);
    
    INSERT INTO dbo.TempDump ([Col1a])
      SELECT Col1b
      FROM   @SomeTableVar;
    
    SELECT * FROM dbo.TempDump; -- only needed for debug
    
    SET @SQL = N'DROP SYNONYM dbo.TempDump;';
    EXEC (@SQL);
    
    
    -- create the report query, ending with the DROP statement
    DECLARE @ReportSQL NVARCHAR(MAX) = N'';
    SET @ReportSQL += N'my report, sent as @query, using ' + @TableName + N';';
    SET @ReportSQL += NCHAR(0x0D) + NCHAR(0x0A) + N'DROP TABLE ' + @TableName + N';';
    print @ReportSQL;
    
    -- EXEC  sp_send_dbmail ..., @query = @ReportSQL, @attach_query_result_as_file = 1,
    --                      @query_result_separator = N',', @query_no_truncate = 1, ...;
    

    If the process fails after creating the Table but before the DROP statement is called, no problem: tempdb is created from the empty model Database every time the SQL Server service restarts (which is why I am suggesting to create the Tables in tempdb!).

I believe sp_send_dbmail uses Service Broker and is asynchronous. This would seem to allow for the possibility of the global temporary table not existing when the query in @query finally gets executed, if the session that executed sp_send_dbmail ends before the query in @query starts. In this case, you can do either:

  • add a WAITFOR DELAY '00:00:10.000'; to add a 10-second delay before the calling session ends.
  • instead of using a global temporary table, use NEWID() to construct a unique table name for the purpose of dumping that data into tempdb in a real table that will exist for as long as you need it to. Then you can drop it at the end of the query that you submit for the report. The bigger issue is that now Dynamic SQL is needed for the Table name, yet that won't work with a Table Variable since that can't be passed into Dynamic SQL. We need a known and consistent object name to insert the rows from the Table Variable into the real Table. Thankfully, a SYNONYM can provide a consistent name in the current context that points to the real Table in tempdb since they can be created in Dynamic SQL. That gives us the following (and you can run the following to see that it works, though I did not implement the final DROP of the real Table in tempdb):

    SET NOCOUNT ON;
    
    DECLARE @TableName NVARCHAR(70) = N'tempdb.dbo.[Report_'
                                      + CONVERT(NVARCHAR(36), NEWID()) + N']';
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = N'CREATE TABLE ' + @TableName + N' ([Col1a] INT);';
    EXEC (@SQL);
    
    SET @SQL = N'CREATE SYNONYM dbo.TempDump FOR ' + @TableName + N';';
    EXEC (@SQL);
    
    DECLARE @SomeTableVar TABLE ([Col1b] INT);
    INSERT INTO @SomeTableVar ([Col1b]) VALUES (1), (20), (34), (4444);
    
    INSERT INTO dbo.TempDump ([Col1a])
      SELECT Col1b
      FROM   @SomeTableVar;
    
    SELECT * FROM dbo.TempDump; -- only needed for debug
    
    SET @SQL = N'DROP SYNONYM dbo.TempDump;';
    EXEC (@SQL);
    
    
    -- create the report query, ending with the DROP statement
    DECLARE @ReportSQL NVARCHAR(MAX) = N'';
    SET @ReportSQL += N'my report, sent as @query, using ' + @TableName + N';';
    SET @ReportSQL += NCHAR(0x0D) + NCHAR(0x0A) + N'DROP TABLE ' + @TableName + N';';
    print @ReportSQL;
    
    -- EXEC  sp_send_dbmail ..., @query = @ReportSQL, @attach_query_result_as_file = 1,
    --                      @query_result_separator = N',', @query_no_truncate = 1, ...;
    

    If the process fails after creating the Table but before the DROP statement is called, no problem: tempdb is created from the empty model Database every time the SQL Server service restarts (which is why I am suggesting to create the Tables in tempdb!).

1
source | link