cancel
Showing results for 
Search instead for 
Did you mean: 

B1 create User Defined Field as nvarchar(max) in SQL 2005 database

Former Member
0 Kudos

Hi everyone,

I just created a UDF in B1 as alphanumeric(12), and noticed that B1 created a field in SQL 2005 as nvarchar(max) instead of nvarchar(12). I tested with both 2005A SP01 patch 14 and patch 20. It always creates UDF as nvarchar(max). Is this a bug? It will cause db file use more disk space and also reduce the query performance. Also, B1 won't create the index on the UDF even if you select the option.

Thanks for help,

David

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member201110
Active Contributor
0 Kudos

Hi,

This is not happening for me on patch 19. I can create a UDF as alphanumeric(12) and SQL Server 2005 will have a nvarchar(12) field.

What SP of SQL Server are you on?

Kind Regards,

Owen

Former Member
0 Kudos

We tested on PL 14 and PL 20 with SQL 2005 server SP1.

David

former_member201110
Active Contributor
0 Kudos

Hi David,

I'm using SQL 2005 SP1 as well. I've tried adding a field to a BP, an item and a marketing document header, all with the same results (ie it correctly creates a nvarchar(12) field in SQL). I'm just creating a standard alphanumeric UDF with a length of 12. Are you setting any other options?

Kind Regards,

Owen

Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Owen,

I'm not using any special option to create the field.

I only set the Title, the Description, the type alphanumeric and the Length.

But I'm testing with B1 2007 version...

My MSSQL2005 version is 9.0.1399.

I have used the SQL Profiler and I can see the line:

ALTER TABLE [@SAP_] ADD [U_dafadf] nvarchar(max)

@SAP_ is my table

U_dafadf is the UDF I want to create

Regards

Trinidad.

Former Member
0 Kudos

The problem is SQL 2005 won't allow index on nvarchar(max) field. I can't create index on UDF either from B1 or on SQL management studio directly.

Former Member
0 Kudos

Hi Owen,

Can you tell us which patch level of B1 2005ASP1 you are using?

Thanks,

David

former_member201110
Active Contributor
0 Kudos

Hi David,

I was running on patch 19 when I wrote my earlier posts. I'm now running patch 20 without any issues. I also have a range of clients who are running SBO 2005A SP1 on various patch levels above 10 (though not all are on SQL 2005) and I've yet to come across this issue.

Did you log this with SAP Support? Have they come up with any suggestions?

Maybe this will work:

1) Start SBO. When you get to the login screen, click on Choose Company.

2) Change the Current Server field from SQL 2005 to SQL 2000. Reenter your server settings if prompted

3) Login as normal and try and create your field.

4) Log out and go back in to change back to SQL 2005

Kind Regards,

Owen

Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

I have logged a message for support and the answer is:

This is not a bug.

Alphanumeric User fields are implemented as nvarchar(max) in MSSQL 2005.

This implementation helps solving the 8k page limitation in MSSQL server

(AkA 8060 limitation)

Please let me know if you think it is logic or not.

Regards

Trinidad.

Former Member
0 Kudos

Hi Trinidad,

Thanks for the infomation from SAP. For me, this makes no sense to create a nvarchar(max) field in SQL database for a nvarchar(1) UDF (Yes/No field). And also, we couldn't create SQL index on nvarchar(max) field.

There is a workaround on this issue. We connected to B1 by select SQl2000 as database type, then the system will create correct db field for the UDF, after that, we switched the db type back to SQL 2005.

Regards,

David

Former Member
0 Kudos

This causes a big bug, that can affect database performance gravely!

Test case:

1. create a user table

2. create a user field, type alphanumeric

3. from the Manage User Fields form, try to create an key (index) on this field

You will get a red error message:

-


[Microsoft][SQL Native Client][SQL Server]Column 'U_test' in table 'dbo.@TEST' is of a type that is invalid for use as a key column in an index. 'User Key Description' (OUKD)

This means that the 'Keys' concept of Business One is unusable under MSSQL 2005. This means a severe performance penalty.

Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Janos,

Please create a message for support, it is very important they know about it.

Thanks

Trinidad.

Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi David,

You are right, I have just tried and even using B1 application it creates the field with size = max in the database. B1 application keeps the correct size but not in the database.

I haven't seen any message for it, please create a message to support explaining the situation and let us know the answer.

Regards

trinidad.