Import SQL SERVER data to EXCEL

 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.