In the operational database, we will inevitably encounter the import and export of data in the database problem, but sometimes when using SQL statements, we will report a variety of errors, such as the following error.
Wrong various find Ah, the final solution, now summarized the reasons are as follows:
1. The ad Hoc distributed Queries Service is not turned on:
The opening and closing methods are as follows:
1--Enable AD Hoc distributed Queries:2 exec sp_configure ' show advanced options ', 1 3 reconfigure 4 exec sp_config Ure ' ad hoc distributed Queries ', 1 5 reconfigure 6 7 --close Ad hoc distributed queries:8 exec sp_conf after use is complete Igure ' Ad Hoc distributed Queries ', 0 9 reconfigure10 exec sp_configure ' show advanced options ', 011 Reconfigure12
2.microsoft.jet.oledb.4.0 engine settings prevent import and export.
The settings are as follows (SQL2005 for example):
SQL Server Management Studio-server Object--Link server-->microsoft.jet.oledb.4.0--> Right-click Properties--Access interface options: None
The logon identity settings for 3.SQL Server (MSSQLSERVER) and SQL full-filter Deamon Launcher services are incorrect:
Here's how it's set up:
Change the logon status of SQL Server (MSSQLSERVER) and SQL Full-filter Deamon Launcher (MSSQLSERVER) two services to LocalSystem
4. Make sure the data you want to import is formatted correctly.
5. Ensure that Excel tables or other database tables are not opened by other software when importing data.
6. Determine your version of Office, and select the appropriate SQL statement to import and export. (Import as an example)
If it is before 2003, the following SQL statement is generally used:
1 insert INTO Dbo.data_cldeb (GG,XH,CL,DE) 2 Select spec, model, ' copper ', copper from OpenDataSource (' Microsoft.Jet.OLEDB.4.0 ', 3 ' data S Ource=f:\gongzuo\ series 7.xls; Extended properties=excel 5.0 ') ... [sheet1$]
If Office is after 2007, you typically use the following SQL statement:
1 insert INTO Dbo.data_cldeb (GG,XH,CL,DE) 2 Select spec, model, ' copper ', copper from OpenDataSource (' Microsoft.Jet.OLEDB.4.0 ', 3 ' data Source=f:\gongzuo\ series 7.xls; Extended properties=excel 8.0 ') ... [sheet1$]
Import Export database data, error, OLE DB provider "Microsoft.Jet.OLEDB.4.0" of the linked server "(NULL)" returned the message "Unspecified error".