Error message... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error message...

Author

Topic

JohnnieWalkerLover
Starting Member

5 Posts

Posted - 2011-11-03 : 06:20:37

Hi all,

getting the following....Msg 4863, Level 16, State 1, Line 1

Bulk insert data conversion error (truncation) for row 24752, column 8 (SIZEGROUP_CODE).

Updating Base Table:

EUR_POSITF_IMPORT_SKUS - Started

(1 row(s) affected)

USE [EPOS]
GO
/****** Object: StoredProcedure [dbo].[EUR_POSITF_RunImport] Script Date: 11/03/2011 10:00:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[EUR_POSITF_RunImport]
@bDoFTP bit = 1,
@bDoUnPack bit = 1
as
-- Version Change Date
-- 44.0 Created 25/07/03
--
--
--
--
declare @szFilePath varchar(2038),
@szImportDirectory varchar(1024),
@szbranchcode varchar(3),
@sCmd varchar(1024)
declare @objZip int,
@objFTP int,
@hr int,
@szSrc varchar(255),
@szDesc varchar(255),
@szMsg varchar(255),
@bDone bit,
@szFTPServer varchar(32),
@szFTPPath varchar(255),
@szFTPUser varchar(32),
@szFTPPassword varchar(64),
@szFilename varchar(64),
@szImportFileName varchar(255)

exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'Software\Euroshop\Retail\EPOS\Settings', 'ROOTDIR', @szImportDirectory OUTPUT
IF SUBSTRING(@szImportDirectory, LEN(@szImportDirectory), 1) <> '\'
BEGIN
SELECT @szImportDirectory = rtrim(@szImportDirectory) + '\IMPORT\'
END
ELSE
BEGIN
SELECT @szImportDirectory = rtrim(@szImportDirectory) + 'IMPORT\'
END

exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'Software\Euroshop\Retail\EPOS\Settings', 'BRANCHCODE', @szbranchcode OUTPUT

select @szBranchCode, @szImportDirectory
if (@bDoFTP = 1)
begin

select @szFilename = 'POSITF_TILL_' + @szbranchcode + '.zip'

exec @hr = sp_OACreate 'Eurpacker.ftp', @objFTP OUTPUT
if @hr <> 0
begin
exec sp_OAGetErrorInfo @objFTP, @szSrc OUT, @szDesc OUT
select @szMsg = 'Cannot create FTP object: Src = ' + @szSrc + ' Desc = ' + @szDesc
exec EurPOSITFReportError 'Eur_POSITF_RunImport', @szMsg, 1
return
end

select @szFTPUser = FTP_user, @szFTPPassword = FTP_Password, @szFTPServer = FTP_Server, @szFTPPath = FTP_Path_Outbound from polling_parameters

exec @hr = sp_OASetProperty @objFTP, 'username', @szFTPUser
exec @hr = sp_OASetProperty @objFTP, 'password', @szFTPPassword

exec @hr = sp_OAMethod @objFTP, 'SetRemoteSystem', @bDone OUT, @szFTPServer

select @szImportFileName = @szImportDirectory + 'POSITF_TILL_' + @szbranchcode + '.ZIP'

exec @hr = sp_OAMethod @objFTP, 'GetFile', @bDone OUT, @szFTPPath, @szFilename, @szImportFileName
select @hr, @bDone

if (@bDone = 0)
begin
select @szMsg = 'Cannot get FTP stock file from server: ' + @szFTPServer
exec EurPOSITFReportError 'Eur_POSITF_RunImport', @szMsg, 1
end

exec sp_OADestroy @objFTP

-- set @sCmd = @szImportDirectory + 'ftp_batch.bat'
-- exec master.dbo.xp_cmdshell @sCmd

end

if (@bDoUnPack = 1)
begin

select @szImportFileName = @szImportDirectory + 'POSITF_TILL_' + @szbranchcode + '.ZIP'

exec @hr = sp_OACreate 'Eurpacker.compress', @objZip OUTPUT
if @hr <> 0
begin
exec sp_OAGetErrorInfo @objZip, @szSrc OUT, @szDesc OUT
select @szMsg = 'Cannot create compress object: Src = ' + @szSrc + ' Desc = ' + @szDesc
exec EurPOSITFReportError 'Eur_POSITF_EPOS_COMPRESS_AND_SEND_TRANS', @szMsg, 1
return
end

exec sp_OASetProperty @objZip, 'bUseSpan', 0
exec sp_OASetProperty @objZip, 'bStoreFullPath', 0

exec @hr = sp_OAMethod @objZip, 'Extract', @bDone OUT, @szImportFileName, @szImportDirectory

if (@bDone = 0)
begin
select @szMsg = 'Cannot unzip stock file ' + @szImportFileName
exec EurPOSITFReportError 'Eur_POSITF_RunImport', @szMsg, 1
end

exec sp_OADestroy @objZip

-- set @sCmd = 'cscript ' + @szImportDirectory + 'stockimport.vbs ' + @szImportDirectory + ' ' + @szbranchcode
-- exec master.dbo.xp_cmdshell @sCmd
end

-- ensure postcode tables are present for BPH if BPH postcodes are installed
if (exists (select null from CUSTOMER_POSTCODE_MAPPING_DEFINITION where data_vendor_name = 'BPH Data' and mapping_name = 'BPH_DATA_STREET_LEVEL_SET'))
if (not exists (select null from sysobjects where name = 'POSTCODE_PCS'))
exec LoadBPHPostcodes '', '', '', '', '', '', 1, 1 -- loads empty BPH postcodes data set

-- search for non-trading till(s) and insert any initial dummy ZReads not present
exec Eur_POSITF_GenerateInitialZReads

set @szFilePath = @szImportDirectory + 'Currencies.csv'
exec EUR_POSITF_import_currencies @szFilePath
set @szFilePath = @szImportDirectory + 'ExchangeRates.csv'
exec EUR_POSITF_import_exchange_rates @szFilePath
set @szFilePath = @szImportDirectory + 'Countries.csv'
exec EUR_POSITF_import_countries @szFilePath
set @szFilePath = @szImportDirectory + 'Branches.csv'
exec EUR_POSITF_import_branches @szFilePath
set @szFilePath = @szImportDirectory + 'TillDefinitions.csv'
exec Eur_POSITF_Import_TillDefinitions @szFilePath
set @szFilePath = @szImportDirectory + 'POSDefinitions.csv'
exec Eur_POSITF_Import_POSDefinitions @szFilePath
set @szFilePath = @szImportDirectory + 'POSDefinitionsAttributes.csv'
exec Eur_POSITF_Import_POSDefinitions_Attributes @szFilePath
set @szFilePath = @szImportDirectory + 'Vat.csv'
exec EUR_POSITF_import_vat @szFilePath
set @szFilePath = @szImportDirectory + 'Vatdated.csv'
exec EUR_POSITF_import_vatdated @szFilePath
set @szFilePath = @szImportDirectory + 'Buyers.csv'
exec EUR_POSITF_import_buyers @szFilePath
set @szFilePath = @szImportDirectory + 'Product_Hierarchy.csv'
exec EUR_POSITF_import_producthierarchy @szFilePath
set @szFilePath = @szImportDirectory + 'Colours.csv'
exec EUR_POSITF_import_colours @szFilePath
set @szFilePath = @szImportDirectory + 'Sizes.csv'
exec EUR_POSITF_import_sizes @szFilePath
set @szFilePath = @szImportDirectory + 'SizeRangeHeader.csv'
exec EUR_POSITF_import_sizerangeheader @szFilePath
set @szFilePath = @szImportDirectory + 'SizeRangeDetail.csv'
exec EUR_POSITF_import_sizerangedetails @szFilePath
set @szFilePath = @szImportDirectory + 'Suppliers.csv'
exec EUR_POSITF_import_suppliers @szFilePath
set @szFilePath = @szImportDirectory + 'Timeyears.csv'
exec EUR_POSITF_import_time_year @szFilePath
set @szFilePath = @szImportDirectory + 'TimeSeasons.csv'
exec EUR_POSITF_import_time_season @szFilePath
set @szFilePath = @szImportDirectory + 'Timeweeks.csv'
exec EUR_POSITF_import_time_weeks @szFilePath
set @szFilePath = @szImportDirectory + 'Parameters.csv'
exec EUR_POSITF_IMPORT_GLOBAL_PARAMETERS @szFilePath
set @szFilePath = @szImportDirectory + 'ProductAnalTypes.csv'
exec EUR_POSITF_import_product_analtypes @szFilePath
set @szFilePath = @szImportDirectory + 'ProductAttributes.csv'
exec EUR_POSITF_import_product_attributes @szFilePath
set @szFilePath = @szImportDirectory + 'FlightDestinations.csv'
exec EUR_POSITF_import_flight_destination @szFilePath
set @szFilePath = @szImportDirectory + 'Products.csv'
exec EUR_POSITF_import_products @szFilePath
set @szFilePath = @szImportDirectory + 'SKUs.csv'
exec EUR_POSITF_import_skus @szFilePath
set @szFilePath = @szImportDirectory + 'DeliveryHeader.csv'
exec EUR_POSITF_import_delivery_header @szFilePath
set @szFilePath = @szImportDirectory + 'DeliveryDetails.csv'
exec EUR_POSITF_import_delivery_details @szFilePath
set @szFilePath = @szImportDirectory + 'MarkdownHeader.csv'
exec EUR_POSITF_import_markdown_header @szFilePath
set @szFilePath = @szImportDirectory + 'MarkdownDetails.csv'
exec EUR_POSITF_import_markdown_details @szFilePath
--set @szFilePath = @szImportDirectory + 'Customers.csv'
--exec EUR_POSITF_import_customers @szFilePath

-- import routines cannot key on GUID if data manually created at till - import disabled
--set @szFilePath = @szImportDirectory + 'EntityGroups.csv'
--exec Eur_POSITF_Import_EntityGroups @szFilePath
--set @szFilePath = @szImportDirectory + 'EntityLevels.csv'
--exec Eur_POSITF_Import_EntityLevels @szFilePath
--set @szFilePath = @szImportDirectory + 'EntityDefinitions.csv'
--exec Eur_POSITF_Import_EntityDefinitions @szFilePath
--set @szFilePath = @szImportDirectory + 'EntityAttributes.csv'
--exec Eur_POSITF_Import_EntityAttributes @szFilePath
--set @szFilePath = @szImportDirectory + 'EntityAttributesLCL.csv'
--exec Eur_POSITF_Import_EntityAttributesLCL @szFilePath
--set @szFilePath = @szImportDirectory + 'CustomerFields.csv'
--exec Eur_POSITF_Import_CustomerFields @szFilePath

set @szFilePath = @szImportDirectory + 'AccountTypes.csv'
exec EUR_POSITF_import_AccountTypes @szFilePath
set @szFilePath = @szImportDirectory + 'CustomerAccounts.csv'
exec EUR_POSITF_import_customer_accounts @szFilePath
set @szFilePath = @szImportDirectory + 'StockSales.csv'
exec EUR_POSITF_import_stocksales @szFilePath
set @szFilePath = @szImportDirectory + 'SS_users.csv'
exec EUR_POSITF_IMPORT_SS_USERS @szFilePath
set @szFilePath = @szImportDirectory + 'LegalTransactionTypes.csv'
exec EUR_POSITF_IMPORT_ATLPTT @szFilePath
set @szFilePath = @szImportDirectory + 'POHeader.csv'
exec EUR_POSITF_IMPORT_PO_HEADER @szFilePath
set @szFilePath = @szImportDirectory + 'PODetails.csv'
exec EUR_POSITF_IMPORT_PO_DETAILS @szFilePath
set @szFilePath = @szImportDirectory + 'LevelDescriptions.csv'
exec EUR_POSITF_IMPORT_LEVEL_DESCRIPTIONS @szFilePath
set @szFilePath = @szImportDirectory + 'IBTReasons.csv'
exec EUR_POSITF_IMPORT_IBT_REASONS @szFilePath
set @szFilePath = @szImportDirectory + 'ZReadCashDenominations.csv'
exec EUR_POSITF_IMPORT_ZREAD_CASH_DENOMINATIONS @szFilePath
set @szFilePath = @szImportDirectory + 'BranchAnal.csv'
exec EUR_POSITF_IMPORT_BRANCH_ANAL @szFilePath
-- DON'T do this - deposit customers are NOT shared between stores
--set @szFilePath = @szImportDirectory + 'DepositCustomers.csv'
--exec EUR_POSITF_IMPORT_TILL_CUSTOMERS @szFilePath
set @szFilePath = @szImportDirectory + 'USTax.csv'
exec EUR_POSITF_Import_US_Tax @szFilePath
set @szFilePath = @szImportDirectory + 'SIBTMaster.csv'
exec EUR_POSITF_Import_SIBT_Master @szFilePath
set @szFilePath = @szImportDirectory + 'SIBTDetails.csv'
exec EUR_POSITF_Import_SIBT_Details @szFilePath
set @szFilePath = @szImportDirectory + 'AnalysisQuestions.csv'
exec EUR_POSITF_IMPORT_ANALYSIS_QUESTIONS @szFilePath
set @szFilePath = @szImportDirectory + 'AnalysisAnswers.csv'
exec EUR_POSITF_IMPORT_ANALYSIS_ANSWERS @szFilePath
set @szFilePath = @szImportDirectory + 'AnalysisBranches.csv'
exec EUR_POSITF_IMPORT_ANALYSIS_BRANCHES @szFilePath
set @szFilePath = @szImportDirectory + 'HO_RECEIPTS.csv'
exec EUR_POSITF_IMPORT_HO_RECEIPTS @szFilePath

-- optional centralised control of SS_USERS from H/O
-- this feature does direct transfer between servers and needs no filepath
exec Eur_POSITF_LoadSSUsersFromHODirect

-- Promotions tables
set @szFilePath = @szImportDirectory + 'Promotions.csv'
exec EUR_POSITF_IMPORT_PROMOTIONS @szFilePath
set @szFilePath = @szImportDirectory + 'Promotion_Branches.csv'
exec EUR_POSITF_IMPORT_PROMOTION_BRANCHES @szFilePath
set @szFilePath = @szImportDirectory + 'Promotion_Offers.csv'
exec EUR_POSITF_IMPORT_PROMOTION_OFFERS @szFilePath
set @szFilePath = @szImportDirectory + 'Promotion_Offer_Conditions.csv'
exec EUR_POSITF_IMPORT_PROMOTION_OFFER_CONDITIONS @szFilePath
set @szFilePath = @szImportDirectory + 'Promotion_Offer_Condition_Setmembers.csv'
exec EUR_POSITF_IMPORT_PROMOTION_OFFER_CONDITION_SETMEMBERS @szFilePath
set @szFilePath = @szImportDirectory + 'Promotion_Offer_Condition_SKUs.csv'
exec EUR_POSITF_IMPORT_PROMOTION_OFFER_CONDITION_SKUS @szFilePath
set @szFilePath = @szImportDirectory + 'Promotion_Offer_Results.csv'
exec EUR_POSITF_IMPORT_PROMOTION_OFFER_RESULTS @szFilePath
set @szFilePath = @szImportDirectory + 'Promotion_Offer_Result_Setmembers.csv'
exec EUR_POSITF_IMPORT_PROMOTION_OFFER_RESULT_SETMEMBERS @szFilePath
set @szFilePath = @szImportDirectory + 'Promotion_Offer_Result_SKUs.csv'
exec EUR_POSITF_IMPORT_PROMOTION_OFFER_RESULT_SKUS @szFilePath

-- Force a reload of the Promotions EPOS working tables
exec Eur_EPOS_Promotions_FillWorkingTables 1

set @szFilePath = @szImportDirectory + 'TimeAttendanceUsers.csv'
exec EUR_POSITF_IMPORT_TIMEATTENDENCE_USERS @szFilePath
set @szFilePath = @szImportDirectory + 'TimeAttendanceCodes.csv'
exec EUR_POSITF_IMPORT_TIMEATTENDENCE_CODES @szFilePath

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 07:34:45

seems like your table doesnt have enough size in one of fields to hold data you're trying to bulk insert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JohnnieWalkerLover
Starting Member

5 Posts

Posted - 2011-11-03 : 08:21:19

The strange things are:

1) The job had 5 successful morning runs before erroring out;

2) The job only fails on the 24,752th row..

JohnnieWalkerLover
Starting Member

5 Posts

Posted - 2011-11-03 : 08:30:10

Would running a trace on the failing sproc help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 08:32:21

quote:
Originally posted by JohnnieWalkerLover

Would running a trace on the failing sproc help?

check the data coming in problem row first

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources