CODEWITHSUNDEEP
sql-serversql-agent-jobtruncatedvarcharmax
VLo88
VLo88

Reputation: 51

T-SQL VARCHAR(MAX) Truncated - Not Display Issue

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.

Upvotes: 1

Views: 365

Answers (0)

Related Questions