I am trying to do some UI validation on a database name. I was told from different places that the SQL Server database name is stored as sysname inside SQL Server. I also verified that by checking the sys.Databases.
So, I make my textbox in the UI to have maxlength 128 characters long. This is the max length of sysname
type.
However, I found that in my SQL Server 2005 Express edition, if I enter a database name with 128 characters long, it always complains
Could not create default log file because the name was too long
To make sure I can successfully create the database, I found that I need to set the max limit to 124 characters.
My question now is whether 124 characters limit is true for all versions of SQL Server on all different Windows OS?
Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.
SQL maximum column name length limitation is 128 characters. If we create more than 128 characters, it shows an error.
Microsoft SQL Server 2008 (and above) can store up to 8000 characters as the maximum length of the string using varchar data type. SQL varchar usually holds 1 byte per character and 2 more bytes for the length information.
The log file name does not need to include the database name.
This works fine for me for example.
CREATE DATABASE [AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA] ON PRIMARY
(
NAME = N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\A128.mdf')
LOG ON
(
NAME = N'A128_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\A128_log.LDF')
Are you not running into the problem of the file names for the data and log files. The underlying operating system (Windows) places restrictions on it and therefore the database cannot be created. The database name may be 128 characters long and works given that the file names used are not being to long (when using the SQL Server Management Studio it automatically generates file names based on the database names, and they therefore end up being long).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With