Thursday, August 30, 2018

SSRS: Remove Duplicates in Parameter Drop-down

While SharePoint was being queries from a SSRS report, GroupBy did not work in the CAML query in the DataSet definition of the SSRS report.

 One workaround was to use a global VB function inside the report, which removes duplicate parameter values in a parameter dropdown.

https://social.technet.microsoft.com/wiki/contents/articles/36632.ssrs-remove-duplicate-filter-values-from-parameter-drop-down-using-vb-code.aspx


Wednesday, August 29, 2018

Insert Stored Procedure Result into Table

The following does not work unfortunately unless table1 already exists with columns with matching field names, data types and field order.
INSERT INTO table1
exec sp_GetProducts @CategoryID=12
Stored procedures that returns tabular results in SQL Server do not allow saving the results in a new table like we would expect (The receiving table must exist with all columns' data types and fields names ahead of time for the query to work).
The workaround is to use either OpenRowset or by creating a new function.
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT *
INTO table1
FROM OPENROWSET (
  'sqlncli',
  'server=(local)\SQL12;trusted_connection=yes',
  'exec Northwind.dbo.sp_GetProducts @CategoryID=12'
);


When using a user-defined function instead, we are basically re-writing the stored procedure in user-defined function.
CREATE FUNCTION GetProductsByCategory
(
    @CategoryID int
)
RETURNS TABLE
AS
    RETURN
        select * from dbo.Products where CategoryID=@CategoryID
GO

SELECT *
INTO table1
FROM GetProductsByCategory(12);
GO

Tuesday, August 21, 2018

Send email notification when free disk space is low

Powershell script that detects local disk drives with less free space than specified value and sends email notifications (I can't recall where I got the following script. It was probably written by a SQL Server admin).

# Get SMTP server info
$smtp=new-object Net.Mail.SmtpClient("myEmailServer.myDomain.com")

# Set thresholds in GB for C: drive and other drives
$driveCthreshold=10
$threshold=10

# Replace settings below with your e-mails
$emailFrom="serverToMinitor@myDomain.com"
$emailTo="myName@myDomain.com"

# -----------------------------------------------------------------------------

# Get server name
$hostname=Get-WMIObject Win32_ComputerSystem | Select-Object -ExpandProperty name

# Get all drives with free space less than a threshold, while excluding System Volumes
$Results = Get-WmiObject -Class Win32_Volume -Filter "SystemVolume='False' AND DriveType=3" | Where-Object {($_.FreeSpace/1GB –lt  $driveCthreshold –and $_.DriveLetter -eq "C:") –or ($_.FreeSpace/1GB –lt  $threshold –and $_.DriveLetter -ne "C:" )}

If ( ($Results | measure).Count -gt 0 ) 
{
    ForEach ($Result In $Results)
    {
        $drive = $Result.DriveLetter
        $space = $Result.FreeSpace
        $thresh = if($drive -eq 'C:'){$driveCthreshold} else {$threshold}

        # Send e-mail if the free space is less than threshold parameter 
        $smtp.Send(
           $emailFrom, 
           $emailTo, 
           # E-mail subject
           "Disk $drive on $hostname has less than $thresh GB of free space left ",
           # E-mail body 
           ("{0:N0}" -f [math]::truncate($space/1MB))+" MB"
        )
    }
}

Monday, August 20, 2018

ASP.NET MVC - Handle binary or byte arrays as in file download

In ASP.NET MVC, downloading a file over http has been simplified and is user-friendly. The following code streams byte arrays to browser in two ways. GetFile would prompt for user dialog for choice as to Open or Save. OpenFile would try to stream the byte array content and render it in browser directly (pdf, etc). The sample code assumes that db.Binaries entity already has properties, such as BinaryData, MimeType and FileName, etc.
[HttpGet]
pulic FileContentResult GetFile(int id)
{
  var binary = db.Binaries.Find(id);
  if (binary == null)
  {
    return null;
  }
  byte[] binaryData = binary.BinaryData;
  string mimeType = binary.MimeType;
  string fileName = binary.FileName;

  return File(binaryData, mimeType, fileName);
}

[HttpGet]
public ActionResult OpenFile(int id)
{
  var binary = db.Binaries.Find(id);
  if (binary == null)
  {
    return null;
  }
  var contentDisposition = new System.Net.Mime.ContentDisposition
  {
    FileName = binary.FileName, Inline = false
  };
  Response.AddHeader("Content-Disposition", "inline; filename=" + binary.FileName);
  
  return File(binary.BinaryData, binary.MimeType);
}

Thursday, August 16, 2018

Maximum Request Length Exceeded - ASP.NET

When uploading or downloading a large amount of content, ASP.NET may need some request length adjustments in the web.config file. Example below would allow a request to continue for maximum of 3600 seconds (1 hour) and 1 GB of content.

<configuration>
  ...
  <system.web>
    <httpRuntime targetFramework="4.5" executionTimeout="3600" maxRequestLength="1048576" />
  </system.web>
  ...
  <system.webServer>
    <security>
      <requestFiltering>
        <requestLimits maxAllowedContentLength="1073741824" /> <!-- 1 GB in bytes -->
      </requestFiltering>
    </security>
  </system.webServer>
  ...
</configuration>


Tuesday, August 7, 2018

SQL Server Database and Log File Details

Quick way to retrieve a database and its log file size, etc...

/*-- Get all databases' mdf and ldf info, including logical names --*/
SELECT * FROM SYS.MASTER_FILES

/*-- Get mdf info (more detail) -- */
SELECT * FROM SYS.DATABASES

/*-- Get mdf info (less detail)  --*/
SELECT * FROM SYS.SYSDATABASES

/*-- Get database info -- */
EXEC sp_helpdb

/*-- Get database info (mdf and ldf), including logical names --*/
EXEC sp_helpdb @dbname = N'Northwind'


Tuesday, July 31, 2018

Get "exec sp_who2" into a table

Running sp_who2 will quickly display all connections to a SQL Server instance. Below is an enhancement to sp_who2 that allows database-specific connections.


DECLARE @DB_NAME varchar(1000)
SELECT @DB_NAME = 'Northwind_DB'

DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX), 
    SPID_1 INT,
    REQUESTID INT
)

INSERT INTO @AllConnections EXEC sp_who2
SELECT * FROM @AllConnections WHERE DBName = @DB_Name
ORDER BY HostName