Short Fast Code

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server

Problem: I receive this error.

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "xxxx.WORLD".

Solution:

Go to the the OraOLEDB.Oracle provider options and tick "Allow inprocess"

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.

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