Reporting Services 2005 & Parameters

Hi, I experiencing some problems when accesing the Parameters collection on
the dataset to build the the sql statement.
Below is my code (very simple):
DataSet:
="SELECT 'userid' = '" & code.Parameter("userID", Parameters) & "' FROM
users"
Code:
Public Function Parameter(ByVal field As String, ByRef pars As Object) As
String
return pars(field).Value
End Function
This code works just fine on the Preview but if I test the report on the
browser, it doesnt work and returns the following error:
a.. An error has occurred during report processing.
a.. Cannot set the command text for data set 'ExpoMedios'.
a.. Error during processing of the CommandText expression of dataset
'ExpoMedios'.
Doing some debugging the error message inside the function is:
Attempt to access the method failed.
Can anyone pleae explain why this is happening. Your help will be
appreciated.
Regards,
FabianAny help please? It happens when I access to the Parameters Collection
inside a custom code.
Any help will be appreciated.
Thanks in avance,
Fabian von Romberg
"Fabian von Romberg" <fromberg100@.hotmail.com> wrote in message
news:uBbFLro3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> Hi, I experiencing some problems when accesing the Parameters collection
on
> the dataset to build the the sql statement.
> Below is my code (very simple):
> DataSet:
> ="SELECT 'userid' = '" & code.Parameter("userID", Parameters) & "' FROM
> users"
> Code:
> Public Function Parameter(ByVal field As String, ByRef pars As Object) As
> String
> return pars(field).Value
> End Function
>
> This code works just fine on the Preview but if I test the report on the
> browser, it doesnt work and returns the following error:
> a.. An error has occurred during report processing.
> a.. Cannot set the command text for data set 'ExpoMedios'.
> a.. Error during processing of the CommandText expression of dataset
> 'ExpoMedios'.
> Doing some debugging the error message inside the function is:
> Attempt to access the method failed.
>
> Can anyone pleae explain why this is happening. Your help will be
> appreciated.
> Regards,
> Fabian
>

Reporting services 2000 / 2005 parameters

Hi all,

Is there any way to pass a dataset parameter in RS 2000? Basicly, I have some data in my web page and want to print it. But I don't want to create another page to pass parameters to RS. I want to print the page as it is with the data already in the page, so if there is a way I can send the data through a dataset to the report directly, would be great. Is there?

Thank you very much,

Marco

You can give this a try.

http://blogs.msdn.com/bryanke/archive/2004/09/13/229129.aspx

cheers,

Andrew

|||The link from the blog is not valid.|||

More ideas, please

Thanks,

Marco

|||

I found it after googling custom dataset reporting services.

http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=b8468707-56ef-4864-ac51-d83fc3273fe5

cheers,

Andrew

|||

Good article, Andrew.

Thank you very much

Regards,

Marco

Reporting Services - Variables/Parameters - Is this a bug?

When creating a Reporting Services report and declaring local variables as part of your query in a dataset there is sometimes a problem. When you hit run in the Data section and the “Define Query Parameters” box pops up, all the variables are not there. Sometimes when you go to properties (…) of that dataset the parameters are gone. Is this a bug? This is happening both in RS2000 and 2005.

Thanks

?

|||Anyone?|||

Not an expert in this area of the product, but i tried it briefly on my computer - local variables are recognized when you use the generic query designer (the default). They do not appear in the parameters (as they shouldn't). Parameters that are not local variables are automatically detected and shown in the parameters.

I couldn't emulate the behavior you describe. No matter how many times I opened/closed the report the parameters were correctly recognized and the local variables were preserved.

If you're using the graphical query designer, you may be running into some problems - that component rewrites queries and sometimes makes mistakes with more complicated query structures. Unfortunately, it is a standard component that we do not control. Recommendation is to try the generic query designer and see if the problem persists. There is a button on the data tab that lets you switch from using the graphical to using the generic query designer.

Hope that helps,

-Lukasz

|||

this is possibly related, and everytime i encounter it i get closer to having a nervous break down.. its very very annoying to say the least..

here's what i'm getting:

i have a report that gets data from a WebService.. webservice takes 6 parameters, all are primative datatypes.

i have a set of report parameters, and a set of dataset parameters that come from the report params.

i hit the execute button, fill in my values, it chugs along and give's me my expected results in the results grid.. perfect..

i edit my dataset query and put in an <ElementPath> so i can specify a particular table, hit execute and again i get my expected results..

if i do anything ie, edit the query, change to the layout window, then go back to the data window and hit refresh, the query blows up.. i edit my dataset and on the parameters tab, there's nothing.. all gone! wow.. i put all the parameters back in again, viewed the report source, copy and paste the block of code contained in the <DataSets> node to the notepad so i dont have to continually waste time re-entering the parameters..

this seems to happen whenever i click the refresh button.. i've tried to put it in generic query designer, and do it, but the UI seems to revert back to the graphical designer..

i've been dealing with it because most of my reports only took a parameter or two..

any ideas?

EDIT:

actually, i think it might be a problem if there is an error when you type in the parameters.. ie if i have a string param called strGuid, and it craps out the webservice when i cast the string to a guid (missed a char).. it seems to wipe out the dataset parameters..

|||

I've seen this many times, codemare & ruckazz

It will happen for one of the following reasons:

1. Your Query does not get parsed correctly i.e type error and you hit the properties button to go to parameters

2. You use a parameter that is not defined or you spelled incorrectly and go to parameters in properties

Use the Undo button (Ctrl-Z or Edit>Undo) and your parameters WILL be restored, do not save, find your flaw, fix it, save it, run it.

Reporting Services - Variables/Parameters - Is this a bug?

When creating a Reporting Services report and declaring local variables as part of your query in a dataset there is sometimes a problem. When you hit run in the Data section and the “Define Query Parameters” box pops up, all the variables are not there. Sometimes when you go to properties (…) of that dataset the parameters are gone. Is this a bug? This is happening both in RS2000 and 2005.

Thanks

?

|||Anyone?|||

Not an expert in this area of the product, but i tried it briefly on my computer - local variables are recognized when you use the generic query designer (the default). They do not appear in the parameters (as they shouldn't). Parameters that are not local variables are automatically detected and shown in the parameters.

I couldn't emulate the behavior you describe. No matter how many times I opened/closed the report the parameters were correctly recognized and the local variables were preserved.

If you're using the graphical query designer, you may be running into some problems - that component rewrites queries and sometimes makes mistakes with more complicated query structures. Unfortunately, it is a standard component that we do not control. Recommendation is to try the generic query designer and see if the problem persists. There is a button on the data tab that lets you switch from using the graphical to using the generic query designer.

Hope that helps,

-Lukasz

|||

this is possibly related, and everytime i encounter it i get closer to having a nervous break down.. its very very annoying to say the least..

here's what i'm getting:

i have a report that gets data from a WebService.. webservice takes 6 parameters, all are primative datatypes.

i have a set of report parameters, and a set of dataset parameters that come from the report params.

i hit the execute button, fill in my values, it chugs along and give's me my expected results in the results grid.. perfect..

i edit my dataset query and put in an <ElementPath> so i can specify a particular table, hit execute and again i get my expected results..

if i do anything ie, edit the query, change to the layout window, then go back to the data window and hit refresh, the query blows up.. i edit my dataset and on the parameters tab, there's nothing.. all gone! wow.. i put all the parameters back in again, viewed the report source, copy and paste the block of code contained in the <DataSets> node to the notepad so i dont have to continually waste time re-entering the parameters..

this seems to happen whenever i click the refresh button.. i've tried to put it in generic query designer, and do it, but the UI seems to revert back to the graphical designer..

i've been dealing with it because most of my reports only took a parameter or two..

any ideas?

EDIT:

actually, i think it might be a problem if there is an error when you type in the parameters.. ie if i have a string param called strGuid, and it craps out the webservice when i cast the string to a guid (missed a char).. it seems to wipe out the dataset parameters..

|||

I've seen this many times, codemare & ruckazz

It will happen for one of the following reasons:

1. Your Query does not get parsed correctly i.e type error and you hit the properties button to go to parameters

2. You use a parameter that is not defined or you spelled incorrectly and go to parameters in properties

Use the Undo button (Ctrl-Z or Edit>Undo) and your parameters WILL be restored, do not save, find your flaw, fix it, save it, run it.

Reporting Services - Variables/Parameters - Is this a bug?

When creating a Reporting Services report and declaring local variables as part of your query in a dataset there is sometimes a problem. When you hit run in the Data section and the “Define Query Parameters” box pops up, all the variables are not there. Sometimes when you go to properties (…) of that dataset the parameters are gone. Is this a bug? This is happening both in RS2000 and 2005.

Thanks

?

|||Anyone?|||

Not an expert in this area of the product, but i tried it briefly on my computer - local variables are recognized when you use the generic query designer (the default). They do not appear in the parameters (as they shouldn't). Parameters that are not local variables are automatically detected and shown in the parameters.

I couldn't emulate the behavior you describe. No matter how many times I opened/closed the report the parameters were correctly recognized and the local variables were preserved.

If you're using the graphical query designer, you may be running into some problems - that component rewrites queries and sometimes makes mistakes with more complicated query structures. Unfortunately, it is a standard component that we do not control. Recommendation is to try the generic query designer and see if the problem persists. There is a button on the data tab that lets you switch from using the graphical to using the generic query designer.

Hope that helps,

-Lukasz

|||

this is possibly related, and everytime i encounter it i get closer to having a nervous break down.. its very very annoying to say the least..

here's what i'm getting:

i have a report that gets data from a WebService.. webservice takes 6 parameters, all are primative datatypes.

i have a set of report parameters, and a set of dataset parameters that come from the report params.

i hit the execute button, fill in my values, it chugs along and give's me my expected results in the results grid.. perfect..

i edit my dataset query and put in an <ElementPath> so i can specify a particular table, hit execute and again i get my expected results..

if i do anything ie, edit the query, change to the layout window, then go back to the data window and hit refresh, the query blows up.. i edit my dataset and on the parameters tab, there's nothing.. all gone! wow.. i put all the parameters back in again, viewed the report source, copy and paste the block of code contained in the <DataSets> node to the notepad so i dont have to continually waste time re-entering the parameters..

this seems to happen whenever i click the refresh button.. i've tried to put it in generic query designer, and do it, but the UI seems to revert back to the graphical designer..

i've been dealing with it because most of my reports only took a parameter or two..

any ideas?

EDIT:

actually, i think it might be a problem if there is an error when you type in the parameters.. ie if i have a string param called strGuid, and it craps out the webservice when i cast the string to a guid (missed a char).. it seems to wipe out the dataset parameters..

|||

I've seen this many times, codemare & ruckazz

It will happen for one of the following reasons:

1. Your Query does not get parsed correctly i.e type error and you hit the properties button to go to parameters

2. You use a parameter that is not defined or you spelled incorrectly and go to parameters in properties

Use the Undo button (Ctrl-Z or Edit>Undo) and your parameters WILL be restored, do not save, find your flaw, fix it, save it, run it.

reporting service performance issue

Hi,
I construct a sql statement as a dataset. But when I click Run or Refresh,
the VS.NET runs so slow and no response sometimes. Do you know why, and how
to
deal with this issue?
below is the sql statement I used for:
DECLARE
@.sql NVARCHAR(4000)
select @.resp=ltrim(rtrim(@.resp)),
@.country =ltrim(rtrim(@.country )),
@.entity=ltrim(rtrim(@.entity)),
@.loc=ltrim(rtrim(@.loc)),
--@.office=ltrim(rtrim(@.office)),
@.local_oc=ltrim(rtrim(@.local_oc)),
@.for_agent=ltrim(rtrim(@.for_agent))
select @.sql =
'SELECT Country_Application.country_code, Country_Application.type,
Country_Application.appl_no,
Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
Action_Due.item, Action_Due.action_due_resp_atty,
Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
Action_Due.due_date,
Action_Due.remark, Action_Due.resp_admin
from Country_Application INNER JOIN Action_Due on
Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
= Action_Due.sub_case
inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
''ANNUITY''' + --hard code
' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
''''
if @.resp not in ('ALL', '')
begin
set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
end
if @.country not in ('ALL', '')
begin
if @.country = '<> US'
begin
set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
end
else
begin
set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
+')'
end
end
if @.entity not in ('ALL', '')
begin
set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
Invention_Data.loc = '''+@.loc+''''
end
if @.local_oc <> ''
begin
set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
end
if @.for_agent <> ''
begin
set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
end
exec sp_executesql @.sqlThe best way to deal with this is to pull the query out and run it in SQL
SErver Management Studio - get the query plan and find out what is
happening...
You are using some <>, and functions in where clauses which prevents the
optimizer from using index statistics to choose indexes..so you might not the
best plan...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"David Zhu" wrote:
> Hi,
> I construct a sql statement as a dataset. But when I click Run or Refresh,
> the VS.NET runs so slow and no response sometimes. Do you know why, and how
> to
> deal with this issue?
> below is the sql statement I used for:
> DECLARE
> @.sql NVARCHAR(4000)
> select @.resp=ltrim(rtrim(@.resp)),
> @.country =ltrim(rtrim(@.country )),
> @.entity=ltrim(rtrim(@.entity)),
> @.loc=ltrim(rtrim(@.loc)),
> --@.office=ltrim(rtrim(@.office)),
> @.local_oc=ltrim(rtrim(@.local_oc)),
> @.for_agent=ltrim(rtrim(@.for_agent))
> select @.sql => 'SELECT Country_Application.country_code, Country_Application.type,
> Country_Application.appl_no,
> Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> Action_Due.item, Action_Due.action_due_resp_atty,
> Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> Action_Due.due_date,
> Action_Due.remark, Action_Due.resp_admin
> from Country_Application INNER JOIN Action_Due on
> Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
> = Action_Due.sub_case
> inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>
> where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> ''ANNUITY''' + --hard code
> ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
> ''''
> if @.resp not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> end
> if @.country not in ('ALL', '')
> begin
> if @.country = '<> US'
> begin
> set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> end
> else
> begin
> set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> +')'
> end
> end
> if @.entity not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> Invention_Data.loc = '''+@.loc+''''
> end
> if @.local_oc <> ''
> begin
> set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> end
> if @.for_agent <> ''
> begin
> set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> end
> exec sp_executesql @.sql
>
>
>
>|||Hi Snyder,
Thanks a lot.
I also did some investigation. Unfortunately, the sql statement run fast
enough in
Sql query analyser. And I found the report run fast on the RS2000 without
SP2 machine, but slow on RS2000 with SP2.
Could you please give me some suggestion?
"Wayne Snyder" wrote:
> The best way to deal with this is to pull the query out and run it in SQL
> SErver Management Studio - get the query plan and find out what is
> happening...
> You are using some <>, and functions in where clauses which prevents the
> optimizer from using index statistics to choose indexes..so you might not the
> best plan...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "David Zhu" wrote:
> > Hi,
> >
> > I construct a sql statement as a dataset. But when I click Run or Refresh,
> > the VS.NET runs so slow and no response sometimes. Do you know why, and how
> > to
> > deal with this issue?
> >
> > below is the sql statement I used for:
> >
> > DECLARE
> >
> > @.sql NVARCHAR(4000)
> >
> > select @.resp=ltrim(rtrim(@.resp)),
> > @.country =ltrim(rtrim(@.country )),
> > @.entity=ltrim(rtrim(@.entity)),
> > @.loc=ltrim(rtrim(@.loc)),
> > --@.office=ltrim(rtrim(@.office)),
> > @.local_oc=ltrim(rtrim(@.local_oc)),
> > @.for_agent=ltrim(rtrim(@.for_agent))
> >
> > select @.sql => >
> > 'SELECT Country_Application.country_code, Country_Application.type,
> > Country_Application.appl_no,
> > Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> > Action_Due.item, Action_Due.action_due_resp_atty,
> > Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> > Action_Due.due_date,
> > Action_Due.remark, Action_Due.resp_admin
> >
> > from Country_Application INNER JOIN Action_Due on
> > Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
> > = Action_Due.sub_case
> > inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
> >
> >
> > where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> > ''ANNUITY''' + --hard code
> >
> > ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
> > ''''
> >
> > if @.resp not in ('ALL', '')
> > begin
> > set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> > end
> >
> > if @.country not in ('ALL', '')
> > begin
> > if @.country = '<> US'
> > begin
> > set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> > end
> > else
> > begin
> > set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> > +')'
> > end
> > end
> >
> > if @.entity not in ('ALL', '')
> > begin
> > set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> > Invention_Data.loc = '''+@.loc+''''
> > end
> >
> > if @.local_oc <> ''
> > begin
> > set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> > end
> >
> > if @.for_agent <> ''
> > begin
> > set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> > end
> >
> > exec sp_executesql @.sql
> >
> >
> >
> >
> >
> >
> >|||My suggestion is to put this in a stored procedure and call that instead and
see what that does for performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David Zhu" <DavidZhu@.discussions.microsoft.com> wrote in message
news:C32A3B2C-5392-4C4B-B909-13D6B1B26F9B@.microsoft.com...
> Hi Snyder,
> Thanks a lot.
> I also did some investigation. Unfortunately, the sql statement run fast
> enough in
> Sql query analyser. And I found the report run fast on the RS2000 without
> SP2 machine, but slow on RS2000 with SP2.
> Could you please give me some suggestion?
> "Wayne Snyder" wrote:
>> The best way to deal with this is to pull the query out and run it in SQL
>> SErver Management Studio - get the query plan and find out what is
>> happening...
>> You are using some <>, and functions in where clauses which prevents the
>> optimizer from using index statistics to choose indexes..so you might not
>> the
>> best plan...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "David Zhu" wrote:
>> > Hi,
>> >
>> > I construct a sql statement as a dataset. But when I click Run or
>> > Refresh,
>> > the VS.NET runs so slow and no response sometimes. Do you know why, and
>> > how
>> > to
>> > deal with this issue?
>> >
>> > below is the sql statement I used for:
>> >
>> > DECLARE
>> >
>> > @.sql NVARCHAR(4000)
>> >
>> > select @.resp=ltrim(rtrim(@.resp)),
>> > @.country =ltrim(rtrim(@.country )),
>> > @.entity=ltrim(rtrim(@.entity)),
>> > @.loc=ltrim(rtrim(@.loc)),
>> > --@.office=ltrim(rtrim(@.office)),
>> > @.local_oc=ltrim(rtrim(@.local_oc)),
>> > @.for_agent=ltrim(rtrim(@.for_agent))
>> >
>> > select @.sql =>> >
>> > 'SELECT Country_Application.country_code, Country_Application.type,
>> > Country_Application.appl_no,
>> > Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
>> > Action_Due.item, Action_Due.action_due_resp_atty,
>> > Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
>> > Action_Due.due_date,
>> > Action_Due.remark, Action_Due.resp_admin
>> >
>> > from Country_Application INNER JOIN Action_Due on
>> > Country_Application.pdno = Action_Due.pdno and
>> > Country_Application.sub_case
>> > = Action_Due.sub_case
>> > inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>> >
>> >
>> > where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type)
>> > <>
>> > ''ANNUITY''' + --hard code
>> >
>> > ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' +
>> > @.DateEnd +
>> > ''''
>> >
>> > if @.resp not in ('ALL', '')
>> > begin
>> > set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
>> > end
>> >
>> > if @.country not in ('ALL', '')
>> > begin
>> > if @.country = '<> US'
>> > begin
>> > set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
>> > end
>> > else
>> > begin
>> > set @.sql = @.sql + ' and Country_Application.country_code in ('+
>> > @.country
>> > +')'
>> > end
>> > end
>> >
>> > if @.entity not in ('ALL', '')
>> > begin
>> > set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
>> > Invention_Data.loc = '''+@.loc+''''
>> > end
>> >
>> > if @.local_oc <> ''
>> > begin
>> > set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
>> > end
>> >
>> > if @.for_agent <> ''
>> > begin
>> > set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
>> > end
>> >
>> > exec sp_executesql @.sql
>> >
>> >
>> >
>> >
>> >
>> >
>> >|||I have the same problem..
Unfortunately, things did improve when I wrote it into a stored procedure.
The bad part about this is that we are planning to allow our customers
create reports using our views. They will not like extremely slow reports. I
have tracked this down in the log files to the fact that the report is being
add to a job list and then not being run from 1 to 3 minutes.
I haven't figured out how to make the report run without going to this list.
"David Zhu" <DavidZhu@.discussions.microsoft.com> wrote in message
news:44673263-E25F-44C1-8C33-E5D9DF419422@.microsoft.com...
> Hi,
> I construct a sql statement as a dataset. But when I click Run or Refresh,
> the VS.NET runs so slow and no response sometimes. Do you know why, and
> how
> to
> deal with this issue?
> below is the sql statement I used for:
> DECLARE
> @.sql NVARCHAR(4000)
> select @.resp=ltrim(rtrim(@.resp)),
> @.country =ltrim(rtrim(@.country )),
> @.entity=ltrim(rtrim(@.entity)),
> @.loc=ltrim(rtrim(@.loc)),
> --@.office=ltrim(rtrim(@.office)),
> @.local_oc=ltrim(rtrim(@.local_oc)),
> @.for_agent=ltrim(rtrim(@.for_agent))
> select @.sql => 'SELECT Country_Application.country_code, Country_Application.type,
> Country_Application.appl_no,
> Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> Action_Due.item, Action_Due.action_due_resp_atty,
> Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> Action_Due.due_date,
> Action_Due.remark, Action_Due.resp_admin
> from Country_Application INNER JOIN Action_Due on
> Country_Application.pdno = Action_Due.pdno and
> Country_Application.sub_case
> = Action_Due.sub_case
> inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>
> where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> ''ANNUITY''' + --hard code
> ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd
> +
> ''''
> if @.resp not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> end
> if @.country not in ('ALL', '')
> begin
> if @.country = '<> US'
> begin
> set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> end
> else
> begin
> set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> +')'
> end
> end
> if @.entity not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> Invention_Data.loc = '''+@.loc+''''
> end
> if @.local_oc <> ''
> begin
> set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> end
> if @.for_agent <> ''
> begin
> set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> end
> exec sp_executesql @.sql
>
>
>
>|||David.
You may experience a great performance improvment if you write the
statement as a stored procedure AND avoid using dynamic execution
(sp_executesql).
In fact, you can rewrite your SQL query in a simple statement.
Try to replace code like the following:
if @.resp not in ('ALL', '')
begin
set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
end
by a condition in the clause WHERE:
WHERE
...
AND (@.resp not in ('ALL', '') or Action_Due.hpj_resp_atty in (select
[str] from fList(@.resp)))
where fList is as UDF that converts a comma-separated list of values to
a table:
ALTER FUNCTION fList(@.list ntext)
RETURNS @.tbl TABLE
( listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000) COLLATE
SQL_Latin1_General_CP1_CI_AS,
nstr nvarchar(2000) COLLATE
SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
if @.list is null return
DECLARE @.pos int, @.textpos int, @.tam smallint, @.tmpstr
nvarchar(4000), @.resto nvarchar(4000), @.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.resto = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.tam = 4000 - datalength(@.resto) / 2
SET @.tmpstr = @.resto + substring(@.list, @.textpos, @.tam)
SET @.textpos = @.textpos + @.tam
SET @.pos = charindex(',', @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(',', @.tmpstr)
END
SET @.resto = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.resto)),
ltrim(rtrim(@.resto)))
RETURN
END
David Zhu escreveu:
> Hi,
> I construct a sql statement as a dataset. But when I click Run or Refresh,
> the VS.NET runs so slow and no response sometimes. Do you know why, and how
> to
> deal with this issue?
> below is the sql statement I used for:
> DECLARE
> @.sql NVARCHAR(4000)
> select @.resp=ltrim(rtrim(@.resp)),
> @.country =ltrim(rtrim(@.country )),
> @.entity=ltrim(rtrim(@.entity)),
> @.loc=ltrim(rtrim(@.loc)),
> --@.office=ltrim(rtrim(@.office)),
> @.local_oc=ltrim(rtrim(@.local_oc)),
> @.for_agent=ltrim(rtrim(@.for_agent))
> select @.sql => 'SELECT Country_Application.country_code, Country_Application.type,
> Country_Application.appl_no,
> Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> Action_Due.item, Action_Due.action_due_resp_atty,
> Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> Action_Due.due_date,
> Action_Due.remark, Action_Due.resp_admin
> from Country_Application INNER JOIN Action_Due on
> Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
> = Action_Due.sub_case
> inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>
> where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> ''ANNUITY''' + --hard code
> ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
> ''''
> if @.resp not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> end
> if @.country not in ('ALL', '')
> begin
> if @.country = '<> US'
> begin
> set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> end
> else
> begin
> set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> +')'
> end
> end
> if @.entity not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> Invention_Data.loc = '''+@.loc+''''
> end
> if @.local_oc <> ''
> begin
> set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> end
> if @.for_agent <> ''
> begin
> set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> end
> exec sp_executesql @.sql|||David.
You may experience a great performance improvment if you write the
statement as a stored procedure AND avoid using dynamic execution
(sp_executesql).
In fact, you can rewrite your SQL query in a simple statement.
Try to replace code like the following:
if @.resp not in ('ALL', '')
begin
set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
end
by a condition in the clause WHERE:
WHERE
...
AND (@.resp not in ('ALL', '') or Action_Due.hpj_resp_atty in (select
[str] from fList(@.resp)))
where fList is as UDF that converts a comma-separated list of values to
a table:
ALTER FUNCTION fList(@.list ntext)
RETURNS @.tbl TABLE
( listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000) COLLATE
SQL_Latin1_General_CP1_CI_AS,
nstr nvarchar(2000) COLLATE
SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
if @.list is null return
DECLARE @.pos int, @.textpos int, @.tam smallint, @.tmpstr
nvarchar(4000), @.resto nvarchar(4000), @.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.resto = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.tam = 4000 - datalength(@.resto) / 2
SET @.tmpstr = @.resto + substring(@.list, @.textpos, @.tam)
SET @.textpos = @.textpos + @.tam
SET @.pos = charindex(',', @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(',', @.tmpstr)
END
SET @.resto = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.resto)),
ltrim(rtrim(@.resto)))
RETURN
END

Reporting Service DataSet

Hello Dears,

Im Starting to make an Report Using Reporting Service And i make Everything and i add the first data set and i make execute to the reprot its work ok

but now when i add the second dataset its not work it give me the following error :

Error: [rsMissingDataSetName] The data set name is missing in the data region

any body help me im fresh in reporting service

with my best regard

khalil hamad

Hi,

I think now you have two datasets,u will have to mention which dataset should be used for the report explicitly.

Take a look at this post and assign the dataset name

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348279&SiteID=1

Reporting Oracle Long datatypes

I am including a Oracle Long datatype dataset field in a table report item textbox cell and only getting 100 characters of the field displayed. Is this the maximum length possible or is it possible to display more characters?
I am connecting to the Oracle database with the Oracle provider for OLE DB and having no problems with other datatypes.Which version of the Oracle client is installed? It has to be the Oracle
8.1.7 client or later. Some people on the newsgroup have reported issues
with garbage returned when using the Oracle 8.1.5 client against an Oracle
9i server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RW" <RW@.discussions.microsoft.com> wrote in message
news:52AE66B5-576F-4BF3-B215-CC3193C0955E@.microsoft.com...
> I am including a Oracle Long datatype dataset field in a table report item
textbox cell and only getting 100 characters of the field displayed. Is this
the maximum length possible or is it possible to display more characters?
> I am connecting to the Oracle database with the Oracle provider for OLE DB
and having no problems with other datatypes.

Reporting on SharePoint list data?

Hi,
Can anyone direct me to a step by step tutorial on how to create a Reporting
Service's XML Dataset that queries a SharePoint list? I have found bits and
pieces in the SQL Books on line but I have not been able to put it all
together.
Thanks
Jeff.Hello again,
I was able to setup a dataset that queries a SharePoint list so I don't need
help on that any more :-).
The new problem: The report run fine in my development environment but when
I deploy my report and I try to run the report from the RS server I get an
"Query execution failed for data set 'DataSet1'" error. Since the
SharePoint server is running on a different machine I suspect that RS is
running the web service call under the identity of the ASP.NET thread which
does not have rights on the SharePoint server. I tried modifying the thread
pool that IIS is running RS but after I did that Report Manager would not
run any more :-(
What is the correct procedure to get RS to run under a domain account?
Thanks
Jeff.
"Jeff Richardson" <BobcatRidge@.newsgroups.nospam> wrote in message
news:uTVyJEaiGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Can anyone direct me to a step by step tutorial on how to create a
> Reporting Service's XML Dataset that queries a SharePoint list? I have
> found bits and pieces in the SQL Books on line but I have not been able to
> put it all together.
> Thanks
> Jeff.
>|||Hello Jeff,
Thank you for posting in the MSDN newsgroup.
As for the reporting service accessing SPS list data issue, I think your
thought on the security permission is reasonable. At development time in VS
IDE, it use the logon user as the security context to access any remote
datasource (if require authentcated) user. However, after you deployed it
to reportServer, if you haven't explicitly set any credential for the
datasource(used in the report), it'll still use the authenticated user(if
you choose integrated windows credential) or the report server's service
account(if you do not specify any credentials).
Anyway, changing the report server's service identity is certainly not
recommended. I suggest you consider choose a certain credential supply mode
for your SPS datasource. We can configure credentials for report datasource
through two means:
1. In the DataSource's "Credentials" properties at design-time in VS IDE.
2. Use the report manager to configure credentials info for datasource
after deploying to report server.
Here is the BOL reference about how to specify credentials for datasource
connection:
#Specifying Credential and Connection Information
http://msdn2.microsoft.com/en-us/library/ms160330.aspx
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Jeff,
Have got any further progress on this issue or does my last reply helps you
a little? If there is anything else we can help, please feel free to post
here.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Reporting off of stored procedures

Hi, I'm new to SQL Reporting Services. I want to create a report from a dataset where the source is a stored procedure. The sp does several things to generate the ultimate reporting rows; temp table(s) are created and updated, joined with other tables and views, etc. The final line of the sp is: select * from #temptable. I'm getting an error that the temp table is an invalid object. Also, this sp used to have input parameters which caused errors also, so I just hard coded values to get past that. Any ideas here? Thanks, Marta.

You might want to put the data you are ultimately querying in a permanent table, not a temp table. It may be that when you return the cursor, the temp table is no longer available and when the report is going through the cursor and trying to get records, the data is not available anymore.

|||

Hi,

The problem must be elsewhere. I am doing this without problem.

Did you set the report dataset to type StoredProcedure?

Did you try to call your sp from a query window?

Did you create the datasource as to be of type Microsoft SQL Server?

Also, I do not like the " Select * " try to specify the columns if you can.

As Jayplus said, a permanent table would be better (if the data will remain unchanged for a while).

Philippe