I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Export Query results to Excel with Column names in T-SQL

In this thread  it's shown how to export query results to excel File using OpenRowset. The problem with this is that you have to create an excel  file with columns. For me that is simply unacceptable because i had to export any query result to excel and having to make an excel file for each query is simply ridicolous. So I went looking into BCP. Now BCP can't export column names adn i saw some solutions that use a view but nothing really dynamic. So i had to do a little workaround.

It resulted in this stored procedure. Data is exported in BCP RAW format. It can also be exported to any format that BCP is familiar with.

use master
go
if object_id('spExportData') is not null
    drop proc spExportData
go
create proc spExportData 
(
    @dbName varchar(100) = 'master', 
    @sql varchar(5000) = '',     
    @fullFileName varchar(100) = ''
)
as
if @sql = '' or @fullFileName = ''
begin
    select 0 as ReturnValue -- failure
    return
end 
-- if DB isn't passed in set it to master
select    @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
    drop table ##TempExportData
if object_id('##TempExportData2') is not null
    drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select    @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' + 
substring(@sql, charindex('from', @sql)-1, len(@sql)) exec(@dbName + @tempSQL) if @@error > 0 begin select 0 as ReturnValue -- failure return end -- build 2 lists -- 1. column names -- 2. columns converted to nvarchar SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name, @columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),' + column_name + case when data_type in ('datetime', 'smalldatetime') then ',121' when data_type in ('numeric', 'decimal') then ',128' when data_type in ('float', 'real', 'money', 'smallmoney') then ',2' when data_type in ('datetime', 'smalldatetime') then ',120' else '' end + ') as ' + column_name FROM tempdb.INFORMATION_SCHEMA.Columns WHERE table_name = '##TempExportData' -- execute select query to insert data and column names into new temp table SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID]
from ##TempExportData union all select '
'' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]' exec (@sql) -- build full BCP query select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW' -- execute BCP Exec master..xp_cmdshell @sql if @@error > 0 begin select 0 as ReturnValue -- failure return end drop table ##TempExportData drop table ##TempExportData2 select 1 as ReturnValue -- success go declare @sql varchar(6800), @dbName varchar(100), @fullFileName varchar(100) select @dbName = 'northwind', @sql = 'select * from orders order by orderdate', @fullFileName = 'e:\test.xls' exec master..spExportData @dbName, @sql, @fullFileName

Maybe it's ugly but it gets the job done. :))

Print | posted on Tuesday, July 25, 2006 3:11 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# Here's my approach

Well.....here was my shot at doing this....

http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx

7/26/2006 3:06 PM | Brett
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

was that a cursor i saw brett? :))
7/26/2006 3:08 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

What's the point of the declaration on all 3 fields to hit 8000 varchar. What happens when one hits that? I thought the size of the row matters ;)
7/26/2006 4:50 PM | Jon
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Well if you hit 8000 chars with a query.... that's really long query then... then you should probably shorten it :)
7/26/2006 5:13 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Let's me study this! :)
Yet I'm out of my usual (brilliant) shape (apparently).
Heh.
7/26/2006 7:04 PM | Stoad
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

You could have just used REPLCE command here

select @tempSQL =
left(@sql, charindex('from', @sql)-1)
+ ' into ##TempExportData '
+ substring(@sql, charindex('from', @sql)-1, len(@sql))
7/27/2006 10:21 AM | PP
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Error in code. Insted of

if object_id('spExportData') <> null
drop proc spExportData
go

Use this

if object_id('spExportData') IS NOT null
drop proc spExportData
go
7/27/2006 10:28 AM | PP
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Thanx. fixed the error.
The replace you can fix yourself if you use the code :)
7/27/2006 10:36 AM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

test.xls file not creating but its giving output like below:

Password:
NULL
Starting copy...
SQLState = 01000, NativeError = 5701
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'Northwind'.
NULL
831 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 15
NULL

ReturnValue
1

9/13/2006 2:04 PM | jilani
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

the file gets created on the computer that is running sql server. not on the client.

check there.
9/13/2006 2:11 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Select a,b,c,d into tmpMyTable from tblMyTable


and it will craete a table called tmpMyTable and you can export the data from that table to an excel sheet with aDTS package, ofcourse with column titles

;)) Seems Silly? :))
9/14/2006 11:33 AM | Deepu Sreedhar
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

what are you trying to say exactly?

If I have a few tables or query results to export to excel i don't feel like running a DTS package for that when i can just run a stored procedure.
9/14/2006 11:38 AM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

This is great! I've been looking for something like this for a while.

Is there anyway to generate a csv file rather than a xls file (just changing xls to csv works but it doesn't havew any , in it)

Thanks for this though.
9/19/2006 3:48 PM | Andrew
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

you're welcome to extend the sproc :)
add a parameter in which you specify the separator and add it to the bcp comand.
9/19/2006 3:59 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Ah.

Got it.

this
-- build full BCP query
select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'
-- execute BCP

becomes

-- build full BCP query
select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -t"," -CRAW'
-- execute BCP

Thanks
9/19/2006 4:14 PM | Andrew
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi...
It is exactly I am looking...useful thread. guys I am looking for comma seperated and single quote file so can be imported into cross databases.. any knows ? I tried the following command... but error came up.

EXEC master..xp_cmdshell 'bcp "Northwind.dbo.EXPORT_ORDERS" OUT "C:\Orders.txt" -Slocalhost -T -n -r\n -t| -q'
Error: '-q' is not recognized as an internal or external command

help please....

2/15/2007 10:38 AM | sonny singh
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi Sonny,

You need to enclose the | (pipe) inside double quotes;

EXEC master..xp_cmdshell 'bcp "Northwind.dbo.EXPORT_ORDERS" OUT "C:\Orders.txt" -Slocalhost -T -n -r\n -t"|" -q'



3/15/2007 12:06 PM | Mark
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi..
for call procedure from Query Anylyzer i don't have any problem, but if i call procedure from trigger (After Update) the process can't be work
3/29/2007 2:32 PM | husen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

calling it from trigger is a bad idea anyway.
3/29/2007 2:37 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

anyway for update table just end of month (process only by supervisor) and have only one record, can you help me please...
fyi (I Use MSSQL 2000)
3/30/2007 3:57 AM | husen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

so how do you expect anyone to help if you just say: "It doesn't work" ??
what's the error you get?
what happens etc?
a little more info would be nice...
3/30/2007 10:18 AM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

ok i am so sorry,
when i update a table the sql like hang (no error message and i cant click anywhere in the sql enterprise manager and cant close a table (to close a table i must end task the enterprise manager) and i cant open a table again until stop and start service manager.

my trigger like this :

CREATE TRIGGER Run1_Excel
ON Table_ProEx
AFTER UPDATE
AS
DECLARE @Process VARCHAR (1)
SELECT @Process = OK_RUN FROM Table_ProEx
IF @Process ='Y'
Begin
EXEC master..spExportData 'SALES1','SELECT * FROM Sal_New','C:\ABC.XLS'
End


in table Sal_New i only have 30 records

tx for support









3/30/2007 3:51 PM | husen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

run profiler and see what statements are executing
3/30/2007 3:56 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Ok,
i have 3 statements in profile (until update record)
1. set implicit_transactions on
2. -- network protocol: LPC
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
3. set quoted_identifier off


3/30/2007 5:18 PM | husen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

sorry i am asking again, what must i do if have statements like sending before
3/30/2007 6:45 PM | husen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

no no...
does the statement in the trigger execute or not?
SSMS doesn't have a timeout so it will just keep on running.
4/2/2007 5:36 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi There,

I have been searching for something like this for ages, fantastic stuff.

Slight issue howerver, within the table that I want to export there are fields with data_type 'image' and as a result I am getting the following error.

"Explicit conversion from data type image to nvarchar is not allowed."

These fields with datatype image are actually a binary code and I can not convert them to something else because of the application that uses these fields.

I have tried adding the data_type into the field to contvert it to nvarchar

when data_type in ('image') then ',256'

but this does not seem to resolve the issue.

Any suggestions?
4/11/2007 2:00 AM | DT
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

i have no idea if excel accutally supports image datatype.
but i guess you could change the sproc to not convert to nvarchar when the column is image.
but then you won't have a column name for it...

sorry but i have no practical idea about this one...
4/11/2007 9:31 AM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Can the file be created on my local (client) disc instead of server? How?
4/13/2007 1:42 PM | Martin
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

it can be created on your computer if you have a public shared folder that the server can access.
then you specify the path to the share in the sproc.

Other than that no.
4/13/2007 2:19 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Ok, thanks...
4/13/2007 2:24 PM | Martin
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi
First of all Thanks for the great SP.

I am getting the following error when I runned the SP, Can you drop some pointers where I am going wrong

Here is what I Ran

declare @sql varchar(6800), @dbName varchar(100), @fullFileName varchar(100)
select @dbName = Db_Name(), @sql = 'select * from Employee ', @fullFileName = 'C:\test.xls'
exec master..spExportData @dbName, @sql, @fullFileName

The Following Error I got

(3133 row(s) affected)
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '4000'.

(13 row(s) affected)
Msg 3701, Level 11, State 5, Procedure spExportData, Line 55
Cannot drop the table '##TempExportData2', because it does not exist in the system catalog.

(1 row(s) affected)


Thanks
5/5/2007 8:47 AM | Chirag
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Sorry for bothering, I guess i got it the eror was due to @sql going above 8000 characters.
5/5/2007 9:15 AM | Chirag
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi Mladen
Its a very useful query. But, when I try executing the query as below. Its taking huge time and does not give out any result. I renamed your SP for my convenience. When I try debugging it in sql2k. Its getting hanged at the 'bcp' statement inside the SP. Do I have to turn some network protocols on etc to work it out.

Early reply is much appreciated

use master
exec dbo.New1_ExportData 'Myrecords', 'select * from records', 'C:\Documents and Settings\Administrator\Desktop\Book1.xls'


Thanks!
Santhosh

5/9/2007 4:33 PM | santhosh
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

you must have permissions to run master..xp_cmdshell
5/9/2007 4:35 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

and alos the file gets created on the server not on the client computer.
5/9/2007 4:35 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

I am using SA credentials. So, it should work. But, it is not working. Its got stucking at the same bcp statement. Query is executed on the server only. Any pointers on this?

Thanks!
Santhosh
5/9/2007 4:56 PM | santhosh
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

the file that is created is saved on the sql server not at the client you're on.
5/9/2007 4:58 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Yeah, I mean the same. I.e., file is not getting created on the server. The basic thing is its getting stucked at the bcp statement itself. Its not going beyond.The status message shows "Executing batch query....". The table hardly contains 30 rows to export. I waited for fifteen minutes to see. But no result.-
5/9/2007 5:03 PM | santhosh
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

This solution is great! But, what if I want to add restrictions to the WHERE CLAUSE ?

For example, I run the procedure inside another one, and I want to filter the select from and up to two different dates.

I have this variables:

@FecIni datetime
@FecEnd datetime

Who can I add this restricction to the select that recieves one of the parameters in your procedure??

Thanks a lot if anyone answer this!!


Sherar
9/7/2007 5:09 AM | Sherar
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

I 've solved my problem by declaring a varchar type variable and adding my query to it.

Maybe it is useful for someone else so I pass you the code:

declare @Sql varchar(5000)
set @Sql = 'select convert (varchar(12),Fecha,103) as Fecha,Tipo,Nro,Nombre,Cuit,
ConIva = case ConIva when ''Responsable Inscripto'' then ''R.I''
when ''Monotributista'' then ''Mono'' end,
from taFactCom inner join taProveedores on taProveedores.ProvId= taFactCom.ProvId
inner join taEmpreProv on taEmpreProv.ProvId= taProveedores.ProvId
inner join taConceptos on taConceptos.ConceptoId = taFactCom.ConceptoId
where Fecha >=''' + CONVERT(varchar(10),@FechaDesde,112)+'''
order by convert (datetime,Fecha,101) desc'

exec spExportData 'NazaDB',@Sql,'c:\Maria\Reportes\Proveedores.xls'

@FechaDesde is a variable that my procedure recieves.

Thanks a lot again,

Sherar
9/7/2007 5:29 AM | Sherar
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

I was excited to find this stored procedure, but it's not working completely correctly for me. First, I had to add a "-T" to the end of the bcp line (to establish a trusted connection - it wouldn't connect prior). Now, when I run, I get the following output. It seems to me that bcp is struggling to define the column names?

NULL
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
SQLState = 01000, NativeError = 5701
Warning = [Microsoft][SQL Native Client][SQL Server]Changed database context to 'TellUs_CRL'.
NULL
BCP copy out failed
NULL

Can you provide any assistance?
9/17/2007 11:17 PM | Jay
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Any ideas why the short version of your code below does not produce the results? (although ##TempExportData has the source data) Thanks!
...
declare @sql varchar (2000), @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select @sql = 'select DESCRIPTION, SHORT_NAME from AGENCY '
select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@tempSQL)
SELECT @columnNames = COALESCE( @columnNames + ',', '') + COLUMN_NAME,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'
+ COLUMN_NAME + case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
when data_type in ('datetime', 'smalldatetime') then ',120'
else ''
end + ') as ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = '##TempExportData'
-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID]
from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
exec (@sql)
select * from ##TempExportData2

+++++++++++++++++++++++++++++++++++OUTPUT FROM Query Analyser+++++++++++++++++++++++++++

(32 row(s) affected)

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '##TempExportData2'.
9/20/2007 5:57 PM | paperless
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

use print @sql instead of exec (@sql)
and see what query you are executing.
9/20/2007 6:00 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Adding print @sql before the last exec (@sql) line produces the same output as before.
The print @sql does not output anything...?
9/20/2007 6:27 PM | paperless
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

it does in messages tab. this will display the sql statement being run
9/20/2007 6:33 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Neither tab has any output from the print @sql. The output is the same with or without this print @sql line...

Note that when I place a print @tempSQL in the beginning it outputs 'select DESCRIPTION, SHORT_NAME into ##TempExportData from AGENCY'
9/20/2007 6:58 PM | paperless
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Note also that I placed an if @columnNames is null print 'Empty @columnNames' and... it does print out
9/20/2007 7:26 PM | paperless
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

I don't have write or execute permissions for stored procs where I'm at, and needed something simple in Query Analyzer that will assist in outputting column names along with the output, when saving in CSV output. My results below.

SET NOCOUNT ON;
declare @sql varchar(2000), @columnNames varchar(8000),
@colID varchar(8000), @tempSQL varchar(8000),
@useDB varchar(8000), @dbName varchar(8000);

