Import SQL SERVER data to EXCEL

Source: Internet
Author: User
Tags sql server books table definition

 1. Insert data to Excel in SQL SERVER

2. Export the data queried in SQL SERVER into an Excel file

 

  

1. Insert data to Excel in SQL SERVER

Insert into opendatasource ('Microsoft. JET. OLEDB.4.0 ', 'excel 5.0; DATABASE = c: \ book1.xls ')... [Sheet1 $] SELECT [BlogID], [Title] FROM [database name]. [dbo]. [Blog]

Possible problems:

1. "SQL Server blocks access to the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' component because this component has been disabled as part of the Server's security configuration. The system administrator can enable 'ad Hoc Distributed Queries 'by using sp_configure '. For more information about enabling 'ad Hoc Distributed querys', see "peripheral application configurator" in SQL Server books online ". "

Solution:

-- Enable Ad Hoc Distributed Queries: exec sp_configure 'show advanced options', 1 reconfigureexec sp_configure 'ad Hoc Distributed Queries ', 1 reconfigure -- disable Ad Hoc Distributed Queries after use: exec sp_configure 'ad Hoc Distributed Queries ', 0 reconfigureexec sp_configure 'show advanced options', 0 reconfigure

 

2. "The number of column names or provided values does not match the table definition. "

Solution: The Sheet 1 in book1.xls must have fields corresponding to the excel table; otherwise, an error occurs. You can query the EXCEL table structure before insertion.

/* View the xls table structure */SELECT * FROM OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "c: \ book1.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... [SHEET1 $]

 

3. "message 7399, level 16, status 1, 2nd rows
The ole db access interface "Microsoft. Jet. OLEDB.4.0" of the linked server "(null)" reports an error. The provider does not provide any information about the error.
Message 7303, level 16, state 1, 2nd rows
You Cannot initialize the data source object of the ole db access interface "Microsoft. Jet. OLEDB.4.0" of the linked server "(null. "

Solution: Make sure that the xls file is closed.

 

 

2. Export the data queried in SQL SERVER into an Excel file

EXEC master .. xp_mongoshell 'bcp "SELECT BlogID, Title FROM [database name]. [dbo]. [Blog] "queryout c: \ temp1.xls-c-q-S" 6350BFDFB9DD413 "-U" sa "-P" sa_123 "'/* output into NULL Start copying... NULL has eight rows copied. Network packet size (bytes): 4096 total clock time (milliseconds): 1 average: (8000.00 rows per second .) NULL (7 rows affected )*/

Possible problems:

1. "SQL Server blocked access to 'sys. xp_expose shell' IN THE 'xp _ external shell' process, because this component has been disabled as part of the Server's security configuration. The system administrator can enable 'xp _ javasshell' by using sp_configure '. For more information about enabling 'xp _ external shell', see "peripheral application configurator" in SQL Server books online ". "

Solution:

/* Allow */EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp _ cmdshell', 1; RECONFIGURE; /* disable */EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp _ cmdshell', 0; RECONFIGURE;

 

2. "The replication direction must be 'in', 'out', or 'format '.
Usage: bcp {dbtable | query} {in | out | queryout | format} data file"

Solution: Double quotation marks are used, and all codes are placed in one line. Carriage return or line feed are not allowed.

 

 

Referenced from:

Data conversion http://www.microsoft.com/china/community/column/31.mspx for SQL SERVER and ACCESS, EXCEL

SQL Server blocks access to the STATEMENT ''openrowset/OpenDatasource ''OF THE ''ad Hoc Distributed Queries ''' component.

Import EXCEL Data to SQL http://topic.csdn.net/u/20091213/19/72280C51-B7D8-43E0-9964-A7142604F0B9.html

When bcp is executed, it always prompts that the replication direction must be 'in', 'out', or 'format '. The expert gave me some advice.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.