In this article, I am going to tell you how to create a procedure in the real time scenario. A stored procedure is a set of SQL statements, which has been created and stored in the database as an object. Stored procedure will accept the input and output parameters, so that a single procedure can be used over the network by several users, using different input. Stored procedure will reduce the network traffic and increase the performance.

Real time scenario
Step 1: Create a table to describe and create the stored procedure.
    create table Product 
    ( 
          ProductId int primary key, 
          ProductName varchar(20) unique, 
          ProductQty int, 
          ProductPrice float 
    ) 

Step 2: Insert some value to the describe scenario.
    insert product values(1,'Printer',10,4500) 
    insert product values(2,'Scanner',15,3500) 
    insert product values(3,'Mouse',45,500)  

Step 3: Check your table with the inserted value.
    select * from product  

Step 4: Real time scenario is given below:
Create a stored procedure, which is used to perform the requirements, given below:

Before inserting, check the detail about the product name. If the product name is available, update an existing product qty + inserted product qty,

• Before inserting, check the detail about the product name.
  
• If the product name is available, check the product price.
• If the existing product price is less, the inserted product product price replaces the existing product price with the inserted product price.
• If first and second conditions are not satisfied, insert the product information, as new record into the table.

    create procedure prcInsert  
    @id int, 
    @name varchar(40), 
    @qty int, 
    @price float 
    as 
    begin 
     declare @cnt int 
     declare @p float 
     select @cnt=COUNT(ProductId)from Product where pname=@name 
     if(@cnt>0) 
     begin 
      update Product set ProductQty=ProductQty+@qty where ProductName=@name 
      select @p=ProductPrice from Product where ProductName=@name 
      if(@p<@price) 
      begin 
       update Product set ProductPrice=@price where ProductName=@name 
      end 
     end 
     else 
     begin 
      insert Product values(@id,@name,@qty,@price) 
     end 
    end  

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

As we’ve come to expect from new versions of Windows Server, Windows Server 2016 arrives packed with a huge array of new features. Many of the new capabilities, such as containers and Nano Server, stem from Microsoft’s focus on the cloud. Others, such as Shielded VMs, illustrate a strong emphasis on security. Still others, like the many added networking and storage capabilities, continue an emphasis on software-defined infrastructure begun in Windows Server 2012.

The GA release of Windows Server 2016 rolls up all of the features introduced in the five Technical Previews we’ve seen along the way, plus a few surprises. Now that Windows Server 2016 is fully baked, we’ll treat you to the new features we like the most.

Here are several features that you can get from Windows Server 2016:

Nano Server

Nano Server boasts a 92 percent smaller installation footprint than the Windows Server graphical user interface (GUI) installation option. Beyond just that, these compelling reasons may make you start running Nano for at least some of your Windows Server workloads:

• Bare-metal OS means far fewer updates and reboots are necessary.
• Because you have to administratively inject any server roles from outside Nano, the server has a much-reduced attack surface when compared to GUI Windows Server.
• Nano is so small that it can be ported easily across servers, data centers and physical sites.
• Nano hosts the most common Windows Server workloads, including Hyper-V host.
• Nano is intended to be managed completely remotely. However, Nano does include a minimal local management UI called "Nano Server Recovery Console," shown in the previous screenshot, that allows you to perform initial configuration tasks.

Containers

Microsoft is working closely with the Docker development team to bring Docker-based containers to Windows Server. Until now, containers have existed almost entirely in the Linux/UNIX open-source world. They allow you to isolate applications and services in an agile, easy-to-administer way. Windows Server 2016 offers two different types of "containerized" Windows Server instances:

• Windows Server Container. This container type is intended for low-trust workloads where you don't mind that container instances running on the same server may share some common resources
• Hyper-V Container. This isn't a Hyper-V host or VM. Instead, its a "super isolated" containerized Windows Server instance that is completely isolated from other containers and potentially from the host server. Hyper-V containers are appropriate for high-trust workloads.

