Strange issue in execution of sql

[Vish4u]

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............

[amitpatel66]

The below line looks big..can you break this and add as a seperate statement?

+ ' 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, '

something like this:

+ ' 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, '

[Vish4u]

I have tried what u suggested...but still it fails.

Its ridiculous as i m not getting any help for such an issue.

I have tried google but have never came across such an issue.

The query works ok at our end but ignores two line when executed at client side,,,,

Please help me..............

[ck9663]

Check the your variable size and total length of the query. What's the max your variable can handle? What's the max string on your app?

-- CK