How to Store (and Retrieve) Non-English Characters (e.g.What is the RetainSameConnection Property of OLEDB Connection in SSIS?.Example of SSIS Foreach Loop Item Enumerator and Dynamic OLEDB Connection.SQL Server object names can be maximum 128 characters long so using 130 characters in scripts will handle the extra two characters. I suggest that you use 130 as the width for the variables or columns that will store an object name.
And this is not the first time! The Solution In my shop, we do have some long index names and I have had to debug some scripts for this issue. 447990 Assume an indexed view projects columns from the base and non-base tables, and assume a column from the non-base table has the same order id and type as a column of the base table. The two extra characters will break the variable assignment or row insert statements with truncation error – String or binary data would be truncated. A user cannot change their password in SQL Server 2005 by using SQL Server Management Studio (SSMS) of SQL Server 2008. So for really long object names that are 128 characters in length, the value would be 128 + 2 = 130 characters. The brackets will add two more characters to the value. Set = quotename(object_name(object_id)) The Issue
This handles those cases where there are special characters in the name or the name is a reserved keyword.Įxample – set = quotename(db_name(db_id())) SQL Server object names can be at most 128 characters long, so common wisdom is to declare the holding variable or column as SYSNAME data type or one of the alphanumeric datatype with a width of 128 characters.Īnother technically correct thing to do is to use the QUOTENAME function to wrap the object name in brackets. database name, table name, index name etc. Some index maintenance scripts or dynamic SQL scripts use SQL object names as values for variables or columns i.e.