Category: SQL

Users of countless software products use search often to locate what they need. Search, in it’s most simplest form, is to take a search keyword from the user, run down a list and find results which partially or fully (it’s up to you) match the keyword. In this blog, we’ll talk about two features that we feel should be provided with any user-friendly search implementation. These are related to handling search with data having accented and special characters. We’ll see how to implement these using MS SQL Server.

Introduction

Database is a wonderful tool to store information and to look at the data in different ways to make decisions, present an opinion etc. However, the same data can pose a big problem should it fall in the hands of unscrupulous people and the impact can be more devastating if it has sensitive information such as Credit Card/Bank Account numbers, personal Telephone/Email details. For some organizations, it is mandatory to meet stringent data security regulations such as PCI-DSS, HIPAA/HITECH, or GLBA/FFIEC. Hence all such sensitive information must be encrypted in order to protect the customers and avoid data loss in the event of data breach.

This article focuses on how to do data encryption in Microsoft SQL Server by looking at the available options.

MS SQL Server integration service (SSIS) is very widely used in data acquisition that subsequently goes through the ETL (Extract, Transform, and Load) process which makes the data available for immediate use or can be used by other sources as input. Many organizations get data from outside sources in common formats such as CSV or MS EXCEL and those files are dropped into a folder at regular intervals. Since it is humanely not possible to monitor the folder 24×7, it would be ideal to have a monitoring application that kicks into picture as soon as files are dropped into the folder and parses it, and logs any failures occurred during the process.

We’ve encountered an issue with one of our projects based on MVC 3.0 and MySQL. Entity Framework is used as the communication layer between the application and the database. Many stored procedures were used in the application and one day it was noticed that upon refreshing the EDMX file, all the stored procedure information is lost. This was quite frustrating as we’d to rebuild the EDMX file manually even when a small modification is done to any of the stored procedure or a new one is added.

ISSUE:

EDMX file is added with 10 stored procedures and parameter for one stored procedure is modified. To reflect that modification, EDMX was refreshed. Instead of refreshing a single stored procedure, EDMX now shows nothing under the Stored Procedures node, as can be seen in the below image: