Sql Server : Maximum Recursion on a CTE

Quick one today:

In the past, we’ve covered a MAXDOP query hint with details and nuances (and here too). This time, we’ll get into another interesting query hint MAXRECURSION.

One of my colleagues needed to come up with a numbers table with values like this, where there are multiple batches and each batch has some records.

Batch	RecordsInBatch
1	1
1	2
1	3
1	4
…	…
1	2500
2	1
2	2
..	..
2	2500
..	..
999	2500

This could be achieved with recursive CTE with a table OPTION (MAXRECURSION = n).

--
--  Numbers table with recursive CTE
--
DECLARE @BN INT = 999, @RN INT = 2500

;WITH BatchNumbers (BatchNum)
AS	(
		SELECT 1 AS BatchNum
		UNION ALL
		SELECT BatchNum + 1 AS BatchNum
		FROM BatchNumbers
		WHERE BatchNum < @BN
	),
RecordNumbers (RecNum)
AS	(
		SELECT 1 AS RecNum
		UNION ALL
		SELECT RecNum + 1 AS RecNum
		FROM RecordNumbers
		WHERE RecNum < @RN
	)
SELECT BatchNumbers.BatchNum, RecordNumbers.RecNum
FROM RecordNumbers
CROSS JOIN BatchNumbers
ORDER BY BatchNumbers.BatchNum, RecordNumbers.RecNum
OPTION (MAXRECURSION 2500)
GO

MAXRECURSION – points to keep in mind

• Server-wide, the limit is set to 100.
  • Use MAXRECURSION query hint to prevent infinite loops
• Server-wide default is set to 100
• When MAXRECURSION 0 is specified, it is same as infinite loops (no limit)
• In query hint, values for MAXRECURSION range from 0 to 32,767
• In cases where incorrect code enters into production, that results in infinite loops, MAXRECURSION could be used to limit the loops.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Error Messages: An error occurred while executing batch. Error message is: There is not enough space on the disk.

Quick one today:

Recently, we ran into this interesting error when running some random queries on a Sql Server machine.

An error occurred while executing batch. Error message is: 
There is not enough space on the disk.

The error message seems nebulous and its hard to decipher what it is referring to. The usual suspects of MDF & LDF files are all good. All data and backup drives on the server have plenty of free space. So it was a bit confusing.

Upon further banging-head-on-the-desk, it became obvious that the problem is not with Sql Server, but with SSMS. On Operating System, Management Studio has its own workspace, where it stores all the results, and that is running out of free space.

Resolution:

• Go to %TEMP% directory and clear up some space.
• And clear up some more space on C: (where SSMS is installed).

With these two actions, SSMS works again.

Hope this helps,
_Sqltimes

Read Full Post »

SSRS Error Message : The dataset `DataSource1′ refers to the shared data source `DataSource1′, which is not published on the report server. The shared data source `DataSource1′ must be published before this report can run.

Quick one today:

Earlier today, in our SSRS lab, after deploying reports using PowerShell, we ran into this error:

The dataset `DataSource1' refers to the shared data source `DataSource1', 
which is not published on the report server. The shared data source `DataSource1' 
must be published before this report can run.

Obviously, since we are still in the process of figuring out the correct “SSRS Deployment Steps” for automation, we may have missed a step or two. In this case, we missed/forgot the “data source association to each RDL” step.

Resolution:

Since we are still figuring out an automated step to it, for now, this is what we did to resolve this issue manually.

1. Go to Reports Manager URL on IE browser (with Administrator rights)
2. Go to the individual report/RDL and hover mouse over it.
3. Click on the right side yellow drop down and go to ‘Manage’
4. Go to ‘Data Sources’ tab
5. Assign the correct shared data source (see below image for clarity)

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server 2016 : Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase

Quick one today:

Earlier today, when installing Sql Server 2016, ran into this interesting error:

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase

Looks like JRE (Java Runtime Engine) is needed for Polybase application to run. So, lets get it from Oracle website here. Download from the JRE section (see image below).

In the new page, “Accept License Agreement” & download the version pertinent to your environment.

After installing JRE, the checks ran successfully for install.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Reporting Services : SSRS : How to send reporting errors to client (or web page) – EnableRemoteErrors

Quick one day:

Lately, while working on SSRS, we’ve uncovered some tidbits that have been helping us identify the root cause. Here is one of them:

In our environment, we pull SSRS reports from a client application (web page); For the most part, things run smooth. On occasion, when we run into issues, we need to be able to pull up the underlying error message. In SSRS, by default, error reporting back to calling application (client webpage) is disabled. But it is easy to enable it as and when needed and then turn it off, once done.

Go to SSMS and connect to Reporting Service. Right click on the root and go to properties. Under Advanced tab, under Security section, look for EnableRemoteErrors and set the value as True from the drop down. This sends error back to the client, so we can see what is going on. See the image below for clarity.

 

Read Full Post »