title: Running sql script in sqlcmd is cutting part of my html code in file result
body: <p>I create a Script sql-server that returns two html code from two different  views that I created. The problem is that, when I'm running the .sql file (scrip) in sqlcmd, the html result in the file output is incomplete (obs. If I execute in management studio, the result in html is complete).</p>
  
  <p>I have no idea how to proceed. I think in Oracle <code>set long</code> is used, but I don't know in Microsoft SQL Server.</p>
  
  <pre><code>--CODE IN SQLCMD
      --SQLCMD -S LOCALHOST\SQLEXPRESS -i C:\checklist_sql\check_sql\checklist.sql -o C:\checklist_sql\lib\resultado.html
  
  
  SET NOCOUNT ON;
      ---------ROCEDUR TO HTML----
      GO
      create PROCEDURE [dbo].[SqlTableToHtml] (
      @TABLENAME  NVARCHAR(500),
    @OUTPUT   NVARCHAR(MAX) OUTPUT,
    @TBL_STYLE NVARCHAR(1024) = '',
     @TD_STYLE NVARCHAR(1024) = '',
  @HDR_STYLE NVARCHAR(1024) = '')
  AS
  
      -- Description 
      --        Stored Procedure to take an arbitraty temporary table and return 
     --        the equivalent HTML string .
  
  
      -- @exec_str stores the dynamic SQL Query
     -- @ParmDefinition stores the parameter definition for the dynamic SQL
         DECLARE @exec_str  NVARCHAR(MAX)
         DECLARE @ParmDefinition NVARCHAR(500)
  
  
  --We need to use Dynamic SQL at this point so we can expand the input table name parameter
  SET @exec_str= N'
  DECLARE @exec_str  NVARCHAR(MAX)
  DECLARE @ParmDefinition NVARCHAR(500)
  
  --Make a copy of the original table adding an indexing columnWe need to add an index column to the table to facilitate sorting so we can maintain the
  --original table order as we iterate through adding HTML tags to the table fields.
  --New column called CustColHTML_ID (unlikely to be used by someone else!)
  --
  
  select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM ' + @TABLENAME + ' 
  
  --Now alter the table to add the auto-incrementing index. This will facilitate row finding
  DECLARE @COUNTER INT
  SET @COUNTER=0
  UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1 
  
  -- @HTMLROWS will store all the rows in HTML format
  -- @ROW will store each HTML row as fields on each row are iterated through 
  -- using dymamic SQL and a cursor
  -- @FIELDS will store the header row for the HTML Table
  
  DECLARE @HTMLROWS NVARCHAR(MAX) DECLARE @FIELDS NVARCHAR(MAX) 
  SET @HTMLROWS='''' DECLARE @ROW NVARCHAR(MAX) 
  
  -- Create the first HTML row for the table (the table header). Ignore our indexing column!
  SET @FIELDS=''&lt;tr&gt;''
  SELECT @FIELDS=COALESCE(@FIELDS, '' '','''')+''&lt;th ' + @HDR_STYLE + '&gt;'' + name + ''&lt;/th&gt;''
  FROM tempdb.sys.Columns
  WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
  AND name not like ''CustColHTML_ID''
  SET @FIELDS=@FIELDS + ''&lt;/tr&gt;''
  
  -- @ColumnName stores the column name as found by the table cursor
  -- @maxrows is a count of the rows in the table, and @rownum is for marking the
  -- ''current'' row whilst processing
  
  DECLARE @ColumnName  NVARCHAR(500)
  DECLARE @maxrows INT
  DECLARE @rownum INT
  
  --Find row count of our temporary table
  SELECT @maxrows=count(*) FROM  #CustomTable2HTML
  
  
  --Create a cursor which will look through all the column names specified in the temporary table
  --but exclude the index column we added (CustColHTML_ID)
  DECLARE col CURSOR FOR
  SELECT name FROM tempdb.sys.Columns
  WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
  AND name not like ''CustColHTML_ID''
  ORDER BY column_id ASC
  
  --For each row, generate dymanic SQL which requests the each column name in turn by 
  --iterating through a cursor
  SET @rowNum=0
  SET @ParmDefinition=N''@ROWOUT NVARCHAR(MAX) OUTPUT,@rowNum_IN INT''
  
  While @rowNum &lt; @maxrows
  BEGIN
    SET @HTMLROWS=@HTMLROWS + ''&lt;tr&gt;''
  
    SET @rowNum=@rowNum +1
    OPEN col
    FETCH NEXT FROM col INTO @ColumnName
    WHILE @@FETCH_STATUS=0
      BEGIN
        --Get nth row from table
        SET @exec_str=''SELECT @ROWOUT=(select COALESCE(['' + @ColumnName + ''], '''''''') AS ['' + @ColumnName + ''] from #CustomTable2HTML where CustColHTML_ID=@rowNum_IN)''
  
      EXEC  sp_executesql 
        @exec_str,
        @ParmDefinition,
        @ROWOUT=@ROW OUTPUT,
              @rowNum_IN=@rownum
  
        SET @HTMLROWS =@HTMLROWS +  ''&lt;td ' + @TD_STYLE + '&gt;'' + @ROW + ''&lt;/td&gt;''
        FETCH NEXT FROM col INTO @ColumnName
      END
    CLOSE col
    SET @HTMLROWS=@HTMLROWS + ''&lt;/tr&gt;''
  END
  
  SET @OUTPUT=''''
  IF @maxrows&gt;0
  SET @OUTPUT= ''&lt;table ' + @TBL_STYLE + '&gt;'' + @FIELDS + @HTMLROWS + ''&lt;/table&gt;''
  
  DEALLOCATE col
  '
  
  DECLARE @ParamDefinition nvarchar(max)
  SET @ParamDefinition=N'@OUTPUT NVARCHAR(MAX) OUTPUT'
  
  --Execute Dynamic SQL. HTML table is stored in @OUTPUT which is passed back up (as it's
  --a parameter to this SP)
  EXEC sp_executesql @exec_str,
  @ParamDefinition,
  @OUTPUT=@OUTPUT OUTPUT
  
  RETURN 1;
  
  
  ---- VIEW  VERSION SQL--
  GO
  create view versao_sql as 
  SELECT Convert(nvarchar(30),SERVERPROPERTY('MachineName')) AS [NOME DA MAQUINA] ,
   Convert(nvarchar(30),SERVERPROPERTY('InstanceName')) AS [INSTANCIA] ,
   Convert(nvarchar(30),SERVERPROPERTY('ProductVersion')) AS [PRODUCT VERSION],
   Convert(nvarchar(30),SERVERPROPERTY('ProductLevel')) AS [PRODUCT LEVEL] ,
   Convert(nvarchar(30),SERVERPROPERTY('Edition')) AS [EDIÇÃO] ,
   ( CASE SERVERPROPERTY('EngineEdition')
   WHEN 1 THEN 'Personal or Desktop'
   WHEN 2 THEN 'Standard'
   WHEN 3 THEN 'Enterprise'
   else ''
   END ) AS [ENGINE TYPE] ,
   Convert(nvarchar(30),SERVERPROPERTY('LicenseType')) AS [TIPO DE LICENÇA] ,
   convert(nvarchar(30), SERVERPROPERTY('NumLicenses')) AS [LICENÇAS] ,
   ( CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
   WHEN 0 THEN 'Mista'
   WHEN 1 THEN 'Integrada'
   else ''
   END ) AS [SOMENTE SEGURANÇA INTEGRADA] ,
   convert(nvarchar(30),SERVERPROPERTY('Collation')) AS [COLLATION] ,
   ( CASE SERVERPROPERTY('IsClustered')
   WHEN 0 THEN 'Nao'
   WHEN 1 THEN 'Sim'
   END ) AS [CLUSTER];
  
  
  ------INFO INSTANCE--
  GO
  create view info_instancia as
  select 'VERSAO DO CHECK LIST' as DESCRICAO,'2.2.6' as VALOR
  union all
  select 'NOME DO SERVIDOR' as DESCRICAO, CAST(@@SERVERNAME as nvarchar) as VALOR
  union all
  select 'IP DO SERVIDOR' as DESCRICAO, CAST(CONNECTIONPROPERTY('local_net_address') as nvarchar) as VALOR
  union all
  select 'NOME DA INSTANCIA' as DESCRICAO, CAST(SERVERPROPERTY('InstanceName') as nvarchar) as VALOR
  union all
  select 'NLS_CHARACTERSET' as DESCRICAO, CAST(SERVERPROPERTY('Collation') as nvarchar) as VALOR
  union all
  select 'VERSAO SQL SERVER' as DESCRICAO, CAST(@@VERSION as nvarchar) as VALOR
  union all
  select 'DATA DE HOJE' as DESCRICAO, CAST(GETDATE() as nvarchar) as VALOR;
  
  -- RUNNING PROCEDURE FOR EACH VIEW
  GO
  --SQL VERSION
  declare @html nvarchar(max)
  exec SqlTableToHtml 'versao_sql', @html output, 'style="table:"border=1 width=65%"', '', 'style="scope=col"'
  select @html;
  
  GO
  --INSTANCE SQL
  declare @html nvarchar(max)
  exec SqlTableToHtml 'info_instancia', @html output, 'style="table:"border=1 width=65%"', '', 'style="scope=col"'
  select @html;
  
  ----DROPINGS----
  GO
  drop view versao_sql;
  GO
  drop view info_instancia;
  GO
  DROP PROCEDURE SqlTableToHtml;    
  </code></pre>