All Alpha User Fields going to SQL 2005 as nvarchar(max)

[Former Member]

Good Day

Experts:

I just noticed that in my SBO 2005 SP01 working with MS SQL 2005, that no matter how long in the UDF tool I create an alphanumeric field for (ie. 15, 20, 30), in SQL the column format is nvarchar(max) instead of char(15), char(20), char(30).

This is causing me major trouble in Crystal. I can't group by a nvarchar(max) field, nor can I link these to any other table.field.

Has anyone encountered this? Is there something that can be changed or are we doing something wrong?

I appreciate any insight you may have...

Thanks,

EJD

[Former Member]

For development I'm using SQLExpress 2005. I do not seem to able able to connect as 'SQL2000' to get around this problem.

[former_member201110]

Hi Ed,

SAP made the decision that alphanumeric fields will create a max column in SQL 2005. This was done to ease the problem of some sites running out of space on a SQL table (SQL 2000 had a 8K bytes per row limit).

The workaround is to connect to SBO via the SBO client (or via the DI API), setting the server type to SQL 2000. You can then create your field as usual and then log out. After this you can log in using the normal SQL 2005 connection.

Kind Regards,

Owen

[former_member313593]

Any suggestions as to what to do now that we've added some UDF's on marketing documents (rows and headers) and populated them with data? I don't think we can go into SQL and chage them to char(x), can we?

Thanks

[former_member201110]

Hi Scott,

Unfortunately, you can't change the SQL table directly. I think you'll need to export the data, drop the old UDFs, create new ones with the correct datatype and size and then reload the data (via DTW or the DI API and Excel VBA are great for writing a little utility to upload the data). The only real problem is on marketing document rows. In some cases, when a row has been closed, you may find it is no longer possible to upload data to a UDF on that row.

Kind Regards,

Owen

[Former Member]

Owen:

I think the SAP Patchlevel upgrades recreate the database, so if you're not careful, you will end up with nvarchar(max) fields again after patchlevel upgrade.

Not a good thing.

Only workaround is to be careful and connect as SQL2000 to the server when upgrading.