Error Msg When Dynamically Calling DBMail in a Cursor to Send Multiple File Attachments

Question

Hello,

Issue: Query hangs when attempting to call sp_send_dbmail and email separate file attachments from within a Cursor.

On canceling the query, I get the 'failed to initialize sqlcmd library' error. Although, I'm fully qualifying my table name with database context.

Permissions: I'm using the admin account and have full permissions. Also, I can execute sp_send_dbamil successfully with an attached file outside of the Cursor.

I ran a Trace and captured user error msg, but it's not clear what the issue is as the user has admin permissions to 'master' and 'msdb' databases.

Below are the Trace results and query. Thanks kindly for assisting.

USE MyDb;
GO 

BEGIN TRANSACTION
BEGIN TRY

		DECLARE @MyTable TABLE
		(
			ID INT IDENTITY(1, 1) NOT NULL,
			[State] NVARCHAR(2) NULL,
			City NVARCHAR(30) NULL,
			Zip INT
       
		);

		INSERT @MyTable ([State], City, Zip) VALUES (N'NY', N'Central Village', 109171)
		INSERT @MyTable ([State], City, Zip) VALUES (N'NY', N'Clay', 109172)
		INSERT @MyTable ([State], City, Zip) VALUES (N'NY', N'Queens', 109173)
		INSERT @MyTable ([State], City, Zip) VALUES (N'FL', N'Pembroke Pines', 32701)
		INSERT @MyTable ([State], City, Zip) VALUES (N'FL', N'Palm Beach', 32701)
		INSERT @MyTable ([State], City, Zip) VALUES (N'AL', N'Florence', 35630)
		INSERT @MyTable ([State], City, Zip) VALUES (N'NY', N'Birmingham', 35615)
		INSERT @MyTable ([State], City, Zip) VALUES (N'CA', N'Alpine', 91901)
		INSERT @MyTable ([State], City, Zip) VALUES (N'CA', N'Bel Air', 90210)

		DECLARE	@ctr INT;

		DECLARE state_list CURSOR FOR
			WITH cte AS (
				SELECT 
					cte.*, 
					DENSE_RANK() OVER(ORDER BY [State]) AS ctr
				FROM @MyTable AS cte
		)
		SELECT DISTINCT ctr FROM cte

		OPEN state_list;
		FETCH NEXT FROM state_list INTO @ctr;

		WHILE @@FETCH_STATUS = 0
			BEGIN
				IF OBJECT_ID(N'[dbo].[WORK_TABLE]', N'U') IS NOT NULL 
					BEGIN
						DROP TABLE [dbo].[WORK_TABLE];
					END
					;

		    WITH cte AS (
					SELECT 
						cte.*, 
						DENSE_RANK() OVER(ORDER BY [State]) AS ctr
					FROM @MyTable AS cte
			 )
				SELECT 
					[State],
					City,
					Zip
				INTO WORK_TABLE
				FROM cte
				WHERE ctr = @ctr;
	 
				-- SELECT * FROM WORK_TABLE

				
				-- declare dbmail variables and call dbmail
				DECLARE @column1name varchar(50),
						@qry varchar(8000)

				-- Assign the column name with the instruction in the column name variable
				SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'State]'

				-- Create the query, concatenating the declared column name variable as an alias
				SELECT @qry = 'SET NOCOUNT ON; SELECT w.[State] ' + @column1name + 
						 ' ,w.City, w.Zip FROM MyDb.dbo.WORK_TABLE w'

				-- email state specific result sets
				EXEC msdb.dbo.sp_send_dbmail 
									@blind_copy_recipients = '******@myemail.com',
                                    @subject = 'State CSV Extract',
                                    @query = @qry,
                                    @attach_query_result_as_file = 1,
                                    @query_attachment_filename = 'state.csv';			

				FETCH NEXT FROM state_list INTO @ctr;

		END;

	CLOSE state_list;
	DEALLOCATE state_list;

END TRY

BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

	DEALLOCATE state_list;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;
COMMIT TRANSACTION;

Answer 1

Hi @Francis, W

How about add USE Mydb in your query?

If not work, please refer to this article for more possible solutions: Troubleshooting SQL Server sp_send_dbmail Error Failed to Initialize SQLCMD Library.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.