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: 22,087
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: 914
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: 761
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: 1,652
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: 1,140
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: 11,784
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: 26,718
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: 2,594
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: 1,087
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: 3,167
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: 1,480
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: 106,155
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: 584
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: 20,867
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: 460
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: 614
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,782
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: 910
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: 6,286
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: 10,172