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;

asked 20 Jun, 18:35

musicsw's gravatar image

musicsw
392
accept rate: 0%

edited 21 Jun, 11:29

Volker%20Barth's gravatar image

Volker Barth
32.1k327470688


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:

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

permanent link

answered 21 Jun, 11:35

Volker%20Barth's gravatar image

Volker Barth
32.1k327470688
accept rate: 32%

edited 21 Jun, 11:40

1

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

(27 Jun, 15:12) musicsw
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×86
×27

question asked: 20 Jun, 18:35

question was seen: 79 times

last updated: 27 Jun, 15:12