Ad-hoc reporting memory consumption

Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working. We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)? Thanks. Alberto.

Hi Alberto,

We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.

|||

Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.

HTH.

Alberto

Ad-hoc reporting memory consumption

Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working.

We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)?

Thanks.

Alberto.

Hi Alberto,

We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.

|||

Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.

HTH.

Alberto

Adding Records in SQL Server Express 2005

When adding records containing a date field in SQL Server 2005 Express I get an errorInput string was not in a correct format. Do datefields have to be converted when adding or editing into strings?

I usually insert dates into SQL in String format. SQL can convert them by himself. BUT at least in our environment you have to create the date string in "MM.DD.YYYY" format so if you are trying "DD.MM.YYYY" It propably wont work though I know this could be solved with localization somewhere, somehow :)
|||

I do have it in string format. Any Ideas? My code excerpt

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:Web2005ConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:Web2005ConnectionString1.ProviderName %>"

SelectCommand="SELECT * FROM [People] WHERE ([id] = @.id)"

InsertCommand="INSERT INTO [People] ([LastName], [FirstName], [Price], [LogDate]) VALUES (@.LastName, @.FirstName, @.Price, @.LogDate)"UpdateCommand="UPDATE [People] SET [LastName] = @.LastName, [FirstName] = @.FirstName, [Price] = @.Price, [LogDate] = @.LogDate WHERE [id] = @.id"DeleteCommand="DELETE FROM [People] WHERE [id] = @.id">

<SelectParameters>

<asp:ControlParameterControlID="GridView1"Name="id"PropertyName="SelectedValue"

Type="Int64"/>

</SelectParameters>

<UpdateParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

<asp:ParameterName="id"Type="Int64"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

</InsertParameters>

Adding a value to a 'datetime' column caused overflow.

Hi,
When I use dateadd function to a table containing around 10000 values,
it gave the following msg. Adding a value to a 'datetime' column caused
overflow. What does it mean?
Thanks,
Mike
You are exceeding the valid datetime range differs if you use datetime
or smalldateimte, which command did you use ? could you please post the
commandtext you are using ? What is the datatype of you are doing the
dateadd operation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for your reply.
I am using float type date to convert to a datetime. Please see the
following codes.
select dateadd(dd, Def_Date, '1/1/1960') from one;
Thanks,
Mike
Jens wrote:
> You are exceeding the valid datetime range differs if you use datetime
> or smalldateimte, which command did you use ? could you please post the
> commandtext you are using ? What is the datatype of you are doing the
> dateadd operation.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
|||Thanks a lot! This is exactly the problem!
Thanks,
Mike
Gert-Jan Strik wrote:[vbcol=seagreen]
> Note that when you use dateadd, the second parameter should be a number
> representing the number of ... (in your case days) that should be added.
> If Def_Date is a float "representing" a date, then this value is likely
> to be too high. If it exceeds 2936549 you will get an out or range error
> (or similar error). If it represents a date, you should cast it to a
> datetime.
> Gert-Jan
>
> Michael wrote:

Adding a value to a 'datetime' column caused overflow.

Hi,
When I use dateadd function to a table containing around 10000 values,
it gave the following msg. Adding a value to a 'datetime' column caused
overflow. What does it mean?
Thanks,
MikeYou are exceeding the valid datetime range differs if you use datetime
or smalldateimte, which command did you use ? could you please post the
commandtext you are using ? What is the datatype of you are doing the
dateadd operation.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks for your reply.
I am using float type date to convert to a datetime. Please see the
following codes.
select dateadd(dd, Def_Date, '1/1/1960') from one;
Thanks,
Mike
Jens wrote:
> You are exceeding the valid datetime range differs if you use datetime
> or smalldateimte, which command did you use ? could you please post the
> commandtext you are using ? What is the datatype of you are doing the
> dateadd operation.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Note that when you use dateadd, the second parameter should be a number
representing the number of ... (in your case days) that should be added.
If Def_Date is a float "representing" a date, then this value is likely
to be too high. If it exceeds 2936549 you will get an out or range error
(or similar error). If it represents a date, you should cast it to a
datetime.
Gert-Jan
Michael wrote:
> Thanks for your reply.
> I am using float type date to convert to a datetime. Please see the
> following codes.
> select dateadd(dd, Def_Date, '1/1/1960') from one;
> Thanks,
> Mike
> Jens wrote:
> > You are exceeding the valid datetime range differs if you use datetime
> > or smalldateimte, which command did you use ? could you please post the
> > commandtext you are using ? What is the datatype of you are doing the
> > dateadd operation.
> >
> > HTH, Jens K. Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --|||Thanks a lot! This is exactly the problem!
Thanks,
Mike
Gert-Jan Strik wrote:
> Note that when you use dateadd, the second parameter should be a number
> representing the number of ... (in your case days) that should be added.
> If Def_Date is a float "representing" a date, then this value is likely
> to be too high. If it exceeds 2936549 you will get an out or range error
> (or similar error). If it represents a date, you should cast it to a
> datetime.
> Gert-Jan
>
> Michael wrote:
> >
> > Thanks for your reply.
> >
> > I am using float type date to convert to a datetime. Please see the
> > following codes.
> >
> > select dateadd(dd, Def_Date, '1/1/1960') from one;
> >
> > Thanks,
> > Mike
> >
> > Jens wrote:
> > > You are exceeding the valid datetime range differs if you use datetime
> > > or smalldateimte, which command did you use ? could you please post the
> > > commandtext you are using ? What is the datatype of you are doing the
> > > dateadd operation.
> > >
> > > HTH, Jens K. Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --

Adding a value to a 'datetime' column caused overflow.

Hi,
When I use dateadd function to a table containing around 10000 values,
it gave the following msg. Adding a value to a 'datetime' column caused
overflow. What does it mean?
Thanks,
MikeThanks a lot! This is exactly the problem!
Thanks,
Mike
Gert-Jan Strik wrote:[vbcol=seagreen]
> Note that when you use dateadd, the second parameter should be a number
> representing the number of ... (in your case days) that should be added.
> If Def_Date is a float "representing" a date, then this value is likely
> to be too high. If it exceeds 2936549 you will get an out or range error
> (or similar error). If it represents a date, you should cast it to a
> datetime.
> Gert-Jan
>
> Michael wrote:|||You are exceeding the valid datetime range differs if you use datetime
or smalldateimte, which command did you use ? could you please post the
commandtext you are using ? What is the datatype of you are doing the
dateadd operation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks for your reply.
I am using float type date to convert to a datetime. Please see the
following codes.
select dateadd(dd, Def_Date, '1/1/1960') from one;
Thanks,
Mike
Jens wrote:
> You are exceeding the valid datetime range differs if you use datetime
> or smalldateimte, which command did you use ? could you please post the
> commandtext you are using ? What is the datatype of you are doing the
> dateadd operation.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Note that when you use dateadd, the second parameter should be a number
representing the number of ... (in your case days) that should be added.
If Def_Date is a float "representing" a date, then this value is likely
to be too high. If it exceeds 2936549 you will get an out or range error
(or similar error). If it represents a date, you should cast it to a
datetime.
Gert-Jan
Michael wrote:[vbcol=seagreen]
> Thanks for your reply.
> I am using float type date to convert to a datetime. Please see the
> following codes.
> select dateadd(dd, Def_Date, '1/1/1960') from one;
> Thanks,
> Mike
> Jens wrote: