A few weeks back I blogged how to do CONTAINSTABLE() and FREETEXTTABLE() using SQL variables. This weekend, I was working on creating a backup script for a client and ran into a similar problem where the SQL Server 2005 documentation says you can use a variable but it didn't actually work because of a crucial missing detail.
For this script:
declare @databaseName nvarchar
select @databaseName = 'DbProTesting'
backup database @databaseName
to disk = 'c:\temp\DbProTesting.bak'
I got the following weird SQL error:
Msg 911, Level 16, State 11, Line 5
Could not locate entry in sysdatabases for database 'D'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 5
BACKUP DATABASE is terminating abnormally.
Of course there's no database named "D" in sysdatabases but why does SQL Server 2005 think that that's what the database name should be?!
Solution: If you change the variable data type from "nvarchar" to "nvarchar(1024)" it works fine.
Here's the working script:
declare @databaseName nvarchar(1024)
select @databaseName = 'DbProTesting'
backup database @databaseName
to disk = 'c:\temp\DbProTesting.bak'
-Ben
posted @ Monday, August 13, 2007 9:37 AM