PlanetTech
Posts: 13
|
| Posted: 10/01/2009, 6:04 PM |
|
When setting up the custom insert SP, the IDE sets any datatype of *Char(MAX) to size 0 in the Store Procedure parameter window. This fails when inserting data. After change this size to e.g 4000 the SP works. Is this a bug?
If using MAX size what should the Data Size value be set to in the parameter window?
Am using SQL 2005 back-end
|
 |
 |
ReneS
Posts: 225
|
| Posted: 10/02/2009, 12:06 PM |
|
Hi,
This is from: http://msdn.microsoft.com/en-us/library/ms186939.aspx
nchar and nvarchar (Transact-SQL)
Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
nchar [ ( n ) ]
Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.
nvarchar [ ( n | max ) ]
Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.
Remarks
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
Use nchar when the sizes of the column data entries are probably going to be similar.
Use nvarchar when the sizes of the column data entries are probably going to vary considerably.
sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.
Objects that use nchar or nvarchar are assigned the default collation of the database unless a specific collation is assigned using the COLLATE clause.
SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF does not apply to the nchar or nvarchar data types.
So I guess that 4000 is the maximum, so if you think you need that size, that's the way to go.
Rene
|
|
|
|
PlanetTech
Posts: 13
|
| Posted: 10/03/2009, 5:49 PM |
|
Thanks Rene for that great explanation. Very helpful.
|
|
|
|
|
