Posts

Tricky SQL Interview Questions

SQL Interview Questions How to insert 10 default records in SQL Table. For example suppose we have Student Table with columns Name, Address and other. Write a query to insert default value in one go. INSERT INTO mytable DEFAULT VALUES GO 10 What is GO keyword in SQL? GO keyword is not associated with SQL but it is property of SQL Server Management Studio. GO can be understand simply for following example. Student with Mark on their T-Shirt 1,2,3,4,5,6,7,8,9,10 and 11,12….20 are ready to run in an event but when it is said student from 1 to 10 Go then they start running but students from 11 to 20 wait until they are not said. So it is batch separator in SQL. What is Temp table and Temp variable in SQL and why they are used? 1.       Temp Table store location is Tempdb while Temp variable storage location if not specified is current database. 2.       Table variables can only be accessed within the batch and scope in which they are declared. Temp Tables a
Post a Comment
Read more

Access API from Stored Procedure in SQL Server

Image
This document describes how to call any API with POST, GET, PUT and DELETE methods from stored procedure. For example, if we want to send email from SQL Server using stored procedure but don’t want to use inbuild functionality of SQL Server but to use any third party API. To make it working (For the purpose of testing) first I created API you can get the API of GET and POST http://mongobox.azurewebsites.net/swagger Now created a CLI Project in Visual Studio and created a method called SendMail which is accepting few parameters like WebURL, EmailTo,EmailBody,EmailSubject. See below image. Now as I am utilizing the POST method API, so I need to pass various parameters in that API and I am passing it to API with given method: (Name, Age and other params are according to API we can use any params as per need of API) Now, I need to build and publish this code in folder to get DLL file which will be used as assembly file in SQL server see below image (We need t
Post a Comment
Read more

Add Custom Tags in Web config file in ASP .Net

Image
For any reason we want to add some custom tags in web config file but unfortunately these is some predefined tag available for web.config file. To do so we need to create XML file in the format that suits IIS. You can take example of any config present in C:\Windows\system32\inetsrv\config\schema Now create any custom tags and create file and save in the same directory. Now but this is not enough you need to add in applicationhost file as well at C:\Windows\system32\inetsrv\config This will solve your problem and you will be able to add custom config tag in web config file in ASP.Net website.
1 comment
Read more

DTS package conversion in SSIS to use in SQL 2012 and SQL 2016

Image
In my company my team decided to migrate whole SQL 2008 to brand new SQL 2016 in Azure VMs. This was a good idea as we should always be up to date in terms of technology. We did migration all SQL 2008 to SQL 2016 without any problem but suddenly we saw we need to migrate warehouse as well and it was containing very complex DTS package almost 50 in number and we have very less time to finish this task. We first decided to do following things: Upgrade DTS in SSIS with wizard available in SQL 2008 But this was not helpful because if you have some VB script written in DTS and it enables or disables any step then it will not work in SSIS because enabling and disabling is not allowed in SSIS. YOu will have to manually correct all these code. So we tried this and dropped this idea. Manually Convert whole DTS in SSIS: This was blunder for us as it was going to take many months of re creation and testing so tried this and dropped this idea as well because we got a very motivati
Post a Comment
Read more

SQL Server Failover Clustering in SQL Server 2016 with DNS in Azure

Image
As we know that database mirroring is no longer supported in the latest version of SQL Server i.e. in MS SQL Server 2016. So people are moving toward SQL Server failover clustering as they want to achieve High Availability of database. Here we will setup the things in Azure cloud service, you can enjoy your free subscription of Azure cloud for 1 month. So we can do this in two ways: 1. by using DNS Server which will cost you extra server need more money and maintenance. 2. By using workgroup i.e. without DNS Server this will take less cost in comparison to first one. Best way to do implement SQL Server failover clustering Its all up to your requirement if you have low budget then go without DNS and if have sufficient budget then go with DNS Server extra. Both ways have their own pro and cons so we can't rigid with one way. Steps by Steps to create a   SQL Server Always On Availability Group on Azure Virtual Machines. Create resource group 1)     Sign in to the
Post a Comment
Read more

Entity Power Tool for Visual Studio 2015

Image
EF Power tool is widely used for reverse engineering in MVC Projects to create Model and Mapping which takes more time for developers when do manually. Since EF Power tool is initially designed for VS 2010, 2012, and 2013. You can see it on Microsoft website where compatible version is clearly mentioned. So here I am going to tell you how you will install EF Power tool in VS 2015. Step 1: Just download the file click to download and simply Install it by just double-clicking on it and then Next Next. Step 2: Now when You will do reverce engineering you will get error One or more errors occurred while processing template 'Entity.tt'.  To solve this error you can just change the Target framework 4.1.5 from your current version. It will solve your problem. When you have done with reverse engineering then again change the Target framework.
Post a Comment
Read more

How to use AngularJs with MVC C#

Image
So this tutorial is related to JSON data Download the project file form here What we will do in this tutorial? 1. Will know what is JSON data? 2. How to get JSON data returned from a method in C#? 3. How to use them in AngularJs or in Javascript? First start with  what is JSON data? As per Json.org it is Javascript Object Notation which is very lightweight and easy to use for machine and us also. For more details visit : http://json.org/ How to get JSON data returned from a method in C#? So yo can see video here and get to know how it actually works. Steps are as:  Take a empty MVC project and name it what ever you want.  Create a new model StudentDetails (or what ever you want you are free to do so)  Take a controller and it will be good to take Default for learning purpose  Create A JsonResult (Represents a class that is used to send JSON-formatted content to the response.) Type method as mentioned in Video  Create instance of model and assign value to its pr
3 comments
Read more