Use of spt_values to generate a short series of integer consecutive values

WARNING

In SQL server instance spt_values is an undocumented table in the master database. Do not use this table if you are worried that Microsoft might decide to drop it in a future release of SQL server. As of now it exists in SQL 2008 R2 and it still exists in SQL 2012.

Problem

Generate a series of ID badge numbers in the range between 1001 and 30000. The amount of ID badges that are ordered in one batch is between 20 and 100. Each time an order is created, the ID card records must be inserted automatically for each number in the range. E.g. when a new order is made for badges from 1501 to 1520, then 20 badge records must be created with these numbers. Instead of using a FOR loop in the SQL code or instead of generating my own table of consecutive numbers (which the customer did not prefer) I opted to use the numbers in the spt_values table.

Solution

Given the starting number and ending number, a T-sql statement must generate the range of numbers from start to end.

DECLARE @OrderSerialNrFrom int;

DECLARE @OrderSerialNrTo int;

SET @OrderSerialNrFrom = 1501;

SET @OrderSerialNrTo = 1520;

SELECT DISTINCT n = number + (@OrderSerialNrFrom - 1) 

        FROM master..[spt_values] WHERE number BETWEEN 1 AND (@OrderSerialNrTo - @OrderSerialNrFrom + 1)

GO

The result looks like this:

n
-----------
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520

(20 row(s) affected)

To insert new ID badge records with this range and data from the order, the T-SQL statement looks like this:

DECLARE @OrderSerialNrFrom smallint;

DECLARE @OrderSerialNrTo smallint;

DECLARE @OrderId int;

DECLARE @Comment nvarchar(100);

SET @OrderSerialNrFrom = 1501;

SET @OrderSerialNrTo = 1520;

SET @OrderId = 101;

SET @Comment = N'Expo 2014';

WITH a (SerialNr)

    AS (SELECT DISTINCT n = number + (@OrderSerialNrFrom - 1) 

        FROM master..[spt_values] WHERE number BETWEEN 1 AND (@OrderSerialNrTo - @OrderSerialNrFrom + 1)

        )

    INSERT INTO dbo.Badge (BadgeNr, IsActive, IsBlocked, Orderid, Comment)

    SELECT a.SerialNr, 0, 0, @OrderId, @Comment

        FROM a;

GO

Conclusion

With the aid of spt_vlaues I can easily create small ranges of records without adding complicated code or extra tables to the database.

In SQL 2012, there is a SEQUENCE object that could be used instead of spt_values for generating the number series.

Expand or collapse a report group depending on the value of a parameter

Problem: synchronize expand/collapse depending on the value of a parameter

In a report with a tablix with different group levels, the lower levels need to be expanded automatically when a specific parameter is filled in and stay collapsed when the parameter has not been filled in. This report has subscriptions that are emailed in HTML format to general managers who do not need to see all detail. The same report has subscriptions for regional managers who only need to see their region with the detail for child groups expanded automatically. when the Report is opened with IE the proper expand/collapse symbols must be shown.

Solution: use expressions to set value for Hidden and for InitialToggleState

The parameter is called RegionId, when the parameter value is null then only country and region is shown, the other groups are collapsed.

E.g. on the rowgroup ZoneName.

Set Hidden property

Select group properties

In the dialog window select Visibility in the left pane and click Show or hide based on an expression.

The display can be toggled by the parent group report item ‘RegionName’.

Click on the Fx button to create the expression

=IsNothing(Parameters!RegionId.Value)

This expression is to set the value of the Hidden property: when Nothing is filled in for the regionId then the isNothing function evaluates to True, meaning that the ZoneGrup will be hidden (collapsed).

To the contrary, the RegionName and CountryName group visiblity is set to ‘Show’.

Set the InitialToggleState value

This has to be set to True when the Region is not selected to show (+) sign. Set to False when the Region is selected to show a (-) sign.

Select the ZoneName textbox, in the Visual Studio properties window, scroll down to the InitialToggleState property

Set the property to the expression:

=Not(IsNothing(Parameters!RegionId.Value))

The same has to be done for the RegionName and CountryName textboxes.

Result

Initial report when no Region is selected:

• Country group is expanded and (-) is shown to collapse it.


• Region group is collapsed and (+) is shown to expand.

The initial report when the Region id is set to a specific value (as would be the case for a subscription for the particular region manager).

• Country group is expanded and (-) is shown to collapse it.


• Region group is expanded and (-) is shown to collapse it.


• Zone is expanded and (-) is shown to collapse it.

SSRS group toggle visibility show initially as expanded, but shows plus sign instead of minus

SQL Server 2008 R2

Problem

A report with mulitple row groups is defined to toggle opened or closed. The requirement is to show the report with the groups initially shown as expanded. This works fine, but the toggle sign is shown counterintuitively as a plus (+) sign and not as the minus (-) sign.

The child groups and detail sections are setup to be shown with a toggle item that is referring to the parent group.

Typically a report with the detail expanded, is shown with (+) sign as toggle. From the (+) sign you would expect to expand the details, instead when clicking it, it will collapse.

Solution to initially show toggle item expanded with minus (-) sign

In hte example there are 3 group levels: country – region – zone, distinguished by the fields CountryName, RegionName, ZoneName. The basic toggle setup is as follows:

• In Visual Studio 2008 report design, select the tablix with the report groups
• In the row group pane, open the DetailsGroup rowgroup dropdown and select ‘Group properties…’
  
• In the Group Properties dialog window, in the left hand pane, select ‘Visibility’. In the right hand pane, the ‘Change display options are shown.
• Select the radio button option ‘Show’
• Check ‘Display can be toggled by this report item option’ and select the name of the parent group field, in this case ‘ZoneName’.
  
• Clcik OK to accept the change and close the dialog.
• The same is done for the rowgroup with the ZoneName. Open the Group properties dialog, set option to show, check and select the parent groupt report item ‘RegionName’.
• The same is done for the RegionName rowgroup, set to ‘CountryName’.

At this stage we have a report that opens expanded as shown in the introduction, with a (+) sign instead of (-)sign.

The next step is to set the InitialToggleState of each toggle report item to True.

• In the tablix, select the textbox with the toggle item ‘ZoneName’.
• In the Properties window of Visual Studio (NOT in the ‘Text Box properties… dialog window when one rightclicks on a textbox), scroll down to the InitialToggleState and set this property to True.
  
• Do the same for the textbox with the RegionName and CountryName.
• Save the report and preview it. Now it will show items expanded with a (-) sign.
  

Conclusion

On reports with several group levels, for which you want to toggle expand and show the report initially expanded but with (-) sign in front of the toggle report item, set the InitialToggleState property of the report item to True.

WCF client: The request channel timed out while waiting for a reply after 00:00:59.998

Problem

A WCF service that needs to prepare a list of technical data which it requests from an external system, needs between 1 and 2 minutes to respond. The WCF client times out with the message

“The request channel timed out while waiting for a reply after 00:00:59.998…”

Solution

Increase the send timeout value on the client service instantiation.

MyWcfService.MyWcfServiceClient dsvc = new MyWcfServiceClient();

//TimeSpan(days, hours, minutes, seconds) - increase send timeout to wait for 10 minutes

dsvc.Endpoint.Binding.SendTimeout = new TimeSpan(0, 0, 10, 0);  

Disadvantage

Every client application needs to set the timeout value. In this particular case it would be sufficient to set the timeout value on the service server side, preferably with values in web.config. I need to investigate this further.