September 27, 2012 at 6:00 pm
We have a website that allows users to create their own business logic. They select expressions and fields and the backend compiles it into a sproc which evaluates it against their client base. This is done by this module dynamically creating stored procedures with dynamic SQL. So again, we have dynamic SQL that dynamically creates stored procedures. This surprisingly has worked OK up until recently, when we've apparently reached a limit in the number of individual rules that can be implemented.
What this means in database terms is that the dynamically created script for creating the stored procedure has grown too large....somewhere. And, this is where I'm puzzled. We started getting an error with one particular client - "Incorrect syntax near ')'." Initially, I thought it was a problem with the nvarchar(max) (@Script) variable's length being reduced from an assignment from a shorter string variable, but when I inspect the length of @Script, it seems ok...around 73k. Additionally, I can SUBSTRING(@Script, 65000, LEN(@Script) - 65000) and see all the way to the end of the script.
I've thought about using SUBSTRING to break it up, but I'm running into issues with that. In particular, taking it in chunks of 4000 or so seems to leave gaps along the way. Anyone have any suggestions here?
September 27, 2012 at 9:29 pm
How are you executing the SQL?
1. EXEC
2. sp_executesql
I've used #2 with VARCHAR(MAX) strings (that contained > 8000 chars) before with no problem.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2012 at 9:31 pm
I have tried both.
September 27, 2012 at 9:59 pm
The next thing I would try is to SELECT the problematic SQL out to the Results pane in SSMS, copy/paste it into an Execute window, run it there and see what happens.
Note that I don't think you'll be able to PRINT and then copy/paste from the Messages pane. I ran into problems where PRINT was truncating long results.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2012 at 10:13 pm
Well, that's the problem. Like I mentioned, the users are able to build their own expressions through our UI and keep any number of them that they want. I know exactly where the problematic section is. It's where we grab all of their different expressions and parse them out, concatenating them onto @script. If we only grab 4 or 5, it's fine, but this particular user has 120 of them and SQL just freaks out when we try to pass in 70k length nvarchar to it. But yeah, we definitely can't see it all on the same result pane, either.
September 27, 2012 at 10:31 pm
coronaride (9/27/2012)
Well, that's the problem. Like I mentioned, the users are able to build their own expressions through our UI and keep any number of them that they want. I know exactly where the problematic section is. It's where we grab all of their different expressions and parse them out, concatenating them onto @script. If we only grab 4 or 5, it's fine, but this particular user has 120 of them and SQL just freaks out when we try to pass in 70k length nvarchar to it. But yeah, we definitely can't see it all on the same result pane, either.
You don't need to see it. SELECT @Script then copy/paste/execute.
That's what I did when I had a similar problem. In my case, I was finding a truncation occurring somewhere along the line (the SQL got an error when I did the above). That's when I scrolled to the end of it and found a big chunk was missing.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply