Thursday, July 18, 2013

How to open SQL profiler in SQL Express LocalDB

SQL Profiler is an extremely wonderful tool to debug the SQL server problems. I personally love this tool very much. Recently while working with one of the project I had to deal with SQL Express LocalDB instance. According to my coding style I was very much sure that, I will need to debug at SQL level using SQL profiler to fix the issues.

As expected one of my stored procedure in SQL Express LocalDB was failing and I tried to debug it using SQL Profiler. You can open the SQL profiler in SQL Server Management Studio from “Tools” menu.

The login window appears where in you will need to put the SQL Express LocalDB server instance name. As a usual practice you may put the server name like for example, (localDB)\MyDB with connection option as Windows Authentication or with credentials for SQL Server authentication. However with this approach the SQL profiler may not open.

To overcome this problem we need to use SQLLocalDB utility. SQLLocalDB utility is command line tool which helps user to manage and perform administrative operations on SQL Express LocalDB instance. Here to connect SQL profiler to LocalDB instance we need to retrieve the correct instance name using SQLLocalDB utility. Open the command prompt and type following line in command line window.
Sqllocaldb info “InstanceName”
InstanceName in above command will be replaced by the instance name of your LocalDB server instance. The “info” command argument return you name, version, and state, last start time and most important “Local Pipe Name” of the specified LocalDB instance. The information is as shown below –
The local pipe name selected and reported in above command output need to be used for making the connection in SQL Server Profiler for SQL Express LocalDB as shown below –

Friday, July 5, 2013

The function import cannot be executed because it is not mapped to a store function

This is very typical error you receive when your underlying database store is changed and there is no update made to EDMX file.
For example, I imported a stored procedure in edmx and saved it. After that I change the stored procedure and do not update edmx. I run the application and at runtime the error is encountered as - “the function import cannot be executed because it is not mapped to a store function”.
This error can be resolved easily by updating the edmx. Following is procedure to resolve the error –
Open the edmx and open Model Browser. Model browser can be opened as shown below.
Find the required stored procedure in model browser. The right click and select option – “Delete from Model” as shown below –
Now right click on any area on edmx file and select the option “Update Model from Database” option. The popup containing the list of Tables, View and stored procedure will appear. Select the earlier deleted stored procedure and click “Finish” on the pop up.
Now again open the model browser and find the added stored procedure. Right click on it and select the “Edit” option as shown below -