Importing a file into a Sql Server varbinary column

 

Previously I’ve mostly achieved this by using Powershell, but when the file you want to import resides on the Sql server itself (or somewhere accessible to it) it can actually be done with just a few T-SQL statements.

However I need to dynamically name the the file to be imported, and that takes an extra hoop to jump through because the OPENROWSET doesn’t take variable names as a parameter for the file name. There are a good deal of suggestions on the web on how to do this, but I favour simplicity so my solution really tries to cut it down to the bare necessities.

First i created a “Documents” table for storing my files:

CREATE TABLE [dbo].[Documents](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date]  AS (getdate()),
    [FileName] [varchar](512) NOT NULL,
    [DocumentData] [varbinary](max) NOT NULL,
CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

Then the import function:

DECLARE @FileName VARCHAR(200) = ‘e:\Wheat_vehicles_combine_harvester_farming_john_deere_1920x1200.jpg’;

DECLARE @sql varchar(max) = ‘SELECT ”’ + @filename + ”’,* FROM OPENROWSET(BULK N”’ + @fileName + ”’, SINGLE_BLOB) AS RS’;

INSERT INTO PERH..Documents (FileName, DocumentData)
    EXEC(@sql);

That is really all there is to it – happy coding Smile

PS. Checking for file existence, deleting source file after loading etc can be achieved via the extended stored procedures on your system such as xp_fileexist, xp_delete_file.

Create PDF from Sql with Powershell

Saving the results of a query to PDF would be a neat feature to have in SSMS – but second best is to have a small Powershell script to assist you.

I came across this article which contains a Powershell Module by Dr. Tobias Weltner that nicely wraps  PdfSharp-WPF.dll and then it was jus a matter of processing my own Sql (in this case a list of tables/columns) like so:

Import-Module E:\PowerShell\PDF\PDFTools

$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=LC7207SQL;Database=Watson; Integrated Security=true;”)
$query =
    “
    SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS
    ORDER BY TABLE_NAME, ORDINAL_POSITION
    ”
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter ($query, $connection)
$table = New-Object System.Data.DataTable
$adapter.Fill($table) | out-null

@($table) | Out-PTSPDF -Path $env:temp\report.pdf -Open -A -GroupBy TABLE_NAME

There are a number of parameters sto play with on the Out-PTSPDF module, and if required you can also pipe your data through a formatter, like Format-List, before PDF’ing it.

RegEx in R to extract values between square brackets

Except for RegEx always being a pain, one would think that parsing a string in R is a piece of cake. Well, it is – sort of…

So here goes:

install.packages("stringr")
library(stringr)

formulas <- c(
    "[000275]-[007825]-[007826]-[007827]",
    "[bru]-[007303]-[007301]-[007305]-[007307]"
    )

variables <- regmatches(formulas, gregexpr("\\[.+?\\]", formulas))

Getting R to run on Sql Server 2016

 

I have slowly been moving into the R space for a couple of years, and see it as both an exciting DSL as well as a journey from Sql and C# experience into the realm of serious data analysis and data science (and visualization).

It is a giant contribution that Microsoft is making by making the results of it’s Revolution Analytics acquisition more or less freely available to the world. I was able to attend the EARL conference on R applications in London some weeks back, and it was an exciting view into the world of analytics!

Today I got around to fire up some R on an Sql 2016, with an EARL session by Adam Rich as my starting point. It was somewhat laborious and the documentation and samples around the web were really convoluted or sometimes plain misleading. I really had to cut through a lot of extraneous information and so for future reference, here is what I did.

I had already installed Microsoft R and RStudio (and VS R tools, though I didn’t use them for this) as well as Sql Server 2016 with R, but at first I just wanted to check out the server with a very simple script:

EXEC sp_execute_external_script  @language =N’R’,   
    @input_data_1 = N’SELECT 42 AS TheAnswer’,
    @script = N’OutputDataSet <- InputDataSet’
    WITH RESULT SETS (([TheAnswer] int not null));

This failed with the message:

‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

The answer to this was to run these commands and then to restart the database service:

sp_configure ‘external scripts enabled’, 1; 
RECONFIGURE; 

 

Now it was time to try my hand at an R script that I would write locally on my PC and run remotely on the Sql Server with a trusted connection – in R parlance in a remote compute context – pulling a bit of data. First, however, the Microsoft R Client had to be installed – the version I had apparently didn’t  include the MRO stuff required to connect to the server – you can check the “c:\Program Files\Microsoft\MRO”  directory to verify that you have both an “MRO” and “R Client” directory.

A major problem was this message when setting up the server-connection (RxInSqlServer):

Error in .rxEnsureOdbcCredentials(connectionString, server, databaseName,  : 
  Trusted_Connection or User must be specified either in the connection string with the uid keyword or as a named parameter.
 

Most samples included a connectionstring for the RxInSqlServer-function, but this seems to be unnecessary.  I had to fiddle quite a bit with the parameters for the various special R server functions, ie those prefixed with RX. Also note that sometimes the prefix is Rx and sometimes rx… It appears that 2 connections are necessary: one for the R server computing context and one when executing Sql in the R context. But, to cut a long story short, this is what I got to work as a sort of minimum solution:

# Set up a trusted connection to R in Sql 2016

sqlCompute <- RxInSqlServer(
    server = “LC_SQL”,
    #wait = TRUE,
    #consoleOutput = FALSE,
    #shareDir = “e:/”,

    user = “Trusted_Connection”
    )

rxSetComputeContext(sqlCompute) # Switch script execution to Sql server

sqlConnString <- “Driver=Sql Server; Server=localhost; Database=master; Trusted_Connection = True”;

sqldata <- RxSqlServerData(sqlQuery = “select name, database_id from sys.databases”, connectionString = sqlConnString)
rxGetVarInfo(data = sqldata) # Show our variables
myframe <- rxImport(inData = sqldata) # Get data as a dataframe
print(myframe)
rxSetComputeContext(RxLocalSeq()) # Switch script execution back to local machine

 

Hope this can help someone struggling with the same challenges Smile

Use Powershell function parameters like Write-Host

It would seem straightforward, but I’ve spent a couple of hours googling and experimenting to solve the riddle, so here is what I found.

What I wanted to do, was to write a Powershell function with parameters that work like the Write-Host commandlet, where you can have both named parameters (ie –BackgroundColor) and a number of other parameters (words) that are not positional parameters but get collected into a string like so:

image

I my case I wanted a Text-Out function with an optional –Filename parameter where the output could be appended as well as written to the console like this:

Text-Out Lorem ipsum dolor -Filename "e:\test.txt" sit amet

Here is my Text-Outfunction:

function Text-Out
{
    [CmdletBinding(PositionalBinding=$false)]

    param(
     [string] $filename,
     [Parameter(ValueFromRemainingArguments=$true)]
     [string[]] $remainingargs
    )

    Write-Host ([string]::Join(" ", $remainingargs))

    if (-not $filename) {return}

    Add-Content $filename ([string]::Join(" ", $remainingargs))

}

 

The first point to note is the CmdletBinding, this is required in order that Powershell does not interpret the first word as a positional parameter, ie sets $filename = Lorem, in case the filename named parameter has been left out.

Second, the Parameter(ValueFromRemainingArguments=$true) ensures that all the words that are neither positional nor name parameters as such get collected into an array of strings.

Third, these words are then joined with the .Net [string]::Join() function. If the separating spaces were not needed, the built in Powershell –join function could be used instead. If Add-Content was just given the $remaingargs array, it would write one line for each word.

Can’t install Sql Management Studio on Windows 8, .Net 3.5 missing

It is really odd that it should be so hard getting SSMS 2014 on a Windows 8 box.

The problem is that SSMS needs .Net 3.5, but that the ordinary methods  to get .Net 3.5 installed (Windows Features in Programs and Features) do not work but only result in some very unhelpful messages!

Kunal Chowdry has made a nice description of the whole thing here, but to cut a long story short here is the gist of it:

DISM  /Online  /Enable-Feature  /FeatureName:NetFx3  /All  /LimitAccess  /Source:x:\sources\sxs

You run from an administrative command prompt – remember to replace “x:” whith the path to your Windows 8 installation path, ie a DVD.

DISM (Deployment Image Servicing and Management) has come to my help before. So sad that Windows has always lacked proper package management, so we need to rely on a mix of Control Panel, DISM, Installers, Chocolatey, Boxstarter etc.

T-SQL UNPIVOT’ing columns with different datatypes

Well, you can’t, really. What you have to do is to make sure that all the the columns have the same datatype and then perform the unpivot.

Here is the table I want to partially unpivot:

image

So what I do is to cast all columns to a suitable common format, in this case VARCHAR(100), before the actual pivoting:

SELECT Id, ColumnName, Value
FROM 
( SELECT 
        Id,
        CAST(SysNr AS Varchar(100)) SysNr,
        CAST(KredsNr AS Varchar(100)) KredsNr,    
        CAST(EjendomsNr AS Varchar(100)) EjendomsNr,    
        CAST(Navn AS Varchar(100)) Navn,
        CAST(EjendomStatusId AS Varchar(100)) EjendomStatusId,
        CAST(Aktiv AS Varchar(100)) Aktiv
    FROM Ejendom
) AS S
UNPIVOT
(
    Value FOR ColumnName IN (SysNr, KredsNr, EjendomsNr, Navn, EjendomStatusId, Aktiv)
) AS U

Voila – quite a bit of typing, but pretty easy peasy otherwise.

Switching your IP setting to static with PowerShell

 

In our Hyper-V test environment we use a mixture of dynamic and static IP addresses. Some servers need to have static IP addresses and in order to facilitate the process of provisioning a new server I wanted to have a script that toggles the current network adapter setting from dynamic (DHCP) to static.

Do remember to mark that IP in the DHCP server as unavailable as well in order not to get IP confilcts…

cls
Write-Host "Switch Current IP configuration to to Static IP"

$wmiip = Get-WmiObject win32_networkadapterconfiguration -filter "DHCPEnabled = 'true' and ipenabled = 'true'"

if ($wmiip)
{
    Write-Host "Switching..."

    $ipaddress = $wmiip.IPAddress[0] 
    $ipgateway = $wmiip.DefaultIPGateway[0]
    $ipdnsordr = $wmiip.DNSServerSearchOrder

    $wmiip.EnableStatic($ipaddress, "255.255.255.0")
    $wmiip.SetGateWays($ipgateway, 1)
    $wmiip.SetDNSServerSearchOrder($ipdnsordr)
}
else
{
    Write-Host "Static already set"
}

Get-WmiObject win32_networkadapterconfiguration -filter "ipenabled = 'true'"
"DNS              : " + $wmiip.DNSServerSearchOrder

Write-Host "Done..." 

 

This script was inspired by an article by Scripting Guy

T-Sql to generate a table of datetimes (time series)

The other day I needed a table of datetimes for each second for a given period, and investigated various suggestions of which there are many around the web.

What I came up with as the, in my opinion, easiest solution was this code. I already had a very large table that could provide me with a set of records to iterate over (millions), avoiding some complicated T-Sql looping.

SELECT TOP 86399
        ROW_NUMBER() OVER (ORDER BY Id) AS Number,
        DATEADD(second, 
            ROW_NUMBER() OVER (ORDER BY Id), 
            CONVERT(datetime, 
            DATEDIFF(DAY, 0, GETDATE()))) AS DateTime
        FROM [DB]..[LargeTable]

The number 86399 corresponds to  the number of seconds (One day = (60*60*24) – 1) to generate in the table.