Ryan McCauley – Page 7

Working with bit masks in T-SQL

Decoding bitwise masks can be a bit confusing, so I wanted to share a couple of T-SQL functions I’ve created to make them easier to deal with. If you’d like to read more about bitmasks and applying them in T-SQL, you can read about it at SQL Fool: T-SQL Bitwise Operators.

The first will return the value in a given bit position of an integer – it accepts two parameters (the lookup value and the bit position) and returns a bit for the value in that position. Note that it starts with position zero, so make sure you’re counting correctly:

CREATE FUNCTION dbo.BitwisePosition(
	@value BIGINT,
	@PositionNumber INT
) RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN

	DECLARE @Result BIT
	DECLARE @Mask BIGINT
		SET @Mask = POWER(2,@PositionNumber)

	SET @Result = (CASE @value & @Mask WHEN @Mask then 1 else 0 end)

	RETURN @Result

END
GO
The second function returns a bit (true/false) based on whether a provided bitmask applies to a reference value:
CREATE FUNCTION dbo.BitwiseMask(
	@value BIGINT,
	@Mask BIGINT
) RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN

	DECLARE @Result BIT

	SET @Result = (CASE @value & @Mask WHEN @Mask then 1 else 0 end)

	RETURN @Result

END
GO
Don’t forget to grant them permissions:
GRANT EXECUTE ON dbo.BitwiseMask TO PUBLIC
GRANT EXECUTE ON dbo.BitwisePosition TO PUBLIC
To use these functions, you’d call them as in these examples:
-- Value:   1110001000
-- Position 9876543210
-- Checkpoing position 7, 4, and 0 should return 1, 0, 0
 select dbo.bitwiseposition(904, 7),
		dbo.bitwiseposition(904, 4),
		dbo.bitwiseposition(904, 0)

-- Value:   1110001000 = 904
-- Bitmask: 0010001000 = 136
-- Will return true since mask "fits inside" value
select dbo.bitwisemask(904, 136)

-- Value:   1110001000 = 904
-- Bitmask: 0010001001 = 137
-- false since mask has a bit "outside" value
select dbo.bitwisemask(904, 137)
I hope you find them helpful!

Oracle Goldengate REPLICAT frozen on “Starting”

We use Oracle Goldengate (expensive and probably overkill for Oracle->MSSQL, but good at what it does) to replicate data from an Oracle database into a SQL Server. However, I got an alert the other day that replication had stopped, and when I checked the status of replication, all the REPs we had set up were in status “Starting…”, but none we actually doing anything.

Attempting to stop them got the following error:

GGSCI (GGSERVER) 68&gt; stop rep MYREP
Sending STOP request to REPLICAT MYREP ...
ERROR: opening port for REPLICAT MYREP (TCP/IP error: Connection refused).

Stopping/Starting the manager service or rebooting the PC didn’t help either – they still said “Starting” and were unresponsive. Even stranger, deleting and recreating the REP gave the same result – before I even attempted to start the REP for the first time, it said “Starting”, and an attempt to start it gave me “Process is starting up – try again later”.

The cause was the REP process status file, located in the DIRPCS folder under the Goldengate root – there should be a file for each REP that’s currently running giving details about the status. When a REP stops, this file is deleted. Since all of the current REPs weren’t doing anything (they were all sitting at the end of the previous trail file), they should have been stopped. I deleted the PCR files for the affected REP streams, and then manager reporting “STOPPED” – at that point, I was able to start up each REP without issue.

I’m not sure how they got that way, but once started again, they all worked without issue. I hope this saves you the troubleshooting time of hunting down these files!

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:

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!