nvarchar(max) Discussion

[Former Member]

Good Day

Experts:

I read all the posts on the nvarchar(max) difficulties and have a question to open up to the forum.

Hopefully someone has worked with the workaround with success. I think Owen mentioned in one post to set the DbServerType = dst_MSSQL instead of dst_MSSQL2005 before adddind UDF's.

I have a 2005 db that has, of course, been determined that when using my Auto-add function for UD tables produces the nvarchar(max) fields. So, right before I run this routine I have added the following line:

g_B1Connection.Company.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL

I run the routine again(after deleting the previously created tables) and still get the nvarchar(max).

Is there anything else I need to set with the above line to be able to run my routine without getting the nvarchar(max)?

Thanks,

Ed

[former_member201110]

Hi Ed,

Is your program an addon or a standalone Windows app (that uses the DI API)? What version of SBO are you using?

For SBO 2005A SP1 running on SQL 2005:

You can create UDFs without using the nvarchar(max) datatype by setting the DbServerType property to SQL2000 prior to making a connection via the DI API. If you are using an addon (which will pick up the connection settings from the SBO client), all you need to do is set the database type in the Choose Company login screen to SQL 2000. If you are using a standalone program that uses the DI API then you need to set your DbServerType property before making the company connection.

For SBO 2007A running on SQL 2005:

Unfortunately, the SBO client and DI API will automatically reset itself to SQL 2005 settings if you try and change it to SQL 2000. Therefore there is no workaround, that I know of, for creating UDFs without nvarchar(max) in this version.

Hopefully, SAP will correct this issue and allow us to choose between the max and non-max settings. Nvarchar(max) is useful because it gets past the 8000K limitation on a table rowsize but it's not always appropriate.

Kind Regards,

Owen

P.S. Perhaps if enough of us put a request on the collaboration forum then we could get this issue a higher priority for a development change...(?)

[Former Member]

Good Day

Owen:

In my Help about I have the following:

2005 A (6.80.320) SP:01 PL:46

Our program is an AddOn.

When I make a new db on the SAP opening screen, what determines which of the MSSQL/MSSQL_2005 settings to use?

So, I make a new db using MSSQL_2005 setting. Then I open up the company and start the AddOn. I have an Auto-add tables and queries function. To avoid the NVARCHAR(max) issue, if I understand correctly, there is really no way programatically to set the serverType bacl to MSSQL then revert back to MSSQL_2005 when completed? I would be interested in that for UDF additions as well. But if there is no way to do this in the code, I can connect to the company and start the AddOn them go to Administration/ChooseCompany and change the setting there as long as I select the company I have just logged onto?

As always, thanks for the time to clarify.

Have a good day,

Ed

[former_member201110]

Hi Ed,

I did a little testing and I found that later patches of 2005A SP1 work differently from earlier patches. In the earlier patches you could log in to the SBO client using the SQL 2000 setting and then you could create UDFs (either manually or via code) and you wouldn't get the nvarchar(max) datatype. However, I tried on a patch 22 system and it kept reverting to SQL 2005 when I changed the setting in the login screen. However, I was able to create the required UDFs by using an independent DI API connection via code.

Therefore, if I'm correct, your choices would be:

1) Amend your current function so that it opens a separate DI API connection (you can have multiple DI API connections open in the same addon, provided you have sufficient licenses). If you set the DbServerType property of this new connection to SQL 2000 prior to opening it, and use this connection to create your UDFs then you'll get the correct UDF settings. The only complication with this solution is that you'll need to know the connection properties (eg database user and password, license server etc). Some of this you can get from the DI API connection that is already open in your addon but you'll probably need to prompt the user for users and passwords before you can open the new connection.

Here's a basic example:

// Create a new company object
SAPbobsCOM.Company sboCompany2 = new SAPbobsCOM.Company();
// Copy connection properties from the existing company connection
sboCompany2.CompanyDB = _sboCompany.CompanyDB;
sboCompany2.Server = _sboCompany.Server;
sboCompany2.LicenseServer = _sboCompany.LicenseServer;
// Set the database type to SQL 2000
sboCompany2.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL;
// *********************************
// Here's where you would prompt for user names and passwords
// But I'm going to cheat and hardcode them
// *********************************
sboCompany2.DbUserName = "sa";
sboCompany2.DbPassword = "mysapassword";
sboCompany2.UserName = "manager";
sboCompany2.Password = "mymanagerpassword";
// Connect the new connection
iRetVal = sboCompany2.Connect();

// Create my UDF
SAPbobsCOM.UserFieldsMD sboUserFieldsMD = (SAPbobsCOM.UserFieldsMD)sboCompany2.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields);
try
{
    int iErr;
    string sErrMsg;
    
    sboUserFieldsMD.TableName = "OCRD";
    sboUserFieldsMD.Name = "MyField";
    sboUserFieldsMD.Description = "My Field";
    sboUserFieldsMD.Type = SAPbobsCOM.BoFieldTypes.db_Alpha;
    sboUserFieldsMD.EditSize = 10;

    // Add the field to the table
    iRetVal = sboUserFieldsMD.Add();

    sboCompany2.GetLastError(out iErr, out sErrMsg);
    if (iErr != 0)
    {
        _sboApp.SetStatusBarMessage("Error: " + iErr.ToString() + " - " + sErrMsg, SAPbouiCOM.BoMessageTime.bmt_Long, true);
        iRetVal = 1;
    }
}
catch (Exception ex)
{
    _sboApp.SetStatusBarMessage("Error: " + ex.Message, SAPbouiCOM.BoMessageTime.bmt_Long, true);
}
finally
{
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sboUserFieldsMD);
    sboUserFieldsMD = null;
    GC.Collect();
}
sboCompany2.Disconnect();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sboCompany2);
sboCompany2 = null;
GC.Collect();

In the above example _sboCompany is my existing DI API company object that was connected as part of the single sign-on when the addon was started. I then create a new company object, set it's properties and open it. I use this connection to create the UDF and then disconnect it.

2) Take your function out of the addon and instead provide a little .NET app that opens a DI API connection (using DbServerType = SQL 2000) and creates the UDFs. Again, you'll need to prompt the user for the connection settings. Essentially this is the same as above but you are splitting the UDF creation functionality out of your addon and providing a separate tool to do this part.

Kind Regards,

Owen

[Former Member]

Thanks Owen...

Your response on the topic should be a "sticky" to make sure it is at the top of the list.

Very good, I appreciate the insight.

Ed