(scottish) sql bob blog

rss

Some thoughts of your typical data shepard / data groomer / data dance teacher sort of person.


Why should programmers be more like plumbers, electricians or mechanics

Over the years l have worked with many different people, and observed many people at work.  When working in a mechanical garage l was impressed with the range of tools used by the time served mechanic's.  When a plumber and electrician come to fit a new kitchen l was always was curious about the range to tools to do different jobs.

Today l had a relatively simple task when completing data migration task, which l was doing using SSIS.  Most of the work uses TSQL when possible.  In this case though l was asked convert the a string value from the source for one specific control on one form to Camel Case.  First thought use play to my strengths and use TQL, so quick google, and found this stack overflow posting -> http://stackoverflow.com/questions/5164201/is-there-any-sql-server-built-in-function-to-convert-string-in-camel-case

Ok yes it was possible to use TSQL, the phrase that ran through my mind at that point was "when you have a hammer everything looks like a nail".  Yes l could create the function and call it from the query etc.... 
SSIS is the most wonderful toolbox though (pun intended if you notice it).  One of them is the script task.  Since l only had to convert one control (there is a large number!) why not use some code?  Would that be easier?  Goggling lead to stack overflow posting -> http://stackoverflow.com/questions/1206019/converting-string-to-title-case-in-c-sharp ( This article filled in the gaps for me https://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase.aspx

My code 
public void Main() 

            string fieldName = (string)Dts.Variables["User::str_Field_ID"].Value; 
            string stringValue = (string)Dts.Variables["User::str_NNLFP_VALUE_TXT"].Value; 
            if (fieldName == "XYZ014") 
            { 
               TextInfo textInfo = new CultureInfo("en-GB", false).TextInfo; 
                stringValue = textInfo.ToTitleCase(stringValue); 
                Dts.Variables["User::str_NNLFP_VALUE_TXT"].Value = stringValue.ToString(); 
           } 
Dts.TaskResult = (int)ScriptResults.Success; 


Yes it has taken me time and effort to learn C#, that said it's an excellent additional to my toolbox.  Yes l could have done the same in TSQL, it would have taken probably taken longer. Right tool for the right job?  That l am sure is a matter of opinion and or debate.  Speaking personally for this specific project yes l believe so.  It has encouraged me to keep adding to my own personal IT toolbox......


Win 10 Pro and SQL Server 2016 SSRS report mgr

Ok long story, short. I downloaded and installed SQL server 2016 CTP3 on Windows 10 Pro X64 Virtual machine which was set up for testing purposes. Yes l know that was a silly idea, what the heck living dangerously is fun sometimes! The OS was a standard install and it's standalone as in not connected to a domain or clever stuff. When propmted to enter a user account, l used a personal Microsoft account l have.  Next l installed SQL Server 2016 CTP nothing fancy and choose the native SSRS install

(If you just interested in list of steps l followed they are at the bottom of this posting)

Next logged in as the user account that was used during set up, this is Microsoft account. Now l wanted to play with SSRS, so open the default browser (Microsoft Edge) and entered the url “localhost/reportserver” and waited. Next got a dialog box asking me to enter my user credentials 



So l entered my Microsoft account details, and eventually ended up with the error message below.



Yes l know that I should have remembered, an admin account requires to grant permission on Report Manager to the account you are connecting with “Doh!”. Next step was to see if l could run Microsoft Edge (the default browser) as Administrator, no that was not possible.  As can be seen from the screen shot below it was possible to run command prompt as Administrator, which did not make sense, but not Microsoft Edge.

So off to Google and found this link http://www.ghacks.net/2014/11/12/how-to-enable-the-hidden-windows-10-administrator-account/

So following the instructions, I enabled the administrator account and set the password. Just as an aside l loved the fact when you type in the password, nothing appeared on the screen. Yes l expected some stars at least! So password duly set for the administrators account. Next step was to switch accounts to the Administrator account. At this point l thought “simple run Microsoft Edge as when logged in as the Administrator and set the privilege’s in SSRS report manager”. Got the error message below.



Again Google the rescue and found this page - http://www.virtualizationhowto.com/2015/07/windows-10-edge-opened-builtin-administrator-account/. Followed instructions open Microsoft Edge, restarted Windows was able to run Microsoft Edge as Administrator, "Yippee!!!" Typed in the url “localhost/reportserver”, then eventually got the following error message.


During earlier search found this page http://www.windowscentral.com/how-find-internet-explorer-windows-10-if-you-really-need-it. I had tried this before making the change in this page http://www.ghacks.net/2014/11/12/how-to-enable-the-hidden-windows-10-administrator-account/. So entered ‘Internet ‘ into the search box and selected to run Internet Explorer as Administrator.



Success !!!!!! (see screenshot below) As the saying goes “ a long way for a short cut”, however it works!



So l set about making the relevant changes in report manager, setting up the Microsoft user account l normally log in with as content mgr etc. That all seemed to go as expected. Next switched accounts back to the Microsoft account and thought l will just open Microsoft Edge. Since the account has been set up and l do not need to run it as Administrator. This did not work l got the error message below. So l typed into the search box 'Internet' and Internet Explorer was one of the applications l was able choose to run, the result can be seen below. It worked as expected.



Is there an easier way? Then please let me know, this was not the most fun learning journey, and I’m always open to learn.

So what steps did l follow?

1) http://www.ghacks.net/2014/11/12/how-to-enable-the-hidden-windows-10-administrator-account/

Used instructions to enable the administrator account and set the password for the Administrator account.
Switched accounts from Microsoft account to Administrator account 

2) http://www.virtualizationhowto.com/2015/07/windows-10-edge-opened-builtin-administrator-account/
Used these instructions to make relevant change to the security policy

3) http://www.windowscentral.com/how-find-internet-explorer-windows-10-if-you-really-need-it
Used these instructions to find Internet Explorer and open using "Run as Administrator" option

4) Open SSRS report manager (running as local administrator), set the relevant permissions for the Microsoft account

5) Switched accounts from Administrator account, to Microsoft account. Searched for Internet Explorer then browsed to "localhost/reports" and was able to see SSRS report manager.


A picture can be worth > 1000 words
It's been a interesting time in my new role which l have been in for about 12 months now.  The product l get to work with is fantastic in so many ways for the end user.  The way that is configured allows it to be changed and moulded to fit the users wishes (within reason).  The downside to this is that there is a lot of complexity. 

The team l work with has responsibility for migrating data from our clients existing system to our system.  Speaking for myself, this can be a bit of a Rubik's Cube puzzle of what bit of data goes where and how.  Which speaking as a data geek can be fun.  Its taken me a while to understand both the product and the data model that supports the product and l am still learning very day!

Whilst working on a migration for one client, the form in which we received the data was a set of a large number of spreadsheets.  The information was spread over several spreadsheets mapping the data had been done by my colleague.  During a regular telephone conference with the client, we released that the client was not completely clear on how the data was being mapped from the spreadsheets to the application.  The spreadsheet view was a in a form they as the client understood and trusted (think trusted blanket).   Where as the application was still new shiny complicated and cold.  So my colleague took some screenshots of the spreadsheets, and of the application of where the data was being mapped to.  Using Google Diagrams, they drew some lines showing where values on the spreadsheet was placed in the application.  This was then passed to the client to review.

During the next telephone conference, the client was delighted with this simple diagram.  What my colleague had done was to delight and reassure the client at the same time.  They received from both the client and our own team praise for we saw as a simple task.

As l write this post l am creating some diagrams for another client we are working with.  One their requests was for a data dictionary for the views we provide for reporting.   The data dictionary was to include primary and foreign keys including which tables the foreign keys referenced. I was tasked with this bit of work, which l duly delivered to the client, it when down well.  The client then asked could be do some Entity Relationship Diagrams, with the object names and foreign keys.

At first l did not think this was going to be of much benefit.  All the required information was in the data dictionary after all.  Once l had completed the first one l had to say that my mind was changed.  Even though l had a good grasp of the data model, mapping the data dictionary to the ERD diagram was not as easy and simple as l first thought.  Even worst than that first l was enjoying the process, secondly l was learning as l went along.  Another of our regular meetings came round again.  So l had completed a rough draft of two diagrams, so l presented them.

During the updates l presented the two diagrams, explaining that they where intended primarily for non-technical users who might be required to do some work on reporting.  Much to my surprise the client was delighted, and related that these would prove to be very useful to all users.

The take away for me is that even the simplest scruffiest diagram (back of a paper napkin) can communicate so much more than we might appreciate.  As adults we spend much of time, complicating verbally.  We should from time to time get the crayons out and just draw lines, circles, shapes.  It might be possible to explain in words something.  Yet l am reminded of the simple diagram of joins that has cemented firmly in my mind SQL joins (http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/).  To this day, when l am thinking of a left or right join, that diagram pops into my head.  This says to me that what l need to remember its not how l communicate something, more that l communicate it in a way the client can readily (or instantly) understand.  Sometimes a picture is worth more than a thousand words.