Insert database name into a table

Question

Post reply

Insert database name into a table

richardmgreen1

SSChampion

Points: 10303
More actions
November 11, 2015 at 5:47 am

#149138

Hi all
I'm trying to set up a maintenance plan with logging on several servers (there's none at all at the moment).
The script I've got at the minute is this:-
set nocount on
-- Update stats on all databases to start with
exec sp_MSforeachdb '
use [?]
if db_name() not in (''master'',''msdb'',''model'',''tempdb'')
declare
@start datetime
set @start = getdate()
begin
-- Start of logging for this section
INSERT INTO [DBAUtilities].[dbo].[MaintenanceLog]
([Database]
,[Action]
,[StartDateTime])
select
?
,''Updating statistics''
,@start
-- Do the actual updates
exec sp_updatestats
-- Complete the logging for this section
update [DBAUtilities].[dbo].[MaintenanceLog]
set
[EndDateTime]=getdate()
where
[Database]=db_name()
and [StartDateTime]=@start
end
'
The table I'm trying to insert into looks like this:-
USE [DBAUtilities]
GO
/****** Object: Table [dbo].[MaintenanceLog] Script Date: 11/11/2015 12:41:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MaintenanceLog](
[Database] [varchar](50) NULL,
[Action] [varchar](max) NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
[TimeTaken] AS (datediff(second,[StartDateTime],[EndDateTime])) PERSISTED
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
These are my current error messages:-
Msg 1934, Level 16, State 1, Line 12
INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
There's one for each database
I've tried using QUOTENAME and casting the database name as a varchar to fit with the table but it still gives me similar errors.
Anyone any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try removing this column:

[TimeTaken] AS (datediff(second,[StartDateTime],[EndDateTime])) PERSISTED

It's complaining about a computed column, so unless you really, really need that in the table and not calculated in a query, removing it should fix things.

If you really need that column, try setting QUOTED_IDENTIFIER on within your dynamic SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

Another thing I notices is that you have your database name defined as a varchar(50). The database name is actually a sysname data type. The sysname is an nvarchar(128) not null.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

richardmgreen1 SSChampion Points: 10303 More actions · Answer 3

Gail - Thanks for that, it never occurred to me that the computed column would cause such an issue. I've removed the column and everything's working (so far at least)

Ed - Thanks for the heads up, I've altered the table definition to accept 128 characters just in case.