Dienstag, 6. Dezember 2016

sqlcmd nowadays

Hi,

it is quite uncommon for MS-SQL Server-DBAs to work in command line (or much more uncommon in comparison to oracle or pg-sql DBAs). Nevertheless there is a tool from the stone-age called sqlcmd which has very nice features and which is actively developed by Microsoft up to now. See here: http://www.sqlserverspecialists.com/2012/10/sqlcmd-commands-sql-server-tool.html and https://msdn.microsoft.com/en-us/library/ms162773.aspx .

Main thing is:
- connect to different db
- call dos commands (mixing up OS and DB)
- set and use variables
- load sql files for execution
- redirect output to filestreams (file, stdout, stderr)
- change output format to XML

... even if this sounds quite limited it is still helpful for many purposes like e.g.: scheduled tasks. Especially the OS / DB combination is often very useful if you need to wait for the execution of A to start db-job B.

In SQL Server Management Studio the so called sqlcmd-mode to be used inside the query window is implemented which allows a limited feature set in comparison to the command line features seen above. See: https://msdn.microsoft.com/en-us/library/ms174187.aspx

Using SQL Server Agent you need to use an Operating-System Job-Step to work with sqlcmd (like with any other OS application). See an article about differences between TSQL-JobSteps and CmdExec using sqlcmd here: http://www.travisgan.com/2014/04/sql-server-agent-job-and-sqlcmd.html

So finally: with sqlcmd scripting can be improved by using variables, call different scripts and call the OS. In the original command line version it has some possibilities (like opening a dedicated admin connection) which are nice, but another very helpful thing is that it is kept small and performant.

Kr,
Daniel

simple secure data safe

Hi,

today I added a new github repo which contains a script toggling a folder to be

1) a password secured file or
2) a data folder to work in

https://github.com/starkeeny/SSDS

I will go on working on this, but I think it is a nice start for securing my data.

Btw: there is a nice hint from codeproject related to the topic of securing data http://www.codeproject.com/Articles/1151836/How-to-hide-your-files-in-windows-using-unmounted , but I think to remove the drive letter from a drive is not my favourite solution. Nevertheless as a user I wouldn't have expected to find data on an "un-named" drive... so this is a nice and still simple trick...


Kr,
Daniel

Samstag, 3. Dezember 2016

SQL Server - Providing Demo Data (temporary)

Today I found a new solution to provide temporary data! Additionally to creating a table (not really temporary), creating a temp table (create table #xy) and declare a table variable (declare @xy table(...)) there is also the possibility to put values directly into a select statement:

select * from
(
  values 
    (1,2,3),
    (2,3,4),
    (3,4,5)
) as myValues (nr1, nr2, nr3);


this looks quite easy and can be very useful in a with block, because you can use this adhoc-values like a table and if it is necessary you can move them very easily into a table.

with config as (
  select * from ( values ('dataFolder', '/home/...') as config (key, val)
)
select 
  dataFolder.Value + data.FileName, data.Content
from 
  data, config dataFolder
where 
   dataFolder.Key = 'dataFolder'


... but this is probably not the best show case... I think demonstration / training about SQL would be a better use-case (e.g.: showing how join statements work or stuff like this... would be the first training explaining "with" before "join" :-) ... nevertheless I was fascinated and love this feature...).

kr,
Daniel

Dienstag, 29. November 2016

Powershell-code inside of my application

Hi,

I wanted to combine powershell and my .net application. In fact this works quite easily. The following link was quite helpful to make this work: https://blogs.msdn.microsoft.com/kebab/2014/04/28/executing-powershell-scripts-from-c/

We see here that an assembly has to be added (System.Management.Automation), but that's more or less it... I encapsulated the invocation of the powershell code into the following class


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
using System.Management.Automation;
using System.Collections.ObjectModel;

class PSExecutor
{
    protected Collection<PSObject> Execute(string command, params Tuple<string, object>[] parameters)
    {
        using (PowerShell PowerShellInstance = PowerShell.Create())
        {
            // add a script that creates a new instance of an object from the caller's namespace
            PowerShellInstance.AddScript(command);
            parameters.ToList().ForEach(x => PowerShellInstance.AddParameter(x.Item1, x.Item2));

            return PowerShellInstance.Invoke();
        }
    }
}

... so we can use this class as a base class (if you want to call the execution of the code directly: change method Execute to be public). The following snippet is an example how to use the base class:



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    // https://gallery.technet.microsoft.com/Get-WindowsUpdatesps1-7c82c1f4
    class GetWindowsUpdatePSExecutor : PSExecutor
    {
        public Collection<PSObject> Execute()
        {
            return base.Execute(
@"
$UpdateSession = New-Object -ComObject 'Microsoft.Update.Session'
$UpdateSearcher = $UpdateSession.CreateUpdateSearcher()
$SearchResult = $UpdateSearcher.Search(""IsInstalled=0"") # ""IsInstalled=0 and Type='Software' and IsHidden=0""
$SearchResult.Updates |Select-Object -Property Title, Description, SupportUrl, UninstallationNotes, RebootRequired
");
        }
    }

... with the snippet above you can check the OS for pending windows updates.

kr,
Daniel

Freitag, 16. September 2016

SQL Server: GO with count | delete table in chunks

Hi,

I was asked to delete parts of a table. Unfortunately the amount of rows was to big to delete them all in a single shot (TLog would exceed the file limit). I tried a different approach deleting the table in chunks using a loop, but this still executes as a single transaction.

Then I thought I could solve the problem by using a top statement and using go... afterwards I would copy these lines some thousand times and go for a coffee... but than the msdn article about go ( https://msdn.microsoft.com/en-us/library/ms188037.aspx ) opened up a very nice alternative.

Go can have an argument "count" with the following description:

count
Is a positive integer. The batch preceding GO will execute the specified number of times.

... nice, but how will it be executed? Statement-Block COUNT times and then one go (same problem like in the loop scenario) or is it a COUNT times series of statement, go, statement, go,...

I was not able to find the answer so I checked it myself with the following code:

waitfor delay '00:01:00'
go 3

the session-details of the activity monitor showed the single waitfor - statement. Proof enough that go with count creates a statement-go-statement-go series which perfectly solves my problem of the first section (see https://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx ).

delete top (5000) from table where x = 123;
go 10000

kind regards,
Daniel

Mittwoch, 3. August 2016

Dates in WCF - JavaScript communication over JSON

Hi,

today we had really big troubles with WCF and javascript communication. We wanted to send dates over the wire and use its value for additional libraries (which rely on type Date). After hours of playing around I finally solved it with JSON.parse.

Different approaches starting from overriding the WCF object serializer till writing a custom json parser libraries had been tried but finally all these are very unstable.

Resources I found to solve the problem
- Hanselman ... describing my problem
Stackoverflow ... similar issue (solution mentioned)
- Documentation ... after knowing the solution a perfect page :-)

JavaScript/HTML testproject follows...

kind regards,
Daniel




 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
<html><head></head><body>

<script>
function test() {

var objectString = '{"name" : "Daniel", "date" : "\/Date(1293034567877)\/"}';

//___________________________________________________________________________________
//

var objectDataWithParse =  JSON.parse(objectString);

var objectDataWithDate = {
    "name": objectDataWithParse.name,
    "date": new Date(parseInt(objectDataWithParse.date.substr(6, objectDataWithParse.date.length-8)))
};

function parseReviver(key, value) {
    var a;
    if (typeof value === 'string') {
        if(value.substr(0, 6) == '\/Date(') {

            alert(value.substr(6, value.length-8));
            return new Date(parseInt(value.substr(6, value.length-8)))
        }
    }
    return value;
};

var objectDataWithReviver = JSON.parse(objectString, parseReviver);

//___________________________________________________________________________________
//

var outObj = {};
outObj = objectDataWithDate;
outObj = objectDataWithParse;
outObj = objectDataWithReviver;

//___________________________________________________________________________________
//

document.getElementById('outputDIV_json').innerHTML = objectString;
document.getElementById('outputDIV_name').innerHTML = outObj.name;
document.getElementById('outputDIV_date').innerHTML = outObj.date;

};
</script>

<div>
<button onclick="test()" >test</button>
</div>
<hr />
<div id="outputDIV_json" ></div>
<div id="outputDIV_name" style="float:left;margin-right: 5px;margin-top: 5px; background: lightgreen"></div>
<div id="outputDIV_date" style="float:left;margin-right: 0px;margin-top: 5px; background: orange"></div>

</body></html>

Sonntag, 26. Juni 2016

Castle and WCF (3)

Hi,

I went through hell to get this stuff work, but finally I got it. As partly mentioned in part 2 of this series I wanted to create an IIS hosted rest-service without the need of adding a svc-file. So I created an empty web-project, added a global.asax file and added the following code in the Application_Start method:


WindsorContainer
 container = new WindsorContainer();


ServiceHostFactoryBase factory = new WindsorServiceHostFactory<RestServiceModel>(container.Kernel);
container.AddFacility<WcfFacility>(f => f.CloseTimeout = TimeSpan.Zero)
  .Register(Component.For<IMyService>()
                     .ImplementedBy<MyService>()
                     .LifeStyle.Is(Castle.Core.LifestyleType.Singleton));

RouteTable.Routes.Add(new ServiceRoute("MyService", factory, typeof(IMyService)));

There is no need to configure the service in the web.config-file except ASP.NET Compatibility which is needed by the Routes.Add code-line.

kind regards,
Daniel