February 21, 2014 at 10:00 am
SQLRNNR (2/21/2014)
jcrawf02 (2/21/2014)
Jeff (and all you guys who helped test the delimited split 8k) are solving problems you don't even know about. Found this in my inbox today from a former colleague:I actually got it to work. Here’s how:
The problem was with how the parameter was being passed into the stored procedure. The sp can’t read the all values as separate and distinct values, it’s read as one which is why when the character length was set to 15 it could read the first value only programid in the program table is 15 characters) but when I was set to 200 it interpreted it as one value. To fix add this to the beginning of the main query:
Select Item
Into #LOBList
From dbo.fDelimitedSplit8K(@rpLOB,',')
and instead of adding the parameter to the where clause create a join like this:
join #LOBList LOB on p.programid = LOB.Item
Everything ing in SSRS was correct already. It appears to work now. Let me know if you have any questions.
Very cool
Very cool, indeed. I'm a little concerned over the term "LOB", though. DelimitedSplit8K wasn't designed to handle LOBs effeciently and the built in cteTally would need to be expanded or "silent truncation" could occur.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2014 at 12:18 pm
Jeff Moden (2/21/2014)
SQLRNNR (2/21/2014)
jcrawf02 (2/21/2014)
Jeff (and all you guys who helped test the delimited split 8k) are solving problems you don't even know about. Found this in my inbox today from a former colleague:I actually got it to work. Here’s how:
The problem was with how the parameter was being passed into the stored procedure. The sp can’t read the all values as separate and distinct values, it’s read as one which is why when the character length was set to 15 it could read the first value only programid in the program table is 15 characters) but when I was set to 200 it interpreted it as one value. To fix add this to the beginning of the main query:
Select Item
Into #LOBList
From dbo.fDelimitedSplit8K(@rpLOB,',')
and instead of adding the parameter to the where clause create a join like this:
join #LOBList LOB on p.programid = LOB.Item
Everything ing in SSRS was correct already. It appears to work now. Let me know if you have any questions.
Very cool
Very cool, indeed. I'm a little concerned over the term "LOB", though. DelimitedSplit8K wasn't designed to handle LOBs effeciently and the built in cteTally would need to be expanded or "silent truncation" could occur.
Maybe LOB means something different in that shop - after all, he said he had tried setting the length to 200 in the hope that it would then see the whole thing, and of course it did but treated it as just one item until he included the splitter.
Tom
February 21, 2014 at 1:00 pm
TomThomson (2/21/2014)
Jeff Moden (2/21/2014)
SQLRNNR (2/21/2014)
jcrawf02 (2/21/2014)
Jeff (and all you guys who helped test the delimited split 8k) are solving problems you don't even know about. Found this in my inbox today from a former colleague:I actually got it to work. Here’s how:
The problem was with how the parameter was being passed into the stored procedure. The sp can’t read the all values as separate and distinct values, it’s read as one which is why when the character length was set to 15 it could read the first value only programid in the program table is 15 characters) but when I was set to 200 it interpreted it as one value. To fix add this to the beginning of the main query:
Select Item
Into #LOBList
From dbo.fDelimitedSplit8K(@rpLOB,',')
and instead of adding the parameter to the where clause create a join like this:
join #LOBList LOB on p.programid = LOB.Item
Everything ing in SSRS was correct already. It appears to work now. Let me know if you have any questions.
Very cool
Very cool, indeed. I'm a little concerned over the term "LOB", though. DelimitedSplit8K wasn't designed to handle LOBs effeciently and the built in cteTally would need to be expanded or "silent truncation" could occur.
Maybe LOB means something different in that shop - after all, he said he had tried setting the length to 200 in the hope that it would then see the whole thing, and of course it did but treated it as just one item until he included the splitter.
LOB = Line of business
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
February 24, 2014 at 4:32 am
On Saturday I went to the Classic Rock Society awards. Fairly small run outfit, but well respected.
It was being hosted by one of my musical heroes - Fish, he of once being Marillion's singer fame...
So I arrived slightly later than I planned due to Sat Nav deciding that the town it was being held it was approximately 20 miles West of where it really was, or I can't write down a postcode to save my life, or I entered it wrong - you choose!
So anyway rather than getting 5 minutes after doors open, to be sociably on time I arrived 2 minutes before it started!
I walk in a see a couple of familiar faces, one of whom is the photographer for night, Chris. So I walk up to say hi, and realise he's talking to someone. So I wait. After a little while I decide to see who he's talking to... Yep it's Fish! Doh! So I left them to it!
Good night, couple of surprises, Steve Hackett was there to pick up an award for his bass player and then promptly left, only to be the winner of Best Guitarist. He got lost coming back... Got back after the awards had finished and just before the headline act took the stage. 😀
And I see still no news of 2014 release date or SQL Bits, starting to think I've imagined all of this...
Rodders...
February 24, 2014 at 9:22 am
rodjkidd (2/24/2014)
On Saturday I went to the Classic Rock Society awards. Fairly small run outfit, but well respected.It was being hosted by one of my musical heroes - Fish, he of once being Marillion's singer fame...
So I arrived slightly later than I planned due to Sat Nav deciding that the town it was being held it was approximately 20 miles West of where it really was, or I can't write down a postcode to save my life, or I entered it wrong - you choose!
So anyway rather than getting 5 minutes after doors open, to be sociably on time I arrived 2 minutes before it started!
I walk in a see a couple of familiar faces, one of whom is the photographer for night, Chris. So I walk up to say hi, and realise he's talking to someone. So I wait. After a little while I decide to see who he's talking to... Yep it's Fish! Doh! So I left them to it!
Good night, couple of surprises, Steve Hackett was there to pick up an award for his bass player and then promptly left, only to be the winner of Best Guitarist. He got lost coming back... Got back after the awards had finished and just before the headline act took the stage. 😀
And I see still no news of 2014 release date or SQL Bits, starting to think I've imagined all of this...
Rodders...
I went to the Rock and Roll Hall of Fame in Cleveland a few weeks ago. It was pretty neat to see the history of some bands and people. Large Rolling Stones exhibit there. Amazed to think how they've changed, from being Beatles-like in the 60s to where they are today.
February 24, 2014 at 10:00 am
Thanks for all the encouragement guys.
Quick update. Had one in-person and one telephone interview last week that both led to 2nd interviews scheduled this week. Still being pretty picky about positions I'll even entertain at this point.
The 2 biggest frustrations are getting the health insurance stuff figured out & and having been in a job that was all 2008R2 because most jobs now are looking for 2012 experience. Doesn't rule me out, but is a bit of a knock not having worked with 2012 in production.
Health insurance is a pain because the state I live in, New Hampshire, only has 1 provider in the exchange, so the rates and coverage are worse than what I could have gotten before the Affordable Care Act. I won't get any more political than that.
Got a couple of events coming up that I get to speak at, SQLSaturday #268 - Connecticut this week and then SQLSaturday #262 - Boston at the end of this month. Looking forward to both events since I haven't been to an event since the PASS Summit last October.
Looks like Grant is the only other Threadizen speaking at either event.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2014 at 12:20 am
Last night home. Start my journey back to Afghanistan Tuesday morning.
February 25, 2014 at 12:53 am
Lynn Pettis (2/25/2014)
Last night home. Start my journey back to Afghanistan Tuesday morning.
Good luck!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 25, 2014 at 3:11 am
Lynn - Good Luck!
Jack - er, Good Luck as well!
Rodders...
February 25, 2014 at 4:34 am
Lynn Pettis (2/25/2014)
Last night home. Start my journey back to Afghanistan Tuesday morning.
Best of luck!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2014 at 5:16 am
Lynn Pettis (2/25/2014)
Last night home. Start my journey back to Afghanistan Tuesday morning.
Have a safe flight and be safe while you're there. How long is this deployment scheduled to be?
February 25, 2014 at 6:03 am
Ed Wagner (2/25/2014)
Lynn Pettis (2/25/2014)
Last night home. Start my journey back to Afghanistan Tuesday morning.Have a safe flight and be safe while you're there. How long is this deployment scheduled to be?
Supposed to be 6 months. I may try extending it a few months if possible.
February 25, 2014 at 7:15 am
Lynn Pettis (2/25/2014)
Last night home. Start my journey back to Afghanistan Tuesday morning.
Good luck Lynn. Safe travels!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 25, 2014 at 7:41 am
Lynn Pettis (2/25/2014)
Last night home. Start my journey back to Afghanistan Tuesday morning.
Good luck and safe travels.
Hope it was a good time back home.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 25, 2014 at 10:39 am
Steve Jones - SSC Editor (2/24/2014)
I went to the Rock and Roll Hall of Fame in Cleveland a few weeks ago. It was pretty neat to see the history of some bands and people. Large Rolling Stones exhibit there. Amazed to think how they've changed, from being Beatles-like in the 60s to where they are today.
Sounds like a good place to visit.
The Stones - and most people think they have always just sounded like the "Stones" 😀
Rodders...
Viewing 15 posts - 43,066 through 43,080 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply