Reporting Services and Linked Server - can't stop execution of pro

Hello,
I have the same problem with Reporting Services and Linked Server.
The Reporting Services receive data source from stored procedure, which
executes inside it stored procedure on the Linked Server. If Reporting
Service successfully forms the report, then everything is OK, but if there
are some troubles (e.g. I close the browser page where report was forming), I
have the problem: the process on the Linked Server doesnâ't stop itself. Itâ's
continuing executing for eternity and locks other processes. The only way to
stop it â' is to kill it. Iâ'll try to set timeouts in the configuration of
Reporting Service and Query Timeout in the Server Options of Linked Server,
but it doesnâ't help. This problem shows itself only if I work through Linked
Server.
Thanks for your help.
Boris.The default timeout for linked server queries is 600 seconds, i.e. 10
minutes. This can be changed in SQL Server using an sp_configure option
"remote query timeout".
Yes, you're right, this is a problem which has nothing to do with Reporting
Services. You may want to repost the relevant part of your question to a SQL
Server forum. You may get more advice on the issue.
HTH
Charles Kangai, MCT, MCDBA
"Boris" wrote:
> Hello,
> I have the same problem with Reporting Services and Linked Server.
> The Reporting Services receive data source from stored procedure, which
> executes inside it stored procedure on the Linked Server. If Reporting
> Service successfully forms the report, then everything is OK, but if there
> are some troubles (e.g. I close the browser page where report was forming), I
> have the problem: the process on the Linked Server doesnâ't stop itself. Itâ's
> continuing executing for eternity and locks other processes. The only way to
> stop it â' is to kill it. Iâ'll try to set timeouts in the configuration of
> Reporting Service and Query Timeout in the Server Options of Linked Server,
> but it doesnâ't help. This problem shows itself only if I work through Linked
> Server.
> Thanks for your help.
> Boris.
>sql

Reporting services and filtering

Hi,

I have being trying this for quiet a while now and I just cant find a solution. Basically I have a field called Total Active. The total active indicates for example..... A project is open or closed. If the project is open, the value 1 is displayed in the database and if closed the value 0 is displayed in the database.

So I have my field in reporting services and now I just want to count all the open projects. I thought you could do this:

= Count(Fields!Active_flag.value = 1)

This does not seem to work.....or my syntax is wrong or something.

Then I tried to pass all the values in Fields!Active_flag as a object using VB custom code. Example:

=code.getTotalActive(Fields!Active_flag.value)

then receive the Object and loop through it to count all the 1's in the Object (Active_flag.value) and pass the total back. However I was not successful with this because I couldn’t get the length of the Object....which I needed to iterate through all the values.

My next attempt which I thought was a sure winner was using a group and filtering out all the 0's which left only 1's that I could count. But this brought about another problem.....the filter only looks for 1's and then counts the 1's and displays then.... But when it doesn’t find any 1's then the entire Field does not display on the report. This is very frustrating because I would rather have is say "no projects active" instead of it not displaying.

Any help will be much appreciated. I have boggled my mind for the solution.

thanks

Ziyaad

I had something similar in my report but it is not that simple because you need CASE Statement to split the column into two Case Active 1, Case Inactive 0 but SQL Server will not recognize your derived columns as valid so you need to put both in temp table and do the calculation. There maybe a more elegant solution but this will give you what you want. I have done many reports both Crystal and SQL Server in the Enterprise so I know that is not an expression problem because SQL can solve it.

So split it do Count(*) of the active column in a temp table.

|||

Not sure I follow. This active and inactive status could change any time. This is because my database pulls information from another SQL database and this process of obtaining my data is run every nigh using a SQL DTS Package.

Could you please explain your solution more?

Thanx

|||If you are using DTS package why not just create separate Active or Inactive destination columns so all you have to do is count the Active column for what you need.|||

Ok, I understand that...I just feel like it's going to be a big effort to create the package all over again. Are you sure there isnt another way?

I am sure using the custom code is easier... if you can get the length of the object passed. I am very new to Reporting Services, which means I basically just started...as well as creating the DTS Packages.

I possible could you maybe explain shortly or give some idea how I would seperate the colums using the DTS packages?

thanks

|||

If you don't want to do it in the DTS package then move the data to a staging table then do an INTO that is SELECT INTO which creates a table with the CASE 1 then Active CASE 0 then Inactive and write a stored procedure which pulls the data from the destination table with COUNT(*) for the Active column and you will get the total. I could not find a more elegant solution, expecting reporting service to filter and give you a count you will need to be an expression expert.

Another option is to create a View with all your query result with Active and Inactive as columns then run a SELECT with COUNT(*) for Active. Run a search for CASE in the BOL and check the link below for more CASE samples.

http://www.craigsmullins.com/ssu_0899.htm

|||

If in the database you have a 1 or 0 (1 being active), couldnt you just do a Sum(Fields!Active.Value) to get the total number of active projects?

Or am I oversimplifying?

BobP

|||

That sound like a good idea, using the CASE. I have done this and added it to my storedPROC which is used to populate my datasource.

However I would like to add that values now to my Report Example:

=Fields!Active.value

However I cannot find this in the expressions box. Is this right and how do i get access to the CASE field from my StoredPROC.

Thanx

|||Modify the CASE statement to add 1 AS Active and 0 AS Inactive, I am sorry I assumed you understand when I told you to do the criteria based conditional split you will know that the derived columns should be renamed. If you are still getting problem use the Case in a Create View which makes both Active and Inactive fields by default and do Select (*) from the View and do the sum or COUNT(*) with SUM.

Reporting services and filtering

Hi,

I have being trying this for quiet a while now and I just cant find a solution. Basically I have a field called Total Active. The total active indicates for example..... A project is open or closed. If the project is open, the value 1 is displayed in the database and if closed the value 0 is displayed in the database.

So I have my field in reporting services and now I just want to count all the open projects. I thought you could do this:

= Count(Fields!Active_flag.value = 1)

This does not seem to work.....or my syntax is wrong or something.

Then I tried to pass all the values in Fields!Active_flag as a object using VB custom code. Example:

=code.getTotalActive(Fields!Active_flag.value)

then receive the Object and loop through it to count all the 1's in the Object (Active_flag.value) and pass the total back. However I was not successful with this because I couldn’t get the length of the Object....which I needed to iterate through all the values.

My next attempt which I thought was a sure winner was using a group and filtering out all the 0's which left only 1's that I could count. But this brought about another problem.....the filter only looks for 1's and then counts the 1's and displays then.... But when it doesn’t find any 1's then the entire Field does not display on the report. This is very frustrating because I would rather have is say "no projects active" instead of it not displaying.

Any help will be much appreciated. I have boggled my mind for the solution.

thanks

Ziyaad

I had something similar in my report but it is not that simple because you need CASE Statement to split the column into two Case Active 1, Case Inactive 0 but SQL Server will not recognize your derived columns as valid so you need to put both in temp table and do the calculation. There maybe a more elegant solution but this will give you what you want. I have done many reports both Crystal and SQL Server in the Enterprise so I know that is not an expression problem because SQL can solve it.

So split it do Count(*) of the active column in a temp table.

|||

Not sure I follow. This active and inactive status could change any time. This is because my database pulls information from another SQL database and this process of obtaining my data is run every nigh using a SQL DTS Package.

Could you please explain your solution more?

Thanx

|||If you are using DTS package why not just create separate Active or Inactive destination columns so all you have to do is count the Active column for what you need.|||

Ok, I understand that...I just feel like it's going to be a big effort to create the package all over again. Are you sure there isnt another way?

I am sure using the custom code is easier... if you can get the length of the object passed. I am very new to Reporting Services, which means I basically just started...as well as creating the DTS Packages.

I possible could you maybe explain shortly or give some idea how I would seperate the colums using the DTS packages?

thanks

|||

If you don't want to do it in the DTS package then move the data to a staging table then do an INTO that is SELECT INTO which creates a table with the CASE 1 then Active CASE 0 then Inactive and write a stored procedure which pulls the data from the destination table with COUNT(*) for the Active column and you will get the total. I could not find a more elegant solution, expecting reporting service to filter and give you a count you will need to be an expression expert.

Another option is to create a View with all your query result with Active and Inactive as columns then run a SELECT with COUNT(*) for Active. Run a search for CASE in the BOL and check the link below for more CASE samples.

http://www.craigsmullins.com/ssu_0899.htm

|||

If in the database you have a 1 or 0 (1 being active), couldnt you just do a Sum(Fields!Active.Value) to get the total number of active projects?

Or am I oversimplifying?

BobP

|||

That sound like a good idea, using the CASE. I have done this and added it to my storedPROC which is used to populate my datasource.

However I would like to add that values now to my Report Example:

=Fields!Active.value

However I cannot find this in the expressions box. Is this right and how do i get access to the CASE field from my StoredPROC.

Thanx

|||Modify the CASE statement to add 1 AS Active and 0 AS Inactive, I am sorry I assumed you understand when I told you to do the criteria based conditional split you will know that the derived columns should be renamed. If you are still getting problem use the Case in a Create View which makes both Active and Inactive fields by default and do Select (*) from the View and do the sum or COUNT(*) with SUM.