Category: Big Data

Setting your queue name with Microsoft Hive ODBC

When setting up an ODBC connection to a Hive server, you sometimes need to change your queue name – if the default queue you’re assigned doesn’t work for what you need to do, here’s how you change it in the DSN settings. You can use this process to set any other “Server Side Property” (or SSP) that your server requires.

  1. Open your DSN settings window for the data source where you want to set to queue name, and click “Advanced Options…” in the lower left:
The DSN Settings window that shows the common settings. This window is currently showing the default settings for the ODBC driver and your settings may be different.
  1. Click “Server Side Properties” in the lower-left corner of the Advanced Options window:
The “Advanced Settings” window with default settings.
  1. In the Properties window, click “Add..” on the right side to create a new property:
The window where you add new server side properties.
  1. Add any properties required by your Hive instance, including the mapreduce queue name if needed. You may have to ask your administrator what the name of this property is – some servers use “mapreduce.job.queuename” by default, and one server I use has this defined as “mapred.job.queue.name“, so you’ll need to confirm your server’s value. Once you know what it is, create it and click OK, and then it should look like this:
Show the results of the server side property for queue name once it’s set.
  1. That’s it! Go ahead and click “OK” and save the data source name settings, then you should be good to go!

If you have the need to use different queues for different purposes (or any other settings change based on your work), you can create multiple versions of your ODBC DSN and connect to whichever one is appropriate for the work you’re currently doing.

If you’re looking for a Hive driver, I’m showing the Microsoft Hive ODBC driver in this walkthrough and I’ve had a great experience with it (and it’s free – most other drivers at least require you to give your contact information, but this one doesn’t). There are other drivers you can use, and each has a place you can set any SSP that you need for your connection – please check the documentation for your specific driver if you need details.

Enjoy!

Removing expired/unused SSRS subscriptions

SQL Reporting Services doesn’t do a very good job keeping the SQL Agent clean by removing expired or otherwise unusable subscriptions from the job list. To deal with this, we created a script that pulls some details about these old subscriptions, including the report responsible, the last run time and status, and the user who originally scheduled it. If you notice your SQL Agent job list getting excessively long, you can use this query to identify the culprit reports and owners, and then either notify them or remove the old subscriptions manually yourself (run this on the server with your SSRS databases):

  select c.Name as ReportName,
         s.EventType,
         s.Description as SubscriptionDescription,
         s.LastStatus as LastSubscriptionStatus,
         s.LastRunTime SubscriptionLastRunTime,
         case
            when recurrencetype = 1 then 'One Time'
            when recurrencetype = 2 then 'Hourly'
            when recurrencetype = 4 then 'Daily'
            when recurrencetype = 5 then 'Monthly'
            when recurrencetype = 6 then 'Month Week'
            else 'Other'
         end as RecurranceType,
         s.DeliveryExtension,
         u.UserName as SubscriptionSetUpBy,
         s.ModifiedDate as SubscriptionLastModifiedDate
    from [ReportServer].[dbo].[Subscriptions] s
    join [ReportServer].[dbo].[Catalog] c
      on c.ItemID = s.Report_OID
    join [ReportServer].[dbo].[Users] u
      on u.UserID = s.OwnerID
    join [ReportServer].[dbo].[reportschedule] rs
      on c.itemid = rs.reportid
     and s.subscriptionid = rs.subscriptionid
    join [ReportServer].[dbo].[schedule] sch
      on rs.scheduleid = sch.scheduleid
   where s.EventType <> 'RefreshCache'
     and s.LastRunTime < dateadd(m, -3, getdate())
order by c.name

There are a number of similar scripts out there that pull much of this information together, but there wasn’t one that collected all the details we were looking for in one place. From here, you can deal with the subscriptions as you see fit.
Note that you can just remove the old subscriptions by brute force if you’d prefer, and SSRS will clean up the orphaned SQL jobs, but I’ve preferred to review the list and notify users as we’ve never had too much volume to deal with. If you want to just delete them straight away, you can do so here:
DELETE ReportServer.dbo.Subscriptions
WHERE InactiveFlags != 0
	OR LastRunTime < dateadd(m, -3, getdate())

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!