How can I define a parameter as nvarchar(max) in sql data tools

Question

The variables I defined with MaxByteSize = -1 are scripted as nvarchar(4000) by sql server data tools and so is the other SqlString variables I used in scalar functions etc. In the older clr projects I was creating my own script to deploy these same clr functions therefore I was declaring them as nvarchar(max) but I could not figure out how to establish the same with a dacpac project against a sql 2012 system.

Any advise will be appreciated.

Thank you

---

Gokhan Varol

Answer 1

Hi Gokhan,

Could you please elaborate the issue you're experiencing with detailed repro steps
and sample code?

Thanks,

Yang

---

YangG(MSFT), SDE

Answer 2

Gokhan,

If you are still experiencing this problem, would it be possible to post a more elaborate description of what and how you are trying to do? Based on the information we have now we cannot determine the problem you are running in to.

Thanks,

---

-GertD @ www.sqlproj.com

Answer 3

Gert

Stopping ddl generation from visual studio project and declaring sql functions manually fixed the problem

Thank you

---

Gokhan Varol

Answer 4

Thanks for the followup, seems like you worked around the problem. I would still like to understand what the actual problem was, so we can determine how to address it, would it be possible to share some more insight in what you where trying to do? The statement about MaxByteSize = -1 suggests that you are using the SqlUserDefinedTypeAttribute.MaxByteSize property in the SQL-CLR functionality http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqluserdefinedtypeattribute.maxbytesize.aspx

Is the issue when you define the SQL-CLR function as MaxByteSize the DDL generated to deploy the CLR function does not reflect the fact it is NVARCHAR(MAX) but instead generate NVARCHAR(4000)?

This would be a bug that we need to fix if that is the case!

Thanks for you help,

---

-GertD @ www.sqlproj.com

Answer 5

What I am using is not a clr scalar function that returns a single varchar(max) out. I am creating a clr function that takes many input parameters, they can be different sizes varying from nvarchar(30) to nvarchar(4000) to nvarchar(max) and you cannot do this with auto ddl creation, you also can have a default schema for objects but you cannot have different schema names and different object names (different than the assembly function names)  using the default ddl generation, that's why the workaround does the job for me. It took me some time to find the workaround during migration to sql 2012 project from sql 2008 project.

Basically the variable size data types in sql server like numeric(10,2) vs numeric(20,6) or float() or nchar() nvarchar() etc have their default mappings if you let the project generate the functions definitions for you, they could work in most cases but did not work in my case.

---

Gokhan Varol