Running a .bat file as administrator changes default directory

When you run a .bat file as administrator ("Run as Administrator") under Windows Vista and 7 the current directory gets set to C:\windows\system32. This can be confusing as this is not the same behaviour as when not running as administrator and can cause problems with your scripts if you use relative paths.

To fix this problem, include these two lines at the top of your .bat file:

@SETLOCAL ENABLEEXTENSIONS
@cd /d "%~dp0"

This will change the current directory to the location of the .bat file.

How it works:

1. @SETLOCAL ENABLEEXTENSIONS - controls the visibility of environment variables and enables cmd extensions.

For more information on SETLOCAL: http://ss64.com/nt/setlocal.html
For more information on cmd extensions: http://ss64.com/nt/cmd.html

2. %0 is the full path and file name of the batch file. %~dp0 Expands %0 to a drive letter and path.

For more information on batch parameters: http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/percent.mspx?mfr=true

Prevent Service Reference from automatically implementing INotifyPropertyChanged

If for whatever reason you don't want the objects generated by your service reference to implement INotifyPropertyChanged you can stop it being generated by using <EnableDataBinding>false</EnableDataBinding> in the *.svcmap file. You have to edit the XML inside the file, its not exposed in the properties window.

<?xml version="1.0" encoding="utf-8"?>

< ClientOptions>

...

< EnableDataBinding>false</EnableDataBinding>

...

< /ClientOptions>

....

</ReferenceGroup>

This makes a lot of sense if you are never going to use these events. You can also achieve this by using the svcutil.exe and not specifing the /enableDataBinding (/edb) command-line argument.

Shrink all SQL Server Databases

A quick way to ensure all the databases on your dev pc/server are all set to simple backup and log files are shrunk to avoid wasting disk space.

You can easily add databases to the list you don't want affected.

declare @ssql nvarchar(4000)

set @ssql= '

if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')

begin

use [?]

Alter database [?] SET Recovery simple

end'

exec sp_msforeachdb @ssql

set @ssql= '

if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')

begin

use [?]

Declare @LogFileLogicalName sysname

select @LogFileLogicalName=Name from sys.database_files where Type=1

--print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,1)

end'

exec sp_msforeachdb @ssql

How to get sysadmin access on SQL Server 2008

If you've ever inherited a PC with SQL Server 2008 installed on it have probably hit the situation where you are a local admin but you can't access the SQL Server instance.This is because SQL Server 2008 unlike earlier versions of SQL Server doesn't by default give sysadmin access to the local Administrators group.

Here is a list of steps that will allow you to gain administrator access - provided you are a windows local admin.

Force SQL server to support mixed-mode authentication

Run REGEDIT
Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
Set "LoginMode" to 2.
(Source: http://support.microsoft.com/kb/285097)
Restart SQL Server.

Force SQL server to let you in temporarily

Go to services.
Stop SQL Server.
Grab the SQL server command-line (right click the service - properties). Mine is:
"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS
Open an administrative command prompt.
Run the command-line from step 3, but add -m -c for single-user maintenance mode command-line.
Open another administrative command prompt.
Run "sqlcmd -S localhost\SQLEXPRESS" from that same directory (replace with your server and instance name)
Now you can do all the stuff everyone told you to do that didn"t work. For example, to create a hero user with administrative access:
CREATE LOGIN hero WITH PASSWORD="123", DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember @loginame = "hero", @rolename = "sysadmin"
GO
QUIT and close the command-prompt
Go to the SQL Server command-line window and hit ctrl+C. It will prompt "Do you wish to shutdown SQL Server (Y/N)?" and enter Y.
Close the command-prompt
(Source: http://msdn.microsoft.com/en-us/library/dd207004.aspx)

Finally, login using your hero

Restart the SQL Server service
Login using SQL Server authentication as the user "hero" with password "123"
*BAM* now you are in. Now give yourself sysadmin access, etc, etc.

How to send an email using Gmail and c#

How you can sent an email in c # using Gmail's SMTP servers. The only trick here is that Gmail requires you to use port 587, enable SSL and supply valid login details.

var client = new SmtpClient("smtp.gmail.com", 587)

{

Credentials = new NetworkCredential("[valid Gmail username]", "[valid Gmail password]"),

EnableSsl = true

};

client.Send("[email from address]", "[email to addression]", "[email subject]", "[email body]");

How to check the progress when Shrinking Database SQL Server

Compacting a large databases in SQL Server can take what feels like forever. If you're like me self doubt is sure to set in as there is no way to tell that the command is progressing in SSMS!
However you can use the system views in SQL Server to see how its going using the following query:

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests

Works on SQL Server 2005+

Source: http://blogs.msdn.com/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx

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]
                             });