Use Linq in Codesmith Template

If you want to use Linq in your codesmith templates to filter, organize and sort your metadata the trick to ensure you have set the CompilerVersion to "v3.5" or "v4.0" etc.

Requires CodeSmith 5.1 or above.

Bulk renaming auto-named default constraints

When you create a default constraint on a column you do not have to specify a name for it. In this case SQL Server will assign each generate an ugly name for you like "DF__ ExampleTa __BitF__42D9640B". This can become a real problem if you have multiple instances of your database as the name of the constraint can be different in each database.

This script renames anything named automatically by sql server eg “DF__ExampleTa__BitF__42D9640B” to something that makes sense and will be the same across all databases where you run the script i.e. “DF_ExampleTable_BitFlag”.

BEGIN TRANSACTION

DECLARE @tname nvarchar(MAX)

DECLARE @cname nvarchar(MAX)

DECLARE @dname nvarchar(MAX)

DECLARE @ddef nvarchar(MAX)

WHILE EXISTS (SELECT * from sys.default_constraints d WHERE d.name LIKE '%[_][_]%')

BEGIN

select top 1 @tname=t.name, @cname=c.name, @dname=d.name, @ddef=d.definition

from sys.tables t

join

sys.default_constraints d

on d.parent_object_id = t.object_id

join

sys.columns c

on c.object_id = t.object_id

and c.column_id = d.parent_column_id

WHERE d.name LIKE '%[_][_]%'

PRINT 'alter table [dbo].['+@tname+'] drop constraint ['+@dname+']'

exec('alter table [dbo].['+@tname+'] drop constraint ['+@dname+']')

PRINT 'alter table [dbo].['+@tname+'] add constraint [DF_'+@tname+'_'+@cname+'] DEFAULT '+@ddef+' FOR '+@cname

exec('alter table [dbo].['+@tname+'] add constraint [DF_'+@tname+'_'+@cname+'] DEFAULT '+@ddef+' FOR '+@cname)

END

COMMIT

SQL Server: Can’t access sys.dm_tran_current_transaction

Problem:

User doesn’t have access to sql server management views

select transaction_id from sys.dm_tran_current_transaction

Msg 297, Level 16, State 1, Line 1

The user does not have permission to perform this action.

Solution:

Run the following script. Must be run as a server admin.

USE master;
GRANT VIEW SERVER STATE TO <User>;

ms186327.aspx

Using linq to read csv file

Alternative title: Fastest way to read a csv file into objects

Alternative title 2: Its only one line of code!

File.ReadAllLines("Employees.csv")
                        .Select(x => x.Split(','))
                        .Select(x =>
                             new EmployeeObject
                             {
                                 FirstName=x[0],
                                 LastName=x[1],
                                 DateOfBirth=DateTime.Parse(x[2]),
                                 Department=x[3]
                             });

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()

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.