July 2, 2009 at 11:14 am
Or you can set column width:
Tools \ Options \ Query results \ SQL Server \ Results to Text \ Maximumnumber of characters displayed in each column
July 2, 2009 at 11:42 am
noelpv (7/2/2009)
Or you can set column width:Tools \ Options \ Query results \ SQL Server \ Results to Text \ Maximumnumber of characters displayed in each column
Yes, this is exactly what I wrote in my yesterday's reply.
July 3, 2009 at 5:51 am
Hi there,
Thnx for your reply. I tried your work around but it seems to be not working, may be I am making some mistake, but let me tell you what I have tried.
1. Created a table called Test1
2. Added 2 columns a) col1 float & b) col2 numeric(38,17)
3. Inserted 2 records.
12345678998765432123456789.98765432100000000
123456789.98765433123456789987.65432100000000000
4. Generated the insert statement by the SP you have provided
insert into dbo.test1 ([col1], [col2]) values (0x4345ee2a319dbfbc, 0x261100010091ba4e78fe7c994c360a00);
insert into dbo.test1 ([col1], [col2]) values (0x419d6f3457f35ba8, 0x261100010068c688f3054290371be427);
5. Created a table called Test1 in another Db.
6. Added 2 columns a)col1 numeric(38,10) b)col2 numeric(38,17)
7. Tried to execute the insert statement, it raised an error
Arithmetic overflow error converting varbinary to data type numeric.
8. At the same time, if I copied these 2 records by means of CTRL+C and pasted on newly created table Test1 it got pasted.
Regards,
a_k93
July 3, 2009 at 7:21 am
I really like this a lot, Oleg!
And Florian, a pity your script went largely unnoticed - you should have submitted it as an article!
Real shame about the 4000 char limitation. However I guess given we can sp_helptext both functions, we could simply script our own corrected functions to get round this limitation.
Thanks again,
David.
July 3, 2009 at 10:18 am
a_k93 (7/3/2009)
Hi there,Thnx for your reply. I tried your work around but it seems to be not working, may be I am making some mistake, but let me tell you what I have tried.
1. Created a table called Test1
2. Added 2 columns a) col1 float & b) col2 numeric(38,17)
3. Inserted 2 records.
12345678998765432123456789.98765432100000000
123456789.98765433123456789987.65432100000000000
4. Generated the insert statement by the SP you have provided
insert into dbo.test1 ([col1], [col2]) values (0x4345ee2a319dbfbc, 0x261100010091ba4e78fe7c994c360a00);
insert into dbo.test1 ([col1], [col2]) values (0x419d6f3457f35ba8, 0x261100010068c688f3054290371be427);
5. Created a table called Test1 in another Db.
6. Added 2 columns a)col1 numeric(38,10) b)col2 numeric(38,17)
7. Tried to execute the insert statement, it raised an error
Arithmetic overflow error converting varbinary to data type numeric.
8. At the same time, if I copied these 2 records by means of CTRL+C and pasted on newly created table Test1 it got pasted.
Regards,
a_k93
There are a couple of problems here. In my previous reply to you I quoted information from the BOL, but let me repeat it again here, because it is rather important. According to BOL float and real are
Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
When float is declared without number of bits that are used to store the mantissa specified then that number defaults to 53.
While the range of values of float is huge (- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308), the data is approximate part of the BOL quote is the key. This is because the precision of the float when n is omitted (defaults to 53) is 15 digits, that is all. This means that in your example, you cannot really store the number 12345678998765432 as float without losing precision. For example, if you try to select * from your table you will notice that the actual value of col1 of the first record comes out as 1.23456789987654E+16 loosing last 2 digits, namely 32.
Internally, float is stored differently from numeric (decimal) data type. Here is example:
declare @f float, @n numeric(15, 6);
set @f = 123456789.987654;
set @n = 123456789.987654;
-- float is stored differently, it occupies 8 bytes while numeric - 9
select
cast(@f as varbinary(max)) f, datalength(@f) f_length,
cast(@n as varbinary(max)) n, datalength(@n) n_length;
Results:
f f_length n n_length
---------------------------------------------------------------------
0x419D6F3457F35B92 8 0x0F06000146C11C8648 9
The second problem is the small bug in my code which handles the text but does not handle the float. Float, text and ntext are the data types which do not have explicit conversion from varbinary(max). This means that case part of my procedure should be modifed like this:
The case statement currently reading
case data_type
when 'text' then 'cast([' + column_name + '] as varchar(max))'
else '[' + column_name + ']'
end + ' as varbinary(max))), ''null'')'
should become
case data_type
when 'float' then 'cast([' + column_name + '] as decimal(38, 10))'
when 'ntext' then 'cast([' + column_name + '] as nvarchar(max))'
when 'text' then 'cast([' + column_name + '] as varchar(max))'
else '[' + column_name + ']'
end + ' as varbinary(max))), ''null'')'
This will solve your problem (except those 2 lost digits but nobody can help with that, float data type is indeed approximate). Please replace the case part of the procedure as described before and inserts for your table will be generated correctly. This will teach me a good lesson to test the solution more thoroughly before attempting to publish article on this site.
Oleg
July 7, 2009 at 7:13 am
Oleg,
Well spotted, thanks for the advice. Running in "Results to grid" mode worked a treat. Fantastic.
Matt
July 8, 2009 at 8:15 am
good work. You gave me another tool for my arsenal and a fresh perspective on handling raw data versus "human" data.
I would say as other mentioned that you should update usp_generate_inserts to support 2 parameters one for table one for schema. That way there is no confusion.
Thanks again,
T
August 4, 2009 at 6:59 am
The script is very good, but how to convert the data again to english ?
August 4, 2009 at 9:53 am
sathesg (8/4/2009)
The script is very good, but how to convert the data again to english ?
You don't have to. It is already stored in correct format. Selecting inserted records from the table will display data correctly.
Oleg
August 26, 2009 at 12:02 am
i have written some code to help developers spend less time on scripting basic statements.
the first link is a demonstration of this:
http://www.sqlservercentral.com/scripts/AutoGenerate/65042/
the second one is the actual code that uses metadata to easily script select, insert and update statements:
http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/
the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:
http://www.sqlservercentral.com/scripts/Stored+Procedures/65869/
August 26, 2009 at 12:06 am
i have written some code to help developers spend less time on scripting basic statements.
the first link is a demonstration of this:
the second one is the actual code that uses metadata to easily script select, insert and update statements:
the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:
August 26, 2009 at 12:16 am
i have written some code to help developers spend less time on scripting basic statements.
the first link is a demonstration of this:
the second one is the actual code that uses metadata to easily script select, insert and update statements:
the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:
August 26, 2009 at 12:44 am
i have written some code to help developers spend less time on scripting basic statements.
the first link is a demonstration of this:
the second one is the actual code that uses metadata to easily script select, insert and update statements:
the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:
January 7, 2010 at 12:06 pm
Not only did I appreciate this script, I *USE* it. I made a slight change to shrink the output a tad (shown below). The negative to my approach is that I must manually delete the very last "union all" piece, but I can live with that. Thanks Boss!
set @script = 'select ''select '' + ' + substring(@values, 11, len(@values)) + ' + '') union all '' from ' + @table;
if ( @is_identity = 1 ) begin
print ('set identity_insert ' + @table + ' on');
end
print 'insert into ' + @table + ' (' + substring(@columns, 3, len(@columns)) + ')'
exec sp_executesql @script;
if ( @is_identity = 1 ) begin
print ('set identity_insert ' + @table + ' off');
end
January 8, 2010 at 10:20 am
And I'll likely page the resulting statements so I can keep inserts to batches of 1,000 at a time -- it works faster this way.
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply