1

Anyone know of a way to execute a dynamic SQL which is around 300000 characters ?

background - a nightly process is run to clean up data and triggers are created based on the rules that are defined in the application during the day. These triggers update multiple tables and the only way to create them at this point is through dynamic sql. Any ideas?

WhatsInAName
  • 9
  • 1
  • 5
  • 2
    this sounds like a maintenance nightmare. couldn't you create several, smaller stored procedures to be run in a scheduled plan? – Vland Aug 26 '14 at 22:11
  • 1
    Leaving aside how ugly this sounds, sp_executesql can handle up to a 2 GB string, which leaves plenty of headroom, so this can get even uglier! – Andrew Aug 26 '14 at 22:13
  • this doesnt happen too often, its more like when the policies change and triggers seemed like the ideal solution to implement something similar to cascade updates. Even if I switch the object from trigger to stored proc, I would end up running into the same dynamic sql limitation problem. – WhatsInAName Aug 26 '14 at 22:15
  • What problem are you having? As Andrew states this is perfectly possible. `nvarchar(max)` allows 1 billion characters. – Martin Smith Aug 26 '14 at 22:20
  • now that I read about it. I am not sure why its cutting me on the first 4000 chars. – WhatsInAName Aug 26 '14 at 22:23
  • Make sure that the strings you are concatenating always have a `max` datatype string participating as one of the pair. See http://stackoverflow.com/a/12639972/73226 – Martin Smith Aug 26 '14 at 22:26
  • i dont think i missed any of those but let me dive in an check. Thanks for the all instant replies. – WhatsInAName Aug 26 '14 at 22:36
  • Does anyone know if express edition has a limitation on nvarchar to 4000? – WhatsInAName Aug 26 '14 at 23:17
  • @WhatsInAName, you probably run up against a limitation in SSMS. In the Options, you can configure how much characters from long fields / variables are actually returned. Also, the `datalength()` function will show the actual string size. – Roger Wolf Aug 27 '14 at 04:06
  • @Ennor - yes that is what was going on. The sql string was built right, I just could not see it because of SSMS setting. Again, thanks to everyone for jumping in to help. – WhatsInAName Aug 27 '14 at 06:40
  • @vland - I did end up running into recursive trigger issues, so had to move everything into stored procs. Moving on to the last part, I have to figure out a way to call these stored procs from the app . – WhatsInAName Aug 27 '14 at 06:44
  • 1
    it's tripping at 4000 characters because of the types you are using for your dynamic sql. [This SO answer goes into the details](http://stackoverflow.com/a/12639972) Basically your dynamic sql is getting converted to nvarchar(4000), likely from a concatenation between `varchar(n)` and `nvarchar(n|max)` – SumGuy Sep 24 '14 at 20:20

0 Answers0