June 17, 2008 at 8:51 pm
Jeff:
Given that you've found a clever way to block annoying PM's :), I was forced to start this thread so that I could contact you:
--======
Jeff:
Whats a good Split() type function/method for SQL 2005? I need to break downa bunch of syscomments entries into lines and I cannot locate the threads of the big tests that you and Matt did on split functions.
Thanks, Barry
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2008 at 9:24 pm
Heh... I've got 100 PM's that I really want to keep... and Steve hasn't developed a way for me to download them, yet and I've been too busy to think of a way.
In case anyone needs to, they can always send me an email... I'd prefer that folks as for help via the forums here so that we can all benefit from the wisdom of the folks that reply, but I'll answer most emails. There are a couple of annoying folks that I've had to block altogether... but most everyone else can get through.
And, "No", Barry... I haven't blocked you (yet :hehe:)
Anyway, I think what you're looking for is a subsection called "Single Dimension "Monster" Splits (SQL Server 2005 only)" and that may be found in the article at the following URL...
http://www.sqlservercentral.com/articles/T-SQL/63003/
Also, take a look at the "antonio" link I provide in the introduction. Some pretty good info there, as well.
As for the link where Matt and I did a bunch of testing? I don't know why but I keep loosing track. I really should keep better track of those because he's an awesome tester and the stuff he and I have been through is just bloody remarkable. I may have to spend a weekend going through his posts and find all those tests we did together.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2008 at 9:39 pm
SYSCOMMENTS is no longer a table but is a view included for backward compatability. Module source is now in a varchar(max) column and can be obtained using at least two methods.
SQL:
select * from sys.sql_modules -- source is column "definition"
Function OBJECT_DEFINITION:
SELECT OBJECT_DEFINITION (OBJECT_ID(N'sys.sp_columns')) AS [Source]
SQL = Scarcely Qualifies as a Language
June 17, 2008 at 11:17 pm
Jeff Moden (6/17/2008)
Heh... I've got 100 PM's that I really want to keep... and Steve hasn't developed a way for me to download them, yet and I've been too busy to think of a way.
Like I said: "clever way to block PM's".
There are a couple of annoying folks that I've had to block altogether... but most everyone else can get through.
And, "No", Barry... I haven't blocked you (yet :hehe:)
Well that's 1, anyway.
Anyway, I think what you're looking for is a subsection called "Single Dimension "Monster" Splits (SQL Server 2005 only)" and that may be found in the article at the following URL...
http://www.sqlservercentral.com/articles/T-SQL/63003/
Also, take a look at the "antonio" link I provide in the introduction. Some pretty good info there, as well.
As for the link where Matt and I did a bunch of testing? I don't know why but I keep loosing track. I really should keep better track of those because he's an awesome tester and the stuff he and I have been through is just bloody remarkable. I may have to spend a weekend going through his posts and find all those tests we did together.
Thanks, Jeff. As for the rest, I may have confused the "Joins" discussion with the "Splits" discussion, so I wouldn't worry about it too much.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2008 at 11:21 pm
Carl Federl (6/17/2008)
SYSCOMMENTS is no longer a table but is a view included for backward compatability. Module source is now in a varchar(max) column and can be obtained using at least two methods.SQL:
select * from sys.sql_modules -- source is column "definition"
Function OBJECT_DEFINITION:
SELECT OBJECT_DEFINITION (OBJECT_ID(N'sys.sp_columns')) AS [Source]
Or from SysComments. Yeah, I knew all that Carl. I just need to break those humongous Varchar(MAX)'s down into bite-sized easy to print Lines.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 18, 2008 at 7:39 am
Easy to print? They contain CR/LF's... should print just fine if you display them in a text mode instead of a grid mode...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 10:00 am
Jeff Moden (6/17/2008)
As for the link where Matt and I did a bunch of testing? I don't know why but I keep loosing track. I really should keep better track of those because he's an awesome tester and the stuff he and I have been through is just bloody remarkable. I may have to spend a weekend going through his posts and find all those tests we did together.
Thanks for the glowing review. I do enjoy testing (my career guidance counselor thought I should have been a research doctor, but I just couldn't face that much academia), since that's usually where I learn most. Heck I often think I get more out of doing the actual testing than those watching on the sides, so it's certainly not entirely altruistic.
As to that old thread... I just haven't been able to find it. It's out of my 500 post limit, and the search interface isn't pulling it up: Regex, phone redaction, comedy-separated values pull up nothing (and yet that was the meat of the "initial" throwdown on CLR). I vaguely remember this being somewhere around September (so - August-October timeframe), but the only thing CLR I find is something that turned into a shout fest between several oldtimers on the board, that plain just doesn't need to get dug back up.
I tried looking at your old posts, and my old ones too (both from "My Posts" and from the adv. search), and I get nothing....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 18, 2008 at 4:18 pm
Jeff Moden (6/18/2008)
Easy to print? They contain CR/LF's... should print just fine if you display them in a text mode instead of a grid mode...
Well, sort of. There's several different ways to display proc listings from a SQL command line, but they all fall about two steps short. For instance, try displaying a valid listing for Master.sys.sp_vupgrade_mergetables from a SQL command. It's not too easy (or possible?) with just the built-in facilities. But I can do it just fine with your split code (with minor modifications for this use).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 18, 2008 at 4:21 pm
Oh, and that's another thing that I discovered today: They do not ALL contain CR/LF's. Some of them contain just LF's! That's a nice little inconsistency.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 18, 2008 at 5:40 pm
rbarryyoung (6/18/2008)
Jeff Moden (6/18/2008)
Easy to print? They contain CR/LF's... should print just fine if you display them in a text mode instead of a grid mode...Well, sort of. There's several different ways to display proc listings from a SQL command line, but they all fall about two steps short. For instance, try displaying a valid listing for Master.sys.sp_vupgrade_mergetables from a SQL command. It's not too easy (or possible?) with just the built-in facilities. But I can do it just fine with your split code (with minor modifications for this use).
Ummmm... You sure about that? Either of the following methods seem to do just fine in the text mode instead of the grid mode....
SELECT Definition
FROM Master.sys.All_SQL_Modules
WHERE Object_ID = OBJECT_ID('sp_vupgrade_mergetables')
USE Master
EXEC sp_HelpText 'sys.sp_vupgrade_mergetables'
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 7:52 pm
Jeff Moden (6/18/2008)
rbarryyoung (6/18/2008)
Jeff Moden (6/18/2008)
Easy to print? They contain CR/LF's... should print just fine if you display them in a text mode instead of a grid mode...Well, sort of. There's several different ways to display proc listings from a SQL command line, but they all fall about two steps short. For instance, try displaying a valid listing for Master.sys.sp_vupgrade_mergetables from a SQL command. It's not too easy (or possible?) with just the built-in facilities. But I can do it just fine with your split code (with minor modifications for this use).
Ummmm... You sure about that? Either of the following methods seem to do just fine in the text mode instead of the grid mode....
SELECT DefinitionFROM Master.sys.All_SQL_Modules
WHERE Object_ID = OBJECT_ID('sp_vupgrade_mergetables')
USE Master
EXEC sp_HelpText 'sys.sp_vupgrade_mergetables'
Yep. All of these techniques work some of the time, but none of the built-in techniques work consistently all of the time. They might look fine, but they won't compile.
For instance, on all of my SSMS installs, the max textwidth it will display is 8192 (if I set it higher, it will just lower it back to 8192). So here is what lines 166-170 look like from the first query:
union all
select col_name =
(1 row(s) affected)
As for sp_HepText, it inserts line breaks every X characters (not sure how big X is...) so here is what lines 209-214 look like:
union all
select col_name = 'automatic_reinitialization_policy', col_type = 'bit not null default 0'-- whether or not to upload first on reinits that are triggered by certain publication/article property
changes
) as t1
left outer join
Obviously, neither of these is going to compile. And I need something that will work all of the time (or 99.9%).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 18, 2008 at 10:22 pm
Ack! I see what you mean...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 11:09 pm
Yeah. It's easy to get it to work when you want to pipe it into a Varchar(MAX) variable and execute it, which is what I usually do, because the TextWidth constraints don't bite you there.
But sometimes, and this is one of those times, I am building and maintaining a system on a "Gold Code" server instance, and then periodically building kits for distribution to other servers. Up to a point, I can do that myself through SSMS, but I am beyond that point (well beyond, actually) plus the customer wants that part automated and "fool-proof" so that they can do it themselves. As close as I can get to automating this (without writing SQLCLR-SMO routines) is to execute SQL commands that throw the scripts into the output windows with instructions on how to save the output into one of the Kit's build script files.
The good news is that your code worked great and I am now automatically generating thousands of lines of SQL for distribution. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 18, 2008 at 11:36 pm
Ya gotta send me a copy of what you did... I'd love to see your implementation... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 11:41 pm
Sure. I don't have it here right now, so I'll have to send it from work tomorrow.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply