Tuesday, September 5, 2017

Enron data on github

I've posted the Enron data on GitHub, found here:

https://github.com/busynovadad/EnronData


Tuesday, August 29, 2017

Why can I not use SQLPS to run this?

Here's the scenario:

I have a script, loaded into C:\temp\checkps.ps1, on a remote dev SQL Server, whose execution policy has been set to unrestricted. (Because you wouldn't allow that on a Prod server, would you?)

Let's say that file has the following body:

 Import-Module Sqlps -DisableNameChecking  
 Get-Module -ListAvailable -Name Sqlps  

(Incidentally, that's from this MS docs page)

I want to execute something like this in SSMS:

 Exec xp_cmdshell 'powershell.exe -file c:\temp\checkps.ps1 -ExecutionPolicy Unrestricted'  

And instead, I got this error:

invoke-sqlcmd is not recognized in windows powershell
The term 'invoke-sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
Import-Module : The specified module 'Sqlps' was not loaded because no valid

"Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory."
also, got this

Invoke-Sqlcmd : The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:1 char:1


So, I wound up having to do three things.

First, I downloaded and ran the Powershell extensions installer on the server itself.

Second, to copy the SQLPS module directory from where it was, to here:

C:\Windows\system32\WindowsPowerShell\v1.0\Modules

Note that I first had to get the location of SQLPS, as in running this in poweshell:

$env:PSModulePath


Third, I had to grant the service account full control of the script location through Windows right click menu (Properties >> Security >> Add >> Add user account >> Full Control )











Tuesday, August 15, 2017

Why use Enron data, anyway?

From Wikipedia's article on the same:

The Enron Corpus is a large database of over 600,000 emails generated by 158 employees[1] of the Enron Corporation and acquired by the Federal Energy Regulatory Commission during its investigation after the company's collapse.[2]

This makes it ideal, because:

  • It's public
  • It's relational
  • It's relatively numerous rows, but still sits inside a 60 GB VM


(Another really good candidate, if you have the VM / hard drive space is the Stack Overflow data set)

Tuesday, August 8, 2017

How does one get Enron data?

For upcoming samples, I'm going to be using Enron data.

Some places to find this information:

https://www.cs.cmu.edu/~./enron/

Others:

https://www.opensciencedatacloud.org/publicdata/enron-emails/

https://www.kaggle.com/wcukierski/enron-email-dataset


Tuesday, August 1, 2017

Death of retail due to the cloud, so to speak


This seems insane to me - Payless is going to close 1,000 stores.

http://www.msn.com/en-us/money/companies/these-haunting-photos-of-the-retail-apocalypse-reveal-a-new-normal-in-america/




There's a lot of ink spilled on the impact of "the cloud" to the DBA, but ultimately, if you knew what motivates people to buy shoes, one hopes that one could find a job using that knowledge as either a transferable skill or a direct application, such as marketing director for a shoe company.

I would suggest this is that same pattern that has always been - if you're good at something with a business application and committed to lifelong learning and growth, one industry (retail) declining just means the growth and opportunities have a new name, not that they've gone away.


Tuesday, July 25, 2017

What the tools? No SSDT in VS2017

In Visual Studio 2017 Community, I can't develop SSIS packages out of the box like I could with Visual Studio 2015. I went looking, and they're doesn't appear to be a SSIS SSDT for VS2017.





Instead, I had to install VS2017 Community Edition, and head to the marketplace to find tools that I used to be able to get as a part of the SSDT download.

Report Projects for Visual Studio

Analysis Services Modeling Projects


But I still need to edit SSIS packages, which, according to the blog at this MSDN link, support is "forthcoming".

Argh.

Back to Visual Studio 2015 for a while longer, I guess.

Tuesday, June 6, 2017

SSIS Error: System.IO.IOException: The process cannot access the file "xxxxx\project.ispac" because it is being used by another process.

The .ispac file is locked by the debug process, which is a normal function of debugging. However, if the debugger crashes (or if Visual Studio itself dies/crashes) then it doesn't let you execute your SSIS package in Visual Studio.

You will likely get the error:
System.IO.IOException: The process cannot access the file "xxxxx:\Project.ispac" because it is being used by another process.

So! To fix this apparent insanity, you have to kill the processes (SSIS Debug Host and/or SSIS Debug Host (32 bit)) and restart Visual Studio.


Tuesday, January 10, 2017

Kerberos Error

Of all the things, unexpected authentication errors from Kerberos are my least favorite. Mostly because, once I get the authentication set up, it's one of the things I expect to change the least.

I got the following error while trying to log in to the my lab:

Cannot connect to SQL Server.
The target principal name is incorrect. Cannot generate SSPI context.


Turns out, it was cached settings to blame, and running the following commands flushed the old settings and allowed a successful connection.

  • klist purge
  • ipconfig /flushdns







Tuesday, January 3, 2017

Joining a domain

When I wrote the how-to a few months ago, I forgot to jot down how to join the domain in both Windows 10 and from the command line.

To join an existing domain from Windows 10:

Right click on the Start button
Choose System from the pop up menu
Choose Advanced System Settings from the menu on the left
Choose the Computer Name tab
Click Change
Click Domain, and follow the prompts to enter your username and password


From the command line, for example, from a new test VM:

Open an elevated command prompt, and then type
netdom join machinenamegoeshere /domain:domainnamegoeshere