Linux Secure Boot

Secure Boot is part of the Unified Extensible Firmware Interface (UEFI) specification that protects a server's startup environment against the injection of rootkits or other assorted boot-time malware.

The problem with Windows Server-based Secure Boot is that your server would blow up (figuratively speaking) if you tried to create a Linux-based Generation 2 Hyper-V VM because the Linux kernel drivers weren't part of the trusted device store. Technically, the VM's UEFI firmware presents a "Failed Secure Boot Verification" error and stops startup.

Nowadays, the Windows Server and Azure engineering teams seemingly love Linux. Therefore, we can now deploy Linux VMs under Windows Server 2016 Hyper-V with no trouble without having to disable the otherwise stellar Secure Boot feature.

ReFS

The Resilient File System (ReFS) has been a long time coming in Windows Server. In Windows Server 2016, we finally get a stable version. ReFS is intended as a high-performance, high-resiliency file system intended for use with Storage Spaces Direct (discussed next in this article) and Hyper-V workloads.

Storage Spaces Direct

Storage Spaces is a cool Windows Server feature that makes it more affordable for administrators to create redundant and flexible disk storage. Storage Spaces Direct in Windows Server 2016 extends Storage Spaces to allow failover cluster nodes to use their local storage inside this cluster, avoiding the previous necessity of a shared storage fabric.

ADFS v4

Active Directory Federation Services (ADFS) is a Windows Server role that supports claims (token)-based identity. Claims-based identity is crucial thanks to the need for single-sign on (SSO) between on-premises Active Directory and various cloud-based services.

ADFS v4 in Windows Server 2016 finally brings support for OpenID Connect-based authentication, multi-factor authentication (MFA), and what Microsoft calls "hybrid conditional access." This latter technology allows ADFS to respond when user or device attributes fall out of compliance with security policies on either end of the trust relationship.

Nested Virtualization

Nested virtualization refers to the capability of a virtual machine to itself host virtual machines. This has historically been a "no go" in Windows Server Hyper-V, but we finally have that ability in Windows Server 2016.

Nested virtualization makes sense when a business wants to deploy additional Hyper-V hosts and needs to minimize hardware costs.

Hyper-V Server has allowed us to add virtual hardware or adjust the allocated RAM to a virtual machine. However, those changes historically required that we first power down the VM. In Windows Server 2016, we can now "hot add" virtual hardware while VMs are online and running. I was able to add an additional virtual network interface card (NIC) to my running Hyper-V virtual machine.

PowerShell Direct

In Windows Server 2012 R2, Hyper-V administrators ordinarily performed Windows PowerShell-based remote administration of VMs the same way they would with physical hosts. In Windows Server 2016, PowerShell remoting commands now have -VM* parameters that allows us to send PowerShell directly into the Hyper-V host's VMs!

Invoke-Command -VMName 'server2' -ScriptBlock {Stop-Service -Name Spooler} -Credential 'tomsitprotim' -Verbose

We used the new -VMName parameter of the Invoke-Command cmdlet to run the Stop-Service cmdlet on the Hyper-V VM named server2.

Shielded VMs

The new Host Guardian Service server role, which hosts the shielded VM feature, is far too complex to discuss in this limited space. For now, suffice it to say that Windows Server 2016 shielded VMs allow for much deeper, fine-grained control over Hyper-V VM access.

For example, your Hyper-V host may have VMs from more than one tenant, and you need to ensure that different Hyper-V admin groups can access only their designated VMs. By using BitLocker Drive Encryption to encrypt the VM's virtual hard disks, shielded VMs can solve that problem.

Angular js URL:
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script> 

Html tag:
<div ng-app="myApp" ng-controller="myCntrl"> 
    <table> 
        <thead> 
            <tr> 
                <th> 
                    Emp Code. 
                </th> 
                <th> 
                    Employee Name 
                </th> 
                <th> 
                    Pan No 
                </th> 
                  
            </tr> 
        </thead> 
        <tr ng-repeat="student in EmployeeList"> 
            <td ng-bind="student.StudentID"> 
            </td> 
            <td ng-bind="student.StudentName"> 
            </td> 
            <td ng-bind="student.PanNO"> 
            </td> 
              
        </tr> 
    </table> 
</div> 

Angular js Script :

<script> 
   var app = angular.module("myApp", []); 
   app.controller("myCntrl", function ($scope, $http) { 

       $scope.fillList = function () { 
           $scope.EmployeeName = ""; 
           var httpreq = { 
               method: 'POST', 
               url: 'Default2.aspx/GetList', 
               headers: { 
                   'Content-Type': 'application/json; charset=utf-8', 
                   'dataType': 'json' 
               }, 
               data: {} 
           } 
           $http(httpreq).success(function (response) { 
               $scope.EmployeeList = response.d; 
           }) 
       }; 
       $scope.fillList(); 
   }); 
</script> 

Asp.net Cs page code:
using System; 
using System.Collections.Generic; 
using System.Data.SqlClient; 
using System.Data; 

public partial class Default2 : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 

} 
[System.Web.Services.WebMethod()] 
public static List<Employee> GetList() 
{ 
    List<Employee> names = new List<Employee>(); 
    DataSet ds = new DataSet(); 
    using (SqlConnection con = new SqlConnection(@"Data Source=140.175.165.10;Initial Catalog=Payroll_290716;user id=sa;password=Goal@12345;")) 
    { 
        using (SqlCommand cmd = new SqlCommand()) 
        { 
            cmd.Connection = con; 
            cmd.CommandText = "select EmpId,Empcode, name,PanNo from EMPLOYEEMASTER  order by Name;"; 
            using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 
            { 
                da.Fill(ds); 
            } 
        } 
    } 
    if (ds != null && ds.Tables.Count > 0) 
    { 
        foreach (DataRow dr in ds.Tables[0].Rows) 
            names.Add(new Employee(int.Parse(dr["EmpId"].ToString()), dr["name"].ToString(), dr["PanNo"].ToString())); 
    } 
    return names; 
} 
} 
public class Employee 
{ 
public int StudentID; 
public string StudentName; 
public string PanNO; 
public Employee(int _StudentID, string _StudentName, string _PanNO) 
{ 
    StudentID = _StudentID; 
    StudentName = _StudentName; 
    PanNO = _PanNO; 
} 
}  

Whole HTML page:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head runat="server"> 
<title></title> 
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script> 
</head> 
<body> 
<form id="form1" runat="server"> 
<div ng-app="myApp" ng-controller="myCntrl"> 
    <table> 
        <thead> 
            <tr> 
                <th> 
                    Emp Code. 
                </th> 
                <th> 
                    Employee Name 
                </th> 
                <th> 
                    Pan No 
                </th> 
                  
            </tr> 
        </thead> 
        <tr ng-repeat="student in EmployeeList"> 
            <td ng-bind="student.StudentID"> 
            </td> 
            <td ng-bind="student.StudentName"> 
            </td> 
            <td ng-bind="student.PanNO"> 
            </td> 
              
        </tr> 
    </table> 
</div> 
<script> 
    var app = angular.module("myApp", []); 
    app.controller("myCntrl", function ($scope, $http) { 

        $scope.fillList = function () { 
            $scope.EmployeeName = ""; 
            var httpreq = { 
                method: 'POST', 
                url: 'Default2.aspx/GetList', 
                headers: { 
                    'Content-Type': 'application/json; charset=utf-8', 
                    'dataType': 'json' 
                }, 
                data: {} 
            } 
            $http(httpreq).success(function (response) { 
                $scope.EmployeeList = response.d; 
            }) 
        }; 
        $scope.fillList(); 
    }); 
</script> 
</form> 
</body> 
</html>  

HostForLIFE.eu AngularJS Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Dynamic vs. Static Data Masking

When masking data, organizations prevent unauthorized users from viewing sensitive data and protect information for following regulatory needs.  Data masking technology provides data security by replacing sensitive information with a non-sensitive content, but doing so in such a way that the copy of data that looks and acts like the original.

In this article, we talk about the different types of data masking and discuss how organizations can use data masking to protect sensitive data.

Masking data isn’t the same as a firewall

Most organizations have a fair amount of security around their most sensitive data in the production (live) databases. Access to databases is restricted in a variety of ways from authentication to firewalls.

Masking limits the duplication of sensitive data within development and testing environments by distributing substitute data sets for analysis. In other cases, masking will dynamically provide masked content if a user’s request for sensitive information is considered ‘risky’. Masking data is designed to fit within existing data management frameworks and mitigate risks to information without sacrificing its usefulness. Masking platforms tend to guard data, locate data, identify risks and protect as information moves in and out of the applications.

Data masking hides the actual data. There are a variety of different algorithms for masking, depending on the requirements.

Simple masking just turns characters to blank, so, for example, an e-mail address would appear as [email protected]

More complex masking understands values, so, for example, a real name like “David Patrick” would be transformed into a fake name (with the same gender characteristics), like “John Smith”

In some algorithms, values are scrambled, so, for example, a table of health conditions might appear with values of the health conditions, but not associated with the correct person for the particular salary

Most data masking tools will offer a variety of levels of masking that can be enabled in your network. Both static and dynamic data masking use these same masking methodologies.

Static data masking

Static data masking is used by most organizations when they create testing and development environments, and, in fact, is the only possible masking method when using outsourced contractors or developers in a separate location or separate company. In these cases, it’s necessary to duplicate the database. When doing so, it is crucial to use a static data masking tools. These tools ensure that all sensitive data is masked before sending it out of the organization.

Static data masking provides a basic level of data protection by creating an offline or testing database using a standard ETL procedure. This procedure replicates a production database, but substitute’s data that has been masked, in other words, the data fields are changed to data that’s not original or is not readable.

It’s important to be aware that static masking can provide a backdoor, especially when outsourced personnel is used for administration, development, or testing. To mask data, the data is extracted from the database, at least for inspection, to comprehend the data before masking. Theoretically, this could provide a backdoor for data breaches, though it is not one of the common methods of malicious data capture.

Also, it’s clear that the static database always lags behind the actual data. The static database can be updated periodically, for example on a daily or weekly basis. This is not a security risk, but it often has implications for a variety of tests and development issues.

Static data masking allows database administrators, quality assurance, and developers to work on a non-live system so that private data is not exposed.

In many cases, in fact, you’ll want a test database anyhow. You don’t want to be running live experiments on a production database, so for R&D and testing, it makes sense to have a test database. There’s nothing wrong with this scenario.

Is your database protected with static data masking?

The answer should be obvious from the image above. Your actual production database is, in fact, not protected in any way when it comes to concealing sensitive information. Anyone or any system that has access to the production database might also have access to sensitive information. For most organizations, the only protection under this scenario is provided by limiting authorization access to the production database.

Concerns about static data masking

With static data masking, most of the DBAs, programmers, and testers never actually get to touch the production database. All of their work is done on the dummy test database. This provides one level of protection and is necessary for many environments. However, it is not a complete solution because it does not protect authorized users from viewing and extracting unauthorized information. The following concerns should be noted when using static database solutions.

Static solutions actually require extraction of all the data before it is masked, that is, it actually guarantees the data gets out of the database in unmasked form. One of the most disturbing facts about static data masking is the standard ETL (extract, transform, and load) approach. In other words, the database information was extracted as-is from the database, and only afterward transformed. You have to hope or trust that the masking solution successfully deleted the real data, and that the static masking solution is working on a secure platform that was not compromised.

The live database is not protected from those who do have permissions to access the database. There are always some administrators, QA, developers, and others with access to the actual live database. This personnel can access actual data records, which are not masked.

For organizations where a test database is not necessary for other purposes, it is wasteful to have a full test database that is a copy of the full production database, minus identifying information. The cost is in the hardware and maintenance of the second system.

Activities have to be performed twice: once on the test system and then implemented on the live system. There’s no guarantee that it will work on the production system, and then the developers or DBAs who need to debug the system will be either debugging on the testing system, or they will be granted permissions that allow them to see the actual live data.

Dynamic data masking: security for live systems

Dynamic data masking is designed to secure data in real time for live production and non-live systems. Dynamic data masking masks all sensitive data as it is accessed, in real time and the sensitive information never leaves the database. When a DBA or other authorized personal views actual data in the production database, data is masked or garbled, so the real data is never exposed. This way, under no circumstances, is anyone exposed to private data through direct database access.

Using a reverse proxy, the dynamic masking tool investigates each query before it reaches the database server. If the query involves any sensitive data, the data is masked on the database server before it reaches the application or the individual who is requesting the data. This way, the data is fully functional for development or testing purposes but is not displayed to unauthorized users.

Dynamic masking allows all authorized personnel to perform any type of action on the database without seeing real data. Of course, activities that are supposed to show data do show that data, but only to the authorized personnel using the correct access. When using advanced data masking rules, it’s possible to identify whether a particular field should be shown to a particular person, and under what circumstances. For example, someone may be able to access one hospital record at a time but only from a particular terminal or IP address, using a specific application and specific credentials. Accessing that same record using a direct database command would not work or would produce masked data.

Concerns with dynamic data masking

Dynamic data masking requires a reverse proxy, which means adding a component between the data query and response. Different solutions exist, some of which require a separate on-premises server, and others that are software-only based and can be installed on the database server.

Furthermore, when a company uses only dynamic data masking and does not have a production system, there are issues associated with performing functions on the live database.

The following concerns should be noted when using dynamic database masking solutions.

• Response time for real-time database requests. In environments where milliseconds are of crucial importance, dynamic masking needs to be carefully tested to ensure that performance meets the organization standards. Even when a particular item of data is not masked, the proxy does inspect the incoming request.
• Security of the proxy itself. Any type of software installed on the database server needs to be secure. And once a proxy is present, you have to enforce that the entire connections to the database are now passing through this SQL proxy. Bypassing this proxy in any way will result in access to the sensitive data without masking.
• Performing of database development and testing on live systems can cause errors in the production system. In many cases, DBAs perform changes on a limited part of the system before deploying. However, best practices would require a separate database for development and testing.

Static vs. Dynamic Data Masking

The main reason to use data masking is to protect sensitive and confidential information from being breached and protected according to regulatory compliance requirements. At the same time, the data must stay in the same structure, otherwise, the testing will not show accurate results. The data needs to look real and perform exactly as data normally would in the production system. Some companies take real data for non-production environments but sometimes the data may have other uses. For example, in some organizations, when a call center personnel views customer data, the credit card data may be masked on screened.

Generally speaking, most organizations will need some combination of dynamic and static database masking. Even when static data masking is in place, almost any organization with sensitive information in the database should add dynamic data masking to protect live production systems. Organizations with minimal development and testing can rely solely on dynamic data masking, though they may find themselves providing some data with static masking to outside developers or other types of contractors.

Advantages of static data masking

• Allows the development and testing without influencing live systems
• Best practice for working with contractors and outsourced developers, DBAs, and testing teams
• Provides a more in-depth policy of masking capabilities
• Allows organizations to share the database with external companies

Advantages of dynamic data masking

• The sensitive information never leaves the database!
• No changes are required at the application or the database layer
• Customized access per IP address,  per user, or per  application
• No duplicate or off-line database required
• Activities are performed on real data, saving time and providing real feedback to developers and quality assurance

In this post, let me show you how to fix SQL Server Can't Connect to Local Host. Many times we find issues when connecting to the SQL Server. It gives us the message “SQL Server is not able to connect to  local host” as you can see on the following picture:

To fix this issue:
Go to Start->Run->Services.msc.

Once the Services are open, select SQL Server and start it, as per the given screenshot, given below:

After you do it, SQL server will be up and running again.

SQL Server sometimes stops because of some problem. These steps help to make it up. Thanks for reading.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this post, I will show you how to Convert String To Color in SQL Server. The following code will describe how to convert String To Color In SQL Server 2016. And now write code below:

CREATE FUNCTION dbo.fn_conversion_string_color(  

@in_string VARCHAR(200)  

)  

RETURNS NVARCHAR(500)  

AS  

BEGIN  

DECLARE @fsetprefix BIT, -- append '0x' to the output   

@pbinin VARBINARY(MAX), -- input binary stream   

@startoffset INT, -- starting offset    

@cbytesin INT, -- length of input to consider, 0 means total length   

@pstrout NVARCHAR(MAX),   

@i INT,   

@firstnibble INT ,  

@secondnibble INT,   

@tempint INT,  

@hexstring CHAR(16)  

SELECT @fsetprefix = 1,  

@pbinin = SUBSTRING(HASHBYTES('SHA1', @in_string), 1, 3),  

@startoffset = 1,  

@cbytesin = 0   

-- initialize and validate   

IF (@pbinin IS NOT NULL)   

BEGIN    

SELECT @i = 0,   

@cbytesin = CASE  WHEN (@cbytesin > 0 AND @cbytesin <= DATALENGTH(@pbinin))  

  THEN @cbytesin  

  ELSE DATALENGTH(@pbinin)  

  END,   

@pstrout =  CASE  WHEN (@fsetprefix = 1)  

  THEN N'0x'  

  ELSE N''  

  END,   

@hexstring = '0123456789abcdef'   

--the output limit for nvarchar(max) is 2147483648 (2^31) bytes, that is 1073741824 (2^30) unicode characters   

IF (  

((@cbytesin * 2) + 2 > 1073741824)  

OR ((@cbytesin * 2) + 2 < 1)  

OR (@cbytesin IS NULL )  

)   

RETURN NULL   

IF (  

( @startoffset > DATALENGTH(@pbinin) )  

OR (@startoffset < 1 )  

OR (@startoffset IS NULL )  

)   

RETURN NULL   

-- adjust the length to process based on start offset and total length   

IF ((DATALENGTH(@pbinin) - @startoffset + 1) < @cbytesin)   

SELECT @cbytesin = DATALENGTH(@pbinin) - @startoffset + 1   

-- do for each byte   

WHILE (@i < @cbytesin)   

BEGIN   

-- Each byte has two nibbles  which we convert to character   

SELECT @tempint = CAST(SUBSTRING(@pbinin, @i + @startoffset, 1) AS INT)   

SELECT @firstnibble = @tempint / 16   

SELECT @secondnibble = @tempint % 16   

-- we need to do an explicit cast with substring for proper string conversion.     

SELECT @pstrout = @pstrout +   

  CAST(SUBSTRING(@hexstring, (@firstnibble+1), 1) AS NVARCHAR) +   

  CAST(SUBSTRING(@hexstring, (@secondnibble+1), 1) AS NVARCHAR)   

SELECT @i = @i + 1   

END   

END   

RETURN  '#' + UPPER(RIGHT(@pstrout, 6))  

 END  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

OnceSQL Server Full-Text Search with rank values  I wrote a post titled enabling Fulltext search in Azure SQL database discussing Full-Text search in Azure. while using it with one of my databases, needed to show the result of the search ordered by however well they match to the search criteria. in order to sort the result as i need, the best is, get a rank generated for every row and use it for ordering the result. I had used Freetext operate for obtaining the result but if i realized that this can not be achieved using the Freetext function.

The CONTAINSTABLE and FREETEXTTABLE functions return a column named Rank for showing the rank related to the record based on matching. this can be used get the result sorted based on it, showing most relevant records at the top. Remember, the higher value of the Rank generated indicates the best matching.

Now, write the following code:
view plainprint?

    -- Creating a table  
    CREATE TABLE dbo.EmployeeDetails  
    (  
     EmployeeDetailsId int identity(1,1) not null  
     , constraint pk_EmployeeDetails primary key (EmployeeDetailsId)  
     , WorkingExperience nvarchar(4000) not null  
     , ProjectsWorked nvarchar(4000) not null  
     , Resume nvarchar(max)   
    )  
    GO  
      
    CREATE FULLTEXT CATALOG EmployeeCatelog;  
    GO  
      
    CREATE FULLTEXT INDEX ON dbo.EmployeeDetails   
     (WorkingExperience, ProjectsWorked, Resume) KEY INDEX pk_EmployeeDetails  
     ON EmployeeCatelog;  
     -- By default CHANGE_TRACKING = AUTO  
      
      
    -- Once enabled, search can be performed;  
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((WorkingExperience, ProjectsWorked, Resume), 'SQL');  
      
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((Resume), 'SQL');  
      
    -- Get the rank and sort the result using it  
    SELECT t.Rank, e.*  
    FROM dbo.EmployeeDetails e  
     INNER JOIN CONTAINSTABLE (dbo.EmployeeDetails, (WorkingExperience, ProjectsWorked, Resume), 'SQL') AS t  
      ON e.EmployeeDetailsId = t.[Key]  
    ORDER BY t.Rank DESC  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

In This code snippet, i will tell you about how Uploading Excelsheet Data in SQL Server Table. Now, write the followind code:

protected void btnSend_Click(object sender, EventArgs e) { 
    try { 
        string path = string.Concat(Server.MapPath("~/File/" + fileuploadExcel.FileName)); 
        fileuploadExcel.SaveAs(path); 
        string connExcelString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", path); 
        OleDbConnection excelConnection = new OleDbConnection(connExcelString); 
        OleDbCommand cmd = new OleDbCommand("Select [Name],[Mobile],[Email],[City],[DataId],[Date],[Source] from [Sheet1$]", excelConnection); 
        excelConnection.Open(); 
        OleDbDataReader dReader; 
        dReader = cmd.ExecuteReader(); 
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); 
        SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping("Name", "Name"); 
        sqlBulk.ColumnMappings.Add(mapName); 
        SqlBulkCopyColumnMapping mapMobile = new SqlBulkCopyColumnMapping("Mobile", "Mobile"); 
        sqlBulk.ColumnMappings.Add(mapMobile); 
        SqlBulkCopyColumnMapping mapEmail = new SqlBulkCopyColumnMapping("Email", "Email"); 
        sqlBulk.ColumnMappings.Add(mapEmail); 
        SqlBulkCopyColumnMapping mapCity = new SqlBulkCopyColumnMapping("City", "City"); 
        sqlBulk.ColumnMappings.Add(mapCity); 
        //SqlBulkCopyColumnMapping mapState = new SqlBulkCopyColumnMapping("State", "State"); 
        //sqlBulk.ColumnMappings.Add(mapState); 
        SqlBulkCopyColumnMapping mapDataId = new SqlBulkCopyColumnMapping("DataId", "DataId"); 
        sqlBulk.ColumnMappings.Add(mapDataId); 
        SqlBulkCopyColumnMapping mapAmount = new SqlBulkCopyColumnMapping("Date", "Date"); 
        sqlBulk.ColumnMappings.Add(mapAmount); 
        SqlBulkCopyColumnMapping mapSource = new SqlBulkCopyColumnMapping("Source", "Source"); 
        sqlBulk.ColumnMappings.Add(mapSource); 
        //Give your Destination table name 
        sqlBulk.DestinationTableName = "UploadedExcelData"; 
        sqlBulk.WriteToServer(dReader); 
        excelConnection.Close(); 
        UpdateRecords(); 
        lblMsg.Text = "File Data Uploaded Successfully... "; 
        File.Delete(path); 
    } catch (Exception ex) { 
        lblMsg.Text = "Something Went Wrong... Plz Check Excel File "; 
        //string script = "<script>alert('" + ex.Message + "');</script>"; 
    } 
}

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this post, i will tell you about ng ng-options instance but remove the selected items from previous uses in Angularjs. Now write the following code:

    <!DOCTYPE html> 
    <html> 
     
    <head> 
        <title></title> 
        <meta charset="utf-8" /> 
        <script src="Scripts/angular.js"></script> 
        <link href="Content/bootstrap.css" rel="stylesheet" /> 
        <script> 
    var app = angular.module('myApp', []); 
    app.controller('demoCtrl', function ($scope) 
    { 
        $scope.options = [ 
        { 
            name: 'Apple', 
            id: '1' 
        }, 
        { 
            name: 'Orange', 
            id: '2' 
        }, 
        { 
            name: 'Banana', 
            id: '3' 
        }, 
        { 
            name: 'Pear', 
            id: '4' 
        }, ]; 
    }); 
        </script> 
    </head> 
     
    <body> 
        <div ng-app="myApp" ng-controller="demoCtrl"> 
            <div class="form-group col-md-3"> 
                <label for="select1">Select 1:</label> 
                <select ng-model="newForm.select1" ng-options="option.name as option.name for option in options" class="form-control"> 
                    <option value=""></option> 
                </select> 
            </div> 
            <div class="row"> 
                <br> </div> 
            <div class="form-group col-md-3"> 
                <label for="select2">Select 2:</label> 
                <select ng-model="newForm.select2" ng-options="option.name as option.name for option in options | filter: newForm.select1 && {name: '!' + newForm.select1}" class="form-control"> 
                    <option value=""></option> 
                </select> 
            </div> 
        </div> 
    </body> 
     
    </html> 

HostForLIFE.eu AngularJS Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this post, I'll share few useful SQL queries for database analysis on SQL Server 2012. I shared few SQL queries useful in analyzing database, which I use quite often. This query will return all table names and no.of rows in it for built-in tables.

    -- List all table names and number of rows in it for user-defined tables 
    SELECT distinct t.name,prt.rows 
    FROM sys.tables t INNER JOIN sys.partitions AS prt 
    ON t.object_id = prt.object_id where t.is_ms_shipped=1 -- 0 for user-defined tables 
    order by prt.rows desc 

This query will return column names and its data type of a table.
    -- Get column names and its types of a table 
    SELECT cols.name,t.name 
    FROM sys.objects o join sys.columns cols on o.object_id= cols.object_id 
    join sys.types t on t.system_type_id=cols.system_type_id 
    and o.name='Employee'-- Table Name

This query will return file name, its size and file group name of a database.
    SELECT sdf.name AS [FileName], 
    size/128 AS [Size], 
    fg.name AS [File_Group_Name] 
    FROM sys.database_files sdf 
    INNER JOIN 
    sys.filegroups fg 
    ON sdf.data_space_id=fg.data_space_id 

Batch file to execute all sql files in a directory, Save it as .bat in a folder that have sql script files to be executed.
    @Echo Off 
    FOR /f %%i IN ('DIR *.Sql /B') do call :RunSql %%i 
    GOTO :END 
    :RunSql 
    Echo Executing SQL: %1 
    SQLCMD -S server1 -U user1 -P pwd1 -d DB1 -i %1 
    Echo Completed SQL: %1 
    :END 

This query will return all table names that have a Foreign key:
    SELECT SCHEMA_NAME(schema_id) AS SchemaName, 
    name AS TableName 
    FROM sys.tables where OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 1 -- Return all

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.