November 27, 2008 at 4:43 am
I am runing my SP from MSSMS:
execute 'SP Name'
And im getting the following error:
Msg 50000, Level 16, State 1, Procedure procImportAllFiles, Line 59
Import Updates Failed
Nothing else.... Where can I find more information on this error?
Where do I start?
The SP updates some tables using information from other tables.
the other tables have been populated via a SSIS package.
Thanks All
November 27, 2008 at 4:49 am
It is hard to say what the problem is with out seeing the procedure that is causing the problems.. Post it here is you want some constuctive help..
November 27, 2008 at 4:56 am
USE [book]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[procImportAllFiles]
AS
SET NOCOUNT ON
DECLARE @Today DATETIME
DECLARE @ImportDate DATETIME
SET @ImportDate = Convert(SMALLDATETIME,(SELECT convert(char(8),date) FROM CheckDate),3)
SET @Today =Convert(SMALLDATETIME, (SELECT GETDATE()),102)
--We only update and append if the checkpoint is a day before todays date
IF (DATEDIFF(day,@ImportDate,@Today) = 1 )
BEGIN
--After import update the stock table
UPDATE dbo.stock SET Riskless_Principal = 1 WHERE Riskless_Principal = '*RP'
UPDATE dbo.stock SET Riskless_Principal = 0 WHERE Riskless_Principal = ''
UPDATE dbo.stock SET FX_Rate = 1 WHERE Riskless_Principal = ''
UPDATE dbo.stock SET FX_Rate = 1 WHERE Riskless_Principal = 0
--Update the MarketSector in stock to UN if it is not found in sector
UPDATE dbo.stock SET MarketSector = 'Unk' WHERE MarketSector Not In
(Select MarketSectorNew FROM tblSector)
--Add any new stocks to tblStocks
INSERT INTO dbo.tblStocks(SEDOL, ISIN, RIC, Long_Name, Short_Name, )
SELECT dbo.stock.SEDOL,dbo.stock.ISIN, dbo.stock.RIC, dbo.stock.StockLongName, dbo.stock.StockShortName,
dbo.stock.IndustrialClassification, dbo.stock.MarketSector, dbo.stock.MarketCap, dbo.stock.MidPrice, dbo.stock.NumOfSharesInIssue
FROM dbo.stock LEFT OUTER JOIN dbo.tblStocks ON dbo.stock.ISIN = dbo.tblStocks.ISIN WHERE (dbo.tblStocks.ISIN IS NULL)
GROUP BY dbo.stock.SEDOL,dbo.stock.ISIN, dbo.stock.RIC, dbo.stock.StockLongName, dbo.stock.StockShortName,
dbo.stock.IndustrialClassification, dbo.stock.MarketSector, dbo.stock.MarketCap, dbo.stock.MidPrice, dbo.stock.NumOfSharesInIssue
--Update the MarketSector to ShortDescription from the stocks table by joining on the ISIN
UPDATE dbo.stock set marketsector = dbo.sector.shortDescription FROM dbo.stock INNER JOIN dbo.tblStocks
ON dbo.stock.ISIN = dbo.tblStocks.ISIN INNER JOIN dbo.sector ON dbo.tblStocks.MarketSector = dbo.sector.MarketSectorNew
UPDATE dbo.stock set marketsector='Unk' Where marketsector is null
--Add new currency and clients to appropriate tables
INSERT INTO dbo.tblCurrency(Code) SELECT dbo.vwImportNewCurrency.Currency FROM dbo.vwImportNewCurrency
INSERT INTO dbo.tblClients(Client_Short_Name,Client_Long_Name,CountryCode,ClientID) SELECT
dbo.vwImportAllNewClientIDs.Client, dbo.vwImportAllNewClientIDs.Client, dbo.vwImportAllNewClientIDs.CountryCode,
dbo.vwImportAllNewClientIDs.ClientID FROM dbo.vwImportAllNewClientIDs
--Append the stock table to tblDeals
INSERT INTO dbo.tblDeals(dbo.tblDeals.Client,dbo.tblDeals.Bought_Sold,dbo.tblDeals.RIC,dbo.tblDeals.Trade_Date,dbo.
tblDeals.Price,
dbo.tblDeals.Counterparty_Code,dbo.tblDeals.Dealer,dbo.tblDeals.Commission,dbo.tblDeals.Consideration,dbo.tblDeals.Quantity,dbo.
tblDeals.Currency,dbo.tblDeals.SEDOL,dbo.tblDeals.Participant_ID,dbo.tblDeals.FIDESSA_Ref,dbo.tblDeals.FX_Rate,dbo.
tblDeals.Local_Commission,dbo.tblDeals.Bgn_Cond1,dbo.tblDeals.Bgn_Cond2,dbo.tblDeals.Bgn_Cond3,dbo.tblDeals.Bgn_Cond4,
dbo.tblDeals.Dealing_Capacity,dbo.tblDeals.RisklessPrincipal,dbo.tblDeals.Sector,dbo.tblDeals.ClientID) SELECT Client,BoughtSold,
RIC,Trade_Date,Price,Counterparty_Code,Dealer,Commission,Consideration,Quantity,Currency,SEDOL,Participant_ID,FIDESSA_Ref,
FX_Rate,Local_Commission,Bgn_Cond1,Bgn_Cond2,Bgn_Cond3,Bgn_Cond4,Dealing_Capacity,Riskless_Principal,MarketSector, ClientID
FROM dbo.stock
--Delete all records from the stock table
DELETE dbo.stock
--Update all MM in tblStocks to False
UPDATE dbo.tblStocks SET MarketMaking = 0 WHERE (dbo.tblStocks.SE<>'0000002')
--Update all MM in tblStocks using the imported table
UPDATE dbo.tblStocks SET MarketMaking = 1 FROM dbo.house RIGHT OUTER JOIN dbo.tblStocks ON
dbo.house.ISIN_CODE = dbo.tblStocks.ISIN WHERE (dbo.house.ISIN_CODE <> '')
--Delete recs from house
DELETE dbo.house
--Add sector code and append to tblProfitAndLoss
INSERT INTO dbo.tblProfitAndLoss (FiscalAccountID, PLAmount, RIC, ShortName, SEDOL, [Date], Sector)
SELECT dbo.MKTMKG_PL.FiscalAmountID, dbo.MKTMKG_PL.PLAmount, dbo.MKTMKG_PL.RIC, dbo.MKTMKG_PL.ShortName,
dbo.MKTMKG_PL.SEDOL, dbo.MKTMKG_PL.TradeDate, dbo.sector.ShortDescription FROM dbo.sector INNER JOIN dbo.tblStocks
ON dbo.sector.MarketSectorNew = dbo.tblStocks.MarketSector RIGHT OUTER JOIN dbo.MKTMKG_PL
ON dbo.tblStocks.SEDOL = dbo.MKTMKG_PL.SEDOL
--Update tblProfitAndLoss to fill any null sectors with unknown
UPDATE dbo.tblProfitAndLoss SET Sector='Unk' WHERE Sector IS Null
--Delete records from MKTMKG_PL
DELETE dbo.MKTMKG_PL
--Delete records from CheckDate
DELETE dbo.CHECKDATE
END
--Otherwise the files should not be updated
ELSE
BEGIN
Execute procImportFailClearTables
RAISERROR('Import Updates Failed',16,1)
END
--ADD CALLS TO STORED PROC TO DELETE FILES FROM E:DRIVE
November 27, 2008 at 5:04 am
That's obvious I think. When you executed the SP it goes to the ELSE at the end and executes procImportFailClearTables and raises the error. So it's actually behaving just as designed.
[font="Verdana"]Markus Bohse[/font]
November 27, 2008 at 5:15 am
right sorry, your right I needed to remove that anyway.
That has now been removed but, now I have the following error:
Msg 8152, Level 16, State 4, Procedure procImportAllFiles, Line 33
String or binary data would be truncated.
The statement has been terminated.
thank you very much
November 27, 2008 at 5:31 am
It looks like you are inserting a value into a row that is too big,
check that that your input datatypes and size match that of the column they are going to.
Also if you could fix the wrapping on your post, it would be easier to read.
November 27, 2008 at 5:50 am
Sorry about the formatting.
Step One
I have a SSIS package which picks up and loads data from a flatfile (CSV) into 3 tables.
Step two
The SP then takes information from temp tables and laods it into 3 main tables
Within the SSIS package most of the OutPutColumnWidths are set to 50 (default).
Is this the vaule i need to change to match the column size in table?
thanks
November 27, 2008 at 6:37 am
pri.amin (11/27/2008)
Sorry about the formatting.
Can you edit your post to fix it please?
Run the updates and the inserts in that proc one by one until you find the one throwing the error. That'll tell you what needs changing where.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 8:13 am
Thanks VERY good idea!
Ok so the following line is bring the error:
INSERT INTO dbo.tblClients(Client_Short_Name,Client_Long_Name,CountryCode,ClientID) SELECT
dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.CountryCode,
dbo.vwImportAllNewClientIDs.ClientID
FROM dbo.vwImportAllNewClientIDs
The View is a combination of 2 tables one of which is being importied in my SSIS package.
Do I just need to make sure the SSIS package is importing the same datatypes?
November 27, 2008 at 8:23 am
yes check that the data types in the views match the data types in the table you are inserting data into.
You may be able to fix this issue in the procedure with the use of CAST or by truncating some of your values.
November 28, 2008 at 2:20 am
Ok cant seem to get around this,
the error is:
Msg 8152, Level 16, State 4, Procedure procImportAllFiles, Line 33
String or binary data would be truncated.
The statement has been terminated.
This part of the SP is failing:
INSERT INTO dbo.tblClients
(Client_Short_Name,
Client_Long_Name,
CountryCode,
ClientID)
SELECT dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.Client, dbo.vwImportAllNewClientIDs.CountryCode, dbo.vwImportAllNewClientIDs.ClientID
FROM dbo.vwImportAllNewClientIDs
The following are the table details:
dbo.tblClients
Client_short_Name (nvarchar(30),not null)
Client_long_name (nvarchar(100), null)
CountryCode (varchar(5), null)
ClientID (PK,nvarchar(10), notnull)
VwImportAll
Client (varchar(255),null)
ClientID (varchar(20),null)
CountryCode (varchar(50),null)
View uses information from the following table:
dbo.tblBarg - Populated by my SSIS package from a flat file
Client (varchar(255),null)
ClientID (varchar(20),null)
CountryCode (varchar(50),null)
ANY HELP IS MUCH APPRECIATED!!!!
November 28, 2008 at 3:57 am
Truncation can occur any time you're inserting from a wider column into a narrower column. In the insert statement you posted, you're doing that 4 times.
You're inserting a varchar(255) (Client) into an nvarchar(30) (Client_Short_Name) - possible truncation
You're inserting a varchar(255) (Client) into an nvarchar(100) (Client_Long_Name) - possible truncation
You're inserting a varchar(50) (CountryCode) into an varchar(5) (CountryCode) - possible truncation
You're inserting a varchar(20) (ClientID) into an nvarchar(10) (ClientID) - possible truncation
If the source table has any string values in any of its columns that are wider than the destination, you'll get a truncation.
Look through the source table and see where you have values that won't fit into the columns that you're trying to insert them into.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2008 at 4:06 am
Thanks,
I have changed all the vaules in the tblclient table to have the correct values, as you pointed out.
Now im having the following error:
Msg 2627, Level 14, State 1, Procedure procImportAllFiles, Line 33
Violation of PRIMARY KEY constraint 'PK_tblClients'. Cannot insert duplicate key in object 'dbo.tblClients'.
The statement has been terminated.
ClientID is PK in the tblclient table.....
THANKS again!!
November 28, 2008 at 4:17 am
As a wild guess, you're trying to insert a duplicate primary key value. ie, you're trying to insert a value for ClientID that's already there. Primary key must be unique.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2008 at 4:17 am
Have you actually looked at books online to see what might be causing all those errors... no offense but a simple F1 hit could get you almost all those answers much faster than anyone of this forum!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply