The column was specified multiple times for 'S'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

The column was specified multiple times for 'S'

Author

Topic

itsdeshpande
Starting Member

3 Posts

Posted - 2008-05-29 : 08:00:35

Hi All,

As you all might be aware, there is a procedure called "p_CreateViewsForClassOrRelationshipDefinitions" in MOM's SystemCenterReporting database. This is called I dont know from where, but this creates a lot of views in the DB, with the help of dynamic sql. We are facing an issue with this SPROC. If fails with the error:

Server: Msg 8156, Level 16, State 1, Procedure SC_Class_Computer_View, Line 2
The column 'DAT File Version' was specified multiple times for 'S'.
Server: Msg 777977204, Level 16, State 1, Procedure p_CreateViewsForClassOrRelationshipDefinitions, Line 1609
SQL Server error 8156 encountered in p_CreateViewsForClassOrRelationshipDefinitions

I broke the down the huge procedure and made it print all the CREATE VIEW scripts. For the view SC_Class_Computer_View, for which this is throwing the error, I extracted that script and run it individually, it runs fine!!! It Created the view. But, if the SPROC is run, then it fails with that error. I looked at the create view script and there is no error - well, if it was, it wouldn't run individually.

This SPROC (same, not even a space extra) run fine on another server. Please note that this is a out of the box script, and not customized.

Can anyone tell what could be going wrong? I could paste the complete procedure here, but it is really really huge - 1614 lines - to be given here...

But below is the view's script:

CREATE VIEW [dbo].[SC_Class_Computer_View] AS
SELECT S.ClassInstanceID AS ClassInstanceID,
MAX(S.[ComputerName]) AS [ComputerName],
MAX(S.[ActiveTimeBias]) AS [ActiveTimeBias],
MAX(S.[AM-LON: Live Oracle Servers]) AS [AM-LON: Live Oracle Servers],
MAX(S.[AM-LON: Microsoft SQL Server version]) AS [AM-LON: Microsoft SQL Server version],
MAX(S.[Bias]) AS [Bias],
MAX(S.[BizTalk Server 2002 Enterprise Edition]) AS [BizTalk Server 2002 Enterprise Edition],
MAX(S.[BizTalk Server Root Key]) AS [BizTalk Server Root Key],
MAX(S.[Bridgehead Server]) AS [Bridgehead Server],
MAX(S.[BusinessCriticality]) AS [BusinessCriticality],
MAX(S.[Citrix Farm Metric Server]) AS [Citrix Farm Metric Server],
MAX(S.[Citrix Zone Data Collector]) AS [Citrix Zone Data Collector],
MAX(S.[Computer Description]) AS [Computer Description],
MAX(S.[Computer Manufacturer]) AS [Computer Manufacturer],
MAX(S.[Computer Model]) AS [Computer Model],
MAX(S.[Computer Name]) AS [Computer Name],
MAX(S.[ComputerType]) AS [ComputerType],
MAX(S.[Crystal Reporting Server]) AS [Crystal Reporting Server],
MAX(S.[Custom Data 1]) AS [Custom Data 1],
MAX(S.[DAT File Version]) AS [DAT File Version]
FROM (SELECT CI.ClassInstanceID AS ClassInstanceID,
CI.KeyValue AS [ComputerName],
[ActiveTimeBias] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '2D9E3AB8-6F72-4420-A117-148B192F8F02') THEN CAI.Value ELSE NULL END,
[AM-LON: Live Oracle Servers] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '7A5A35FF-0EFC-4073-A39D-8BA3E6612A43') THEN CAI.Value ELSE NULL END,
[AM-LON: Microsoft SQL Server version] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '54278596-9365-4838-BE2C-61EC7B56240C') THEN CAI.Value ELSE NULL END,
[Bias] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '4FF64B2F-E1BD-4F48-AFC5-C70682687847') THEN CAI.Value ELSE NULL END,
[BizTalk Server 2002 Enterprise Edition] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = 'D85D1D01-8EF9-4839-BF59-3D83BC512A21') THEN CAI.Value ELSE NULL END,
[BizTalk Server Root Key] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = 'A9267C0A-2374-435C-96F0-A85F00012061') THEN CAI.Value ELSE NULL END,
[Bridgehead Server] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '1E6A36DD-968A-4990-99AA-636E5067A800') THEN CAI.Value ELSE NULL END,
[BusinessCriticality] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = 'C14CD34C-77CF-417E-BE64-8507AE054D6E') THEN CAI.Value ELSE NULL END,
[Citrix Farm Metric Server] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '7E259CCA-F24D-4AF8-94F3-09288D12B435') THEN CAI.Value ELSE NULL END,
[Citrix Zone Data Collector] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = 'D2142E96-D2B7-43D7-96B5-24A3EA2BC87C') THEN CAI.Value ELSE NULL END,
[Computer Description] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '99374D6A-5694-413E-99F4-7EF8294A40E7') THEN CAI.Value ELSE NULL END,
[Computer Manufacturer] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = 'D385B2F8-F78D-43CD-9E75-27AF0550FCA0') THEN CAI.Value ELSE NULL END,
[Computer Model] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = 'CFAAE679-EDB6-4665-AB69-46B369F5CFC6') THEN CAI.Value ELSE NULL END,
[Computer Name] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = 'EF34ED1D-B97F-4C41-9CCD-F426C78C196E') THEN CAI.Value ELSE NULL END,
[ComputerType] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '5B7CF3BF-C12A-4501-BAD0-BB34C3C9458E') THEN CAI.Value ELSE NULL END,
[Crystal Reporting Server] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = 'A172B592-F8F3-45B4-A869-7A1A1812166D') THEN CAI.Value ELSE NULL END,
[Custom Data 1] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '48AC1F8A-77BC-4B59-93F1-27703181BA48') THEN CAI.Value ELSE NULL END,
[DAT File Version] = CASE WHEN CAI.ClassAttributeDefinition_FK = (SELECT CAD.SMC_InstanceID
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
WHERE CAD.ClassAttributeID_PK = '5698C78B-88D2-4A5C-89AA-EAAC9402FD60') THEN CAI.Value ELSE NULL END
FROM dbo.SC_ClassInstanceFact_Latest_View AS CI
LEFT OUTER JOIN dbo.SC_ClassAttributeInstanceFact_Latest_View AS CAI ON CI.ClassInstanceID = CAI.ClassInstanceID
WHERE CI.ClassDefinition_FK = (SELECT CD.SMC_InstanceID FROM dbo.SC_ClassDefinitionDimension_View AS CD WHERE CD.ClassID_PK = 'B27C00AD-94DB-49F5-875D-4048E2231BEC')
) AS S GROUP BY S.ClassInstanceID

Please help..

Thanks a lot,

Manoj Deshpande.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 09:00:44

The error is inside your derived table S. As error suggests you have more than one column having the name/alias name as 'DAT File Version'. The code is really messy and i really cant find out the column inside S!

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-29 : 13:00:53

I'd put those attributeID_PKs in a table and clean the code up a bit.

An infinite universe is the ultimate cartesian product.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 13:07:06

I wouldn't make any changes to the MOM code as your changes might be undone if you install patches or service packs.

Since this issue is with the MOM product, you should open a case with MS. Opening cases with MS are free when it is found to be a bug. This is obviously a bug.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

itsdeshpande
Starting Member

3 Posts

Posted - 2008-05-30 : 01:52:41

Thanks thanks a lot all of you!
@tkizer, you are right. We cannot make any changes to that code. There is a hint however, that the column "DAT File" has occured twice, because it is entered twice (the computer attributes might be created twice with the same name - it allows this in the Admin Console). We are checking this on the OnePoint database. The below query (the second one. The first I think gets the attributes. I have put that data into a temporary table) returns 4 rows with count 2 for each on SystemCenterReporting database, which supports the above hint a bit. Let us see how it goes.
------------------------------------------------------------------------------------------------
Select * into Tempo from (
SELECT CD.ClassID_PK AS ClassID,
CAST(CD.[Name] AS NVARCHAR(108)) AS ClassName,
CAD.ClassAttributeID_PK AS ClassAttributeID,
CAST(CAD.ClassAttributeName AS NVARCHAR(128)) AS ClassAttributeName,
(SELECT CAST(CADPK.ClassAttributeName AS NVARCHAR(128))
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CADPK
WHERE CADPK.IsPrimaryKey = 1
AND CADPK.ClassDefinition_FK = CAD.ClassDefinition_FK) AS ClassPrimaryKeyName
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
INNER JOIN dbo.SC_ClassDefinitionDimension_View AS CD
ON CAD.ClassDefinition_FK = CD.SMC_InstanceID
WHERE CD.[Name] NOT IN (N'NULL_CLASS', N'UNRESOLVED_CLASS', N'UNAVAILABLE_CLASS') )a
------------------------------------------------------------------------------------------------
SELECT ClassAttributeName,ClassID, COUNT(*) FROM Tempo
GROUP BY ClassAttributeName,ClassID
Having count(*)>1
------------------------------------------------------------------------------------------------
However, I would be happy to recieve more ideas if anyone has.

Again, thanks for all your replies.

Manoj Deshpande.

itsdeshpande
Starting Member

3 Posts

Posted - 2008-06-26 : 06:44:07

Hi People!
There is some news on this issue. We have found that there are infact duplicate entries and that is because of different ClassAttributeID's on them. We are evaluating the reason why would there be a different ID for the same name. Details are here:

When checked there appeared to be duplicates, which led to the discussion on ClassAttributeID's. There are 4 duplicate
ClassAttributeNames but hacing 2 different ClassAttributeID_PK's, meaning, 8 different ClassAttributeID_PK's. When tried with this query, it reveals this:

SELECT CAD.ClassAttributeName,CD.ClassID_PK,CD.[Name],*
FROM dbo.SC_ClassAttributeDefinitionDimension_View AS CAD
INNER JOIN dbo.SC_ClassDefinitionDimension_View AS CD
ON CAD.ClassDefinition_FK = CD.SMC_InstanceID
WHERE CD.[Name] NOT IN (N'NULL_CLASS', N'UNRESOLVED_CLASS', N'UNAVAILABLE_CLASS')
AND CAD.ClassAttributeName in ('DAT File Version','Microsoft Windows Current
Version','Server Description','Virusscan Engine Version') ORDER BY CAD.ClassAttributeName,CD.ClassID_PK ASC

Now, why would this change? Why would there be different ClassAttributeID_PK's for the same ClassAttributeNAmes?

Any ideas would be appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources