2014-10-24

Export binary data field into a file from Microsoft SQL Server, using BCP utility

The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format.

Start a command prompt in the place where you wish the file saved and execute the following command:

BCP "SELECT BinaryField FROM TableWithBinaryField WHERE Id = 1" queryout [OutputFileName] -S [ServerName] -d [DataBaseName] -U [Username] -P [Password]

The first parameter is the query that selects the field that needs to be exported. The italic values have to be specified by you, without the [] straight parenthesis characters around.
If you wish to use a trusted connection then replace the -U Username -P [Password] part with -T.

After executing the command, just press Enter at every question it throws at you.

Here is an examplewhen you wish to specify a username and password:
BCP "SELECT BinaryField FROM TableWithBinaryField WHERE Id = 1" queryout FileName.pdf -S MyServer -d ProductionDatabase -U sa -P secretPassword1

Here is an another example with trusted connection, where no username and password have to be specified:
BCP "SELECT BinaryField FROM TableWithBinaryField WHERE Id = 1" queryout FileName.pdf -S MyServer -d ProductionDatabase -T

2013-12-03

Excel: get number of days in overlapping intervals/dates

The following Excel formula calculates how many days overlap in two intervals.

$B$1 - end of the first interval
$A$1 - start of the second interval
$B$2 - end of the first interval
$A$2 - end of the second interval

=MAX(MIN($B$1, B2) - MAX($A$1, A2) + 1, 0)