See
Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259.
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
3 answers
Sort by: Most helpful
-
-
LiHongMSFT-4306 27,016 Reputation points
2024-06-28T06:05:38.52+00:00 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".
-
Elson Wong 0 Reputation points
2024-09-06T03:45:50.0366667+00:00 Answer is to check the SQL query syntax