Home Dashboard Directory Help
Search

<Unsupported Data Type> reported for supported data types in nested query by Shaggie


Status: 

Active


8
0
Sign in
to vote
Type: Bug
ID: 464339
Opened: 6/4/2009 7:22:24 PM
Access Restriction: Public
2
Workaround(s)
view
9
User(s) can reproduce this bug

Description

When I run the query below on any of our databases, the field InventoryItemsAuditTrailID is reported as an <Unsupported Data Type>

InventoryItemsAuditTrailID is an INT Identity Primary Key for the InventoryItemsAuditTrail table. InventoryItemsAuditTrail contains an 8000 character varchar field and several (15 -> 20) 250 character fields.

If required I can try to put together a sample copy of the database that demonstrates this issue.
Details
Sign in to post a comment.
Posted by BIDSReporter on 5/7/2014 at 9:57 AM
This problem still persists to this day. It most often occurs when I query across multiple databases on a single server using "UNION" queries with nested subqueries.
However, even though the "errors" show up in the SSRS Query Designer' output pane, when I preview the SSRS report the data shows fine (ie., normal output data).

Is this a "memory" issue?
Posted by Hannover Fist on 2/21/2014 at 1:21 PM
Yet another bug that Microsoft refuses to fix.

"a low number of votes"

Less than 1% (more likely < .05%) of users actually report bugs because:

A. The bug reporting requirements are overly complicated and time consuming
B. Microsoft never fixes any bugs and spends more effort defending their defective product than fixing anything would take
Posted by Shaggee on 9/4/2013 at 2:34 PM
I am glad to see that this is still active.
Hopefully, this will get fixed in SQL 2012 management studio.

Unfortunately the workaround mentioned does not work well at all. It only works for simple queries. In more complex queries, removing the all of the aliases is completely impossible!
Posted by Microsoft on 1/11/2013 at 8:52 AM
Hi Shaggie

Thank you for your patience. We have looked in to this issue and decided not to improve this behavior in our current release since there is a work around and a low number of votes for this for such an old bug. Therefore I will be resolving this issue shortly.

Regards,
-Sam Hughes
Posted by Shaggee on 9/20/2012 at 12:27 PM
Is there any information that can be shared as to the status of this bug. It is quite annoying for us and we would like to know when and where this problem will be fixed.

I appreciate you quick response.

Thank you,

Mark
Posted by Todd Sutay on 8/21/2012 at 2:24 PM
The problem appears to be getting worse, as even simple views are now reporting fields as "<Unsupported Data Type>". Although the designer seems to be eager to rewrite complicated SQL statements after clicking out of the SQL tab, in these simple views, the designer is not doing that, but still reporting the same result.

The problem does not seem to occur if the view is return from a stored procedure, but that's often not a viable solution either.

Posted by Seradex on 6/20/2012 at 9:26 AM
We need a better, more permanent solution to this problem.
Posted by Seradex on 6/20/2012 at 9:25 AM
I have also noted that removing the alias from the nested SQL does fix the problem, but this is often temporary since SQL Server Management Studio loves to re-write the SQL statements as entered, which often breaks that SQL Statement.
Posted by Shaggie on 5/2/2012 at 5:11 PM
Note that although removing the alias from the nested SQL does fix the problem, that this is quite often NOT POSSIBLE to do, due to the complexity of the query. (Example: the nested query exists more than once in the query due to different filtering or grouping)
Also if the table is aliased as itself, the problem still occurs. "InventoryItemsAuditTrail AS InventoryItemsAuditTrail" for example.
Posted by Shaggie on 5/2/2012 at 5:04 PM
Please take care to note that the exact same thing occurs if you create a new view with this SQL and run it prior to saving the view. This also occurs running it after saving, whether you have closed the view and re-opened for modification or you have left it open.
Posted by Shaggie on 5/2/2012 at 5:00 PM
Note that no special steps were taken when attaching the database.
Posted by Shaggie on 5/2/2012 at 4:59 PM
Sorry I accidentally attached the file 2x as I thought I read as I clicked to go back to this page that it was uploading the file.
Posted by Shaggie on 5/2/2012 at 4:56 PM
I reproduced this on SQL Server 2008 R2 SP1 with CU6 applied.

Microsoft SQL Server Management Studio    10.50.2811.0
SQL Server 10.50.2811
I am adding a Word Document (Unsupported Data Type.doc) in a zip file showing this issue.
Posted by AMWild on 3/6/2012 at 2:12 PM
I also have this problem with SQL Server 2008R2 Enterprise 64 bit

Microsoft SQL Server Management Studio    10.50.2425.0
Microsoft Analysis Services Client Tools    10.50.2425.0
Microsoft Data Access Components (MDAC)    6.0.6002.18005
Microsoft MSXML            3.0 4.0 5.0 6.0
Microsoft Internet Explorer        8.0.6001.19170
Microsoft .NET Framework        2.0.50727.4216
Operating System            6.0.6002:

When creating a view using the diagram pane and pasting in subqueries I got the following SQL, which resulted in <Unsupported Data Type> in PreForecastModelID, NEVDISModelID and SlsTgtCarlineCatID but NOT HRAPModelID:

SELECT     Fore.tbl_ModelCodes.FAWCode, Fore.tbl_ModelCodes.ForecastModelID, qryPreForecastModel.PreForecastModelID, Fore.tbl_ModelCodes.FAWModelID,
                     qryHRAPModel.HRAPModelID, Fore.tbl_ModelCodes.BuildModelID, qryNEVDISModel.NEVDISModelID, qrySlsTgtCarlineCategories.SlsTgtCarlineCatID,
                     Fore.tbl_ModelCodes.SlsTgtCarlineID, Fore.tbl_ModelCodes.ColourModelID, Fore.tbl_ModelCodes.IncludeInFAW, Fore.tbl_ModelCodes.CurrentModel
FROM         Fore.tbl_ModelCodes LEFT OUTER JOIN
                         (SELECT     FAWCode, ModelID AS NEVDISModelID
                            FROM         Fore.tbl_CustomModelCodes AS tbl_CustomModelCodes_1
                            WHERE     (ModelTypeID = 2)) AS qryNEVDISModel ON Fore.tbl_ModelCodes.FAWCode = qryNEVDISModel.FAWCode LEFT OUTER JOIN
                         (SELECT     FAWCode, ModelID AS SlsTgtCarlineCatID
                            FROM         Fore.tbl_CustomModelCodes AS tbl_CustomModelCodes_3
                            WHERE     (ModelTypeID = 4)) AS qrySlsTgtCarlineCategories ON Fore.tbl_ModelCodes.FAWCode = qrySlsTgtCarlineCategories.FAWCode LEFT OUTER JOIN
                         (SELECT     FAWCode, ModelID AS HRAPModelID
                            FROM         Fore.tbl_CustomModelCodes
                            WHERE     (ModelTypeID = 1)) AS qryHRAPModel ON Fore.tbl_ModelCodes.FAWCode = qryHRAPModel.FAWCode LEFT OUTER JOIN
                         (SELECT     FAWCode, ModelID AS PreForecastModelID
                            FROM         Fore.tbl_CustomModelCodes AS tbl_CustomModelCodes_2
                            WHERE     (ModelTypeID = 3)) AS qryPreForecastModel ON Fore.tbl_ModelCodes.FAWCode = qryPreForecastModel.FAWCode

However, when I deleted the "AS tbl_CustomModelCodes_" aliases from within the derived table definitions and allowed SSMS to recreate them, I got the following query which functioned as expected, WITHOUT the <Unsupported Data Type> errors:

SELECT     Fore.tbl_ModelCodes.FAWCode, Fore.tbl_ModelCodes.ForecastModelID, qryPreForecastModel.PreForecastModelID, Fore.tbl_ModelCodes.FAWModelID,
                     qryHRAPModel.HRAPModelID, Fore.tbl_ModelCodes.BuildModelID, qryNEVDISModel.NEVDISModelID, qrySlsTgtCarlineCategories.SlsTgtCarlineCatID,
                     Fore.tbl_ModelCodes.SlsTgtCarlineID, Fore.tbl_ModelCodes.ColourModelID, Fore.tbl_ModelCodes.IncludeInFAW, Fore.tbl_ModelCodes.CurrentModel
FROM         Fore.tbl_ModelCodes LEFT OUTER JOIN
                         (SELECT     FAWCode, ModelID AS NEVDISModelID
                            FROM         Fore.tbl_CustomModelCodes
                            WHERE     (ModelTypeID = 2)) AS qryNEVDISModel ON Fore.tbl_ModelCodes.FAWCode = qryNEVDISModel.FAWCode LEFT OUTER JOIN
                         (SELECT     FAWCode, ModelID AS SlsTgtCarlineCatID
                            FROM         Fore.tbl_CustomModelCodes AS tbl_CustomModelCodes_3
                            WHERE     (ModelTypeID = 4)) AS qrySlsTgtCarlineCategories ON Fore.tbl_ModelCodes.FAWCode = qrySlsTgtCarlineCategories.FAWCode LEFT OUTER JOIN
                         (SELECT     FAWCode, ModelID AS HRAPModelID
                            FROM         Fore.tbl_CustomModelCodes AS tbl_CustomModelCodes_2
                            WHERE     (ModelTypeID = 1)) AS qryHRAPModel ON Fore.tbl_ModelCodes.FAWCode = qryHRAPModel.FAWCode LEFT OUTER JOIN
                         (SELECT     FAWCode, ModelID AS PreForecastModelID
                            FROM         Fore.tbl_CustomModelCodes AS tbl_CustomModelCodes_1
                            WHERE     (ModelTypeID = 3)) AS qryPreForecastModel ON Fore.tbl_ModelCodes.FAWCode = qryPreForecastModel.FAWCode

If the error-producing view is run outside the view designer (e.g. SELECT * FROM faw.qry_ModelCodes), it gives correct results.

It would appear that if the first instance of a table within a derived table does not have an alias, and later instances do, there is no error, but if the first instance does have an alias and a later instance does not, the error occurs.

Here is the definition of the tables concerned, with a very truncated data set:

/****** Object: Table [Fore].[tbl_ModelCodes]    Script Date: 03/07/2012 08:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Fore].[tbl_ModelCodes](
    [FAWCode] [varchar](15) NOT NULL,
    [ForecastModelID] [int] NULL,
    [FAWModelID] [int] NULL,
    [BuildModelID] [int] NULL,
    [SlsTgtCarlineID] [int] NULL,
    [ColourModelID] [int] NULL,
    [IncludeInFAW] [bit] NOT NULL,
    [CurrentModel] [bit] NULL,
    [aus_series_cde] AS (substring([FAWCode],(1),(2))) PERSISTED,
    [mrktg_model_cde] AS (substring([FAWCode],(3),(5))) PERSISTED,
    [mrktg_pkg_cde] AS (substring([FAWCode],(8),(3))) PERSISTED,
    [mrktg_pkg_vers_id] AS (substring([FAWCode],(11),(1))) PERSISTED,
CONSTRAINT [PK_tbl_ModelCodes] PRIMARY KEY CLUSTERED
(
    [FAWCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_PADDING ON
GO
INSERT [Fore].[tbl_ModelCodes] ([FAWCode], [ForecastModelID], [FAWModelID], [BuildModelID], [SlsTgtCarlineID], [ColourModelID], [IncludeInFAW], [CurrentModel]) VALUES (N'AH8CP67184B', 1, 1, 1, 1, 7, 1, NULL)
SET ANSI_PADDING OFF
/****** Object: Table [Fore].[tbl_CustomModelCodes]    Script Date: 03/07/2012 08:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Fore].[tbl_CustomModelCodes](
    [FAWCode] [varchar](15) NOT NULL,
    [ModelTypeID] [int] NOT NULL,
    [ModelID] [int] NOT NULL,
CONSTRAINT [PK_tbl_CustomModelCodes] PRIMARY KEY CLUSTERED
(
    [FAWCode] ASC,
    [ModelTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IX_tbl_CustomModelCodes_ModelTypeID] ON [Fore].[tbl_CustomModelCodes]
(
    [ModelTypeID] ASC
)
INCLUDE ( [FAWCode],
[ModelID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
INSERT [Fore].[tbl_CustomModelCodes] ([FAWCode], [ModelTypeID], [ModelID]) VALUES (N'AH8CP67184B', 1, 1)
INSERT [Fore].[tbl_CustomModelCodes] ([FAWCode], [ModelTypeID], [ModelID]) VALUES (N'AH8CP67184B', 2, 337)
INSERT [Fore].[tbl_CustomModelCodes] ([FAWCode], [ModelTypeID], [ModelID]) VALUES (N'AH8CP67184B', 3, 428)
INSERT [Fore].[tbl_CustomModelCodes] ([FAWCode], [ModelTypeID], [ModelID]) VALUES (N'AH8CP67184B', 4, 431)
/****** Object: Default [DF_tbl_ModelCodes_IncludeInFAW]    Script Date: 03/07/2012 08:53:53 ******/
ALTER TABLE [Fore].[tbl_ModelCodes] ADD CONSTRAINT [DF_tbl_ModelCodes_IncludeInFAW] DEFAULT ((1)) FOR [IncludeInFAW]
GO
/****** Object: ForeignKey [FK_tbl_CustomModelCodes_tbl_ModelCodes]    Script Date: 03/07/2012 08:53:53 ******/
ALTER TABLE [Fore].[tbl_CustomModelCodes] WITH CHECK ADD CONSTRAINT [FK_tbl_CustomModelCodes_tbl_ModelCodes] FOREIGN KEY([FAWCode])
REFERENCES [Fore].[tbl_ModelCodes] ([FAWCode])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [Fore].[tbl_CustomModelCodes] CHECK CONSTRAINT [FK_tbl_CustomModelCodes_tbl_ModelCodes]
GO
Posted by Microsoft on 6/29/2011 at 11:59 AM
Please provide us with more detailed repro instructions including the following:

1. all steps taken when attaching the zipped files to a DB server.
2. version of the DB server & version of SSMS you are using
3. Exact text of menu options you are choosing when the error occurs.
4. Exact text of the error message & where that error message appears.
5. A screen-shot that illustrates the error.
Posted by Shaggie on 6/29/2011 at 7:41 AM
Note that I had forgotten that I posted this originally using my home account. I accidentally replied using my "Shaggee" account which is my work account. Please treat them as the same.
Posted by Shaggee on 6/29/2011 at 7:33 AM
This is still a problem even with SQL Server 2008 R2.
What version of Microsoft SQL Server Management Studio 2008 R2 did you use. I tried with version 10.50.1600.1 and was able to reproduce the problem without difficulty.

Please ensure that you are trying the view from design mode as this BUG is not reproduceable when opening the view (Edit in 2008 and above) or when selecting from the view.
This bug is also reproduceable when opening a table (Edit in 2008 and above)
Posted by Microsoft on 6/28/2011 at 10:08 PM
Hi,

This is regarding the connect item 464339. We are not able to recreate the issue in SQL Server 2008 R2 and later versions, we tried the attached test database and also creating new ones per the details given. This issue is now being closed. However if you are still able to see the issue in 2008R2 or later, kindly let us know and we will reconsider.

thank you

Seshagiri
PM, SSMS
Posted by Microsoft on 4/21/2010 at 8:58 AM
Hello,

My apologies for the long delay in getting back to you about this issue. We were able to reproduce the behavior you described and have tracked down the source of the problem to the Designer code in the query editor. The problem will not be fixed in SQL Server 2005. We will consider fixing this problem in the next major release, or possibly in a future cumulative update to SQL Server 2008. For now, there is a workaround to execute the query in the editor, as you have already discovered.

Thank you for reporting this issue. We appreciate your help in improving the quality of SQL Server.

Susan Price
Program Manager
SQL Server Database Engine
Posted by Shaggie on 4/8/2010 at 1:35 PM
Please let me know of the status of this issue. It is significantly impeding my team's productivity when writing SQL statements.
Posted by Seradex on 1/20/2010 at 9:06 AM
I get this problem as well with both SQL 2005 and SQL 2008. I know it can be worked around by converting the data type of the find to the same data type, or by running it in the Query Analyzer window or by saving a view and opening it in non-desgn mode, but any of those possibilities can significantly impede productivity.
Any update on when this will be fixed as it is quite annoying for developers and others as well?
Posted by Shaggie on 10/1/2009 at 3:45 PM
I attached a database back in August, but I can no longer see it in this issue. Do you still have that file??
Posted by Shaggie on 9/4/2009 at 2:26 PM
My tests show that it should not be difficult to reproduce. You must simply run a query from design mode or from a modified table's view ("Open Table")
Posted by Shaggie on 8/19/2009 at 3:53 PM
I have attached a sample database with a sample query that reproduces this problem. Note that it is the same query as below. Please note that if you "Open" the query it will work correctly. I believe it works in that instance because you are querying the view. The problem occurs specifically when you design the query and run it from the designer.
Posted by Microsoft on 6/16/2009 at 2:42 PM
Hello,

Thank you for filing this report with the SQL Server Engine Team. We need some additional information to help us investigate this issue. Please send us a CREATE TABLE script or a copy (or sample) of the database so that we can try to reproduce the problem you are encountering.

Thanks,

Susan Price
Program Manager
SQL Server Database Engine
Posted by AaronBertrand on 6/5/2009 at 9:44 AM
You need to give CREATE TABLE scripts so that people can try to reproduce it. Also where exactly is this "reported"? Where are you running this query?
Posted by Shaggie on 6/4/2009 at 7:25 PM
FYI, the Qty and Cost fields are float fields, and the remaining fields in the query are integer fields.
Sign in to post a workaround.
Posted by Liberate Te on 12/24/2012 at 1:12 PM
the workaround below only worked for me until I saved the redone view. Once opened again, the error was back.

By accident I found the DUMBEST of all workarounds:

If you create a second view from the select all of the failing view, then the results will be returned correctly.

BUT: IT MUST BE A NEW VIEW. If you try to nest the original view, it will NOT work!

Now even the link in Access performs well!


Panem et circenses
Posted by AMWild on 3/6/2012 at 2:16 PM
In the view designer, remove all aliases from subquery tables and allow SSMS to recreate them. See my post on 6/03/2012 at 2:12 PM
File Name Submitted By Submitted On File Size  
MSTest.zip (restricted) 8/19/2009 -
Unsupported Data Type.zip 5/2/2012 143 KB
Unsupported Data Type.zip 5/2/2012 143 KB