I'm new to C# programming and wanted to know what I'm doing wrong here. This script has been working fine for the past 2 weeks but now throws a invocation error message. Can anyone shed any light ?
The script runs from my local computer and connects to the database on a server.
Thanks Bal
public void Main()
{
string SqlConn = Dts.Variables["User::SQLConn"].Value.ToString();
string StartDate = Dts.Variables["PeriodStart"].Value.ToString();
string EndDate = Dts.Variables["PeriodEnd"].Value.ToString();
SqlConnection Conn = new SqlConnection(SqlConn);
SqlCommand TraderQuery = new SqlCommand("SELECT DISTINCT [Code] FROM [dbo].[Trader] ORDER BY [Code]", Conn);
DataTable TraderTable = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(TraderQuery);
adapter.Fill(TraderTable);
// Execute SQL Query
foreach (DataRow row in TraderTable.Rows)
{
string Trader = row["Code"].ToString();
string VolumeRebateQuery = (
"CREATE TABLE STA_VolumeRebate " +
"(" +
"Rank int IDENTITY(1,1) PRIMARY KEY," +
"TraderId varchar(100) NOT NULL," +
"Market varchar(10)," +
"Product varchar(30)," +
"Lots Float," +
"LotsOfHigherOrder Float" +
")" +
"DECLARE @TraderName VARCHAR(10) " +
"DECLARE @StartDate DATE " +
"DECLARE @EndDate DATE " +
"SET @TraderName = '" + Trader + "' " +
"SET @StartDate = '" + StartDate + "'" +
"SET @EndDate = '" + EndDate + "'" +
//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
";WITH MarketTotals AS (SELECT Trader.Code AS 'TraderId', MarketGrouping.Name AS 'Market', SUM(Trades.BuyQuantity + Trades.SellQuantity) AS 'Lots' " +
"FROM [dbo].[Trades] JOIN " +
"dbo.Account ON Trades.Account = Account.Id JOIN " +
"dbo.Trader ON Account.TraderId = Trader.Id JOIN " +
"dbo.Market ON Trades.Market = Market.Id JOIN " +
"dbo.MarketGrouping ON Market.MarketGrouping = MarketGrouping.Id " +
"WHERE Trader.Code = @TraderName AND Trades.[Date] BETWEEN @StartDate AND @EndDate " +
"AND Trades.Account NOT IN (1475) AND Trades.ExchangeFeeCurrency+Trades.ClearingFee<>0 " +
"GROUP BY Trader.Code, MarketGrouping.Name), " +
//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"TraderTotals AS (SELECT Trader.Code AS 'TraderId', MarketGrouping.Name AS 'Market', ProductGrouping.Code AS 'Product', SUM(Trades.BuyQuantity + Trades.SellQuantity) AS 'Lots' " +
"FROM [dbo].[Trades] JOIN " +
"dbo.Account ON Trades.Account = Account.Id JOIN " +
"dbo.Trader ON Account.TraderId = Trader.Id JOIN " +
"dbo.Market ON Trades.Market = Market.Id JOIN " +
"dbo.MarketGrouping ON Market.MarketGrouping = MarketGrouping.Id JOIN " +
"dbo.ProductDetails ON Trades.Product = ProductDetails.Id JOIN " +
"dbo.ProductGrouping ON ProductDetails.ProductId = ProductGrouping.Id " +
"WHERE Trader.Code = @TraderName AND Trades.[Date] BETWEEN @StartDate AND @EndDate " +
"AND Trades.Account NOT IN (1475) AND Trades.ExchangeFeeCurrency+Trades.ClearingFee<>0 " +
"GROUP BY Trader.Code, MarketGrouping.Name, ProductGrouping.Code), " +
//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"TempTBL AS (SELECT TraderTotals.*, MarketTotals.Lots AS 'LotsOfHigherOrder' " +
"FROM TraderTotals JOIN " +
"MarketTotals ON TraderTotals.Market = MarketTotals.Market), " +
"TempTBL2 AS (select TraderId,Market, Product, Lots,LotsOfHigherOrder, " +
"CASE WHEN TempTBL.Market IN ('CBOT','CME','COMEX','NYMEX') THEN 'CME' " +
" WHEN TempTBL.Market IN ('LIFFE','LCP') THEN 'LIFFE' ELSE TempTBL.Market END AS 'MarketGrouping' " +
"from TempTBL), " +
"TempTBL3 AS (select TraderId,MarketGrouping, SUM(Lots) AS 'LotsOfHigherOrder' " +
"from TempTBL2 " +
"GROUP BY TraderId,MarketGrouping), " +
"FinalTBL AS (SELECT TempTBL2.TraderId, TempTBL2.Market, TempTBL2.Product, TempTBL2.Lots, TempTBL3.LotsOfHigherOrder " +
"FROM TempTBL2 LEFT JOIN " +
"TempTBL3 ON TempTBL3.MarketGrouping = TempTBL2.MarketGrouping) " +
"SELECT TraderId, Market, Product, Lots, LotsOfHigherOrder " +
"INTO STG_VolumeRebate " +
"FROM FinalTBL " +
"ORDER BY LotsOfHigherOrder DESC, Lots DESC " +
"INSERT INTO dbo.STA_VolumeRebate (TraderId, Market, Product, Lots, LotsOfHigherOrder) " +
"SELECT TraderId, Market, Product, Lots, LotsOfHigherOrder " +
"FROM STG_VolumeRebate " +
"WHERE Market = 'ICE OTC' " +
"ORDER BY LotsOfHigherOrder DESC, Lots DESC " +
"INSERT INTO dbo.STA_VolumeRebate (TraderId, Market, Product, Lots, LotsOfHigherOrder) " +
"SELECT TraderId, Market, Product, Lots, LotsOfHigherOrder " +
"FROM STG_VolumeRebate " +
"WHERE Market <> 'ICE OTC' " +
"ORDER BY LotsOfHigherOrder DESC, Lots DESC " +
"INSERT INTO dbo.VolumeRebate " +
"SELECT [Rank]" +
",[TraderId]" +
",[Market]" +
",[Product]" +
",[Lots]" +
",[LotsOfHigherOrder]" +
",(SELECT SUM(Lots) FROM dbo.STA_VolumeRebate) - SUM([Lots]) OVER ( PARTITION BY [TraderId] " +
"ORDER BY [Rank] " +
"RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'LotsOfHigherOrder' " +
" FROM dbo.STA_VolumeRebate" +
" ORDER BY [Rank]" +
"DROP TABLE STG_VolumeRebate DROP TABLE STA_VolumeRebate");
//System.Windows.Forms.MessageBox.Show(VolumeRebateQuery);
//System.IO.File.WriteAllText(@"C:\QueryOutput.txt", VolumeRebateQuery);
SqlCommand VolumeRebateExecute = new SqlCommand(VolumeRebateQuery, Conn);
VolumeRebateExecute.Connection.Open();
MessageBox.Show("Trying to execute query for Trader " + Trader + " now");
VolumeRebateExecute.ExecuteNonQuery();
VolumeRebateExecute.Connection.Close();
}
I found the solution ! there is a timeout set on ADO Connections to 30 seconds and the one account took longer than this. I added the below line to resolve the problem ! Thanks All for suggestions and improving my code ! Bal
"VolumeRebateExecute.CommandTimeout = 120;"