Tag: SQL Server

Devart LINQ Insight

Posted onAuthorEmilLeave a comment

I was recently approached by a representative from Devart who asked if I wanted to have a look at some of their products, so I decided to try out the LINQ Insight add-in for Visual Studio.

LINQ Insight has two main functions:

  • Profiler for LINQ expressions
  • Design-time LINQ query analyzer and editor

If you work much with LINQ queries you probably know that Visual Studio is somewhat lacking with functionality around LINQ queries by default so the functions that LINQ Insight offers should be pretty welcome for any database developer out there on the .Net platform (which should be pretty many of us these days). Let’s discuss the two main features of LINQ Insight in some more detail.

Profiling LINQ queries

If you’re using Entity Framework (LINQ Insight apparently also supports NHibernate, RavenDB, and a few others but I have not tested any of those) and LINQ it can be a little difficult to know exactly what database activity occurs during the execution of the applications. After all, the main objective of OR mappers is to abstract away the details of the database and instead let the developer focus on the domain model. But when you’re debugging errors or analyzing performance it’s crucial to analyze the database activity as well, and that’s what LINQ Insight’s profiling function helps with.

There are other tools to this of course, such as IntelliTrace in Visual Studio Ultimate, but since it’s only included in Ultimate, not many developers have access to it. LINQ Insight profiler is very easy to use and gives access to a lot of information.

To enable profiling, follow these steps:

  1. Make sure that IIS Express process is not started. Stop it if it is. (This assumes we’re using IIS Express of course. I’m not quite sure how to work with the full IIS server in conjunction with LINQ Insight.)
  2. Open the profiling window by selecting View/Other Windows/LINQ Profiler, or pressing Ctrl+W, F
  3. Press the “Start profiler session” button in the upper left corner of the window (it looks like a small “Play” icon)
  4. Start debugging your application, for example by pressing F5.
  5. Debugging information such as this should now start to fill the profiler window:
    The profiler displays all LINQ activity in the application.

    The profiler displays all LINQ activity in the application.

    As you can see, in this case we have several different contexts that have executed LINQ queries. For example, ApplicationContext is used by ASP.Net Identity and HistoryContext is used by Code First Database Migrations. Context is our application context.

  6. We can now drill down into the queries and see what triggered them and what SQL statements were executed.
    Drilling down into profiler data.

    Drilling down into profiler data.

    We can see the LINQ query that was executed, the SQL statements, duration, call stack, etc. Very useful stuff indeed.

Query debugger and editor

The other feature LINQ Insight brings into Visual Studio is to help writing LINQ queries and debug them. To debug a query, follow these steps:

  1. To open a query in the query editor, just right-click on it in the standard C# code editor window and select the “Run LINQ Query” option:

    To debug or edit a LINQ query, use the right-click menu.

    To debug or edit a LINQ query, use the right-click menu.

  2. If the query contains one or more parameters, a popup will be shown where values for the parameters can be given.
  3. Next, the query will be executed, and the results will be displayed:

    Query results are displayed in the Results tab.

    Query results are displayed in the Results tab.

  4. This is of course useful in itself, and even better is that the generated Sql statements are displayed in the SQL tab and the original LINQ query is in the LINQ tab, where it can be edited and re-executed, after which the Sql and Results tab are updated. Really, really useful!

If an error is displayed in the Results tab, then the most probably reason is that the database could not be found in the project’s config file, or that it could not be interpreted correctly. The latter is the case if using the LocalDB provider with the "|DataDirecory|" placeholder, which only can be evaluated at runtime in a ASP.Net project. To make LINQ Insight find a database MDF file in App_Data in a web project, you can follow these steps:

  1. Make sure that your DbContext sub-class (for Entity Framework, that is) has an overloaded constructor that takes a single string parameter, namely the connection string to use: 
    public Context(string connString) : base(connString) {}

    This is required if LINQ Insight cannot deduce the connection string for the project’s config file. This is usually a problem in my projects since I like to separate domain logic into a separate project (normally a class library) from my “host application”.

  2. Double-click the MDF file in the App_Data folder to make sure it’s present in the Server Explorer panel in Visual Studio.
  3. Select the database in the Server Explorer and right-click it and select Properties. Copy its Connection String property.
  4. In the LINQ Interactive window, click the Edit Connection String button, which is only enabled if the DbContext class has a constructor overload with a connection string parameter, which we ensured in step 1.
  5. Paste the connection string to the Data/ConnectionString field in the panel:
    Use the connection string dialog to override the "guessed" connection string.

    Use the connection string dialog to override the “guessed” connection string.

    Click OK to close the dialog.

  6. Re-run the query with the Run LINQ Query button in the LINQ Interactive window, and it should now work correctly. If it doesn’t, try to Run LINQ Query command in the C# code editor again, since it re-initializes the query.

The ability to freely set the connection string should make it possible to work against any database, be it a local MDF file, a full SQL Server database or a Windows Azure database. This could be used as a simple way to try out new or modified LINQ queries against a staging or production database, right from the development enviroment. Could be very useful in some situations for debugging nasty errors and such.

Summary

All in all, I think the LINQ Insight is a very useful tool and I recommend you try it out if you find yourself writing LINQ queries from time to time.

I should also mention that if you have tried LINQ Insight before and found it be slightly unstable then I should mention that Devart have recently fixed a few errors that really makes the tool much more robust and useful. If unsure, just download the trial version and test it out.

Happy Linqing!

Emil

Connection strings used for logging should not enlist in transactions

Posted onAuthorEmilLeave a comment

I was just reminded of an old truth when it comes to logging errors in a database: Do not enlist the error logging operation in the current transaction, if there is one! The default is to do just that, so be sure to add Enlist=false in your connection string used for logging:

Example:

  <connectionStrings>
    <add name="myDb" connectionString="..."  providerName="System.Data.SqlClient"/>
    <add name="logging" connectionString="...;Enlist=false"  providerName="System.Data.SqlClient"/>
  </connectionStrings>

If you don’t, then the error logging will be rolled back with the transaction in case of an error. Not so good…

Also note that it’s a good idea to use separate connection strings for the normal database operations (which should be done inside transactions) and the logging operations (that shouldn’t).

Now that I have written this down I will hopefully remember this the next time I do error logging in a database…

/Emil

Extracting stored procedure source text from SQL Server

Posted onAuthorEmil2 Comments

It’s been a really long time since the last post so here’s a small SQL Server tip. I recently had to extract the source code for more than two hundred stored procedures from a SQL Server database. Obviously I didn’t want to do that by hand so I wrote a simple script using sp_helptext:

exec sp_helptext proc1
exec sp_helptext proc2
exec sp_helptext proc3
...

This did indeed extract all the source code for the procs but for some obscure reason I had a newline character after every 255:th character in the result. Close but no cigar…

I did some googling without finding anyone with the identical problem (I had already increased the “Maximum number of characters displayed in each column” setting of SQL Server Management Studio). However, it turns out there’s an alternative to sp_helptext, namely the sys.sql_modules table which can be queried like this:

select definition from sys.sql_modules where object_id = object_id('proc1')
select definition from sys.sql_modules where object_id = object_id('proc2')
select definition from sys.sql_modules where object_id = object_id('proc3')
...

This time there were no irritating newlines in the output. Problem solved!

/Emil

Unique constraints in Sql Server do not allow multiple NULL values

Posted onAuthorEmilLeave a comment

For some obscure reason Sql Server does not allow multiple NULL values on columns with UNIQUE constraints. This is in conflict with ANSI standards and also very contra-intuitive.

If you think this is poorly designed, you’re not alone: http://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values

However, there is a way to create a constraint that allows multiple NULL values rather easily (this uses “filtered constraints”, introduced in Sql Server 2008):

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

Credits: http://stackoverflow.com/questions/767657/how-do-i-create-unique-constraint-that-also-allows-nulls-in-sql-server

/Emil

Fixing CLR trigger error after installing .Net Framework updates

Posted onAuthorEmil1 Comment

Today I discovered an error in one of our CLR triggers in a SQL Server database:

System.Data.SqlClient.SqlException: A .NET Framework error occurred during execution of user-defined routine or aggregate "PersonalChangedTrigger": 
System.IO.FileLoadException: Could not load file or assembly 'System.Messaging, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

This was kind of unexpected since we didn’t modify the trigger. It turns out that it was probably a Windows Update patch that modified the System.Messaging assembly so we had to “re-import” it into the database. This is how to do that, in our case:

ALTER ASSEMBLY Messaging
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE

/Emil

Storing and retrieving strings in the SQL Server session context

Posted onAuthorEmil1 Comment

Sometimes it can be useful to store temporary data (e.g. a string with the current username for auditing or similar) in the context of the current Sql Server session and it’s actually rather easy to do. All that’s needed is to convert the data to a varbinary of max length 128 and then call SET CONTEXT_INFO like this:

-- store data
DECLARE @encodedUsername AS VARBINARY(128)
SET @encodedUsername = convert(varbinary(128),'emila')
SET CONTEXT_INFO @encodedUsername

Unfortunately it’s not quite as easy to retrieve the original string… You can retrieve the varbinary data using CONTEXT_INFO(), but that will only give the varbinary data back:

SELECT CONTEXT_INFO()

0x656D696C610000000000000...

Converting that data back to a varchar seems to work at first but the string contains trailing 0-characters (cf. CHAR(0) ):

-- Convert to a string, looks ok...
SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO())
emila

-- ... but the length is 128 characters
SELECT LEN(CONVERT(VARCHAR(MAX), CONTEXT_INFO()))
128

Removing those illegal character is surprisingly difficult (e.g. REPLACE() cannot be used) but one possible solution is to do a conversion into an Xml string:

-- convert to xml for 
SELECT CONVERT(VARCHAR(MAX), (SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO()) AS data FOR XML PATH('')))
<data>emila&#x00;&#x00;&#x00;&#x00;&#x00;&#x00;&#x00;...

Substrings of that Xml string can then be replaced like in any other string and that logic is best put in a user-defined function:

-- Retrieve data from the context and cleanup illegal characters
CREATE FUNCTION GetLoggedInUserName() RETURNS varchar(20)
AS
  BEGIN
    RETURN
      REPLACE(
        REPLACE(
          REPLACE(
            CONVERT(VARCHAR(MAX), (SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO()) AS data FOR XML PATH('')))
            , '</data><data>', '')
          , '</data>', '')
        , '&#x00;', '')
  END
GO


SELECT dbo.GetLoggedInUserName()
emila

The result is now a string of the correct length and without illegal characters.

This code works as expected, but perhaps there is a more efficient alternative out there? Please leave a comment if you have a suggestion for improvement 🙂

/Emil

Templates for SQL Server Management Studio

Posted onAuthorEmilLeave a comment

Templates for SQL Server Management Studio 2008 are stored in this folder on WIndows XP:

C:\Documents and Settings\[user]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

On my new WIndows 7 machine, the location is:

C:\Users\[user]\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

Create sub-folders and SQL files there and they show up in the Template Explorer. Really useful…

/Emil

Finding dependencies on a table column

Posted onAuthorEmilLeave a comment

This will list stored procedures, views, etc. that uses a given table column:

SELECT OBJECT_NAME(sd.id) Dependent_Object,
      (SELECT xtype FROM sysobjects so WHERE so.id = sd.id) Object_Type
FROM sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id
INNER JOIN sysdepends sd  ON so.id = sd.depid and sc.colid = sd.depnumber
WHERE so.id = OBJECT_ID('tblcategorypage')
AND sc.name = 'categorytype'

Credits for this tip to SQLServerCentral.

/Emil

Bitwise operators in T-SQL?

Posted onAuthorEmilLeave a comment

You won’t need them very often but it might be good to know that they’re there. Here’s an example of how to use a bitwise AND in a where clause:

SELECT * FROM uppdrag WHERE (applikationer & 1) > 0

In this example, ‘applikationer’ is a column containing several combined flags and we want to check the setting on the flag in the lowest bit.

The bitwise operators are:

  • & – bitwise AND
  • | – bitwise OR
  • ^ – bitwise EXCLUSIVE OR

Easy way to create temporary tables in SQL Server

Posted onAuthorEmilLeave a comment

Have you ever had a need to create temporary database tables in a SQL Server session or procedure? Are you used to creating them by hand like this:

CREATE TABLE #tmp
( 
    id INT, 
    name VARCHAR(32) 
)

Depending on what you need the table for, then there might be an easier way. Have a look at this short session:

SELECT * INTO mytemptable FROM foobar
SELECT * FROM mytemptable
DROP TABLE mytemptable

The first line creates a new temporary table based on the result set of a SELECT query, and stores the result set in the table. The query can be more complex than in the example, with JOINs, WHERE clauses etc. Incredibly useful when mangling and transforming data!