SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Error report doesn't like DB's that starts with #
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

EHR-Administrator
Starting Member

1 Posts

Posted - 06/17/2013 :  14:19:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 06/17/2013 :  14:34:06  Show Profile  Reply with Quote
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).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000