Error report doesn't like DB's that starts with #

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

Error report doesn't like DB's that starts with #

Author

Topic

EHR-Administrator
Starting Member

1 Post

Posted - 2013-06-17 : 14:19:35

Hi all,

Great community you have here, been lurking and researching for sometime when I have issues. I'm a support manager and system administrator for a Electronic Heath Record vendor down in Tampa. We host our clients systems on a Virtual environment on SQL2012. I have an error log report that I run as job nightly and recently added some blank databases for templates to one of my servers. I named the databases based on the software's release year and the specialty.

Ie:

2011PC (Primary Care)
2011Cardio
ect.

Anyways when the report runs it errors out on those names. Since these are blank template databases, I could detach and rename them but i would rather learn a little something and adjust the report script. I have a feeling it is something very simple. Thanks in advance if anyone can figure it out!

Here is the Error:
Executed as user: DAS\Administrator. Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011'. [SQLSTATE 42000] (Error 102) Incorrect syntax near '2011

Here is the report script:

USE [TimeTracker]
GO
/****** Object: StoredProcedure [dbo].[spErrorLogReport] Script Date: 6/17/2013 1:59:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spErrorLogReport] @date DATETIME = NULL AS
DECLARE @SendTo varchar(100)
, @Subject varchar(100)
, @Message varchar(8000)

IF @date IS NULL
SET @date = GETDATE()

SELECT @SendTo =
--@SendTo = -- for Testing
, @Subject = 'SQL4 Error Log Report - ' + CONVERT(VARCHAR, @date, 101)
, @Message = ''

CREATE TABLE #ErrorReport ( DatabaseName VARCHAR(255),
ErrorCount INT
)

DECLARE @databaseName VARCHAR(255)
DECLARE @cmd NVARCHAR(MAX)
DECLARE databaseCursor CURSOR FOR
SELECT Name FROM sys.databases

OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @databaseName
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @cmd = 'IF EXISTS(SELECT * FROM ' +
@databaseName +
'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =''ErrorReport'') ' +
'INSERT #ErrorReport(DatabaseName, ErrorCount) ' +
'SELECT ''' + @databaseName + ''',' +
'(SELECT COUNT(*) FROM ' + @databaseName + '..ErrorReport WHERE CONVERT(DATE, LastModifiedDate) = CONVERT(DATE, ''' + cast(@date as VARCHAR) + '''))'
--select @cmd
exec sp_executesql @cmd

FETCH NEXT FROM databaseCursor INTO @databaseName
END
CLOSE databaseCursor
DEALLOCATE databaseCursor

SELECT @Message = @Message + 'Total Errors ' + CAST((SELECT SUM(ErrorCount) FROM #ErrorReport) AS VARCHAR)
+ ' over ' + CAST((SELECT COUNT(DatabaseName) FROM #ErrorReport) AS VARCHAR) + ' databases' + CHAR(13)
DECLARE @errorCount INT
DECLARE messageCursor CURSOR FOR SELECT DatabaseName, ErrorCount FROM #ErrorReport ORDER BY DatabaseName
OPEN messageCursor
FETCH NEXT FROM messageCursor INTO @databaseName, @errorCount
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @Message = @Message + CONVERT(CHAR(10),@databaseName) + dbo.fnPadLeft(' ', 10,CONVERT(VARCHAR(12),@errorCount)) + CHAR(13)
FETCH NEXT FROM messageCursor INTO @databaseName, @errorCount
END
CLOSE messageCursor
DEALLOCATE messageCursor

--SELECT * FROM #ErrorReport ORDER BY DatabaseName
DROP TABLE #ErrorReport

--select @Message

EXEC msdb.dbo.sp_send_dbmail @recipients = @SendTo,
@subject = @Subject,
@body = @Message

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-17 : 14:34:06

Escape the names with square brackets - for example, instead of 2011PC, use [2011PC]. Whether you set the variable with the square brackets included, or whether you add that into the dynamic query as you are constructing the dynamic query does not matter - do whichever is more convenient (probably the former).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources