0

I have a script that does a find and replace on other scripts. I get the text of the script using "selece definition from sys.sql_modules..." and store it in a varchar(max) variable. Then I split the text into 3 other variables because "Print" only works on 8000 characters even if the variable is varchar(max). The procedure that I'm doing teh find/replace has almost 22,000 characters, but it seems the varchar(max) only holds 17,245 characters. I've tried 

Set @SQL = substring(select definition..., 17246, 8000) but that returns null.

I've also tried

declare @t table(line varchar(8000))
insert @t exec sp_helptext 'sputilCopyEntity'
select * From @T

but it has the same size limitation. The last row is the same as the last of the text when I use the varchar(max) variable.

How can I get the rest of the script?

jarlh
  • 42,561
  • 8
  • 45
  • 63
FirstByte
  • 563
  • 1
  • 6
  • 20
  • As far as i know, you must ensure that all varchar are set to max. Take a look to this question http://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated – ADreNaLiNe-DJ Jan 26 '16 at 15:18
  • 1
    I'm not seeing the same limitation that you are seeing. Can you post your original code that is filling the `VARCHAR(MAX)` variable? – Tom H Jan 26 '16 at 15:20
  • You might consider coming at this from another angle. Maybe using powershell to create an array of objects and then retrieve the definition in similar fashion. Perform the replace and run the new code back into SQL Server. You won't be dealing with the limitations you have in engine and will have the full .NET library at your command. Just a thought. – Steve Mangiameli Jan 26 '16 at 15:37
  • Not sure why it would matter, but what if you put the `substring` inside the `select` instead of vice-versa, like your first code example? – Tab Alleman Jan 26 '16 at 15:50

0 Answers0