Another WTF for MS - Row Count Error

I try to get a Row Count from data flow - and get Error:
[Row Count [724]] Error: The variable "User::COUNT_NEWIMGS" specified by VariableName property is not a valid variable. Need a valid variable name to write to.

I tried setting COUNT_NEWIMGS on Package Level, on Data Flow level (Int32 datatype)
i tried specifying variable as "COUNT_NEWIMGS", "User::COUNT_NEWIMGS", "@.[COUNT_NEWIMGS]" - still the same error

And then when I'm ready to crash my keyboard (again) - I found on another forum that Row Count doesnt even update that variable until the dft is complete. WTF!!?!!

TheViewMaster wrote:

I try to get a Row Count from data flow - and get Error:
[Row Count [724]] Error: The variable "User::COUNT_NEWIMGS" specified by VariableName property is not a valid variable. Need a valid variable name to write to.

I tried setting COUNT_NEWIMGS on Package Level, on Data Flow level (Int32 datatype)
i tried specifying variable as "COUNT_NEWIMGS", "User::COUNT_NEWIMGS", "@.[COUNT_NEWIMGS]" - still the same error

Its case-sensitive. Could that be the problem?

TheViewMaster wrote:

And then when I'm ready to crash my keyboard (again) - I found on another forum that Row Count doesnt even update that variable until the dft is complete. WTF!!?!!

Well of course it doesn't. If you want to know how many rows are in a data path you have to wait until all the rows have gone through the data path before you know. How can you possibly have a complaint about that?

-Jamie

|||Please be sure that you don't have a trailing space when you type in the variable name...|||

Jamie Thomson wrote:

Well of course it doesn't. If you want to know how many rows are in a data path you have to wait until all the rows have gone through the data path before you know. How can you possibly have a complaint about that?

-Jamie

IDK - How the data flow was suppose to work that it takes in List of Images to Unzip - it checks in DB if those are required & if they are newer version. Then it uses Script component (Xceed) to unzip. Since - when you run it in BIDS - you can see no of records - I wanted to show status of progress e.g. 250 of 2500 images unzipped - 10% complete.

To my surprise - even when using zip component the most optimal way (creating an array of imagenames to unzip) - doing it in data flow was MUCH SLOWER than in Script task which unzipped images one-by-one.
Following is approximate time codes to unzip 767 images to a network share:
Data Flow - unzip 1-by-1: 2:55
Data Flow - array of imgs: 1:02
Custom Script task 1by1: 0:40

