|
Hello Everyone,
I have a encountered a strange issue with the execution of my stored procedure on clients machine.
My stored procedure contains a cursor in which there is a select statement written in concatinated string format (provided below).
when i execute the procedure on my clients database some concatinated
lines in the select statement are not considered.(i.e the select statement is executed without those two line between the query and this results in error).
Please find the below Actual string query in the procedure.
SELECT @sqlA = ' SELECT DISTINCT d.Dossierid, w.WorkFlowNo FileNo, CONVERT(VARCHAR,w.OpenedDate,103) [Date], '
+ ' p.Name Client, t.name [Trade], s.name [Services], '
+ ' qs.QuotedIncome Estimated_Income, '
+ ' qs.QuotedExpense Estimated_Expense, '
+ ' ISNULL((SELECT SUM(ISNULL(i.InvoiceIncome,0)) FROM IRISARCHIVE.dbo.InvoiceSummary i WHERE i.Dossierid = d.DossierId),0)+ ISNULL(IRISARCHIVE.dbo.GetDRCRAmtLocal_JC_WithoutV AT(d.DossierID),0) as Actual_Income,'
+ ' IRISARCHIVE.dbo.GetActualAmount_WithoutVAT(d.Dossi erID) as Actual_Expense, FileCloseYN '
+ ' FROM IRISARCHIVE.dbo.QuoteSummary qs, IRISARCHIVE.dbo.WorkFlow w, IRISARCHIVE.dbo.Party p, IRISARCHIVE.dbo.ProfitCenter pc, IRISARCHIVE.dbo.Service s, IRISARCHIVE.dbo.Trade t, '
+ ' IRISARCHIVE.dbo.Dossier d '
+ ' LEFT JOIN IRISARCHIVE.dbo.InvoiceSummary i ON d.DossierID = i.DossierID '
+ ' WHERE qs.DossierID = w.DossierID '
+ ' and d.DossierID = qs.DossierID '
+ ' and d.ClientID = p.PartyID '
+ ' and d.ServiceID = s.ServiceID '
+ ' and d.TradeID = t.TradeID '
+ ' and s.ProfitCenterID = pc.ProfitCenterID '
but when this query is executed in the cursor on client side it does not have the below two line in it.
+ ' IRISARCHIVE.dbo.Dossier d '
+ ' LEFT JOIN IRISARCHIVE.dbo.InvoiceSummary i ON d.DossierID = i.DossierID '
and what runs inside the cursor at client side is this
SELECT DISTINCT d.Dossierid, w.WorkFlowNo FileNo,
CONVERT(VARCHAR,w.OpenedDate,103) [Date], p.Name Client,
t.name [Trade], s.name [Services], qs.QuotedIncome Estimated_Income,
qs.QuotedExpense Estimated_Expense, ISNULL((SELECT SUM(ISNULL(i.InvoiceIncome,0))
FROM IRISARCHIVE.dbo.InvoiceSummary i
WHERE i.Dossierid = d.DossierId),0)+ ISNULL(IRISARCHIVE.dbo.GetDRCRAmtLocal_JC_WithoutV AT(d.DossierID),0) as Actual_Income,
IRISARCHIVE.dbo.GetActualAmount_WithoutVAT(d.Dossi erID) as Actual_Expense, FileCloseYN
FROM IRISARCHIVE.dbo.QuoteSummary qs, IRISARCHIVE.dbo.WorkFlow w, IRISARCHIVE.dbo.Party p,
IRISARCHIVE.dbo.ProfitCenter pc, IRISARCHIVE.dbo.Service s, IRISARCHIVE.dbo.Trade t, --lines missing over here..
WHERE qs.DossierID = w.DossierID and d.DossierID = qs.DossierID and d.ClientID = p.PartyID
and d.ServiceID = s.ServiceID and d.TradeID = t.TradeID and s.ProfitCenterID = pc.ProfitCenterID
and d.FileCloseYN=-1 and w.WorkFlowNo like '' and d.ClientId =0 and d.TradeId =0 and d.ServiceId =0
and CONVERT(VARCHAR,CONVERT(DATETIME,w.OpenedDate,03), 112) between 20080401 and 20080408
Well this is not the issue at our end as when we run the same cursor all whole select statement get executed and there is no error and the report is displayed........but this issue is on the client side....is there some kind of sql or database settings that i am missing which is causing this...because we have the same database at our development side and we find no issue at out end......
Please help this issue is killing me............
| 
