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