0

I'm trying to write a T-SQL script to align two copies of a database with the tablediff utility exposed by SQL Server but I am facing a problem with all the rows of a text field inside a table.

The problem is that all the strings stored on that text field are null-terminated (there actually is a null character at the end of the string if I export them to a text file) and the INSERT and UPDATE queries generated by tablediff are all failing due to the fact that that null terminator truncates the query.

Is there a way to prevent the generated scripts from failing?

UPDATE

Creation query generated by MSSQL for the table I'm trying to align

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TABTEST](
    [Code] [varchar](50) NOT NULL,
    [Source] [text] NULL,
 CONSTRAINT [PrK_TABTEST] PRIMARY KEY CLUSTERED 
(
    [Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Insert query generated by tablediff (keep in mind that the character you don't see at the end of the Source after the NOOP is an ANSI NULL character)

UPDATE [dbo].[TABTEST] SET [Source]=N'NOOP ' WHERE [Code] = N'TestCode'
zeb
  • 1,105
  • 2
  • 9
  • 32
  • Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. – Zohar Peled May 08 '18 at 06:43
  • https://stackoverflow.com/questions/2828333/what-is-the-null-character-literal-in-tsql – Tim Biegeleisen May 08 '18 at 06:48
  • 1
    You can try post-processing the scripts with a custom written util (e.g. a C# console app) and replace the failing `...\0'` with `...' + char(0)` - assuming that you want to preserve the 0-characters. – Peter B May 08 '18 at 06:52
  • @PeterB the fact is that I'm am writing this script to align two databases so that I can create all the update scripts to generate updates for our application and we have to do all inside the SQL script – zeb May 08 '18 at 06:56
  • You could run the fixer app from inside your main script, search `sql server call exe` for more info. – Peter B May 08 '18 at 07:00
  • Oh with `xp_shellcmd`...we could try that if there isn't another solution – zeb May 08 '18 at 07:03

1 Answers1

0

The CHAR(0) can be nasty...

My suggestion is to cut the last character away, LEN() will return the lengt including this 0x0. Try it out:

DECLARE @s VARCHAR(100)='test' + CHAR(0)

SELECT @s + 'extension' AS Result1,
       LEFT(@s,LEN(@s)-1) + 'extension' AS Result2;
Shnugo
  • 66,100
  • 9
  • 53
  • 114