The error
The type of column "XXX" conflicts with the type of other columns specified in the UNPIVOT list.
is very obvious and one of the better messages. It tells you that your UNPIVOT list contains columns of different types. Here's an example that causes such an error: SQL Fiddle
SEL...
Why does it cause an error with the remote query? In what situation would the CASTs not be resolved there and therefore allow the query to run?
SELECT * FROM
(
SELECT
ID,
CONVERT(sql_variant, F1) AS F1,
CONVERT(sql_variant, F2) AS F2,
CONVERT(sql_variant, F3) AS F3
FROM
SQL2K5.Sandpit.dbo.temp AS t
) AS T
UNPIVOT(FieldValue for FieldName IN (F1, F2, F3)) AS UNPVT;
So, my answer to the popover is because you haven't held it up with respect to the horizontal axis, but you have held it up with respect to the vertical axis.
@swasheck I hadn't, I just figured everybody knew our eyes were better tuned to blue than to violet, violet being the end of the visible spectrum (and ultraviolet coming after)
If I had to actively explain to someone why the color blue is more visible than the color violet if both are portrayed as part of a broad spectrum of light, they would likely ask such a question in the first place.
The very act of asking such a question leads me to think that we need to have a more indepth discussion, about one of the following three points:
Think before you ask, for like 20 seconds.
What the cones and bars and the similar on the retina
@RichardTheKiwi Might be something special about MAX or something stringy like collation. All sorts of fun to be had with linked servers and distributed query in general. And by fun, I mean you know...pain.
@dezso because there is already an established vendor-neutral Q&A site that facilitates finding answers, that is already integrated into a developers regular workflow, and that has all the features that facilitate getting an answer, that isn't G+.
I can give reasons why G+ is better for some things, like hangouts, or document stores, or long unmoderated emotional discussions
@SQLKiwi Hmm... collation, maybe. Hope you don't mind I added a reference to your chat transcript solution in the answer, and linked to your profile :)
Funny thing is, for this whole thing to happen, it involved TWO Google fuckups. Firstly, they put 2 emails inquiring about this through godaddy into spam as false positives. Then I detected it because someone replied to a Google+ posting via email (noreply@google) and then asked me if I received it.
Then in recovering a couple dozen false positives, I came across this follow up to an earlier email (through LinkedIn) asking about it which I thought was done. Google is not in my good books this Christmas.
I'm using MS SQL server 2008. I have two data base call "Adb" and "Bdb". Both date base has one same table call "commonTable". I want to update date "Adb" data base ever 15 min using "Bda" bata base "commonTable" data.
How can I do it?
Unless I completely misunderstood the point of PQsendQuery then a query my be collected in multiple chunks, I.e fragments of a big table. Therefor PQisBusy return 0 does not indicate that the query is over, in that case my question would be mood, it only indicates that some input is available. Validating this code by testing it is actually not a good idea; the code may run non blocking in lab environments but that doesn't guaranty that the code wont block in production. — JustMaximumPoweryesterday
the last sentence
I bet the OP tried to spare testing, and unfortunately my knowledge on this is next to nothing
@usr And to Paul's point, there are going to be cases where you can find gaps in functionality in the optimizer. If they're not going to be fixed, and you know a better way to write the query, use the better way. Patient: "Doctor, it hurts when I do x." Doctor: "Don't do x." :-) — Aaron Bertrand7 mins ago
@AaronBertrand ... i know you're out there listening ... watching ... just posted a comment on that question and when i hit enter, your comment appeared
I'm using Google App Engine, a Non-Relational Database (NoSQL). My question is, which is the best way to model a rank (ranking of players) using their scores?
For example, my players are:
Player { String name, int score}
I want to know the rank (position) for a player and also get the top 10 ...
Without more specifics:
CREATE PROCEDURE dbo.UpdateA
AS
BEGIN
SET NOCOUNT ON;
UPDATE A
SET col1 = B.col1 /* , ... */
FROM Adb.dbo.commonTable AS A
INNER JOIN Bdb.dbo.commonTable AS B
ON A.key_col = B.key_col;
INSERT Adb.dbo.commonTable(key_col, col1 /* , ... */)
SELE...
Point is, something like NOT EXISTS can stop looking as soon as it finds a match. LEFT JOIN has to process the full set, then there's a separate filter to find the NULLs you introduced. Ugh.
@SQLKiwi I'm not anti-semi-joinic, but the NOT EXISTS and EXCEPT are usually a lot more readable and "more declarative". Typically in the past they weren't always optimized as well. LEFT JOIN can stop sooner IF there is a constraint which would tell you there can be no more than one match.
@swasheck in this case I have no idea about querying Google AE, so if those python files are the equivalent of queries, then it should likely go to Stack Overflow as a means of "how do I sort desc on a given column?" which is basic SQL all the way if it were SQL99.
@SQLKiwi right, i knew that part. my retardedness^H^H^Hinexperience is showing now ... so if you're bringing in new rows (a la MERGE), wouldn't you want the whole set?
@SQLKiwi Not sure why he mixed the two techniques in the same answer. In this case, since it should be a unique key in each table the execution plans should be equivalent, wouldn't you think?
@swasheck The query is, "delete rows from A where there isn't a match in B'. For each row of A, not exists can stop thinking about deleting that row as soon as it finds a match in B. Left join performs the complete outer join before filtering for the NULLs.
@SQLKiwi right ... ok ... i think i found the flaw in my logic. i was thinking that it would have to resume from a RID on B to keep looking. but i was just not thinking clearly
@CadeRoux There's no reason is couldn't be rewritten, the opposite case is, there just isn't a transform for it. I asked why once, and was told it hadn't been asked for enough.
@CadeRoux If nothing else, even when looking for a PK-PK match (i.e. one row maximum) all the rows move from the outer join to the filter operator. In the ASJ operator plan, rows are rejected inside the operator. It all adds up.
The error
The type of column "XXX" conflicts with the type of other columns specified in the UNPIVOT list.
is very obvious and one of the better messages. It tells you that your UNPIVOT list contains columns of different types. Here's an example that causes such an error: SQL Fiddle
SEL...
SELECT column_name, data_type,character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTable' and TABLE_SCHEMA='yourDBname';
I'm using MS SQL server 2008. I have two data base call "Adb" and "Bdb". Both date base has one same table call "commonTable". I want to update date "Adb" data base ever 15 min using "Bda" bata base "commonTable" data.
How can I do it?
I'm using Google App Engine, a Non-Relational Database (NoSQL). My question is, which is the best way to model a rank (ranking of players) using their scores?
For example, my players are:
Player { String name, int score}
I want to know the rank (position) for a player and also get the top 10 ...
Without more specifics:
CREATE PROCEDURE dbo.UpdateA
AS
BEGIN
SET NOCOUNT ON;
UPDATE A
SET col1 = B.col1 /* , ... */
FROM Adb.dbo.commonTable AS A
INNER JOIN Bdb.dbo.commonTable AS B
ON A.key_col = B.key_col;
INSERT Adb.dbo.commonTable(key_col, col1 /* , ... */)
SELE...