SET @dbName = 'AccountMgmt';
select @sql = '
SELECT TOP 1 *
FROM dbo.AccountOpening
WHERE Account_ID <> ''''
ORDER BY NEWID()
'

IF OBJECT_ID('tempdb..##TempExportDataA') IS NOT NULL
drop table ##TempExportDataA;
IF OBJECT_ID('tempdb..##TempExportDataB') IS NOT NULL
drop table ##TempExportDataB;

select @dbName = 'use ' + @dbName;
select @tempSQL = left(@sql, charindex('from', @sql)-1) +
' into ##TempExportDataA ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))

exec(@dbName + @tempSQL);

SELECT @colID = COALESCE( CONVERT(varchar(255),@colID) + ',', '') + CONVERT(varchar(255),@colID),
@columnNames = COALESCE( @columnNames + ',', '') + [name]
from tempdb..syscolumns
where id = object_id('tempdb..##TempExportDataA');

-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportDataB from ##TempExportDataA'
exec (@sql);

--output query results
select * from ##TempExportDataB

IF OBJECT_ID('tempdb..##TempExportDataA') IS NOT NULL
drop table ##TempExportDataA;
IF OBJECT_ID('tempdb..##TempExportDataB') IS NOT NULL
drop table ##TempExportDataB;
10/2/2007 11:28 PM | Eve McGivern
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

and your question, young lady, would be?
10/2/2007 11:31 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

No question; just posting here in case someone needs something simple that's not in a stored procedure; also didn't have access to spExportData and xp_cmdshell. Of course, this was all done after I was informed there's an option in Query Analyzer that will output with column headers. :)
10/3/2007 1:29 PM | Eve McGivern
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

cool! thanx for posting that.
10/3/2007 5:00 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Thanks!
10/9/2007 3:59 AM | Chienld
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hello,
I've Sql 2005 Express and SSMSE and I try the query to export to an XLS.
When I execute it my result is:

NULL
Avvio della copia in corso...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Impossibile risolvere le regole di confronto a livello di colonna
SQLState = 01000, NativeError = 5701
Warning = [Microsoft][SQL Native Client][SQL Server]Il contesto di database è stato sostituito con 'Test'.
NULL
Copia BCP out non riuscita
NULL

What should I do??
What goes wrong??

Thanks in advance
11/19/2007 11:20 AM | Elet
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

well... i don't read italian.
but it looks like you have specified the same column name twice in your query.
11/19/2007 11:27 AM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Sorry Mladen,
now I try to translate it:

NULL
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
SQLState = 01000, NativeError = 5701
Warning = [Microsoft][SQL Native Client][SQL Server]Changed database context to 'Test'.
NULL
BCP copy out failed
NULL

I used the query on the top of this page.
11/19/2007 12:05 PM | Elet
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

you have to change a collation of your columns. try Latin1_General_CI_AS
11/19/2007 1:13 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

On my db the collation is already set to: Latin1_General_CI_AS
11/19/2007 2:44 PM | Elet
Gravatar

# re: Export Query results to Excel with Column names in T-SQL


Thanks for preparing this great SP

i am getting output as follows . excel file is not created and also output is not transferring to excel
i am not getting where to provide -T as i am using trusted connection.plz reply me on my mail id

thanking you
chirag shah




User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
11/25/2007 1:40 PM | chirag shah
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Code is great! How can I get to output pipe delimited???
12/5/2007 6:30 PM | DNF
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

read the 15th comment posted by Andrew.
you have to add -t and specify the pipe as the delimiter to the bcp command.
12/5/2007 6:42 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

hi nice article!!

i have a cursor running based on some criteria and for each i export excel thru bcp. now i would like to have all those in a single workbook ie., i would like to export into different worksheets of excel

any help would be greatly aprreciated!!

12/20/2007 10:01 PM | Pramod Kumar
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Great procedure Mladen, very helpful. Thank you very much for posting it.
1/9/2008 10:38 AM | Goga
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

I am getting following error:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
Running bpc command with -T trusted connection (local Admin).
The collation is set to: SQL_Latin1_General_CP1_CI_AS

Thanks for your help

1/15/2008 6:32 PM | HP
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi Mladen, I found you script is very helpful. One more question. I have a couple of stored procedures. I want to export stored procedures' results to Excel with column names. How to do that? Thank you very much.
1/23/2008 7:33 PM | Mike
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

put the sproc result into a temp table
insert into #temp(your columns)
exec yourSproc

then put values from #temp table to excel
1/23/2008 7:45 PM | Mladen
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

in the above procedure how can u insert worksheet.. if there has to be multiple data into multiple worksheet in the same excel file..
2/5/2008 10:21 PM | mt
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi,

I am getting following error:
NULL
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
SQLState = 01000, NativeError = 5701
Warning = [Microsoft][SQL Native Client][SQL Server]Changed database context to 'TestDB'.
NULL
BCP copy out failed
NULL

I am Running bpc command with -T trusted connection (local Admin).
The collation is set to: SQL_Latin1_General_CP1_CI_AS

Here is my bcp command:
select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2 " queryout "' + @fullFileName + '" -S .\SQLEXPRESS -T -c -C RAW '


Thanks for your help
2/6/2008 12:20 AM | HP
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

I ran it on sql server and i got this error message,

Password:
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Invalid Instance()).
NULL
3/5/2008 12:31 AM | Doddy
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Use Employees
go
if object_id('spExportData') IS NOT null
drop proc spExportData
go
create proc spExportData
(
@dbName varchar(100) = 'Employees',
@sql varchar(5000) = '',
@fullFileName varchar(100) = ''
)
as
if @sql = '' or @fullFileName = ''
begin
select 0 as ReturnValue -- failure
return
end
-- if DB isn't passed in set it to master
select @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
drop table ##TempExportData
if object_id('##TempExportData2') is not null
drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'
+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
when data_type in ('datetime', 'smalldatetime') then ',120'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'
-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
exec (@sql)
-- build full BCP query
select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -t"," -CRAW'

-- execute BCP
Exec Employees..xp_cmdshell 'bcp "Employees.dbo.EmpDetails" OUT "d:\test3.xls" -Slocalhost -T -n -r\n -t"|" -q'
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
select 1 as ReturnValue -- success
go
declare @sql varchar(6800), @dbName varchar(100), @fullFileName varchar(100)
select @dbName = 'Employees', @sql = 'select * from Empdetails', @fullFileName = 'd:\test3.xls'
exec Employees..spExportData @dbName, @sql, @fullFileName

This is the way i wrote.My Excel file is not getting recognised.
I need help.
3/26/2008 11:33 AM | veena
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

I have a column which datetime and the other is int. When I open my excel sheet , the message is appearing that the data is not in correct format. Please tell me the solution to this. Thanks in advance.
3/26/2008 11:40 AM | veena
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi Mladen,

I got the following error message that some of people already posted before. I just wonder why it always shows "Unable to resolve column level collations". The script looks fine.
---------------------------------------------------------------------
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
SQLState = 01000, NativeError = 5701
Warning = [Microsoft][SQL Native Client][SQL Server]Changed database context to 'Northwind'.
NULL
BCP copy out failed
---------------------------------------------------------------------

Thanks.
5/12/2008 4:16 AM | Albert
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hi Mladen,
I found your script is very helpful for me, But the same code is working fine in Sql server 2000
But when i tried the same code in Sql Server 2005 i am gettign the following error, i think so many people were getting same error.

NULL
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
SQLState = 01000, NativeError = 5701
Warning = [Microsoft][SQL Native Client][SQL Server]Changed database context to 'HTMS-PROD-1'.
NULL
BCP copy out failed
NULL

Do you know how to solve this error?
HimaSagar
5/17/2008 1:24 PM | Hima Sagar Kutikuppala
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

I altered the script a bit to use a static sql and a cursor to create separate files:

Declare tCur Cursor for
Select
[Id], [Description]
from AccountType

declare @typeid int, @typedesc varchar(255)

open tCur

Fetch Next from tCur into @typeid, @typedesc

While @@Fetch_Status <> -1
Begin

declare @sql varchar(5000),
@columnNames varchar(8000),
@columnConvert varchar(8000),
@rowcount int

set @sql = ''
set @columnNames = null
set @columnConvert = null

Select distinct
a.Id as AccountNumber,
type.Description,
isnull(cn.First + ' ','') + isnull(cn.Middle + ' ','') + isnull(cn.Last,'') as AccountName,
st.FullName as State
into ##TempExportData
From Account a (nolock)
join AccountType type (nolock) on a.TypeId = type.Id
join Site s (nolock) on a.Id = s.AccountId
join Contact c (nolock) on s.Id = c.SiteId
join ContactName cn (nolock) on c.NameId = cn.Id
join ContactAddrXref cx (nolock) on c.Id = cx.ContactId
join ContactAddr ca (nolock) on cx.AddrId = ca.Id
join States st (nolock) on ca.StateId = st.Id
Where type.Id = @typeid

set @rowcount = isnull((Select count(*) from ##TempExportData), 0)
print 'Rows selected - ' + (convert(varchar, @rowcount))

if @rowcount > 0
Begin

SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),' + column_name +
case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
when data_type in ('datetime', 'smalldatetime') then ',120'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'

-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
exec (@sql)

IF @@Error = 0
Begin
set @sql = 'bcp "use Phoenix; Select * from ##TempExportData2" queryout "f:\Phoenix\MonthlyReports\AccountList_' + @typedesc + '.csv" -c -t"," -U"web_app" -P"web_app_id" -q -r\n -CRAW'
exec master..xp_cmdshell @sql
IF @@Error <> 0
Begin
print @sql
goto EndLoop
End
End
Else
Begin
print @sql
goto EndLoop
End

drop table [##TempExportData2]
drop table [##TempExportData]

End

Fetch Next from tCur into @typeid, @typedesc

End

EndLoop:
Close tCur
Deallocate tCur
6/11/2008 6:30 PM | Steven Baggett
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

thank youu
9/1/2008 11:49 AM | parke
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Mladen,
FYI, I have modified my "Export to Excel" thread to export data with columns as first row
Refer point 5
10/3/2008 12:40 PM | Madhivanan
Gravatar

# re: EASIEST FIX: Export Query results to Excel with Column names in T-SQL

Here is the fix:



1) Open SQL server

2) Connect to ‘Draco’ (or any other server)

3) In the top toolbar, go to “Tools” and select “Options” from the drop down list

4) A box will pop up, and on the left side you’ll see a white list of options

5) Click on the ‘+’ sign next to “Query Results”

6) Click on the ‘+’ sign next to ‘SQL Server”

7) Select “Results to Grid”

8) Check the box that says “Include column headers when copying or saving the results”

9) Click “ok”

10) You’re done… J
2/25/2009 5:33 PM | Jesus_Saves
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

I have pasted comment 15 posted by Andrew. But I still get the error msg. Pls help

User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
3/11/2009 6:18 AM | victoria_13om
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

This works. i have been looking out for something like this. Thnx
5/12/2009 11:01 AM | Vamsee Krishna
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Hello all,
I did follow the above steps but im also getting the same error even though i changed the sp as per the 15th comment by Andrew.
Please Help...

User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
5/22/2009 12:37 PM | bhanupriya
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

If anyone is interested, I created an SSMS 2005/2008 Addin that allows saving SQL query results as an XLS file, with header and value formats intact.

http://www.SsmsXlsExport.com/

Cheers,
David
10/15/2009 5:08 AM | David Poirier
Gravatar

# re: Export Query results to Excel with Column names in T-SQL


Hi, I executed spexportdata and getting below error:

NULL
Starting copy...
SQLState = 01000, NativeError = 5701
Warning = [Microsoft][SQL Native Client][SQL Server]Changed database context to 'Prot4'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
NULL
BCP copy out failed
NULL

My sql statement is straightforward: select * from &lt;tabname&gt;. After digging around this problem for some time I observed that my source table has datetime columns and collation setting for those column in source table is NULL. The stored proc spexportdata converts all the columns to nvarchar datatype and during this conversion collation setting for this datetime column is being set to something. Could this be the reason and if yes then what is the solution? How can I change the collation setting for the column in tempdb?
12/17/2009 6:33 PM | KA
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

Thanks, this is a fantastic script!
As to all the people who are getting the error "Unable to resolve column level collations", I changed the following line:

-- build full BCP query
select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'

to

-- build full BCP query
select @sql = 'bcp "select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -T -CRAW'


Cheers.
6/11/2010 3:45 AM | Brettski
Gravatar

# re: Export Query results to Excel with Column names in T-SQL

'OSQL -SLOCALHOST
8/13/2010 3:39 PM | 12
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET