Outputs: Database Tables - field lengths?

October 18, 2012 5:56AM

I have imported data to SQL Server from a DataManager database output object. Looking at the data in the database, the fields are 200 characters larger. Is there a way to make the fields size to the maximum length of the data in it? For example, in one table I have a field called [Vendor Name], DataManager has output a field of datatype nvarchar(279) in SQL Server and when I execute this statement select MAX(len([vendor name])) from tblMyTable the result is 79. I have tried this for several columns and it appears the datatype now has a length 200 more than the max data length in that field. Is this correct? Thanks- Andy

chris · October 18, 2012 6:03AM

Hi,

Thanks for the question. At the moment when you create a new Database table, Omniscope will add 200 additional characters to the maximum String length. This is done to allow you to insert data into the table where the maximum String length might be greater than the maximum String length when the table was created.

We could add an option to the database publisher that would allow you to specify the extra "space" required for each field, allowing you to hard-code it to "0". In your example it would then create a nvarchar(79) field instead of nvarchar(279). Would this be an adequate solution?

October 18, 2012 6:31AM

That would be great. At the moment I am using this code to generate a script to alter the table for me:

select 'alter table ' + ob.name + ' alter column [' + sc.name + '] nvarchar(' + CAST((max_length/2)-200 as varchar) + ') NULL'
from sys.columns sc inner join sys.objects ob on sc.object_id = ob.object_id where sc.system_type_id = 231
and ob.name like '%2012q3%' -- enter wildcard here for the table names you want to change
order by ob.name, sc.column_id

thanks

Andy

chris · October 18, 2012 6:39AM

Hi Andy,

We will add this into Omniscope 2.8 in the next few days. I'll post back on here when it's available.

April 9, 2013 5:25AM

Chris

Did you ever make this change?

Thanks

Andy

chris · April 9, 2013 6:49AM

Hi,

Appologies for the delay. We have now added this option. This will be available in tonight's 2.8 release (b771)

Welcome!

Categories

Ideas Parade

Tagged