September 11, 2009 at 8:20 am
When I use the unpivot I get the following message:
The type of column "Addr_1" conflicts with the type of other columns specified in the UNPIVOT list.
Does this mean that the columns in the unpivot have to be the same data type?
Here is the structure information for the fields Im wanting to unpivot
[acmc_id] [int] NOT NULL,
[company_id] [int] NOT NULL,
[company_type_id] [int] NULL,
[activity_company_id] [int] NOT NULL,
[name] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Addr_1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Addr_2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Addr_3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zipcode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
September 11, 2009 at 8:28 am
Can you post the unpivot code that generated this error? I'm pretty sure I specified the fields incorrectly the first couple times yesterday and got similar errors. As I mentioned, the unpivot syntax is very confusing ;-).
September 11, 2009 at 8:33 am
hey again 🙂
I figured it out
-- original problem
Select Transaction_id,fieldName, oldvalue
from (
Select
Transaction_id
, acmc_id
, company_id
, company_type_id
, activity_company_id
, Addr_1
, Addr_2
, Addr_3
from activity_company_master_copy
Where CharIndex('Original',user_action) > 0
and transaction_id is not null) as aammc_src
unpivot
(oldvalue For fieldName in (acmc_id, company_id, company_type_id, activity_company_id
, Addr_1, Addr_2, Addr_3)
) as unpvt_aammc
-- fixed
Select Transaction_id,fieldName, oldvalue
from (
Select
Transaction_id
, cast(acmc_id as varchar(255)) as acmc_id
, cast(company_id as varchar(255)) as company_id
, cast(company_type_id as varchar(255)) as company_type_id
, cast(activity_company_id as varchar(255)) as activity_company_id
, cast(Addr_1 as varchar(255)) as addr_1
, cast(Addr_2 as varchar(255)) as addr_2
, cast(Addr_3 as varchar(255)) as addr_3
from activity_company_master_copy
Where CharIndex('Original',user_action) > 0
and transaction_id is not null) as aammc_src
unpivot
(oldvalue For fieldName in (acmc_id, company_id, company_type_id, activity_company_id
, Addr_1, Addr_2, Addr_3)
) as unpvt_aammc
Bascially had to make them all the same data type and width 🙁
So this adds even more complexity... but at least Im starting to understand it more.
September 11, 2009 at 8:56 am
Hrm, so they do. While that's unfortunate, due to the fact that you might be using Dynamic SQL anyways, this shouldn't be a major problem. You'll just add a CAST statement in the dynamic SQL which will be applied to all the columns.
With that in mind though, consider using sql_variant instead of casting everything to varchars. I say consider because I haven't used sql_variant much myself, so I can't swear that it's the correct way to go, but it *seems* like it'd be a better option to me.
September 11, 2009 at 9:37 am
Kevin Ison (9/11/2009)
Does this mean that the columns in the unpivot have to be the same data type?
Yes, because the row data in those columns will end up in the same column after the UNPIVOT.
Imagine if SQL Server let you mix types destined for the same column, and you used the SELECT...INTO to create a table from the final result set - what would be the type of the mixed-types column in the created table? 🙂
September 11, 2009 at 9:43 am
Garadin (9/11/2009)
With that in mind though, consider using sql_variant instead of casting everything to varchars. I say consider because I haven't used sql_variant much myself, so I can't swear that it's the correct way to go, but it *seems* like it'd be a better option to me.
Even SQL_VARIANT can't contain all data types, so that wouldn't work in all cases, and comparisons would be complex.
SQL_VARIANT cannot contain any of the LOB data types (including XML and MAX), ROWVERSION (timestamp), SQL_VARIANT, or UDTs.
September 11, 2009 at 10:03 am
Paul White (9/11/2009)
Garadin (9/11/2009)
With that in mind though, consider using sql_variant instead of casting everything to varchars. I say consider because I haven't used sql_variant much myself, so I can't swear that it's the correct way to go, but it *seems* like it'd be a better option to me.Even SQL_VARIANT can't contain all data types, so that wouldn't work in all cases, and comparisons would be complex.
SQL_VARIANT cannot contain any of the LOB data types (including XML and MAX), ROWVERSION (timestamp), SQL_VARIANT, or UDTs.
While the limitations of SQL_Variant are absolutely correct, for his purposes I doubted the existence of any lob types. Timestamp is a possibility, but since he's using date fields in his audit table, I doubted it as well. Regardless, the restrictions are definitely worth pointing out.
What do you mean about the comparisons being complex? I'm a bit curious as to exactly how this datatype would work in this situation. Would it just convert everything to a varchar(255) in the above situation anyways?
If so, my thought process is that for this table it converted everything to varchar(255), but for another table, maybe it needs varchar(2000). Attempting to pick the correct casting type per table (He's looking at doing an unpivot on 30+ different tables with different definitions in one script into a single uniform structure; that information isn't in this thread, it was in his other one) is a rather large extra step that would have to be taken, where as it is something that I was thinking SQL_Variant could take care of automatically. It is entirely possible that I'm misunderstanding how it would behave in this situation though.
September 11, 2009 at 10:07 am
Here's the other thread I mentioned:
http://www.sqlservercentral.com/Forums/Topic785062-338-1.aspx
The datatype thing could be mostly academic. With SQL2005 he could probably get away with just casting everything as varchar(max) and calling it a day, but it seems inefficient. Is there some overhead in sql_variant that would make something else a better choice?
September 11, 2009 at 10:38 am
Garadin (9/11/2009)
While the limitations of SQL_Variant are absolutely correct, for his purposes I doubted the existence of any lob types. Timestamp is a possibility, but since he's using date fields in his audit table, I doubted it as well. Regardless, the restrictions are definitely worth pointing out.
I like to be helpful like that. 😉
Garadin (9/11/2009)
What do you mean about the comparisons being complex? I'm a bit curious as to exactly how this datatype would work in this situation. Would it just convert everything to a varchar(255) in the above situation anyways?
You'd still have to CAST or CONVERT each original column's value to a SQL_VARIANT. The final column would then also be a SQL_VARIANT. The base types would likely differ per-row, but that's ok. To be clear, in one row the SQL_VARIANT base type might be INT, but be NVARCHAR in the next. The details are non-trivial, especially when it comes to comparing SQL_VARIANTs or applying operators or functions to them. It doesn't help that SQL 2005 lacks 2008's SQL_VARIANT_PROPERTY to determine the base type.
Anyway, rather than reproduce quite a dense section of Books Online here, I'll just give the link
Garadin (9/11/2009)
If so, my thought process is that for this table it converted everything to varchar(255), but for another table, maybe it needs varchar(2000). Attempting to pick the correct casting type per table (He's looking at doing an unpivot on 30+ different tables with different definitions in one script into a single uniform structure; that information isn't in this thread, it was in his other one) is a rather large extra step that would have to be taken, where as it is something that I was thinking SQL_Variant could take care of automatically. It is entirely possible that I'm misunderstanding how it would behave in this situation though.
So long as you avoid the incompatible types, it runs ok. Example:
SELECT U.P, U.A, U.B
FROM (
SELECT
P = 'A',
X = CAST(123.45 AS SQL_VARIANT),
Y = CAST('BCD' AS SQL_VARIANT),
Z = CAST(NULL AS SQL_VARIANT)
) P
UNPIVOT (A FOR B IN (X, Y, Z)) U
PAB
A123X
ABCDY
...you end up with different base types per row in column 'A', but the column is SQL_VARIANT.
So it would work, but the caveat list is long, and pain may not be far away.
Also note that 'Z' is swallowed by the UNPIVOT (regardless of type conversions).
September 11, 2009 at 10:40 am
Wow thanks for the replies... definitely gives me something to think about. Handling the the variable column widths and data types are going to be a challenge... but I think a little hard work there will hopefully make this a better product in the long run.
Im not comfortable with just setting everything to varchar(max) at this time... unless I get desperate. heheh.
If you think of anything else by all means let me know!
Thanks again for informative replies... very helpful!
Kevin
September 11, 2009 at 11:05 am
I've read that BOL article several times, maybe one of these times I'll actually remember what it says the next day ;-).
I still don't see anything that would steer me away from using it in your situation. If you were doing anything other than simply comparing an old and new value (such as adding them together, or determining which one was the 'greater' value), you might have issues if they changed 'Twelve' to 11 due to the way sql_variant evaluates different base data types (one being a number and one being a string) However, since all you're worried about is whether or not the value has *changed*, it should work out properly.
I think that when you get to this portion of the script in Dynamic SQL that you'll find '... CAST(' + @CurrentField + ' as sql_variant)...' is a whole lot easier to deal with than querying the system tables to figure out what an appropriate datatype for each table is. There are definitely pitfalls and restrictions on sql_variant, but at least they're documented and you can see most of them ahead of time.
September 11, 2009 at 8:24 pm
Garadin (9/11/2009)
Here's the other thread I mentioned:http://www.sqlservercentral.com/Forums/Topic785062-338-1.aspx
Aha - now I have the context. SQL_VARIANT seems like a reasonable choice now. I'm just a bit confused over the structure of the table that is being UNPIVOTed...is there really one column per audited column?
Have you considered an XML approach? The general idea is to use DML or DDL triggers to capture content from the inserted and deleted pseudo-tables as XML in the audit table. You can then use XQuery to produce all sorts of reporting goodness. It's potentially very powerful and flexible - if you are comfortable with XML.
There are any number of books, blogs, and articles out there which will generate the audit objects for you. Just in case it is of interest, here's a few related links:
Michael Coles: Dynamic Logging Trigger
Paul Nielsen: Code Generator For Fixed Audit Trail Triggers
SQL Server Central: A Simple Audit Trail[/url]
General stuff on audit trails and logging by Gsquared: http://www.sqlservercentral.com/articles/Auditing/63247/
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply