Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

BCP command to export data to excel with column headers Expand / Collapse
Author
Message
Posted Friday, September 25, 2009 12:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
Comments posted to this topic are about the item BCP command to export data to excel with column headers
Post #794023
Posted Thursday, October 01, 2009 10:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:20 AM
Points: 4, Visits: 20
Hello,

I am very new to SQL 2008 Express, and am not that comfortable with stored procedures yet. However, I love your post about exporting to Excel. It will be extremely helpful. I just need some help implementing it.

I copied the code and changed the lines you indicated: 1,8,9,10,57,58. But actually, line 10 was blank in the copied code, so I added it even though I don't see where the variable is used in the script below. In line 57, I entered the revised path, but couldn't that line be changed to use the @fullFileName variable? Or should @fullFileName be the path without the actual file name?

The first time I executed the procedure, I received the following error, so I found instructions on Microsoft's website as to how to enable the use of xp_cmdshell, which appeared to work.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


The second time I ran the procedure, the output was as follows:

Warning: -w overrides -c.
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '##TempExportData2'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL


Do you have any idea what I am doing wrong?

Thank you so much for any help you're able to give.
Diana
Post #796478
Posted Sunday, October 04, 2009 11:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
Hi diana..

You can use the variable instead of the path in the query..and thats actually intended for dynamic path only..

For error..please debug and print the dynamic SQL getting prepared..and rectify the error..

or if that doesnt solve the problem... send your full query..

Regards,
Post #797665
Posted Monday, October 05, 2009 9:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:20 AM
Points: 4, Visits: 20
Hi Samardeep,

I tried using the debugger, but it only allows me to step into the code once before it exists the debugger. There again, maybe I'm doing something wrong with the debugger too. I tried to figure it out using the online books to no avail. It really is frustrating, so I appreciate your help.

Thank you,
Diana


Here is my code:

USE [MSG]
GO
/****** Object: StoredProcedure [dbo].[spExportData_n] Script Date: 10/05/2009 08:39:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery6.sql|7|0|C:\Documents and Settings\dianal\Local Settings\Temp\~vs1ED.sql
ALTER proc [dbo].[spExportData_n]
(
@dbName varchar(100) = 'MSG',
@sql varchar(8000) = 'select requestID,submittedDate,neededDate,projectID from dt_Requests',
@fullFileName varchar(100) = 'C:\Inetpub\wwwroot\Admin\test.xls'
)
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
DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout'
SET @bcpCommand = @bcpCommand + ' C:\Inetpub\wwwroot\Admin\test.xls -c -w -T -U sa -P sa","-CRAW'
EXEC MSG..xp_cmdshell @bcpCommand
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
set @columnNames =' '
set @columnConvert =' '
set @tempSQL =' '
select 1 as ReturnValue

Post #798017
Posted Tuesday, October 06, 2009 1:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
Hi Diana,

I cheked your script on my local environment and it worked absolutely fine.

Now few things i have in my mind.

Check the userid and password for your DB in the SQL query for BCP.

another thing are you running this script on remote server or your local server?

please check these and then let me know..

Regards
Samar
Post #798344
Posted Tuesday, October 06, 2009 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:20 AM
Points: 4, Visits: 20
Hi Samar,

I was running the query on my local server and getting the error, so I tried it on my remote server and it worked perfectly.

The remote production server database owner is "sa". I log into the remote server with the SA credentials.

My local test server database owner is "abdc\dianal", and I use Windows Authentication to log in as abdc\dianal.

The owner of the stored procedure on both servers is "dbo". Since the query worked on the remote server, should I change ownership of all the databases on my local server to "sa"? I'm not sure how the ownership was setup as "abdc\dianal" in the first place.

Also, there is another question I have. Every time I execute the procedure on either server, it opens a dialog box that asks for the variable information again, so it must not be reading it from the query. The dialog box lists the parameter name, data type, output parameter (for each line it says "No"), pass null value (checkbox is always unchecked), and value. I enter the values into the dialog box and click OK, then the query continues. Is that supposed to happen?

Diana
Post #798711
Posted Tuesday, October 06, 2009 11:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
Hi Diana..

Glad to know that it worked fine on your remote server..since you are running with windows authentication on your local server then try to remove the credentials from the query..and then run it..

As far as prompting the parameter is concerned..it should not ask it everytime..try to see your local settings..

Regards..

Samar
Post #798925
Posted Wednesday, October 07, 2009 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:20 AM
Points: 4, Visits: 20
Hi Samar,

How do you remove the credentials from a query? I searched for an answer on the web, but found nothing. In the stored procedure properties, I added the abdc\dianal user and gave it full permissions including "take ownership", but it still failed.

I will look into my settings to see why the prompt comes up, but at least the query works on the production server if I enter the paramaters into the prompt.

Thanks for your help,
Diana

Post #799349
Posted Monday, October 26, 2009 6:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 5:50 PM
Points: 6, Visits: 50
Thanks so much for this code. It has really helped me a great deal.

I am having one problem. When I pull a small amount of data, 5 to 10 rows, the headers are appearing at the top of the spreadsheet. When I pull about 400 rows the headers appear about 50 lines down in the spreadsheet.

Do you have any ideas how this could happen?

Thanks!
Post #809046
Posted Tuesday, October 27, 2009 12:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
Hi,

Glad to know that it helped you..

If you could post your script then i'll try to look into it..

Regards
Post #809103
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse