Issue with execution of concatenated script.

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

General SQL Server Forums

New to SQL Server Programming

Issue with execution of concatenated script.

Author

Topic

Vish_1x1
Starting Member

5 Posts

Posted - 2008-07-24 : 09:04:44

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

Vish4u

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-24 : 12:35:13

Well, first of all, if your string was formatted sensibly then this would be a whole lot easier to debug...


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'

Is easier to understand than your SELECT with concatenated strings. (Why are you doing that by the way? -- is what you posted exactly what happens in your stored proc or is each + followed by a string built somewhere else?)

Check the stored procedure in your clients database -- I don't think it is the same as the one on your development side. I guess it's an earlier version that doesn't have your JOIN in it. (going by the fact that all the other relationships are declared OLD SCHOOL in the where clause).

Ask the client to provide you a database backup if they are unable to check / won't give you direct access to check.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources