Thursday, March 29, 2012

Error from Maintenance plan

Hi there,
I get an error from the maintenance plan, when it every weekend reorganizes
the data and index tables:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
I wonder if it could be because i have an calculated field in the table,
[FullName] AS ([LastName] + ', ' + [FirstName]) ?
The table is create as follows:
CREATE TABLE [dbo].[Assigner] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (25) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL ,
[FullName] AS ([LastName] + ', ' + [FirstName]) ,
[CustomerGroup_id] [int] NOT NULL ,
[Deactivated] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Assigner] WITH NOCHECK ADD
CONSTRAINT [PK_Assigner] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 70 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Assigner] ADD
CONSTRAINT [FK_Assigner_CustomerGroup] FOREIGN KEY
(
[CustomerGroup_id]
) REFERENCES [dbo].[CustomerGroup] (
[id]
)
GOJohn
This SELECT stetement will produce a syntax error
You may want to change it as SELECT ([LastName] + ', ' + [FirstName]) AS
[FullName] FROM .....
"John Boghossian" <john.boghossian@.investorab.com> wrote in message
news:eS2iwyljEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Hi there,
> I get an error from the maintenance plan, when it every weekend
reorganizes
> the data and index tables:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> I wonder if it could be because i have an calculated field in the table,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ?
>
> The table is create as follows:
> CREATE TABLE [dbo].[Assigner] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (25) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
,
> [LastName] [varchar] (30) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ,
> [CustomerGroup_id] [int] NOT NULL ,
> [Deactivated] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] WITH NOCHECK ADD
> CONSTRAINT [PK_Assigner] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 70 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] ADD
> CONSTRAINT [FK_Assigner_CustomerGroup] FOREIGN KEY
> (
> [CustomerGroup_id]
> ) REFERENCES [dbo].[CustomerGroup] (
> [id]
> )
> GO
>|||John,
You most probably have either an index on that computed column, or SQL Server has created statistics
on the column. In those situations, whenever you need to rebuild such an index or statistics, the
connection need special SET options defined. For some reason, Maint wizard doesn't set these. You
could try to drop the statistics (DROP STATISTICS), chances are that they will re-added. You could
tell SQL Server to not create statistics, but the issue might pop up somewhere else. To be safe,
perform the DBCC DBREINDEX commands from your own SQL Server Agent job and make sure to set the SET
commands as they need to be set. I'm sure there's a KB on this topic if you search for appropriate
keywords...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Boghossian" <john.boghossian@.investorab.com> wrote in message
news:eS2iwyljEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Hi there,
> I get an error from the maintenance plan, when it every weekend reorganizes
> the data and index tables:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> I wonder if it could be because i have an calculated field in the table,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ?
>
> The table is create as follows:
> CREATE TABLE [dbo].[Assigner] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (25) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL ,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ,
> [CustomerGroup_id] [int] NOT NULL ,
> [Deactivated] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] WITH NOCHECK ADD
> CONSTRAINT [PK_Assigner] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 70 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] ADD
> CONSTRAINT [FK_Assigner_CustomerGroup] FOREIGN KEY
> (
> [CustomerGroup_id]
> ) REFERENCES [dbo].[CustomerGroup] (
> [id]
> )
> GO
>|||John,
Go with TIbors suggestions... A computed column which is index, etc , then
those settings matter during maint plan stuff... I would suspect you are
getting the error on index rebuild..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"John Boghossian" <john.boghossian@.investorab.com> wrote in message
news:eS2iwyljEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Hi there,
> I get an error from the maintenance plan, when it every weekend
reorganizes
> the data and index tables:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> I wonder if it could be because i have an calculated field in the table,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ?
>
> The table is create as follows:
> CREATE TABLE [dbo].[Assigner] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (25) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
,
> [LastName] [varchar] (30) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ,
> [CustomerGroup_id] [int] NOT NULL ,
> [Deactivated] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] WITH NOCHECK ADD
> CONSTRAINT [PK_Assigner] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 70 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] ADD
> CONSTRAINT [FK_Assigner_CustomerGroup] FOREIGN KEY
> (
> [CustomerGroup_id]
> ) REFERENCES [dbo].[CustomerGroup] (
> [id]
> )
> GO
>|||> I wonder if it could be because i have an calculated field in the table,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ?
Why would you have this calculated column in the table? I would be tempted
to drop it and run this concatenation in the SELECT proc(s) or even a view.
--
http://www.aspfaq.com/
(Reverse address to reply.)

No comments:

Post a Comment