Article ID: 303822
Article Last Modified on 9/5/2007
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q303822
BUG #: 354508 (SHILOH_BUGS)
SYMPTOMS
The Transform Data Task Properties dialog box in the Data Transformation Services (DTS) Designer may take several minutes to open, depending upon these factors:
- The number of tables and views in the source database.
- The naming convention of the tables and views.
- The collation or sort order of the source database.
- The collation or sort order of the locale used by the client account.
- The amount of RAM and CPU speed of the client computer that is running the DTS Designer.
While you wait for the Transform Data Task Properties dialog box to open, in the Task Manager you can see that the Mmc.exe process consumes a lot of memory and also see 100% CPU usage on the client computer.
CAUSE
To fill the Table/View drop-down combo box, in the Transform Data Task Properties dialog box, the DTS Designer uses the standard Microsoft Windows API, SendDlgItemMessage, with the CB_ADDSTRING message. Because the drop-down combo box was created with the CBS_SORT style, all the entries are sorted according to the locale of the current user.
If the sort order of the database is the same as the sort order on the client computer, and the names of the views are all greater than the names of the tables, everything works quickly because SendDlgItemMessage only has to append to the end of the list.
However, if the two sort orders differ or the names of the views have to be sorted in between the names of the tables, SendDlgItemMessage must insert every new item into the correct place.
The Windows SendDlgItemMessage API is not designed to sort tens of thousands of entries, so the sort may take a long time.
RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
Hotfix
NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2.
The English version of this fix should have the following file attributes or later:
Date Time Version Size File name Platform
----------------------------------------------------------------
29-Jun-2001 15:49 8.00.405 455232 Ssqlgui.dll Intel
NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.
WORKAROUND
To work around this problem, use these steps:
- Export only the tables and views that are needed by your Data Transformation Services (DTS) package to a new database.
- Design and test your DTS package against the new database.
- Point the DTS package back to the original database before deployment.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.
REFERENCES
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
242391 INF: DTS Package Development, Deployment and Performance
Please refer to the documentation of the combo box message "CB_ADDSTRING" in the Platform SDK for more details.
Keywords: kbbug kbfix kbsqlserv2000presp2fix KB303822
