eMail Blogging…

I can send emails directly to blog and instantly they are published.  How cool is that. Here is a photo of my little “kick butt” kung fu panda brother Arthur. To setup your email to blog go to: [Link] for more information.

The only problem I ran into was the attachement went before the actual text so I had to manually adjust it as you can see below. I moved the photo below the text, but other then that I like it!

How I did it.

<!–

–>

text Blogging…

This is my first mobile posting that was sent from my cell phone directly to my blog! To setup your text to blog go to: [Link] for more information.

Just FYI: The only problem I see so far with text to blog is that a title is not added to the post. I had to manually set it, so keep that in mind.

<!– –>
<!–
<!–

–>

Windows Powershell…useful tools

If you haven’t heard about Windows Powershell yet then my friend you might want to look into it. I have found several useful tools and one in particular I have used over and over again. First off what is Powershell? Well in-short it is a command-line shell and scripting language. It is now a part of Windows Server 2008. For more detailed information about Windows Powershell visit: Windows Powershell.

To download and install Windows Powershell visit: How to download

Here is what it looks like. Similar to a DOS or command prompt screen.

As far as the tools go…one that is not listed but very useful is the FSUTIL. You won’t find it listed when you run HELP, but you can find more information while in Powershell simply by entering fsutil followed by the enter key.

fsutil file createnew is my favorite for the time being.

I can create blank files and specify their sizes. Hmmm that is freaking awesome. I can create files exactly 1KB, 1MB, 64MB, 128MB 500MB or even 1000MB plus in size.

Why would I need to create blank files with specific file sizes you ask? Well I use SQL Server 2005 quite a bit and monitoring how long it takes to insert data of all sizes into the tables is one of the main reasons. I can get a baseline for the time it takes to insert data. Since all networks are not created equal, with this type of testing I can provide some realistic answers. I usually test from the closest area followed by the furthest area and time the process from start to finish of course.

Bytes [Bytes to Megabytes to Gigabytes calculator]
1048576 = 1MB
1073741824 = 1GB

Stored Procedures…good or bad?

This topic can be an ongoing argument between the parties who swear by Stored Procedures (SP) and the parties that absolutely apose them. Each perspective make valid points, but it is entirely up to you to decide. If you love them that is great and if you hate them that is great too.

I have only been using (SPs) for a short time so I cannot complain, but then again I am not an expert. I do find that (SPs) help me a lot with my daily support. So let’s run through an example of creating a simple (SP) that returns a simple result set. Then we will create a slightly more advanced (SP) that requires a variable in order to return a specific result set. Again these are simple examples!

Let’s just say you have a table called “tbl_Contacts” and in it you have firstName, LastName, DOB, a computed column called AGE and a IsDeleted column with a BIT data type (0 being active and 1 being deleted).

CREATE PROC sp_GetAllActiveContacts
AS
SELECT firstName, lastName, CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (IsDeleted = 0) –Returns all active records

To test your freshly created (SP) run the following:
EXEC sp_GetAllActiveContacts

If you do not have a table called tbl_Contacts don’t worry just use the examples I posted below!

— [ CREATE TABLE ]
CREATE TABLE [dbo].[tbl_Contacts]
(
 [ContactID] [int] NOT NULL,
 [firstName] [nvarchar](50) NOT NULL,
 [lastName] [nvarchar](50) NOT NULL,
 [IsDeleted] [bit] NULL CONSTRAINT [DF_tbl_Contacts_conDeleted] DEFAULT ((0)),
 [createDate] [datetime] NULL CONSTRAINT [DF_tbl_Contacts_conCreateDate] DEFAULT (getdate()),
 [DOB] [smalldatetime] NULL,
 [Age] AS (datediff(year,[DOB],getdate()))
)

— [ CREATE RECORDS ]
INSERT INTO tbl_Contacts
VALUES (1,‘Adam’,‘Sandler’,DEFAULT,DEFAULT,‘9-9-1966’)
INSERT INTO tbl_Contacts
VALUES (2,‘Emmanuelle’,‘Chriqui’,DEFAULT,DEFAULT,’12-10-1977′)
INSERT INTO tbl_Contacts
VALUES (3,‘Rob’,’Schneider’,DEFAULT,DEFAULT,’10-31-1963′)
INSERT INTO tbl_Contacts
VALUES (4,‘Val’,’Kilmer’,DEFAULT,DEFAULT,’12-31-1959′)
INSERT INTO tbl_Contacts
VALUES (5,‘Megan’,’Fox’,DEFAULT,DEFAULT,‘5-16-1986’)

— [ VERIFY RECORDS ]
SELECT * FROM tbl_Contacts

— [ CREATE STORED PROCEDURE ]
CREATE PROC sp_GetAllActiveContacts
AS
SELECT firstName,lastName,CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (IsDeleted = 0)

— [ TEST STORED PROCEDURE ]
EXEC sp_GetAllActiveContacts

— [ CREATE STORED PROCEDURE WITH VARIABLE ]
CREATE PROC sp_GetAllActiveContactsByID ( @ContactID INT )
AS
SELECT firstName,lastName,CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (ContactID = @ContactID) AND (IsDeleted = 0)

— [ TEST STORED PROCEDURE ]
DECLARE @ID INT
SET @ID = 1
WHILE @ID <= 5
BEGIN
   EXEC sp_GetAllActiveContactsByID @ID
   SET @ID = @ID +1
END

Importance of the Connection String…

The Connection String to put it simply: allows you to connect your application front-end to the data store or database back-end. It contains attribute information about the targeted data store or database, for example: the server name, instance name, database name, and connection type to name a few. Without a Connection String all you have is either an interface that may look nice but non functional or a relational database that is ready to work but is just sitting there. The Connection String brings the two together and from there the magic starts to happen.

Developers in general use it on a regular basis. Some use it in the config files, some in the code behind, some in the page or applet using the different dataSource objects. I am a bit biased to SQL Server so I will provide a SQL Server sample…of course!

SQL Server Sample: ConnectionString=“Server=ServerName\InstanceName;Database=DataBase;Trusted_Connection=True;“

Here is an article that dives deeper into the attributes of the Connection String and the syntax. It is geared towards .NET and SQL Server but it is a good article. It is dated back to 2006 but it is still valid. Educate yourself about .NET SQL Server connection string syntax

Please visit ConnectionStrings.com for more information about connecting to other resources such as: MySQL, Oracle, Excel, Active Directory and so-on.

Table Variables…SQL Server 2005

If you have ever created or have used #tempTables in SQL then using Table Variables will be a snap!

the norm for #tempTables are:

CREATE TABLE #tempTable
(
tempID INT NOT NULL,
firstName NVARCHAR(50),
createDate DATETIME DEFAULT (getdate() )
)

INSERT INTO #tempTable VALUES (100,‘Samson’,DEFAULT)
INSERT INTO #tempTable VALUES (200,‘Stephen’,DEFAULT)
INSERT INTO #tempTable VALUES (300,‘David’,DEFAULT)
INSERT INTO #tempTable VALUES (400,‘Peter’,DEFAULT)
INSERT INTO #tempTable VALUES (500,‘Diana’,DEFAULT)

SELECT * FROM #tempTable
DROP TABLE #tempTable



the norm for Table Variables:

DECLARE @tableVariable TABLE
(
tableVarID INT NOT NULL,
firstName NVARCHAR(50),
createDate DATETIME DEFAULT (getdate() )
)

INSERT INTO @tableVariable VALUES (100,‘Samson’,DEFAULT)
INSERT INTO @tableVariable VALUES (200,‘Stephen’,DEFAULT)
INSERT INTO @tableVariable VALUES (300,‘David’,DEFAULT)
INSERT INTO @tableVariable VALUES (400,‘Peter’,DEFAULT)
INSERT INTO @tableVariable VALUES (500,‘Diana’,DEFAULT)

SELECT * FROM @tableVariable
DELETE @tableVariable

WshShell.SendKeys…VBS Script

