asked
Error Message - Any ideas?
Can anyone help with the following error I added the following for an XML output.. select top 1 LTRIM(REPLACE(SUBSTRING(mm.MATTERCODE, 1, CHARINDEX(''.'', mm.MATTERCODE)), ''0'', '''')) + LTRIM(REPLACE(SUBSTRING(mm.MATTERCODE, CHARINDEX(''.'', mm.MATTERCODE) + 1, LEN(mm.MATTERCODE)), ''0'', '''')) as ''MATTERCODE'', ' + but get the following error Msg 50000, Level 16, State 1, Procedure ls_bcp_out_from_query, Line 124 An unknown error has occurred. The output file was not created. at this point in the stored procedure: -- The -a flag allows us to request a greater packet size, which will speed up processing. set @cmd = 'bcp "' + @query + '"' + ' queryout ' + @temp_output_path + ' -t^' + @field_terminator Line 124.... + ' -T -c -a32767' + ' -S ' + @@servername
the path and it is ok. The script breaks when I change the select statement for the xml path from: set @query ='select top 1 mm.MATTERCODE, '+ by adding a function as follows: set @query ='select top 1 dbo.lsfunc_cm_trim_zeros (''M'',MATTERCODE) as MATTERCODE, '+ The results of @temp_output_path is \\xxxsql\xxxx\Inputfiles\\2175\33659.xml The results of @cmd is dir/B "\\xxxsql\Proclaim\Inputfiles\\2175" Both are correct when testing.. set @query ='select top 1 mm.MATTERCODE but when i change to set @query ='select top 1 dbo.lsfunc_cm_trim_zeros (''M'',MATTERCODE) as MATTERCODE, '+ Error is Msg 50000, Level 16, State 1, Procedure ls_bcp_out_from_query, Line 123 An unknown error has occurred. The output file was not created.
I have now resolved this. The function (dbo.lsfunc_cm_trim_zeros) required the DB name i.e a fully qualified name. Thank you for all your help
Hope this help declare @query varchar(4000) declare @temp_output_path varchar(100) declare @field_terminator char(2) declare @cmd varchar(8000) set @query = '"select * from DBName.dbo.abcd"' set @temp_output_path = 'C:\abc.bat' set @field_terminator = ',' set @cmd = 'bcp ' + @query + ' queryout ' + @temp_output_path + ' -t ' + @field_terminator + ' -T -c -a32767 -S' + @@servername print @cmd exec master..xp_cmdshell @cmd
This is the full script I am running declare @cmd varchar(8000), @temp_output_path varchar(500), @temp_output_file varchar(255), @final_output_path varchar(500) set @append_existing = isnull(@append_existing, 'N') set @field_terminator = isnull(@field_terminator, ',') if isnull(@file_path, '') = '' begin set @cmd = 'Unable to export data.' + char(10) + char(13) + 'The file location was not specified.' raiserror(@cmd, 16, -1) return -1 end if isnull(@file_name, '') = '' begin set @cmd = 'Unable to export data.' + char(10) + char(13) + 'The output file name was not specified.' raiserror(@cmd, 16, -1) return -1 end if isnull(@query, '') = '' begin set @cmd = 'Unable to export data.' + char(10) + char(13) + 'The SQL query was not specified.' raiserror(@cmd, 16, -1) return -1 end set @final_output_path = @file_path + '\' + @file_name -- Table for storing output of commands where required. create table #CMD_OUTPUT (OUTPUT_TEXT varchar(255) null) -- Check path/file exists. set @cmd = 'dir/B "' + @file_path + '"' insert into #CMD_OUTPUT exec master.dbo.xp_cmdshell @cmd if exists (select * from #CMD_OUTPUT where upper(OUTPUT_TEXT) in ('FILE NOT FOUND', 'THE SYSTEM CANNOT FIND THE PATH SPECIFIED.')) begin set @cmd = 'Unable to export data.' + char(10) + char(13) + 'The following output path does not exist:' + char(10) + char (13) + @file_path raiserror(@cmd, 16, -1) drop table #CMD_OUTPUT return -1 end else if @append_existing = 'Y' and not exists (select * from #CMD_OUTPUT where OUTPUT_TEXT = @file_name) begin set @cmd = 'Unable to export data to file.' + char(10) + char(13) + 'The output file could not be found:' + char(10) + char (13) + @file_path + '\' + @file_name raiserror(@cmd, 16, -1) drop table #CMD_OUTPUT return -1 end if @append_existing = 'Y' begin set @temp_output_file = left(@file_name, len(@file_name) - 4) + '_' + convert(varchar, getdate(), 112) + '_' + replace(convert(varchar, getdate(), 108), ':', '') + '.txt' set @temp_output_path = @file_path + '\' + @temp_output_file end else begin set @temp_output_path = @final_output_path set @temp_output_file = @file_name end -- The -a flag allows us to request a greater packet size, which will speed up processing. set @cmd = 'bcp "' + @query + '"' + ' queryout ' + @temp_output_path + ' -t^' + @field_terminator + ' -T -c -a32767' + ' -S ' + @@servername -- Escape any shell metacharacters set @cmd = (select replace(@cmd, '&', '^&')) set @cmd = (select replace(@cmd, '>', '^>')) set @cmd = (select replace(@cmd, '<', '^<')) exec master.dbo.xp_cmdshell @cmd, no_output -- Wait for a few seconds to ensure the file that's been created/updated -- has been freed up by the O/S. waitfor delay '00:00:03' -- Check the output file exists. set @cmd = 'dir/B "' + @file_path + '"' truncate table #CMD_OUTPUT insert into #CMD_OUTPUT exec master.dbo.xp_cmdshell @cmd -- Check that the file has been created. if not exists (select * from #CMD_OUTPUT where OUTPUT_TEXT = @temp_output_file) begin set @cmd = 'An unknown error has occurred.' + char(10) + char(13) + 'The output file was not created.' raiserror(@cmd, 16, -1) drop table #CMD_OUTPUT return -1 end if @append_existing = 'Y' begin -- Concatenate the original & new files together. set @cmd = 'copy /Y /B ' + @final_output_path + ' + ' + @temp_output_path + ' ' + @final_output_path exec master.dbo.xp_cmdshell @cmd, no_output -- Wait for a few seconds to ensure the file that's been created/updated -- has been freed up by the O/S. waitfor delay '00:00:03' -- Delete the temporary output file. set @cmd = 'del "' + @temp_output_path + '"' exec master.dbo.xp_cmdshell @cmd, no_output end return 0 go
16 People are following this question.
