Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
1 12
Newest
 Caucus
Impact
~9k people reached

  • 0 posts edited
  • 0 helpful flags
  • 15 votes cast
Aug
4
awarded  Caucus
Aug
3
accepted Show all stored procedure definitions (content) for editing
Aug
3
comment Show all stored procedure definitions (content) for editing
Checks done. Largest proc was only 31,500 characters so I was well under the 65,535 limit. A lucky escape and your lesson will be well-remembered :-)
Aug
3
comment Show all stored procedure definitions (content) for editing
Well I didn't win the lottery; so here I am. Done some tests today creating massive stored proc's, and I do see the truncation, so yes I was lucky. I'm going to run your code to find the large procs, then check each one manually using Modify to see that they're all there. Thank you very much for sharing your wisdom.
Jul
31
comment Show all stored procedure definitions (content) for editing
No reason at all @MisterMagoo apart from that my tiny brain was reeling at the thought of the day ahead, and missed the simple things...
Jul
31
comment Show all stored procedure definitions (content) for editing
... If so I'm doing the lottery tomorrow night
Jul
31
comment Show all stored procedure definitions (content) for editing
Thanks @Aaron. Just to help me get my head around this... After I had pasted the results into an editor and checked for the create issue (just one occurrence), I executed the query and it completed (47,800 lines of code in all and 550 proc's compiled). I then ran an alternate query with LEN(m.definition) sorted in descending order. The largest proc was 33,222 (characters?) in size. I located that in the object browser and went to modify it. It loaded up in all its former glory; no truncation, line breaks and tabs all in tact, everything (AFAIK). Am I just being extremely lucky here?
Jul
31
comment Show all stored procedure definitions (content) for editing
Fantastic post, thank you. I ran the > 4000 script, and it pulled out about 80 proc's. When I checked one though in my orginial code, ALL of the code seems to be there (no truncation). Why would that be? In my SSMS Tools > Options, the Maximum Characters Retrieved value is 65535. Is that larger than normal (not sure I've change it a long time ago).
Jul
31
awarded  Citizen Patrol
Jul
31
revised Show all stored procedure definitions (content) for editing
added 57 characters in body
Jul
31
comment Show all stored procedure definitions (content) for editing
There's only me, so that's not a problem. And there's no other requirements to modify stored procedures at this time, so a large file is not a problem. One-by-one is going to take an age just for the time spent opening each one. I have to replicate this on the live server once I'm done.
Jul
31
revised Show all stored procedure definitions (content) for editing
Alter replaces Create in results
Jul
31
asked Show all stored procedure definitions (content) for editing
Jun
25
awarded  Popular Question
Apr
20
awarded  Popular Question
Mar
9
awarded  Popular Question
Jan
12
comment Can I use Backup/Restore to clone a database? Running out of ideas
Lovely, thank you. I still vote to keep this question open from the "duplicate" as it avoids people having to separate the relevant data (and a nice clean example of code) from the noise that surrounds it.
Jan
12
accepted Can I use Backup/Restore to clone a database? Running out of ideas
Jan
12
comment Can I use Backup/Restore to clone a database? Running out of ideas
Thanks Kenneth. I assume this also won't affect the restoring state of the original database?
Jan
12
comment Can I use Backup/Restore to clone a database? Running out of ideas
That question deals with multiple instances, or the requirement for. Also experts are clearly warning against production server usage with the solution, of which this is a production machine.