Short Fast Code: July 2012

How to open firewall ports for SQL Server

Slow Way: Real the Books Online article "The Configuring the Windows Firewall to Allow SQL Server Access" (http://msdn.microsoft.com/en-us/library/cc646023.aspx) which contains the information to how to open the required ports in Windows Firewall.

Fast Way: Use this bat script taken from the KB Article "How to open the firewall port for SQL Server on Windows Server 2008" (http://support.microsoft.com/kb/968872)

@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

You can download the bat file from here: https://dl.dropbox.com/u/677104/shortfastcode/OpenSqlServerPort.bat

Works with Windows Server 2008, Windows Vista/7

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