SQL Transact-SQL not working for database name configuration1

Question

I've created a SQL agent job in our SQL Server 2008 that executes the following code:

BEGIN
SET NOCOUNT ON;
declare @database nchar(20);

DECLARE Database_Cursor CURSOR FOR
SELECT [name]
FROM master.dbo.sysdatabases
ORDER BY [name]

OPEN Database_Cursor;
FETCH NEXT FROM Database_Cursor INTO @database;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC    [dbo].[sp_BackupDatabase]
        @databaseName = @database

      FETCH NEXT FROM Database_Cursor INTO @database;
END;
CLOSE Database_Cursor;
DEALLOCATE Database_Cursor;
END
GO

Basically I retrieve a list of databases and execute a backup script for each database. This script works for most database, but some databases return an error:

Msg 911, Level 16, State 11, Line 1
Database 'appName_Configuration' does not exist. Make sure that the name is entered correctly.

This is correct, this database does not exist. We have several database named like this:

appName_Configuration1
appName_Configuration2
...

For some reason, the script doesn't pass the name including the number to the backup script. If I replace the @database variable with a static name (appName_Configuration1) it works just fine.

Also worth mentioning: there are a few other databases that have a number at the end, which work fine:

appName_Microsoft1
appName_Microsoft2

I suspect that the word "Configuration" may have something to do with it, but renaming the database is not an option for now. Can anyone help me finding a solution so that the name is passed to the stored procedure correctly ?

Kind regards, Mathew

The answers given are correct, that you need to make your variable larger. For thoroughness though, you ought to use `nvarchar(128)`, or its synonym `sysname`, since that's the definition used in [`sysdatabases`](http://msdn.microsoft.com/en-us/library/aa260406(v=sql.80).aspx) (Of course, given you're on 2008, you ought to update to using `sys.databases` instead, also) — Damien_The_Unbeliever, May 30 '12 at 07:56
Make also sure that the `sp_BackupDatabase`'s argument is the same type as the variable (at least, not shorter). And maybe you should consider renaming the SP, as this prefix, `sp_` is uniformly used by system SPs and using the same naming convention for your own SPs may be confusing. — Andriy M, May 30 '12 at 11:24

score 3 · Accepted Answer · answered May 30 '12 at 07:49

3

Increase the size of @database variable as per your database name maximum length can be... e.g.

declare @database nchar(100);

answered May 30 '12 at 07:49

Asif

2,657
19
25

The maximum identifier length in SQL Server is [128](http://msdn.microsoft.com/en-us/library/ms143432.aspx) – Damien_The_Unbeliever May 30 '12 at 08:01
Yes you are right maximum length of database name is 128. but if you don't specify the log file name, it automatically append "_log" to your database name. That makes maximum limit 124. – Asif May 30 '12 at 09:09

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

You declared @database as nchar(20). Your names are longer. Use nvarchar(50), for example.

edited May 23 '17 at 12:27

Community

1
1

answered May 30 '12 at 07:34

David Brabant

41,623
16
83
111

SQL Transact-SQL not working for database name configuration1

2 Answers2