July 17, 2013 at 9:15 am
Stefan Krzywicki (7/17/2013)
I've been having my own little annoyance with something SQL Server related lately. If you use SSIS to create text files and save it from one server to another, into SQL Server, the text qualifier changes from <none> to _x003C_none_x003E_ in every connection manager that affects a text file. To fix it I have to open the SSIS package on the local machine, delete the text qualifier and save it again. It isn't a huge deal, but it is annoying and I have to remember every time I deploy a new SSIS package from Dev to Prod that creates a text file and I don't do that very often.
Really? I don't recall seeing that issue. But then, we're using TFS to move the files up through our different servers.
July 17, 2013 at 9:29 am
Brandie Tarvin (7/17/2013)
Stefan Krzywicki (7/17/2013)
I've been having my own little annoyance with something SQL Server related lately. If you use SSIS to create text files and save it from one server to another, into SQL Server, the text qualifier changes from <none> to _x003C_none_x003E_ in every connection manager that affects a text file. To fix it I have to open the SSIS package on the local machine, delete the text qualifier and save it again. It isn't a huge deal, but it is annoying and I have to remember every time I deploy a new SSIS package from Dev to Prod that creates a text file and I don't do that very often.Really? I don't recall seeing that issue. But then, we're using TFS to move the files up through our different servers.
It could easily be a consequence of our setup, permissions, network, etc... And in typing it out for all of you, I think I figured out what it is doing, if not why. _x003C_ is code for < and _x003E_ is code for >. It is treating it like some kind of string and putting in the code for non-standard characters. I'm going to (if I remember) try deleting <none> before I save it next time and see what happens.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 17, 2013 at 9:45 am
WHOO HOO!
Not only did I just teach myself how to use MERGE (never had time or reason), but I managed to use it correctly with a Cross Applied Tally table and an INNER JOIN inside the USING clause.
<SnoopyDance>
And it only took me 30 minutes to figure it all out.
July 17, 2013 at 9:54 am
Brandie Tarvin (7/17/2013)
WHOO HOO!Not only did I just teach myself how to use MERGE (never had time or reason), but I managed to use it correctly with a Cross Applied Tally table and an INNER JOIN inside the USING clause.
<SnoopyDance>
And it only took me 30 minutes to figure it all out.
There is little as enjoyable as figuring things out. I think that's one reason I love IT despite all the frustrations that go along with it.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 17, 2013 at 12:01 pm
Brandie Tarvin (7/17/2013)
I see a SQL Spackle article in my future... Plus a blog post followup.
Write it up and send it in
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 17, 2013 at 12:39 pm
Steve Jones - SSC Editor (7/17/2013)
Koen Verbeeck (7/17/2013)
Jeff Moden (7/16/2013)
Steve Jones - SSC Editor (7/16/2013)
Arrgggg, some days I hate software.BWAAA-HAAA!!!! You mean like the stuff that generates the SSC News Letter? :sick::-D:-P
Touché 😀
Yeah, that's been annoying lately. An argument is underway
Well at least they didn't incorporate a "Ribbon Bar". 😛
--Jeff Moden
July 17, 2013 at 2:07 pm
Stefan Krzywicki (7/17/2013)
I've been having my own little annoyance with something SQL Server related lately. If you use SSIS to create text files and save it from one server to another, into SQL Server, the text qualifier changes from <none> to _x003C_none_x003E_ in every connection manager that affects a text file. To fix it I have to open the SSIS package on the local machine, delete the text qualifier and save it again. It isn't a huge deal, but it is annoying and I have to remember every time I deploy a new SSIS package from Dev to Prod that creates a text file and I don't do that very often.
I had that issue once. It had a specific reason why this happens, but I can't remember why...
So basically I'm useless 😀
(and now on to Jeff with "BWA-HAAAAA! This never happens with bcp!!!") 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 17, 2013 at 2:18 pm
Stefan Krzywicki (7/17/2013)
I've been having my own little annoyance with something SQL Server related lately. If you use SSIS to create text files and save it from one server to another, into SQL Server, the text qualifier changes from <none> to _x003C_none_x003E_ in every connection manager that affects a text file. To fix it I have to open the SSIS package on the local machine, delete the text qualifier and save it again. It isn't a huge deal, but it is annoying and I have to remember every time I deploy a new SSIS package from Dev to Prod that creates a text file and I don't do that very often.
I remember why.
What appears to happen is that when a .dtsx is copied to a new server then the text qualifiers for flat file connections can become corrupted. Where no text qualifier is defined, then <none> is shown in the properties, but this can get converted to _x003C_none_x003E_ (i.e. <none> with the < and > converted to their unicode values). So when SSIS exports the CSV file, it surrounds every field with _x003C_none_x003E_.
This is the workaround -
There are 2 ways to edit the TextQualifier property of a flat file connection. One is to use an edit box and the other is to directly edit the property in the Property explorer. If you use the edit box and blank out the Text Qualifier then it actually sets the property to <none> (whether you want it to or not). If however you blank out the TextQualifier property directly in the Property explorer then it stays as blank.
HTH
---------------------------------------------------------------------
July 17, 2013 at 5:56 pm
Jeff Moden (7/17/2013)
Steve Jones - SSC Editor (7/17/2013)
Koen Verbeeck (7/17/2013)
Jeff Moden (7/16/2013)
Steve Jones - SSC Editor (7/16/2013)
Arrgggg, some days I hate software.BWAAA-HAAA!!!! You mean like the stuff that generates the SSC News Letter? :sick::-D:-P
Touché 😀
Yeah, that's been annoying lately. An argument is underway
Well at least they didn't incorporate a "Ribbon Bar". 😛
why should they need a ribbon bar when they know how to waste far more screen space without having one?
Tom
July 17, 2013 at 6:25 pm
Brandie Tarvin (7/17/2013)
WHOO HOO!Not only did I just teach myself how to use MERGE (never had time or reason), but I managed to use it correctly with a Cross Applied Tally table and an INNER JOIN inside the USING clause.
<SnoopyDance>
And it only took me 30 minutes to figure it all out.
One thing to take care with when using MERGE though: A Hazard of Using the SQL Merge Statement[/url]
It's not a bug it's a feature, but does have the potential to cause you grief if you don't know about it.
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
July 17, 2013 at 8:37 pm
dwain.c (7/17/2013)
Brandie Tarvin (7/17/2013)
WHOO HOO!Not only did I just teach myself how to use MERGE (never had time or reason), but I managed to use it correctly with a Cross Applied Tally table and an INNER JOIN inside the USING clause.
<SnoopyDance>
And it only took me 30 minutes to figure it all out.
One thing to take care with when using MERGE though: A Hazard of Using the SQL Merge Statement[/url]
It's not a bug it's a feature, but does have the potential to cause you grief if you don't know about it.
IMO the big one: multiple updates to the same row. MERGE will crap out on that, but if you are doing say daily updates to CRM BI, this is a common situation.
July 17, 2013 at 8:39 pm
Revenant (7/17/2013)
dwain.c (7/17/2013)
Brandie Tarvin (7/17/2013)
WHOO HOO!Not only did I just teach myself how to use MERGE (never had time or reason), but I managed to use it correctly with a Cross Applied Tally table and an INNER JOIN inside the USING clause.
<SnoopyDance>
And it only took me 30 minutes to figure it all out.
One thing to take care with when using MERGE though: A Hazard of Using the SQL Merge Statement[/url]
It's not a bug it's a feature, but does have the potential to cause you grief if you don't know about it.
IMO the big one: multiple updates to the same row. MERGE will crap out on that, but if you are doing say daily updates to CRM BI, this is a common situation.
Yes! I have seen that one too.
Isn't that also a problem for UPDATE?
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
July 17, 2013 at 8:40 pm
Revenant (7/17/2013)
dwain.c (7/17/2013)
Brandie Tarvin (7/17/2013)
WHOO HOO!Not only did I just teach myself how to use MERGE (never had time or reason), but I managed to use it correctly with a Cross Applied Tally table and an INNER JOIN inside the USING clause.
<SnoopyDance>
And it only took me 30 minutes to figure it all out.
One thing to take care with when using MERGE though: A Hazard of Using the SQL Merge Statement[/url]
It's not a bug it's a feature, but does have the potential to cause you grief if you don't know about it.
I have to chastise myself for posting this in "Anything that is not about SQL". It is; sorry.
IMO the big one: multiple updates to the same row. MERGE will crap out on that, but if you are doing say daily updates to CRM BI, this is a common situation.
July 17, 2013 at 8:41 pm
dwain.c (7/17/2013)
Revenant (7/17/2013)
dwain.c (7/17/2013)
Brandie Tarvin (7/17/2013)
WHOO HOO!Not only did I just teach myself how to use MERGE (never had time or reason), but I managed to use it correctly with a Cross Applied Tally table and an INNER JOIN inside the USING clause.
<SnoopyDance>
And it only took me 30 minutes to figure it all out.
One thing to take care with when using MERGE though: A Hazard of Using the SQL Merge Statement[/url]
It's not a bug it's a feature, but does have the potential to cause you grief if you don't know about it.
Yup. But my self-chastisation still applies.
IMO the big one: multiple updates to the same row. MERGE will crap out on that, but if you are doing say daily updates to CRM BI, this is a common situation.
Yes! I have seen that one too.
Isn't that also a problem for UPDATE?
July 17, 2013 at 8:47 pm
Revenant (7/17/2013)
dwain.c (7/17/2013)
Revenant (7/17/2013)
dwain.c (7/17/2013)
Brandie Tarvin (7/17/2013)
WHOO HOO!Not only did I just teach myself how to use MERGE (never had time or reason), but I managed to use it correctly with a Cross Applied Tally table and an INNER JOIN inside the USING clause.
<SnoopyDance>
And it only took me 30 minutes to figure it all out.
One thing to take care with when using MERGE though: A Hazard of Using the SQL Merge Statement[/url]
It's not a bug it's a feature, but does have the potential to cause you grief if you don't know about it.
Yup. But my self-chastisation still applies.
IMO the big one: multiple updates to the same row. MERGE will crap out on that, but if you are doing say daily updates to CRM BI, this is a common situation.
Yes! I have seen that one too.
Isn't that also a problem for UPDATE?
Sorry to quote myself, but something went wrong with my previous posting.
Yes, it is. The problem is that UPDATE does not detect the problem but MERGE does.
Once again, IMO this does not belong under the 'Anything that is NOT about ...' heading, because it does.
Viewing 15 posts - 40,591 through 40,605 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply