Developers, use Profiler to profile yourself

Posted on September 27, 2009 by | 1 comment

Problem

You’re starting to work on an existing application that doesn’t have sufficient documentation.  You need to trace the current flow to understand and review all SQL calls during the lifecycle of the application (RPC:Complete and SQL:BatchCompleted).  You know that SQL Profiler can do this but you’re unsure how to make use of the filter options.

Solution

In order to solve the following problem you would want to use SQL Profiler.  Start a new trace and in the event selection deselect all events other than RPC:Completed and SQL:BatchCompleted.

Profile1

Next check the checkbox to show all columns so we can add a few columns that are left off by default.  Now scroll to the right and you will notice many columns that could be helpful.  For example, I see DatabaseName, HostName, and NTLoginName.  Place a check in the checkbox for these columns.

Now we will move along to filters.  Click on the Column Filters button.  Notice that the columns you just added are also candidates for filtering.  In this example I am going to select HostName and use my computer name “WHBV53YC1” (You could also use NTUserName and use your login name)

Profile2

Its that simple, you just configured the trace to only capture Stored Procedures and TSQL commands issued by yourself.

1 Comment

Posted in SQLServerPedia Syndication

Converting a vertical table to horizontal table

Posted on September 24, 2009 by | 7 comments

Last week I received a request to convert a vertical table from a vendor application into a horizontal table.  There was one catch, the vertical table included text columns that needed to be pivoted horizontally.  The following was my plan for tackling this request.

  1. You must figure out how many columns are required for the worst case scenario in you horizontal table.  You can do this by multiplying the columns being pivoted by the rows in the vertical table. In this example you know the worst case is eight (see figure two.)  In this example we will assume that you will not know how many columns are needed.
  2. Now we will use dynamic sql to create our new table that will support the columns needed in the horizontal table.
  3. Next we will create a cursor that will loop through the vertical table to create and execute insert statements to populate the horizontal table.

Example of vertical table (Input)

ManagerName ManagerEmail Review Employee
Jack Wilson jwilson@comp.com 2009 Review John Sterrett
Jack Wilson jwilson@comp.com 2009 Review Bo Smith
Hank Reed hreed@comp.com 2008 Review John Sterrett
Jack Wilson jwilson@comp.com 2008 Review Chris Cupp

Figure 1 – The vertical table

The following is the horizontal table (output)

ManagerName ManagerEmail Review1 Employee1 Review2 Employee2 Review3 Employee3
Jack Wilson jwilson@comp.com 2009 Review John Sterrett 2009 Review Bo Smith 2008 Review John Sterrett
Hank Reed hreed@comp.com 2008 Review Chris Cupp NULL NULL NULL NULL

Figure 2 – The horizontal table

For this example we used two scripts.  The first script will create a vertical table and insert the sample data.  The second script populates the horizontal table and also prints out all scripts created dynamically to the message window.

If you have any questions please feel free to leave a comment . I will try to point you in the right direction.

7 Comments

Posted in Scripts, SQLServerPedia Syndication

Tagged

Can SQLSaturday happen in Wheeling, WV?

Posted on August 16, 2009 by | 8 comments

I noticed a great event that is occurring down south.  Its called SQLSaturday and one of my short term goals is to see if it’s possible to bring this great event to Wheeling, WV. 

What is SQLSaturday?

SQLSaturday is a platform for free one day training events for SQL Server professionals. This event focuses on speakers, providing a good variety of topics, and making it all happen through the efforts of volunteers. Whether you’re attending one or thinking about hosting your own(this would be me), we think you’ll find it’s a great way to spend a Saturday.

Initial Goals:

  1. Bring technology professionals from Pittsburgh, Columbus and Morgantown to Wheeling.
  2. Have 50 to100 attendees attend this initial event.
  3. Provide great speakers to present topics about SQL Server and .NET
  4. Utilize sponsors to make the event 100% free to all who attend.
  5. Provide the opportunity for IT Professionals to network with their peers.
  6. Provide exposure for AITP and the Greater Wheeling Chapter of AITP 

Conclusion:

Will this event happen?  I sure hope so, but  I am still planning.  I hope to have an answer soon.  If we do go live I am targeting early 2010.

