January 31, 2018 at 3:16 pm
Sorry I did not read all of the solutions but there is something I noticed on the original post that doesn't seem right:
Given the definition of REPLACE:REPLACE ( string_expression , string_pattern , string_replacement )
Doesn't this line: REPLACE(b.TempTableColumnDef,b.TempTableColumnDef,'') +
replace the entire contents of b.TempTableColumnDef (the string_pattern in the definition) with the null string?
Sorry, you can ignore this if it's totally off.....
February 1, 2018 at 2:11 am
I have found if text is unexpectedly truncated to approximately 4000 or 8000 characters this can be a string casting issue. SQL thinks an intermediate expression is NVARCHAR(4000) or VARCHAR(8000) and silently performs the truncation. I had this happen when concatenating string literals with string variables, the result was limited to 4000 characters. I eventually found one of my variables was type SYSNAME which is an NVARCHAR type. In my case casting the SYSNAME typed variable to varchar(max) fixed the issue.
February 1, 2018 at 6:47 am
Has anyone tried my suggestion of CASTing EVERYTHING to (n)varchar(max)? I have used explicit CASTing of pretty much everything for a long time to avoid any such issues (string and otherwise). If you know something should be and it isn't that or you need to ensure it is that, then simply do it. Use a few brain cells and a few more seconds of typing to remove the issue forever.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 1, 2018 at 9:56 am
TheSQLGuru - Thursday, February 1, 2018 6:47 AMHas anyone tried my suggestion of CASTing EVERYTHING to (n)varchar(max)? I have used explicit CASTing of pretty much everything for a long time to avoid any such issues (string and otherwise). If you know something should be and it isn't that or you need to ensure it is that, then simply do it. Use a few brain cells and a few more seconds of typing to remove the issue forever.
Yes, the OP has already claimed he did this.
Mike Hotek - Wednesday, January 31, 2018 12:15 PMYes, but it does not fix the problem. Everything is explicitly cast to a VARCHAR(MAX) and it STILL truncates based on where I place that single line of code....I've even gone to the length of explicitly casting every column in the CTEs to a VARCHAR(MAX) and then further wrapping every single column OR static string in a CAST to VARCHAR(MAX). [/i]
(my emphasis)
That's why I asked him exactly how he's checking the resulting string for correctness, and what, if any, differences there are between the code he posted here and what he's actually running, since neither the truncation nor the "less than 8000 character" non-truncated string have been reproduced on any systems (the non-truncated string on all of my systems and in Lynn's test is close to 20000 characters, and there would be no truncation if everything were explicitly cast).
William Rayer - Thursday, February 1, 2018 2:11 AMI have found if text is unexpectedly truncated to approximately 4000 or 8000 characters this can be a string casting issue. SQL thinks an intermediate expression is NVARCHAR(4000) or VARCHAR(8000) and silently performs the truncation. I had this happen when concatenating string literals with string variables, the result was limited to 4000 characters. I eventually found one of my variables was type SYSNAME which is an NVARCHAR type. In my case casting the SYSNAME typed variable to varchar(max) fixed the issue.
Indeed, this is what I explained in my earlier post in this thread, https://www.sqlservercentral.com/Forums/FindPost1920983.aspx 🙂
timwell - Wednesday, January 31, 2018 3:16 PMSorry I did not read all of the solutions but there is something I noticed on the original post that doesn't seem right:
Given the definition of REPLACE:REPLACE ( string_expression , string_pattern , string_replacement )
Doesn't this line:REPLACE(b.TempTableColumnDef,b.TempTableColumnDef,'') +replace the entire contents of b.TempTableColumnDef (the string_pattern in the definition) with the null string?
Sorry, you can ignore this if it's totally off.....
That's a fair thing to notice, but the OP is aware of that 🙂
Mike Hotek - Tuesday, January 30, 2018 11:06 AM...It also does not matter, as I've done, if you literally wipe out the contents of b.TempTableColumnDef by turning it into an empty string.[/i]
(my emphasis)
Cheers!
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply