SQL Server Performance Forum – Threads Archive

Column names are not permitted

I have a database ‘admin’ in our test server and production server.When I execute the a script on my test server its running fine..but when I run it on production servr it output the following error
Server: Msg 128, Level 15, State 1, Procedure usp_truncatelog, Line 22
The name ‘ ‘ is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Server: Msg 170, Level 15, State 1, Procedure usp_truncatelog, Line 23
Line 23: Incorrect syntax near ‘BACKUP LOG ‘.
here is the script which executed on test server without any error USE ADMIN
GO
CREATE PROCEDURE usp_truncatelog
AS
Set quoted_identifier off
DECLARE @dbn varchar(30)
DECLARE @dbn_header varchar(80)
DECLARE dbns_cursor CURSOR FOR SELECT name FROM master..sysdatabases
WHERE dbid > 6 OPEN dbns_cursor FETCH NEXT FROM dbns_cursor INTO @dbn WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM dbns_cursor INTO @dbn
CONTINUE
END
SELECT @dbn_header = "Database " + RTRIM(UPPER(@dbn))
PRINT " "
PRINT @dbn_header
EXEC("BACKUP LOG " + @dbn + " WITH TRUNCATE_ONLY")
EXEC("DBCC SHRINKDATABASE (" + @dbn + ",TRUNCATEONLY)")
FETCH NEXT FROM dbns_cursor INTO @dbn
END DEALLOCATE dbns_cursor

I suppose you have differents configurations with ‘ and ". Check it in both servers. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

.. such as sort order, collation settings and using quotes. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>