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


ASP.NET MVC Scaffolding Template Files (T4)

The following is where you can find the T4 template files used by ASP.NET MVC Scaffolding:

Visual Studio 2015:
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\Web\Mvc\Scaffolding\Templates\

Visual Studio 2017:
C:\Program Files\dotnet\sdk\NuGetFallbackFolder\microsoft.visualstudio.web.codegenerators.mvc\


Friday, July 27, 2018

Database Drop takes a long time when deleting backup history option is selected in SQL Server

Here's a workaround to speed up database drop. When there are a long history of backup in MSDB database, dropping a database with "Delete backup history" option could take a while.

Update statistics on MSDB tables and create an index for backupset table for [database_name] column. 

/* Update statistics in [msdb] database */
   
USE msdb; 
GO
UPDATE STATISTICS backupfile; 
GO
UPDATE STATISTICS backupmediafamily; 
GO
UPDATE STATISTICS backupmediaset; 
GO
UPDATE STATISTICS backupset; 
GO
UPDATE STATISTICS restorefile; 
GO
UPDATE STATISTICS restorefilegroup; 
GO
UPDATE STATISTICS restorehistory; 
GO


/* Create an index on [backupset] table in [msdb] database for [database_name] column */

Create index IX_backupset_database_name on backupset(database_name); 
GO


Thursday, June 28, 2018

AD Query still returns old user name after user name was changed at domain controller

Symptoms:

Consider the following scenario:

  1. On the domain member computer, an application calls the LsaLookupSids function to translate a security identifier (SID) to a user name.
  2. The user name has been changed on a domain controller.
  3. In this scenario, the LsaLookupSids function may return the old user name instead of the new user name. This behavior may prevent the application from working correctly.


Cause:

The local security authority (LSA) caches the mapping between the SID and the user name in a local cache on the domain member computer. The cached user name is not synchronized with domain controllers. The LSA on the domain member computer first queries the local SID cache. If an existing mapping is already in the local SID cache, the LSA returns the cached user name information instead of querying the domain controllers. This behavior is intended to improve performance.

The cache entries do time out, however chances are that recurring queries by applications keep the existing cache entry alive for the maximum lifetime of the cache entry.


Workaround:

To work around this issue, disable the local SID cache on the domain member computer as follows.

  1. In RegEdit, locate HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
  2. Create a new DWORD directly under "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa", name it LsaLookupCacheMaxSize and assign value 0.
  3. Reboot may not be required.

Note The LsaLookupCacheMaxSize registry entry sets the maximum number of cached mappings that can be saved in the local SID cache. The default maximum number is 128. When the LsaLookupCacheMaxSize registry entry is set to 0, the local SID cache is disabled.


Resources:
https://support.microsoft.com/en-us/help/946358/the-lsalookupsids-function-may-return-the-old-user-name-instead-of-the
https://marclsitinfrablog.wordpress.com/2011/06/25/lsa-lookup-cache/

Wednesday, June 13, 2018

Steps to add VS solution to Visual Studio Team Services

Steps to add an existing Visual Studio solution to a repository on Visual Studio Team Services (or Team Foundation Server).


  1. Install Git on your PC first.
  2. Create a project with Git as version control on your Visual Studio Team Services website.
  3. Get the URL of the new project after it is created,
    i.e., https://developer2201.visualstudio.com/WebApp1/_git/WebApp1
  4. Assuming your Visual Studio solution folder is C:\Dev\VS2017_Eval\WebApp1...
  5. Open command line console. Go to "C:\Dev\VS2017_Eval\WebApp1".
  6. Run command: git init
  7. Run command:
    git remote add origin https://developer2201.visualstudio.com/WebApp1/_git/WebApp1
  8. add ".gitignore" file to the folder. You can download it from github at
    https://github.com/github/gitignore/blob/master/VisualStudio.gitignore
  9. Run command: git add .gitignore
  10. Run command: git commit -m ".gitignore file added"
  11. Run command: git add *
  12. Run command: git commit -m "Initial source codes"
  13. Run command: git push -u origin --all

Step 13 can be done in Visual Studio by using Sync and Push in outgoing commit in Visual Studio, too.


Wednesday, May 23, 2018

SharePoint 2013: Include javascript and css in the MasterPage

<!--SPM:<SharePoint:CssRegistration runat="server" ID="CssRegistration1" Name="&#60;% $SPUrl:~sitecollection/Style Library/style.css %&#62;" After="corev15.css"/>-->

<!--SPM:<SharePoint:ScriptLink runat="server" ID="ScriptLink1" Language="javascript" Name="~sitecollection/Style Library/script.js"/>-->

Thursday, May 17, 2018

Sample Databases (AdventureWorks)

Various SQL Server sample databases including the AdventureWorks2012 sample databases are available at this location.

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

Thursday, May 10, 2018

WinAero - very useful to customize Windows 10 font size, etc

Since the last major Window 10 updates, the old way to customize the icon font size went away. The closest way to make the Windows 10 desktop icon's font size bigger is through using Settings --> Display --> Advanced Custom Scaling. However, this does not yield the desired result, because it could end up increasing the size of fonts as well as icon size and everything else on the screen in proportion.

 I did a quick Google Search and found a utility called WinAero (http://WinAero.com). This tool allows granular customization of specific items, such as icon font and icon font size. On a 14-inch screen laptop with 1366x768 resolution, being able to use 11-point, semi-bold Segoe UI font makes it really nice to use the laptop again.

Monday, April 9, 2018

CentOS 6 - Set Static IP

In order to set static ip on CentOS 6:


  1. Open ifcfg-Auto_eth0 file at /etc/sysconfig/network-scripts folder
    1. sudo vi /etc/sysconfig/network-scripts/ifcfg-Auto_eth0
      1. The file name could be different. It could be ifcfg_eth0, too.
  2. Change the content to something like below:
    1. TYPE=Ethernet
      BOOTPROTO=none
      IPADDR=192.168.11.30
      PREFIX=24
      GATEWAY=192.168.11.1
      DNS1=192.168.11.10
      DNS2=209.18.47.62
      DEFROUTE=yes
      IPV4_FAILURE_FATAL=yes
      IPV6INIT=no
      NAME=eth0
      UUID=06823066-d18a-4af3-b3a0-e2158d152465
      ONBOOT=yes
      HWADDR=00:0C:29:D3:6C:5C
      LAST_CONNECT=1523339343
  3. :wq
  4. Open resolv.conf and check or correct the nameserver
    1. sudo vi /etc/resolv.conf
    2. It should be like the following.
      1. nameserver 192.168.11.10
      2. nameserver 209.18.47.62
  5. Restart network
    1. sudo /etc/init.d/network restart




Other admins use /etc/sysconfig/network file to specify the default gateway. They may not specify DNS1 or DNS2 in ifcfg-eth0 or ifcfg-Auto_eth0 file. Instead, only the /etc/sysconfig/network file is used to specify the nameservers. 

Below is another example of setting up static ip on CentOS 6



## Configure eth0
#
# vi /etc/sysconfig/network-scripts/ifcfg-eth0

DEVICE="eth0"
NM_CONTROLLED="yes"
ONBOOT=yes
HWADDR=A4:BA:CC:37:F1:03
TYPE=Ethernet
BOOTPROTO=static
NAME="System eth0"
UUID=5fb06bd0-0bb0-7ffb-45f1-d6edd65f3e03
IPADDR=192.168.11.30
NETMASK=255.255.255.0


## Configure Default Gateway
#
# vi /etc/sysconfig/network

NETWORKING=yes
HOSTNAME=centos6
GATEWAY=192.168.11.1


## Restart Network Interface
#

/etc/init.d/network restart

## Configure DNS Server
#
# vi /etc/resolv.conf

nameserver 192.168.11.10      # Replace with your nameserver ip
nameserver 209.18.47.62       # Replace with your nameserver ip

CentOS 6 - Change hostname

To check the host name of CentOS 6,

hostname
hostname -f

To change the host name on CentOS 6,
  1. sudo vi /etc/sysconfig/network
    1. Update HOSTNAME=[hostname.domain.com] to match the FQDN.
    2. For example,
      1. HOSTNAME=cent6.contoso.edu
  2. :wq
  3. If necessary, update /etc/hosts file for internal networking. Change the host that is associated with the IP address.
    1. sudo vi /etc/hosts
    2. change the host name as needed.
  4. Run the hostname command to change the hostname on the server. For example,
    1. sudo hostname cent6.contoso.edu
  5. Restart network 
    1. sudo /etc/init.d/network restart


CentOS 6 - Add a user to sudoers

Here are steps in adding a user to sudoers in CentOS 6


  1. Log on as root ( or switch to su with root password).
  2. On the command window, do the following.
  3. Enter "visudo". 
  4. vi loads the sudoer file.
  5. Look for %wheel in the sudoer file by entering " /%wheel "
  6. Remove # from the line "#  %wheel  ALL=(ALL)  ALL" to enable the wheel group with same privileges as root.
  7. Save and exit vi via  :wq.
  8. To create a new user:
    1. adduser   [new_user_name]
    2. passwd    [new_user_name]   [password]
  9. Use the usermod command to add the user to the wheel group:
    1. usermod   -aG   wheel   [user_name]
  10. Switch to the non-root user by entering "su  -  [user_name]"
  11. Test by running a command that's allowed for root only.
    For example, /root is generally accessible only by root user. Try listing the content of /root. 
    1. sudo  ls  -al  /root

Friday, March 9, 2018

Git could not pull because there were uncommitted changes

Recently, I could not pull from origin master on my Visual Studio 2017. Git could not pull because there were uncommitted changes. I forgot that every change in my local repo must first be committed before pull.

One easy way to commit uncommited changes and then pull was using command-line on the Visual Studio solution folder as follows.

git add -A
git commit -m "your message"
git fetch origin master
git pull origin master
git push origin master //To push to the git