Get Last Running Query Based on SPID

Handy for tracking down long running queries,  use in conjunction with sp_who2



DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = <SPID>
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)



Source: http://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/

Why doesn't my ControlTemplate get applied in the ItemContainerStyle?

Are the items you are adding to the control UIElements?

Took me ages until I found this obscure reference:

"The container for ItemsControl is normally a ContentPresenter, but if the child is a UIElement then it won't use a container. In this case, all of the children are Controls, so the ItemContainerStyle will apply to them directly. If you added an item other than a UIElement, that setter would set the Control.Template property on the ContentPresenter, which would succeed but have no effect."

http://stackoverflow.com/questions/3542381/specify-controltemplate-for-itemscontrol-itemcontainerstyle/3542399#3542399

Getting around sql server print 8000 max length

Use this stored proc. THe only down side is you get a line break every 8000 charachters :(

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)

AS

/*
Example:

exec LongPrint @string =
'This String
Exists to test
the system.'

*/

/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */

DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */

set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN

IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN

SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END

PRINT SUBSTRING(@String, 1, @CurrentEnd)

set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)

END /*End While loop*/

This was originally posted on SQLServerCentral.com at http://www.sqlservercentral.com/scripts/Print/63240/

How to call Dispatcher.Invoke

Dispatcher.Invoke(

DispatcherPriority.Normal,

(Action)(() => { tbName.Text = text; })

);

How to get version number of assembly

Assembly.GetEntryAssembly().GetName().Version.ToString()

OR

FileVersionInfo.GetVersionInfo("").ToString()

Your current security settings do not allow you to download files from this location.

Problem: I was trying to download something from an ftp server to a windows server 2008 box. and received the following error "Your current security settings do not allow you to download files from this location."

Solution: You need to a an exception in the security section of your Internet Explorer Internet Options.

1. Open IE

2. Open Tools > Internet Options > Security

3. Click on Trusted Sites then the sites button below

4. Enter the URL of the site you're downloading from and click add

5. Done. Retry your download.

Using Beyond Compare with visual studio

Beyond Compare is the best merge and compare tool I’ve ever had the pleasure of using but I have found the experience of integrating it with visual studio can depend greatly on using the right parameters.
Using a different merge/compare tool with visual studio is a fairly straight forward thing to do but if you’re using something like winmerge but beyond compare has a lot more bells and whistles to fiddle with. Below is my preferred setup.

How To:

Under Tools > Options > Source Control > Visual Studio Team Foundation Server

Select Configure User Tools. Add both Compare and Merge operations.

Beyond Compare

Compare:
   Tool: C:\Program Files (x86)\Beyond Compare 3\BCompare.exe
   Arguments: %1 %2 /title1=%6 /title2=%7 /solo
Merge:
   Tool: C:\Program Files (x86)\Beyond Compare 3\BCompare.exe
   Arguments: %1 %2 %3 %4 /title1=%6 /title2=%7 /title3=%8 /title4=%9 /solo

Further Reading: List of other merge tools you can use

Making Xceed excel export use foreign key lookups

Why does print functionality use foreign key values and excel export doesn't? Because Xceed hates you.

There is a workaround by rolling your own excel export class that extends Xceed's ExcelExporter base class though.

Here is it: <download>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Xceed.Wpf.DataGrid;
using Xceed.Wpf.DataGrid.Export;

namespace sfc.Controls.Custom
{
    public class CustomExcelExporter : ExcelExporter
    {
        private DataGridControl _dgc;

        public CustomExcelExporter(DataGridControl dgc)
            : base(dgc)
        {
            _dgc = dgc;
        }

        protected override void StartDataItemField(ExportContext exportContext, object fieldValue)
        {
            // Attempt to find the export column in the master section of the DataGrid
            var column = (Column)_dgc.Columns[exportContext.Column.FieldName];

            // If the column cannot be found in the master section, search the columns of the detail configurations
            if (column == null)
            {
                foreach (DetailConfiguration detailConfiguration in _dgc.DetailConfigurations)
                {
                    if ((column = (Column)detailConfiguration.Columns[exportContext.Column.FieldName]) != null)
                    {
                        break;
                    }
                }
            }

            if (column != null)
            {
                ForeignKeyConfiguration fkc = column.ForeignKeyConfiguration;

                // Check if the column being exported has a ForeignKeyConfiguration and export the mapped value if so
                if (fkc != null && fkc.ForeignKeyConverter != null)
                {
                    fieldValue = fkc.ForeignKeyConverter.GetValueFromKey(fieldValue, fkc);
                }
            }

            base.StartDataItemField(exportContext, fieldValue);
        }
    }
}

One gotacha I found when writing this is that if the foreign key lookup returns a null its going to corrupt your excel document.

Execute all SQL Scripts in a Folder

How to quickly execute a collection of SQL Scripts against a SQL Server Database.

The simplest solution I have found is to use a quick batch script and leverage the osql command. The osql utility is a Microsoft® Win32® command prompt utility for ad hoc, interactive execution of Transact-SQL statements and scripts.


To use the script you will need to:
1. download it from here, This script will execute every .sql in the folder it is executed in.
2. open it up in a text editor of your choice and substitute your connection information for the dummy "[Your XX]" placeholders.
3. Close the file and double click on it to execute.

RunScript.bat

FOR %%x IN (*.sql) DO OSQL -U [Your UserName] -P [Your Password] -S [My Server] -d [My Database] -i "%%x" >> Log.txt

%%x is a variable for the current file. (*.sql) is what selects the files in the folder. Everything after the Do is what is executed for every file.

Note: scripts will execute in alphabetical order. As a bonus the script logs the output to Log.txt

How to know when the sort is changed on the Xceed WPF grid

There is no easy to use sort event on the on the Xceed WPF grid itself however there is a not so obvious way that you can detect when the sort is changed by wiring up the following code.

DataGridCollectionView view = grid.ItemsSource as DataGridCollectionView;

      if (view != null)

      {

            ((INotifyCollectionChanged)view.SortDescriptions).CollectionChanged -=

                                                      new NotifyCollectionChangedEventHandler(SortCollectionChanged);

}

      private void SortCollectionChanged(object sender, NotifyCollectionChangedEventArgs e)

      {

            //do stuff

      }