Short Fast Code: 2013

How to open firewall ports for SQL Server V2

This is an updated version of the original script posted here (http://shortfastcode.blogspot.com/2012/07/how-to-open-firewall-ports-for-sql.html) updated for Windows Server 2012.

netsh advfirewall firewall add rule name="Open Port 80" dir=in action=allow protocol=TCP localport=80

@echo ========= SQL Server Ports ===================

@echo Enabling SQLServer default instance port 1433

netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433

@echo Enabling Dedicated Admin Connection port 1434

netsh advfirewall firewall add rule name="SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434

@echo Enabling Conventional SQL Server Service Broker port 4022

netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022

@echo Enabling Transact SQL/RPC port 135

netsh advfirewall firewall add rule name="SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135

@echo ========= Analysis Services Ports ==============

@echo Enabling SSAS Default Instance port 2383

netsh advfirewall firewall add rule name="Analysis Services" dir=in action=allow protocol=TCP localport=2383

@echo Enabling SQL Server Browser Service port 2382

netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382

@echo ========= Misc Applications ==============

@echo Enabling HTTP port 80

netsh advfirewall firewall add rule name="HTTP" dir=in action=allow protocol=TCP localport=80

@echo Enabling SSL port 443

netsh advfirewall firewall add rule name="SSL" dir=in action=allow protocol=TCP localport=443

@echo Enabling port for SQL Server Browser Service's 'Browse' Button

netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=UDP localport=1434

@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)

netsh firewall set multicastbroadcastresponse ENABLE

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

I was trying to use Simego.SQLImport.exe on 64bit windows 7 and I got this error.

---------------------------

SQL Import Studio

---------------------------

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

---------------------------

This is because if your application runs in 64-bit mode, all of the components it uses must also be 64-bit. There is no 64-bit Jet OLE DB Provider, so you get the message described. You would receive a similar error when trying to connect to a database using OLE DB or ODBC if there is no 64-bit version of the specified OLE DB provider or ODBC driver.

This problem only occurs in applications that run in 64-bit mode. Compiling the application so it runs only in 32-bit mode is the best current solution.

In this case the easiest was to fix this was to use corflags to force it to run in 32bit

corflags /32bitreg+ /force Simego.SQLImport.exe

Problem Steps Recorder Command Line Arguments

Problem Steps Recorder is a cool tool in Windows 7+ and Windows Server 2008 that can record a users actions as a series of images. See this technet article for more information about the tool. Here is the list of command line arguments you can use to automate the tool.

psr.exe [/start |/stop][/output ] [/sc (0|1)] [/maxsc ]
    [/sketch (0|1)] [/slides (0|1)] [/gui (o|1)]
    [/arcetl (0|1)] [/arcxml (0|1)] [/arcmht (0|1)]
    [/stopevent ] [/maxlogsize ] [/recordpid ]

/start         :Start Recording. (Outputpath flag SHOULD be specified)
/stop          :Stop Recording.
/sc            :Capture screenshots for recorded steps.
/maxsc         :Maximum number of recent screen captures.
/maxlogsize    :Maximum log file size (in MB) before wrapping occurs.
/gui           :Display control GUI.
/arcetl        :Include raw ETW file in archive output.
/arcxml        :Include MHT file in archive output.
/recordpid     :Record all actions associated with given PID.
/sketch        :Sketch UI if no screenshot was saved.
/slides        :Create slide show HTML pages.
/output        :Store output of record session in given path.
/stopevent     :Event to signal after output files are generated.

PSR Usage Examples:

psr.exe
psr.exe /start /output fullfilepath.zip /sc1 /gui 0 /record 
    /stopevent  /arcetl 1

psr.exe /start /output fullfilepath.xml /gui 0 /recordpid 
    /stopevent 

psr.exe /start /output fullfilepath.xml /gui 0 /sc 1 /maxsc 
    /maxlogsize  /stopevent 

psr.exe /stop

Notes:
1.    Output path should include a directory path (e.g. '.\file.xml').
2.    Output file can either be a ZIP file or XML file
3.    Can't specify /arcxml /arcetl /arcmht /sc etc. if output is not a ZIP file.

Add a user to Administrator group remotely

A handy way to add a user as an administrator to a PC/Server remotely using Sysinternal PSExec Tool. You can download the suite of products here:
http://technet.microsoft.com/en-us/sysinternals/bb842062

Once the tools are downloaded, find the psexec executable and run the following:

In this scenario, we are adding user "agorilla" to the local Administrator's Group on a server named svrapp01.

Then, from the cmd prompt, enter the below command and hit enter:
c:\Sysinternals>psexec \\svrapp01 cmd

The return will be something like this:

PsExec v1.97 - Execute processes remotely
Copyright (C) 2001-2009 Mark Russinovich
Sysinternals - www.sysinternals.com

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

This opens a remote cmd shell on that server at "C:\Windows\System32.
Next, enter this:

c:\Windows\system32>net localgroup administrators domain\agorilla /add

The return is:
The command completed successfully

Then close the connection:
c:\Windows\system32>exit

The return is:
cmd exited on svrapp01 with error code 0

SQL Server write to EventLog

Getting debug information from a complex stored procedure can sometimes be a challenge in sql server. Do you write to a log table or return the information as part of the query response? Why not use the event log?

Turns out writing to the event log from within a stored procedure is really easy:

DECLARE @@MESSAGE varchar(255)
set @@MESSAGE = 'executing sub query XXX'
EXEC xp_logevent 60000, @@MESSAGE, informational

Determine the file size of a database using t-sql

In SQL Server there are a number of ways to determine how large a database is. Right clicking and selecting properties > files in management studio for example is probably the easiest. However if you do not have very high permissions on the database you are interested in this t-sql may be what you need.

This script requires the least permissions of all the different methods I could find and is fairly simple which is a bonus.

select a.FILEID,
      [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
      [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
      [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)),
      NAME = left(a.NAME,15),
      FILENAME = left(a.FILENAME,30)
from dbo.sysfiles a

Tested in SQL Server 2005+

Font made of dashes

If you are looking for a way to make letters in ms word etc be made up of dots or dashes then you will need a to download a special font. A good example for why you may want this is to help kids learn to write letters by tracing.

Here is one that I have as some success with. Its free and just works: http://desktoppub.about.com/od/lessonplans/ig/Free-Print---Cursive-Fonts/Print-Clearly-Dashed.htm

The good thing about a font rather than a ready made worksheet is that you can make it however you like for example you can make a page with the childs name on it etc.