Ten = Fifteen?
-
This is brought to you by a SQL Server MVP. Actually it was brought to me by an MVP and I thought that I'd talk about it since it's so strange that I can't really understand why someone would allow this. So imagine you're doing your good deed for the day and trawling forums looking to help a few people during a slow time. You happen upon this situation.
I've got a field declared as char(10), and my datareader (.NET) in SSIS picks up the column as DT_WSTR 10, however some of my data is up to 15 characters long. What can I do?
If you didn't just double-take, you probably need to reread that. And if you're suddenly worried, don't be. This is a Progress database, not SQL Server. It's strange to me that you can declare a column that appears to be for results only, but actually store more data in there. In C you could do this with arrays and that's were we get all these wonderful buffer overruns when we go past the end of the array and start reading and writing into other memory spots. There's a workaround if you need it.
However I'm just stunned that anyone, even back in the day, would actually allow you to declare a data type one way and then ignore that declaration for the storage of data. I realize flexibility is nice, but this causes and allows many more problems than it solves. Probably less of an issue in databases than in other programming areas, but still.
I ended up reading the workaround first and then the thread, so I wasn't completely sure what my MVP friend wanted me to see, but once I did get it, I wasn't sure whether to laugh out loud or cry in my coffee.
I still can't believe anyone architected a program this way.
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 -
Not that I need a workaround for a problem I don't have in a database I don't have, but I was curious to see what kind of workaround was devised.
But the link doesn't work.
-
I don't mean to pick on spelling (I'm no expert myself, goodness knows), but this sounded funny:
"So imagine you're doing your good dead for the day..."
A little gruesome, no? 🙂
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Whoopsie! It's corrected and I guess the spell checker only does so much
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 -
I picked up on 'trolling' forums. I suspect you meant 'trawling', unless you think we're a rather mischievous bunch!
--
Scott -
I found this post to be very humerous in the forum talking about the fix:
This tool can be run while the database is up and can analyze the entire database. This will require a large amount of system resources and time to complete. A good estimate would be to double the amount of time an online backup takes.
So, it can be done online as long as you don't mind it killing your system performance for a really long time. I'm sold. Put me down for two of them in case I wear out the first one!!
And if that didn't convince you to use it on your production database, he then said:
I caution to have the backup as it corrupted my database on one run.
Better bump my order up to a baker's dozen. I don't think I can get enough of this amazing product. Surely, a bargain at twice the price!!!
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
I'm sure Steve thinks we're a rather mischievous bunch whether we're trolling or trawling! 🙂
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Lol, I love it
And people are actually using this system? I will make sure to check future jobs before becoming a DBA for such a system -
What is even funnier is the fact that the database system is called *Progress*. With that kind of progress, who needs databases--lets use stone tablets instead
. -
Lighten up, people! SQL Server isn't the only DB in the world.
Defining the length of a column in SQL Server defines both the number of characters to display and the maximum number of characters to store for the column. For Progress, the length of a column only defines the maximum number of characters to display, not the maximum number for storage. It's as if all text columns have an implied varchar(32000). (Progress has a larger text column limit than SQL Server.) This has always been a hard concept for SQL Server people to accept and understand.
In general, this difference in how the column length is used is no problem, as long as you only use the Progress development environment. It becomes a big problem when you interface with the rest of the world (i.e. .Net). That's what the SQLLength parameter is for, to tell the rest of the world what the maximum "storage" size is.
Initially, the SQLLength parameter is set to the (display) column width. It can be altered at any time. The dbtool is one way to do this. It figures out the maximum size of each text column in every table and sets the SQLLenght parameter to that, but it has to scan the entire database in order to do that. This is why it uses lots of resources. Once set from a suffiecent amount of data, you ususally don't have to set the SQLLength parameter again.
-
That just isn't right. Doesn't matter what world you come from - when I say 10, I want 10, not 10 if I really need and more or less depending on the situation, I said 10, dammit... make it 10. When I write code, it's not a suggestion.
-
"I've got a field declared as char(10), and my datareader (.NET) in SSIS picks up the column as DT_WSTR 10, however some of my data is up to 15 characters long. What can I do?"
----------------
Ummm... how about:
1) declare the field as char(15)
2) check your bank account balance and make sure it is not overdrawn also
3) send an email to all users telling them not to enter any more than 10 characters in the field from now on
4) ignore it and hope all works out for the best - after all probably not too many people use those last 5 characters anyway
5) blame the problem on someone else
6) don't call it a problem.. call it a feature
-
Before the SQL Serverites start pointing too many fingers, many other products including SQLServer (or more specifically the Excel ADO code) are guilty of similar things.
Try importing a text column from an Excel spreadsheet into SQLServer2005 using the SSIS wizard. Feel free to define the import column as nvarchar(2000) or TEXT if you want, the package will simply fail if any string is longer than 255.
Just because the data in the source has a defined size, and the destination has a defined size doesn't mean that the SSIS package gives two hoots about those things
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=967986&SiteID=1
-
I'm just beginning to familiarize myself with SQL Server after over 20 years with Progress development and dba'ing and it's great to hear the same old complaint from the other side of the fence.
Problem is that it's easy enough to control the maximum number of characters entered in a Progress CHARACTER column. However, the display format is not the way to go - it's the default DISPLAY format. It simply controls the default display - screen, printer, whatever - of the column.
So, the size being referred to here is not the "defined size". As was previously mentioned the Progress CHARACTER datatype is more like a SQL VARCHAR(32000).
Even though I'm new here, I suspect RTFM has the same meaning in this world.
-
Good point on the Excel code.
Every product has bugs and I'd consider this one just as I'd consider Excel's driver not sending over more than 255 character.
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply