Pro SQL Server Reporting Services

Just got this new book on Reporting Services ... VERY good book. If you are just starting out or need help with some good examples, I highly recommend this book.

Stored procedure to find all instances of a field

As a report writer, database administrator, developer or user, it may be useful to know which table a field exists in. Many third party vendors don't supply a data schema, making it difficult to query and build reports. This stored procedure is a simple method of searching through all the table in the current database for a field that you specify.

While you certainly cannot assume that because two tables have the same field that they are related, this will certainly be at least one tool you can use to help find those relationships.

CREATE PROCEDURE dbo.sp_FieldInfo
(
@Column_Name nvarchar(384) = NULL
)
AS
SELECT Object_Name(id) as 'Table Name',
rtrim(Name) as 'Field Name'
FROM syscolumns
WHERE name like @Column_Name
GO

Then to call this proc:
sp_fieldinfo employeeid

Email attachment limits

What a pain it is when you want to email someone a large file only to find out Hotmail, Gmail, Yahoo, or any of the other numerous email providers don't support large attachments - most only allow up to 10mb. 10MB?? With today's focus on media (pictures, movies, songs, etc.) - how can I email a bunch of pictures in one big zip file?

Yousendit has started a service which will allow you to email large attachments to somebody - up to 1 GIG!! THAT'S more like it! Using the yousendit website, you upload your file, enter the email addresses you want it sent to, a link is sent to them via email, they click on the link to download the file. Your file is stored for seven days, then deleted. VERY COOL!

Office Developer Center: VBA Language References

Office Developer Center: VBA Language References

Microsoft has recently published the language references for all of the Office 2003 applications. You can view them online or download them.

If you are a VBA programmer, you should download these useful references.

Blogging on Visio

Blogging on Visio

Just found another great blog with very useful tips and techniques using Microsoft Visio. For some strange reason, Visio seems to have very little documentation and there aren't many Visio books available. If you see other Visio sites or have any books to recommend, please post in the comments - thanks!

Microsoft Photo Story 3 for Windows: make show-n-tell cool again

Microsoft Photo Story 3 for Windows: make show-n-tell cool again

If you are into photo editing or you share your photos with friends and family - you gotta check this out! Microsoft Photo Story 3 - it's free!!!

Bring life into your favorite memories with Photo Story 3 for Windows by adding motion, effects, music, and more to your digital photos. Easily retouch your images with a single click and add slick-looking titles, or add dramatic pans and zooms that give your creations a professional finish, create a soundtrack - even record narration. Then enjoy the show. Start sharing your stories today!

Retrieve current username from windows using VBA

Ever needed to retrieve username of the person currently logged into Windows? Using VBA in any of the Office products, the following code will return the current username. Great for implementing your own security or auditing.

' Makes sure all variables are dimensioned in each subroutine.
Option Explicit

' Access the GetUserNameA function in advapi32.dll and
' call the function GetUserName.
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

' Main routine to Dimension variables, retrieve user name
' and display answer.
Sub Get_User_Name()

' Dimension variables
Dim lpBuff As String * 25
Dim ret As Long, UserName As String

' Get the user name minus any trailing spaces found in the name.
ret = GetUserName(lpBuff, 25)
UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)

' Display the User Name
MsgBox UserName
End Sub

Library of Free Data Models

Library of Free Data Models

Reading the news groups today, I came across this little gem of a website. If you are just starting out with database design or need help coming up with a data model, this website gives some EXCELLENT examples of well designed databases.

Remind yourself to send attachments with Outlook

Ever forget to send an attachment to someone? The following code will search your outgoing message text for the word "attach." You can add as many words as you want. The code needs to go in the ThisOutlookSession module in Outlook VBA; make sure macro security is not set to High.

Private Sub Application_ItemSend(ByVal Item as Object, Cancel as Boolean)
If Instr(1, Item.Body, "attach", vbTextCompare) > 0 Then
If Item.Attachments.Count = 0 Then
MsgBox "You forgot the attachment."
Cancel = True
End If
End If
End Sub

For more examples of VBA using Outlook, check out Sue Mosher's site OutlookCode.

Microsoft Access Performance Considerations

While Access is certainly NOT an enterprise solution for databases, it can certainly provide small to medium size businesses with a very valid solution for their database needs. It's very common to read discussions about how Access cannot handle a multi-user environment or is too slow to use across the network. Most of the time, I find there are major design flaws with the databases these people are using. For most developer, there a few simple configurations and code tricks that can be used to attain reasonable performance from Access.

First, it is VERY possible to create a successful client/server application using linked tables for up to 20 concurrent users. But there are lots of details you must fix to be successful. To get good performance, it's all about keeping the "wire" cool -- that is, making sure you minimize the amount of data you ask JET to fetch and the number of threads you have open to the file server.

One of the most insidious "wire" eaters in Access 2000 and later is the silly Subdatasheet feature. If you leave SubdatasheetName set to [Auto], Access opens an extra thread AND fetches the rows for any recordset built on the table. You'll see a dramatic improvement in performance if you go to all the tables in the data file on the server and set this property for each table to [None].

You must also design your application to minimize the data fetched to the client machine. It is NOT true that JET on the client always does a full table scan. If you apply a filter on an indexed field, JET first fetches the index blocks and then sends specific requests to the file server for the data pages it needs to satisfy the request. So, it's important to both define appropriate indexes on your tables AND to NEVER request "all the tea in china" by opening a form or report unfiltered.

You should design custom "query by form" forms into your application that prompt the user to focus on the records needed for the task at hand, and the QBF code should refuse to proceed unless the user specified at least one filter value. You should also avoid any combo boxes that return more than about 200 rows. Although Access does try to optimize the combo box row source by not fetching all the rows until the user types in the box or drops it down, fetching 1000+ rows in a combo box is a performance killer. Most successful applications provide a way for the user to filter the combo first - for example, by selecting a product category that then filters the product name list. One alternative is to write code in the Change event to lookup the closest value for the user as the user types the entry they want. Single probes via DLookup are much more efficient than fetching the 1000+ row recordset.

In applications that might release the last active thread by returning to an unbound switchboard, performance can be improved by keeping one recordset open in code as long as the application is running. When you close the last bound form or report, Access releases the thread to the server - and if someone else has the data database open, it can take seconds to reopen the thread. If you keep a thread open, other processes in the application will share the open thread.

For reports, consider fetching the "unnormalized" report data to a local table and bind the report to that. Depending on how your report is designed, the report engine can end up fetching all the data from the server four or five times before it displays the result. Also, if you open a large report in Print Preview and the user pages back and forth in the result, the report engine will fetch the data several times again.

Be advised that "upsizing" to SQL Server is not a panacea. If your appication is badly designed for client/server, it won't run any better on SQL Server, and might actually run worse. You should upsize only when you need to manage more than 200 meg of data, you need true security, or you need to support more than 20 simultaneous users.

As for lookups - don't use them, at least not in table definitions. Check out my previous discussion for more information.