There's an answer to your question.'s Blog

Jerry Dausman's profileThere's an answer to you...BlogLists

<< First < Previous Next > Last >>

May 05

ASP to Excel Zip Code Format

So we had a problem today. We were trying to create, from an .asp page, an Excel spreadsheet that users could download. One of the columns carried zip codes, but the leading zeros of the zip codes were dropped, since Excel thought of these as numbers. Well, there's a lot of complicated things you can do, such as <meta> tags and such, but what's the easiest way to get this data into Excel, and have it display correctly? Just write the data in an HTML style table and use a style tag that Excel recognizes.

The example below is a simple spreadsheet, saved with the .xls extension for Excel. It has only two cells, both with the zip code value "02139" included. Notice that the second cell has a class attribute of "txt" however. That makes all the difference.

<html>
<head>
<style>
.txt {mso-number-format:\@;}
</style>
</head>
<body> <table>
    <tr>
      <td>02139</td>
      <td class='txt'>02139</td>
    </tr>
</table></body>
</html>

January 24

Searching for Answers

I can't believe I haven't added anything to this blog in 11 months! Somebody wake me up!

On my new job I wrote a script to help me find specific text in files. I know what you're going to say: there's a built in feature in Windows that will do the same thing. True, but it only gives me a list of files. I want more! And I got more. The script below does the following:

uses an specific list of files to search (editable),
lists the line number where the text was found in the file,
puts the output in .csv format so I can play with it in another program, and
can be adapted to display any information about the file.

The script uses a list (Reports.txt) as an input list of files to scan. You can create this list by using the directory command at the "C:>" prompt as follows:

dir *.* /B /S >Reports.txt

Here's the code for the script that does the work:

' ************************************
' **                                **
' ** Scan for Particular Commands **
' **                                **
' **        Scan4Commands.vbs       **
' **                                **
' ************************************
'
' Copyright (C) 2007 Jerome F. Dausman
'
' Purpose:
'
'    This program takes a list of reports
' and scans the list for particular text.
' The user inputs the path and filename
' of the text file listing the reports.
' The user also inputs the text that is
' being searched for.
' An output file is created with the
' cross-reference information.
'
'=== DECLARATIONS =========================================

Option Explicit

'--- Objects
Dim objFSO        'file system object
Dim objRepts      'the list of report files
Dim objRpt        'the report file being scanned
Dim objOut        'the output report generated

'--- string variables
Dim str           'useful string variable
Dim strScript     'this script
Dim strLocation   'this script's location
Dim strRepts      'the name of the listing file
Dim strRpt        'the name of individual report files
Dim strOut        'the name of the output file
Dim strSearch     'the search string

'--- numeric variables
Dim intLine 'line count
Dim intX 'useful integer

'--- constants
Const ForReading = 1
Const ForAppending = 8

Const strQ = """"
Const strC = ","

'CONST vbOKOnly = 0
'CONST vbOKCancel = 1 'Display OK and Cancel buttons.
'CONST vbAbortRetryIgnore = 2 'Display Abort, Retry, and Ignore buttons.
'CONST vbYesNoCancel = 3 'Display Yes, No, and Cancel buttons.
'CONST vbYesNo = 4 'Display Yes and No buttons.
'CONST vbRetryCancel = 5 'Display Retry and Cancel buttons.
'CONST vbCritical = 16 'Display Critical Message icon.
'CONST vbQuestion = 32 'Display Warning Query icon.
'CONST vbExclamation = 48 'Display Warning Message icon.
'CONST vbInformation = 64 'Display Information Message icon.
'CONST vbDefaultButton1 = 0 'First button is the default.
'CONST vbDefaultButton2 = 256 'Second button is the default.
'CONST vbDefaultButton3 = 512 'Third button is the default.
'CONST vbDefaultButton4 = 768 'Fourth button is the default.
'CONST vbApplicationModal = 0 'Application modal.
'CONST vbSystemModal = 4096 'System modal.

'=== MAIN PROGRAM =========================================

'--- get the script name and location --------
strScript = WScript.ScriptName
strLocation = WScript.ScriptFullName
intX = Instr(strLocation, strScript) - 1
strLocation = Left(strLocation, intX)
MsgBox strLocation

'-- get the name of the textfile list of reports to scan
str = "What is the name of the list of report files to scan?"
strRepts = InputBox(str,"List of Reports", "Reports.txt")

'--- check file validity ---------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not (objFSO.FileExists(strRepts)) Then
MsgBox "The file '" & strRepts & "' does not exist.", vbCritical, "No Scan"
WScript.Quit
End If

'--- create an output file -------------------
str = "What is the name of the report file you will create?"
strOut = InputBox(str, "Output File", "Search.csv")
On Error Resume Next
Set objOut = objFSO.OpenTextFile(strOut, ForAppending, True)
If (Err.Number <> 0) Then
str = strOut & vbCrLf
str = str & "is not a valid file for reporting."
Msgbox str, vbCritical, "No Output"
WScript.Quit
End If
On Error Goto 0

'--- get input search string -----------------
str = "What is the string you are looking for?"
strSearch = InputBox(str, "Search String", ".CommandText")

'--- write output header ---------------------
objOut.WriteLine(" ")
str = strQ & "===============================================" & strQ
objOut.WriteLine(str)
str = strQ & "Scan4Command Output Report" & strQ & strC
str = str & strQ & Date() & strQ
objOut.WriteLine(str)
str = strQ & "-----------------------------------------------" & strQ
objOut.WriteLine(str)
str = "Search Text: " & strSearch
objOut.WriteLine(str)
str = strQ & "===============================================" & strQ
objOut.WriteLine(str)
objOut.WriteLine(" ")
str = strQ & "Report" & strQ & strC
str = str & strQ & "Line" & strQ & strC
str = str & strQ & "Text" & strQ
objOut.WriteLine(str)
str = strQ & "-------" & strQ & strC
str = str & strQ & "-----" & strQ & strC
str = str & strQ & "-----------------------" & strQ
objOut.WriteLine(str)

'--- read from list and loop -----------------
Set objRepts = objFSO.OpenTextFile(strRepts, ForReading)
Do While objRepts.AtEndOfStream <> True

    '--- test a file name --------------------
    strRpt = objRepts.ReadLine
    If (objFSO.FileExists(strRpt)) Then

        '--- open the file -------------------
        intLine = 0
on error resume next
        Set objRpt = objFSO.OpenTextFile(strRpt, ForReading)
If Err.Number <> 0 Then
    msgbox "bad file = " & str
End If
on error goto 0

        '--- look for the search string ------
        Do While objRpt.AtEndOfStream <> True
            str = objRpt.ReadLine
            intLine = intLine + 1
            intX = Instr(str, strSearch)
            If (intX > 0) Then
               str = mid(str, intX)

               '--- output info --------------
               objOut.Write(strQ & strRpt & strQ)
               objOut.Write(strC & CStr(intLine) & strC)
               objOut.WriteLine(strQ & str & strQ)
            End If
        Loop
    End If
Loop

'--- write end of output ---------------------
str = strQ & "===============================================" & strQ
objOut.WriteLine(str)

'--- down and out ----------------------------
objRepts.Close
objOut.Close
str = "Done!"
MsgBox str, vbInformation, "Scan4Commands"
WScript.Quit

Hope this is helpful for you!

February 26

Forgotten Dependancies

Correct me if I'm wrong, but I believe past versions of SQL Server used to show all dependancies for all types of objects. Today I had to find out in which stored procedures a certain view was used. I ran sp_depends on the name of the view and all I got was the tables that the view depended upon. Knowing that the view was used in at least one stored procedure I proceeded to investigate the system table sysdepends. Lo and behold, there was no information for that stored procedures relationship to the view in sysdepends! What to do? Write my own procedure that I'll call spDependsAlso:

CREATE PROCEDURE spDependsAlso
@bblock varchar(50)
AS
SELECT
@bblock AS 'Building_Block'
, so.name AS 'Dependant'
FROM sysobjects so JOIN syscomments sc ON so.id = sc.id
WHERE sc.text like '%' + @bblock + '%'
AND @bblock <> so.name

Of course, you may want to create this as a function instead. And it would certainly work faster if we used the 'CONTAINS' function in the WHERE clause ... but then we'd have to use Search Service and regularly update the search indexes. I find this version works well enough for me.

January 25

New Job

What would you say to to the same hours, but more pay?

A good friend and neighbor dropped an offer in my lap last November. So not wanting to turn down a big raise and a challenging position I accepted. I am now working for Dynamics Research Corporation on a contract with the Department of Homeland Security, Citizenship and Immigration Services. My official title is Senior Staff IT Analyst.

But wait! There's more!

Two and a half weeks into the job my new boss gets an offer he can't refuse! So who gets to take his old job? It's offered to me ... and I took a second raise and promotion (effective February 1).

Call now! Operators are Standing By!

I will continue with this blog ... for several reasons: 1) my job still involves teaching, albeit a lot less; 2) I intend to continue on as retain my MCT certification; and 3) I seem to like sharing what I learn!

1:12 PM | Add a comment | Permalink | Blog it | miscellaneous

September 28

Sharing Session between ASP and ASP.NET

One of my students in my recent Upgrading to ASP.NET class wanted to know how to share Application and Session variables between .asp and .aspx pages. The manual says you can't do it directly. Two minutes after the last student left I found good answers from Microsoft MVP Peter A. Bromberg's site.

There are two ways you can share the information. We discussed one of the ways in class: create a COM object that you use in place of a session object. When you save a session variable you would use both the session id (which you can retrieve from ServerVariables) and a key to store the value. Since you can use COM objects in both .asp and .aspx pages, each would have access to the information. The COM object would typically use a database to store the data on the back end. The second way the MVP suggested was to store the data in both Session variables and hidden <input> tags, posting them across from .asp to .aspx, and vice versa.

A detailed discussion and accompanying code can be found at http://www.eggheadcafe.com/articles/20021207.asp and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/ConvertToASPNET.asp

[ms2310, module 14, page 7
and ms2640, module 5, page 8]

August 22

.NET Language Choices

How many .NET languages are there? That question came up during my ms2373 class yesterday. The answer is ... lots, espcially if you count the several variations of many of the languages. Did you know that there are about 11 variations or variants of the C# language out there? (Don't ask me why!) If you want to count them for yourself, and track the additions, you can look at a couple of good sources. First is Brian Ritchie's .NET Development site at http://www.dotnetpowered.com/languages.aspx. Brian is kind enough to list the URLs to his sources. Second is http://www.dotnetlanguages.net/DNL/Resources.aspx on the .NET Languages blog.

[ms2124, module 1, page 6;
ms2373, module 1, page 8;
ms2559, module 1, page 7;
ms2609, module 1, page 6]

August 10

Credit Card Offers

Do you get tired of endless credit card offers in the mail? You might try checking out https://www.OptOutPreScreen.com. This site is a joint venture between the credit reporting agencies. The Fair Credit Reporting Act allows these reporting companies to send out info on you to banks, insurance companies, etc. but you can ask them not to forward the information for requests not initiated by you. In other words, stop some of the junk mail.

August 07

Easy XML Menu

The one thing in the MS2310 class that bothers me the most is the clunky menu system they have you do as a web control. I understand why they have you do it as a class: to demonstrate class building and usage. But that's not how we'd do it in real life, is it? I prefer doing my menus in XML files. It makes them easy to update on the web. The following code shows one way to use an XML file in a menu control.

First we need an XML file that describes our menu. The following example file is one way of structuring an XML file to be a menu file:

<?xml version='1.0' ?>
<Menu>
  <item title="Default" url="Default.aspx" bgcolor="yellow" />
  <item title="New Employees" url="EmployeeEdit.aspx" bgcolor="cyan" />
  <item title="Dausman Blog" url="http://dausman.spaces.live.com/" bgcolor="lightgreen" />
  <item title="Google" url="http://www.google.com" bgcolor="goldenrod" />
</Menu>

The easiest way to translate this xml into something visible is to use an xslt stylesheet. The following is a simple stylesheet:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
  <table width="100%">
    <tr align="center">
      <xsl:apply-templates select="/Menu/item" />
    </tr>
  </table>
</xsl:template>

<xsl:template match="item">
  <td>
    <xsl:attribute name="bgcolor">
      <xsl:value-of select="@bgcolor" />
    </xsl:attribute>
    <a>
      <xsl:attribute name="href">
        <xsl:value-of select="@url" />
      </xsl:attribute>
      <b>
        <xsl:value-of select="@title" />
      </b>
    </a>
  </td>
</xsl:template>

</xsl:stylesheet>

Now, if we call the XML menu file Menu.xml and the XSLT stylesheet file Menu.xsl we can simply put an XML Data Control on a user-defined web control page (an .ascx page) like so:

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="XMLMenu.ascx.cs" Inherits="XMLMenu" %>
<asp:Xml ID="Xml1" runat="server"
DocumentSource="~/Menu.xml"
TransformSource="~/Menu.xsl"></asp:Xml>

Voila! We have a menu control we can drag-and-drop on any individual .aspx page (or our MasterPage). Now changing a menu is as simple as changing our XML data file.

If you're looking for a bit more control, check out my next blog article (Coming Soon!) and I'll show a more involved alternative that still uses an XML file for the basic menu.

[ms2310, Lab 3 (menu component) and Lab 5, Exercise 3]

July 21

Demo WebService Functionality

One of the (almost) unavoidable aspects of using the demo webservices in the official Microsoft courseware is that they all run on the instructor's computer. In other words, they are on the local LAN. One of the great benefits of webservices (in my humble opinion) is that the functionality can be anywhere on the internet. So, to demonstrate that benefit in my classes, I use a webservice on my personal website. I have just rewritten my webservice to include two functions, accessable by the HTTP Post and Get protocols: .Punchline and .CrazyWords. .Punchline takes no input parameters, .CrazyWords takes 8 strings as input.

Here is the URL: http://www.Dausman.net/Functions.asmx . Have fun!

[Any Visual Studio.NET Course]

July 13

Hashtable Internal Order

A question came up in class a few weeks ago (just after returning from TechEd 2006 Boston). The question had to do with Collections in .NET. All collection types (in System.Collections) offer some type of .Sort method ... except the Hashtable. So does the Hashtable automatically sort the items when you add them? Actually, no! OK, does it at least save them in the order you entered them? No again! I ran the code below to demonstrate:

using System;
using System.Collections;

namespace HashtableOrder
{
class Class1
{
  [STAThread]
  static void Main(string[] args)
  {
   // create the worlds most sophisticated hashtable
   Hashtable alpha = new Hashtable();

   // add items marvelous items
   alpha.Add("A", "99");
   alpha.Add("B", "22");
   alpha.Add("C", "88");
   alpha.Add("D", "33");
   alpha.Add("E", "77");

   // iterate through, in Key (internal hashtable) order
   foreach (string letter in alpha.Keys)
   {
    Console.WriteLine(letter +"   "+ alpha[letter]);
   }

   // wait a sec so we can read output
   Console.Read();
  }
}
}

And the output I received was ...

C   88
A   99
D   33
E   77
B   22

I used C# because VB.NET doesn't have a foreach loop. I would have used a For ... Next loop in VB.NET, but the Hashtable's Keys collection doesn't have an indexer (i.e., you can't reference it with Keys(i) syntax).

So how do we get a useable sort routine from a hashtable? Create a "super" hastable that inherits from a regular hashtable, and add your own sort method! (Ouch! Not an easy answer!)

[Any Visual Studio.NET Course]

June 16

TechEd Is Almost Over! Do IT Again.

There were so many things to see and do at TechEd 2006, not only at the show, but in Boston. (The party at Fenway Park last night was awsome!) Unfortunately, my ISV had problems and my site shut down on Tuesday! No web, no email.

So what to do? Well, do it all over again! Specifically, one of the concerns that I received from folks doing the Hands-On Labs was that there were more great labs than they had time to do. So I spent some of my free time looking for the virtual machine images of the labs to take back to Washington, DC. I have many of them, and am planning to run a few Hands-On Labs nights at ICI Systems in downtown DC this summer. (EMail me at JFDausman@ICISys.com and I'll put you on the list to be notified.) We'll have pizza, sodas, labs ... and a great time.

11:08 AM | Add a comment | Permalink | Blog it | TechEd 2006

June 10

TechEd 2006 Boston Is Cool!

I'm here and I'm psyched! Right now I'm previewing the TechEd Hands-On Labs that I'll be helping attendees through. I'm working in the Business Intelligence section of the Labs. Talk about cool. One of the neatest things about TechEd is the people I get to meet and work with. It's an amazing amount of brain power all under one roof. Be there ... or be square!

6:25 PM | Add a comment | Permalink | Blog it | TechEd 2006

June 07

I Am In Boston!

Hey! We (my 14 year old son and I) made it to Boston yesterday evening. Rain, rain, rain. And every 20 seconds or so ... wind! Otherwise known as horizontal rain.

Check the weather for Boston before coming. My son, in his infinite teenage wisdom, did not bring sweatshirt, jacket or coat. And the temperature has been as high as 65F.

10 Day forcast for Boston (zip code = 02110):

Tomorrow ... rain ... 56F

Friday, June 9 ... 40% chance of showers ... 62F

Saturday, June 10 ... 60% chance of showers ... 68F

TechEd 2006 Starts ...

Sunday, June 11 ... partly cloudy (20% chance precipitation) 69F

Monday, June 12 ... mostly sunny (20% chance precipitation) 74F

Tuesday, June 13 ... partly cloudy (20% chance precipitation) 72F

Wednesday, June 14 ... mostly cloudy (20% chance precipitation) 74F

Thursday, June 15 ... partly cloudy (20% chance precipitation) 71F

Friday, June 16 ... showers (40% chance precipitation) 71F

end of TechEd 2006 Boston

See you all soon. I'll be one of the Technical Learning Guides in the Hands-On Lab area. Come and learn something!

Only 4 days left till TechEd 2006!

April 26

User Account Expiration Dates

I can't believe I haven't entered anything since March 8! My bad.

Today's entry is from a week of scripting I taught a few weeks back. We wanted to develop a script to show Windows user account expiration dates. We would have liked to use ADSI objects, but they don't give you the object properties that you can use. Using LDAP is the easiest way. Below is the code I came up with.

' Display User Account Expiration Dates
' Jerome F. Dausman
' April 2006

Option Explicit

Dim objSvc  'server
Dim objCon  'container
Dim objUser 'user
Dim strDate 'expire date
Dim strOut  'output string

Set objSvc = GetObject("LDAP://London/DC=nwtraders,DC=msft")

For Each objCon in objSvc
    If objCon.class = "organizationalUnit" Then
        strOut = strOut & objCon.name & vbcrlf
        GetAllUsers(objCon)
    ElseIf objCon.class = "container" AND objCon.name = "CN=Users" Then
        strOut = strOut & objCon.name & vbcrlf
        GetAllUsers(objCon)
    End If
Next

WScript.Echo strOut

WScript.Quit

Sub GetAllUsers(objX)
    For Each objUser in objX
        If objUser.class = "user" Then
           strOut = strOut & " " & objUser.name & vbTab
           If objUser.accountExpires.HighPart = 0 Then
               strOut = strOut & "NEVER" & vbcrlf
           Else
               strDate = objUser.accountExpirationDate
               if strDate = "1/1/1970" Then
                   strOut = strOut & "NEVER" & vbcrlf
               else
                   strOut = strOut & strDate & vbcrlf
               end if
           End If
        End If
    Next
End Sub

There's a couple of things you may want to change in the above script. The first is, of course, the line where we "Set objSvc" to the nwtraders.msft domain. You might want to use an input box to take in any domain you're interested in.

The second thing you'll probably want to change is the output. Instead of using WScript.Echo you will probably want to write the output to a file.

Finally, my script only checks the Users and organizational units 'folders' for user accounts. If you have a more complex organizational structure, such as subdomains or a forest, you will have to modify the script accordingly.

[ms2433, module 5, page 26]

March 08

TechEd Update

Once again Microsoft has let me slip through the cracks and be a Technical Learning Guide (last year's term was "proctor") for the Hands-On Labs. The site is up and running for TechEd 2006 ... so take a look at this year's offerings: http://www.microsoft.com/events/teched2006/default.mspx

Only 95 days left till TechEd 2006!

10:34 AM | Add a comment | Permalink | Blog it | TechEd 2006

March 01

Web Service Protocol Configuration

We all know that the .NET Framework 1.1 disables by default the web service protocols HttpGet and Http Post. If you don't know how to turn them back on again ... read on!

The key is adjusting your web.config file. You need to add a section, under <system.web> called <webServices>. Inside the <webServices> element you add the <protocols> element. The <protocols> element allows three sub-elements: <add>, <remove>, and <clear>. <add> allows the web service to use the protocols not normally initialized. The code below shows you how to implement HttpGet and HttpPost in your web.config file.

The <remove> element is used the same way, but to turn off protocols, such as HttpSoap.

The <clear> element removes all protocols from your web service.

You may have noticed I removed a protocol called 'Documentation'. Removing Documentation turns off both the Help Page automatically generated by the web service, and generation of the WSDL file. If you don't want a help page, but do want a WSDL file you can redirect WSDL generation to a file of your choice. The code below demonstrates this.

Of course, you may choose to display only the portions of the actual WSDL file that you want the public to use. References used for this blog posting are:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/gngrfprotocols.asp

for protocols, and http://msdn2.microsoft.com/en-us/library/2tyf2t8t.aspx for the Documentation protocol.

[ms2310: Module 13, page 17 and Module 15, page 27]
[ms2524: Module 5, page 8]

February 28

The New DBA: Periodic Tasks, Part Two

This is the seventh and final post in a series of posts concerning what a person, just hired as a new DBA, needs to keep in mind to hang onto that job.

Periodic Tasks, Part Two

Task 19: Use Profiler to check for deadlocks and locking timeouts. See if a particular user or application is causing problems and investigate.

Deadlocks indicate data being either locked for too long, or being locked in the wrong order. In either case processing time suffers. Rewriting code is often indicated.

Task 20: Read! Books Online, SQL Server Magazine and other resources will give you a wealth of new ideas for improving your systems.

The boss won't mind if it wasn't your idea originally ... just as long as it gets the job done! And it can't hurt to be known as knowledgeable. (Go ahead and take credit if it was one of my ideas ... I don't mind!)

Task 21: Don't forget to ask your boss for a raise when your hard work pays off with increased throughput or decreased response time or more utilization of that ancient equipment they make you put up with!

It generally can't hurt to ask!

We've come to the end of our discussion of the important things an new DBA should keep in mind. Obviously there are many more things we could have added, but these were the top things my students and I discussed in the last 6 years of my teaching SQL 7 and SQL 2000 Administration. I hope you enjoyed this series. And if you have any suggestions or additions, please comment.

[Every SQL Server course: the last chapter]

11:53 AM | Add a comment | Permalink | Blog it | SQL Server

February 27

The New DBA: Periodic Tasks, Part One

This is the sixth in a series of posts concerning what a person, just hired as a new DBA, needs to keep in mind to hang onto that job.

Periodic Tasks, Part One

Task 16: Restore your offsite (or off-machine) backups of all production databases to a test machine to prove your restore strategy. Run this drill a minimum of once a year to train for minimizing downtime and eliminate flaws in your restore process.

How do you know if your offsite storage provider has kept your tapes in good condition? How do you know if your second-in-charge can handle the restore process when you're on vacation? How do you know how long a restore will actually take ... if you don't actually test it?

Task 17: Rerun baseline traces at least twice a year to spot trends that a) indicate new problems, or b) may lead to processing bottlenecks.

Organizations tend either to grow or die, and growing organizations have growing data requirements. Sooner or later some problem or bottleneck will arise, and it's always wise to know when that day is coming. Plan for it.

Task 18: Talk to end users regularly to get a sense of: new developments, changing requirements, comfort level with the current system, etc.

This task (like the previous one) is focused on the future of the organization. It's always good to have a little prior notice of anything that will affect data useage in the organization. As an added benefit, you will be seen as someone approachable and knowledgeable in the organization. Alternatively, if your organizations data requirements seem to be shrinking ... you'll know when to polish up that resume!

Please add your comments ... and stay tuned ... there's more to come in this series!

[Every SQL Server course: the last chapter]

9:31 PM | Add a comment | Permalink | Blog it | SQL Server

February 22

The New DBA, Secondary Tasks, Part Two

This is the fifth in a series of posts concerning what a person, just hired as a new DBA, needs to keep in mind to hang onto that job.

Secondary Tasks, Part Two

Task 12: Use sp_depends and the database table sysdepends to determine the number (and structure) of levels of views built on views. Try and limit the number of view levels.

A view that references a view treats the referenced view as a subquery. In other words, the subquery is optimized independantly of the first view. Since, ultimately, all our data comes from tables, it stands to reason that each view can (and should) directly reference tables so that only one optimization needs to be performed.

Task 13: Use sp_depends and sysdepends to determine the stored procedures that call other stored procedures, and how far they are nested. Determine the critical stored procedures in the nexting structure and analyze them to determine if a) we can reduce nesting levels, and/or b) if these critical stored procedures can be written to be more efficient.

Not only is optimization involved, but also nesting levels. SQL has a limit of 32 nesting levels, and we don't want to run into that limit!

Task 14: Become familiar with roles and permissions in each production database. Understand the particular internal security measures.

Follow the rule of thumb that users should only have access to the data they need to do their job. Figuring out what that data is, and the best way to lock out all the other data is the hard part. Design of these access rules requires a thorough knowledge of the business application and the environment in which it runs.

Task 15: Look for optimizer hints in SQL code. Determine why they are in place. See if you can take them out without compromising reporting or performance.

In my humble opinion "optimizer hints" are a misnomer. I believe they should be called "optimizer commands" because that's just what they are: directives to run the command the way the programmer wants even if the optimizer determines a "better" way. The optimizer almost always knows the best way to process data. There are situations where optimizer hints work better, but these are rare. You really need to know your database to out-think the optimizer.

Please add your comments ... and stay tuned ... there's more to come in this series!

[Every SQL Server course: the last chapter]

February 13

Your Next Available Drive

We're taking a short break from the list of things a new DBA ought to keep in mind to cover a script we put together in our ms2433 class last week.

It's nice to know what the next available drive letter is, especially if you're going to use it for a network mapping. The example in our 2433 class looks through the current drive mappings to find the highest drive letter, and then chooses the next letter in the alphabet as the next available letter. All well and good ... except if the last drive letter currently is 'Z'. People (and programs) can map drives in any order they wish, so I wrote a short script to show the first and last available drive letters.

' NextDrive.vbs
' Jerome Dausman February 8, 2006

Option Explicit

Dim objFSO              'file system object
Dim objDrive            'placeholder drive object
Dim strDrive            'drive letter
Dim intDrive            'the ASCII value of the drive - 64
Dim strAvailables       'list of available drives
Dim strOutput           'output string

'initialize strings
strAvailables = " DEFGHIJKLMNOPQRSTUVWXYZ "
strOutput = "Available Drives:" & vbCrLf

'get a file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'scroll through the list of drives
For Each objDrive in objFSO.Drives
strDrive = objDrive.DriveLetter
intDrive = ASC(strDrive) - 64

    'knock the drive letter out of the list
    If (intDrive = 1) Then
        strAvailables = " " & Mid(strAvailables, 2)
    Else
        strAvailables = Left(strAvailables, intDrive - 1) & " " _
            & Mid(strAvailables, intDrive + 1)
    End If
Next

'show the available drives, first and last
strOutput = strOutput & "     " & strAvailables & vbCrLf & vbCrLf
strOutput = strOutput & "First Available Drive: " _
    & Left(LTrim(strAvailables), 1) & vbCrLf
strOutput = strOutput & " Last Available Drive: " _
    & Right(RTrim(strAvailables), 1)

msgbox strOutput, vbInformation, "Drives for Mapping"

So, the idea is to get a drive letter and knock it out of the list (strAvailables). I started by knocking out drives A, B and C, since I always have those drives on all my systems. You can taylor the script for your own needs, such as knocking out a drive everyone always uses as the departmental file server.

[ms2433: Module 6, page 22]

12:04 PM | Add a comment | Permalink | Blog it | Scripting