Any way to put a query timeout on the datasource behind a report model?

Folks, we're wanting to put a timeout of 2 minutes on the queries which get generated by an ad-hoc report built in Report Builder. This particular report model has been generated on top of a cube by clicking the Generate Model button after creating an Analysis Services datasource in Report Manager. The connection string for that datasource reads:

Data Source=(local);Initial Catalog=MyCubeDatabase

I expected there to be a connection string property I could set (such as "Timeout=120") but I can't seem to find that in the documentation anywhere... And Timeout=120 doesn't appear to do it. Do you know of one? If not, do you know of another way to put a timeout limit on a Report Builder generated query or a Report Builder report itself?

I tried setting the "Limit report execution to the following number of seconds" at /Reports/Pages/Settings.aspx in Report Manager to 120, but that does not appear to have an effect on Report Builder reports.

See the following bug I reported for more details:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=232694

This is quite an urgent issue for us as we go live in a week. Any help would be appreciated!

Time out is this a joke , this tool can't even get the rigth result's .... Maybe in version 11B pacth 12334 you have the time out feature

Any way to create 5KB size of Database?

Hi
when we create new database that will take base structure and size of the
model database. it will be approx 1.43 MB as per version. now i want to
create database that size is 5kb any way to create database like that size,
why the reason is for CD technolgy we have less space.. can u suggest me?
Thanks
--
Sriram.Srikanth
SQL-Server Database Administrator,Hi
The newly created DB gets it's sizing from Model DB. Model DB can not be
made smaller. With all the structures needed in the MDF and LDF files,
1.43mb is the smallest you can get.
Why ship the DB? Create the DB though T-SQL code on the client machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||What you could do is distribute the MDF file only, in a zipped version...
Then unzip it, and use sp_attach_single_file_db to load it up..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||You could also simply distribute the create database/object script. This
would insulate you from physical changes to the mdf file, which is a risk
with the other way I mentioned.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>

Any help would be welcomed!

I'm using DMO library.
I'm struggling and pulling out my hair trying to find the XXXX.. property
for the Recovery Model (simple, full,
Server.Databases.Item1....
Does anyone here have
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)Seems to be a Property of the DBOption2 object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:0E4F63B6-E3F5-4CD0-BC2A-9D71F68B3717@.microsoft.com...
> I'm using DMO library.
> I'm struggling and pulling out my hair trying to find the XXXX.. property
> for the Recovery Model (simple, full,
> Server.Databases.Item1....
> Does anyone here have
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)|||jezz, seeing on dmo I have only:
server.databases.item.dboption...
!!
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:

> Seems to be a Property of the DBOption2 object.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:0E4F63B6-E3F5-4CD0-BC2A-9D71F68B3717@.microsoft.com...
>|||I'm sorry...I've got it
Dim oss1 As SQLDMO.DBOption2
?oss1.RecoveryModel
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:

> Seems to be a Property of the DBOption2 object.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:0E4F63B6-E3F5-4CD0-BC2A-9D71F68B3717@.microsoft.com...
>

Answer: Report Model/Builder

In a database there are 2 tables, one is student and the other is activities. A student can have multiple activities. There is a one to many relationship between student and activities based upon student_id. Not every student has an activity.
In report builder I would like all of the students to appear regardless of whether they have an activity or not. By default, only those students who have activities appear.

This appears:
Student Activity
1 Track
2 Track
4 Volleyball

I would like to see:
Student Activity
1 Track
2 Track
3 NULL
4 Volleyball

What settings need to be tweaked to make this happen?

Thanks for your assistance.Hi

What query are you using to generate this report? I created 2 tables: student, activity. Not all students have activities. If I use the following query:

select * from student
left outer join activity on student.i = activity.i

where i = student id in both the tables,
I get the list of students, activities (even for students who dont have activities mapped on the activity table)

The Left Outer Join operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.

Is this what you were looking for?

Thanks

|||Within Report Designer you can create a query and specify any type of join you need. This is IN REPORT BUILDER, where you are not able to specifiy the type of join you would like to use. The query is generated from the report model and dragging fields into the design area.

Thanks for your response though.|||Hi Chris

Can you try this out and let me know if this works?

Create a report model and within the data source view, right click and add a NEW NAMED QUERY, here you can specify a query with joins. Once this query is created, create the report model. This should also list the query that you have created, apart from the 2 tables.

Now from Report Builder, select this datasource and drag/drop your query.
Remove the columns that you dont want (I just had student id and activity)
and run report.

This will give you all records, even the students who dont have activities listed.

If you are just using a report model, I think you need to edit that and add a query into that, so you can use it in your report builder to get the required data.

Thanks.|||

Actually, you don't need to create a named query to do this. You can do it in the Report Model. In your example, you need to set Cardinality=OptionalMany on the role from Student to activity. This tells RB that not all students have activities (so don’t do an inner join or you might lose a few).

Answer: Report Model/Builder

In a database there are 2 tables, one is student and the other is activities. A student can have multiple activities. There is a one to many relationship between student and activities based upon student_id. Not every student has an activity.
In report builder I would like all of the students to appear regardless of whether they have an activity or not. By default, only those students who have activities appear.

This appears:
Student Activity
1 Track
2 Track
4 Volleyball

I would like to see:
Student Activity
1 Track
2 Track
3 NULL
4 Volleyball

What settings need to be tweaked to make this happen?

Thanks for your assistance.Hi

What query are you using to generate this report? I created 2 tables: student, activity. Not all students have activities. If I use the following query:

select * from student
left outer join activity on student.i = activity.i

where i = student id in both the tables,
I get the list of students, activities (even for students who dont have activities mapped on the activity table)

The Left Outer Join operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.

Is this what you were looking for?

Thanks

|||Within Report Designer you can create a query and specify any type of join you need. This is IN REPORT BUILDER, where you are not able to specifiy the type of join you would like to use. The query is generated from the report model and dragging fields into the design area.

Thanks for your response though.|||Hi Chris

Can you try this out and let me know if this works?

Create a report model and within the data source view, right click and add a NEW NAMED QUERY, here you can specify a query with joins. Once this query is created, create the report model. This should also list the query that you have created, apart from the 2 tables.

Now from Report Builder, select this datasource and drag/drop your query.
Remove the columns that you dont want (I just had student id and activity)
and run report.

This will give you all records, even the students who dont have activities listed.

If you are just using a report model, I think you need to edit that and add a query into that, so you can use it in your report builder to get the required data.

Thanks.|||

Actually, you don't need to create a named query to do this. You can do it in the Report Model. In your example, you need to set Cardinality=OptionalMany on the role from Student to activity. This tells RB that not all students have activities (so don’t do an inner join or you might lose a few).