User Instance login flag is not allowed when connecting to a SQL Server instance: Solved

While working with an ASP.NET MVC application I encountered following exceptions:

clip_image002

Exception clearly says that user instance login flag is not allowed when connecting to a SQL Server instance. For sure this error is related to SQL Server instance I am working on in this particular application. I examined the connection string in the web.config file.

clip_image003

There are two important points about this connection string:

  1. It points to local SQL Server database.
  2. User Instance is set to true

All local database runs under user instance and hence they don’t allow to configure user instance value in the web.config file. Removing User Instance entry should solve this problem. I modified connection string as follows:

clip_image001

And changing this resolved the issue. Hope it helps.

Some Important Points from “SQL Misconceptions” Session from Pinal Dave and Vinod Kumar

image

I attended session of Pinal Dave and Vinod Kumar on SQL Misconceptions. Few of points from that session is as following

  1. SET ROWCOUNT and TOP and OrderBy is not same.
  2. Set RowCount , Top and OrdetBy gives different execution plan
  3. Misconception is Cluster Index gives the result out in Order. It sort and gives result
  4. Seems due to parallelism Cluster Index does not gives result in order
  5. Cluster Index assure u sorted result only if you are explicitly using order by in the query
  6. Precision of small DateTime 31 sec rounded to next minute and 29 sec gets truncated to orevious minute
  7. As of textbook cluster index physically store the data.
  8. Cluster Index does not store in physical order
  9. Go 10 will run 10 times
  10. In simple recovery model your transaction log is as big as longest transaction run in the system.

 

I hope this quick post is useful. Thanks for reading.

Executing SQL Query using LINQ to SQL

In this post, I will discuss how we can execute a SQL Query directly from LINQ.

Explanation

To execute SQL query there is a method in DataContext class called ExecuteQuery

clip_image002

ExecuteQuery takes two input parameter

1. SQL Query as string

2. Parameters used in SQL query

clip_image003

And it returns an IEnumerable.

Example

Below code will execute a simple select statement and it will return IEnumerable<Person>

clip_image005

If you want to pass some parameter in the query, you can pass that as second parameter.

clip_image007

If you want pass input parameter as hardcoded value you can very much do that as below

clip_image009

For your reference source code is as below,


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.Linq;

namespace Relatedtable
{
    class Program
    {

        static DataClasses1DataContext context;
        static void Main(string[] args)
        {
            context = new DataClasses1DataContext();

            var result = context.ExecuteQuery<Person>("select * from Person");
            foreach (var r in result)
            {
                Console.WriteLine(r.PersonID + r.FirstName);
            }

            int idToPass = 1;
            var result1 = context.ExecuteQuery<Person>
                          ("select * from Person where PersonID={0}", idToPass);
            foreach (var r in result1)
            {
                Console.WriteLine(r.PersonID + r.FirstName);
            }

            Console.ReadKey(true);

            var result2 = context.ExecuteQuery<Person>
                          ("select * from Person where PersonID='1'");
            foreach (var r in result2)
            {
                Console.WriteLine(r.PersonID + r.FirstName);
            }

            Console.ReadKey(true);
}
}
}

On pressing F5 you should get output as below,

clip_image002[5]

I hope this post was useful. Thanks for reading Smile

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine