Overcome 1,000 items limit for IN clause in Oracle with SSRS

Overcome 1,000 items limit for IN clause in Oracle with SSRS In Oracle, there is a 1,000 items limit when using the IN clause. In order to overcome this, we can use table JOIN, having the list of items in a temp table or sort. But what happens when the list is for SSRS parameter? We can use a Common Table Expression (CTE) to solve this.

The following code is specific to Oracle, but it can be written for SQL Server too with tweak. This is just to show the idea.

WITH cteTempTbl AS (
	SELECT regexp_substr(txt,'[^,]+',1,level) AS ITM
	FROM (SELECT :prmSSRS AS txt FROM DUAL)
	CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (txt, '[^,]'))+1
)

The above code takes the list of items, comma separated (that's how SSRS pass the values) from the parameter, prmSSRS, and create a temp table (cteTempTbl).

To use this CTE, we can do a couple of things.

• Using JOIN

      SELECT A.*
      FROM TABLE A
      INNER JOIN cteTempTbl t ON t.ITM = A.ITM
      

• Using EXISTS (potentially better performance)

      SELECT A.*
      FROM TABLE A
      WHERE
      	EXISTS (SELECT 1 FROM cteTempTbl t WHERE t.ITM = A.ITM)
      

With the example above, we should be able to pass any number of items in the SSRS parameter dropdown list into the SQL query.

Report Elapsed Time for SSRS report

1. Add a new report variable named thisTime with value of =DateTime.Now
2. Add the following code snippet to the Report Code section:

   Public Dim execText As String

   ' for this function to work,
   ' the following Report Variable needs to be defined and set.
   ' thisTime =DateTime.Now

   Public Function LapseTime(execTime as Date, sysTime as Microsoft.ReportingServices.ReportProcessing.OnDemandReportObjectModel.Variable) As String

       Dim ElapsedMins As Integer 
       Dim ElapsedSecs As Integer 

       Dim currTime As System.DateTime = sysTime.Value

       If (ExecText = "") Then
           'Calculate execution time here
           ElapsedMins = DateDiff(DateInterval.Minute, execTime , currTime) mod 60 
           ElapsedSecs = DateDiff(DateInterval.Second, execTime , currTime) mod 60

           execText = ElapsedMins.ToString() & "m " & ElapsedSecs.ToString() & "s"
           'execText = ElapsedMins.ToString() & ":" & Right("0" & ElapsedSecs.ToString(), 2)
       End If

       Return execText

   End Function

3. To use it, create a new textbox and enter the following in the Expression:

   =Code.LapseTime(Globals!ExecutionTime,Variables!thisTime)

4. Done!

SQL Developer Dropping Connections – Solved

The solution is to add this following line:

AddVMOption -Doracle.net.disableOob=true

to the sqldeveloper.conf file.

The root cause is explained in the following article.
Brendan Tierney – Oralytics Blog: SQL Developer is dropping connections

Filtering Empty Values in PowerShell

This information is from Jeffery Hicks’s The Lonely Adminstrator website. All credits go to the him and all the blog contributors. I added here so I can remember the tips for myself.

The posted tip used an example where you wanted to find processes where the company name is defined. The way suggested in the tip, and a technique I see often goes something like this:

PS C:\> get-process | where {$_.Company -ne $Null} | Sort Company| Select Name,ID,Company

While it mostly works, this is a better PowerShell approach, in my opinion.

PS C:\> get-process | where {$_.Company} | Sort Company| Select Name,ID,Company”

When I run the first technique, I still got a blank company name. The tip offers a work around for this situation like this:

PS C:\> get-process | where {$_.Company -ne $Null -AND $_.company -ne ”} | Sort Company| Select Name,ID,Company

This gives the same result as my suggested approach. My approach uses Where-Object to say, if the Company property exists, pass on the object. If you wanted to find processes without a company name, then use the -NOT operator.

PS C:\> get-process | where {-Not $_.Company}

I use a similar technique to filter out blank lines in text files.

get-content computers.txt | where {$_} …

While we’re on the subject, a related filtering technique I often see involves boolean properties. You don’t have to do this:

PS C:\> dir | where {$_.PsIsContainer -eq $True}

PsIsContainer is a boolean value, so let Where-Object simply evaluate it:

PS C:\> dir | where {$_.PsIsContainer}

As above, use -Not to get the inverse. Don’t feel you need to explicitly evaluate properties in a Where-Object expression. I see this is a VBScript transition symptom that I hope you can break.

SQL Server Management Studio 2012 Color Theme Support

This information is a summarization from Brad Gearon’s blog (blog doesn't exist any longer, hence no link). All credits go to the him and all the blog contributors. I added here so I can remember the steps for myself.

These are the things needed to support color theme in SQL Server Management Studio 2012.

• Visual Studio 2012 Color Theme Editor(http://visualstudiogallery.msdn.microsoft.com/366ad100-0003-4c9a-81a8-337d4e7ace05)
  If the 2012 version doesn’t work, use the 2010 version.
• Download a theme from (http://studiostyl.es/)

Putting it together (this is from Arthur from the Brad’s blog. Thanks Arthur!)…

1. Install the 2012 Color Theme Editor.
2. Find the files somehwere in “C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\10.0\Extensions\” .. for me, it was in “\shvn3avp.com” for the current version of the plugin.
3. Copy those files to a new folder: “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Extensions\VSTheme”
4. Now extract the extension.vsixmanifest file from Brad’s vstheme.zip file and replace the one in “…ManagementStudio\Extensions\VSTheme”.
   
   Note: The important difference in the vsixmanifest files is the lines:

ssms
instead of visual studio product numbers

5. Start SQL Server Management Studio and enjoy the colors.

Keep Column Headers Visible in SSRS

The information below is a duplication from article written by Ginger Keys Daniel posted in SQLServerCentral site (great SQL resource). It's here so I can have a record incase the original posting is removed. All credits belong to Ginger Keys Daniel. Thank you Ginger.

---

When scrolling through the pages of a SQL Server Reporting Services (SSRS) report, it is very useful to be able to see the column headers throughout the report. Let’s say you have successfully created an SSRS report using Visual Studio, and everything looks wonderful…except the headers on your columns disappear when you scroll down the page. You have even set the properties of your Tablix to “Keep Headers Visible While Scrolling”, but it still doesn’t work! Trying to keep the column headings visible while you scroll down the page of your SSRS report can be a frustrating endeavor. The following steps will demonstrate how to make it work.

Adjust Tablix Properties In Visual Studio, make sure you are on the Design tab of your report. Right click on the upper left corner of your Tablix to open the Tablix Properties.

When the Properties dialog box opens, un-check the Keep header visible while scrolling checkboxes for both Row Headers and Column Headers. This doesn’t seem logical, but trust me.

Go to Advanced Mode for the Grouping Section. You can do this in the Design tab of your report, click on drop down arrow on the far right side of the Grouping Section and select Advanced Mode.

A Static property element will show above your Row Groups as shown below. Click on the top Static cell which represents the first column header of the report. A properties box will open on the right side of the page for this Static element.

Change Tablix Member properties for the Static element as shown

• Fixed Data = True
• Keep With Group = After
• Repeat On New Page = True

Preview and Run your Report

Click on the Preview tab on your report, save it, and deploy the report to Reporting Services. Below you can see the report as shown in SSRS browser.

As you can see below, the page header disappears when you scroll down, but the column headers remain visible. (note: You may need to change the background color of the column headers, else the text will run over one another when scrolling)

Conclusion

Sometimes when creating a report for SQL Server Reporting Services, you want the column headers to remain visible while you scroll down the report page. Adjusting the Tablix properties isn’t as straightforward as it should be, and can be frustrating to figure out. Following the steps above will keep your column headers visible in your reports.

Disable Automatic Maintenance in Windows Server 2012 R2

Automatic Maintenance in Windows Server 2012 R2 is very resource intensive process. Even though it might be scheduled to run in the middle of the night, the process may continues running throughout the day. This can severely impact the performance of the system.

The following instructions will disable the Automatic Maintenance in Windows Server 2012 R2. I found the instructions from the post by Lars Jørgensen and this blog.

Disclaimer: Not sure if disabling the Automatic Maintenance will disable Windows Updates too. Apply the steps at YOUR OWN RISK and PERIL.

1. You will need the PsExec command from here.
2. Open a command prompt in Administrator mode.
3. Run this command (if you are on the local server, you can skip the \\SERVERNAME parameter):

psexec \\SERVERNAME -s schtasks /change /tn “\Microsoft\Windows\TaskScheduler\Maintenance Configurator” /DISABLE



4. You will receive a SUCCESS notification from the command ran above if all went well.


5. Check it in your Task Scheduler. It should look like below:
   


6. Done

That should reduce some server performance headache. Good Luck!!!