Hunting for the elusive column, table, proc, ...

{

One of our clients has a Microsoft SQL Server table with 284 fields. So when I'm given a spec that references the "OrigLoanAmount" field, it's sometimes difficult to ascertain where that field lives. On some systems, especially if you've got familiarity where data are likely to be stored, you can make an educated guess. But if you're interested in some quick digging, try these queries from the Information Schema views:

-- find that elusive column
SELECT * FROM Information_Schema.Columns WHERE Column_Name LIKE '%MyColumn%'

-- list out the tables
SELECT * FROM Information_Schema.Tables

For procedures, views, and functions, I've just queried sysobjects. I learned this from a guy who was in my class once; I used to be one of the hapless Enterprise Manager users until I saw him digging effortlessly with queries like:

-- show me all stored procedures
SELECT * FROM syobjects WHERE type = 'p' AND name NOT LIKE 'dt%'

-- show me all views
SELECT * FROM sysobjects WHERE type='v'

-- show me all user defined functions
SELECT * FROM sysobjects WHERE type='FN'

... and so on.

}

Web Developer Tools

{

Glad I found about Chris Pederick's Web Developer Extension. It's a set of tools that will run inside Firefox or Mozilla revealing the underlying structure of any site you pull up in your browser.

Some of my favorite things:

• Displaying borders on table structure
• Displaying borders on frame and iframe elements
• Showing dimmensions of images on screen
• Adding your own user stylesheet
• Displaying stylesheets for a site within your browser
  

Now to find an IE version...

}

Office Interop with .NET

{

Are you still doing VBA with Office even though you know you should be doing your coding with the .NET Framework? Probably like many I did things the old way rather than dealing with COM Interop, especially since this was particularly daunting with Office.

It isn't as of a while ago, but you, like me, may have been a bit too busy to notice.

First, you must download the Office Interop Libraries from here. The instructions are clear; run the register.bat from the VS.NET command prompt. After that you can make a reference to the application interop assembly of your choice, such as Microsoft.Office.Interop.Excel.dll.

Reference the namespace in your app:
using Excel = Microsoft.Office.Interop.Excel;

Code away:

Excel.Application app = new Excel.Application();
Excel.Workbook book = app.Workbooks.Open(@"c:\data\Daita.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets[1];
for(int i=1;i<10;i++) j="1;j<7;j++)" rg =" (Excel.Range)sheet.Cells[i,j];">

A few interesting things:

• The Type.Missing passed for all those COM-esque optional parameters. Ugh.
• Casting the cell you are on into a Excel.Range when you need to access its contents.

Finally:
Many more articles can be found here, on MSDN.

}

Try. Fail. Try again. Fail better...

{

After months of neglect, I'm going to try this again. No more epics, just short snippets of developer talk.

}