Error trying to Export to Excel

[Former Member]

I have requirement to create an excel file header and then append table data to that excel file. However, I get an error even trying to create the header excel file. I will appreciate any help identifying the problem. Here is the stored procedure:

CREATE PROCEDURE DBA."proc_test_export" ( @exp_file_loc VARCHAR(200) DEFAULT '',@return_execute int output)
as
begin
declare  @astr_string varchar(8000)
Declare  @exp_file_loc varchar(200)
--  call proc_test_export ('',0)
if @exp_file_loc = ''
    Select @exp_file_loc='c:\\msi_wh\\Web_Listing\\Web_Export_' + dateformat(getdate(),'yyyy-mm-dd hh-nn') + '.csv'+''
set @exp_file_loc=REPLACE(@exp_file_loc,'\\\\','\\\\\\\\')
set @astr_string=''
set @astr_string=@astr_string+' SELECT ''Handle'',''Title'',''Vendor'',''Option1_Name'',''Option1_Value'',''Variant_SKU'',''Variant_Inventory_Qty'',''Variant_Price'';'
set @astr_string=@astr_string+' OUTPUT to '
set @astr_string=@astr_string+' '''+@exp_file_loc+'''  '
set @astr_string=@astr_string+' DELIMITED BY ''\\x7C'' FORMAT ASCII; '

  execute(@astr_string)

  if @@error <> 0
    begin
      rollback transaction
      raiserror 25000 'Error while executing proc_test_export '+@@error
      set @return_execute = -1
    return
    end



commit Transaction
set @return_execute =1 
END;

[VolkerBarth]

Note, you cannot use the DBISQL OUTPUT command within a stored procedure, as is discussed in this forum in several FAQs (say, here) and also in the online help.

You can use the UNLOAD statement but that does not directly support the Excel format. Some hints are available here:

• Exporting stored procedure data to Excel

Or you might use xp_cmdshell to start DBISQL with an OUTPUT TO command from within your procedure.

[Former Member]

Thank you. I changed the code to use the UNLOAD command as given in examples and it is working. Thank you.