I don't know what the problem was, but for now it seem to be working okay.
What I did is recreating the query line by line and checking step by step.
Thanks for all the help.
Aldo.
string otdStoredProc = ""
+ " if not exists(Select * from sysobjects where name = 'Condor_Telcon2006_ON_TIME_DELIVERY_20081002000') "
+ " begin "
+ " EXEC dbo.sp_executesql @statement = N' "
+ " CREATE PROCEDURE Condor_Telcon2006_ON_TIME_DELIVERY_20081002000 "
+ " @FirstDate varchar(25), "
+ " @LastDate varchar(25) "
+ " AS "
+ " SELECT * FROM "
+ " ( "
+ " SELECT DISTINCT "
+ " GROUPING([ID]) AS ''grp'', "
+ " CASE WHEN GROUPING([Accounts.FULLNAME]) = 1 THEN ''...Sub Total'' ELSE [Accounts.FULLNAME] END AS ''Accounts.FullName'', "
+ " Sum([WorkOrders]) AS ''WorkOrders'', "
+ " Sum([WO_FromStock]) AS ''WO_FromStock'', "
+ " Sum([WO_ToManuf]) AS ''WO_ToManuf'', "
+ " Sum([Early]) AS ''Early'', "
+ " Sum([OnTime]) AS ''OnTime'', "
+ " Sum([1Week]) AS ''1Week'', "
+ " Sum([2Weeks]) AS ''2Weeks'', "
+ " Sum([1Month]) AS ''1Month'', "
+ " Sum([OverMonth]) AS ''OverMonth'', "
+ " CASE WHEN Sum([WO_ToManuf]) > 0 THEN Cast(Sum([Early]) as real) / Sum([WO_ToManuf]) ELSE 0 END AS ''%Early'', "
+ " CASE WHEN Sum([WO_ToManuf]) > 0 THEN Cast(Sum([OnTime]) as real) / Sum([WO_ToManuf]) ELSE 0 END AS ''%OnTime'', "
+ " CASE WHEN Sum([WO_ToManuf]) > 0 THEN Cast(Sum([1Week]) as real) / Sum([WO_ToManuf]) ELSE 0.00 END AS ''%1Week'', "
+ " CASE WHEN Sum([WO_ToManuf]) > 0 THEN Cast(Sum([2Weeks]) as real) / Sum([WO_ToManuf]) ELSE 0.00 END AS ''%2Weeks'', "
+ " CASE WHEN Sum([WO_ToManuf]) > 0 THEN Cast(Sum([1Month]) as real) / Sum([WO_ToManuf]) ELSE 0.00 END AS ''%1Month'', "
+ " CASE WHEN Sum([WO_ToManuf]) > 0 THEN Cast(Sum([OverMonth]) as real) / Sum([WO_ToManuf]) ELSE 0.00 END AS ''%OverMonth'', "
+ " CASE WHEN Sum([WO_ToManuf]) > 0 THEN (Cast(Sum([Early]) as real) / Sum([WO_ToManuf])) + (Cast(Sum([OnTime]) as real) / Sum([WO_ToManuf])) ELSE 0 END AS ''%OTD'', "
+ " CASE WHEN Sum([WO_ToManuf]) > 0 THEN (Cast(Sum([1Week]) as real) / Sum([WO_ToManuf])) + (Cast(Sum([2Weeks]) as real) / Sum([WO_ToManuf])) + (Cast(Sum([1Month]) as real) / Sum([WO_ToManuf])) + (Cast(Sum([OverMonth]) as real) / Sum([WO_ToManuf])) ELSE 0.00 END AS ''%PassDue'', "
+ " CASE WHEN Sum([WO_ToManuf]) > 0 THEN (Cast(Sum([Early]) as real) / Sum([WO_ToManuf])) + (Cast(Sum([OnTime]) as real) / Sum([WO_ToManuf])) + (Cast(Sum([1Week]) as real) / Sum([WO_ToManuf])) ELSE 0 END AS ''%SupplyGrade'' "
+ " FROM "
+ " (SELECT "
+ " 1 AS ''ID'', "
+ " Accounts.FULLNAME AS ''Accounts.FullName'', "
+ " CASE WHEN StockMoves.DETAILS is not Null THEN 1 ELSE 0 END AS ''WorkOrders'', "
+ " CASE WHEN StockMoves.REFERENCE = 0 THEN 1 ELSE 0 END AS ''WO_FromStock'', "
+ " CASE WHEN StockMoves.REFERENCE <> 0 THEN 1 ELSE 0 END AS ''WO_ToManuf'', "
+ " CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) < 0 THEN 1 ELSE 0 END AS ''Early'', "
+ " CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) = 0 THEN 1 ELSE 0 END AS ''OnTime'', "
+ " CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) BETWEEN 1 AND 7 THEN 1 ELSE 0 END AS ''1Week'', "
+ " CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) BETWEEN 8 AND 14 THEN 1 ELSE 0 END AS ''2Weeks'', "
+ " CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) BETWEEN 15 AND 30 THEN 1 ELSE 0 END AS ''1Month'', "
+ " CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) > 30 THEN 1 ELSE 0 END AS ''OverMonth'' "
+ " FROM ITEMS AS Items "
+ " INNER JOIN STOCKMOVES AS StockMoves "
+ " INNER JOIN STOCK AS Stock ON StockMoves.STOCKID = Stock.ID "
+ " INNER JOIN DOCUMENTSDEF AS DocumentsDef ON StockMoves.DOCUMENTID = DocumentsDef.DOCUMENTID "
+ " INNER JOIN ACCOUNTS AS Accounts ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY ON Items.ITEMKEY = StockMoves.ITEMKEY "
+ " LEFT JOIN StockMoves AS StockMoves2 ON StockMoves.DETAILS = StockMoves2.DETAILS AND StockMoves2.DOCUMENTID In (6, 36) "
+ " WHERE "
+ " Accounts.SORTGROUP Between 3000 And 3999 "
+ " AND Items.ITEMKEY Not In (''ZI'',''ZP'',''*'',''.'',''@'',''#'') "
+ " AND Stock.VALUEDATE BETWEEN convert(datetime, @FirstDate, 121) AND convert(datetime, @LastDate, 121) "
+ " AND StockMoves.DOCUMENTID In (1, 2, 35) "
+ " )AS A "
+ " GROUP BY "
+ " [ID], "
+ " [Accounts.FULLNAME] "
+ " WITH ROLLUP "
+ " ) AS B "
+ " WHERE "
+ " grp = 0 AND [WO_ToManuf] > 0 "
+ " 'end "
+ "";
private void OTDWorker_DoWork(object sender, DoWorkEventArgs e)
{
// Check if Stored Procedure already exists, if not, create it.
CreateOTDStoredProc(otdStoredProc);
ExecOTDStoredProc(" EXEC Condor_Telcon2006_ON_TIME_DELIVERY_20081002000 ", dsOTD);
}
private void CreateOTDStoredProc(string storedProcedure)
{
// Declare Database name
string dbName = " a ";
string conn = @"server=;uid=;pwd=;database=" + dbName;
try
{
using (SqlConnection myConnection = new SqlConnection(conn))
{
using (SqlCommand sqlComm = new SqlCommand())
{
myConnection.Open();
sqlComm.Connection = myConnection;
// Select the database.
sqlComm.CommandText = "USE " + dbName;
sqlComm.ExecuteNonQuery();
// Create sp (If Stored Procedure doesn't exists).
sqlComm.CommandText = storedProcedure;
sqlComm.ExecuteNonQuery();
myConnection.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
System.Windows.Forms.Application.Exit(); // finish the program
}
}
private void ExecOTDStoredProc(string execString, DataSet ds)
{
// Declare Database name
string dbName = " a ";
string conn = @"server=;uid=;pwd=;database=" + dbName;
try
{
using (SqlConnection myConnection = new SqlConnection(conn))
{
using (SqlCommand sqlComm = new SqlCommand())
{
myConnection.Open();
sqlComm.Connection = myConnection;
// Select the database.
sqlComm.CommandText = "USE " + dbName;
sqlComm.ExecuteNonQuery();
// Execute sp.
sqlComm.CommandText =
execString +
" @FirstDate = " + "'" + dtFirstDate.Value.ToString("yyyy/MM/dd 00:00:00.000") + "'" + ", " +
" @LastDate = " + "'" + dtLastDate.Value.ToString("yyyy/MM/dd 00:00:00.000") + "'" +
"";
sqlComm.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
//DataSet ds = new DataSet();
da.Fill(ds);
//dgv1.DataSource = ds.Tables[0];
}
myConnection.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
System.Windows.Forms.Application.Exit(); // finish the program
}
}
#region
Variable Declaration
// Dataset.
private DataSet dsOTD = new DataSet();
#endregion