Please critique for WTFery + ? about a bcp error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please critique for WTFery + ? about a bcp error

Author

Topic

LaurieCox

158 Posts

Posted - 2006-12-14 : 09:57:57

The code works, but I don't want to do something the wrong way. I would appreciate any comments and criticisms on my code.

Description:

Output data in Diagnosis table formatted for XML.
Notes:
1. XML file name is formatted mmddhhmmDX.XML*
2. No more than 1000 records should be in each output file.
3. Because the smallest unit of time in the XML file name is minutes, I use the system date for the first file output and then increment by 1 minute for each successive file, so that the files names are unique.

DDL and sample data:


Create table ##Diagnosis
(DxIdentity             int Identity,
 DxEternalID		int,
 MemberExternalID	int,
 ICDDSM		  	char (1),
 PrimaryDx		char (1),
 Code			varchar (10),
 DxKey			varchar(10),
 ProvisionalDx		char (1),
 ProvSSN		varchar (9),
 Axis			char (1),
 StartDate		char (10),
 EndDate                char (10),
 XMLFileName            char(10))  

Insert into ##Diagnosis
(DxEternalID, MemberExternalID, ICDDSM, PrimaryDx, Code, DxKey, ProvisionalDx, ProvSSN, Axis, StartDate, EndDate)
select 42850, 31, '1', '0', '300.02', '', '0', '', '', '10/26/2006', '' union
select 41083, 68, '1', '1', '298.9', '', '0', '', '', '05/10/2006', '' union
select 39791, 72, '1', '0', '300.3', '', '0', '', '', '02/28/2005', '' union
select 37525, 121, '1', '0', 'V71.09', '', '0', '', '', '09/13/2005', '' union
select 42480, 147, '1', '0', '312.30', '', '0', '', '', '08/23/2006', '' union
select 661, 155, '0', '1', '307.42', '', '0', '', '1', '03/27/1997', '' union
select 41366, 205, '1', '0', '300.02', '', '0', '', '', '04/17/2006', '' union
select 34801, 207, '1', '0', '312.30', '', '0', '', '', '01/24/2005', '' union
select 41736, 220, '1', '1', '296.31', '', '0', '', '', '07/07/2006', '' union
select 40179, 241, '1', '1', '295.30', '', '0', '', '', '08/10/2005', '' union
select 35160, 245, '1', '0', '312.30', '', '0', '', '', '04/13/2004', '' union
select 37195, 275, '1', '1', '295.70', '', '0', '', '', '08/03/2005', '' union
select 40847, 286, '1', '0', '300.00', '', '0', '', '', '04/12/2006', '' union
select 38426, 316, '1', '0', '299.00', '', '0', '', '', '04/01/2005', '' union
select 41400, 318, '1', '0', '296.90', '', '0', '', '', '02/03/2006', '' union
select 32822, 346, '1', '0', '295.90', '', '0', '', '', '07/14/2004', '' union
select 41692, 359, '1', '1', '313.82', '', '0', '', '', '06/26/2006', '' union
select 42722, 414, '1', '0', 'V71.09', '', '0', '', '', '09/04/2006', '' union
select 38870, 421, '1', '0', '309.81', '', '0', '', '', '08/26/2005', '' union
select 33091, 429, '1', '1', '298.9', '', '0', '', '', '08/18/2004', ''

Code to critique


DECLARE @out_dte datetime
DECLARE @XMLFileName char(10)
DECLARE @xmlSQL varchar(300)
DECLARE @bcpString varchar(8000)
DECLARE @CopyCommand varchar(100)

/**************************************************************************************************
Output XML files
**************************************************************************************************/

set @out_dte = getdate()

while (select count(*) from ##Diagnosis where XMLFileName is null) > 0
begin
	set @XMLFileName = replace(replace(replace(substring(convert(varchar, @out_dte, 120), 6, 11), ' ', ''), '-', ''), ':', '') + 'DX'

	update ##Diagnosis set XMLFileName = @XMLFileName  
	          from (select top 1000 *
			  from ##Diagnosis
	                 where XMLFileName is null
	                 order by DxIdentity) as d1
	         where ##Diagnosis.DxIdentity = d1.DxIdentity

	set @xmlSQL = 'Select DxEternalID,  MemberExternalID,  ' +
		      'ICDDSM,  PrimaryDx,  Code,  DxKey,  ProvisionalDx, ' +  
                      'ProvSSN,  Axis,  StartDate,  EndDate ' +
                      'from ##Diagnosis as Diagnosis ' + 
                      'where XMLFileName = ''' + @XMLFileName + ''' for xml auto'


	set @bcpString = 'bcp "' + @xmlSQL + '" queryout e:' + @XMLFileName +  '.TXT -c -r  -Sxxxxx -Uxx -Pxxxxxxx '
	exec master.dbo.xp_cmdshell @bcpString
	set @CopyCommand = 'copy/b e:DX-XML-Header.txt+e:'+  @XMLFileName + '.TXT+e:DX-XML-Footer.txt ' + 'e:' + @XMLFileName + '.xml'
	exec master.dbo.xp_cmdshell @CopyCommand

	set @out_dte = DATEADD(mi,1,@out_dte)
end

bcp error message:

When I run this I get multiple errors like this:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Server data (3046 bytes) exceeds host-file field length (0 bytes) for field (1). Use prefix length, termination string, or a larger host-file field size. Truncation cannot occur for BCP out

… but the output file looks fine and seems to have all the data in it. Is this something I should worry about?

Thanks,

Laurie

*Thank you Peso for your help in formatting the file name

Edited to correct ddl by adding EndDate to create table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources