Is this newgroup monitored at all

I know that Bruce Loehle-Conger (MVP) answers a lot of the questions posted
to this newsgroup, but I have posted 3 messages in the past 2 weeks or so
and gotten no replies at all. I know there are no guarantess in life ;-) but
is there anyone who regularly monitors this newsgroup to make sure posted
questions are being answered? I don't think my questions were difficult or
unreasonable, but not having answers is hampering my development efforts
with SSRS.
Thanks in advance for any input anyone can provide,
Chris GeissMS started the web based forums and pretty much that is the only place MS
people hang out and answer question with one big exception. If you are doing
managed newsgroups (have a MSDN subscription) then this is the place you
have to post and then an answer is guaranteed.
If you have MSDN subscription you should look into that. Otherwise, I
suggest trying the web based forums:
http://msdn.microsoft.com/sql/support/forums/
I haven't hung out there much partly because I don't like web interfaces.
The volume is going down here though so I expect to eventually move over
there as well.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris Geiss" <cgeiss@.sssi.us> wrote in message
news:uoJFt%233cGHA.4912@.TK2MSFTNGP05.phx.gbl...
>I know that Bruce Loehle-Conger (MVP) answers a lot of the questions posted
>to this newsgroup, but I have posted 3 messages in the past 2 weeks or so
>and gotten no replies at all. I know there are no guarantess in life ;-)
>but is there anyone who regularly monitors this newsgroup to make sure
>posted questions are being answered? I don't think my questions were
>difficult or unreasonable, but not having answers is hampering my
>development efforts with SSRS.
> Thanks in advance for any input anyone can provide,
> Chris Geiss
>
>|||After reading this I just created an alias account through my Microsoft
Subscription and am now posting through it from Outlook Express. So exactly
what does this mean? I used to post here through Google Groups; I'm just
wondering what the difference is...
Thanks
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uLlcEL4cGHA.4276@.TK2MSFTNGP03.phx.gbl...
> MS started the web based forums and pretty much that is the only place MS
> people hang out and answer question with one big exception. If you are
> doing managed newsgroups (have a MSDN subscription) then this is the place
> you have to post and then an answer is guaranteed.
> If you have MSDN subscription you should look into that. Otherwise, I
> suggest trying the web based forums:
> http://msdn.microsoft.com/sql/support/forums/
> I haven't hung out there much partly because I don't like web interfaces.
> The volume is going down here though so I expect to eventually move over
> there as well.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Chris Geiss" <cgeiss@.sssi.us> wrote in message
> news:uoJFt%233cGHA.4912@.TK2MSFTNGP05.phx.gbl...
>>I know that Bruce Loehle-Conger (MVP) answers a lot of the questions
>>posted to this newsgroup, but I have posted 3 messages in the past 2 weeks
>>or so and gotten no replies at all. I know there are no guarantess in life
>>;-) but is there anyone who regularly monitors this newsgroup to make sure
>>posted questions are being answered? I don't think my questions were
>>difficult or unreasonable, but not having answers is hampering my
>>development efforts with SSRS.
>> Thanks in advance for any input anyone can provide,
>> Chris Geiss
>>
>>
>|||If you have gone through the MSDN process for managed newsgroups then if you
are the originator of the post (so if you add on a question to another
existing thread it is not monitored) they monitor it and make sure you get
an answer. I believe they guarantee in two days? The MSDN site specifies
this.
I have two newsgroups setup in Outlook Express. My normal one and then one
for managed postings when I have a question for them.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"xcolin" <xcolin@.noemail.noemail> wrote in message
news:OVXbXg6cGHA.4264@.TK2MSFTNGP05.phx.gbl...
> After reading this I just created an alias account through my Microsoft
> Subscription and am now posting through it from Outlook Express. So
> exactly what does this mean? I used to post here through Google Groups;
> I'm just wondering what the difference is...
> Thanks
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uLlcEL4cGHA.4276@.TK2MSFTNGP03.phx.gbl...
>> MS started the web based forums and pretty much that is the only place MS
>> people hang out and answer question with one big exception. If you are
>> doing managed newsgroups (have a MSDN subscription) then this is the
>> place you have to post and then an answer is guaranteed.
>> If you have MSDN subscription you should look into that. Otherwise, I
>> suggest trying the web based forums:
>> http://msdn.microsoft.com/sql/support/forums/
>> I haven't hung out there much partly because I don't like web interfaces.
>> The volume is going down here though so I expect to eventually move over
>> there as well.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Chris Geiss" <cgeiss@.sssi.us> wrote in message
>> news:uoJFt%233cGHA.4912@.TK2MSFTNGP05.phx.gbl...
>>I know that Bruce Loehle-Conger (MVP) answers a lot of the questions
>>posted to this newsgroup, but I have posted 3 messages in the past 2
>>weeks or so and gotten no replies at all. I know there are no guarantess
>>in life ;-) but is there anyone who regularly monitors this newsgroup to
>>make sure posted questions are being answered? I don't think my questions
>>were difficult or unreasonable, but not having answers is hampering my
>>development efforts with SSRS.
>> Thanks in advance for any input anyone can provide,
>> Chris Geiss
>>
>>
>>
>

Is this error something to worry about?

Hi Everyone,
One of my clients started getting error messages in their "Database
Maintenance Plan" that has me concerned.
Databases: master & msdb (two error messages but same codes)
Activity: Check Data and Index Linkage
Error number: 7919
Message: Repair statement not processed. Database needs to be in single
user mode.
I checked Microsoft knowledgebase and it appears that this is a known issue
http://support.microsoft.com/default...;en-us;Q290622
What concerns me is this never happened before to any of my clients and
it seems serious as it affects key system databases Master and msdb.
Should I be worried?
Thanks
Richard
hi Richard,
Richard Fagen wrote:
> ...
> What concerns me is this never happened before to any of my clients
> and
> it seems serious as it affects key system databases Master and msdb.
> Should I be worried?
hopefully not, but you actually and unfortunately have no workaround for
that...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
I suspected as much, but I wanted confirmation from the experts
Maybe I'll convince them to upgrade to SQL 2005 later in the year.
I know ISA 2004 will be included in SBS SP1 for free! (expected to be
almost 400M!) Do you think Microsoft would be generous with SQL 2005 or
maybe offer it at a reduced price for SBS 2003 users?
Thanks for your help
Richard
Andrea Montanari wrote:
> hi Richard,
> hopefully not, but you actually and unfortunately have no workaround for
> that...
|||hi Richard,
Richard Fagen wrote:
> Hi Andrea,
> I know ISA 2004 will be included in SBS SP1 for free! (expected to be
> almost 400M!) Do you think Microsoft would be generous with SQL 2005
> or maybe offer it at a reduced price for SBS 2003 users?
I suspect this is out of my concerns :D:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Is this DELETE possible?

I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.

DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)

Error msg: The table 'TableA' is ambiguous.

Can this be done with SQL or should I use T-SQL with cursors here?Try EXISTS or IN

DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_2 = y.col_1)

Or did you want:

DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_1 = y.col_2)

As always, I recommend you test with SELECT queries first. (No warrantees
implied, etc...)

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407131933.72eea682@.posting.google.c om...
I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.

DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)

Error msg: The table 'TableA' is ambiguous.

Can this be done with SQL or should I use T-SQL with cursors here?|||Try:

DELETE FROM x
FROM TableA x
INNER JOIN TableA y ON (x.col_1 = y.col_2)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407131933.72eea682@.posting.google.c om...
> I am getting error messages when I try to delete from a table using
> the values in the table itself. The intent is to delete all rows from
> TableA where col_2 matches any of the col_1 values.
> DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
> y.col_2)
> Error msg: The table 'TableA' is ambiguous.
> Can this be done with SQL or should I use T-SQL with cursors here?|||Aaron,

Thanks for the tip. It worked!

On a related note, I am facing the same error when I try to update
TableA with data from the same TableA.

The command I used is this:

UPDATE TableA SET col_2 = y.col_2
FROM TableA x INNER JOIN TableA y
ON (x.col_1 = y.col_1)

Error message is: The table 'TableA' is ambiguous.

Do you have a similar solution?

"Aaron W. West" <tallpeak@.hotmail.NO.SPAM> wrote in message news:<P4udnTGzyaWGIWndRVn-hA@.speakeasy.net>...
> Try EXISTS or IN
> DELETE TableA
> WHERE EXISTS (SELECT * FROM TableA y
> WHERE TableA.col_2 = y.col_1)
> As always, I recommend you test with SELECT queries first. (No warrantees
> implied, etc...)|||On 14 Jul 2004 07:59:43 -0700, php newbie wrote:

> Aaron,
> Thanks for the tip. It worked!
> On a related note, I am facing the same error when I try to update
> TableA with data from the same TableA.
> The command I used is this:
> UPDATE TableA SET col_2 = y.col_2
> FROM TableA x INNER JOIN TableA y
> ON (x.col_1 = y.col_1)
> Error message is: The table 'TableA' is ambiguous.
> Do you have a similar solution?

If you're using "x" as a label for TableA, you need to use it throughout.

UPDATE x SET x.col_2 = y.col_2
FROM TableA x
INNER JOIN TableA y
ON (x.col_1 = y.col_1)|||I also got it to work like this:

CREATE TABLE #T (A int,B int)
INSERT #T SELECT 1,2
INSERT #T SELECT 2,2
INSERT #T SELECT 3,3
INSERT #T SELECT 4,3
INSERT #T SELECT 5,3
INSERT #T SELECT 6,4
INSERT #T SELECT 7,4

SELECT * FROM #T WHERE A IN (SELECT DISTINCT B FROM #T)

DELETE FROM #T WHERE A IN (SELECT DISTINCT B FROM #T)|||>> On a related note, I am facing the same error when I try to update
TableA with data from the same TableA. <<

Why in the world do you think that SQL has a FROM clause in UPDATE and
DELETE? You are writing unpredictable, proprietary code that EVEN
IF IT WAS ALLOWED, would not produce results.

There is no FROM clause in a Standard SQL UPDATE statement; it would
make no sense. Other products (SQL Server, Sybase and Ingres) also
use the UPDATE .. FROM syntax, but with different semantics. So it
does not port, or even worse, when you do move it, it trashes your
database. Other programmers cannot read it and maintaining it is
harder. And when Microsoft decides to change it, you will have to do
a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
conversions? The last time the UPDATE FROM changed?

The correct syntax for a searched update statement is

<update statement> ::=
UPDATE <table name>
SET <set clause list>
[WHERE <search condition>]

<set clause list> ::=
<set clause> [{ , <set clause> }...]

<set clause> ::= <object column> = <update source
<update source> ::= <value expression> | NULL | DEFAULT

<object column> ::= <column name
The UPDATE clause simply gives the name of the base table or updatable
view to be changed.

Notice that no correlation name is allowed in the UPDATE clause; this
is to avoid some self-referencing problems that could occur. But it
also follows the data model in Standard SQL. When you give a table
expression a correlation name, it is to act as if a materialized table
with that correlation name has been created in the database. That
table then is dropped at the end of the statement. If you allowed
correlation names in the UPDATE clause, you would be updating the
materialized table, which would then disappear and leave the base
table untouched.

The SET clause is a list of columns to be changed or made; the WHERE
clause tells the statement which rows to use. For this discussion, we
will assume the user doing the update has applicable UPDATE privileges
for each <object column>.

* The WHERE Clause

As mentioned, the most important thing to remember about the WHERE
clause is that it is optional. If there is no WHERE clause, all rows
in the table are changed. This is a common error; if you make it,
immediately execute a ROLLBACK statement.

All rows that test TRUE for the <search condition> are marked as a
subset and not as individual rows. It is also possible that this
subset will be empty. This subset is used to construct a new set of
rows that will be inserted into the table when the subset is deleted
from the table. Note that the empty subset is a valid update that
will fire declarative referential actions and triggers.

* The SET Clause

Each assignment in the <set clause list> is executed in parallel and
each SET clause changes all the qualified rows at once. Or at least
that is the theoretical model. In practice, implementations will
first mark all of the qualified rows in the table in one pass, using
the WHERE clause. If there were no problems, then the SQL engine
makes a copy of each marked row in working storage. Each SET clause
is executed based on the old row image and the results are put in the
new row image. Finally, the old rows are deleted and the new rows are
inserted. If an error occurs during all of this, then system does a
ROLLBACK, the table is left unchanged and the errors are reported.
This parallelism is not like what you find in a traditional
third-generation programming language, so it may be hard to learn.
This feature lets you write a statement that will swap the values in
two columns, thus:

UPDATE MyTable
SET a = b, b = a;

This is not the same thing as

BEGIN ATOMIC
UPDATE MyTable
SET a = b;
UPDATE MyTable
SET b = a;
END;

In the first UPDATE, columns a and b will swap values in each row. In
the second pair of UPDATEs, column a will get all of the values of
column b in each row. In the second UPDATE of the pair, a, which now
has the same value as the original value of b, will be written back
into column b -- no change at all. There are some limits as to what
the value expression can be. The same column cannot appear more than
once in a <set clause list> -- which makes sense, given the parallel
nature of the statement. Since both go into effect at the same time,
you would not know which SET clause to use.

If a subquery expression is used in a <set clause>, and it returns a
single value, the result set is cast to a scalar; if it returns an
empty, the result set is cast to a NULL; if it returns multiple rows,
a cardinality violation is raised.

Same logic for the basic delete statement:

DELETE FROM Foobar
WHERE EXISTS
(SELECT *
FROM Foobar AS F1
WHERE Foobar.col_1 = F1.col_2)|||Dan,

This works beautifully and also applies directly to the update query. Thanks a lot!

My gratitudes also go to Russ and Jim. I appreciate your help.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<kZ9Jc.2879$Qu5.1593@.newsread2.news.pas.earthlink.n et>...
> Try:
> DELETE FROM x
> FROM TableA x
> INNER JOIN TableA y ON (x.col_1 = y.col_2)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||newtophp2000@.yahoo.com (php newbie) wrote in message news:<124f428e.0407131933.72eea682@.posting.google.com>...
> I am getting error messages when I try to delete from a table using
> the values in the table itself. The intent is to delete all rows from
> TableA where col_2 matches any of the col_1 values.
> DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
> y.col_2)
> Error msg: The table 'TableA' is ambiguous.
> Can this be done with SQL or should I use T-SQL with cursors here?

Hi,
try this:
DELETE x FROM TableA AS x INNER JOIN TableA AS y ON
(x.col_1 = y.col_2)

With best regards!|||newtophp2000@.yahoo.com (php newbie) wrote in message news:<124f428e.0407131933.72eea682@.posting.google.com>...
> I am getting error messages when I try to delete from a table using
> the values in the table itself. The intent is to delete all rows from
> TableA where col_2 matches any of the col_1 values.
> DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
> y.col_2)
> Error msg: The table 'TableA' is ambiguous.
> Can this be done with SQL or should I use T-SQL with cursors here?

Hi,
try this:
DELETE x FROM TableA AS x INNER JOIN TableA AS y ON
(x.col_1 = y.col_2)

With best regards!|||> Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> conversions?

Joe,

Just out of curiosity, was LEFT OUTER JOIN (et. al.) always part of the ANSI
standard? If so, why do you think major database vendors such as Microsoft,
Sybase and Oracle choose proprietary syntax?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:18c7b3c2.0407141855.550aba73@.posting.google.c om...
> >> On a related note, I am facing the same error when I try to update
> TableA with data from the same TableA. <<
> Why in the world do you think that SQL has a FROM clause in UPDATE and
> DELETE? You are writing unpredictable, proprietary code that EVEN
> IF IT WAS ALLOWED, would not produce results.
> There is no FROM clause in a Standard SQL UPDATE statement; it would
> make no sense. Other products (SQL Server, Sybase and Ingres) also
> use the UPDATE .. FROM syntax, but with different semantics. So it
> does not port, or even worse, when you do move it, it trashes your
> database. Other programmers cannot read it and maintaining it is
> harder. And when Microsoft decides to change it, you will have to do
> a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> conversions? The last time the UPDATE FROM changed?
> The correct syntax for a searched update statement is
> <update statement> ::=
> UPDATE <table name>
> SET <set clause list>
> [WHERE <search condition>]
> <set clause list> ::=
> <set clause> [{ , <set clause> }...]
> <set clause> ::= <object column> = <update source>
> <update source> ::= <value expression> | NULL | DEFAULT
> <object column> ::= <column name>
> The UPDATE clause simply gives the name of the base table or updatable
> view to be changed.
> Notice that no correlation name is allowed in the UPDATE clause; this
> is to avoid some self-referencing problems that could occur. But it
> also follows the data model in Standard SQL. When you give a table
> expression a correlation name, it is to act as if a materialized table
> with that correlation name has been created in the database. That
> table then is dropped at the end of the statement. If you allowed
> correlation names in the UPDATE clause, you would be updating the
> materialized table, which would then disappear and leave the base
> table untouched.
> The SET clause is a list of columns to be changed or made; the WHERE
> clause tells the statement which rows to use. For this discussion, we
> will assume the user doing the update has applicable UPDATE privileges
> for each <object column>.
> * The WHERE Clause
> As mentioned, the most important thing to remember about the WHERE
> clause is that it is optional. If there is no WHERE clause, all rows
> in the table are changed. This is a common error; if you make it,
> immediately execute a ROLLBACK statement.
> All rows that test TRUE for the <search condition> are marked as a
> subset and not as individual rows. It is also possible that this
> subset will be empty. This subset is used to construct a new set of
> rows that will be inserted into the table when the subset is deleted
> from the table. Note that the empty subset is a valid update that
> will fire declarative referential actions and triggers.
> * The SET Clause
> Each assignment in the <set clause list> is executed in parallel and
> each SET clause changes all the qualified rows at once. Or at least
> that is the theoretical model. In practice, implementations will
> first mark all of the qualified rows in the table in one pass, using
> the WHERE clause. If there were no problems, then the SQL engine
> makes a copy of each marked row in working storage. Each SET clause
> is executed based on the old row image and the results are put in the
> new row image. Finally, the old rows are deleted and the new rows are
> inserted. If an error occurs during all of this, then system does a
> ROLLBACK, the table is left unchanged and the errors are reported.
> This parallelism is not like what you find in a traditional
> third-generation programming language, so it may be hard to learn.
> This feature lets you write a statement that will swap the values in
> two columns, thus:
> UPDATE MyTable
> SET a = b, b = a;
> This is not the same thing as
> BEGIN ATOMIC
> UPDATE MyTable
> SET a = b;
> UPDATE MyTable
> SET b = a;
> END;
> In the first UPDATE, columns a and b will swap values in each row. In
> the second pair of UPDATEs, column a will get all of the values of
> column b in each row. In the second UPDATE of the pair, a, which now
> has the same value as the original value of b, will be written back
> into column b -- no change at all. There are some limits as to what
> the value expression can be. The same column cannot appear more than
> once in a <set clause list> -- which makes sense, given the parallel
> nature of the statement. Since both go into effect at the same time,
> you would not know which SET clause to use.
> If a subquery expression is used in a <set clause>, and it returns a
> single value, the result set is cast to a scalar; if it returns an
> empty, the result set is cast to a NULL; if it returns multiple rows,
> a cardinality violation is raised.
> Same logic for the basic delete statement:
> DELETE FROM Foobar
> WHERE EXISTS
> (SELECT *
> FROM Foobar AS F1
> WHERE Foobar.col_1 = F1.col_2)|||Glad it helped.

--
Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407141856.659b70a8@.posting.google.c om...
> Dan,
> This works beautifully and also applies directly to the update query.
Thanks a lot!
> My gratitudes also go to Russ and Jim. I appreciate your help.|||Dan,

Both implementations pre-dated the '92 standard.

VC

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%vHJc.4441$Qu5.433@.newsread2.news.pas.earthli nk.net...
> > Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > conversions?
> Joe,
> Just out of curiosity, was LEFT OUTER JOIN (et. al.) always part of the
ANSI
> standard? If so, why do you think major database vendors such as
Microsoft,
> Sybase and Oracle choose proprietary syntax?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:18c7b3c2.0407141855.550aba73@.posting.google.c om...
> > >> On a related note, I am facing the same error when I try to update
> > TableA with data from the same TableA. <<
> > Why in the world do you think that SQL has a FROM clause in UPDATE and
> > DELETE? You are writing unpredictable, proprietary code that EVEN
> > IF IT WAS ALLOWED, would not produce results.
> > There is no FROM clause in a Standard SQL UPDATE statement; it would
> > make no sense. Other products (SQL Server, Sybase and Ingres) also
> > use the UPDATE .. FROM syntax, but with different semantics. So it
> > does not port, or even worse, when you do move it, it trashes your
> > database. Other programmers cannot read it and maintaining it is
> > harder. And when Microsoft decides to change it, you will have to do
> > a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > conversions? The last time the UPDATE FROM changed?
> > The correct syntax for a searched update statement is
> > <update statement> ::=
> > UPDATE <table name>
> > SET <set clause list>
> > [WHERE <search condition>]
> > <set clause list> ::=
> > <set clause> [{ , <set clause> }...]
> > <set clause> ::= <object column> = <update source>
> > <update source> ::= <value expression> | NULL | DEFAULT
> > <object column> ::= <column name>
> > The UPDATE clause simply gives the name of the base table or updatable
> > view to be changed.
> > Notice that no correlation name is allowed in the UPDATE clause; this
> > is to avoid some self-referencing problems that could occur. But it
> > also follows the data model in Standard SQL. When you give a table
> > expression a correlation name, it is to act as if a materialized table
> > with that correlation name has been created in the database. That
> > table then is dropped at the end of the statement. If you allowed
> > correlation names in the UPDATE clause, you would be updating the
> > materialized table, which would then disappear and leave the base
> > table untouched.
> > The SET clause is a list of columns to be changed or made; the WHERE
> > clause tells the statement which rows to use. For this discussion, we
> > will assume the user doing the update has applicable UPDATE privileges
> > for each <object column>.
> > * The WHERE Clause
> > As mentioned, the most important thing to remember about the WHERE
> > clause is that it is optional. If there is no WHERE clause, all rows
> > in the table are changed. This is a common error; if you make it,
> > immediately execute a ROLLBACK statement.
> > All rows that test TRUE for the <search condition> are marked as a
> > subset and not as individual rows. It is also possible that this
> > subset will be empty. This subset is used to construct a new set of
> > rows that will be inserted into the table when the subset is deleted
> > from the table. Note that the empty subset is a valid update that
> > will fire declarative referential actions and triggers.
> > * The SET Clause
> > Each assignment in the <set clause list> is executed in parallel and
> > each SET clause changes all the qualified rows at once. Or at least
> > that is the theoretical model. In practice, implementations will
> > first mark all of the qualified rows in the table in one pass, using
> > the WHERE clause. If there were no problems, then the SQL engine
> > makes a copy of each marked row in working storage. Each SET clause
> > is executed based on the old row image and the results are put in the
> > new row image. Finally, the old rows are deleted and the new rows are
> > inserted. If an error occurs during all of this, then system does a
> > ROLLBACK, the table is left unchanged and the errors are reported.
> > This parallelism is not like what you find in a traditional
> > third-generation programming language, so it may be hard to learn.
> > This feature lets you write a statement that will swap the values in
> > two columns, thus:
> > UPDATE MyTable
> > SET a = b, b = a;
> > This is not the same thing as
> > BEGIN ATOMIC
> > UPDATE MyTable
> > SET a = b;
> > UPDATE MyTable
> > SET b = a;
> > END;
> > In the first UPDATE, columns a and b will swap values in each row. In
> > the second pair of UPDATEs, column a will get all of the values of
> > column b in each row. In the second UPDATE of the pair, a, which now
> > has the same value as the original value of b, will be written back
> > into column b -- no change at all. There are some limits as to what
> > the value expression can be. The same column cannot appear more than
> > once in a <set clause list> -- which makes sense, given the parallel
> > nature of the statement. Since both go into effect at the same time,
> > you would not know which SET clause to use.
> > If a subquery expression is used in a <set clause>, and it returns a
> > single value, the result set is cast to a scalar; if it returns an
> > empty, the result set is cast to a NULL; if it returns multiple rows,
> > a cardinality violation is raised.
> > Same logic for the basic delete statement:
> > DELETE FROM Foobar
> > WHERE EXISTS
> > (SELECT *
> > FROM Foobar AS F1
> > WHERE Foobar.col_1 = F1.col_2)|||> Both implementations pre-dated the '92 standard.

So it appears many vendors implemented proprietary SQL extensions to address
deficiencies in the SQL-89 standard. Once the standard was enhanced to
address the need, many vendors added support for ANSI-style joins as well.

Portability is a consideration but, IMHO, is less important than
functionality in most environments.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"VC" <boston103@.hotmail.com> wrote in message
news:LuPJc.87240$JR4.26140@.attbi_s54...
> Dan,
> Both implementations pre-dated the '92 standard.
> VC
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%vHJc.4441$Qu5.433@.newsread2.news.pas.earthli nk.net...
> > > Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > > conversions?
> > Joe,
> > Just out of curiosity, was LEFT OUTER JOIN (et. al.) always part of the
> ANSI
> > standard? If so, why do you think major database vendors such as
> Microsoft,
> > Sybase and Oracle choose proprietary syntax?
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> > news:18c7b3c2.0407141855.550aba73@.posting.google.c om...
> > > >> On a related note, I am facing the same error when I try to update
> > > TableA with data from the same TableA. <<
> > > > Why in the world do you think that SQL has a FROM clause in UPDATE and
> > > DELETE? You are writing unpredictable, proprietary code that EVEN
> > > IF IT WAS ALLOWED, would not produce results.
> > > > There is no FROM clause in a Standard SQL UPDATE statement; it would
> > > make no sense. Other products (SQL Server, Sybase and Ingres) also
> > > use the UPDATE .. FROM syntax, but with different semantics. So it
> > > does not port, or even worse, when you do move it, it trashes your
> > > database. Other programmers cannot read it and maintaining it is
> > > harder. And when Microsoft decides to change it, you will have to do
> > > a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > > conversions? The last time the UPDATE FROM changed?
> > > > The correct syntax for a searched update statement is
> > > > <update statement> ::=
> > > UPDATE <table name>
> > > SET <set clause list>
> > > [WHERE <search condition>]
> > > > <set clause list> ::=
> > > <set clause> [{ , <set clause> }...]
> > > > <set clause> ::= <object column> = <update source>
> > > > <update source> ::= <value expression> | NULL | DEFAULT
> > > > <object column> ::= <column name>
> > > > The UPDATE clause simply gives the name of the base table or updatable
> > > view to be changed.
> > > > Notice that no correlation name is allowed in the UPDATE clause; this
> > > is to avoid some self-referencing problems that could occur. But it
> > > also follows the data model in Standard SQL. When you give a table
> > > expression a correlation name, it is to act as if a materialized table
> > > with that correlation name has been created in the database. That
> > > table then is dropped at the end of the statement. If you allowed
> > > correlation names in the UPDATE clause, you would be updating the
> > > materialized table, which would then disappear and leave the base
> > > table untouched.
> > > > The SET clause is a list of columns to be changed or made; the WHERE
> > > clause tells the statement which rows to use. For this discussion, we
> > > will assume the user doing the update has applicable UPDATE privileges
> > > for each <object column>.
> > > > * The WHERE Clause
> > > > As mentioned, the most important thing to remember about the WHERE
> > > clause is that it is optional. If there is no WHERE clause, all rows
> > > in the table are changed. This is a common error; if you make it,
> > > immediately execute a ROLLBACK statement.
> > > > All rows that test TRUE for the <search condition> are marked as a
> > > subset and not as individual rows. It is also possible that this
> > > subset will be empty. This subset is used to construct a new set of
> > > rows that will be inserted into the table when the subset is deleted
> > > from the table. Note that the empty subset is a valid update that
> > > will fire declarative referential actions and triggers.
> > > > * The SET Clause
> > > > Each assignment in the <set clause list> is executed in parallel and
> > > each SET clause changes all the qualified rows at once. Or at least
> > > that is the theoretical model. In practice, implementations will
> > > first mark all of the qualified rows in the table in one pass, using
> > > the WHERE clause. If there were no problems, then the SQL engine
> > > makes a copy of each marked row in working storage. Each SET clause
> > > is executed based on the old row image and the results are put in the
> > > new row image. Finally, the old rows are deleted and the new rows are
> > > inserted. If an error occurs during all of this, then system does a
> > > ROLLBACK, the table is left unchanged and the errors are reported.
> > > This parallelism is not like what you find in a traditional
> > > third-generation programming language, so it may be hard to learn.
> > > This feature lets you write a statement that will swap the values in
> > > two columns, thus:
> > > > UPDATE MyTable
> > > SET a = b, b = a;
> > > > This is not the same thing as
> > > > BEGIN ATOMIC
> > > UPDATE MyTable
> > > SET a = b;
> > > UPDATE MyTable
> > > SET b = a;
> > > END;
> > > > In the first UPDATE, columns a and b will swap values in each row. In
> > > the second pair of UPDATEs, column a will get all of the values of
> > > column b in each row. In the second UPDATE of the pair, a, which now
> > > has the same value as the original value of b, will be written back
> > > into column b -- no change at all. There are some limits as to what
> > > the value expression can be. The same column cannot appear more than
> > > once in a <set clause list> -- which makes sense, given the parallel
> > > nature of the statement. Since both go into effect at the same time,
> > > you would not know which SET clause to use.
> > > > If a subquery expression is used in a <set clause>, and it returns a
> > > single value, the result set is cast to a scalar; if it returns an
> > > empty, the result set is cast to a NULL; if it returns multiple rows,
> > > a cardinality violation is raised.
> > > > Same logic for the basic delete statement:
> > > > DELETE FROM Foobar
> > > WHERE EXISTS
> > > (SELECT *
> > > FROM Foobar AS F1
> > > WHERE Foobar.col_1 = F1.col_2)|||--CELKO-- (jcelko212@.earthlink.net) writes:
> There is no FROM clause in a Standard SQL UPDATE statement; it would
> make no sense.

Of course it would.

> Other programmers cannot read it and maintaining it is harder.

Au contraire, I find nested subselects more difficult to understand
and maintain.

So you have this UPDATE statement:

UPDATE tblA
SET col1 = z.col1
col2 = y.col2
FROM tblA a
JOIN ...
WHERE ...

And we are interested in which rows this statement actually hits. A little
cut and paste, select "UPDATE tblA SET", type SELECT, press Execute et
voil!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Is there any sample code to demo the SSB send messages with same sql instance?

Is there any sample code to demo the SSB send messages with same sql instance?

my case is very simple:

I want write a stored procedure to send a xml to another database. The stored procedure is called by tables triggers when some data is changed under the specific conditions.

this should be exactly what you need:

http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker