SQL Server

Could not load file or assembly Microsoft.SqlServer.Management.Sdk.Sfc

Try to add ADO.Net Entity Data Model by using Visual Studio 2013 and having SQL Server 2008 R2 and got the error "Unable to add data connection.", so change my mind and try the same by using Linq to SQL Classes and again go the same error. As usual I try some other options to connect to the database but still the error message is same so decided to explore to fix it. Here is the complete... By Ali Adravi   On 06 Nov 2014  Viewed: 17,913
  • sql server
  • vs 2012
  • vs2013

Get N random records from a table in SQL Server

How to get N rows from a table in Sql Server database? it is difficult to get random Ids of the tables by using any logic, we will need to loop to create N ids but the technique we will explore here will not need any looping or any Id creation in temporary table but the very simple ways to achieve our goal So let's take the very first one We have function in Sql Server "NEWID()", do you... By Ali Adravi   On 03 May 2014  Viewed: 548
  • sql server

Handle error in your database base by using using a single procedure

How to handle all the error in sql server database, it is very easy, you will say the same after reading this article. If I ask the same question right now many of us start thinking how we can use it, do we need to create a table, procedure and what would be useful values which should be in this table and how to call the procedure from every procedure, how to page the parameter etc. stop thinking... By Ali Adravi   On 23 Feb 2014  Viewed: 414
  • sql server
  • procedure
  • error-log

update multiple table with transaction between try catch in sql server

I need to create script for entire database changes every week for my client to run on production server, when I was creating the script I need to check whatever I have created so I try to use transaction and to roll back every change, so I used try catch but it inserted many records in some master tables while there was some error in script and still error was not catching by try catch where i... By Hamden   On 11 Oct 2013  Viewed: 707
  • sql server
  • try..catch

Sql reporting services first report

SQL Reporting services also known as SSRS is a great tool to create report from database. In this article we will learn how to create a basic Report. We will learn how to create a shared data source so we can use it for all the reports in our project, How to create our data set to bind columns in report. For advanced report see my other article [SSRS subreport with drill down step by step in sql... By Ali Adravi   On 10 Sep 2013  Viewed: 727
  • ssrs
  • report
  • sql server

SSRS subreport with drill down step by step in sql server 2008

sql reporting subreport with toggle To create a toggle sub report in sql reporting service is too easy but if you will not aware about it then it is really difficult for you. So in this article we will see how to create a report with having sub report, and sub report data will load only when we will toggle (0pen) the sub report and not on the load of main report. To learn basic of reports see... By Ali Adravi   On 09 Sep 2013  Viewed: 8,936
  • ssrs
  • report
  • sql server

Parameterized view in SQL Server with example

Parameterized view means we can pass some value to get the data from the view the table by using view. The parameter accepts values that can be supplied later by prompting the user or programmatically, it is possible in MS-Access and FoxPro but is it supported in SQL Server? Answer is big NO, but there are ways to achieve the same feature, which we will discuss in this article. By using view... By Ali Adravi   On 07 Sep 2013  Viewed: 1,024
  • sql server
  • cte
  • udf

Get only date or time from a datetime column in sql server

In Sql Server we use DateTime column but in many cases we need to get either only date or only time. So we will see different ways to get these values according to our requirements. If you are using SQL Server 2008 or newer version then luckily we have two types Date and Time Let’s see this in action with Sql Server 2008 (it will not work in older version of SQL) SELECT Getdate()... By Hamden   On 07 Sep 2013  Viewed: 469
  • sql server

SQL COUNT Function with example

Count function takes one parameter and returns the number of rows for the matching criteria. Select Count(*) From employees It will return count of entire records into Employees table, same result can also be get from following sql statement Select Count(1) From employees In this statement we used, 1 in place of *, there are discussion about count(*) and count(1),... By Hamden   On 31 Aug 2013  Viewed: 657
  • count
  • sql server

SQL ISNULL, IFNULL and NVL function with example

ISNULL in Sql Server is uded to check and return alternate value if value is NULL, while to only check the NULL value, we can us IS NULL with space, in MySQL and Sqlite, for same functionality, we can be use IFNULL and in Oracle we can use NVL. Suppose we want to return 0 if column value is null then we can use select ISNULL(columnName, 0) from table_name Column type and alternate... By Hamden   On 31 Aug 2013  Viewed: 2,432
  • sql server
  • mysql
  • sqlite

Delete duplicate records in sql for specific columns

Delete duplicate records from table is the very common question and task we need in our day to day life, so we will see how easily we can delete duplicate records from a table by using subquery and using partition by function supported by Sql Server 2005 and later version. Say the table name is Address, having following records AddressID CustomerID Address City Zip Country 1 ... By Ali Adravi   On 26 Aug 2013  Viewed: 847
  • sql server

MVC insert update delete and select records

CRUD (Create, Retrieve, Update and Delete) in MVC. When we start to learn new language, first we try to run an application with “Hello World” and then CRUD functionality. So in this article we will see how to select records from database (with WebGrid, pagination and sort functionality), update a record, add new record and finally delete a record in ASP.Net MVC. Let’s create a simple table... By Ali Adravi   On 17 Aug 2013  Viewed: 85,618
  • mvc
  • asp.net
  • sql server

UNION VS UNION ALL in Sql Server with Example

UNION and UNION ALL is the way to select records from different tables having the same number and type of columns. Union internally use DISTINCT to remove the duplicate records while Union All don't use distinct and shows all the records even duplicate records. -- Table A -- Table B -- Table C ID VALUE ID VALUE ID VALUE 1 ... By Ali Adravi   On 14 Jun 2013  Viewed: 326
  • union
  • union-all
  • sql server

Bulk insert with text qualifier in sql server

Bulk insert with text qualifier from a text or csv file into sql server can be achieved by using a format file, in format file we can define the sequence of columns in source file (txt or csv file), data type, length, destination table column, version and number of columns, we will see everything in detail with different examples Suppose we have to import following file to our database ... By Ali Adravi   On 10 Jun 2013  Viewed: 15,388
  • bulk-insert
  • sql server
  • csv

New conversion functions in sql server 2012

SQL Server introduced three new conversion functions to convert an expression to another data type, in this post we will see those functions and their usage **Conversion Functions** - PARSE - TRY_PARSE - TRY_CONVERT **PARSE:** This function parse the value and return then result, relies on CLR. It will take some performance overhead. Syntax PARSE (string_value AS data_type [... By Hamden   On 15 May 2013  Viewed: 224
  • sql server

How to get number of days in a month in SQL Server?

Sometimes we need to get the total number of days in month for given date, there is no build in function which can help us to directly use and get our result, so we need to write a small SQL statement to get the total number of days for a particular date. I found on some blog sites to use this but it will not work for every date DECLARE @date DATETIME SET @date = '05/17/2020' ... By Hamden   On 14 May 2013  Viewed: 328
  • sql server

Create procedure with default value parameters

When we create function we can use default value for parameters if we need so, is there any way to create a procedure with default value parameter in SQL Server so when we don’t pass the value for that parameter they can use default values? Yes, we can. In this article we will create procedure with default parameter value. Let's see default template provided by microsoft: SET ANSI_NULLS... By Ali Adravi   On 14 May 2013  Viewed: 1,029
  • sql server
  • procedure

Search a column name into all the table across databases in sql server

How to search a column name in any table (entire database) or how to check a column exists in any table or not, how to check a column belongs to which table, these are very common question we can get on different websites. Some times we know the column name but don't know the column name then we need a script to get the table name to proceed. Suppose we have table with primary key and want to... By Hamden   On 09 May 2013  Viewed: 543
  • sql server

Change column name and data type in Sql Server

How to change column name and data type of a column cannot be done in one statement but first we need to change the column name and then data type, In this post we will check many other things like how to change data type of a column, how to drop a column, how to add a new column, how to make a column not nullable etc. So first we will see how to change a column name as well as data type of... By Hamden   On 05 May 2013  Viewed: 913
  • sql server

How to use comma separated Ids as parameter in sql server?

just think to pass comma separated Id as a parameter or how to use comma delimited value as parameter and use that in SQL server, since there is no way to use parameter as an array so we can use string for multiple IDs or values but if it's integer then we cannot use it in IN clause in SQL Server even if it is string (varchar). So how to use multiple values or IDs as parameter and use that in IN... By Ali Adravi   On 02 May 2013  Viewed: 1,928
  • sql server
12