Thoughts From My Life

Advice from someone who does not know everything.

Category: Techie Stuff (page 1 of 2)

Creating Data-Driven SSRS Reports in SQL Server Standard Edition

I recently had to resolve this issue. I was running SQL Server Standard Edition and needed to have an SSRS subscription that behaved like a data-driven subscription. Data driven subscriptions are available in Enterprise Edition SQL Server, but not Standard.

What is a Data-Driven Subscription

This is a very useful tool in the Enterprise Edition. A regular subscription is simply a scheduled time when a report will run and can be sent to email addresses or saved to a network share. The individual subscription will only run once and utilizes the same report parameters and recipient information.

A data-driven subscription lets you run a database query that returns no results or a set of results. The report is then run as many times as there are row results in the original database query. This allows you to 2 important things:

  • Not run the report if it does not have any meaningful results. e.g. Don’t send anything out.
  • Run multiple reports at once with customized parameters (who it is sent to, the subject of the email, the parameters used to generate the report, etc). This allows you to scale subscriptions really easily without having to go in and create a subscription for each recipient manually every time.

But I Do Not Have SQL Server Enterprise Edition

This means you do not have the data-driven feature when creating subscriptions. If you are doing a lot of data-driven report subscriptions, it may be worth your while to pay for that license as it will be less development time and more intuitive for users to understand what is happening than what I will describe below.

So we will have to come up with an alternative method to achieve something similar.

The Design

  • Publish an SSRS report if it is not published already.
  • Create a regular subscription using the SSRS interface.
    • Set any parameter values that are constant.
    • You may set the other parameters, but they will be overwritten anyways.
    • Set the schedule to have a stop date in the past, this makes sure it is active, but will not run on its own.
  • Create a stored procedure to run our report.
    • Code example in detail below.
    • Needs to run a query that provides how many times you want the report to run and what the override parameters should be.
    • Will loop over the results to:
      • Override the parameters stored in the table.
      • Execute the report.
      • Wait until the report is finished.
      • Continue.
  • Create a SQL Agent job to run the stored procedure on a schedule.

A Note Waiting For Each Report To Finish

Calling EXEC ReportServer.dbo.AddEvent does not block the script until the report is finished. It merely adds the report to the queue to be processed. If the report takes a while to run, this script may overwrite the parameters and settings being used. So it is important to have a loop monitoring the Events table to make sure the report has completed before continuing.

Stored Procedure Example

This example will determine how many reports need to be created and run each report consecutively by waiting for the report to finish before triggering another one.

--Steps to create data-driven like subscription in SQL Server 2016 Standard Edition:
--1. generate a subscription for a single user and note the subscription_id
--2. create stored procedure to update settings and generate reports
--3. schedule a job to run this procedure

-- Notes to Remember
-- Need to use a Job to run this and schedule.  
-- The subscription_id will be generated at the time the report subscription is created
-- The subscription should be set to active with a stop date in the past so it doesn't run on its own if not desired, but is an active report subscription.

IF EXISTS ( SELECT 1 FROM sysobjects WHERE id = object_id('DataDrivenSSRSSubscription_MyExampleReport) AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE dbo.DataDrivenSSRSSubscription_MyExampleReport
END
GO

CREATE PROCEDURE [dbo].[DataDrivenSSRSSubscription_MyExampleReport]
@subscription_id varchar(50),
@parameter_id int

AS
BEGIN
    DECLARE @v_email varchar(256), @v_parameter1 varchar(256)
@settings varchar(4096), @params varchar(4096), @qry varchar(1024), @wait smallint
           
    DECLARE db_cursor CURSOR FORWARD_ONLY READ_ONLY FOR  
SELECT
*
FROM Some_Table
WHERE Some_Table.parameter_id=@parameter_id

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @v_email, @v_parameter1
   
    -- looping through the list
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        -- wait until report has been cleared from the reporting queue
SELECT @wait = COUNT(*) FROM ReportServer.dbo.Event WHERE EventData=@subscription_id
WHILE @wait > 0
BEGIN
WAITFOR DELAY '00:00:01'
SELECT @wait = COUNT(*) FROM ReportServer.dbo.Event WHERE EventData=@subscription_id
END

        -- update parameters before generating report
SET @settings = '<ParameterValues>
  <ParameterValue><Name>TO</Name><Value>'+@v_email+'</Value></ParameterValue>
</ParameterValues>'

SET @params = '<ParameterValues>
  <ParameterValue><Name>Parameter1</Name><Value>'+@v_parameter1+'</Value></ParameterValue>
</ParameterValues>'
UPDATE ReportServer.dbo.Subscriptions
SET extensionsettings = @settings,
parameters = @params
WHERE subscriptionid = @subscription_id

        IF @@error = 0
        BEGIN
            -- run report and send email
EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription',
@EventData=@subscription_id
        END                
        FETCH NEXT FROM db_cursor INTO  @v_email, @v_parameter1
    END  
   
    CLOSE db_cursor  
    DEALLOCATE db_cursor

END    

References

These are some really good references that may be easier to understand. One note though, they do not cover “waiting for each report to finish before moving on to the next report.”

Unable to Download Blocked Gmail Attachment

I ran into an issue recently where I was trying to retrieve an attachment in an email and it was blocked. Gmail blocks the download of attachments it feels are a risk. Files with certain file extensions (like .exe), zip files that contain them, etc. The full listing and help from Google is here: https://support.google.com/mail/answer/6590?hl=en .

The message you will see is:


Anti-virus warning 
– 1 attachment contains a virus or blocked file. Downloading this attachment is disabled.

I was searching for solutions and a lot of them involve asking the sender to send a different way or use a Google Drive folder or equivalent. In my case that would not work as the file was in an email I sent myself.

Eventually I found this article, which describes how to download the original email, open it in Windows Mail, and download the attachment from there. Very simple, free, and convenient to do. I imagine opening the email in a Mac email client would work as well.

XBox Kodi Unable to Open NFS Folder

Problem

I am running Kodi Alpha 3 on my XBox One and on my Surface.  Everything works fine on my Surface, but my XBox One in particular has this issue.

My media is stored a QNAP network attached storage on my local network. 

  • I try to add media by adding a file source. 
  • Add Videos -> Add Video Source -> Browse -> Network File System (NFS)
  • It automatically shows the IP of my NAS. 
  • I select the NAS IP name and it navigates to show me the root list of shared folders (Multimedia, Downloads, Public, etc).  
  • At this point I cannot descend any further into any of the folder names except for Public.  The other ones if I click on them just make an audible UI noise, but nothing happens.
  • I have checked the settings on the Multimedia folder, but they appear to be readonly for guest/everyone access.

On my Windows 10 Surface, the same procedure lets me descend into the Multimedia share and select a folder to index or view a file from.  It is like my XBoxOne is restricted or unable to go any further, but no error message is displayed.

Solution

I first posted about this on the Kodi forums in the article Xbox Kodi Unable to Open NFS Folder.

So my Kodi app on my Surface was working, because from my Surface in Windows Explorer I had logged into the NAS folders using a login/password.  The XBox One Kodi I had not done that (and not sure if you can).  I had to log into my QNAP and comb through the settings.  I had set up access as read only in one spot, but I needed to change a pick list to say “NFS host access” and then set “Access right” enabled.  Once I applied that setting, then I could descend into the folder from Kodi on the XBox One.   Was not able to upload my screenshot here, but here are step by step instructions for my NAS admin page.

1. Connect to your QNAP NAS admin page.
2. Open Control Panel
3. Select Privilege -> Shared Folders. 
4. You will now see a list of folders.
5. In the Actions column, hover over the icon that gives the tooltip that says “Edit Shared Folder Permission” and click it.
6. A window will appear that says “Edit Shared Folder Permission”. 
7. I did have “Guest Access Right” pick list set to “Read Only” here.
8. Change the “Select permission type” pick list to be “NFS host access”.
9. Enable the checkbox for “Access right”.
10. Click Apply.

You should be good to go now.  If you found you needed more steps, please post back here.  But this worked for me. 

I’m running the QNAP TS-231.  Firmware version: 4.3.4.0675 Build 20180810

Cloning Your Hard Drive to Another Hard Drive

A friend of mine the other day had their hard drive approaching death. It was grinding away and had a number of errors occurring. It was time to replace it.

After picking up a hard drive that was almost double the size, he gave it to me as I offered to install it.

There are two main things you need to do when upgrading or replacing your hard drive:

  • Installing the drive.
  • Copy over your data.

Installing the drive is the easy part. Just open up the case, find a spot that is available and the data and power cables can reach the drive from there. Plug everything in, turn on the computer and make sure it can detect the drive.

Continue reading

Recovering Pictures or Files on Flash Memory

I had a scary moment on my most recent trip. My card appeared to fail after I had taken a week’s worth of pictures in Nepal.

My Nikon D70s started flashing “For”, which indicates the compact flash card inside needs to be formatted. I was unable to view the pictures and if I put it into a card reader and tried to access it on Windows, it just wanted to format the card.

I waited a month, until I arrived home, to try and recover the pictures. I tried 8 different programs and I found two that were able to do it.

RescuePro was one of them. They let you download a trial of it and see if it will actually work before you have to buy. It will access the flash card and then scan it. After awhile, it shows you thumbnails and will let you recover a small number of the files for free. If you see your files and think it is worth it, then you can pay the US$40 for a license.

Active File Recovery is the other one that worked. This program is a little more full-featured. It can recover files on hard drives as well. RescuePro was strictly for flash cards.

Anyhow, if you have ever deleted, formatted, corrupted, or had some other failure with files or flash cards. Don’t just erase the card and start from scratch thinking you lost everything. Download a trial copy of RescuePro from the link above and see if you can spot your missing items. It will be well worth the $40 in a lot of cases.

Win32 Application and Logging To The Console

I have been writing demo applications using OpenGL recently in C++. I documented how to set up the environment and create a project in my article OpenGL and Visual Studio Express 2008. I started out by writing the application as a Win32 application. This was a problem though.When you are doing it in a Win32 project instead of a Win32 Console project it makes it very hard to do simple debugging using print statements. If you are like me, I like to scatter prinf or cout statements through different parts of my code so I can trace the progress and examine variables during development. It makes it very easy.If you use these statements in a Win32 project however, this output does not get printed out to the console or anywhere for that matter. It is just eaten and no errors are incurred.

Continue reading

AC3 Encoded Sound Is Too Quiet

I have recently been watching hi definition video downloads on my laptop. Their sound was encoded using AC3 and I found that I was not able to get the sound loud enough on my laptop to watch the movie enjoyably.

The movies were mkv format (otherwise known as Matroska).

Fortunately, I found a very useful AC3 configuration too called the AC3 Filter that allows you to boost the sounds volume and push your speakers a little farther. You can download the program off their site and install it.

Just open it up, adjust the gain or any other settings, restart your media player, and enjoy. The only catch is that you will need to restart your media playing application anytime you change a setting to see its effect.

Awk – Error – Unterminated String

I was frustrated using awk recently. I had created a awk file to do some batch processing of text file information. Anyhow, I had a large text section to create an SQL statement from. It spanned multiple lines and I had used a backslash “\” to indicate the string continued on the next line.

I had a syntax problem however. It was saying I had an unterminated string. I googled for the answer and checked to make sure every line of my text section had a backslash (which it did) and I couldn’t figure it out.

Below is the error I had.

awk: query.awk:2: print "SELECT \awk: query.awk:2:       ^ unterminated string

Anyhow, last straw was to go to everyline that had the backslash at the end and remove any spaces at that came after the backslash (so between the slash and the true end of the line). This fixed my problem.

Fixing Photo Dates and Renaming Files With JHead

I found the most useful picture EXIF tool the other day. It is called JHead. Basically, it allows you to mass change photo files and fix the dates, rename the files based on the dates, and perform other functions.

The reason I needed it was that I was recently on a trip. There were 4 of us. We had 2 Nikon cameras, 2 Canon cameras, and we were all set to the wrong time as we were in another country and messed up our camera settings.

File Naming With Nikon Vs. Canon

One of the big annoyances I found with the Nikon and Canon software is when they import, I couldn’t get them to the same naming convention.

I am used to Nikon’s import where I can set it to use the format YYYY-MM-DD_HH-MM-SS.JPG. This is the full date and time the photo was taken. Because of the order of the numbers, these pictures will sort by name in the right order they were taken. It works great.

Unfortunately, Canon software won’t let you have this same format. Therefore, if you put all the files into the same folder, they won’t sort properly. One exception is if you have Windows XP, you can sort by the actual Date Taken EXIF property. If you are using 3rd party software or an older computer this is useless though.

Renaming Files With JHead

This is an awesome feature. Basically, you just need to download the Windows executable JHead and save it somewhere (preferably where all your photos are stored).

Open up a command prompt (Start -> Run -> type cmd -> Click OK). You should have a black window. Navigate to the folder your pictures are stored in.

Then you can rename them using jhead and the -n switch. Use the JHead instructions to help figure it out.

For example, I renamed my files so they looked like this:

2008-07-29_15-34-23_ng.jpg

This represents the exact date I took the picture and appends my initials in there as well. This is the command to rename the files like that:

jhead -n%Y-%m-%d_%H-%M-%S_ng *.jpg

Fixing the Picture Dates

If your camera had the wrong date set for whatever reason (in another time zone, daylight savings, clock reset), then you can also fix the dates by applying an offset.

JHead has two switches to do this. One is by specifying how many hours it is out and the other is by specifying how many years, months, or days it is out.

Note: Please test this out on a copy of one file before applying it to all of them to see if you get the desired effect.

There are a number of good examples in the JHead instructions as well.

For myself, one camera was out by a month and the other was out by 12 hours so I used the following switches:

Roll forward a month (camera was at May 30th when it should have been June 30th).

jhead -da2008:05:30-2008:06:30 *.jpg

Roll forward 12 hours.

jhead -dt+12 *.jpg

Conclusion

So after adjusting all my times to be correct, I then set the file names for all my pictures and included the initials at the end of who took the photos. It is ten times easier to look through them now and know where they came from.

Keep in mind, a good practice would be to have everyone in a group take a photo at the exact time at the beginning or end of your trip. You could then look at the times for all those photos and if some cameras were offset by a couple minutes, you would be able to adjust them back so the pictures are perfectly aligned.

Escaping The Asterisk (*) in Excel

I had a formula the other day that needed to see if an asterisk – * was present in a cell. So the formula included a comparison for “*” in the syntax. Anyhow, turns out some formulas (not all) will treat the * as a wildcard and match it to any number of letters or numbers. If you are a programmer, like myself, then you are used to escaping letters with a backslash (\). However, Excel does not use this and the proper escape is a tilde (~).

I was a bit surprised how difficult searching the internet for this solution was, so I’m posting it here too. Hopefully I have good enough keywords and explanation that it will be easier for others like me to find.

So my formula that was giving me issues was a SUMIF. It looked like this:

=SUMIF(A1:A500,"=*",B1:B500)

My intention was to add up all the B column values that had a * in the A column, however it was adding up all the B column fields regardless. Once I changed it to the formula below, it worked just fine.=SUMIF(A1:A500,"=~*",B1:B500)

« Older posts