SO - thanks guys for help - but I'm not sold on data flow anymore as I have serious doubts about it's performance/usability (you can't even do a task like Update in reasonable time) - which leads me to use more SQL and application code & rely less on SSIS.

BTW - How many MS employees did it take to create the SSIS?|||

Jamie Thomson wrote:

TheViewMaster wrote:

Jamie Thomson wrote:

Well of course it doesn't. If you want to know how many rows are in a data path you have to wait until all the rows have gone through the data path before you know. How can you possibly have a complaint about that?

-Jamie

IDK - How the data flow was suppose to work that it takes in List of Images to Unzip - it checks in DB if those are required & if they are newer version. Then it uses Script component (Xceed) to unzip. Since - when you run it in BIDS - you can see no of records - I wanted to show status of progress e.g. 250 of 2500 images unzipped - 10% complete.
To my surprise - even when using zip component the most optimal way (creating an array of imagenames to unzip) - doing it in data flow was MUCH SLOWER than in Script task which unzipped images one-by-one.
Following is approximate time codes to unzip 767 images to a network share:
Data Flow - unzip 1-by-1: 2:55
Data Flow - array of imgs: 1:02
Custom Script task 1by1: 0:40
SO - thanks guys for help - but I'm not sold on data flow anymore as I have serious doubts about it's performance/usability (you can't even do a task like Update in reasonable time) - which leads me to use more SQL and application code & rely less on SSIS.
BTW - How many MS employees did it take to create the SSIS?

Huh? You're trying to unzip files within the data-flow? Why would you try and do that?

The data-flow is for moving data. Unzipping files is all about preparing data and no way should that be done in the pipeline. I'm worried that there's some documentation out there somewhere that leads you to think that you SHOULD be doing this. If there is, please point me to it and we'll go about getting it changed.

Why do you continually try and blame anyone but yourself for your own misunderstanding? I'm not trying to blame you for anything, if you misunderstand something then that's ok, let's try and put it right, but there are right and wrong ways of achieving things and in this instance you are trying to achieve something in the wrong way. You can't blame Microsoft for that.

And quite frankly I'm also sick to death of your sarcastic pithy little put-downs of Microsoft and the SSIS team as well. If you have gripes then address them in the right way but veiled insults to people that you've never met when you're hidden behind the anonymity of a computer screen isn't going to endear me to you and therefore I'm less likely to help you out (which I believe I have done quite a bit). Criticism is welcome if its constructive and valid. If its neither of those things, please don't bother.

There's a case in point right here on this thread. I see that you have marked Phil's original answer as correct which means that this was your mistake. Hardly warrants a title of "Another WTF for MS" does it?

You're right, you can't do updates from the pipeline particularly quickly. This is largely inherent in the very nature of an UPDATE and no ETL tool in the world will treat it any differently. With SSIS you have a perfectly valid and performant workaround - stage it to a transient table and issue a set-based update.

-Jamie

|||My bad - sorry I got overly frustrated.

Thanks for all the help - appreciate it

Another thorny summing problem

I'm trying to generate a "rollup" of stock positions based on the trades to
date. I want to generate a row for every month where there was any activity.
Some of the trades have null shares, however. So I did this (and before I get
snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
cut and paste it exactly)...
SELECT stockId,
CASE
WHEN ABS(SUM(f.units)) > 0.01
THEN SUM(f.units)
ELSE 0
END AS openingposition
FROM tblTrans
WHERE tdate < [a date provided by a dialog box in code]
GROUP BY stockId
The problem I'm having is that "opening" months, when you first buy a stock,
don't generate a row. That's because I was generating the opening position by
looking for all trades before the start of the month (the sum of which is
your current position). The obvious solution was to do two rollups, for
opening and closing positions, something like...
SELECT stockId,
CASE when tdate < [startofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
openingposition,
CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
closingposition
FROM tblTrans
WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
GROUP BY stockId
But this doesn't work, it generates multiple rows per stock. I don't
understand exactly why it does this, nor do the number seem to have any
bearing on reality. I suspect the outer CASE on the dates has something to do
with it, and I have to put a similar case in the GROUP perhaps?
Perhaps this is just the wrong way to do this? Two subqueries perhaps?
Maury
I tried my own advice and some fiddling later and I have a solution.
The working idea is to use two subqueries, one summing everything to the
start of the period, another to the end, and then grouping the results on
stock. The only real trick in there was that the opening position could be
NULL, which resulted in odd outcomes. I added CASEs on the outside to force
them to zero, and presto!
Maury
|||If you are still looking for a solution, can you provide the table
schema and some sample data? It would be helpful to arrive at the
solution.
Thanks
Manisha Gandhi
Maury Markowitz wrote:
> I'm trying to generate a "rollup" of stock positions based on the trades to
> date. I want to generate a row for every month where there was any activity.
> Some of the trades have null shares, however. So I did this (and before I get
> snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
> cut and paste it exactly)...
> SELECT stockId,
> CASE
> WHEN ABS(SUM(f.units)) > 0.01
> THEN SUM(f.units)
> ELSE 0
> END AS openingposition
> FROM tblTrans
> WHERE tdate < [a date provided by a dialog box in code]
> GROUP BY stockId
> The problem I'm having is that "opening" months, when you first buy a stock,
> don't generate a row. That's because I was generating the opening position by
> looking for all trades before the start of the month (the sum of which is
> your current position). The obvious solution was to do two rollups, for
> opening and closing positions, something like...
> SELECT stockId,
> CASE when tdate < [startofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
> openingposition,
> CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
> closingposition
> FROM tblTrans
> WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
> GROUP BY stockId
> But this doesn't work, it generates multiple rows per stock. I don't
> understand exactly why it does this, nor do the number seem to have any
> bearing on reality. I suspect the outer CASE on the dates has something to do
> with it, and I have to put a similar case in the GROUP perhaps?
> Perhaps this is just the wrong way to do this? Two subqueries perhaps?
> Maury

Another thorny summing problem

I'm trying to generate a "rollup" of stock positions based on the trades to
date. I want to generate a row for every month where there was any activity.
Some of the trades have null shares, however. So I did this (and before I get
snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
cut and paste it exactly)...
SELECT stockId,
CASE
WHEN ABS(SUM(f.units)) > 0.01
THEN SUM(f.units)
ELSE 0
END AS openingposition
FROM tblTrans
WHERE tdate < [a date provided by a dialog box in code]
GROUP BY stockId
The problem I'm having is that "opening" months, when you first buy a stock,
don't generate a row. That's because I was generating the opening position by
looking for all trades before the start of the month (the sum of which is
your current position). The obvious solution was to do two rollups, for
opening and closing positions, something like...
SELECT stockId,
CASE when tdate < [startofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
openingposition,
CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
closingposition
FROM tblTrans
WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
GROUP BY stockId
But this doesn't work, it generates multiple rows per stock. I don't
understand exactly why it does this, nor do the number seem to have any
bearing on reality. I suspect the outer CASE on the dates has something to do
with it, and I have to put a similar case in the GROUP perhaps?
Perhaps this is just the wrong way to do this? Two subqueries perhaps?
MauryI tried my own advice and some fiddling later and I have a solution.
The working idea is to use two subqueries, one summing everything to the
start of the period, another to the end, and then grouping the results on
stock. The only real trick in there was that the opening position could be
NULL, which resulted in odd outcomes. I added CASEs on the outside to force
them to zero, and presto!
Maury|||If you are still looking for a solution, can you provide the table
schema and some sample data? It would be helpful to arrive at the
solution.
Thanks
Manisha Gandhi
Maury Markowitz wrote:
> I'm trying to generate a "rollup" of stock positions based on the trades to
> date. I want to generate a row for every month where there was any activity.
> Some of the trades have null shares, however. So I did this (and before I get
> snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
> cut and paste it exactly)...
> SELECT stockId,
> CASE
> WHEN ABS(SUM(f.units)) > 0.01
> THEN SUM(f.units)
> ELSE 0
> END AS openingposition
> FROM tblTrans
> WHERE tdate < [a date provided by a dialog box in code]
> GROUP BY stockId
> The problem I'm having is that "opening" months, when you first buy a stock,
> don't generate a row. That's because I was generating the opening position by
> looking for all trades before the start of the month (the sum of which is
> your current position). The obvious solution was to do two rollups, for
> opening and closing positions, something like...
> SELECT stockId,
> CASE when tdate < [startofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
> openingposition,
> CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
> closingposition
> FROM tblTrans
> WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
> GROUP BY stockId
> But this doesn't work, it generates multiple rows per stock. I don't
> understand exactly why it does this, nor do the number seem to have any
> bearing on reality. I suspect the outer CASE on the dates has something to do
> with it, and I have to put a similar case in the GROUP perhaps?
> Perhaps this is just the wrong way to do this? Two subqueries perhaps?
> Maury

Another thorny summing problem

I'm trying to generate a "rollup" of stock positions based on the trades to
date. I want to generate a row for every month where there was any activity.
Some of the trades have null shares, however. So I did this (and before I ge
t
snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
cut and paste it exactly)...
SELECT stockId,
CASE
WHEN ABS(SUM(f.units)) > 0.01
THEN SUM(f.units)
ELSE 0
END AS openingposition
FROM tblTrans
WHERE tdate < [a date provided by a dialog box in code]
GROUP BY stockId
The problem I'm having is that "opening" months, when you first buy a stock,
don't generate a row. That's because I was generating the opening position b
y
looking for all trades before the start of the month (the sum of which is
your current position). The obvious solution was to do two rollups, for
opening and closing positions, something like...
SELECT stockId,
CASE when tdate < [startofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
openingposition,
CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
closingposition
FROM tblTrans
WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
GROUP BY stockId
But this doesn't work, it generates multiple rows per stock. I don't
understand exactly why it does this, nor do the number seem to have any
bearing on reality. I suspect the outer CASE on the dates has something to d
o
with it, and I have to put a similar case in the GROUP perhaps?
Perhaps this is just the wrong way to do this? Two subqueries perhaps?
MauryI tried my own advice and some fiddling later and I have a solution.
The working idea is to use two subqueries, one summing everything to the
start of the period, another to the end, and then grouping the results on
stock. The only real trick in there was that the opening position could be
NULL, which resulted in odd outcomes. I added CASEs on the outside to force
them to zero, and presto!
Maury|||If you are still looking for a solution, can you provide the table
schema and some sample data? It would be helpful to arrive at the
solution.
Thanks
Manisha Gandhi
Maury Markowitz wrote:
> I'm trying to generate a "rollup" of stock positions based on the trades t
o
> date. I want to generate a row for every month where there was any activit
y.
> Some of the trades have null shares, however. So I did this (and before I
get
> snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
> cut and paste it exactly)...
> SELECT stockId,
> CASE
> WHEN ABS(SUM(f.units)) > 0.01
> THEN SUM(f.units)
> ELSE 0
> END AS openingposition
> FROM tblTrans
> WHERE tdate < [a date provided by a dialog box in code]
> GROUP BY stockId
> The problem I'm having is that "opening" months, when you first buy a stoc
k,
> don't generate a row. That's because I was generating the opening position
by
> looking for all trades before the start of the month (the sum of which is
> your current position). The obvious solution was to do two rollups, for
> opening and closing positions, something like...
> SELECT stockId,
> CASE when tdate < [startofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
> openingposition,
> CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
> closingposition
> FROM tblTrans
> WHERE tdate < [endOfMonth[a date provided by a dialog box in code]
)]
> GROUP BY stockId
> But this doesn't work, it generates multiple rows per stock. I don't
> understand exactly why it does this, nor do the number seem to have any
> bearing on reality. I suspect the outer CASE on the dates has something to
do
> with it, and I have to put a similar case in the GROUP perhaps?
> Perhaps this is just the wrong way to do this? Two subqueries perhaps?
> Maury

Another row to col issue

Hi,

I've got a Problem to craete a view which works like this:

I've got this Table:

USR_ID ATNAM ATVAL
-- -
2 USR_DEPARTMENT BS1
2 USR_MARITAL_STATE married
3 USR_DEPARTMENT BS1
3 USR_MARITAL_STATE single

and I have to display it in a view like this

USR_ID USR_DEPARTMENT USR_MARITAL_STATE
- - -
2 BS1 married
3 BS1 single

maybe there will be additional "ATNAMS" in future.

Has anybode an idea how i can achieve this?

thanks in advance

Raimund

Is there a reason that you cannot use PIVOT? Are you using SQL Server 2000 or SQL Server 2005?

With SQL Server 2005 you can create a view based on a pivot; it might look something like:

Code Snippet

create view dbo.aView
as
select USR_ID,
[USR_DEPARTMENT],
[USR_MARITAL_STATE]
from source
pivot( max(ATVAL) for ATNAM
in([USR_DEPARTMENT],[USR_MARITAL_STATE])
) pv

The MAX / CASE construct can be used either with SQL 2000 or SQL 2005; that version might look something like:

Code Snippet

create view dbo.aView
as

select USR_ID,
max( case when ATNAM = 'USR_DEPARTMENT'
then ATVAL end)
as USR_MARITAL_STATE,
max( case when ATNAM = 'USR_MARITAL_STATE'
then ATVAL end)
as USR_MARITAL_STATE
from source
group by USR_ID

|||

I just didn't know about the trick with te max statement used as aggregate function.

For dynamic adaption for new "ATNAMS" I'll try it with a trigger on the according definition table.

Thanks for fast reply

Raimund

Another Row After SubTotal in Matrix

I have a Matrix in Report, with SubTotals of rows and columns.

I want to add another row below the subtotal (as well as another column to the left) in order to display percentage. Could anyone please tell me how?

There is a work around. you need to add one more column and display percentage in that column, this would add a column after total column and would display the sum of percentage column.

Then you reduce the width of the column (the column that you have manually added) to 0.

|||

First, Thanks for your reply.

I Tried it, but it doesn't work.

When I set the width of the added column to 0, the total row becomes 0 too.

?

Another 101 question

With a plain select if no rows are returned then the row count is 0, but
with a select that assigns to a variable you will get a rowcount of one even
though no actual row was found. I am guessing that is because of assigning
to a variable SQL will return an empty result set, which is a result set of
1, is this correct?
i.e. SET @.mycol =
(SELECT col FROM myTable WHERE id = 1)
Under these circumstances is the best technique to just test the variable
@.mycol for a null value
OR write the select like:
IF EXISTS
(SELECT mycol FROM myTable
WHERE myID = 3)
BEGIN
SET @.mycol =
(SELECT mycol FROM myTable
WHERE myID = 3)
PRINT '@.mycol : ' + CAST(mycol as varchar(15))
END
ELSE
PRINT 'ROW DOES NOT EXIST'
Or is there an even better and/or more professional way to do it?Thank you! I was making my self nuts with the what if's, it seemed that
going beyond validating the current entry could turn into a never ending
task. :)
Except for the news groups I am learning in a vacuum, it is not like
learning in maintenance of a production environment where you get to see
what others have done.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eb6rNDKvFHA.3896@.TK2MSFTNGP15.phx.gbl...
> Thanks Erland, I really should have mentioned that.
> Dazed,
> If you have a Unique constraint you should not have to test for
> duplicates when selecting the values out. The constraint will make sure
> there are no duplicates in the first place.
> --
> Andrew J. Kelly SQL MVP
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96D61468ABF3Yazorman@.127.0.0.1...
>|||What is the desired behavior? DO you simply want to know if one or more
rows exist or not? If so then always use EXISTS.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> With a plain select if no rows are returned then the row count is 0, but
> with a select that assigns to a variable you will get a rowcount of one
> even though no actual row was found. I am guessing that is because of
> assigning to a variable SQL will return an empty result set, which is a
> result set of 1, is this correct?
> i.e. SET @.mycol =
> (SELECT col FROM myTable WHERE id = 1)
> Under these circumstances is the best technique to just test the variable
> @.mycol for a null value
> OR write the select like:
> IF EXISTS
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> BEGIN
> SET @.mycol =
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> PRINT '@.mycol : ' + CAST(mycol as varchar(15))
> END
> ELSE
> PRINT 'ROW DOES NOT EXIST'
> Or is there an even better and/or more professional way to do it?
>|||When you use a scalar subquery, you can get a one-row, one-column table
that is converted to a scalar; you can get an empty table that is
converted to a NULL; you can get a multi-row, one-column table that
gives a cardinality error when you try to put it into a scalar.|||In this paticular situation I want the field value. I was wondering if it
was better to just do the select and test the variable for a null or do a
EXISTS SELECT and then if it does exist select into the variable.
I'm just learning and trying to find the best way to build a mouse trap,
I've found this news group very helpful. i.e. I had a 130 line procedure
yesterday that was cut down to about 30 from information obtained from the
group. There are a lot of ways to get things to work, some are a lot better
than others. I'm looking for the right ways.
Thank you!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
> What is the desired behavior? DO you simply want to know if one or more
> rows exist or not? If so then always use EXISTS.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||OK then there is no need for an EXISTS first. You can do this several ways
like:
SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
or
SELECT @.mycol = Col FROM myTable WHERE id = 1
IF @.myCol IS NOT NULL
BEGIN
-- Do your thing here
END
ELSE
...
Also make sure that there will only be at most 1 row returned. As long as
ID is a unique value you should be ok.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
> In this paticular situation I want the field value. I was wondering if it
> was better to just do the select and test the variable for a null or do a
> EXISTS SELECT and then if it does exist select into the variable.
> I'm just learning and trying to find the best way to build a mouse trap,
> I've found this news group very helpful. i.e. I had a 130 line procedure
> yesterday that was cut down to about 30 from information obtained from the
> group. There are a lot of ways to get things to work, some are a lot
> better than others. I'm looking for the right ways.
> Thank you!!
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
>|||Thank you again! id is unique, but I am checking if rowcount is > 1 anyways
and plan on throwing an error if it is. I plan on temporarily removing the
unique constraint and putting in a dupe record to test it.
Is that going too far?
Or is it a good idea to try to handle a situation that in theory should
never happen?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMkfpNIvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> OK then there is no need for an EXISTS first. You can do this several
> ways like:
> SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
> or
> SELECT @.mycol = Col FROM myTable WHERE id = 1
>
> IF @.myCol IS NOT NULL
> BEGIN
> -- Do your thing here
> END
> ELSE
> ...
> Also make sure that there will only be at most 1 row returned. As long as
> ID is a unique value you should be ok.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
>|||DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> Thank you again! id is unique, but I am checking if rowcount is > 1
> anyways and plan on throwing an error if it is. I plan on temporarily
> removing the unique constraint and putting in a dupe record to test it.
> Is that going too far?
> Or is it a good idea to try to handle a situation that in theory should
> never happen?
OK, now we are in for a real treat! I will show you how to do it, and if
that does not convince you that are going too far, nothing will. :-)
Andy showed you two ways, but they are a little different, which he failed
to tell. Let's look at them again:
0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
many rows -> You will get an error, "subquery returned more than one value".
0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
1 row -> @.mycol assigned the value, @.@.rowcount = 1
many rows -> @.mycol gets the last value in the result set, which that
is undefined unless you have an ORDER BY. @.@.rowcount is
set to the number of matching rows.
Look at 0 rows again:
SELECT @.mycol = 4711
SELECT @.mycol = Col FROM myTable WHERE id = 1
If there is no row with id = 1, @.mycol will remain 4711, it will not be
set to NULL.
If you really want to check for duplicates, and handle the situation
yourself, it is the SELECT assignment you want to use.
If you keep the constraints, which you should unless you have very good
reasons, the SET method is a little safer. Then again, if you know how
SELECT behaves you can be careful make sure variable is NULL before
you use it. (Yet then again, that is a trap that even season T-SQL
programmers fall into, every now and then!)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I really should have mentioned that.
Dazed,
If you have a Unique constraint you should not have to test for
duplicates when selecting the values out. The constraint will make sure
there are no duplicates in the first place.
Andrew J. Kelly SQL MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thank you for the SET/SELECT behavior. Your reply seemed to imply (reading
in between the lines) that since the the id is UNIQUE don't bother to check
for multiple rows, SQL will error anyways in the unlikely event.
If there are duplicates in a unique column, then there is data corruption
anyways, pretty messages aren't really going to help the application faling,
it is time to contact the DBA to see why the data is corrupt.
Is that right?
I'm going nuts creating a procedure that checks for both bad and/or
duplicate data being passed into it and handling for corrupt database
information that should not happen. Seems like opening pandora's box when I
try to code pretty returns to notify the application that the database is
corrupt.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>