I will post my future action items and the decisions I make soon.  I hope that this series of posts are helpful for people who are contemplating if they should start a similar event in their area.

If you have any comments, thoughts or suggestions please share them.

8 Comments

Posted in Events, SQL Server

Tagged ,

Review of the August 2009 PGH.NET User Group

Posted on August 11, 2009 by | Leave a comment

Tonight I made the trip from Wheeling to Pittsburgh to attend “Four Guys with Code.”  I thought David, Jeremy, John and Erick all presented good presentations.  The following are some afterthoughts from the event: 

Being a die-hard Pirates fan  I really liked the “Build a RESTful Data-Driven Application with jQuery” presentation.  David leveraged a baseball stats database while showcasing how easy it is to use jQuery.  I have now implemented jQuery on a few projects so its always good to see how others are using this great library. 

David mentioned that there will be a code-lab in September covering “Build WCF Data-Driven Applications with jQuery.  I will add more when I get details.

The following three presentations all gave me some good thoughts.  Being new to Silverlight John’s presentation opened my eyes toward things you must consider before you release a RIA application. Eric’s presentation gave me insight towards refactoring code and setting coding standards. Jermey’s presentation gave me a good introduction into dynamic C#.

Leave a comment

Posted in Events

Tagged , , ,

Free SQL Server 2008 Training Kit

Posted on August 7, 2009 by | Leave a comment

Are you looking for some free SQL Server training?  Are you looking to upgrade your SQL Skills to SQL Server 2008? If so, Microsoft has a download just for you.

Download SQL Server 2008 Developer Training Kit

The SQL Server 2008 Developer Training Kit includes presentations, sample code and labs that cover the following topics:

  • Spatial Support
  • FILESTREAM
  • CLR
  • Reporting Services
  • Date and Time
  • T-SQL enhancements including  Table Value Parameters, Merge, Row Constructors, Grouping Sets and more..

Leave a comment

Posted in SQL Server

Tagged , , , ,

Find tables that contain column name

Posted on July 7, 2009 by | Leave a comment

The following script is used when I need to perform a search to find tables that contain a column name.

-- Use Control+Shift+M to specify a value column name

SELECT    TABLE_SCHEMA + '.' + TABLE_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE    COLUMN_NAME = N'<column_name,varchar,(column_name)>'

or you can also use the following query..  

-- Use Control+Shift+M to specify a value column name

SELECT sc.[name] AS column_name, so.[name] AS [TABLE]
FROM syscolumns sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.[name] LIKE N'<column_name,varchar,(column_name)>'
AND so.xtype = 'U'

I

Leave a comment

Posted in Scripts, SQL Server

WD My Passport Review

Posted on June 21, 2009 by | 2 comments

WDPassport2

This past week I took a trip over to my local Best Buy and decided to purchase a portable hard drive.  I noticed that one of my mentors was using a similar device for hosting Virtual PCs so I thought I would give it a try.  The following are my initial reasons for purchase:

  1. Consolidate Virtual PC’s and Demos
  2. Need to Synchronize documents on multiple computers
  3. Good disk I/O for portable drive

Consolidate Virtual PC’s and Demos

From time to time I do quite a few demos at work, AITP, code camp etc.  I want to consolidate all my presentations, sample code, virtual drives etc. so I could repeat a presentation on the go as needed.  This device works great for this purpose.  A benefit of the WD My Passport is that the device is so small it fits in my pockets.  I now take the portable drive almost everywhere I go. 

Synchronize Documents

I have many files going all the way back to college on several computers.  I want to be able store and/or modify versions of these files on a single device without having to worry about manual synchronization.  Basically, If I make a modification to a document on the portable device I want to be able to synchronize these changes when I connect the portable hard drive back to the computer that hosts the original version of the file.

The WD Sync application that comes with the WD Passport accomplishes this task.  This application allows you to create profiles for computers and allows you to sync documents, photos, videos, email and more.  I was easily able to modify documents from different computers and sync them back with the original pc.  I am very impressed with WD Sync.  It takes a few minutes to understand the functionality but its a great free tool.

Test Disk I/O

Using SQLIO and perfmon I was able to create a quick test to put this portable hard drive to the test.  From the following screen shot below you can see an average disk transfers/sec is 102.093.  This result isn’t great but I believe its workable for a portable hard drive.

image 

 

Conclusion

For only spending $90 I believe this is a good device.  Are you currently using portable hard drives? If so, which model are you are using? What are your favorite features?

2 Comments

Posted in Tech Toys, Uncategorized

T-SQL Scripts included with Management Studio

Posted on May 12, 2009 by | 1 comment

Have you ever wanted to quickly script T-SQL code to add columns to a table, alter a partition function, create endpoints, configure data capture, create an indexed view, backup or restore databases?  These tasks and more are included as templates.  The template explorer is included in SQL Server 2005 and SQL Server 2008 Management Studio (SSMS).   To view the template explorer hit Ctrl+Alt+T or select Template Explorer from the view menu in SSMS.  You will find templates to create objects such as databases, tables, views, indexes, stored procedures, triggers, statistics, and functions. In addition, there are templates that help you to manage your server by creating extended properties, linked servers, logins, roles, users, and templates for Analysis Services, and SQL Server Compact 3.5 SP1.

image

Once you load a template in the editor section in SSMS you can specify values for the template parameters values.  Press CTRL+Shift+M to specify values as shown on the next figure.

image
The templates are placed in the users Documents and Settings folder under Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates. Templates are available for solutions, projects, and various types of code editors as they are scripted as individual sql files.

1 Comment

Posted in SQL Server, SQLServerPedia Syndication

Tagged , ,

Backup Compression with SQL Server 2008

Posted on April 15, 2009 by | 3 comments

I wanted to share my results towards using the backup compression utility built into SQL Server 2008 Enterprise Edition.   I have noticed a compression range from 65% to 75%.

Database SQL 2005 backup SQL 2008 (compression enabled) Compression
DB One 18.2 GB 5.6 GB 69%
DB Two 3.7 GB 1.3 GB 65%
DB Three 1.2 GB .3 GB 75%

By default, backups are not compressed. You can change this setting by using sp_configure to set the value of the backup_compression_default setting or by selecting the option in the Server Properties dialog box. ( I don’t recommend  this as you can specify the compression type as shown below)

For anyone using SQL Server 2008 who is  not familiar with the compression feature you can enable it by using the following example.

When you create a backup on the options page you will notice a compression section at the bottom of the screen.  There are three values in the dropdown including use default server settings, Compress backup, and do not compress backup.  Select compress backup and click OK (I highly recommend you also check the checkbox to verify backup when finished)

SQLBackup

For those who are using the backup compression what are your results?  Shoot a responses.  I want to know how its working out for you.

3 Comments

Posted in SQL Server, SQLServerPedia Syndication

Tagged

AITP Tours the Center of Educational Technology

Posted on April 1, 2009 by | Leave a comment

This month’s meeting of the Greater Wheeling Chapter of AITP is tour of the Center for Educational Technologies. It will start at the CET office located on the WJU campus at 316 Washington Ave. Wheeling, WV 26003-6243 on Wednesday, April 8th 2009. The meeting will include a presentation of video conferencing and a tour of CET given by Dr. Bruce Howard.

Social hour will start at 5:30 P.M. We will meet in the CET lobby on the third floor.  The tour and presentation will start at 6:00 P.M.  Dinner will follow at 7:15 P.M and it will be held at the White Palace in Wheeling Park.  Dinner will be followed by a tech session on IE 8 presented by Chuck Hill.
 
Cost for the evening is $18.00 for AITP members, $10.00 for AITP student members, and $23.00 for guests.  To RSVP please email kkovacs@dwc.org.  If you have any questions or need help with directions please call John at 304-780-8532.

Driving Directions:
The Center for Educational Technologies® is located on the campus of Wheeling Jesuit University in Wheeling, WV. To reach the center, take exit 2B off I-70 in Wheeling
If you’re coming from the West, turn left at the end of the exit ramp and follow Washington Ave., bearing right at the end of the bridge. If you’re coming from the East, turn right onto Washington Ave. From either direction, then, the entrance to the campus is on the left. The center is the first building to the left off the main campus drive. Parking is available in the rear of the building.

campusMap

Leave a comment

Posted in Events

Tagged , ,