Sql-server – SSRS Query Timeout vs DatabaseQuery Timeout

sql serverssrs

In SSRS there are appear to be two separate query timeout options. The Query Timeout option can be found in the Dataset Properties dialog window on the Query tab. The DatabaseQuery Timeout option is found in the rsreportserver.config file. I realize the Query Timeout option is specific to the dataset, and the DatabaseQuery Timeout option is specific to Report Server.

What are the reasons for using different settings? Why not just make the Query Setting to always default to the overall report server DatabaseQuery setting? If I have a lower DatabaseQuery setting will that timeout before the Query setting is met?

Edit: I also want to know if this is in fact a serverside setting that can be overriden with the report specific setting. I can't seem to find any documentation on how these two settings are related/different.

Best Answer

I once supported an application that would timeout certain queries after 300ms (not a reporting system). If that application were to default to a server level setting, they wouldn't have been able to achieve the different timeouts that the business required. This is an extreme case of course, but it's an example of why you might want different settings at the server and data set level.

Related Solutions

SQL Server – How to Pass Multi-Valued Characters in SSRS Report

I found a solution. I did not properly split the values in the stored procedure with a UDF.

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:

=join(Parameters!<your param name>.Value,",")

This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause.

Google search any UDF string parser online. There are many to choose from. I used dba_parseString_udf from Michelle Ufford http://sqlfool.com.

Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as follows:

WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))

Therefore, SSRS will pass the following value:

@Flag = 'A,B,C'

Then my UDF will parse that string out correctly to:

A
B
C

And populate my @Flag parameter correctly with SELECT * FROM UDF()...

Sql-server – Which timeout(s) are causing the linked server query to time out

You could try putting the remoteData into a temp table before doing the merge. a lot of your time will be taken up with comparisons of data not the actual retrieval so if you run the

SELECT remoteData.Name AS Name, remoteData.Value AS Value
into #remoteData
    FROM OPENQUERY (
        MyLinkedServer,'
        SELECT TOP 500000 Name, Value
        FROM MyDatabase.dbo.MyTable
        WHERE Synced = 0'
    )

then in the merge statement

MERGE INTO dbo.MyTable AS target
USING (
    SELECT localData.ID, remoteData.Name AS Name, remoteData.Value AS Value
    FROM #remoteData as remoteData
    JOIN dbo.MyTable AS localData ON localData.Name = remoteData.Name
    WHERE localData.Synced = 0
) AS source

It may help (can't promise)

Best Answer

Related Solutions

SQL Server – How to Pass Multi-Valued Characters in SSRS Report

Sql-server – Which timeout(s) are causing the linked server query to time out

Related Question