September 25, 2009 at 12:47 pm
Comments posted to this topic are about the item BCP command to export data to excel with column headers
October 1, 2009 at 10:42 am
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
October 4, 2009 at 11:56 pm
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,
October 5, 2009 at 9:50 am
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
October 6, 2009 at 1:28 am
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
October 6, 2009 at 12:21 pm
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
October 6, 2009 at 11:13 pm
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
October 7, 2009 at 10:23 am
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
October 26, 2009 at 6:51 pm
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!
October 27, 2009 at 12:14 am
Hi,
Glad to know that it helped you..
If you could post your script then i'll try to look into it..
Regards
October 27, 2009 at 8:26 am
Thanks for the quick reply... here is my SP.
USE [MES - Reporting]
GO
/****** Object: StoredProcedure [dbo].[MBT_RPT_Export_Excel_TEST] Script Date: 10/26/2009 17:08:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 10/24/09
-- Description:Used to export data to Excel
-- =============================================
ALTER PROCEDURE [dbo].[MBT_RPT_Export_Excel_TEST]
--@filename varchar(100),
--@sql varchar(8000),
--@reportid int,
--@msgsubject varchar(1000),
--@bodytext varchar(1000),
--@reccount int OUT
AS
BEGIN
Declare @filename varchar(100)
Declare @sql varchar(8000)
--**Added**
Declare @sorttext varchar(500)
--**
Declare @reportid int
Declare @msgsubject varchar(1000)
Declare @bodytext varchar(1000)
Declare @reccount int
DECLARE @tempSQL varchar(8000)
DECLARE @columnNames varchar(8000)
DECLARE @columnConvert varchar(8000)
DECLARE @bcpCommand VARCHAR(8000)
DECLARE @distlist NVARCHAR(MAX)
DECLARE @distlist_cc NVARCHAR(MAX)
DECLARE @distlist_bcc NVARCHAR(MAX)
Set @filename = 'G:\SQLData\restor\RPT-DispenseDaily.xls'
Set @sql = 'Select * from [Rpt - Daily Dispense Detail]'
Set @reportid = 1
Set @msgsubject = 'Dispense Detail: ' + convert(varchar,GetDate(),101)
Set @bodytext = 'Attached please find the Dispense Detail Report'
Set @reccount = 0
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
select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
EXEC(@tempSQL)
-- 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 ',100'
--+ 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 ',100'
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
SET @bcpCommand = 'bcp "SELECT * from ##TempExportData2" queryout "'
SET @bcpCommand = @bcpCommand + @filename + '" -c -w -T -S"' + @@Servername + '"'
--Print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
Drop Table ##TempExportData
Drop Table ##TempExportData2
Exec MBT_RPT_GetDistList @reportid, @distlist OUT, @distlist_cc OUT, @distlist_bcc OUT
EXEC msdb.dbo.sp_send_dbmail
@recipients=@distlist,
@copy_recipients=@distlist_cc,
@blind_copy_recipients=@distlist_bcc,
@subject = @msgsubject,
@file_attachments = @filename,
@body = @bodytext ;
END
June 22, 2016 at 1:27 pm
Hi,
I tried the above code to get data in excel from SQL query.. But it says command executed successfully. I don't see a file in my folder. Please hellp
June 22, 2016 at 2:00 pm
Hi,
I tried following the same steps and my code runs fine and it says command executed successfully, however I do not get any file saved in my folder. May I know why?Can you please help me?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply