从SSIS错误调用存储过程但在SSMS中运行良好 [英] Calling Stored Proc from SSIS error but runs fine in SSMS

问题描述

大家好

当我从SSIS运行存储过程但我从SSMS运行良好时，我正在发生此错误

I'm grtting this error when I run Stored proc from SSIS but it runs fine from SSMS

< span style ="font-size：8.0pt; line-height：107％; font-family：'Microsoft Sans Serif'，sans-serif; color：black"> SQL Process所有更改：错误：执行查询"EXEC [ProcessAllChanges];"失败并出现以下错误："该语句已终止
。"。可能的失败原因：查询问题，"ResultSet"属性设置不正确，参数设置不正确或连接未正确建立

没有参数输入或输出，也没有记录集可以返回。

There is no parameters in or out and no record sets to return.

这是存储过程。没什么好看的。我做错了什么？它处理了大约95M的记录。

Here is the Stored Proc. Nothing fancy.What am I doing wrong?It process about 95M records.

btw之前有效，刚刚开始提出问题。我们正在运行SQL Server 2016.它在第一次[NOT EXIST]查询时失败。

btw it worked before and just now start giving issues. We are running SQL Server 2016. It fails on the 1st [NOT EXIST] query.

ALTER PROCEDURE [ProcessAllChanges]
AS
BEGIN
	
	SET NOCOUNT ON;

	-- log opening of execution
	DECLARE @execution UNIQUEIDENTIFIER = NEWID();
	DECLARE @task VARCHAR(MAX) = (SELECT OBJECT_NAME(@@PROCID));
	EXEC [util].[log_message] @task = @task, @message = 'start', @execution = @execution;


	DROP TABLE IF EXISTS [#versionsThatAreNew];
	
	-- gather up all versions in [intake] that aren't already present in exact state in [persist]
	SELECT [i].[KeyHash]
		,[i].[RowHash]
		,[i].[AddedOn]
	INTO [#versionsThatAreNew]
	FROM [intake].[tableA] AS [i]
	WHERE NOT EXISTS (
						SELECT 1 
						FROM [persist].[tableB] AS [r] 
						WHERE [r].[KeyHash] = [i].[KeyHash] 
						  AND [r].[RowHash] = [i].[RowHash]
					); -- where the record isn't already exactly there

	EXEC [util].[log_message] @task = @task, @message = '1', @execution = @execution;
	
	
	DROP TABLE IF EXISTS [#versionsThatNeedInsertOrUpdate];
	
	-- produce a set of all the operations that need to happen below, which are:
	--- 1. update any [persist] version's effective window when the keyhash also has a new version in [intake] (means a new version of an old record)
	--- 2. insert any [intake] version that isn't already in [persist]
	SELECT [n].[KeyHash]
		,[n].[RowHash]
		,-1 AS [RowID] -- they don't get RowIDs so put in a non-null placeholder. later we'll join on this column during the update to only hit modified records, making the -1 fall out of that operation.
		,[n].[AddedOn]
		,CAST('0001-01-01' AS DATETIME2(2)) AS [RowEffective]
		,CAST('9999-12-31 23:59:59.99' AS DATETIME2(2)) AS [RowExpiration]
	INTO [#versionsThatNeedInsertOrUpdate]
	FROM [#versionsThatAreNew] AS [n]
	
	UNION ALL -- we know these are disjointed sets because it's (what's not already in persist) + (what's in persist). so union all for performance.
	
	SELECT [r].[KeyHash]
		,[r].[RowHash]
		,[r].[RowID]
		,[r].[AddedOn]
		,CAST('0001-01-01' AS DATETIME2(2)) AS [RowEffective]
		,CAST('9999-12-31 23:59:59.99' AS DATETIME2(2)) AS [RowExpiration]
	FROM [persist].[tableB] AS [r]
	INNER JOIN [#versionsThatAreNew] AS [n]
		ON [r].[KeyHash] = [n].[KeyHash];
	
	EXEC [util].[log_message] @task = @task, @message = '2', @execution = @execution;

	
	DROP TABLE IF EXISTS [#keyHashesThatHaveMultipleVersionsSoTheyNeedCalculatedEffectiveWindows];
	
	-- the working set may have nondistinct KeyHashes in cases where a new version came in to replace an old version (intake.keyhash = persist.keyhash)
	--- for each of these, we can't use the defaulted effective windows that were put on everything in the last step.
	--- so we find just these, to feed into the next step, where we calculate consecutive windows per version.
	SELECT [t].[KeyHash]
	INTO [#keyHashesThatHaveMultipleVersionsSoTheyNeedCalculatedEffectiveWindows]
	FROM [#versionsThatNeedInsertOrUpdate] AS [t]
	GROUP BY [t].[KeyHash]
	HAVING COUNT(*) > 1;
	
	
	-- for multi-version keyhashes, sequence them by AddedOn to get consecutive, non-overlapping time windows.
	--- then update the working set to have those rowEffective and rowExpiration dates.
	WITH [recalc] AS (
		SELECT [v].[KeyHash]
			,[v].[RowHash]
			,[v].[AddedOn]
			,LAG([v].[AddedOn], 1) OVER (PARTITION BY [v].[KeyHash] ORDER BY [v].[AddedOn]) AS [PriorAddedOn]
			,LEAD([v].[AddedOn], 1) OVER (PARTITION BY [v].[KeyHash] ORDER BY [v].[AddedOn]) AS [NextAddedOn]
		FROM [#versionsThatNeedInsertOrUpdate] AS [v]
		INNER JOIN [#keyHashesThatHaveMultipleVersionsSoTheyNeedCalculatedEffectiveWindows] AS [kh]
			ON [v].[KeyHash] = [kh].[KeyHash]

	)
	UPDATE [#versionsThatNeedInsertOrUpdate]
		SET  [RowEffective] = CASE WHEN [r].[PriorAddedOn] IS NULL THEN '0001-01-01 00:00:00.00' ELSE [r].[AddedOn] END
			,[RowExpiration] = ISNULL([r].[NextAddedOn], '9999-12-31 23:59:59.99')
	FROM [#versionsThatNeedInsertOrUpdate] AS [v]
	INNER JOIN [recalc] AS [r]
		ON  [v].[KeyHash] = [r].[KeyHash]
		AND [v].[RowHash] = [r].[RowHash];

	EXEC [util].[log_message] @task = @task, @message = '3', @execution = @execution;



	BEGIN TRANSACTION;


	-- update from the working set any version that exists by RowID in the [persist] also.
	-- we could also join by [KeyHash]+[RowHash], but the table is likely ordered by [RowID] already so this could be quicker.
	UPDATE [persist].[tableB]
	SET [RowEffective] = [v].[RowEffective]
		,[RowExpiration] = [v].[RowExpiration]
	FROM [persist].[tableB] AS [r]
	INNER JOIN [#versionsThatNeedInsertOrUpdate] AS [v]
		ON [r].[RowID] = [v].[RowID];

	DECLARE @RowsUpdated BIGINT = @@ROWCOUNT;
	
	EXEC [util].[log_message] @task = @task, @message = @RowsUpdated, @execution = @execution, @category = 'Rows Updated'


	-- insert to [persist] any version from [intake] that matches to the working set by [KeyHash] and [RowHash].
	-- because the working set didn't include versions that were the same in [intake] and [persist], this avoids re-inserting existing versions.
	INSERT INTO [persist].[tableB] WITH (TABLOCK)
	SELECT [i].*
		,ISNULL([o].[batch_id], -1) AS [BatchID]
		,[v].[RowEffective]
		,[v].[RowExpiration]
	FROM [intake].[tableA] AS [i]
	INNER JOIN [#versionsThatNeedInsertOrUpdate] AS [v]
		ON  [i].[KeyHash] = [v].[KeyHash]
		AND [i].[RowHash] = [v].[RowHash]
	LEFT OUTER JOIN [util].[batch_open] AS [o]
		ON  1=1;

	DECLARE @RowsInserted BIGINT = @@ROWCOUNT;
	
	EXEC [util].[log_message] @task = @task, @message = @RowsInserted, @execution = @execution, @category = 'Rows Inserted'




	COMMIT TRANSACTION;


	EXEC [util].[log_message] @task = @task, @message = 'stop', @execution = @execution;

END;

推荐答案

你好Leo，

你应该可以添加更多日志来查看什么阶段停止。可能是因为等待，例如某事被封锁了。

You should be able to add more logging to see at what stage it stops. Likely it is due to waits, e.g. something becomes blocked.

这篇关于从SSIS错误调用存储过程但在SSMS中运行良好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！