Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259.

Question

DECLARE @RowId NVARCHAR(255)

DECLARE @Query NVARCHAR(MAX)

DECLARE @Header NVARCHAR(MAX)

SET @RowId = '[sep=,' + CHAR(13) + CHAR(10) + 'RowId]'

SET @Query = 'set nocount on;

SELECT ROW_NUMBER() OVER (ORDER BY PDTMO.MONo DESC, PDTWF.CreateDate ASC) AS ' + @RowId + ',

		PDTWFQC.IsConfirmGrn AS [Done FG GRN?],

		dbo.fn_Common_DateTimeFormat(PDTWFQC.IsConfirmGrnDate, SLCBIF.DateFormart_Long) AS [FG GRN Date/Time],

		PDTMO.LocationRemarks AS [Location Remark],

		PDTMO.SoNo AS [SO No.],

		PDTMO.MoNo AS [MO No.],

		PDTMO.ERPPartCode AS [Part No.],

		ISNULL(SODP.Quantity, 0) AS [SO Qty.],

		ISNULL(PDTMO.Mo_OrderedQty, 0) AS [MO Qty.],

		ISNULL(SODP.SPQty, 0) AS [SP Qty.],

		ISNULL(PDTWFQC.OkQtythisStep, 0) AS [QC Ok Qty.],

		(PDTMO.CustomerName + '' ('' + PDTMO.CustomerNo + '')'') AS [Customer Info.],

		ISNULL(SODP.DraftDeliveryQty, 0) AS [Draft DO Qty.],

		PDTMO.Mo_CustomerPO AS [Customer PO.],

		PDTMO.Mo_MOQty AS [MO Required Qty.],

		dbo.fn_Common_DateTimeFormat(PDTWFQC.StartQcTime, SLCBIF.DateFormart_Long) AS [QC Start Date/Time],

		ISNULL(PDTMO.Mo_GrnQty, 0) AS [Total GRN Qty.],

		SODP.SpNo AS [SP No.],

		dbo.fn_Common_DateTimeFormat(SOD.IssueDate, SLCBIF.DateFormart) AS [SO Issue Date],

		SOD.[Status] AS [SO Status],

		SODP.CancelQty AS [SO Cancelled Qty.]

INTO #tb

FROM

(

SELECT	ModuleCode, WorkflowNo, IsConfirmGrn, OkQtythisStep, StartQcTime, IsConfirmGrnDate, [Location]

FROM	Company.dbo.tbl_Production_Workflow_Qc

WHERE	IsNeedGrn = ''1''

AND		OkQtythisStep > 0

AND		IsConfirmGrnDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)

AND		IsConfirmGrnDate <	DATEADD(DAY, 0, DATEDIFF(dd, 0, GETDATE()))

AND		(LEFT(ModuleCode, 5) = ''IRMO-'' OR LEFT(ModuleCode, 3) = ''MO-'' OR LEFT(ModuleCode, 4) = ''SMO-'')

)

AS PDTWFQC

INNER JOIN Company.dbo.tbl_Sys_Local_Branch_Info AS SLCBIF ON 1=1

INNER JOIN Company.dbo.tbl_Production_Workflow AS PDTWF ON PDTWFQC.WorkflowNo = PDTWF.WorkflowNo

INNER JOIN Company.dbo.tbl_Production_Mo AS PDTMO ON PDTMO.MoNo = PDTWF.ModuleCode AND PDTMO.Status <> ''Cancelled''

LEFT JOIN Company.dbo.tbl_SalesOrder_Part AS SODP ON PDTMO.SoNoPartNo = SODP.SoNoPartNo

LEFT JOIN Company.dbo.tbl_SalesOrder AS SOD ON SOD.SoNo = PDTMO.SoNo

LEFT JOIN Company.dbo.tbl_PartLocalPartInfo AS PLCPIF ON (PDTMO.PartNo = PLCPIF.PartNo AND SLCBIF.BranchCode = PLCPIF.BranchCode)

WHERE 1=1

AND PDTMO.Mo_CustomerPO <> ''INTERNALUSE''

AND PDTMO.Mo_CustomerPO <> ''SAMPLE''

ORDER BY PDTMO.MoNo DESC, PDTWF.CreateDate ASC

SELECT TEMPTABLE03.*,

		(CASE WHEN CAST(TEMPTABLE03.[Total QC Ok Qty.] AS BIGINT) >= CAST(TEMPTABLE03.[MO Qty.] AS BIGINT)

			  THEN 0

			  WHEN CAST(TEMPTABLE03.[Total QC Ok Qty.] AS BIGINT) < CAST(TEMPTABLE03.[MO Qty.] AS BIGINT) AND CAST(TEMPTABLE03.[Total QC Ok Qty.] AS BIGINT) + CAST(TEMPTABLE03.[QC Ok Qty.] AS BIGINT) > CAST(TEMPTABLE03.[MO Qty.] AS BIGINT)

			  THEN CAST(TEMPTABLE03.[QC Ok Qty.] AS BIGINT) - (CAST(TEMPTABLE03.[Total QC Ok Qty.] AS BIGINT) + CAST(TEMPTABLE03.[QC Ok Qty.] AS BIGINT) - CAST(TEMPTABLE03.[MO Qty.] AS BIGINT))

			  ELSE CAST(TEMPTABLE03.[QC Ok Qty.] AS BIGINT) END) AS [QC Ok Qty.],

		(CASE WHEN CAST(TEMPTABLE03.[Total QC Ok Qty.] AS BIGINT) >= CAST(TEMPTABLE03.[MO Qty.] AS BIGINT)

			  THEN CAST(TEMPTABLE03.[QC Ok Qty.] AS BIGINT)

			  WHEN CAST(TEMPTABLE03.[Total QC Ok Qty.] AS BIGINT) < CAST(TEMPTABLE03.[MO Qty.] AS BIGINT) AND CAST(TEMPTABLE03.[Total QC Ok Qty.] AS BIGINT) + CAST(TEMPTABLE03.[QC Ok Qty.] AS BIGINT) > CAST(TEMPTABLE03.[MO Qty.] AS BIGINT)

			  THEN CAST(TEMPTABLE03.[Total QC Ok Qty.] AS BIGINT) + CAST(TEMPTABLE03.[QC Ok Qty.] AS BIGINT) - CAST(TEMPTABLE03.[MO Qty.] AS BIGINT)

			  ELSE 0 END) AS [Excess Stock Qty.]

FROM (SELECT TEMPTABLE02.*,

		ISNULL((SELECT	SUM(TEMPTABLE01.[QC Ok Qty.])

				FROM	#tb AS TEMPTABLE01

				WHERE	TEMPTABLE01.[MO No.] = TEMPTABLE02.[MO No.] AND CAST(TEMPTABLE01.RowId AS BIGINT) < CAST(TEMPTABLE02.RowId AS BIGINT)), 0) AS [Total QC Ok Qty.]

		FROM	#tb AS  TEMPTABLE02) AS TEMPTABLE03

DROP TABLE #tb'

SET @header = 'Hi xxx,

Attached Excess Stock List.

This report would be auto sent on the first day of the month At 0800H.

Regards,

yyy'

EXEC msdb.dbo.sp_send_dbmail

@profile_name= 'Company_AutoEmail'

,@recipients = 'yyy@Company.com.sg'

,@copy_recipients = 'xxx@Company.com.sg'

,@subject = 'SG-RPT - FG GRN Stock List'

,@body = @header

,@query = @Query

,@execute_query_database = 'Company'

,@attach_query_result_as_file=1

,@query_attachment_filename='SG-RPT - FG GRN Stock List.csv'

,@query_result_separator=',' --enforce csv

,@query_result_no_padding=1 --trim

,@query_result_width=32767 --stop wordwrap

Answer 1

See

https://www.mssqltips.com/sqlservertip/5552/troubleshooting-sql-server-spsenddbmail-error-failed-to-initialize-sqlcmd-library/

Answer 2

Hi @Elson Wong

Ensure that the user account has appropriate permissions to connect to SQL Server and execute commands. Check both SQL Server login permissions and database permissions. Check if the user was added in SQL server logins folder of Security.

Besides, avoid using temp table in your code.

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".