SQL Server exports data to Excel

1. Export informal Excel

EXEC Master. xp_cmdshell ' bcp t.dbo.tcad out D:\MySelf\output\Temp.xls-c-q-s '. "-U" sa "-P" sql2008 "'
--Parameter: S is the SQL Server name; U is the user; P is the password

2. Enable/disable xp_cmdshell

--to-allow advanced options to be changed.
EXEC sp_configure ' show advanced options ', 1
GO
--To update the currently configured value for advanced options.
RECONFIGURE
GO
--to enable the feature.
EXEC sp_configure ' xp_cmdshell ', 1
GO
--To update the currently configured value for this feature.
RECONFIGURE
GO

--to Disable advanced options to be changed.
EXEC sp_configure ' show advanced options ', 0
GO
--To update the currently configured value for advanced options.
RECONFIGURE
GO

EXEC sp_configure ' xp_cmdshell ', 0
GO
--To update the currently configured value for this feature.
RECONFIGURE
GO

3. Enable/disable AD Hoc distributed Queries

--Enable the method of ad Hoc distributed queries, execute the following query statement, you can:
exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure
--After use, be sure to close it, because this is a security risk, execute the following SQL statement to close:
exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure
exec sp_configure ' show advanced options ', 0
Reconfigure

4, the legendary export of formal Excel (experimental unsuccessful)

/*--Data Export Excel
Exporting data from tables to Excel, containing field names, files as real Excel files
If the file does not exist, the file will be created automatically
The table is created automatically if the table does not exist
Based on commonality considerations, only export of standard data types is supported
--Jiangjian 2003.10 (please keep this information for reference)--*/
/*--Invocation Example
P_EXPORTTB @tbname = ' area data ', @path = ' C # ', @fname = ' Aa.xls '
--*/
--create proc P_EXPORTTB
Declare
@tbname varchar (100),--The name of the table to export
@path nvarchar (1000),--File storage directory
@fname nvarchar (250),--file name, default table name
@err int,
@src nvarchar (255),
@desc nvarchar (255),
@out int,
@obj int,
@constr nvarchar (1000),
@sql varchar (8000),
@fdlist varchar (8000)
Set @tbname = ' TCAD '
Set @path = ' D:\MySelf\output\ '
Set @fname = ' Test '
--Parameter detection
If IsNull (@fname, ") ="
Set @[email protected]+ '. xls '
--Check if the file already exists
If Right (@path, 1) <> ' \ '
Set @[email protected]+ ' \ '
--create table #tb (a bit,b bit,c bit)
Set @[email Protected][email protected]
--insert into #tb exec master. Xp_fileexist @sql
--Database Creation statement
Set @[email Protected][email protected]
--if exists (select 1 from #tb where a=1)
Set @constr = ' Driver={microsoft Excel DRIVER (*.xls)};D sn= ""; Readonly=false '
+ '; create_db= "' [email protected]+ '";D bq= ' [email protected]
--else
--set @constr = ' provider=microsoft.jet.oledb.4.0; Extended properties= "Excel 8.0; Hdr=yes '
--+ ';D atabase= ' [email protected]+ ' "'
EXEC @constr

Keep trying!!!

SQL Server exports data to Excel