Reporting Services Has a problem with my Dynamic SQL

I have a stored proc that I can execute from Visual Studio, Query Analyzer,
and from within the "Design the Query" window in the Report Wizard. It
returns data in all cases. However, when I continue on with the wizard and
get to the "Design the Table" page, there are no fields in the "Available
Fields" list box. If I continue on and Finish, there an ugly error message
about "MS Development Environment is unable to load this document.
Deserialization failed: The element 'http://schemas..../reportdefinition:
TableCells' has incomplete content... An error occurred at , (33, 16)."
What is unusual is that it seems to bomb anytime I use dynamic SQL, but if I
use a local variable (casting a date as a string) it will work. If I use the
same local variable, and parse the incoming date parameter to the identical
date string, it will not work.
Here's the code... any help is appreciated.
ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
(
@.Shop varchar(15),
@.DateBgn datetime,
@.DateEnd datetime,
@.IncTypes varchar(4096)
)
AS
SET NOCOUNT ON
DECLARE
@.lv_shop varchar(15),
@.lv_types varchar(4096),
@.lv_SQL varchar(8000)
BEGIN
If IsNumeric(@.Shop) = 1
SET @.lv_shop = @.Shop
Else
SET @.lv_shop = '%'
SET @.lv_types = @.IncTypes
SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
SET @.lv_types = ('''' + @.lv_types + '''')
SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
'ggptrk07.incdnt_typ_d, ' +
'ggptrk08.incdnt_strt_t, ' +
'ggptrk08.incdnt_end_t, ' +
'ggptrk08.incdnt_n, ' +
'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
getdate())), 2) Duration, ' +
'ggptrk08.mold_n, ' +
'ggptrk08.mount_sect_i, ' +
'ggptrk08.mount_pstn_i, ' +
'ggptrk06.incdnt_rsn_d, ' +
'ggptrk08.incdnt_cmnt_e, ' +
'ggptrk27.incdnt_actn_t, ' +
'ggptrk27.incdnt_solution_c, ' +
'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON GGPTRK08.INCDNT_RSN_C
= GGPTRK06.INCDNT_RSN_C ' +
'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON GGPTRK08.INCDNT_TYP_C
= GGPTRK07.INCDNT_TYP_C ' +
'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
' +
'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
' +
'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
'ORDER BY ' +
'ggptrk08.prod_ln_i, ' +
'ggptrk07.incdnt_typ_d, ' +
'ggptrk08.incdnt_strt_t'
Exec (@.lv_SQL)
ENDIt seems to me that Report Wizard cannot get field list because it is a
dynamic query.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JKramer" <JKramer@.discussions.microsoft.com> wrote in message
news:E9FF54EA-478F-4CE4-BC17-219BAA942D4D@.microsoft.com...
>I have a stored proc that I can execute from Visual Studio, Query Analyzer,
> and from within the "Design the Query" window in the Report Wizard. It
> returns data in all cases. However, when I continue on with the wizard and
> get to the "Design the Table" page, there are no fields in the "Available
> Fields" list box. If I continue on and Finish, there an ugly error message
> about "MS Development Environment is unable to load this document.
> Deserialization failed: The element 'http://schemas..../reportdefinition:
> TableCells' has incomplete content... An error occurred at , (33, 16)."
> What is unusual is that it seems to bomb anytime I use dynamic SQL, but if
> I
> use a local variable (casting a date as a string) it will work. If I use
> the
> same local variable, and parse the incoming date parameter to the
> identical
> date string, it will not work.
> Here's the code... any help is appreciated.
> ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
> (
> @.Shop varchar(15),
> @.DateBgn datetime,
> @.DateEnd datetime,
> @.IncTypes varchar(4096)
> )
> AS
> SET NOCOUNT ON
> DECLARE
> @.lv_shop varchar(15),
> @.lv_types varchar(4096),
> @.lv_SQL varchar(8000)
> BEGIN
> If IsNumeric(@.Shop) = 1
> SET @.lv_shop = @.Shop
> Else
> SET @.lv_shop = '%'
> SET @.lv_types = @.IncTypes
> SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
> SET @.lv_types = ('''' + @.lv_types + '''')
> SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t, ' +
> 'ggptrk08.incdnt_end_t, ' +
> 'ggptrk08.incdnt_n, ' +
> 'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
> getdate())), 2) Duration, ' +
> 'ggptrk08.mold_n, ' +
> 'ggptrk08.mount_sect_i, ' +
> 'ggptrk08.mount_pstn_i, ' +
> 'ggptrk06.incdnt_rsn_d, ' +
> 'ggptrk08.incdnt_cmnt_e, ' +
> 'ggptrk27.incdnt_actn_t, ' +
> 'ggptrk27.incdnt_solution_c, ' +
> 'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
> 'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
> 'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
> 'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
> 'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
> GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
> 'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON GGPTRK08.INCDNT_RSN_C
> = GGPTRK06.INCDNT_RSN_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON GGPTRK08.INCDNT_TYP_C
> = GGPTRK07.INCDNT_TYP_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
> 'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
> GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
> 'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
> 'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
> 'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
> ' +
> 'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
> ' +
> 'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
> 'ORDER BY ' +
> 'ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t'
> Exec (@.lv_SQL)
> END|||Hi JKramer,
yes, when you link a stored procedure to a dataset in report services
it queries the report and builds the list of columns returned from the
query.....this is very, very handy as you can name your fields
consistently and then copy reports and just change the stored procedure
the report uses and it will 'auto-magically' put the data into the
right fields in the datasets linked to tables and charts.....very very
handy...if you use strings like this I gather than you can not do
this...
I don't see why you would want to generate strings like this.....we
tried this and found performance around 50% down on just using straight
sql with variables in it....also with strings you are limited to 8000
characters which you may over-run without knowing it....we have
queries over 8000 characters....
We are yet to find a situation where we cannot make do with using sql
and not strings like this......
Best Regards
Peter
www.peternolan.com|||Peter,
The reason I use dynamic SQL is because one of the where conditions is a
LIKE condition, which I'm taking in as one string parameter. So the input
might be "21,22,23" or it might be "31". It is also dynamic, so I'm never
exactly sure what might be coming in (though I'm sure I won't hit the 8000
character limit). I don't know how to build that using straight SQL. If it
can be done, I agree, that's the way to go.
Can you tell me how I'd accomplish what I'm trying to do with straight SQL?
"Peter Nolan" wrote:
> Hi JKramer,
> yes, when you link a stored procedure to a dataset in report services
> it queries the report and builds the list of columns returned from the
> query.....this is very, very handy as you can name your fields
> consistently and then copy reports and just change the stored procedure
> the report uses and it will 'auto-magically' put the data into the
> right fields in the datasets linked to tables and charts.....very very
> handy...if you use strings like this I gather than you can not do
> this...
> I don't see why you would want to generate strings like this.....we
> tried this and found performance around 50% down on just using straight
> sql with variables in it....also with strings you are limited to 8000
> characters which you may over-run without knowing it....we have
> queries over 8000 characters....
> We are yet to find a situation where we cannot make do with using sql
> and not strings like this......
> Best Regards
> Peter
> www.peternolan.com
>|||In case anyone is interested, I think the problem is actually with the wizard
and not in the handling of the dynamic SQL. After re-reading the error, I got
to thinking the problem was in the *.rdl. So as I attempted to "fix" it (and
I'm not familiar enough with it to do so), I didn't help the issue at all. So
I copied the text of a different *.rdl inot the one I couldn't get to work
and saved it. When I opened it up, it opened just fine (of course the data
source was wrong, since it was copied from another). So I went to the data
tab, pointed it to the correct Stored Proc (still using dynamic SQL) and it
worked fine from there. So the problem was not the data source, but the
wizard.
Hopefully that helps anyone else who runs into this one.
Where does one post a bug for reporting services?
"JKramer" wrote:
> I have a stored proc that I can execute from Visual Studio, Query Analyzer,
> and from within the "Design the Query" window in the Report Wizard. It
> returns data in all cases. However, when I continue on with the wizard and
> get to the "Design the Table" page, there are no fields in the "Available
> Fields" list box. If I continue on and Finish, there an ugly error message
> about "MS Development Environment is unable to load this document.
> Deserialization failed: The element 'http://schemas..../reportdefinition:
> TableCells' has incomplete content... An error occurred at , (33, 16)."
> What is unusual is that it seems to bomb anytime I use dynamic SQL, but if I
> use a local variable (casting a date as a string) it will work. If I use the
> same local variable, and parse the incoming date parameter to the identical
> date string, it will not work.
> Here's the code... any help is appreciated.
> ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
> (
> @.Shop varchar(15),
> @.DateBgn datetime,
> @.DateEnd datetime,
> @.IncTypes varchar(4096)
> )
> AS
> SET NOCOUNT ON
> DECLARE
> @.lv_shop varchar(15),
> @.lv_types varchar(4096),
> @.lv_SQL varchar(8000)
> BEGIN
> If IsNumeric(@.Shop) = 1
> SET @.lv_shop = @.Shop
> Else
> SET @.lv_shop = '%'
> SET @.lv_types = @.IncTypes
> SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
> SET @.lv_types = ('''' + @.lv_types + '''')
> SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t, ' +
> 'ggptrk08.incdnt_end_t, ' +
> 'ggptrk08.incdnt_n, ' +
> 'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
> getdate())), 2) Duration, ' +
> 'ggptrk08.mold_n, ' +
> 'ggptrk08.mount_sect_i, ' +
> 'ggptrk08.mount_pstn_i, ' +
> 'ggptrk06.incdnt_rsn_d, ' +
> 'ggptrk08.incdnt_cmnt_e, ' +
> 'ggptrk27.incdnt_actn_t, ' +
> 'ggptrk27.incdnt_solution_c, ' +
> 'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
> 'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
> 'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
> 'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
> 'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
> GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
> 'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON GGPTRK08.INCDNT_RSN_C
> = GGPTRK06.INCDNT_RSN_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON GGPTRK08.INCDNT_TYP_C
> = GGPTRK07.INCDNT_TYP_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
> 'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
> GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
> 'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
> 'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
> 'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
> ' +
> 'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
> ' +
> 'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
> 'ORDER BY ' +
> 'ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t'
> Exec (@.lv_SQL)
> END|||In general with Stored Procedures I very seldom use the Report Wizard. I
create a new report, go to the data tab and set it up, then to layout and
drag and drop the table. Right mouse click on a column to add additional
columns. Or, start off with a query that has the appropriate fields, use the
wizard and then change to a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JKramer" <JKramer@.discussions.microsoft.com> wrote in message
news:75EEA5FA-0373-420B-B90E-0CF660220EF2@.microsoft.com...
> In case anyone is interested, I think the problem is actually with the
> wizard
> and not in the handling of the dynamic SQL. After re-reading the error, I
> got
> to thinking the problem was in the *.rdl. So as I attempted to "fix" it
> (and
> I'm not familiar enough with it to do so), I didn't help the issue at all.
> So
> I copied the text of a different *.rdl inot the one I couldn't get to work
> and saved it. When I opened it up, it opened just fine (of course the data
> source was wrong, since it was copied from another). So I went to the data
> tab, pointed it to the correct Stored Proc (still using dynamic SQL) and
> it
> worked fine from there. So the problem was not the data source, but the
> wizard.
> Hopefully that helps anyone else who runs into this one.
> Where does one post a bug for reporting services?
> "JKramer" wrote:
>> I have a stored proc that I can execute from Visual Studio, Query
>> Analyzer,
>> and from within the "Design the Query" window in the Report Wizard. It
>> returns data in all cases. However, when I continue on with the wizard
>> and
>> get to the "Design the Table" page, there are no fields in the "Available
>> Fields" list box. If I continue on and Finish, there an ugly error
>> message
>> about "MS Development Environment is unable to load this document.
>> Deserialization failed: The element
>> 'http://schemas..../reportdefinition:
>> TableCells' has incomplete content... An error occurred at , (33, 16)."
>> What is unusual is that it seems to bomb anytime I use dynamic SQL, but
>> if I
>> use a local variable (casting a date as a string) it will work. If I use
>> the
>> same local variable, and parse the incoming date parameter to the
>> identical
>> date string, it will not work.
>> Here's the code... any help is appreciated.
>> ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
>> (
>> @.Shop varchar(15),
>> @.DateBgn datetime,
>> @.DateEnd datetime,
>> @.IncTypes varchar(4096)
>> )
>> AS
>> SET NOCOUNT ON
>> DECLARE
>> @.lv_shop varchar(15),
>> @.lv_types varchar(4096),
>> @.lv_SQL varchar(8000)
>> BEGIN
>> If IsNumeric(@.Shop) = 1
>> SET @.lv_shop = @.Shop
>> Else
>> SET @.lv_shop = '%'
>> SET @.lv_types = @.IncTypes
>> SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
>> SET @.lv_types = ('''' + @.lv_types + '''')
>> SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
>> 'ggptrk07.incdnt_typ_d, ' +
>> 'ggptrk08.incdnt_strt_t, ' +
>> 'ggptrk08.incdnt_end_t, ' +
>> 'ggptrk08.incdnt_n, ' +
>> 'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
>> getdate())), 2) Duration, ' +
>> 'ggptrk08.mold_n, ' +
>> 'ggptrk08.mount_sect_i, ' +
>> 'ggptrk08.mount_pstn_i, ' +
>> 'ggptrk06.incdnt_rsn_d, ' +
>> 'ggptrk08.incdnt_cmnt_e, ' +
>> 'ggptrk27.incdnt_actn_t, ' +
>> 'ggptrk27.incdnt_solution_c, ' +
>> 'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
>> 'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
>> 'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
>> 'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
>> 'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
>> GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
>> 'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON
>> GGPTRK08.INCDNT_RSN_C
>> = GGPTRK06.INCDNT_RSN_C ' +
>> 'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON
>> GGPTRK08.INCDNT_TYP_C
>> = GGPTRK07.INCDNT_TYP_C ' +
>> 'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
>> 'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
>> GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
>> 'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
>> 'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
>> 'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
>> ' +
>> 'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
>> ' +
>> 'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
>> 'ORDER BY ' +
>> 'ggptrk08.prod_ln_i, ' +
>> 'ggptrk07.incdnt_typ_d, ' +
>> 'ggptrk08.incdnt_strt_t'
>> Exec (@.lv_SQL)
>> END|||Hi JKramer,
does it recognise the fieds being returned or do you have to type them
into the dataset? When you add a dataset to a report it automatically
adds the parameters and the fields returned from the SP...this is what
I was saying was useful...I gave up on wizards almost
immediately...they are just WAY too limited....we set up templates and
put the templates into a vs directory and they appear as templates when
we create a new report..although most of the time we just copy reports
anyway...
Peter|||It appears that a lot of folks have raised this issue also, but there was
never a concreate solution from the MFST experts?
Here is an example of a stored procedure that creates a dynamic query. I am
using a simple example to demonstrate the problem that can be recreated
easily, so please do not reply and said "why dont you use a straight select
statement instead?"
Create PROCEDURE dbo.QuarterlyReportingTest
@.CustomerPk int
AS
declare @.SQL varchar(2000)
select @.SQL = 'select * from [DataWarehouse].dbo.CustomerDim where
CustomerPk = ' + convert(varchar(10), @.CustomerPk)
exec (@.SQL)
This query will return the particular rows of information depending on the
report parameter @.CustomerPk
In RS, the dataset was setup as command type of 'Stored Procedure'. The
query string field has 'QuarterlyReportingTest'. When I clicked the '!', it
will prompt me for the CustomerPk. I would enter the CustomerPk, and it will
return the appropriate data.
The problem is, the DataSet fields remain blank. I can click the "Refresh
field" button, but the DataSet fields are still blank. Of course, without
the DataSet field, you cannot go and create the report, and report rendering
would also have error because there are no fields associated with the dataset.
Can anyone help in this so that the Dataset fields are populated / available
for these dynamic queries stored procedures?
Thanks.
LBJOHN99@.yahoo.com|||Hi JKramer,
can you use 'in' and just pass this set of values?
Also, I just got my copy of hitchhikers guide to SQL Server 2000 and
there is a specific example of this on pages 535 and 536. Since I'm a
believer in guys getting paid for their books I won't write the example
here...but they provide an example of picking multiple values from a
parameter in 2000 and then passing it through to a query making it safe
from SQL Injection along the way....the example is selecting a set of
employee ids from a list and printing the employee details...
Not sure if this would be applicable to your specific problem...but if
you know someone near you with a copy of the book you can look and see.
Best Regards
Peter Nolan
www.peternolan.com