"Michael Cheng [MSFT]" wrote:

> Hi Tom,
>
> Welcome to use MSDN Managed Newsgroup!
>
> From your descriptions, I undesrstood that your maintenance plan works well
> but report error 1934 suddenly for some unknown reason. If I have
> misunderstood your concern, please feel free to point it out.
>
> Based on my knowledge, when you choose to update statistics in your
> maintenance plan, and you have index on computed column in that database,
> the maintenance plan will fail with the following error:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server
> Driver][SQL Server]UPDATE STATISTICS failed because the following SET
> options have
> incorrect settings: 'QUOTED_IDENTIFIER'
>
> Please perform the steps below and let me know whether it resolve your issue
> 1. In SQL Enterprise Manager (SEM), right-click on Jobs and select New Job 
> 2. Name the job, and specify the job owner
> 3. Click on the Steps tab, and then choose a New step
> 4. Title the step, and then include the following TSQL commands in the
> command section:
>
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> EXEC sp_MSForeachtable "UPDATE STATISTICS ? WITH FULLSCAN"
>
> 5. Click OK to save the steps. 
> 6. Click on the Schedules tab and schedule a appropriate time to run the
> job. 
> 7. Save the job by clicking OK 
> 8. Right-click the job and choose Start Now to test it.
>
> Related KB article on a similar problem:
>
> SET OPTION considerations when running DBCC with indexes on computed columns
> http://support.microsoft.com/kb/301292
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

"Michael Cheng [MSFT]" wrote:

> Hi Tom,
>
> It's great to hear that it works :)
>
> As I have said, the KB Q301292 is only a *similar* one for your reference
> but not exactly the one for your scenario. So I don't believe you need
> rebuild the index as you do not have it.
>
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>
>
>

"Michael Cheng [MSFT]" wrote:

> Hi Tom,
>
> > if there are no indexes on the new computed columns,
> > why would I be getting the error, as per the kb? Perhaps the error occurs
> > even tho the columns are not indexed?
>
> Do you have indexed views instead?
>
> Based on my knowledge, All connections using indexes on computed columns or
> indexed views must have the same settings for these seven options. These
> six SET options must be set to ON:
>
> ANSI_NULLS
> ANSI_PADDING
> ANSI_WARNINGS
> ARITHABORT
> CONCAT_NULL_YIELDS_NULL
> QUOTED_IDENTIFIER
>
> The NUMERIC_ROUNDABORT option must be set to OFF.
>
> There is no way to set this option via the Maintenance plan which uses a
> separate connection and this is by design. This also happens if you create
> a simple SQL Agent job and run Update statistics. This is because, when you
> run a SQL Agent job, by default the SQL Server Agent does not set
> QUOTED_IDENTIFIER and this is required to be ON if you have an index on a
> computed column.
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>

"Michael Cheng [MSFT]" wrote:

> Hi Tom,
>
> Thanks for your kindly suggestions!
>
> I will try to contact the KB writer with internal ways and keep you updated
> about the result, which may need a couple of days :)
>
>
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

"Michael Cheng [MSFT]" wrote:

> Hi Tom,
>
> I would run the script below to see if there is any problem with a
> particular table. This would fail at that particular table and then you
> could at least narrow it down to a particular table. Then look further at
> the schema of that table.
>
> ****************************************************************************
> ******
> USE database_name
> go
> DECLARE tables_cursor CURSOR FOR
> SELECT name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1
> OPEN tables_cursor
> DECLARE @tablename SYSNAME
>
> FETCH NEXT FROM tables_cursor INTO @tablename
> WHILE (@@FETCH_STATUS <> -1)
> BEGIN
>     --select Getdate()
>     PRINT '----Updating Statistics for table ' + @tablename + ' '
>     EXEC ('UPDATE STATISTICS ['+ @tablename + '] WITH FULLSCAN')
>     FETCH NEXT FROM tables_cursor INTO @tablename
> END
> PRINT '----------------All Statistics are Updated----------------'
> CLOSE tables_cursor
> DEALLOCATE tables_cursor
> GO
> ****************************************************************************
> *******
>
> Let me know the result of it and let's find the root cause together :)
>
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>

"TomT" <tomt@newsgroup.nospam> wrote in message
news:0B5AE3E4-A5E0-48DF-90F3-75E33D1E77F5@microsoft.com...
> Michael,
>
> Sorry for the delay in getting back to you, things have been a bit hectic. I
> know exactly which table is causing the issue, since the problem started
> right after I added two calculation columns to it (not indexed, however).
>
> Here's the table:
>
> CREATE TABLE [dbo].[CommisStaffGoals] (
> [StaffID] [int] NOT NULL ,
> [AccelRate] [decimal] (3, 2) NOT NULL ,
> [BaseSalary] [money] NOT NULL ,
> [Draw] [money] NOT NULL ,
> [AdjustmentToDraw] [money] NOT NULL ,
> [PriorYearNegativeBal] [money] NOT NULL ,
> [ActualCommPaid] [money] NOT NULL ,
> [Reason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BonusPool] [money] NOT NULL ,
> [PrdBonusPct] [decimal] (3, 2) NOT NULL ,
> [PrdSalesQuota] [money] NOT NULL ,
> [PrdGPPctQuota] [decimal] (3, 2) NOT NULL ,
> [PrdGPQuota] [money] NOT NULL ,
> [BkSvcQuota] [money] NOT NULL ,
> [INDActualYTDBookedServices] [money] NOT NULL ,
> [INDAddToProductGP$$] [money] NOT NULL ,
> [INDDeductFromProductGP$$] [money] NOT NULL ,
> [INDAddToServicesBooked] [money] NOT NULL ,
> [INDDeductFromServicesBooked] [money] NOT NULL ,
> [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ATBonusPct] [decimal] (5, 2) NOT NULL ,
> [PrdBonusPctNonAdvTech] AS ([PrdBonusPct] * (1 - [ATBonusPct])) ,
> [PrdBonusPctAdvTech] AS ([PrdBonusPct] * [AtBonusPct]) ,
> [ATProdQuota] [money] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[CommisStaffGoals] WITH NOCHECK ADD
> CONSTRAINT [PK_CommisStaffGoals] PRIMARY KEY CLUSTERED
> (
> [StaffID]
> ) WITH  FILLFACTOR = 80 ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[CommisStaffGoals] WITH NOCHECK ADD
> CONSTRAINT [DF_CommisStaffGoals_AccelRate] DEFAULT (0) FOR [AccelRate] ,
> CONSTRAINT [DF_CommisStaffGoals_BaseSalary] DEFAULT (0) FOR [BaseSalary] ,
> CONSTRAINT [DF_CommisStaffGoals_Draw] DEFAULT (0) FOR [Draw] ,
> CONSTRAINT [DF_CommisStaffGoals_AdjustmentToDraw] DEFAULT (0) FOR
> [AdjustmentToDraw] ,
> CONSTRAINT [DF_CommisStaffGoals_PriorYearNegativeBal] DEFAULT (0) FOR
> [PriorYearNegativeBal] ,
> CONSTRAINT [DF_CommisStaffGoals_ActualCommPaid] DEFAULT (0) FOR
> [ActualCommPaid] ,
> CONSTRAINT [DF_CommisStaffGoals_BonusPool] DEFAULT (0) FOR [BonusPool] ,
> CONSTRAINT [DF_CommisStaffGoals_PrdBonusPct] DEFAULT (0) FOR [PrdBonusPct] ,
> CONSTRAINT [DF_CommisStaffGoals_PrdSalesQuota] DEFAULT (0) FOR
> [PrdSalesQuota] ,
> CONSTRAINT [DF_CommisStaffGoals_PrdGPPctQuota] DEFAULT (0) FOR
> [PrdGPPctQuota] ,
> CONSTRAINT [DF_CommisStaffGoals_PrdGPQuota] DEFAULT (0) FOR [PrdGPQuota] ,
> CONSTRAINT [DF_CommisStaffGoals_BkSvcQuota] DEFAULT (0) FOR [BkSvcQuota] ,
> CONSTRAINT [DF_CommisStaffGoals_INDActualYTDBookedServices] DEFAULT (0) FOR
> [INDActualYTDBookedServices] ,
> CONSTRAINT [DF_CommisStaffGoals_INDAddToProductGP$$] DEFAULT (0) FOR
> [INDAddToProductGP$$] ,
> CONSTRAINT [DF_CommisStaffGoals_INDDeductFromProductGP$$] DEFAULT (0) FOR
> [INDDeductFromProductGP$$] ,
> CONSTRAINT [DF_CommisStaffGoals_INDAddToServicesBooked] DEFAULT (0) FOR
> [INDAddToServicesBooked] ,
> CONSTRAINT [DF_CommisStaffGoals_INDDeductFromServicesBooked] DEFAULT (0)
> FOR [INDDeductFromServicesBooked] ,
> CONSTRAINT [DF_CommisStaffGoals_ATBonusPct] DEFAULT (0) FOR [ATBonusPct] ,
> CONSTRAINT [DF_CommisStaffGoals_ATProdQuota] DEFAULT (0) FOR [ATProdQuota]
> GO
>
> GRANT SELECT, UPDATE, INSERT, DELETE ON [dbo].[CommisStaffGoals] TO [public]
> GO
>
> GRANT SELECT, UPDATE, INSERT, DELETE ON [dbo].[CommisStaffGoals] TO
> [CommissionUsers]
> GO
>
> ALTER TABLE [dbo].[CommisStaffGoals] WITH NOCHECK ADD
> CONSTRAINT [FK_CommisStaffGoals_Staff] FOREIGN KEY
> (
> [StaffID]
> ) REFERENCES [dbo].[Staff] (
> [StaffID]
> )
> GO
>
> exec sp_addextendedproperty N'MS_Description', N'keep this for calculation
> purposes', N'user', N'dbo', N'table', N'CommisStaffGoals', N'column',
> N'ATBonusPct'
> GO
>
> exec sp_addextendedproperty N'MS_Description', N'delete when changes are
> done', N'user', N'dbo', N'table', N'CommisStaffGoals', N'column',
> N'ATProdQuota'
> GO
>
>
>
> "Michael Cheng [MSFT]" wrote:
>
>> Hi Tom,
>>
>> I would run the script below to see if there is any problem with a
>> particular table. This would fail at that particular table and then you
>> could at least narrow it down to a particular table. Then look further at
>> the schema of that table.
>>
>> ****************************************************************************
>> ******
>> USE database_name
>> go
>> DECLARE tables_cursor CURSOR FOR
>> SELECT name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1
>> OPEN tables_cursor
>> DECLARE @tablename SYSNAME
>>
>> FETCH NEXT FROM tables_cursor INTO @tablename
>> WHILE (@@FETCH_STATUS <> -1)
>> BEGIN
>> --select Getdate()
>> PRINT '----Updating Statistics for table ' + @tablename + ' '
>> EXEC ('UPDATE STATISTICS ['+ @tablename + '] WITH FULLSCAN')
>> FETCH NEXT FROM tables_cursor INTO @tablename
>> END
>> PRINT '----------------All Statistics are Updated----------------'
>> CLOSE tables_cursor
>> DEALLOCATE tables_cursor
>> GO
>> ****************************************************************************
>> *******
>>
>> Let me know the result of it and let's find the root cause together :)
>>
>>
>> Sincerely yours,
>>
>> Michael Cheng
>> Microsoft Online Partner Support
>>
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================
>>
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>>
>>

"Tibor Karaszi" wrote:

> Perhaps the problem is that you get statistics on your computed columns?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "TomT" <tomt@newsgroup.nospam> wrote in message
> news:0B5AE3E4-A5E0-48DF-90F3-75E33D1E77F5@microsoft.com...
> > Michael,
> >
> > Sorry for the delay in getting back to you, things have been a bit hectic. I
> > know exactly which table is causing the issue, since the problem started
> > right after I added two calculation columns to it (not indexed, however).
> >
> > Here's the table:
> >
> > CREATE TABLE [dbo].[CommisStaffGoals] (
> > [StaffID] [int] NOT NULL ,
> > [AccelRate] [decimal] (3, 2) NOT NULL ,
> > [BaseSalary] [money] NOT NULL ,
> > [Draw] [money] NOT NULL ,
> > [AdjustmentToDraw] [money] NOT NULL ,
> > [PriorYearNegativeBal] [money] NOT NULL ,
> > [ActualCommPaid] [money] NOT NULL ,
> > [Reason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BonusPool] [money] NOT NULL ,
> > [PrdBonusPct] [decimal] (3, 2) NOT NULL ,
> > [PrdSalesQuota] [money] NOT NULL ,
> > [PrdGPPctQuota] [decimal] (3, 2) NOT NULL ,
> > [PrdGPQuota] [money] NOT NULL ,
> > [BkSvcQuota] [money] NOT NULL ,
> > [INDActualYTDBookedServices] [money] NOT NULL ,
> > [INDAddToProductGP$$] [money] NOT NULL ,
> > [INDDeductFromProductGP$$] [money] NOT NULL ,
> > [INDAddToServicesBooked] [money] NOT NULL ,
> > [INDDeductFromServicesBooked] [money] NOT NULL ,
> > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ATBonusPct] [decimal] (5, 2) NOT NULL ,
> > [PrdBonusPctNonAdvTech] AS ([PrdBonusPct] * (1 - [ATBonusPct])) ,
> > [PrdBonusPctAdvTech] AS ([PrdBonusPct] * [AtBonusPct]) ,
> > [ATProdQuota] [money] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[CommisStaffGoals] WITH NOCHECK ADD
> > CONSTRAINT [PK_CommisStaffGoals] PRIMARY KEY CLUSTERED
> > (
> > [StaffID]
> > ) WITH  FILLFACTOR = 80 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[CommisStaffGoals] WITH NOCHECK ADD
> > CONSTRAINT [DF_CommisStaffGoals_AccelRate] DEFAULT (0) FOR [AccelRate] ,
> > CONSTRAINT [DF_CommisStaffGoals_BaseSalary] DEFAULT (0) FOR [BaseSalary] ,
> > CONSTRAINT [DF_CommisStaffGoals_Draw] DEFAULT (0) FOR [Draw] ,
> > CONSTRAINT [DF_CommisStaffGoals_AdjustmentToDraw] DEFAULT (0) FOR
> > [AdjustmentToDraw] ,
> > CONSTRAINT [DF_CommisStaffGoals_PriorYearNegativeBal] DEFAULT (0) FOR
> > [PriorYearNegativeBal] ,
> > CONSTRAINT [DF_CommisStaffGoals_ActualCommPaid] DEFAULT (0) FOR
> > [ActualCommPaid] ,
> > CONSTRAINT [DF_CommisStaffGoals_BonusPool] DEFAULT (0) FOR [BonusPool] ,
> > CONSTRAINT [DF_CommisStaffGoals_PrdBonusPct] DEFAULT (0) FOR [PrdBonusPct] ,
> > CONSTRAINT [DF_CommisStaffGoals_PrdSalesQuota] DEFAULT (0) FOR
> > [PrdSalesQuota] ,
> > CONSTRAINT [DF_CommisStaffGoals_PrdGPPctQuota] DEFAULT (0) FOR
> > [PrdGPPctQuota] ,
> > CONSTRAINT [DF_CommisStaffGoals_PrdGPQuota] DEFAULT (0) FOR [PrdGPQuota] ,
> > CONSTRAINT [DF_CommisStaffGoals_BkSvcQuota] DEFAULT (0) FOR [BkSvcQuota] ,
> > CONSTRAINT [DF_CommisStaffGoals_INDActualYTDBookedServices] DEFAULT (0) FOR
> > [INDActualYTDBookedServices] ,
> > CONSTRAINT [DF_CommisStaffGoals_INDAddToProductGP$$] DEFAULT (0) FOR
> > [INDAddToProductGP$$] ,
> > CONSTRAINT [DF_CommisStaffGoals_INDDeductFromProductGP$$] DEFAULT (0) FOR
> > [INDDeductFromProductGP$$] ,
> > CONSTRAINT [DF_CommisStaffGoals_INDAddToServicesBooked] DEFAULT (0) FOR
> > [INDAddToServicesBooked] ,
> > CONSTRAINT [DF_CommisStaffGoals_INDDeductFromServicesBooked] DEFAULT (0)
> > FOR [INDDeductFromServicesBooked] ,
> > CONSTRAINT [DF_CommisStaffGoals_ATBonusPct] DEFAULT (0) FOR [ATBonusPct] ,
> > CONSTRAINT [DF_CommisStaffGoals_ATProdQuota] DEFAULT (0) FOR [ATProdQuota]
> > GO
> >
> > GRANT SELECT, UPDATE, INSERT, DELETE ON [dbo].[CommisStaffGoals] TO [public]
> > GO
> >
> > GRANT SELECT, UPDATE, INSERT, DELETE ON [dbo].[CommisStaffGoals] TO
> > [CommissionUsers]
> > GO
> >
> > ALTER TABLE [dbo].[CommisStaffGoals] WITH NOCHECK ADD
> > CONSTRAINT [FK_CommisStaffGoals_Staff] FOREIGN KEY
> > (
> > [StaffID]
> > ) REFERENCES [dbo].[Staff] (
> > [StaffID]
> > )
> > GO
> >
> > exec sp_addextendedproperty N'MS_Description', N'keep this for calculation
> > purposes', N'user', N'dbo', N'table', N'CommisStaffGoals', N'column',
> > N'ATBonusPct'
> > GO
> >
> > exec sp_addextendedproperty N'MS_Description', N'delete when changes are
> > done', N'user', N'dbo', N'table', N'CommisStaffGoals', N'column',
> > N'ATProdQuota'
> > GO
> >
> >
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> >> Hi Tom,
> >>
> >> I would run the script below to see if there is any problem with a
> >> particular table. This would fail at that particular table and then you
> >> could at least narrow it down to a particular table. Then look further at
> >> the schema of that table.
> >>
> >> ****************************************************************************
> >> ******
> >> USE database_name
> >> go
> >> DECLARE tables_cursor CURSOR FOR
> >> SELECT name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1
> >> OPEN tables_cursor
> >> DECLARE @tablename SYSNAME
> >>
> >> FETCH NEXT FROM tables_cursor INTO @tablename
> >> WHILE (@@FETCH_STATUS <> -1)
> >> BEGIN
> >> --select Getdate()
> >> PRINT '----Updating Statistics for table ' + @tablename + ' '
> >> EXEC ('UPDATE STATISTICS ['+ @tablename + '] WITH FULLSCAN')
> >> FETCH NEXT FROM tables_cursor INTO @tablename
> >> END
> >> PRINT '----------------All Statistics are Updated----------------'
> >> CLOSE tables_cursor
> >> DEALLOCATE tables_cursor
> >> GO
> >> ****************************************************************************
> >> *******
> >>
> >> Let me know the result of it and let's find the root cause together :)
> >>
> >>
> >> Sincerely yours,
> >>
> >> Michael Cheng
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >> =====================================================
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >>
> >>
>
>
>