Just A Thought
Bytes and Bites of Internet
Accessing XAMPP MySQL database remotely + locally
May 19th
This weekend, I was playing around with XAMPP which I am thinking to use for my quest to become familiar with WordPress development. For those who don’t know what the hack is XAMPP, it is cousin of WAMP and it basically provides development environment for PHP based applications (e.g. WordPress). It is basically combo package of essential modules like Apache, MySQL, PHP and few other add-ons (Mercury and Tomcat). Good thing about using packages like XAMPP or WAMP is when we install them they just configure all components to talk with each other so you don’t need to do any kind juggling for setting up each of those individual components before using them 
. On internet there are many walkthroughs available about to how to setup XAMPP, so I am not going explain that process.
But here in my case, I have a virtual machine where I have installed XAMPP and I wanted to access that MySQL database from my host system. Main reason for that is I never liked phpMyAdmin to manage MySQL instance and MySQL Workbench is by far very flexible and less confusing to use. I already have MySQL Workbench installed in my local system and I wanted to use that application to access database located in my virtual machine. I must say that if it was SQL Server it is very straight forward process but I didn’t expect that it will be really difficult to setup for MySQL ! Because by default use don’t have permission to access MySQL instance remotely and you have to configure server logins to have permissions to access database remotely. And may be I am wrong, but it looks like that users are bound with specific IP so that user has access to server only from that specified IP (or IP range).
MAPS in SSRS
May 6th
In SQL Server 2008 R2 Microsoft introduced really cool new feature … MAPS. With that user can use their existing data on top of maps to have better visualization of information (say customer base distributed across USA). What’s really good about this feature is user can use maps from maps gallery, or spatial data or ESRI (Environmental System Research Institute) shape files, hell you can even add bing maps background on top of it ! In short I think maps add one more tool in BI arsenal 
For this demo, I have used VS 2010 and SQL Server 2012 for generating map report. I have my little table based on AdventureWorks 2012 which I am using as data source for this report. First step is create new report project in VS. And add new report, create new database connection (for data source) and a query (for data set).
select StateProvinceCode,count(1) as TotalCustomers from tablebar
where countryregioncode = 'US' group by StateProvinceCode order by StateProvinceCode
My above query gets count of customers per state and sorted by state. So this report basically shows count of customers located in each state of USA. Once that is done, its time to design the report. All you need is to drop map control in empty report area. As soon as you drop map control in report, it will start layer wizard. This wizard is used to set different settings of map, like map type, data source for map, layout etc.
 |
 |
 |
WHO IS … SSRS Report
Apr 26th
I know it sounds grammatically incorrect … it should say WHAT IS SSRS REPORT ? But this title is very intentional. It just says that WHO IS SSRS REPORT, means “Who Is” report in SSRS. “Who IS” is a query that is used to find details of registration information of any given public IP address.
Recently I helped our network guy in creating a security report. We were using user access log stored in database to generate some sort of simple report to display what / who / how connected to our system using VPN. This report also included an external IP of users who connected using VPN. Today I was just thinking about creating something cool in SSRS, and it came to my mind …. how cool that will be if you can just click on IP of user and get more details of it … like location ?? Or name of City/State ?? I know it is not so easy to get accurate information about users IP because most of the time ISPs have some remote location which is being used as gateway (bare with me, I am not an expert of this topic) and due to that user IP are usually not so accurate (or TOR lovers like me).
Ok, so about report. Actually it is pretty simple report with just an extra kick. For this demo, I created a table which was using following query,
CREATE TABLE [dbo].[loginLog](
[userId] [varchar](100) NULL,
[userIP] [varchar](100) NULL
) ON [PRIMARY]
-- Some dummy data
-- These IPs are of Google’s
Insert into loginLog values('User1' ,'66.102.0.0')
Insert into loginLog values('User6','66.102.0.0')
Insert into loginLog values('User2','66.102.15.255')
Insert into loginLog values('User4','74.125.0.11')
Insert into loginLog values('User','209.85.128.90')
Issue with Backup sets of Backup in SSMS 2012
Apr 19th
Just like many fellow early adopters, me too have installed shiny new SQL Server 2012 in my work machine. And I have been using it quite a while. Since it is “Powered by Visual Studio” now it has look and feel of VS 2010 too (for me main reason was included support of BIDS … or say SSDT 
). As a go-to guy for SQL Server, I am responsible for maintaining all SQL Server instances .. and every now and then I have to play a role of DBA too.
Yesterday I ran into really strange issue. I have to restore a SQL Server 2008 R2 database to another machine with same edition. Since there were couple of devs around my desk to show off I fired up brand new SSMS 2012 (it feels really a bit nice when you have some toy to play with while others looking at you 
… just kidding). All I had to do was to restore database using GUI just to show them how they can do by them self. But with my surprise when I opened up restore screen for database and pointed it to my backup what I saw was really strange (add shocking and disappointing). In new SSMS 2012 you will not see list of all backup sets inside your backup !!! Instead of that you will have to use so called new feature “TIMELINE” by browsing my backups using some stupid date time picker !! And this is really confusing because now I have to guess which backup I have to use because this TIMELINE doesn’t tell me exactly what were the dates when I took backups
To explain this issue I have prepared process which can be used to reproduce this issue. Following steps will reproduce the issue,
Archiving using 7ZIP commandline
Apr 8th
Couple of days back I had to create a script to archive all files from number folders as part of bigger data transfer task. Since that machine was a bit of older flavor of Windows it didn’t had PowerShell, and I had to reboot machine is I wanted to install PowerShell so I decided to use my favorite script language … batch script to accomplish task.
In my script I used WinRAR (because we have licenses for that) but since I have 7ZIP at home machine, I have modified a script to use 7Zip instead of WinRAR (other business logics). I have one folder called “sample” and this script archives all files located inside of this folder and creates a 7ZIP file called Test.7z. It is just one line command,