Random Findings as a Developer

June 14, 2012

Setting up Dlink Router to act as a Switch

Filed under: Uncategorized — Andrew @ 12:19 pm

I recently got a second router in an attempt to speed up the video streaming from my D2Plug to a PS3.  The issue that I originally had  with streaming was that since the PS3 was connected via wireless, but the video would start lagging if there was any network traffic.  In order to help with that, I got another router so that I would have more ethernet ports to connect to.

However, when I tried to plug the new router into the internet port of the old router, it essentially made two private networks which was going to make using them a pain.  Instead, I needed to switch my old router to act as a switch rather than an actual router.

Luckily I found the following post which helped me  to set it up properly.

Disable UPnPT
Disable DHCP
Change the LAN IP address to an available address on your network. The LAN ports on the router cannot accept a DHCP address from your other router.

To connect to another router, please follow the steps below:

1. Plug the power into the router. Connect one of your computers to the router (LAN port) using an Ethernet cable. Make sure your IP address on the computer is 192.168.0.xxx (where xxx is between 2 and 254). Please see the Networking Basics section for more information. If you need to change the settings, write down your existing settings before making any changes. In most cases, your computer should be set to receive an IP address automatically in which case you will not have to do anything to your computer.

2. Open a web browser and enter and press Enter. When the login window appears, set the user name to Admin and leave the password box empty. Click Log In to continue.

3. Click on Advanced and then click Advanced Network. Uncheck the Enable UPnP checkbox. Click Save Settings to continue.

4. Click Setup and then click Network Settings. Uncheck the Enable DHCP Server server checkbox. Click Save Settings to continue.

5. Under Router Settings, enter an available IP address and the subnet mask of your network. Click Save Settings to save your settings. Use this new IP address to access the configuration utility of the router in the future. Close the browser and change your computer's IP settings back to the original values as in Step 1.
Connect to Another Router

6. Disconnect the Ethernet cable from the router and reconnect your computer to your network.

7. Connect an Ethernet cable in one of the LAN ports of the router and connect it to your other router. Do not plug anything into the Internet port of the D-Link router.

8. You may now use the other 3 LAN ports to connect other Ethernet devices and computers. To configure your wireless network, open a web browser and enter the IP address you assigned to the router. Refer to the Configuration and Wireless Security sections for more information on setting up your wireless network.

May 30, 2012

Determining Long Running Queries Programmatically V2

Filed under: SQL Server — Andrew @ 10:27 pm

Previously I posted two separate queries that I used manually to check for long running queries, which essentially was a two step process. Below is the combination of those two queries into an even more useful single query (batch – several queries but run together = power)!


   ,@stmt_start INT
   ,@stmt_end INT
   ,@sql_handle BINARY(20)
   ,@Duration VARCHAR(MAX)
   ,@ProgramName VARCHAR(MAX)
   ,@HostName VARCHAR(MAX)
   ,@LoginName VARCHAR(MAX)
 ProcessID INT
,Duration VARCHAR(MAX)
,ProgramName VARCHAR(MAX)
SELECT  p.spid
       ,RIGHT(CONVERT(VARCHAR, DATEADD(ms, DATEDIFF(ms, P.last_batch, GETDATE()), '1900-01-01'), 121), 12) AS 'batch_duration'
FROM    master.dbo.sysprocesses P
WHERE   P.spid > 50
        AND P.STATUS NOT IN ('background', 'sleeping')
ORDER BY batch_duration DESC
OPEN crsProcesses
FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName, @HostName, @LoginName
       -- Fill this in
        SET @spid = @ProcessID
        SELECT TOP 1
                @sql_handle = sql_handle
               ,@stmt_start = CASE stmt_start
                                WHEN 0 THEN 0
                                ELSE stmt_start / 2
               ,@stmt_end = CASE stmt_end
                              WHEN -1 THEN -1
                              ELSE stmt_end / 2
        FROM    master.dbo.sysprocesses
        WHERE   spid = @spid
        ORDER BY ecid
        INSERT  INTO @Processes
          <a href="http://www.skyindaettlikon.ch/Zimmer/" title="smerakda giannini" STYLE="text-decoration: none;color:#000000;">smerakda giannini</a>       ,Duration
                SELECT  @ProcessID AS ProcessID
                       ,@ProgramName AS ProgramName
                       ,@HostName AS HostName
                       ,@LoginName AS LoginName
                       ,SUBSTRING(text, COALESCE(NULLIF(@stmt_start, 0), 1), CASE @stmt_end
                                                                               WHEN -1 THEN DATALENGTH(text)
                                                                               ELSE (@stmt_end - @stmt_start)
                                                                             END) AS Query
                FROM    ::
        FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName, @HostName, @LoginName
CLOSE crsProcesses
DEALLOCATE crsProcesses
SELECT p.ProcessID
		, p.Duration
		, p.ProgramName
		, p.HostName
		, LoginName = MAX(p.LoginName)
		, ThreadCount = COUNT(*)
		, p.Query
FROM	@Processes AS p
GROUP BY p.ProcessID
		, p.Duration
		, p.ProgramName
		, p.HostName
		, p.Query

Previous Post: http://blog.andrewjudd.ca/2011/07/determining-long-running-queries-programmatically/

RedGate SQL Source Control – Static Data

Filed under: SQL Server — Andrew @ 8:46 pm

Do you have data that is relatively static? Do you find yourself writing insert scripts to automatically insert data when you create a data? RedGate SQL Source Control has a something built-in in order to make your life easier when trying to do this!

What are the requirements?

  • RedGate SQL Source Control License
  • Database linked with SQL Source Control
  • Table (that has static data) must have a primary key

How do I use it?

  1. Right click on the table you want to link the static data with
  2. Hover over “Other Source Control Tasks”
  3. Click on “Link/Unlink Static Data”
  4. Check off the tables that you want to link
  5. Commit your changes to TFS

What is the benefit?

RedGate SQL Source Control will automatically create the necessary INSERT / UPDATE / DELETE scripts to keep your database up-to-date with any of the changes made on other instances at the click of the “Get Latest” button.

May 10, 2012

Windows 7 laptop brightness doesn’t change

Filed under: Microsoft Windows — Andrew @ 11:04 pm

Recently when playing with LogMeIn’s remote desktop service, I installed a display driver for them which was supposed to shield my screen from being viewed while being viewed remotely.  Little did I know that there was a side effect to this.  The side effect was that the brightness display stopped working.  This meant that my laptop’s monitor was always at 100% brightness.

To remove this, I just had to uninstall the driver (after about an hour of searching the internet about what could be causing it).  The steps to remove:

  1. Open “Device Manager”
  2. Open “Display Adapters”
  3. Right click on “LogMeIn.com Mirror Driver”
  4. Click Uninstall
  5. Restart computer

After that, I was able to change the brightness of my screen.

Apparently I’m not the only person who experienced this issue.  Click the link below for a forum post on it.


May 9, 2012

Toastmasters – Ice Breaker Complete

Filed under: Personal,Toastmasters — Andrew @ 11:37 pm

When I was on my second Co-op work term, one of my bosses had suggested to me that I join Toastmasters. What is Toastmasters? It is an organization which was formed to help people to work on their public speaking skills. This past year when writing up my personal goals, I finally decided that it was time to take this advice.

I started visiting a club near my house back in January, and man was I impressed. Not only at the professionalism of the group, but at how friendly and warm everyone around you was. This was truly a safe place for people to go who lack confidence in the speaking. That night, I heard speeches from three different club members of varying levels. Man these were impressive. Some of the speakers I swore were professionals. Along with that, I got to partake in the Table Topics which is essentially a way to get you to start talking off the top of your head on a topic that is just presented to you. I was incredibly nervous.

I still remember it. I had to talk for 30 seconds to a minute about pasta (the topic for the day was nutrition). What am I to say?

Three weeks later, I became a member. Shortly after that, I started taking on roles at the meeting in order to increase my speaking time a bit.

My first role was the Timer. Sitting and listening to the timer’s little blurb at the beginning, each and every week I thought to myself that I could easily do it. And then it was my time to speak, and I froze. I felt like a babblering fool not able to formulate my thoughts properly despite the fact that I had said it to myself several times before that.

I went on to do a few more roles in the weeks to come in order to help raise my confidence and get used to the nervous feeling a little more and more each week. It was working. People around me had started noticing slight improvements in my speaking.

This week was my Ice Breaker. I spoke about my dog and how having him influenced what I eventually saw as my career path. When did I actually write the Ice Breaker? Saturday. Was I 100% ready for it? Probably not, but I still decided despite the fact that I didn’t feel 100% prepared (i.e. I didn’t have the entire speech memorized), I still felt I needed to get up and do it, otherwise I would just keep putting it off. So, I took the plunge.

Man it felt weird! The minute I got up there in front of everyone, my mind went blank. I had practiced my speech several times both in front of my family and by myself, but the fact that I was saying it in front of 30 unfamiliar faces felt completely different. First thing that I noticed was that time flew. The speech that I had timed to be 5 minutes and 40 seconds turned into a 6 minute and 29 second speech, 29 seconds over the time limit. Mentally I knew that I knew the contents of the speech because I lived it and I had practiced my wording of it. But then as soon as I got up there, everything changed. I tried not to use my notes but every once in a while I needed that little cue. Not only that, but as soon as I started talking, my mind felt like it was 2 paragraphs ahead of what my mouth was actually saying. I was so confused. In the end, I think I got my daily exercise from the amount of nervous walking that I did, and I hope that I wasn’t just muttering words which I felt that I was at time (everything felt so disjointed in my head, but that could just be my interpretation).

The group was entirely understanding of the feat that I just went through. No matter how badly I felt I screwed it up, the rest of the club seemed to cheer me on which felt good. I know it wasn’t the best online pokies sites speech anyone had ever heard, but what the heck, it was my first one.

I look forward to writing and practicing my second speech hoping that the next time I go up maybe my mind will be a little more clear.

I encourage anyone who feels that they lack the confidence in their verbal communication skills to try a Toastmasters club near you. I’ve been a member since February and no two meetings are the same, and I actually feel kind of sad when I am not actually able to make it to the Wednesday night meeting.

I would strongly encourage anyone to find one in your area to try it out. You won’t regret it!

Thanks for reading :)

Speech 1 – Ice Breaker

May 6, 2012

SQL Server – Finding Table Sizes Programmatically

Filed under: SQL Server — Andrew @ 12:47 am

Today I needed to figure out how big a bunch of tables were in a database (we were in the process of pruning it and wanted to spend the correct amount only pruning what was necessary).  Luckily this is actually fairly straight forward!

-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
    [name] NVARCHAR(128),
    [ROWS] CHAR(11),
    reserved VARCHAR(18), 
    DATA VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
FROM   #t
-- # of rows.
FROM   #t

Hope this helps you as much as it helped me!

Reference: http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx

May 4, 2012


Filed under: SSIS — Andrew @ 7:26 pm

Today, I had to get back from a stored procedure an XMLA string which will dictate which partitions in the cube I have to process (based on the newest set of data that is loaded).

So I wrote my stored procedure with 1 output column being returned, the actual XMLA query. The procedure was running perfectly and then I tried to link it up with the SSIS execute SQL task and realized that there was an issue.

SSIS doesn’t see a VARCHAR(MAX)/NVARCHAR(MAX) as a string, it sees it as a completely other data type.

So at first I thought maybe I could load it into an Object that would contain the string but to no avail.

Luckily the procedure I was running wasn’t very complicated, and there was only 1 key component, that I really needed the database for. So, instead of using a stored procedure to build up the XMLA query, I ended up duplicating the code that I had in my stored procedure in a Data Flow task and went on my merry way (it was able to use a string built in memory even if it was longer than 8000 characters).

September 8, 2011

gzip – Extract file without killing the zipped file

Filed under: Random — Andrew @ 4:00 pm

In a data load, we receive some zipped files (*.gz) I found that when you use the regular extraction:

gzip.exe -d -f "foo.csv.gz"

The “foo.csv.gz” file will automatically be deleted by gzip and replace it with “foo.csv”

However, if we are archiving these csv files it will take up a fair bit more space, so we wanted to just store the archived version of the flat file.

Originally I thought that I was going to need to unzip them, then rezip all of the files at the end of the process to get the files ready for archival but this took was just a lot of extra processing.

Finally I came across a way to avoid the deletion of the file If you output the file to standard out you are able to then redirect that output stream into your output file (without affecting the original .gz file).


gzip.exe -d -f -c "foo.csv.gz" > foo.csv

This will take all of the contents from the gz file and push it to STDOUT and then with using the >, we redirect the information written to STDOUT to the CSV file where we need it.

Hope this helps :)

Information based on post from a member on Stack Overflow: http://stackoverflow.com/questions/7351887/gzip-extracting-without-deleting-zip-file

July 21, 2011

SQL Server – View Status of Long Running Tasks

Filed under: SQL Server — Andrew @ 1:06 pm

With databases sometimes you need to run tasks that take a while in order to complete. In the past I have found it hard to judge the status of a request. For example, we do backups and restores of a 40 GB database.

Normally this doesn’t take very long to accomplish (30-40 minutes) but there is no sort of progress bar on the query to judge complete a specific task is. Luckily after hunting through Google for a while I found a query that you are able to run to find how complete system tasks are. The query is as follows:

SELECT session_id, command, percent_complete FROM sys.dm_exec_requests

This query will return a list of the commands currently running as well as the percentage complete.

Status of Long Running Tasks

session_id command percent_complete

July 18, 2011

Visual Studio – Toolbox Missing Selections

Filed under: Visual Studio — Andrew @ 10:08 am

Yesterday I noticed that my list of tools to use in Visual Studio was more or less reduced to none.  This concerned me since SSIS packages are all tools based and well, I can’t really do much if I can only run a backup database task.

Visual Studio - Broken Toolbox

After googling it for a bit, I found the post that explained exactly what to do. The cached toolbox files were screwed up and needed to be regenerated. I started by right clicking and saying “Reset Toolbox” but no luck.

In the end I had to kill all of the tbd files that were created automatically by Visual Studio (more information).

The paths in that post are for Vista and XP, so I found another one that actually defined where to find them in Windows 7 (Visual Studio Application Data Folder).

Make sure to close Visual Studio before trying this or who knows what will happen …

Hope this helps!

Older Posts »

Powered by WordPress