DTS DLL Debugging

Debugging Visual Basic (VB) COM DLLs that you use in Data Transformation Services (DTS) is easier than it sounds. First, open the DTS Package in the DTS Designer. Then, start the VB Interactive Debugging Editor (IDE) and open the WebDataFetcher project. Within the VB IDE, you can run the project with a full compile by pressing Ctrl+F5. Running the project with a full compile is a good way to check for blatant syntax errors. The program will highlight in blue any lines that contain syntax errors.

Related: 34 Development Tips

You can also step through the code and inspect how it works or debug errors in the programming logic by pressing F8, which puts the VB IDE into debug mode. The VB IDE will temporarily register the WebDataFetcher project. In debug mode, the VB IDE will stop on the first line of code in the project before it's executed. When you run the DTS Package, the VB IDE isn't in the screen view. After you run the Package, click the VB IDE icon to bring the IDE into view. The program will highlight in yellow the first line of the GetFile function. VB IDE will execute this line next. Each subsequent time you press F8, the VB IDE will execute the present line, move to the next line, and highlight it. You can move the flashing cursor over any variable, such as aURL, then pause, and a window will pop up that contains the value of that variable. If you don't want to step through code, just press F5 and VB IDE will run the rest of the code without stopping. When you want to stop the VB IDE from running in debug mode, click the End item in the Run menu list.

The Security Connection


  1. Use Windows-only authentication with SQL Server.

Fast Bulk Load in SQL Server 7.0

SQL Server 7.0 offers several high-speed mechanisms for loading data. Bulk copy program (bcp) is a high-speed file-import utility that SQL Server has supported since the early days of the database management system (DBMS). Bcp lets you quickly load large files and is often a good choice, but it's not user friendly.




7 Cursor Cues

Proper API cursor use can mean all the difference in the world when it comes to application scalability. The Microsoft TechNet article "Top 10 SQL Server 7.0 Support Issues and Resolutions" gives several cursor tips, including

  • Use default settings for singleton SELECT statements (which return one row) or other small result sets. Caching a small result set on the client and scrolling through the cache is more efficient than asking the server to implement a cursor.
  • Use the default settings when fetching an entire result set to the client, such as when you're producing a report. Default result sets are the fastest way to transmit data to the client.
  • You have to use default result sets for any T-SQL statement or batch of T-SQL statements that generate multiple result sets.
  • In joins, static or keyset-driven cursors can be faster than dynamic cursors. You must use static or keyset-driven cursors when you want to do absolute fetches.
  • In applications that use server cursors, each call to an API fetch function or method causes a round-trip to the server. Applications can minimize these round-trips by using block cursors with a reasonably large number of rows returned on each fetch. You can set the block size by using ADO's CacheSize property. I like to use batch sizes in the range of 100 to 500 rows.
  • Consider SQL Server 7.0's cursor performance optimization feature, called Fast_Forward Cursors. SQL Server Books Online (BOL) has a lot of information about Fast_Forward Cursors.

Lost in Translation?

Q. I'm a Visual Basic (VB) developer and new to T-SQL programming. Does T-SQL have a function equivalent to VB's InStr() function?

A. Life would be much easier if functions had the same name across different programming languages.

T-SQL's charindex function, for example, lets you do the same thing as VB's InStr(), which specifies the first occurrence of one string in another string. Charindex's syntax is

CHARINDEX ( expression1 , expression2 
\[ , start_location \] )

Expression1 is a short character data type expression that contains the sequence of characters you want to find. Expression2 is the character string that you want to search. And start_location is the character position where charindex starts searching for expression1 in expression2. If you don't specify a start_location or if start_location is a negative number or zero, the charindex search starts at the beginning of expression2.

No Datetime Primary Keys, Please



Variant Behavior



Templates for Development

I use templates in two ways: to develop objects and to reduce the amount of typing that I have to do when I write a section of code. At the company I work for, I customize the templates that I use for development according to the company's development standard. The templates provide handy development snippets of code that I use when I compile a series of objects on a server. Figure A shows my code template for generic stored procedures. This template consists of four sections: drop, create, and permissions sections and a success-or-failure notification section.


Xp_sendmail Rules

You can use xp_sendmail extended stored procedure in triggers, stored procedures, and batch commands to incorporate SQL Mail functionality into code. The xp_sendmail extended stored procedure is in the master database, but with the proper syntax, you can call it from any database. The proper syntax for calling xp_sendmail from another database is master.dbo.xp_sendmail followed by any of the input parameters in Table B (a list of these parameters is also available in BOL).

You should be aware of the following rules when using the xp_sendmail extended stored procedure:

  • You must configure SQL Mail sessions before you use xp_sendmail.
  • Only one user can execute xp_sendmail at a time. If you execute long-running queries, be aware that other connections must wait until the first connection has completed processing.
  • Xp_sendmail queries are subject to locks and blocks just like any other query.
  • Xp_sendmail's Execute permissions default to members of sysadmin, but you can grant these permissions to other users.

Using xp_sendmail from SQL Server with the proper planning about when and who should receive these email messages can simplify administration and troubleshooting.

Use Codebehind to Separate ADO.NET from HTML

Active Server Pages (ASP) developers need to be aware of one change that will break some existing ASP pages you migrate: ASP.NET doesn't let you put functions inside the <% %> render blocks. You need to place all server-side functions within a

Sponsored Introduction Continue on to (or wait seconds) ×