Issue: When posting using any standard BAPI via Integrate, the following error message is received:
An Exception was thrown: Staging table commit to database via bulk copy failed.
Targeted Solution:
This error is typically caused by date fields that are created as varchar(8) instead of varchar(10) or by time fields that are created as varchar(6) instead of varchar(8) in the download tables for the BAPI post. To resolve the issue, change the date fields to varchar(10) and the time fields to varchar(8).
You can use the Column Search Query at the end of this article to help locate columns that have 'nvarchar' or 'varchar' as the data type with a maximum character length of 8. The query can take a long time to run because it searches the entire instance (databases, schemas, and tables).
This solution applies to SST/DSP versions prior to 8.0.1, which included a resolution (DSP70-737).
Catch-all Solution:
This error is sometimes caused by other datatypes (not date or time fields) and can take a very long time to narrow down to a single field and then to determine a proper length. This solution will greatly increase the column sizing in the download and upload tables for the BAPI post, but it should always eliminate the error when the Targeted Solution above does not.
You can run the following query to execute the "catch-all" solution.
- NOTE: The query must be updated to replace "DatabaseName" in line 1 with the database that contains the BAPI's download and upload tables.
- NOTE: The query must be updated to replace "ttBAPI_Name" in line 13 with an identifier that works for the BAPI download and upload tables you're working with.
USE DatabaseName
DECLARE @Sqlcmd NVARCHAR(MAX)
DECLARE @Cur CURSOR
SET @CUR = CURSOR FOR
SELECT
'ALTER TABLE [' + TABLE_NAME + '] ALTER COLUMN [' + COLUMN_NAME
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH = 8 THEN '] NVARCHAR(10)'
ELSE '] NVARCHAR(MAX)' END
FROM
INFORMATION_SCHEMA.columns
WHERE
table_name LIKE 'ttBAPI_Name%' AND data_type = 'nvarchar'
ORDER BY
CHARACTER_MAXIMUM_LENGTH
OPEN @Cur
FETCH NEXT FROM @Cur INTO @Sqlcmd
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@Sqlcmd)
FETCH NEXT FROM @Cur INTO @Sqlcmd
END
CLOSE @Cur
DEALLOCATE @Cur
EXEC sp_updatestats
Column Search Query:
You can adapt the following line in the WHILE block of the query to find the 6 character columns and change the value in quotes from 8 to 6:
WHERE Character_Maximum_Length = ''8'' and DATA_TYPE like ''%varchar''
You can edit the following condition in the script below to exclude additional databases to help speed up the query.
'AND [Name] NOT IN ('master','msdb','tempdb','model','DocxPress')'
SQL Query to Help Locate Columns That Have 'Nvarchar' or 'Varchar':
SET NOCOUNT ON
BEGIN
DECLARE @nuru TABLE (
[TABLE_CATALOG] NVARCHAR(128) NULL
,[TABLE_SCHEMA] NVARCHAR(128) NULL
,[TABLE_NAME] SYSNAME NOT NULL
,[COLUMN_NAME] SYSNAME NULL
,[ORDINAL_POSITION] INT NULL
,[COLUMN_DEFAULT] NVARCHAR(4000) NULL
,[IS_NULLABLE] VARCHAR(3) NULL
,[DATA_TYPE] NVARCHAR(128) NULL
,[CHARACTER_MAXIMUM_LENGTH] INT NULL
,[CHARACTER_OCTET_LENGTH] INT NULL
,[NUMERIC_PRECISION] TINYINT NULL
,[NUMERIC_PRECISION_RADIX] SMALLINT NULL
,[NUMERIC_SCALE] INT NULL
,[DATETIME_PRECISION] SMALLINT NULL
,[CHARACTER_SET_CATALOG] SYSNAME NULL
,[CHARACTER_SET_SCHEMA] SYSNAME NULL
,[CHARACTER_SET_NAME] SYSNAME NULL
,[COLLATION_CATALOG] SYSNAME NULL
,[COLLATION_SCHEMA] SYSNAME NULL
,[COLLATION_NAME] SYSNAME NULL
,[DOMAIN_CATALOG] SYSNAME NULL
,[DOMAIN_SCHEMA] SYSNAME NULL
,[DOMAIN_NAME] SYSNAME NULL
)
DECLARE @DBName SYSNAME
,@SQL_String2 NVARCHAR(4000)
,@TempRowCnt VARCHAR(20)
,@Dbug BIT = 0
DECLARE DB_cursor CURSOR
FOR
SELECT NAME
FROM sys.databases
WHERE STATE = 0
AND [Name] NOT IN ('master','msdb','tempdb','model','DocxPress')
OPEN DB_cursor
FETCH NEXT
FROM DB_cursor
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL_String2 = ' select * into ##Temp_Column_Info from [' + @DBName + '].INFORMATION_SCHEMA.COLUMNS
where Character_Maximum_Length = ''8'' and DATA_TYPE like ''%varchar''
;'
IF @Dbug = 1
SELECT @SQL_String2 AS '@SQL_String2';
EXEC sp_executesql @SQL_String2;
INSERT INTO @nuru
SELECT *
FROM ##Temp_Column_Info;
DROP TABLE ##Temp_Column_Info;
FETCH NEXT
FROM DB_cursor
INTO @DBName
END
SELECT *
FROM @nuru
ORDER BY 4
,2
,3
CLOSE DB_cursor;
DEALLOCATE DB_cursor;
SET @TempRowCnt = (
SELECT cast(count(1) AS VARCHAR(10))
FROM @nuru
)
PRINT ('Rows found: ' + @TempRowCnt + ' end ...')
END