Wednesday, 11 November 2020

How To create SSIS user in Azure SQL server

I've assumed you have already configured domain in azure and have created a domain user.

Create Application in app registration  

First of all Create an app registration [myssissqlserver01]

Set up Active directory Admin in sql server

Now Go to the sql server (not the database) and on settings menu, click Active directory Admin

Click Set Admin and add your domain account. (in case you don't have domain configured , Configure a new domain and create a domain user first)

Connect to the database using MS Sql server management studio

Once admin is set , Open MS Sql server management studio and connect to the database.

Since we have added user to active directory admin, the user will have access to master database. So we can choose default database to connect, 

Create a user in ssis and add to a suitable role

Create a new query window selecting SSISDB database

execute the below queries. Note that the user name should be identical to the Application name created in App registration section . [ myssissqlserver01]

create user [myssissqlserver01] from external provider;

exec sp_addrolemember [db_owner],[myssissqlserver01];

Tuesday, 22 September 2020

How to find first weekday in the month in MSSQL

declare @n datetime = dateadd(mm,0,getDate())

   declare @FirstWeekDayOfMonth int

   SELECT @FirstWeekDayOfMonth= CASE

         WHEN Datename(dw, Dateadd(dd, -Datepart(dd, @n) + 1, @n)) = 'Saturday' THEN Day(Dateadd(dd, -Datepart(dd, @n) + 3, @n))

         WHEN Datename(dw, Dateadd(dd, -Datepart(dd, @n) + 1, @n)) = 'Sunday' THEN Day(Dateadd(dd, -Datepart(dd, @n) + 2, @n))

         ELSE Day(Dateadd(dd, -Datepart(dd, @n) + 1, @n))


   select @FirstWeekDayOfMonth

Note: to find out first day of next month , change  first line to dateadd(mm,1,getDate()) 

1st day of next N month , change the first line to dateadd(mm,n,getDate())

Monday, 7 September 2020

Unit test c#: Create Fake Httpcontext to bypass Null exception for User object

 Microsoft Visual studio Test Framework

Requirement: Nuget package Moq (or later)

Settings in visual studio: Menu->Tools->Options ->Debugging->General
Tick "Use Managed Compatibility Mode"

Code: (copied from stackoverflow)
Step 1: 
Create a new class FakeHttpContext and paste the below code. Change the Returns value to whatever user name you need.

public static class FakeHttpContext
        public static void SetFakeContext(this Controller controller)

            var httpContext = MakeFakeContext();
            ControllerContext context =
            new ControllerContext(
            new RequestContext(httpContext,
            new RouteData()), controller);
            controller.ControllerContext = context;

        private static HttpContextBase MakeFakeContext()
            var context = new Mock<HttpContextBase>();
            var request = new Mock<HttpRequestBase>();
            var response = new Mock<HttpResponseBase>();
            var session = new Mock<HttpSessionStateBase>();
            var server = new Mock<HttpServerUtilityBase>();
            var user = new Mock<IPrincipal>();
            var identity = new Mock<IIdentity>();

            context.Setup(c => c.Request).Returns(request.Object);
            context.Setup(c => c.Response).Returns(response.Object);
            context.Setup(c => c.Session).Returns(session.Object);
            context.Setup(c => c.Server).Returns(server.Object);
            context.Setup(c => c.User).Returns(user.Object);
            user.Setup(c => c.Identity).Returns(identity.Object);
            identity.Setup(i => i.IsAuthenticated).Returns(true);
            identity.Setup(i => i.Name).Returns("sanjeeb");

            return context.Object;

Step 2: 
Initialize the Fakehttpcontext in test initialize function. 


Accessing SMB share from Raspberry pi (or linux)

 Install smbclient :

sudo apt update 
sudo apt install smbclient
List the share items in the server: 
smbclient -L host
for example: smbclient -L
you need to enter password.then you'll see the result like this:
Domain=[WORKGROUP] OS=[Windows 6.1] Server=[Samba 4.9.5-Debian]

 Sharename       Type      Comment
 ---------       ----      -------
 print$          Disk      Printer Drivers
 RaspberrypiShare Disk      
 IPC$            IPC       IPC Service (Samba 4.9.5-Debian)
 pi              Disk      Home Directories
Domain=[WORKGROUP] OS=[Windows 6.1] Server=[Samba 4.9.5-Debian]

 Server               Comment
 ---------            -------
 RASPBERRYPI          Samba 4.9.5-Debian

 Workgroup            Master
 ---------            ------- 
To browse Shared folder :
smbclient \\\\\\RaspberrypiShare pa$$w0rd

Temperature/CPU/Memory info of Raspberry Pi in console

 Open the console/terminal in the desktop or

Connect to the raspberry pi via ssh and type in the below command

For temperature:
/opt/vc/bin/vcgencmd measure_temp

For CPU info:
cat /proc/cpuinfo

For Memory info:
cat /proc/meminfo

Add Python(or Java or anything) to Environment variables

After installation of python in windows 10, python is not added to environment variable by default. If you want to use cmd for python, then python installed path must be configured in the environment variable. 

When you type python in cmd, you'll see the below message:

Follow the below steps to add python to environment:
1) Open System in Control panel
2) Click Advanced system settings
3)Click Environment Variables
4) Select Path in User Variables and click edit
5) Either create new and type in the path of Python installed location or Browse the folder using browse button in the above screenshot.

6) Click OK and close the Environment variables settings window and all other opened windows. 
7) Start a new command prompt and type python to test. 


Monday, 17 August 2020

Add Azure Devops plugins in Android Studio for Git or TFVC Version control

 By default, Android studio can not connect to the Azure devops source codes. To connect to the Azure devops, a plugins by Microsoft needs to be installed in the android studio. To install the plugin, follow the below steps:

1) Open a project in Android studio
2) go to File --> Setting
3) go to Plugins
4) Search for azure devops
5) Click Install
6) Restart the Android studio.
7) Menu->VCS->Checkout from Version Control -> Azure Devops Git
8)Click Sign in ... 
9) Follow the wizard to connect the project.

Create Azure SQL User and add a sql role

I wanted to add a new sql user "sqluser" in Azure SQL server to access a database "sanjeeb". 

Steps to create a new SQL user. 

1) Connect to the Azure sql server with existing user using Microsoft SQL Server Management studio

2) Open new query window under Master database

3) Run the below script
    CREATE LOGIN [sqllogin] WITH PASSWORD = 'verystrongpassword#1' 

4) Open new query window under database "sanjeeb"

5) Run the below script

CREATE USER [sqluser]
FOR LOGIN [sqllogin]

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'sqluser'

6) You've now created a user login "sqllogin", user account "sqluser" and added the user to "db_owner role"

7) Connect to the database using the new user login account. 

login: sqllogin

8) Click Options and select Connection properties tab

9) input Connect to database = sanjeeb

10) Click connect

Tuesday, 11 August 2020

Scrollable readonly EditView in Kotlin

 Scrollable readonly EditView in Kotlin

In the view xml file: 
        tools:targetApi="o" />

in the kotlin file (init function): 
etResult.keyListener = null

this will allow the edit text to be multiline, not editable but scrollable. 

Wednesday, 5 August 2020

Simple use of Timer in Kotlin

the code below will set the button to clickable after 30 sec:

Timer().schedule(timerTask {


}, 1000*30)

the above code runs in a separate thread, 
so the UI is responsive while running this code.

Working with UI controls from Non main thread in Kotlin

import org.jetbrains.anko.doAsync
import org.jetbrains.anko.uiThread

fun anyFunction(){
doAsync {
      // Normal code inside Async thread

    uiThread {

        tvResult.setText =”I is now 100!!”
   //More code
     uiThread {

        tvResult.setText =”there’s some problem!”


Note: - you can include multiple uiThread inside same doAsync
          - you can include multiple doAsync inside any function

Permission request pop up in android

Request user to give permission to read external storage in Android - (Kotlin)

if (ContextCompat.checkSelfPermission(this,
    != PackageManager.PERMISSION_GRANTED) {

    // Permission is not granted    // Should we show an explanation?    if (ActivityCompat.shouldShowRequestPermissionRationale(this,
            Manifest.permission.READ_EXTERNAL_STORAGE)) {

    } else {
        // No explanation needed, we can request the permission.        ActivityCompat.requestPermissions(this,

} else {
    // Permission has already been granted}
NOTE: READ_STORAGE_CODE is just an int constant with fix value like 1001, or 2023
and is a variable in class level. 

TFS -GIT CertGetCertificateChain trust error CERT_TRUST_IS_UNTRUSTED_ROOT

Problem 1)

2019-07-02T14:19:12.5157398Z fatal: unable to access '': SSL certificate problem: unable to get local issuer certificate
2019-07-02T14:19:12.9764145Z ##[error]Git fetch failed with exit code: 128
2019-07-02T14:19:12.9861630Z ##[section]Finishing: Get Sources

In the TFS_Build Server or App server(if build and app server is in the same machine) >

run the script from this url:

Once this is done, you should get another problem:

Problem 2)  CertGetCertificateChain trust error CERT_TRUST_IS_UNTRUSTED_ROOT

2019-07-02T14:24:57.1800199Z ##[command]git -c http.extraheader="AUTHORIZATION: bearer ***" fetch --tags --prune --progress --no-recurse-submodules origin
2019-07-02T14:24:57.4540146Z fatal: unable to access '': schannel: CertGetCertificateChain trust error CERT_TRUST_IS_UNTRUSTED_ROOT
2019-07-02T14:24:57.5198064Z ##[error]Git fetch failed with exit code: 128

2019-07-02T14:24:57.5288511Z ##[section]Finishing: Get Sources


  1. Export the certificate public key to a file. The file is later required.
  2. Open the url "" in IE
  3. Click on the Lock icon in the browser address bar
  4. Select The Root level Certificate and Click View Certificate

Now Find the certificate file from GIT folder. 
normally it's inside GIT\usr\ssl\certs folder

  1. Copy the file to User folder
copy c:\Users\svc_tfs17_app

  1. Config Git to use trusted certificate using the crt file.  
    git config --global http.sslCAInfo c:\Users\svc_tfs17_app\ca-bundle.crt
  2. Convert the \n (Unix) to \r\n (Windows) so that it can be displayed by notepad editor correctly.
  3. Use the unix2dos open source software to convert \n to \r\n, or other notepad editor to replace \n to \r\n.
  1. Copy the content of tfs.cer from step o to ca-bundle.crt at the bottom of the file.


Output :

Insert Identity Column in a table

 insert an identity column in that table by using the following code:

MSSQL Remove duplicate Rows

  1. Select the duplicate key values into a holding table. For example:

        SELECT col1, col2, col3=count(*)
        INTO holdkey
        FROM t1
        GROUP BY col1, col2
        HAVING count(*) > 1
  2. Select the duplicate rows into a holding table, eliminating duplicates in the process. 
    For example:
           SELECT DISTINCT t1.* 
           INTO holddups
           FROM t1, holdkey 
           WHERE t1.col1 = holdkey.col1 
           AND t1.col2 = holdkey.col2
  3. At this point, the holddups table should have unique PKs, however, this will not be the case if t1 had        
    duplicate PKs, yet unique rows (as in the SSN example above). Verify that each key in holddups is unique,
    and that you do not have duplicate keys, yet unique rows. If so, you must stop here and reconcile 
    which of the rows you wish to keep for a given duplicate key value. For example, the query:
           SELECT col1, col2, count(*)
           FROM holddups
           GROUP BY col1, col2
    should return a count of 1 for each row. If yes, proceed to step 5 below. 
    If no, you have duplicate keys, yet unique rows, and need to decide which 
    rows to save. This will usually entail either discarding a row, or creating
    a new unique key value for this row. Take one of these two steps for each 
    such duplicate PK in the holddups table.
  4. Delete the duplicate rows from the original table. For example:
         DELETE t1
         FROM t1, holdkey
         WHERE t1.col1 = holdkey.col1
         AND t1.col2 = holdkey.col2  
  5. Put the unique rows back in the original table. For example:
         INSERT t1 SELECT * FROM holddups
Another Way:
delete from table1
FROM table1 t1, table1 t2

Read TFS Project name from TFS Project collection using c#

Using Team foundation api.
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.dll
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.Client.dll
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.Common.dll
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.WorkItemTracking.Client.dll

using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Server;
using System;
using System.Linq;

class Program
{ private static ICommonStructureService _commonStructureService;
static void Main(string[] args)
 public static void ReadProject(String URL)
writeFile("Project Collection: " + URL);
TfsTeamProjectCollection tpc = new TfsTeamProjectCollection(new Uri(URL));
var collection = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(tpc.Uri);
_commonStructureService = collection.GetService<ICommonStructureService>(); var projects = _commonStructureService.ListAllProjects().OrderBy(a => a.Name);
foreach (var project in projects)

} public static void writeFile(String message) {
using (System.IO.StreamWriter file =
new System.IO.StreamWriter(@"C:\temp\tfs.txt"true))

Output is the list of project names in tfs.txt file. 

GIT - SSL certificate problem: unable to get local issuer certificate

Error encountered while cloning the remote repository: Git failed with a fatal error.
unable to access '': SSL certificate problem: unable to get local issuer certificate

Cloning into 'C:\Users\sojha\Source\Repos\SalesPortalWeb'...
Error encountered while cloning the remote repository: Git failed with a fatal error.
unable to access '': SSL certificate problem: unable to get local issuer certificate

Solution: Open Visual Studio Command prompt

GIT provides an option to choose from OpenSSL and Secure Channel. Choosing secure channel in git global solves this issue.

Run the script:

git config --global http.sslBackend schannel

Entity Framework Core with Existing Database

Entity Framework Core only supports code first database aproach. 
To use existing database, we'll need to reverse Engineer Model from existing Database. 
  • Open Visual studio.
  • Menu > tools >Nuget Package Manager >
  • open PM Console
Note: All the tables in the database must have Primary key defined. else you'll get error like:
Unable to generate entity type for table 'dbo.xOrdersMBS'.
Unable to identify the primary key for table 'dbo.xActivePricesEPB'.
Unable to generate entity type for table 'dbo.xActivePricesEPB'.
Unable to identify the primary key for table 'dbo.xLeadTimePBOM'.
Unable to generate entity type for table 'dbo.xLeadTimePBOM'.

 In the console, run the below power shell commands:

Using Windows Authentication:
Scaffold-DbContext "Server=DatabaseServerAddress;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -f

Using SQL Account:
Scaffold-DbContext "data source=DatabaseServerAddress;initial catalog=DatabaseName;uid=UserName;pwd=Password;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -f

Note: -f force create the files if the file exist in the folder Models.

Thursday, 16 July 2020

Add nuget packages to Artifacts in Azure devops

download latest nuget.exe from the nuget site. (google it)

copy the nuget.exe in d: (any location is fine)

Use visual studio -> Package manager console. 
run the below command
D:\nuget.exe push -Source "IGTelerikFeed" -ApiKey az D:\Telerik.UI.for.AspNet.Core.2020.2.617.nupkg

[Note: It will ask Credential to connect to the devops when you press enter. ]

Thursday, 2 July 2020

EntityFramework Insert error due to PrimaryKey

MyProject.Controllers.CurrencyCodesController Unable to update the EntitySet 'CurrencyCodes' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.
System.Data.Entity.Infrastructure.DbUpdateException: Unable to update the EntitySet 'CurrencyCodes' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation. ---> System.Data.Entity.Core.UpdateException: Unable to update the EntitySet 'CurrencyCodes' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

The error occured while trying to insert the currencycodes entity to the database. 
public ActionResult Create([Bind(Include = "cucCurrencyCode,cucDescription,cucMultiplier,cucRate,cucInUse")] CurrencyCode currencyCode)
{ if (ModelState.IsValid)
{ try
{ db.CurrencyCodes.Add(currencyCode);
 catch (Exception ex) {
log.Error(ex.Message, ex);
 return RedirectToAction("Index");
} return View(currencyCode);


1) Check Primary key in .edmx file.  In my case cucInUse was set as Entity key. I removed it.
2) Now Right click the .edmx file and click "Open with...". Select XML (text) Editor
3)  Find the element <EntitySet name = "CurrencyCodes"...>
4) Change store:Schema="dbo" => Schema="dbo"
5) delete element <DefiningQuery>
6) Clean the project. 
7) close Visual studio.
8) Open visual studio. Open the project. 
9) Clean and build the project. 
10) Run the application.

Friday, 26 June 2020

Fix for SSIS not picking correct configuration file

When there are more then one configuration file for a single package, the SSIS jobs sometimes do not automatically pick the correct configuration depending on how the settings has been made. 

In the SSIS package (using Visual Studio) Make sure you've done following correctly:
1) In the package property, Set Delay Validation =  false
2) Uncheck , Enable Package Configuration. 

steps for 2):
a) open the ssis package in visual studio.
b) In the Control flow tab, right click on the blank space of the canvas.
c) go to Package Configurations...
d) uncheck Enable Package Configuartion and press Close.
e) Save the ssis package and copy that to the deployed folder.

How to remove Workspace in TFS 2010-2018

This solution Works for TFS 2010+.

Open Visual studio Command prompt and run the following script.

tf workspace /delete /server:[Site:Port/path] WORKSPACENAME

for example:
tf workspace /delete /server: sanjeebPC

Use log4net for all logging in c# application

Exception logging is hugely important within an application, not just to ensure we know when an error has occured but also to help use trace back the thread to help investigate where the error originated.
Our strategy is to use log4net for all our logging needs.
This page helps cover the follow areas
1. Setting up logging across the entire applications
2. Including 2 appenders so we can be notified when they occur and ensure a tracable debug log file is available.
Setting up your Applications for Logging
Reference log4net in your applications
in Global.asax under the start_application method include the following
    void Application_Start(object sender, EventArgs e)
        // link up log4net configuration
On the page where you wish to log information declare a new static variable in the class header
log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Whenever you wish to now log an event in this class run one of the follow commands, ensure you include the exception when needed
log.Error("An unhandled exception occured", ex);
To ensure you catch all unhandled exceptions include the following in your Global.asax file. Remember to declare the log variable in the class too.
    void Application_Error(object sender, EventArgs e) 
        // Code that runs when an unhandled error occurs
        // get last exception
        Exception ex = Server.GetLastError().GetBaseException();
        // log it
        log.Error("An unhandled exception occured", ex);
Web.Config Settings
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
    <!-- The DebugFileAppender writes all messages to a log file-->
    <appender name="DebugFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="L:\[APPNAME]\Log.txt" /> <!-- These should be located on the "L" drive (if available) and stored under a folder named for the specific application -->      <datePattern value="'ApplicationLog.'yyyy-MM-dd'.txt'" />
      <staticLogFileName value="false" />
      <rollingStyle value="Composite" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="5MB" />
      <appendToFile value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%d [%t] %-5p %c %m%n" />
    </appender>    <!-- The EmailAppender sends an email when something matches the filters-->
    <appender name="EmailAppender" type="log4net.Appender.SmtpAppender">
      <evaluator type="log4net.Core.LevelEvaluator">
        <threshold value="DEBUG"/>
      <!--  The filters are processed in order:
            1) match the Inserted New User message
            2) match any WARN or higher messages
            3) reject everything else -->
      <filter type="log4net.Filter.StringMatchFilter">
        <stringToMatch value="Inserted a new user" />
        <acceptOnMatch value="true" />
      <filter type="log4net.Filter.LevelRangeFilter">
        <levelMin value="WARN" />
        <acceptOnMatch value="true" />
      <filter type="log4net.Filter.DenyAllFilter" />
      <!--  The SmtpAppender authenticates against the mail server, the buffersize of 10 provides 10 lines
            of context when an error happens. -->
      <subject value="[SEVERITY] [ENV] [APPNAME] - [EXCP_MSG]" />
      <to value="" />
      <from value="[email protected]" />
      <smtpHost value="" />
      <bufferSize value="10" />
      <lossy value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <param name="ConversionPattern" value="%d [%t] %-5p %c %m%n" />
    <appender name="EventLogAppender" type="log4net.Appender.EventLogAppender" >
      <applicationName value="MyApp" />

     <layout type="log4net.Layout.PatternLayout">

        <conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />



      <!-- add other appenders here and the log messages will be sent to every listed appender -->
      <appender-ref ref="DebugFileAppender" />
      <appender-ref ref="EmailAppender" />

How to check roles for sql users

  The following query returns the members of the database roles. SELECT  AS  DatabaseRoleName,        isnull  (,  'No ...