WshShell.SendKeys…if you think about it there are many things you can do with this. For instance you can turn on or off the CAPS LOCK, NUMLOCK and SCROLL LOCK keys or even write a quick note. I know writing a note is over the top and inefficient but it can be fun to play a trick on your co-workers, friends or family especially if you share a pc! Let’s get started…

The first thing you need to do is open up good ol’ notepad. Then copy and paste the following script into notepad.

‘Start Copy
Set WshShell = WScript.CreateObject(“WScript.Shell”)
WshShell.Run “notepad”
WScript.Sleep 100 ‘This gives notepad sometime to open
WshShell.AppActivate “Notepad”
WScript.Sleep 500
x=0
   do while x < 100
      WshShell.SendKeys “I know what you did last summer!”
      WshShell.SendKeys “{ENTER}”
      WScript.Sleep 100
   x=x+1
loop
‘End Copy

then close notepad and when prompted save the file with what ever name of your liking, but change the extension from TXT to VBS. Then place it in your friend’s startup. Then the next time they log into their computer they will see notepad open and several lines of “I know what you did last summer!” will begin to write automagically! This is great for Halloween pranks!

Visit Winstructor to brush up on addtional functions…Website

Highlight every other row in EXCEL…

You are working on an excel spreadsheet that contains literally hundreds to thousands of rows and instead of manually changing the row color line by line, you want it done automagically!

Before

After

Trick number 1.

Select one of the cells that contain data. Then press CTRL+A. I will choose cell 4D. This will highlight all the cells with data in it. If you have breaks or gaps in your spreadsheet this will not work. It only works on clusters of data. Look at the example below!

Trick number 2.

While the needed porition of the spreadsheet is highlighted, click on “Format” from the file menu. Select “Conditional Formatting…”

Change the “Condition 1” dropdown to “Formula Is” and type in

=MOD(ROW(),2)=0, then click “Format”

NOTE:

(The 0 indicates to skip the first row, change it 1 to include the first row. The number 2 in this formula indicates that every other row needs to be highlighted, so if you change this value to the number 3 then every third row will be highlighted, so-on and so forth. Also if you want to highlight columns simply change the word from ROW() to COLUMN() and now the columns will be highlighted instead. However, if you want both then add another formula!)

Click on the “Patterns” tab

Select the lightest shade of Gray and click ok

Then click ok to close the Condional Formatting dialog screen…

…that is it, there you have it. Every other row is now highlighted!

Windows XP Service Pack 3…

Let’s just say that you need to build an image, reload your desktop, laptop, whatever the need may be…as the norm goes you pop in your XP media, install XP and at some point you’re ready to run Windows Updates. Well one would think to one’s self, I could easily just download SP3, completely bypassing the need to install SP2 along with other hotfixes and save myself a ton of valuable time, boy was I wrong!

You must have at the very least Service Pack 1 installed first in order to install SP3 as I had recently encountered. What? I was able to install SP2 after installing Windows XP and it did not require me to have SP1 installed first. What is the deal here? If you know the reason why this is, then that makes one of us!

So save yourself some frustration and have SP1 or SP2 handy…I wonder if slipstreaming SP3 into a custom install CD would work? Since I am too lazy to do it, I want to see if someone has done it or is planning on doing it? Either way let me know the outcome, I am curious to see if that works.

Normalization…What is it?

Normalization is a process for minimizing the amount of duplicate data to increase the integrity of the data. In order to do so, normalization often involves creating multiple tables and specifying the relationships between the tables. There are multiple forms of Normalization each one was designed to serve as a guideline to ensure that the database is normalized…they range from 1NF through 5NF.

The lowest form of normalization is referred to as the First Normal Form or (1NF). The intent is to eliminate duplicate attributes within a table, separate them into their own group of tables and identify the relationships between the tables.

The next form of nomalization is referred to as the Second Normal Form (2NF) This is intended for removing duplicate data. It meets all the requirements from (1NF) and removes the subsets of data that maybe contained within multiple rows of a table and moves them into their own table.

More to come…