Generating SELECT INTO scripts for all user tables

Often on data recovery cases, we need to extract all the user table data into a new database. There are multiple options to perform this task:

1. Export/Import Wizard

2. Custom SSIS Task

3. Copy Database Wizard

4. BCP operations

5. SELECT INTO T-SQL commands

I find that the last option is the easiest. I use the following script to get all the T-SQL SELECT…INTO commands for all user tables in the old database:

 select 'select * into <new db>.''.[''] from <old db>.''.['']' from sys.objects a inner join sys.schemas b on a.schema_id = b.schema_id where a.type = 'U' 

Note: You will have to replace the new database and old database names with the appropriate names. The above script only works for versions of SQL Server 2005 and above.

How to remove unwanted Quotation Marks while importing a data file

I have had multiple questions on how users wanted to remove the quotation marks while importing the data into a SQL Server database table while using BCP or BULK INSERT. Last month I worked on a similar issue where the need was to strip out the quotation marks for all the data columns present in the .DAT file. Read my post on SQLServerFAQ to find out how this can be accomplished using BCP, BULK INSERT or if you like using UI, the Export/Import Wizard.

How to import data from an Excel File using T-SQL

If you have an Excel File named ExcelFile.xls, then you can perform SELECTs on that Excel file using the Excel Sheet name and the Jet Provider.

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\ExcelFile.xls;Extended Properties="Excel 8.0"')...[Data_Load$];

In case, you use the same Excel file over and over again, then you can use OPENQUERY using a Linked Server defined to your Excel File.

FROM OPENQUERY (DataLoad, 'select * from [Data_Load$]')

If you find that the data being imported for non-Text columns is NULL, then refer the following blog entry:

Export Import Wizard throws "Class not registered" error

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:
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 ***/