Random Findings as a Developer

July 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!

SET NOCOUNT ON 
 
DBCC UPDATEUSAGE(0) 
 
-- DB size.
EXEC sp_spaceused
 
-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [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 ''?''' 
 
SELECT *
FROM   #t
 
-- # of rows.
SELECT SUM(CAST([ROWS] AS INT)) AS [ROWS]
FROM   #t
 
DROP TABLE #t

Hope this helps you as much as it helped me!

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

June 4, 2012

SSIS and VARCHAR(MAX) / NVARCHAR(MAX)

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).

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 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

April 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)!

Script

DECLARE @spid INT
   ,@stmt_start INT
   ,@stmt_end INT
   ,@sql_handle BINARY(20)
 
DECLARE @ProcessID INT
   ,@Duration VARCHAR(MAX)
   ,@ProgramName VARCHAR(MAX)
   ,@HostName VARCHAR(MAX)
   ,@LoginName VARCHAR(MAX)
 
DECLARE @Processes TABLE
(
 ProcessID INT
,Duration VARCHAR(MAX)
,ProgramName VARCHAR(MAX)
,HostName VARCHAR(MAX)
,LoginName VARCHAR(MAX)
,Query VARCHAR(MAX)
)
 
DECLARE crsProcesses CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT  p.spid
       ,RIGHT(CONVERT(VARCHAR, DATEADD(ms, DATEDIFF(ms, P.last_batch, GETDATE()), '1900-01-01'), 121), 12) AS 'batch_duration'
       ,P.program_name
       ,P.hostname
       ,P.loginame
FROM    master.dbo.sysprocesses P
WHERE   P.spid > 50
        AND P.STATUS NOT IN ('background', 'sleeping')
        AND P.cmd NOT IN ('AWAITING COMMAND', 'MIRROR HANDLER', 'LAZY WRITER', 'CHECKPOINT SLEEP', 'RA MANAGER')
ORDER BY batch_duration DESC
 
OPEN crsProcesses
 
FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName, @HostName, @LoginName
 
WHILE @@FETCH_STATUS = 0
    BEGIN
 
       -- 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
                              END
               ,@stmt_end = CASE stmt_end
                              WHEN -1 THEN -1
                              ELSE stmt_end / 2
                            END
        FROM    master.dbo.sysprocesses
        WHERE   spid = @spid
        ORDER BY ecid
 
        INSERT  INTO @Processes
                (
                 ProcessID
                ,Duration
                ,ProgramName
                ,HostName
                ,LoginName
                ,Query
                )
                SELECT  @ProcessID AS ProcessID
                       ,@Duration
                       ,@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    ::
                        fn_get_sql(@sql_handle)
 
        FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName, @HostName, @LoginName
 
    END
 
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 – Development Models

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

Playing with SQL Source control you can find several neat things. One of them is the different development models.

Available in RedGate SQL Source Control, you can select one of two different development models. They are:

  • Dedicated database development – Each developer has their own copy of the database
  • Shared database development – All developers work on a central copy of the database

Depending on the database development strategy, SQL Source control treats them differently.

Dedicated database development:

  • Useful for deploying databases
  • Assumes that every user may / may not have the latest copy of the code
  • Allows the user to “Get Latest” on a database
  • Each user develops in their own sandbox and commits changes to source control for others to retrieve
  • May have conflicting changes

Shared database development:

  • Strategy used with our normal approach to development
  • Assumes the database has the latest version of the codebase (and data) – “Get Latest” is disabled
  • All users will automatically have the latest version of the code
  • Reduces the risk of conflicts

See the link below for more details:

http://www.red-gate.com/supportcenter/content/SQL_Source_Control/articles/SSC_Development_Models

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.

April 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 http://192.168.0.1 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.

February 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.

http://community.logmein.com/t5/Free-Pro-User-to-User-Support/Mirror-Driver-Issue/td-p/12741

December 27, 2011

Day 2 – D2Plug – The Setup

Filed under: D2Plug,Global Scale Servers — Andrew @ 1:49 am

After my first night of playing with the D2Plug, I read more of the instruction manual and realized that I didn’t actually need to use the JTAG port in order to connect to the console window for the server.  It turns out there is a port conveniently labeled “Console” which when a Micro-B USB port is inserted, this becomes your serial console.  So much for the JTAG board I got with my Guruplug.

It came more or less set up with everything I needed on it built in and ready for me to use.  After an hour and a half of software updates (yay for updating Ubuntu 10.04) and another hour of installation, the system was set up and ready for me to use as a media server (the original reason why I got the Guruplug).

The snag was where to put it.  I tried to take the unit apart at the power supply unit (PSU) and use the plastic pieces to cover it, but that didn’t work too well, the pieces of plastic that came with it to cover this piece of hardware was insanely difficult to get on to the point that I thought that if I were to need to remove it, I would not be able to.  Instead, I ended up just mounting the D2Plug on the wall of the shelf where it will live.

Man was that a much needed swap.  The Guruplug was originally placed in my basement due to the fact that the server sounded like a hair dryer that was constantly going.  Now when I walk downstairs, all I hear is silence.

Both of these products have been great thus far, I look forward to their future releases :).

December 26, 2011

Day 1 – D2Plug

Filed under: D2Plug,Global Scale Servers — Andrew @ 1:09 am

So I got the D2Plug for Christmas and I immediately started playing with it.  Aside from reading the online PDF, I found it relatively hard to figure out what the default password for the “ubuntu” account (account that comes automatically enabled on the server).

Have no fear, the information is here:

Username: ubuntu

Password: marvell

Reference Manual: http://www.globalscaletechnologies.com/download/D2Plug_QSG_20111011.pdf

Now to get the JTag port working as well as my other external keyboard :)

Older Posts »

Powered by WordPress