print truncating output line from stored procedure

• mikeyd23

  SSC Rookie

  Points: 31

  More actions

  January 20, 2003 at 5:36 am

  #159407

  Hi all,

  I'm running several sp's that print the output of a query on separate lines then output to a text file.

  The problem is that print seems to truncate the each line to 129 characters so if a value has more that 129 charactes then the quotation mark doesn't get added at the end, so osql returns a error saying i have an unclosed quotation mark!

  please see script below for more info:

  declare @id integer, @filename varchar(100), @path varchar(100),@query varchar(200)

  declare @id_str varchar(5), @headline varchar(250), @body varchar(8000)

  set @path = '\\mycomp\C\'

  set @id = 0 --change this accordingly, remembering varchar's can start with a number

  while @id is not null

  begin

  set @id = (select top 1 id from [intranet].[dbo].[tbl_homepage_news_DEV] where id > @id and delete_record = 'N' and internet = 'Y' order by id)

  if @id is null break

  --------------------------------------------

  set @id_str = cast(@id as varchar(8000))

  set @filename = @path + @id_str + '.txt'

  select @headline = headline, @body = body from [intranet].[dbo].[tbl_homepage_news_DEV] where id = @id

  set @query = "exec intranet.dbo.sp_fileprint '" + @id_str + "','" + @headline + "','" + @body + "'"

  --exec sp_ExportData_To_Textfile @query, @filename

  print @query

  end

  any ideas?

  Regards

  Mike

• Prakash Heda

  SSCertifiable

  Points: 5539

  More actions

  January 20, 2003 at 7:16 am

  #446756

  Script you provided is not complete...

  Here is the method to pass something to textfile....

  GO

  IF OBJECT_ID ('DT_ARC_PASSTOFILE') IS NOT NULL DROP PROC DT_ARC_PASSTOFILE

  GO

  CREATE PROC DT_ARC_PASSTOFILE (@PASSTOFILE VARCHAR(4000) )

  AS

  SET @PASSTOFILE = 'ECHO ' + CASE WHEN @PASSTOFILE = '' THEN '--ABC' ELSE @PASSTOFILE END + ' >> C:\ARCHIVE_SCRIPT.TXT'

  EXEC MASTER..XP_CMDSHELL @PASSTOFILE , NO_OUTPUT

  GO

  EXEC DT_ARC_PASSTOFILE 'ANYTHING TILL 4000 CHARACTERS GET PRINTED INTO TEST FILE LOCATED ON C ROOT WITHOUT LINE TRUNCATED....'

  CHEERS,

  Prakash

  Prakash Heda
  Lead DBA Team - www.sqlfeatures.com
  Video sessions on Performance Tuning and SQL 2012 HA