Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Microsoft SQL Server 2008 R2 Web Edition (RTM) - 10.50.1600.1 (Intel X86)

Windows 2003 R2 SP2

I am in the process of setting up a maintenance plan to update statistics on a daily basis for all user databases. I am getting the following error, every time the job is run from SQL Server Agent

Error: 2012-03-14 10:54:38.17 Code: 0xC0024104 Source: Update Statistics Task Description: The Execute method on the task returned error code 0x80131904 (Ambiguous column name 'year'.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:54:35 AM Finished: 10:54:38 AM Elapsed: 3.328 seconds. The package execution failed. The step failed.

There is a column called year in several tables and one non-clustered non-unique index on one of the tables. Removing that index has not helped.

Googling around has not provided any other information. When I run sp_updatestats in SSMS manually within the database, I do not receive any errors.

From SQL Server Profiler, This is the only query I'm seeing when the job is run.

exec sp_executesql N'SELECT
v.name AS [Name],
v.object_id AS [ID],
v.create_date AS [CreateDate],
v.modify_date AS [DateLastModified],
ISNULL(sv.name, N'''') AS [Owner],
CAST(case when v.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
SCHEMA_NAME(v.schema_id) AS [Schema],
CAST(
 case 
    when v.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = v.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N''microsoft_database_tools_support'') 
        is not null then 1
    else 0
end          
             AS bit) AS [IsSystemObject],
CAST(OBJECTPROPERTYEX(v.object_id,N''ExecIsAnsiNullsOn'') AS bit) AS [AnsiNullsStatus],
CAST(OBJECTPROPERTYEX(v.object_id,N''ExecIsQuotedIdentOn'') AS bit) AS [QuotedIdentifierStatus],
CAST(OBJECTPROPERTYEX(v.object_id, N''IsSchemaBound'') AS bit) AS [IsSchemaBound],
CAST(CASE WHEN ISNULL(smv.definition, ssmv.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CAST(OBJECTPROPERTY(v.object_id, N''HasAfterTrigger'') AS bit) AS [HasAfterTrigger],
CAST(OBJECTPROPERTY(v.object_id, N''HasInsertTrigger'') AS bit) AS [HasInsertTrigger],
CAST(OBJECTPROPERTY(v.object_id, N''HasDeleteTrigger'') AS bit) AS [HasDeleteTrigger],
CAST(OBJECTPROPERTY(v.object_id, N''HasInsteadOfTrigger'') AS bit) AS [HasInsteadOfTrigger],
CAST(OBJECTPROPERTY(v.object_id, N''HasUpdateTrigger'') AS bit) AS [HasUpdateTrigger],
CAST(OBJECTPROPERTY(v.object_id, N''IsIndexed'') AS bit) AS [HasIndex],
CAST(OBJECTPROPERTY(v.object_id, N''IsIndexable'') AS bit) AS [IsIndexable],
v.has_opaque_metadata AS [ReturnsViewMetadata]
FROM
sys.all_views AS v
LEFT OUTER JOIN sys.database_principals AS sv ON sv.principal_id = ISNULL(v.principal_id, (OBJECTPROPERTY(v.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id
WHERE
(v.type = @_msparam_0)and(v.name=@_msparam_1 and SCHEMA_NAME(v.schema_id)=@_msparam_2)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'V',@_msparam_1=N'V_DistributeurProductions',@_msparam_2=N'dbo'

When run in Query Analyzer, I get this

Msg 209, Level 16, State 1, Procedure V_DistributeurProductions, Line 10
Ambiguous column name 'year'.
Msg 209, Level 16, State 1, Procedure V_DistributeurProductions, Line 10
Ambiguous column name 'year'.
Msg 209, Level 16, State 1, Procedure V_DistributeurProductions, Line 10
Ambiguous column name 'year'.
Msg 209, Level 16, State 1, Procedure V_DistributeurProductions, Line 10
Ambiguous column name 'year'.
Msg 209, Level 16, State 1, Procedure V_DistributeurProductions, Line 10
Ambiguous column name 'year'.
Msg 209, Level 16, State 1, Procedure V_DistributeurProductions, Line 10
Ambiguous column name 'year'.

So perhaps the view V_DistributeurProductions is the problem ?

Any suggestions would be appreciated.

share|improve this question
    
As a workaround, would scheduling sp_updatestats as an agent job be workable for you? –  Simon Righarts Mar 14 '12 at 10:11
    
Can you run profiler to trace the commands sent by the package to see what specific command is causing the error? –  Martin Smith Mar 14 '12 at 10:14
    
Looks like since the view was created one of the base tables has had a new column added that makes the reference to year ambiguous now then. –  Martin Smith Mar 14 '12 at 10:44
    
Thank you Martin. If you put your comments into an answer, I'll check it off. I talked to the developer and he is going to recreate the views that are causing this problem. –  Craig Efrein Mar 14 '12 at 10:48
    
@CraigEfrein - Not sure why this causes problems for the update statistics task though. I can't reproduce this error this end. –  Martin Smith Mar 14 '12 at 11:08
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.