[Fix] - Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE

Wikitechy | 842 Views | sql server | 09 Jun 2016

 

Scenario :

  • SELECT statements containing TABLESAMPLE generate this error message.

Error :

    Server: Msg 477, Level 15, State 0, Line 1

    Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE 

    clause for table "<Table Name>". The value or seed must be an integer.

Reason for the error :

  • The TABLESAMPLE clause, presented in SQL Server 2005, restricts the number of rows fetched from a table in the FROM clause to either a sample number or to a PERCENT of rows. 

Syntax - TABLESAMPLE:

        TABLESAMPLE ( sample_number [PERCENT | ROWS] ) [ REPEATABLE (repeat_seed) ]

1. If ROWS is specified in the TABLESAMPLE clause, the sample_number must have an integer value as follows

SELECT * FROM [dbo].[WikiTechy_Table] TABLESAMPLE ( 1000.50 ROWS )
  • Again the command here will generate an error as follows:
  • Msg 477, Level 15, State 0, Line 1
  • Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE clause for table "dbo.WikiTechy_Table".

2. The value or seed must be an integer. Hence modify the statement as shown below:

SELECT * FROM [dbo].[WikiTechy_Table] TABLESAMPLE ( 50 ROWS ) REPEATABLE ( 100.0 )
  • Here again instead of integer, a decimal value is presented for REPEATABLE section. Hence the below error occurs:
  • Msg 477, Level 15, State 0, Line 1
  • Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE clause for table "dbo.WikiTechy_Table".

3. Finally to conclude, the value or seed must be an integer.

Fix : 

  • Ensure that the data delivered to the ROWS section or to the REPEATABLE section of the TABLESAMPLE clause is an integer value. Presented here the perfect sample for a TABLESAMPLE clause

SELECT * FROM [dbo].[WikiTechy_Table] TABLESAMPLE ( 1000 ROWS )

SELECT * FROM [dbo].[WikiTechy_Table] TABLESAMPLE ( 50 ROWS ) REPEATABLE ( 100 )


Applies to sqlserver version : 

  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

Related Error Tags :

  • SQL Server Performance Invalid ROWS value or REPEATABLE seed
  • Invalid ROWS value or REPEATABLE seed ā€œ%I64dā€
  • Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE




Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise