SSRS 2005 Report Writer Limitation- 128 Max Length for Query Strings Constructed in Data Tab???!!!
I'm trying to construct a SELECT query within the Report Writer 2005 Data Tab as follows- ="SELECT blah..." & SomeFunctionToConstructWhereClause() " ORDER BY 1" When the length exceeds 128 characters, I get the following error (works otherwise)- Microsoft Report Designer Incorrect syntax near '='. <QUERY TEXT> The identifier that starts with is too long. Maximum length is 128. Say it isn't so or that there is a reasonable workaround for this. 128 characters isn't that much when you're talking about complex query strings. Thanks in advance.
October 20th, 2010 11:14pm

No takers? Really need an answer for this one. Thanks!
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 6:28pm

Hi Bill, Can you please port your query if possible. Did you try to execute your query in query analyzer to see if it is running fine there? I think your query contains error. Thanks
October 21st, 2010 9:08pm

I doubt there are any syntax errors (the query runs fine within SSMS 2005), but I never say never. You'll see that I have a simple IF ELSE that essentially says IF the multi-value parameter @pPaymentRefNumber = 'ALL' run a version of the query ELSE run a slightly different version of the query where the query string is put together by concatenating the bulk of the first query + an additional IN() function in the where clause + the ORDER BY clause. If I keep the length of the string <= 128 characters it works fine. The query I need to run is obviously longer (more complex). if @pPaymentRefNumber = 'ALL' select rtt.AccountTransactionId, x.TotalPaidAmount, at.BillingAmount, Diff = abs(x.TotalPaidAmount - at.BillingAmount), rtt.PaymentRefNumber, p.purchaseCardLogId, p.UniqueID from RemitFile f (nolock) inner join RemitProcess rp (nolock) on rp.RemitProcessId = f.RemitProcessId inner join ( select AccountTransactionId, PaymentRefNumber, RemitFileId, TotalPaidAmount = sum(PaidAmount) from RemitTransaction (nolock) group by AccountTransactionId, PaymentRefNumber, RemitFileId ) rtt on rtt.RemitFileId = f.RemitFileId inner join AccountTransaction at (nolock) on at.AccountTransactionId = rtt.AccountTransactionId inner join PurchaseLogTransactions plt (nolock) on plt.AccountTransactionId = at.AccountTransactionId inner join PurchaseCardLog p (nolock) on p.PurchaseCardLogId = plt.PurchaseCardLogId inner join ( -- the sum of paid transactions for the file date and earlier select rt.AccountTransactionId, TotalPaidAmount = sum(rt.PaidAmount) from RemitFile f (nolock) inner join RemitProcess rp (nolock) on rp.RemitProcessId = f.RemitProcessId inner join RemitTransaction rt (nolock) on rt.RemitFileId = f.RemitFileId where rp.ProcessName = 'Expedia Recon' and f.StatusId = 0 and f.ProcessedDate between @pStartDate and @pEndDate and rt.PaidAmount <> 0.0 group by rt.AccountTransactionId ) x on x.AccountTransactionId = rtt.AccountTransactionId where rp.ProcessName = 'Expedia Recon' and f.StatusId = 0 and f.ProcessedDate between @pStartDate and @pEndDate and at.BillingAmount <> x.TotalPaidAmount order by 1 else ="select rtt.AccountTransactionId, x.TotalPaidAmount, at.BillingAmount, Diff = abs(x.TotalPaidAmount - at.BillingAmount), rtt.PaymentRefNumber, p.purchaseCardLogId, p.UniqueID from RemitFile f (nolock) inner join RemitProcess rp (nolock) on rp.RemitProcessId = f.RemitProcessId inner join ( select AccountTransactionId, PaymentRefNumber, RemitFileId, TotalPaidAmount = sum(PaidAmount) from RemitTransaction (nolock) group by AccountTransactionId, PaymentRefNumber, RemitFileId ) rtt on rtt.RemitFileId = f.RemitFileId inner join AccountTransaction at (nolock) on at.AccountTransactionId = rtt.AccountTransactionId inner join PurchaseLogTransactions plt (nolock) on plt.AccountTransactionId = at.AccountTransactionId inner join PurchaseCardLog p (nolock) on p.PurchaseCardLogId = plt.PurchaseCardLogId inner join ( -- the sum of paid transactions for the file date and earlier select rt.AccountTransactionId, TotalPaidAmount = sum(rt.PaidAmount) from RemitFile f (nolock) inner join RemitProcess rp (nolock) on rp.RemitProcessId = f.RemitProcessId inner join RemitTransaction rt (nolock) on rt.RemitFileId = f.RemitFileId where rp.ProcessName = 'Expedia Recon' and f.StatusId = 0 and f.ProcessedDate between @pStartDate and @pEndDate and rt.PaidAmount <> 0.0 group by rt.AccountTransactionId ) x on x.AccountTransactionId = rtt.AccountTransactionId where rp.ProcessName = 'Expedia Recon' and f.StatusId = 0 and f.ProcessedDate between @pStartDate and @pEndDate and at.BillingAmount <> x.TotalPaidAmount and rtt.PaymentRefNumber" & " IN('" & Join(Parameters!pPaymentRefNumber.Value,"','") &"') " & "order by 1"
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 11:41pm

Why dont't you use stored procedure?
October 22nd, 2010 12:18pm

Forget it. The 128 character maximum error message was completely misleading (had nothing to do with the problem). Apparently you can't have carriage returns within a query string constructed with an expression (doesn't mind if a literal though which is odd). I removed the CRs and everything works now. Thanks.
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2010 4:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics