Category Archives: Tools

Export Import Wizard throws "Class not registered" error

Posted on by

When you launch the Export/Import Wizard from SQL Server Management Studio, you get the following error: 

TITLE: Microsoft SQL Server
——————————
This wizard will close because it encountered the following error:
——————————
ADDITIONAL INFORMATION:
Class not registered 

When you look into the Technical Details of the error, you will find the following call-stack:
===================================
This wizard will close because it encountered the following error: (Microsoft SQL Server)
===================================
Class not registered
——————————
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_DBProviderInfos()
at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetDtsDbProviderInfos(WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceCombo.PopulateProviders(Boolean fSources, WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnInitializePage(EventArgs e)
at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage nextPage)
at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender, EventArgs e) 

If you try to execute the following query, you will get “Class not registered” error or you will get 0 rows as the output: 

EXEC master..xp_enum_oledb_providers  

When you expand Server Objects -> Linked Servers -> Providers, you will find no entries. 

This issue happens because the OLE DB provider DLLs are not registered on the machine. The reason the Export/Import wizard fails is that the landing page of the Wizard executes the code which enumerates all the provider DLLs that are registered with SQL Server. 

You would have to manually register the OLE DB provider DLLs present in the C:\Program Files\Common Files\System\Ole DB\ directory. Or you can run the script below from a Query window to obtain the regsvr32 commands for the all the DLLs present in the OLE DB folder. After executing the regsvr32 commands, check if the providers are listed under Object Explorer in Management Studio. 

/*** Script start ***/ 

set nocount on 

create table #dlltable (rowid int identity(1,1),dllname varchar(4000)) 

insert into #dlltable 

exec xp_cmdshell 'dir "C:\Program Files\Common Files\System\Ole DB\"*.dll /b' 

delete from #dlltable where dllname is null 

update #dlltable set dllname = 'regsvr32 /s "C:\Program Files\Common Files\System\Ole DB\'+dllname+'"' 

select dllname as cmd from #dlltable 

drop table #dlltable 

set nocount off

/*** Script end ***/ 

Collecting Server Side Profiler Traces

Posted on by

When we want to monitor the kind of queries that hit across your SQL Server, most of us set up a Profiler Trace. But what most of us are not aware of is that there are two ways to setup a Profiler trace:

1. A GUI based Profiler trace

2. A Server side Profiler trace

A GUI based profiler trace causes a performance impact on the server as high as 25% depending on the events being captured. So if you are monitoring SQL Server with the help of profiler traces on a long term basis then it would always be advisable to setup a server side profiler trace.

Steps to setup a Server side Profiler Trace
1. Create a Profiler Trace using the GUI and then script out the trace to a file called say sql_trace.sql. Open the SQL_TRACE.SQL file in query analyzer/management studio query window and make the following changes

2. In the line “exec @rc = sp_trace_create @TraceID output, 2, N'<save location of trace file>’, @maxfilesize, NULL”, change the location where you want to store the trace file.

3. Make sure @maxfilesize parameter is set to 350 or a desirable size. (This makes sure that you roll over to the next trace file once the current size hits 350 MB)

4. Execute the script to create the trace. This will generate a Trace ID. The trace information can be obtained from fn_trace_getinfo function

5. To stop the trace, use sp_trace_setstatus ,0

6. To close the specified trace and delete its definition from the server us sp_trace_setstatus ,2

The events that you had configured while in the GUI would be present in the generated script and will be set using the Stored Prodecure sp_trace_setevent.

Remember that server side traces shouldn’t be writing to the same disk on which the SQL files are residing on and your profiler traces shouldn’t be writing to network drives/mapped drives or any other UNC shares. The destination file location should be a local disk. These are some recommendations that you need to keep in mind when configuring a server side profiler trace.

Useful Articles

283790 INF: How to Create a SQL Server 2000 Trace

283786 How to monitor SQL Server 2000 traces

Setting up Perfmon Logs

Posted on by
One of best ways to monitor your system performance for disk contention, high CPU, memory crunch etc. is to use Windows Performance Monitor Logs and Alerts. These logs can be used to capture historical information about different performance objects and would help us understand what was happening on the system at that point of time. In Windows Server 2008 & Windows Vista, Perfmon has a cool new utilty (a management MMC snap-in) called Reliability Monitor which helps you a bird’s eye view of your system stability.

Steps to setup Perfmon data collection for Windows Server 2003/XP
This can be done by opening up Perfmon:

  1. Click on “Performance Logs and Alerts”
  2. Click on “Counter Logs”
  3. Right click on the same and click on “New Log Settings”
  4. Give the log a name
  5. Click on “Add Objects” and add all the objects that are needed for your data collection
  6. Click on the “Log Files” tab
  7. You can change the log file location by clicking on the “Configure” button
  8. Click on the “Schedule” tab and then for “Start Log” and “Stop Log”, select the “Manually (Using the Shortcut menu)” option
  9. Click on the “OK” button to create the log
  10. Then right-click on the Log and click on “Start” to begin the logging
  11. Right-click on the log file and click on “Stop” to end the logging

How to setup a Perfmon data collection for Windows Server 2008/ Vista/ Windows Server 2008 R2

  1. Open up the Performance Monitor snap-in (Start -> Run -> perfmon)
  2. Expand the “Monitoring Tools” option and Right Click on “Perfomance Monitor” -> “New” -> “Data Collector Set“
  3. Give the data collector set a name and click on Next
  4. Give the location where you want to save the Perfmon Logs and click on Next
  5. Here you can provide the Run As user of leave that as the Default user
  6. Select the option “Save and Close” and click on Finish
  7. Then go back to the Perfmon snap-in main window and you should see a Data Collector set with the same name that you created under Data Collector Sets -> User Defined
  8. Click on the Data Collector Set and on the right hand pane, you should see a System Monitor Log Performance Counter. Right click on it and click on properties.
    • Under the Performance Counters tab, add the relevant performance counters required for your data collection, set the log format (binary, SQL, CSV, comma separated) and the sample collection interval time.
    • Under the File tab specify the file format name and logging properties for the file.
  9. Now you can start the Perfmon data collection by right clicking on the Data Collector Set and click on Start or you could do some more work for maintaining disk space by setting up some rules using the Data Manager to create .CAB files or delete older files in case we are setting up perfmon logs for long term monitoring.

There is hardly any performance impact in collecting perfmon logs on any server. Any perfmon data collection unlike other forms of data collection don’t generate voluminous data in terms of size but contain hordes of infromation which can provide valuable insight to an issue which is impacting critical business applications running on a server.

Windows Reliability and Performance Monitor

http://technet.microsoft.com/en-us/library/cc749154.aspx