Strange Execution error in sql.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Strange Execution error in sql.

Author

Topic

Vish_1x1
Starting Member

5 Posts

Posted - 2008-07-18 : 05:24:08

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_WithoutVAT(d.DossierID),0) as Actual_Income,'
+ ' IRISARCHIVE.dbo.GetActualAmount_WithoutVAT(d.DossierID) 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_WithoutVAT(d.DossierID),0) as Actual_Income,
IRISARCHIVE.dbo.GetActualAmount_WithoutVAT(d.DossierID) 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 the 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............

Vish4u

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-18 : 12:20:42

That is weird. Can you verify that you and your client are running the same sql version with the same service pack level and hotfixs installed?

I cannot imagine that it would affect the string getting concatenated correctly, but you might try aliasing your columns and tables withthe ANSI compliant method of using AS, for example: IRISARCHIVE.dbo.Dossier AS d

But, I don't think that would solve anything. You might try cutting and pasting the code into notepad or something to make sure there are not any "funky" whitespace characters in there somewhere...? <shrug>

Vish_1x1
Starting Member

5 Posts

Posted - 2008-07-24 : 09:09:47

Well i have tried the way you suggested. this is still a failure.

Is there any1 who can save me from this crisis.

this is really ridiculous as i m stuck at such topic where everything seems to be right but still there is a execution error.

Please help.

Vish4u

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-24 : 17:55:20

The table IRISARCHIVE.dbo.InvoiceSummary is used twice in your statement with the same alias "i" - maybe there is a problem.

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources