Another VARCHAR Size limitation Problem :(
-
I have to build a dynamic query, now again the problem is the size limitation of VARCHAR i.e 8000.
Look at the following code, i used multiple string variables to avoid the size limitation.
Now problem is when the
LEN(@Str1+@Str2+@Str3) is greater than 8000 (which will be in my case as i have around 425 columns with names as (Point1','Point2'.....'Point425')
the EXECUTE (@Str1+@Str2+@Str3) gives me error.
Even PRINT(@Str1+@Str2+@Str3) truncates the string upto 8000. Some one please help me out to solve this problem.
DECLARE @PointName VARCHAR(200),
@Str1 VARCHAR(8000),
@Str2 VARCHAR(8000),
@Str3 VARCHAR(8000),
@New VARCHAR(8000)
DECLARE PointNames_Cur CURSOR FOR
SELECT PointName
FROM Average_TagInfo
ORDER BY TagId ASC
OPEN PointNames_Cur
FETCH NEXT FROM PointNames_Cur INTO @PointName
SET @Str1 = 'CREATE TABLE PerSecondValues (TimeStamp DATETIME NOT NULL '
SET @Str2 = ''
SET @Str3 = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @New = ','+@PointName+' DECIMAL (38, 6)'
IF LEN(@Str2+@New) < 8000
SET @Str2= @Str2+@New
ELSE
SET @Str3 = @Str3+@New
FETCH NEXT FROM PointNames_Cur INTO @PointName
END
SET @Str3 = @Str3+')'
EXECUTE (@Str1+@Str2+@Str3)
-
Hi!!!
No way to increase size for varchar more than 8000 and also check for
Network Packet Size... is the size of the tabular data scheme (TDS) packets used to communicate between applications and the relational database engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.
Regards,
Papillon
-
Create a base table when the length reaches a threshhold and then use alter table statements to complete the task.
Exec 'STRING TO CREATE TABLE'
Exec 'STRING TO ALTER TABLE AND ADD COLUMNS'
-
Thanks alot, indeed an implementable idea, but in this case i want to know that how will i check that the row size of the table has reached to its max limit i.e (i think 8060), as this check should be there when i will be adding the column in the table.
-
During the cursor test the length of @str1 and when it reaches a threshhold (7500 Bytes or so) complete the create table command and execute the statement. Then continue to build alter table statement either per column or at a threshhold (7500 Bytes) then execute them in the cursor as well. When the cursor is comeplete the table should be built!
You also have a problem with your cursor!
You need to test for a record after you fetch or you will process the last record twice.
/* Loop through the cursor */
While @@fetch_status = 0
Begin
/* Fetch new row from cursor */
Fetch Next From get_tTransaction_Data into @record_guid
/* Test and see if there is data */
If @@fetch_status = 0
Begin
-
Hi!!!!
declare @t as varchar(10)
set @t = 'hi'
select len(@t)
U will get row size is 2!!!
Regards,
Papillon
-
The width of the table.. not the text in the variable!!!
-
ooooo guys where r u goin...
the solution provided by Kory Becker was perfect, but after that i asked about the limit on the row width or row size of a table...which i think is 8096 or close to it......consider the following scenerio...suppose i build the table using the Kory's solution (adding one column at a time)
Seq # ColumnAddedinTable TableRowSize (Bytes)
1 TimeStamp DateTime 8
2 Point1 Decimal(38,6) 8+17
3 Point2 Decimal(38,6) 8+17+17
. . .
. . .
. . .
. . .
in this way i will continue adding the coulmns to the table as there will be no limit on number of columns coming from cursor (i.e cursor could give me even 1000 points if they are present in the table on which cursor is defined)...now the problem will be the size limitation of the table Row.
Now if you ppl understand the problem then give me solution in this context. Thanks
-
If you're building the table column by column, you should be able to stick in a 'SELECT COL_LENGTH('myTable', 'lastColumn')' to keep tabs on the accumulated table width.
/Kenneth
-
yah thats wat i was thinking but u know i dont need to use the COL_LENTH function coz all my columns (except TimeStamp) will be DECIMAL(38,6) therefore each column will use 17 Bytes, so i can accumulate the size...hmmmm
ne ways thanks every one specially Kory
-
What you are trying to store in DECIMAL(38,6) what cannot be stored in FLOAT?
Precision of FLOAT is 53 digits and size 8 bytes.
If you nees fixed comma you can covert to DECIMAL(38,6) when you select data from the table, in a view or SP.
_____________
Code for TallyGenerator -
decimal is precise, while float is approximate.
Using floats in the wrong place or for the wrong purpose will produce 'corrupted' data (in the sense that you've invited rounding errors).
just my .02 though
/Kenneth
-
What do you mean "precise"?
There is no such thing in math. There is only number of precise figures.
Float has 53 precise figures, decimal precision depends on it's value. 25 as DECIMAL(38, 6) keeps only 8 precise figures, but as float it's still 53 figures precise.
Try ROUND(FloatValue, 6) and you'll see the same precision as your decimal.
But if you work out those values every math operation steals one precise figure. So it's better to round as late as possible, after all operations. Applying DECIMAL type you do rounding first and than just loose precision after each operation.
Try this simple exersise:
declare @C decimal(38, 6), @d decimal(38,6)
SET @a = 2
SET @b-2 = 3
SET @C = 2
SET @d = 3
select @a/@b + @a/@b, @C/@d + @C/@d
Look at the results and tell me about precision of DECIMAL values.
_____________
Code for TallyGenerator -
Well, first of all, let me just state that I'm not a math guy.
Precise in my mind could also be called 'predictable'. Floats are not predictable, they (as you say) have to be rounded by whatever implementation of rounding that you choose or have available. There is a problem with that, since there are no one universal rule how to round. There's at least four 'official' methods, and of course different implementations follow different defaults for which to use. Therefore - unpredictable.
Another thing is, that when I enter a value into a table, I expect that same value to be there when I later retrieve it. Float doesn't do that - Decimal does.
And since I'm not a math guy, perhaps someone could explain if the BOL authors aren't either, or why is this piece in there?
float and real
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.
AFAIK, 'approximate' is the opposite of 'precise', not?
Anyways, since stuff like 3.3 when stored as a float translates to 3.2999999999999998 and when stored as a decimal(38,6) is 3.300000, in my mind there's no doubt about which is 'precise' and which is not.
Please feel free to educate me on the higher level of math science, though
/Kenneth
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply