Unexpected results in an SSRS report when using Oracle OLEDB driver and OPENQUERY

Here’s a pretty specific situation we ran across recently – a comment in our OPENQUERY text was causing the results of our query to be completely different than expected.

The T-SQL query we’d written in SSMS that fetched data from an Oracle linked server was running in about 30 seconds, but when we put the query directly into an SSRS report, it would never finish. We were stumped, since we were executing the exact same query in both SSMS and SSRS, but SSRS appeared to hang when executing it. We trimmed down our original query (a few CTEs containing OPENQUERY, joined to a handful of local tables) until we found the single CTE that was doing it. Executing that query in SSMS (or executing the OPENQUERY portion directly in PL/SQL Developer) returned results in about two seconds – however, putting that same portion on a report (even using the report wizard and with no special formatting) resulted in a report that never finished running, whether in the report designer or deployed to our SSRS site.

As it turns out, the SSRS engine (it occurred in both the BIDS/SSDT and once the report was deployed) was consolidating the OPENQUERY text to a single line when executing it, rather than respecting the line breaks in our query text. As a result, the “–” that preceded a comment mid-way through the query was actually commenting out the entire rest of the query, a la SQL injection style. This resulted in a completely ignored WHERE clause on the query, and different results – effectively, SSRS was taking this query:

SELECT *
  FROM OPENQUERY(OracleServer,
                 'SELECT *
                    FROM SomeSchema.SomeTable -- Note the comment here
                   WHERE ID = 50')

and executing it like this:

SELECT *
  FROM OPENQUERY(OracleServer,
                 'SELECT * FROM SomeSchema.SomeTable -- Note the comment here WHERE ID = 50')

Since our query wasn’t returning results, but just hanging for 30 minutes before we gave up, we asked our Oracle admin to watch the execution plan after seeing this Stackoverflow question (and the comments on the accepted answer) that suggested that they were seeing SSRS generate a complete different execution plan for the query (by comparing the plan generated when it was executed from both SSMS and SSRS, I’d hoped the difference would be obvious – it was). This didn’t make any sense at all, since the same query should be hitting SQL Server, and so exactly the same OPENQUERY pass-through query hitting Oracle, but the Oracle DBA confirmed that half of our Oracle query was commented out when it came from SSRS. This meant that our 1000 row expected resultset had become a 1.5 billion row dump of the entire table, which would explain why we were seeing the delay on our report!

This didn’t happen in SSMS, and it didn’t happen when previewing the dataset itself in SSRS, but only occurred when previewing the actual report (this is the only case where SSRS submits a modified version of the query to the SQL engine). Removing the comment from the query resolved the issue, as did switching the comment syntax to /* */ from double-dash.

This occurs both in SQL Server Data Tools (based on VS2010) and BIDS 2008 R2, which is what we had handy to test. Also, it appears to only apply to the Oracle OLEDB driver – creating an Oracle linked server via either ODBC, as well a SQL OLEDB linked server, didn’t exhibit the same issue, and the final report results were filtered as expected.

Steps to reproduce the issue:

1. Create a linked server using the Oracle OLEDB driver

2. In SSMS, create a query that accesses the linked server using OPENQUERY with a simple WHERE clause inside

For example, we had something like the following:

SELECT *
  FROM OPENQUERY(OracleServer,
                 'SELECT *
                    FROM SomeSchema.SomeTable
                   WHERE ID = 50')

3. Execute your query and make note of the number of rows it returns

4. Modify your query by adding a short comment at the end of your FROM line – this should not impact the query results at all

SELECT *
  FROM OPENQUERY(OracleServer,
                 'SELECT *
                    FROM SomeSchema.SomeTable -- Note the comment here
                   WHERE ID = 50')

5. Execute your query again – the row count should be unchanged from step 3

6. In BIDS or SSDT, create a new report, either manually or using the wizard, providing your modified query from step 4 as the dataset

7. Once your report is created, view the dataset properties and preview the query results – note that they match the expected row count

To do this, right-click on your dataset in the “Report Data” panel, select “Dataset properties”. When it opens to the Properties window, select “Query designer…” near the bottom, and then click the exclamation point in the toolbar to execute your query and preview the results. You’ll see your filtered result set, as expected. When you’re done, close this window and go back to your report.

8. Preview your report and notice that the WHERE clause is ignored – all rows from your table are displayed

Summary, and take-away

I’m only using the placement of the comment and ignoring the WHERE clause as an example – the comment could be anywhere in the query, and could even result in an query with invalid syntax that refuses to execute at all.

I hope this explanation helps you avoid the two days of troubleshooting we did to get to this point and find the cause! I’m unclear on why the driver behaves this way, or if it’s an SSRS issue specifically (I suspect it is, since it doesn’t occur in SSMS with the exact same query). If anybody can point me to an open Connect item, I’d be happy to vote for it, but until then, I’m making the effort to migrate to using /* */ comment syntax everywhere – not only is it more clear to readers and flexible for in-line comments, it doesn’t break OPENQUERY functionality (and that’s reason enough for me).

Error creating emailed report subscriptions in Sharepoint-integrated SSRS

Anybody who has the rights to run a report in SSRS can set up a subscription, delivering the report to their own email address. However, if you try to deliver the report as an email to somebody else, you might be greeted with this unclear error message:
Reporting Services - other users email address
It reads:

A subscription delivery error has occurred. (rsDeliveryError)
One of the extension parameters is not valid for the
following reason: The account you are using does not
have administrator privileges. A subscription cannot
be created for bob.thompson@mydomain.com.
(rsInvalidExtensionParameter)

I was setting up a subscription in our Sharepoint-integrated SQL Reporting Services site, and was attending to send the report to our shared group email address. I could set it up to deliver to me and I could forward it, but it wasn’t letting me send the email to anybody else’s email address, either inside or outside the organization.

Solution: Grant your user “Create Alerts” and “Manage Alerts” permission

I received this error because I lacked the “Create Alerts” and “Manage Alerts” on the report in question (or folder, or higher up). The error says you need to be an administrator, but doesn’t really tell you what’s wrong with your request – it’s really complaining that you’re delivering an alert to somebody else. Granting those rights to your user account (for that report, or to an object above it so they propagate down to that report) allows you to send the report to any email address you want.

I hope this helps!

Write permission error when inserting over linked server

I spent some time troubleshoot permissions over a linked server recently before finding out the the cause of my error wasn’t permissions-related at all. I was attempting to perform an insert on a remote table, and was getting the following error:

Msg 7344, Level 16, State 1, Line 2
The OLE DB provider “SQLNCLI10” for linked server “RemoteServer” could not INSERT INTO table “[RemoteServer].[RemoteDB].[dbo].[IdentInsertTest]” because of column “ID”. The user did not have permission to write to the column.

After some time attempting to isolate the missing permissions, I realized that it was actually a disguised error message. I was trying to insert a value into an identity column, but rather than the standard error message I expected to see in that case, I got a generic “You don’t have permission” message, leading to some wasted time troubleshooting.

To recreate the issue, you can follow these steps:

-- Create a test table
CREATE TABLE IdentInsertTest (
	ID INT IDENTITY(1,1),
	SomeValue VARCHAR(10)
)

-- This insert will succeed
INSERT INTO IdentInsertTest (SomeValue)
SELECT 'Some Value'

-- Will fail with IDENTITY_INSERT error
INSERT INTO IdentInsertTest (ID, SomeValue)
SELECT 10, 'Some Value'

The second statement will fail with the standard error message:

Cannot insert explicit value for identity column in table ‘IdentInsertTest’ when IDENTITY_INSERT is set to OFF.

Now, connect to another server and set up a linked server to the other instance, and then try these statements again:

-- This remote insert will succeed
INSERT INTO LinkedServer.RemoteDB.dbo.IdentInsertTest (SomeValue)
SELECT 'Some Value'

-- Will fail with a permissions error
INSERT INTO LinkedServer.RemoteDB.dbo.IdentInsertTest (ID, SomeValue)
SELECT 10, 'Some Value'

If I’d realized what I was doing, it would have saved me some troubleshooting time! The moral here is that if your statement fails over a linked server, ensure your user account is set up correctly and then test it locally – you may get a more accurate error message!

Identifying row in SQL view (or table) with a calculation error

Computed columns can be a great tool if you want to add a value to a table that’s dependent on other columns in the table, and you don’t want to maintain it on its own every time the source columns change. In a view, these can provide a single, consistent calculation to the end users – say, for a

-- Set up a view with the same columns and calculation errors in it
-- The first calculation will always work
-- The second gives a divide by zero error on every 10th row
CREATE VIEW SourceObject AS
SELECT object_id AS KeyColumn1,
       column_id as KeyColumn2,
       object_id - column_id as CalcColumn1,
       (object_id - (column_id % 10)) as CalcColumn2
  FROM msdb.sys.columns

Now that it’s set up, we can try selecting all the rows from the view we just created, and we’ll see about 100 rows output, and then the query will stop with a “Divide by zero” error:

SELECT * FROM SourceObject

The calculation in this query is pretty straightforward, and you can see which rows are causing a problem (where column_id is divisible by 10), but what if it was more complicated? The problem is that SQL doesn’t display the row that had a problem – it stops on the row before the problem, so finding the row with the error is bit more difficult. If there were multiple columns involved in the calculation, or different combinations of values that could result in an error? Tracking down the rows causing an error can be difficult – you have to find all the possible conditions that could cause an error, and then query for each of them.

This script will allow you to find all the rows in a view with a calculation error, all at once. It uses a cursor to pull the rows from the view one at a time, test the calculation, and then write any errors it finds to a table where you can see the rows that are causing problems. Using a CURSOR generally isn’t ideal, but in this case, it’s the only way to react to a bad calculation on a row-by-row basis and deal with it.

The script can use two key values from your view – they’re called KeyColumn1 and KeyColumn2 – and you can modify the script to name them whatever you want, or just a single a value if that makes more sense in your scenario. It also supports two computed columns – CalcColumn1 and 2 – though again, it could be changed to just check a single column.

 -- Set up variables
DECLARE @KeyColumn1 INT,
		@KeyColumn2 INT,
		@CalcColumn1 INT,
		@CalcColumn2 INT
		
DECLARE @CurrentRow BIGINT
	SET @CurrentRow = 1

-- Set up a place to hold key values for rows that work  
  SELECT TOP 0 KeyColumn1, KeyColumn2
    INTO #WorkingRows
    FROM SourceObject

-- Set up a place to hold errors for rows that don't work    
CREATE TABLE #ErrorRows (
	RowNumber BIGINT,
	KeyColumn1 INT,
	KeyColumn2 INT,
	[ERROR_NUMBER] INT,
	[ERROR_MESSAGE] nvarchar(4000)
)    

-- Begin loop to look through rows in the view 
  DECLARE cur INSENSITIVE CURSOR FOR
  SELECT KeyColumn1, KeyColumn2
  FROM SourceObject
  ORDER BY KeyColumn1, KeyColumn2
  
  OPEN cur
  
  FETCH NEXT FROM cur
  INTO @KeyColumn1, @KeyColumn2
	
  WHILE @@FETCH_STATUS = 0
  BEGIN
  
	BEGIN TRY

		-- Try to select the calculated columns	
		-- If there's an error, it will jump to the CATCH block
		SELECT @CalcColumn1 = CalcColumn1,
				@CalcColumn2 = CalcColumn2
		  FROM SourceObject
		 WHERE KeyColumn1 = @KeyColumn1
		   AND KeyColumn2 = @KeyColumn2
		   
		-- This lookup succeeded
		INSERT INTO #WorkingRows
		SELECT @KeyColumn1, @KeyColumn2
		
	END TRY
	BEGIN CATCH
		
		-- The lookup failed - save details
		INSERT INTO #ErrorRows
		SELECT @CurrentRow,
				@KeyColumn1,
				@KeyColumn2,
				ERROR_NUMBER(),
				ERROR_MESSAGE()			
		
	END CATCH
	
	SET @CurrentRow = @CurrentRow + 1
	    
	FETCH NEXT FROM cur
	INTO @KeyColumn1, @KeyColumn2
	    
  END
  
  -- Show the key columns of rows with errors
  SELECT * FROM #ErrorRows
  
  -- Show the key columns of working rows
  SELECT * FROM #WorkingRows
  
  -- Clean things up
  close cur
  deallocate cur
  drop table #ErrorRows
  drop table #workingrows

At the end, you’ll have two tables with results in them – #ErrorRows, which contains the key values for rows with errors in them, as well as details about the error message, and #WorkingRows, which contains the key values for all of the working rows from the view.

Note: I could just as easily set up a table with a computed column in it that causes the same problem You’d be unable to select the entire table without an error, and hunting down the row with an error is painful. The script to find the error is the same, but here’s an example of a table that has a computed column with this problem:

-- Set up table with a list of numbers in it
SELECT object_id AS KeyColumn1,
		RANK() OVER (PARTITION BY 0
					 ORDER BY NEWID()) as KeyColumn2
  INTO SourceObject
  FROM msdb.sys.columns
  
-- Add two calculations to the table
-- The first will always work
-- The second will give a "Divide by zero" every 100 rows
  ALTER TABLE SourceObject ADD CalcColumn1 as (KeyColumn1 - KeyColumn2)
  ALTER TABLE SourceObject ADD CalcColumn2 as (KeyColumn1 / (KeyColumn2 % 100))

-- Note that you can't add a persisted computed column to a table
-- if there's a calculation error in any of the rows, so this
-- command will fail with a "Divide by zero" error
  ALTER TABLE SourceObject ADD CalcColumn3 as (KeyColumn1 / (KeyColumn2 % 100)) PERSISTED

Identifying SQL Server 2005 Enterprise Edition features in use

I recently stumbled across a great system view, sys.dm_db_persisted_sku_features, which identifies any enterprise features in use in the current database, which would prevent you from moving this database to a Standard Edition instance. Unfortunately, it appears in SQL 2008, and I wanted to run this check on a SQL 2005 system.

There are a number of server-level features of SQL 2005 that require Enterprise Edition, but only two database-level features – partitioning and the VarDecimal storage format. Both are easy to check for, so I put together this quick query to do it:

select * from
   (-- VarDecimal Storage Format
    select case
             when max(objectproperty(object_id, N'TableHasVarDecimalStorageFormat')) = 0
               then ''
             else 'VarDecimal Storage'
           end as feature_name
      from sys.objects
    UNION ALL
    -- Partitioning
    select case
             when max(partition_number) > 1
               then 'Partitioning'
             else ''
           end
      from sys.partitions
) t
where feature_name <> ''

On a SQL 2005 server, this query will serve the same purpose that sys.dm_db_persisted_sku_features does on SQL 2008/2012 servers.