BCP command to export data to excel with column headers

  • SAMARDEEP

    Right there with Babe

    Points: 727

    Comments posted to this topic are about the item BCP command to export data to excel with column headers

  • dianal

    Old Hand

    Points: 304

    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

  • SAMARDEEP

    Right there with Babe

    Points: 727

    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,

  • dianal

    Old Hand

    Points: 304

    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

  • SAMARDEEP

    Right there with Babe

    Points: 727

    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

  • dianal

    Old Hand

    Points: 304

    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

  • SAMARDEEP

    Right there with Babe

    Points: 727

    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

  • dianal

    Old Hand

    Points: 304

    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

  • roblowery

    SSC Enthusiast

    Points: 150

    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!

  • SAMARDEEP

    Right there with Babe

    Points: 727

    Hi,

    Glad to know that it helped you..

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

    Regards

  • roblowery

    SSC Enthusiast

    Points: 150

    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

  • SQL beginner

    SSC Enthusiast

    Points: 148

    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

  • SQL beginner

    SSC Enthusiast

    Points: 148

    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 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply