RE: [U2] sql server & universe
I fully agree with Dave. One of the purposes of data typing is to catch data errors - we tend to miss that when using MV. Another purpose is to define memory blocks that are right-sized for the data you need, so that no memory is wasted and/or it can be used more effiiciently. Understand your data and ensure the typing corresponds. Don't just leave the doors unlocked (unlimited data sizes) because you don't like to pull out your keys (data types/sizes) to open the doors. Something else, I've found it convenient to use different dict definitions which are used by SQL than those which are used by MV reports. This allows the SQL definitions to be tweaked without affecting existing and stable reports. Ever increase the size of a dict item by one character and had someone screaming a week later that reports were running down the page instead of across? ;) And Daniel, if you have a customer who is selling widgets for a billion dollars each, give them whatever the hell they want! lol T David Tod Sigafoos wrote: > Your best bet is to make your dictionaries match the > data. Not sure I would actually make the fields lenght > 999 but instead write a routine to examine the data and > let you know the best size to make it. If 13 char is the > longest(in this case) then smudge to 14 if you think the > data will change to that. > > Daniel wrote: ... >> we have a customer using SQL Server 2000 making queries >> to a UV 10.1.11 server >> and the length of the data is 12 or higher (like if the >> widget price was 9.99), this will cause the SQL >> Server query to fail with a "data value overflowed the >> data type used by the provider" error. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] sql server & universe {Unclassified}
David, > -Original Message- > From: [EMAIL PROTECTED] On Behalf Of David Tod Sigafoos > Sent: Wednesday, 23 November 2005 16:06 > To: Daniel Perttula > Subject: Re: [U2] sql server & universe > > Daniel, > > SQL is pretty insistent on data being what it is defined as > (a feature for some .. a drawback for others) > > Your best bet is to make your dictionaries match the data. > Not sure I would actually make the fields lenght 999 but > instead write a routine to examine the data and let you know > the best size to make it. Doesn't HS.SCRUB do this for you? Then you can describe the data correctly in Attribute 10. Or Leave that attribute blank [except for fields which need correct description like DATE, or DECIMAL 12,2] and describe everything else on the SQL side as VARCHAR(255) HTH Mike > If 13 char is the longest(in this case) then smudge to 14 if you > think the data will change to that. > > Tuesday, November 22, 2005, 3:40:56 PM, you wrote: > > DP> Hello all - quick question for the SQL gurus out there, we have a > DP> customer using SQL Server 2000 making queries to a UV 10.1.11 > DP> server. We've noted that SQL Server errors out if the data in any > DP> field matches or exceeds the field length specified in the field > DP> dictionary. For example if we have a > DP> dictionary: > > DP> WIDGET.PRICE > DP> <1> A > DP> <2> 100 > DP> <3> WIDGET PRICE > DP> <4> > DP> <5> > DP> <6> > DP> <7> MD2 > DP> <8> > DP> <9> R > DP> <10> 12 > The information contained in this Internet Email message is intended for the addressee only and may contain privileged information, but not necessarily the official views or opinions of the New Zealand Defence Force. If you are not the intended recipient you must not use, disclose, copy or distribute this message or the information in it. If you have received this message in error, please Email or telephone the sender immediately. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] sql server & universe
my guess is that a numeric of size 999 will barf just as bad and on every value. simply specify the sql data type as something reasonable like DECIMAL,19,2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Perttula Sent: Tuesday, November 22, 2005 6:41 PM To: u2-users@listserver.u2ug.org Subject: [U2] sql server & universe Hello all - quick question for the SQL gurus out there, we have a customer using SQL Server 2000 making queries to a UV 10.1.11 server. We've noted that SQL Server errors out if the data in any field matches or exceeds the field length specified in the field dictionary. For example if we have a dictionary: WIDGET.PRICE <1> A <2> 100 <3> WIDGET PRICE <4> <5> <6> <7> MD2 <8> <9> R <10> 12 and the length of the data is 12 or higher (like if the widget price was 9.99), this will cause the SQL Server query to fail with a "data value overflowed the data type used by the provider" error. We are considering bumping the field lengths for all of our ODBC field dictionaries up to 999 or higher just to avoid these errors. Are there any negatives to doing this, specifically could it cause problems for SQL Server? Or is there another way to handle this (like a query option or switch or ??) Any help or suggestions would be appreciated! Thanks, Dan Daniel Perttula Mortgage Builder Software [EMAIL PROTECTED] (800) 850-8060 x 111 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] sql server & universe
Daniel, SQL is pretty insistent on data being what it is defined as (a feature for some .. a drawback for others) Your best bet is to make your dictionaries match the data. Not sure I would actually make the fields lenght 999 but instead write a routine to examine the data and let you know the best size to make it. If 13 char is the longest(in this case) then smudge to 14 if you think the data will change to that. Tuesday, November 22, 2005, 3:40:56 PM, you wrote: DP> Hello all - quick question for the SQL gurus out there, we have a customer DP> using SQL Server 2000 making queries to a UV 10.1.11 server. We've noted DP> that SQL Server errors out if the data in any field matches or exceeds the DP> field length specified in the field dictionary. For example if we have a DP> dictionary: DP> WIDGET.PRICE DP> <1> A DP> <2> 100 DP> <3> WIDGET PRICE DP> <4> DP> <5> DP> <6> DP> <7> MD2 DP> <8> DP> <9> R DP> <10> 12 DP> and the length of the data is 12 or higher (like if the widget price was DP> 9.99), this will cause the SQL Server query to fail with a "data DP> value overflowed the data type used by the provider" error. We are DP> considering bumping the field lengths for all of our ODBC field dictionaries DP> up to 999 or higher just to avoid these errors. Are there any negatives to DP> doing this, specifically could it cause problems for SQL Server? Or is there DP> another way to handle this (like a query option or switch or ??) DP> Any help or suggestions would be appreciated! --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/