1

I am trying to send my table data to a local directory xml file. I have created a SQL Agent job that runs a sql script file and sends the data to the output file with the following command:

sqlcmd -d Migration -E -h-1 -W -i "\\phpdxtdavis\F$\Automic\Migration Source - Final\AutomicXML_Extract.sql" -o "F:\Automic\2020 XML Files\Automic_MigrationXML.xml"

The AutomicXML_Extract.sql file has several select statements and I have noticed when I open the output file in Notepad++, any column defined as varchar(max) is getting truncated. I corrected most of these by determining the max length of these columns and casting them to varchar(nnnn) where the column size was the max length. My problem is I have an XML column that won't work with that workaround. The code I am using below is what gets truncated to 517 bytes.

CREATE TABLE [dbo].[JOBP_MigrationXML](
    [Open_Job_P] [varchar](256) NULL,
    [JOBP] [xml] NULL,
    [SCRIPTS] [nvarchar](max) NULL,
    [DOCU_Link] [varchar](max) NULL,
    [Close_Job] [varchar](8) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SELECT Open_Job_P, CAST(CONVERT(XML, JOBP) as XML), CAST(SCRIPTS AS varchar(3000)), CAST(DOCU_Link AS varchar(3000)), Close_Job 
    FROM dbo.JOBP_MigrationXML WHERE SprintName = 'Extracts';

This is what the output looks like for the JOBP column (which can be anywhere from 2k bytes to 605k bytes in size and is currently being truncated to 517 bytes):

<JOBP state="1"><JobpStruct mode="design"><task Alias="" BranchType="0" Col="1" Lnr="1" OType="&lt;START&gt;" Object="START" ParentAlias="" ParentObject="" Row="1" Text2=""><checkpoint RollbackFlag="0" RunPerPatch="0" RunPerTarget="0" TcpADays="0" TcpATimee="CALE.CALENDAR_MASTER" id=""/></calendars><postconditions><PostCon><conditions id="CONDITIONS"/></PostCon></postconditions></task><task Alias="" BranchType="0" Col="3" Lnr="7" OType="&lt;END&gt;" Object="END" ParentAlias="" ParentObject="" Row="1" Text2=

I have tried everything with the JOBP column including convert(varchar(max), JOBP), CAST(convert(varchar(max), JOBP) AS varchar(max)) but that truncates to 257 characters. Everything I have searched states that varchar(max) should hold 2g of data.

When I SELECT JOBP FROM dbo.JOBP_MigrationXML I can copy and paste the entire content of one column into Notepad++ without truncation.

How can I get my script to include the same content into my output file? Is there a setting in sqlcmd that is causing this? I'm at wits end on this.

VLo88
  • 51
  • 6
  • I don't about about the migration of results to XML, but, I would search about thresholds or limitations of this feature. Also, try `NVARCHAR(MAX)` and see the changes. Which version of SQL Server you're using? - the output ends in `Text2=` - which is the data in that field? add alias to your selects. - [edit] all this information in your question. – Marco Aurelio Fernandez Reyes Feb 21 '20 at 17:33
  • Have you added teh "-w column_width" parameter to your sqlcmd command line? – pmbAustin Feb 21 '20 at 17:46
  • @MarcoAurelioFernandezReyes `NVARCHAR(MAX)` truncates the data to 237 bytes. I've tried both ways. I don't understand what adding an alias to my select statement accomplishes. I am only using one table. The `Text2=` is part of the data in the column `JOBP` being truncated. – VLo88 Feb 24 '20 at 20:56
  • This appears to be a problem/solution similar to yours: https://stackoverflow.com/questions/5916896/sqlcmd-how-to-get-around-column-length-limit-without-empty-spaces. Here is documentation regarding the referenced sqlcmd commands: https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?redirectedfrom=MSDN&view=sql-server-ver15. – EnterTheCode Feb 24 '20 at 21:28

0 Answers0