January 27, 2009 at 7:28 am
I can post the SP if neded but I would like to get an understand of what the following error(s) mean:
Scenario
SSIS Package
1. Imports 4 files into 4 temp tables
2. SP copies the relevent details into 2 main tables same SP clears the 4 temp tables.
Errors
Error when I run the SSIS package manually:
Error: 0xC002F210 at ProcImportAllFiles, Execute SQL Task: Executing the
query "exec dbo.procImportAllFiles" failed with the following error: "Subquery
returned more than 1 value. This is not permitted when the subquery follows
=, !=, = or when the subquery is used as an expression.". Possible
failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Error when I run the job which calls the SSIS package:
Description: Executing the query "exec dbo.procImportAllFiles" failed with the
following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly. End
Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 14:19:14 Finished: 14:19:17 Elapsed: 3.203 seconds. The
package execution failed. The step failed.
PLEASE HELP!
(Going mad :crazy:)
January 27, 2009 at 11:09 am
Does your stored procedure that you use in the EXEC SQL Task have any input parameters? If it does are they mapped to the correct types. If you have an integer parameter in the procedure params, map it to LONG and not to NUMERIC. If you do not have any params, check the result set of your stored procedure. Does it return a row, full dataset or any out params? let me know and will assist further.
January 27, 2009 at 11:14 am
The problem is here:
"Subquery
returned more than 1 value. This is not permitted when the subquery follows
=, !=, = or when the subquery is used as an expression.". Possible
failure reasons: Problems with the query,
For additional help, we will need to see the code. May also need table DDL, sample data, and expected results. If your not sure how to do all this, please read the first article I have referenced below in my signature block. If you follow the guidelines in that article you will be able to post everything we need to return a tested solution to your problem.
January 27, 2009 at 11:15 am
It's a problem with a subquery in the stored procedure dbo.procImportAllFiles.
Best post the sproc.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 28, 2009 at 1:04 am
Below is the SP in question, (sorry I was having problems with the formatting).
USE [DealbookV2]
GO
/****** Object: StoredProcedure [dbo].[procImportAllFiles] Script Date: 01/28/2009 07:57:54 ******/
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 bargains table
UPDATE dbo.Bargains SET Riskless_Principal = 1 WHERE Riskless_Principal = 'RP'
UPDATE dbo.Bargains SET Riskless_Principal = 0 WHERE Riskless_Principal = ''
UPDATE dbo.Bargains SET FX_Rate = 1 WHERE Riskless_Principal = ''
UPDATE dbo.Bargains SET FX_Rate = 1 WHERE Riskless_Principal = 0
--Update the MarketSector in Bargains to UN if it is not found in tblFTSESector
UPDATE dbo.Bargains SET MarketSector = 'Unk' WHERE MarketSector Not In(Select MarketSectorNew FROM tblFTSESector)
--Add any new stocks to tblStocks
INSERT INTO dbo.tblStocks(SEDOL, ISIN, RIC, Long_Name,
Short_Name, IndustrialClassification, MarketSector, MarketCap, MidPrice, NumOfSharesInIssue) SELECT dbo.Bargains.SEDOL,dbo.Bargains.ISIN,
dbo.Bargains.RIC, dbo.Bargains.StockLongName, dbo.Bargains.StockShortName, dbo.Bargains.IndustrialClassification, dbo.Bargains.MarketSector,
dbo.Bargains.MarketCap, dbo.Bargains.MidPrice, dbo.Bargains.NumOfSharesInIssue FROM dbo.Bargains LEFT OUTER JOIN dbo.tblStocks ON dbo.Bargains.ISIN =
dbo.tblStocks.ISIN WHERE
(dbo.tblStocks.ISIN IS NULL) GROUP BY
dbo.Bargains.SEDOL,dbo.Bargains.ISIN, dbo.Bargains.RIC,
dbo.Bargains.StockLongName, dbo.Bargains.StockShortName,
dbo.Bargains.IndustrialClassification, dbo.Bargains.MarketSector,
dbo.Bargains.MarketCap, dbo.Bargains.MidPrice, dbo.Bargains.NumOfSharesInIssue
--Update the MarketSector to ShortDescription from the stocks
table by joining on the ISIN
UPDATE dbo.bargains set marketsector =
dbo.tblFTSESector.shortDescription FROM dbo.Bargains INNER JOIN dbo.tblStocks ON dbo.Bargains.ISIN = dbo.tblStocks.ISIN INNER
JOIN dbo.tblFTSESector ON dbo.tblStocks.MarketSector =
dbo.tblFTSESector.MarketSectorNew
UPDATE dbo.bargains 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 bargains table to tblDeals
INSERT INTO dbo.tblDeals
(dbo.tblDeals.Client,dbo.tblDeals.Bought_Sold,dbo.tblDeals.RIC,dbo.tblDeals.Tra
de_Date,dbo.tblDeals.Price,dbo.tblDeals.Counterparty_Code,dbo.tblDeals.Deale
r,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.tblDeal
s.Sector,dbo.tblDeals.ClientID,dbo.tblDeals.MarketMaking) SELECT distinct dbo.Bargains.Client,
dbo.Bargains.BoughtSold,dbo.Bargains.RIC,dbo.Bargains.Tra
de_Date,dbo.Bargains.Price,dbo.Bargains.Counterparty_Code,dbo.Bargains.Dealer,dbo.Bargains.Commission,
dbo.Bargains.Consideration,dbo.Bargains.Quantity,dbo.Bargains.Currency,dbo.Bargains.SEDOL,dbo.Bargains.Participant_ID,
dbo.Bargains.FIDESSA_Ref,dbo.Bargains.FX_Rate,dbo.Bargains.Local_Commission,dbo.B
argains.Bgn_Cond1,dbo.Bargains.Bgn_Cond2,dbo.Bargains.Bgn_Cond3,dbo.Bargains.Bgn_Cond4,dbo.Bargains.Dealing_Capacity,
dbo.Bargains.Riskless_Principal,dbo.Bargains.MarketSector, dbo.Bargains.ClientID, isnull
(dbo.tblStocks.MarketMaking,0) FROM dbo.Bargains left join tblStocks on Bargains.SEDOL=tblStocks.SEDOL
--Delete all records from the Bargains table
DELETE dbo.Bargains
--Update all MM in tblStocks to False
UPDATE dbo.tblStocks SET MarketMaking = 0 WHERE
(dbo.tblStocks.SEDOL <> '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, PLAmountLocal, FXRate,
Currency, MarketMaking, Book) 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.tblFTSESector.ShortDescription, dbo.MKTMKG_PL.PLAmountLocal,
dbo.MKTMKG_PL.FXRate, dbo.MKTMKG_PL.Currency, isnull(dbo.tblStocks.MarketMaking,0), dbo.MKTMKG_PL.Book FROM
dbo.tblFTSESector INNER JOIN dbo.tblStocks ON
dbo.tblFTSESector.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 EDRIVE
January 28, 2009 at 5:48 am
Nobody has replied to this because it was difficult to read through all of the code. I've spent a few moments reformatting it, in particular putting in table aliases, and the end result is far more readable, in fact most people who use this forum would spot the most likely candidate for the fault you have reported in seconds.
Here's the reformatted code:
USE [DealbookV2]
GO
/****** Object: StoredProcedure [dbo].[procImportAllFiles] Script Date: 01/28/2009 07:57:54 ******/
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 bargains table
UPDATE dbo.Bargains SET Riskless_Principal = 1 WHERE Riskless_Principal = 'RP'
UPDATE dbo.Bargains SET Riskless_Principal = 0 WHERE Riskless_Principal = ''
UPDATE dbo.Bargains SET FX_Rate = 1 WHERE Riskless_Principal = ''
UPDATE dbo.Bargains SET FX_Rate = 1 WHERE Riskless_Principal = 0
--Update the MarketSector in Bargains to UN if it is not found in tblFTSESector
UPDATE dbo.Bargains SET MarketSector = 'Unk' WHERE MarketSector Not In(Select MarketSectorNew FROM tblFTSESector)
--Add any new stocks to tblStocks
INSERT INTO dbo.tblStocks (SEDOL, ISIN, RIC, Long_Name,
Short_Name, IndustrialClassification, MarketSector, MarketCap, MidPrice, NumOfSharesInIssue)
SELECT b.SEDOL, b.ISIN, b.RIC, b.StockLongName,
b.StockShortName, b.IndustrialClassification, b.MarketSector,
b.MarketCap, b.MidPrice, b.NumOfSharesInIssue
FROM dbo.Bargains b
LEFT OUTER JOIN dbo.tblStocks s ON b.ISIN = s.ISIN
WHERE (s.ISIN IS NULL)
GROUP BY b.SEDOL, b.ISIN, b.RIC, b.StockLongName, b.StockShortName,
b.IndustrialClassification, b.MarketSector, b.MarketCap, b.MidPrice, b.NumOfSharesInIssue
--Update the MarketSector to ShortDescription from the stocks table by joining on the ISIN
UPDATE b set marketsector = f.shortDescription
FROM dbo.Bargains b
INNER JOIN dbo.tblStocks s ON b.ISIN = s.ISIN
INNER JOIN dbo.tblFTSESector f ON s.MarketSector = f.MarketSectorNew
UPDATE dbo.bargains set marketsector = 'Unk' Where marketsector is null
--Add new currency and clients to appropriate tables
INSERT INTO dbo.tblCurrency(Code)
SELECT Currency
FROM dbo.vwImportNewCurrency
INSERT INTO dbo.tblClients(Client_Short_Name,Client_Long_Name,CountryCode,ClientID)
SELECT Client, Client, CountryCode, ClientID
FROM dbo.vwImportAllNewClientIDs
--Append the bargains table to tblDeals
INSERT INTO dbo.tblDeals
(Client, Bought_Sold, 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, RisklessPrincipal, Sector,
ClientID, MarketMaking)
SELECT distinct b.Client, b.BoughtSold, b.RIC, b.Trade_Date, b.Price,
b.Counterparty_Code, b.Dealer, b.Commission, b.Consideration, b.Quantity,
b.Currency, b.SEDOL, b.Participant_ID, b.FIDESSA_Ref, b.FX_Rate,
b.Local_Commission, b.Bgn_Cond1, b.Bgn_Cond2, b.Bgn_Cond3, b.Bgn_Cond4,
b.Dealing_Capacity, b.Riskless_Principal, b.MarketSector, b.ClientID,
isnull(s.MarketMaking,0)
FROM dbo.Bargains b
left join tblStocks s on b.SEDOL = s.SEDOL
--Delete all records from the Bargains table
DELETE dbo.Bargains
--Update all MM in tblStocks to False
UPDATE dbo.tblStocks SET MarketMaking = 0 WHERE SEDOL <> '0000002'
--Update all MM in tblStocks using the imported table
UPDATE s SET MarketMaking = 1
FROM dbo.house h
RIGHT OUTER JOIN dbo.tblStocks s ON h.ISIN_CODE = s.ISIN
WHERE (h.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, PLAmountLocal, FXRate,
Currency, MarketMaking, Book)
SELECT m.FiscalAmountID,
m.PLAmount, m.RIC, m.ShortName, m.SEDOL, m.TradeDate,
f.ShortDescription, m.PLAmountLocal, m.FXRate,
m.Currency, isnull(s.MarketMaking,0), m.Book
FROM dbo.tblFTSESector f
INNER JOIN dbo.tblStocks s ON f.MarketSectorNew = s.MarketSector
RIGHT OUTER JOIN dbo.MKTMKG_PL m ON s.SEDOL = m.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 EDRIVE
Here's the most likely candidate for the fault:
SET @ImportDate = Convert(SMALLDATETIME,(SELECT convert(char(8),date) FROM CheckDate),3)
How many rows does the table CheckDate have? If it's more than one, there's your error.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 28, 2009 at 5:57 am
Thanks again for your time!
CheckDate imports only one row one column
27/01/09
Any more suggestions?
January 28, 2009 at 6:20 am
A Little Help Please (1/28/2009)
Thanks again for your time!CheckDate imports only one row one column
27/01/09
Any more suggestions?
Yes - run this:SELECT convert(char(8),date) FROM DealbookV2.dbo.CheckDate
What happens when you run the stored procedure manually from QA or SMS?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 28, 2009 at 6:41 am
When I run you select statment I just get one row returned.
I think I found the issue, can you tell me how i would truncate the following line of code, the bit which is highlighted.
I need to truncate the column to 30 charachters
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
January 28, 2009 at 6:58 am
A Little Help Please (1/28/2009)
When I run you select statment I just get one row returned.I think I found the issue, can you tell me how i would truncate the following line of code, the bit which is highlighted.
I need to truncate the column to 30 charachters
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
Look up the LEFT function in BOL.
It's unlikely that this is causing your error, as the message is completely different.
As asked above, what happens when you run the stored procedure directly from QA or SMS? Please take the time to do this, as it will make diagnosis far more certain than it is presently.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 28, 2009 at 7:13 am
If i run the SP line by line in QA it failes when it gets to the line:
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
Error:
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
When I run just the SELECT part of the code above i.e.:
SELECT
dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.CountryCode,
dbo.vwImportAllNewClientIDs.ClientID
FROM dbo.vwImportAllNewClientIDs
I can see that the first column (dbo.vwImportAllNewClientIDs.Client) contains 32 characters BUT the dbo.tblClients --> Client_Short_Name is set to varchar 30
Could this be the problem?
January 28, 2009 at 7:18 am
A Little Help Please (1/28/2009)
Could this be the problem?
It is a problem, which of course you can deal with because you've now read about the LEFT function in BOL.
So what happens when you run the fixed code?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 28, 2009 at 7:24 am
Thanks a lot this worked, changing the line in the SP
INSERT INTO
dbo.tblClients(Client_Short_Name,Client_Long_Name,CountryCode,ClientID)
SELECT LEFT
(dbo.vwImportAllNewClientIDs.Client, 30),
dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.CountryCode,
dbo.vwImportAllNewClientIDs.
ClientID
FROM dbo.vwImportAllNewClientIDs
January 28, 2009 at 7:37 am
You would benefit from adopting table aliases. Really.
Before
INSERT INTO
dbo.tblClients(Client_Short_Name,Client_Long_Name,CountryCode,ClientID)
SELECT LEFT
(dbo.vwImportAllNewClientIDs.Client, 30),
dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.CountryCode,
dbo.vwImportAllNewClientIDs.
ClientID
FROM dbo.vwImportAllNewClientIDs
After
INSERT INTO dbo.tblClients (Client_Short_Name, Client_Long_Name, CountryCode, ClientID)
SELECT LEFT(c.Client, 30), c.Client, c.CountryCode, c.ClientID
FROM dbo.vwImportAllNewClientIDs c
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 28, 2009 at 9:04 am
That looks much better thanks il try it!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply