0

I have two databases in Azure SQL Server.

I have a table called AUDIT_TAB which should be absolutely identical in each, both in structure and in contents.

CREATE TABLE [DB].[AUDIT_TAB]
(
    ...
    [THE_TEXT] [varchar](1000) NULL,
    [UPDATE_DATE] [datetime2](0) NOT NULL,
    [UPDATE_USER_ID] [numeric](6, 0) NOT NULL
) ON [PRIMARY]

The THE_TEXT column is populated with data which includes carriage returns.

The query

select * 
from db.audit_tab 
where the_text like '%'+char(10)+'%'

returns the same rows for each database.

The following query which converts the the_text field into hexadecimal

select convert(varchar(max), convert(varbinary(max), the_text),1) 
from db.audit_tab

returns exactly the same for each database.

I have a Java application attached to each database. The same Java application reads one table with the carriage returns, and one table without the carriage returns.

Now, in SSMS itself there is an option to copy CR/LF directly from the tables.

enter image description here

Before I discovered this option, then one instance of my table showed the CR/LF and one instance of my table did not show the CR/LF. There seems to be have been some kind of internal discrepancy between the two databases as to whether to display the CR/LF or not. Now that I have started using this option, both databases seem to work identically in SSMS.

Notwithstanding the fact that in SSMS the problem is solved, the Java applications still read the table differently. One reads it with carriage returns, and one without.

I suspect that the same internal discrepancy between the two databases which caused SSMS to initially display the tables differently, is also causing the Java applications to read the tables differently.

Any ideas?

gordon613
  • 2,770
  • 12
  • 52
  • 81
  • Sounds like a setting on your Excel/IDE, where you were processing/retaining line breaks in one, and not in the other. – Thom A Feb 23 '20 at 12:40
  • The Excel is the same Excel. Also I am having exactly the same problem when processing the rows in my web application. – gordon613 Feb 23 '20 at 12:45
  • You're going to need to give us a way to replicate this. Either the *way* you're processing the data is different **or** the data *is* different. – Thom A Feb 23 '20 at 12:49
  • Want do you get if you paste each into Notepad++ with option View->Show Symbol->Show All Characters set. – CDP1802 Feb 23 '20 at 14:48
  • @CDP1802. I updated the question and replaced "Paste to Excel" with "Paste to Notepad++". Thank you – gordon613 Feb 23 '20 at 15:39
  • @Larnu. Thinking about how I could do this – gordon613 Feb 23 '20 at 15:40
  • Also I saw somebody voted to close this question as off-topic to SO. In my opinion the underlying question is indeed a programming problem. However my attempt to depict the problem in the simplest way may have come across as not being a programming problem. I have updated the question accordingly. – gordon613 Feb 23 '20 at 15:42
  • How does the above replicate the problem? You've asked us to copy some text, without line breaks, to prove it doesn't have line breaks. Of course it doesn't... Also, a close vote doesn't mean it's not about programming; there are lots of reasons for close votes and only a few of those are due to it being off topic. – Thom A Feb 23 '20 at 15:53
  • @Larnu. Thank you for your continued feedback. I have again rewritten the question. BTW If you click on `close (1)` at the bottom of the question then it gives you the complete list of reasons for possibly closing the question, and it shows you which reason has been selected... – gordon613 Feb 23 '20 at 16:00
  • The close vote reason was : “ It's seeking debugging help but needs more information. The question should be updated to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem.”. That’s grouped under “off topic” for no apparent reason. – David Browne - Microsoft Feb 23 '20 at 16:06
  • @gordon613 I have over 40K rep, I know how the close dialogue works, and I know that the vote was not for off topic because it's not about programming, as I can navigate said menus. :) (As David has said, it's because it's asking for help with a program that this question doesn't help us replicate, which though is about programming, it's not "on-topic" as without a replication it's impossible for us to solve without guessing; and guesses aren't good answers). – Thom A Feb 23 '20 at 16:06
  • Larnu and David Browne. Sorry! My bad... – gordon613 Feb 23 '20 at 16:09
  • @gordon613, is it only in the Java application that the cr/lf characters are missing? It's unclear to me from you revised question if you also copy/pasted the results from the SSMS grid with the retain cr/lf option on and saw different results, – Dan Guzman Feb 23 '20 at 16:45
  • @DanGuzman - I have revised yet again! Hopefully it is clearer now! – gordon613 Feb 23 '20 at 17:24
  • The SSMS option is purely client-side. If you can verify data in both tables are identical with `CAST(THE_TEXT AS varbinary(MAX))`, then it must be client-side in the Java app too. – Dan Guzman Feb 23 '20 at 17:28

1 Answers1

0

This is embarrassing...

The short answer is that I did not restart the Web App. (The database entries were held in a Java object which is only initialized on restart of Web App)

The longer answer is that the problem originally manifested because when I copied from one database to another using sp_generate_insert then the carriage returns were not copied. When I corrected this using my modified version of sp_generate_insert (which converts to hexadecimal) then the carriage returns were copied. It is just that I forgot to restart the Web App, as mentioned. Furthermore there was a red herring as described in my question, where even in SSMS (unaffected by Web App restarts) carriage returns were apparently shown differently even after the data was copied properly.

Thank you to everyone who commented!

gordon613
  • 2,770
  • 12
  • 52
  • 81