Profiler -> Duration

Hi,
What does a zero value in the Duration column of SQL
Profiler trace mean for SQL:BatchCompleted and
RPC:Completed events?
Thanks,
Osk
Osk wrote:
> Hi,
> What does a zero value in the Duration column of SQL
> Profiler trace mean for SQL:BatchCompleted and
> RPC:Completed events?
It means the duration was so fast SQL Server recorded it as 0. The
granularity of the timer is probably about 15 ms in most cases.
David Gugick
Imceda Software
www.imceda.com
sql

Profiler -> Duration

Hi,
What does a zero value in the Duration column of SQL
Profiler trace mean for SQL:BatchCompleted and
RPC:Completed events?
Thanks,
OskOsk wrote:
> Hi,
> What does a zero value in the Duration column of SQL
> Profiler trace mean for SQL:BatchCompleted and
> RPC:Completed events?
It means the duration was so fast SQL Server recorded it as 0. The
granularity of the timer is probably about 15 ms in most cases.
David Gugick
Imceda Software
www.imceda.com

Profiler -> Duration

Hi,
What does a zero value in the Duration column of SQL
Profiler trace mean for SQL:BatchCompleted and
RPC:Completed events?
--
Thanks,
OskOsk wrote:
> Hi,
> What does a zero value in the Duration column of SQL
> Profiler trace mean for SQL:BatchCompleted and
> RPC:Completed events?
It means the duration was so fast SQL Server recorded it as 0. The
granularity of the timer is probably about 15 ms in most cases.
--
David Gugick
Imceda Software
www.imceda.com

Profiler

Hi,
I'm usin g profiler to trace TSQL (stmtstarting etc) and
there are 100s of rows with empty values for the textdata
column - any ideas why?
TIANot all events return data for every column. What events are these that have
the blank textdata values?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:ac8801c4d6fe$3192d480$a601280a@.phx.gbl...
> Hi,
> I'm usin g profiler to trace TSQL (stmtstarting etc) and
> there are 100s of rows with empty values for the textdata
> column - any ideas why?
> TIA|||sp:stmtStarting, sp:stmtCompleted :)|||Is it possible that the procedure is encrypted?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:187c01c4d707$1473e8e0$a501280a@.phx.gbl...
> sp:stmtStarting, sp:stmtCompleted :)|||Kalen Delaney wrote:
> Is it possible that the procedure is encrypted?
>
> "Bob" <anonymous@.discussions.microsoft.com> wrote in message
> news:187c01c4d707$1473e8e0$a501280a@.phx.gbl...
>> sp:stmtStarting, sp:stmtCompleted :)
What I generally see with an encrypted SP in Profiler for
SP:StmtStarting/Completed is something like:
-- <sp_name> Encrypted Text
Strange.
David Gugick
Imceda Software
www.imceda.com

Production DB Modifications

We have a production database that is being used by at least 20 different
applications. Some of the tables need to be modified (column added). Does
anybody have a good way or best method of changing table schemas, objects,
etc. without interrupting or at least minimizing the impact on the current
operations?
Hello,
No. If you need to change the schema it defenetely require a downtime.
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
> We have a production database that is being used by at least 20 different
> applications. Some of the tables need to be modified (column added). Does
> anybody have a good way or best method of changing table schemas, objects,
> etc. without interrupting or at least minimizing the impact on the current
> operations?
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6a39HvKHHA.3936@.TK2MSFTNGP02.phx.gbl...
> Hello,
> No. If you need to change the schema it defenetely require a downtime.
>
Eh, really depends on the size of the schema change.
If the column has no defaults, adding it should be a trivial change. We do
these routinely on a 24x7 database.
If the column has defaults, the larger the table, the longer it will be
locked.
One way to NOT do it is via EM. In almost all cases, this will guarantee
the table will be locked, a new one with the updated schema created, and
then data loaded into it, then the original deleted and the new one renamed.
You really have to do these via a script, not via EM.

> Thanks
> Hari
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
>
sql

Production DB Modifications

We have a production database that is being used by at least 20 different
applications. Some of the tables need to be modified (column added). Does
anybody have a good way or best method of changing table schemas, objects,
etc. without interrupting or at least minimizing the impact on the current
operations?Hello,
No. If you need to change the schema it defenetely require a downtime.
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
> We have a production database that is being used by at least 20 different
> applications. Some of the tables need to be modified (column added). Does
> anybody have a good way or best method of changing table schemas, objects,
> etc. without interrupting or at least minimizing the impact on the current
> operations?|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6a39HvKHHA.3936@.TK2MSFTNGP02.phx.gbl...
> Hello,
> No. If you need to change the schema it defenetely require a downtime.
>
Eh, really depends on the size of the schema change.
If the column has no defaults, adding it should be a trivial change. We do
these routinely on a 24x7 database.
If the column has defaults, the larger the table, the longer it will be
locked.
One way to NOT do it is via EM. In almost all cases, this will guarantee
the table will be locked, a new one with the updated schema created, and
then data loaded into it, then the original deleted and the new one renamed.
You really have to do these via a script, not via EM.
> Thanks
> Hari
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
>> We have a production database that is being used by at least 20 different
>> applications. Some of the tables need to be modified (column added). Does
>> anybody have a good way or best method of changing table schemas,
>> objects,
>> etc. without interrupting or at least minimizing the impact on the
>> current
>> operations?
>

Production DB Modifications

We have a production database that is being used by at least 20 different
applications. Some of the tables need to be modified (column added). Does
anybody have a good way or best method of changing table schemas, objects,
etc. without interrupting or at least minimizing the impact on the current
operations?Hello,
No. If you need to change the schema it defenetely require a downtime.
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
> We have a production database that is being used by at least 20 different
> applications. Some of the tables need to be modified (column added). Does
> anybody have a good way or best method of changing table schemas, objects,
> etc. without interrupting or at least minimizing the impact on the current
> operations?|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6a39HvKHHA.3936@.TK2MSFTNGP02.phx.gbl...
> Hello,
> No. If you need to change the schema it defenetely require a downtime.
>
Eh, really depends on the size of the schema change.
If the column has no defaults, adding it should be a trivial change. We do
these routinely on a 24x7 database.
If the column has defaults, the larger the table, the longer it will be
locked.
One way to NOT do it is via EM. In almost all cases, this will guarantee
the table will be locked, a new one with the updated schema created, and
then data loaded into it, then the original deleted and the new one renamed.
You really have to do these via a script, not via EM.

> Thanks
> Hari
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
>

Processing XML data from a table

We have an XML column in a SQL Server 2005 table. Each row of this table contains one XML document.

I want to shred values from the XML documents and process these within a Data Flow. I want the Data Flow to execute once across a record set comprised of all of the XML documents.

I can shred the XML using a For-Each loop and XML Task. I'm kinda stuck on how I then get the data from variables into a Recordset or similar so that I can process this within single iteration of a Data Flow.

Or - is my approach incorrect? I seem to be building a verbose and clunky solution to this problem. I know I could accomplish the same in a pretty simple SQL statement using .value on the XML column... am I missing something? Is a SQL query just better suited to this problem?

Any help much appreciated.

James

" how I then get the data from variables into a Recordset" ... the script source component.

processing each row in a multirow insert

I'm looking for a way to process each row in a insert ... select sentece. What i need is to update a column which is type int (not identity nor sequence) in a way it always get the max record + 1, in a sentence:

insert into my_table
(a,b,c,d)
select isnull((select max(id) from another_table),0) as a,
'b' as b, 'c' as c, 'd' as d
from table1 join table2 on t1=t2

the problem with such a sentence is the embbebed select is it is executed only once so it provides always the same value, i.e. assuming the subselect returns 7 and the join provides 2 rows, what would be inserted in my_table is:

a b c d
-- -- -- --
7 b c d
7 b c d

and not

7 b c d
8 b c d

what i expected

so what i looking for is a way to resolve such a sentence or at least some kind of fuction which generates a identity value in a similar way as identity() but being useful for no-identity tables. Maybe something similar to newid() but returning int value and it would be great if that value would be max(table_field)+1

any idea?i forgot to say don't want to use a cursor to process and insert each row|||This can be done using a single direct, if complicated, SQL statement. It would be simpler to insert your data into a temporary table with an autoincrement field, and then select from the temporary table, adding max(id) to the values in the autoincrement field.

Otherwise, you will need a select statement with a subquery that fabricates sequential values for your data (what order to you want them in? You have to choose an order with this method) and then add max(id) to these sequential values.

blindman

process all row

how would i do this in pure t-sql not in a win app
i have column A B and C all integers
psuedo sql
INSERT INTO TABLE1 (C) values (A+B)
A+B = C
thanksHoward
CREATE TABLE #Test
(
A INT,
B INT,
C AS COALESCE(A,0)+COALESCE(B,0)
)
INSERT INTO #Test SELECT 1,2
SELECT * FROM #Test
"Howard" <howdy0909@.yahoo.com> wrote in message
news:u3lkECkUGHA.776@.TK2MSFTNGP09.phx.gbl...
> how would i do this in pure t-sql not in a win app
> i have column A B and C all integers
> psuedo sql
> INSERT INTO TABLE1 (C) values (A+B)
> A+B = C
> thanks
>|||Exactly as you stated
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Howard" <howdy0909@.yahoo.com> wrote in message
news:u3lkECkUGHA.776@.TK2MSFTNGP09.phx.gbl...
> how would i do this in pure t-sql not in a win app
> i have column A B and C all integers
> psuedo sql
> INSERT INTO TABLE1 (C) values (A+B)
> A+B = C
> thanks
>|||oops sorry i meant update
UPDATE table1
SET colC = colA + colB
WHERE loop all
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:LfWdncfWt9dmaLXZRVnyvQ@.bt.com...
> Exactly as you stated
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:u3lkECkUGHA.776@.TK2MSFTNGP09.phx.gbl...
>|||An unrestricted update (no WHERE clause) will process the entire table.
E.g.:
UPDATE table1
SET colC = colA + colB
ML
http://milambda.blogspot.com/|||Also you dont need to have other column. Just use select
Select colA, colB, colA+colB as colC from table
Madhivanan

Process 53 generated fatal exception

Is anyone having problems with text columns once SP3A is installed?
I have a stored procedure that simply updates a text column with changes and
I'm getting what looks like a fatal error in SQL Server. It shows up with
this error posted through JDBC:
53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
I didn't see anything in the MS knowledge base. If we remove sp3a and put o
n sp3, it doesn't happen.Amy,
I would raise a call with Microsoft support. You may be encountering a bug.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amy Thropp wrote:
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes a
nd I'm getting what looks like a fatal error in SQL Server. It shows up wit
h this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Serv
er is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3,
it doesn't happen.|||If the KB articles on this issue don't apply to your environment, consider
opening a case with Microsoft PSS. You won't be charged if this is a bug,
which is likely with an AV.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amy Thropp" <AmyThropp@.discussions.microsoft.com> wrote in message
news:8E65B0E2-73CD-472D-8947-03ED80BA3F85@.microsoft.com...
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes
and I'm getting what looks like a fatal error in SQL Server. It shows up
with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put
on sp3, it doesn't happen.

Process 53 generated fatal exception

Is anyone having problems with text columns once SP3A is installed?
I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.Amy,
I would raise a call with Microsoft support. You may be encountering a bug.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amy Thropp wrote:
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.|||If the KB articles on this issue don't apply to your environment, consider
opening a case with Microsoft PSS. You won't be charged if this is a bug,
which is likely with an AV.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amy Thropp" <AmyThropp@.discussions.microsoft.com> wrote in message
news:8E65B0E2-73CD-472D-8947-03ED80BA3F85@.microsoft.com...
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes
and I'm getting what looks like a fatal error in SQL Server. It shows up
with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put
on sp3, it doesn't happen.

Process 53 generated fatal exception

Is anyone having problems with text columns once SP3A is installed?
I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.
Amy,
I would raise a call with Microsoft support. You may be encountering a bug.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amy Thropp wrote:
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.
|||If the KB articles on this issue don't apply to your environment, consider
opening a case with Microsoft PSS. You won't be charged if this is a bug,
which is likely with an AV.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amy Thropp" <AmyThropp@.discussions.microsoft.com> wrote in message
news:8E65B0E2-73CD-472D-8947-03ED80BA3F85@.microsoft.com...
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes
and I'm getting what looks like a fatal error in SQL Server. It shows up
with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put
on sp3, it doesn't happen.

Procedure to update one table with value from another table

I want to run a stored procedure that selects a value from one column
in a table and updates a column in another table with the value
selected from the first table.
CREATE PROCEDURE dbo.UpdateField
@.ID NVARCHAR(10)
AS
BEGIN
SET NOCOUNT ON
SELECT ColumnX AS "ColumnX_Value"
FROM Table1
WHERE Table1.ID = @.ID
UPDATE Table2
SET ColumnY = ColumnX_Value
WHERE Table2.ID = @.ID
END
GO
The procedure is called from an .asp page using:
EXEC dbo.UpdateField @.ID = 'SomeValue'"
SQL Server Query Analyzer gives me an "Invalid column name
'ColumnX_Value'" error when I run the above procedure.
What have I done wrong?
Andyza wrote on 21 Jun 2005 03:46:21 -0700:

> I want to run a stored procedure that selects a value from one column
> in a table and updates a column in another table with the value
> selected from the first table.
> CREATE PROCEDURE dbo.UpdateField
> @.ID NVARCHAR(10)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT ColumnX AS "ColumnX_Value"
> FROM Table1
> WHERE Table1.ID = @.ID
> UPDATE Table2
> SET ColumnY = ColumnX_Value
> WHERE Table2.ID = @.ID
> END
> GO
> The procedure is called from an .asp page using:
> EXEC dbo.UpdateField @.ID = 'SomeValue'"
> SQL Server Query Analyzer gives me an "Invalid column name
> 'ColumnX_Value'" error when I run the above procedure.
> What have I done wrong?
You can't do it like that - you're just selecting rows, and then doing an
unrelated updated. Easiest way will be to use a join
UPDATE Table2
SET ColumnY = Table1.ColumnX
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
Dan
|||Andyza
Why you are refering to the alias?
In the below example you will have to ensure that SELECT statement returns
only one row ,otherwise you could get a wrong value
I'd also add UPDLOCK hint to the SELECT statement to ensure that the data
has not changed since i last read id ( without blocking others readers). For
more details please refer the BOL
(Untested)
CREATE PROCEDURE dbo.UpdateField
@.ID NVARCHAR(10)
AS
DECLARE @.var INT
BEGIN
SET NOCOUNT ON
BEGIN TRAN
SELECT @.var=ColumnX AS "ColumnX_Value" (UPDLOCK)
FROM Table1
WHERE Table1.ID = @.ID
UPDATE Table2
SET ColumnY = @.var
WHERE Table2.ID = @.ID
COMMIT TRAN
END
GO
"Andyza" <andyza@.webmail.co.za> wrote in message
news:1119350781.771565.170710@.g14g2000cwa.googlegr oups.com...
> I want to run a stored procedure that selects a value from one column
> in a table and updates a column in another table with the value
> selected from the first table.
> CREATE PROCEDURE dbo.UpdateField
> @.ID NVARCHAR(10)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT ColumnX AS "ColumnX_Value"
> FROM Table1
> WHERE Table1.ID = @.ID
> UPDATE Table2
> SET ColumnY = ColumnX_Value
> WHERE Table2.ID = @.ID
> END
> GO
> The procedure is called from an .asp page using:
> EXEC dbo.UpdateField @.ID = 'SomeValue'"
> SQL Server Query Analyzer gives me an "Invalid column name
> 'ColumnX_Value'" error when I run the above procedure.
> What have I done wrong?
>
|||Daniel
You may want to avoid writing such techniques for UPDATETING because you
can get a wrong output
See , David Portas's example
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
(though these results aren't guaranteed - that's part of the problem).
Why did the result change? The physical implementation has affected the
meaning of the code, with serious, potentially disastrous consequences.
How can you even test your code if its results are subject to change
due to the vagaries of storage, indexing and cacheing?
With the ANSI syntax there is no ambiguity. The UPDATE statement
compels the programmer to design an unambiguous assignment subquery
that returns no more than a single value.
UPDATE Countries
SET capitalcity =
(SELECT MIN(cityname)
FROM Cities
WHERE Countries.countryname = Cities.countryname);
At the very least this forces the developer to reconsider whether the
UPDATE statement makes logical sense. You might want to make an effort
to learn Standard SQL instead of a dialect that can change at any time,
which will not port, cannot be understood by other programmers, etc.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:eyB8e%23kdFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Andyza wrote on 21 Jun 2005 03:46:21 -0700:
>
> You can't do it like that - you're just selecting rows, and then doing an
> unrelated updated. Easiest way will be to use a join
> UPDATE Table2
> SET ColumnY = Table1.ColumnX
> FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
>
> Dan
>
|||Uri wrote on Tue, 21 Jun 2005 13:59:04 +0300:

> Daniel
> You may want to avoid writing such techniques for UPDATETING because you
> can get a wrong output
> See , David Portas's example
Yeah, I can see that, thanks for the post. I assumed (perhaps incorrectly)
that the OP's UPDATE was being done joining on keys (as the column name was
ID which tends to be used for a PK/FK column), and therefore would always be
a single matching row in Table2.
Dan

Procedure to update one table with value from another table

I want to run a stored procedure that selects a value from one column
in a table and updates a column in another table with the value
selected from the first table.
CREATE PROCEDURE dbo.UpdateField
@.ID NVARCHAR(10)
AS
BEGIN
SET NOCOUNT ON
SELECT ColumnX AS "ColumnX_Value"
FROM Table1
WHERE Table1.ID = @.ID
UPDATE Table2
SET ColumnY = ColumnX_Value
WHERE Table2.ID = @.ID
END
GO
The procedure is called from an .asp page using:
EXEC dbo.UpdateField @.ID = 'SomeValue'"
SQL Server Query Analyzer gives me an "Invalid column name
'ColumnX_Value'" error when I run the above procedure.
What have I done wrong?Andyza wrote on 21 Jun 2005 03:46:21 -0700:

> I want to run a stored procedure that selects a value from one column
> in a table and updates a column in another table with the value
> selected from the first table.
> CREATE PROCEDURE dbo.UpdateField
> @.ID NVARCHAR(10)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT ColumnX AS "ColumnX_Value"
> FROM Table1
> WHERE Table1.ID = @.ID
> UPDATE Table2
> SET ColumnY = ColumnX_Value
> WHERE Table2.ID = @.ID
> END
> GO
> The procedure is called from an .asp page using:
> EXEC dbo.UpdateField @.ID = 'SomeValue'"
> SQL Server Query Analyzer gives me an "Invalid column name
> 'ColumnX_Value'" error when I run the above procedure.
> What have I done wrong?
You can't do it like that - you're just selecting rows, and then doing an
unrelated updated. Easiest way will be to use a join
UPDATE Table2
SET ColumnY = Table1.ColumnX
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
Dan|||Andyza
Why you are refering to the alias?
In the below example you will have to ensure that SELECT statement returns
only one row ,otherwise you could get a wrong value
I'd also add UPDLOCK hint to the SELECT statement to ensure that the data
has not changed since i last read id ( without blocking others readers). For
more details please refer the BOL
(Untested)
CREATE PROCEDURE dbo.UpdateField
@.ID NVARCHAR(10)
AS
DECLARE @.var INT
BEGIN
SET NOCOUNT ON
BEGIN TRAN
SELECT @.var=ColumnX AS "ColumnX_Value" (UPDLOCK)
FROM Table1
WHERE Table1.ID = @.ID
UPDATE Table2
SET ColumnY = @.var
WHERE Table2.ID = @.ID
COMMIT TRAN
END
GO
"Andyza" <andyza@.webmail.co.za> wrote in message
news:1119350781.771565.170710@.g14g2000cwa.googlegroups.com...
> I want to run a stored procedure that selects a value from one column
> in a table and updates a column in another table with the value
> selected from the first table.
> CREATE PROCEDURE dbo.UpdateField
> @.ID NVARCHAR(10)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT ColumnX AS "ColumnX_Value"
> FROM Table1
> WHERE Table1.ID = @.ID
> UPDATE Table2
> SET ColumnY = ColumnX_Value
> WHERE Table2.ID = @.ID
> END
> GO
> The procedure is called from an .asp page using:
> EXEC dbo.UpdateField @.ID = 'SomeValue'"
> SQL Server Query Analyzer gives me an "Invalid column name
> 'ColumnX_Value'" error when I run the above procedure.
> What have I done wrong?
>|||Daniel
You may want to avoid writing such techniques for UPDATETING because you
can get a wrong output
See , David Portas's example
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
(though these results aren't guaranteed - that's part of the problem).
Why did the result change? The physical implementation has affected the
meaning of the code, with serious, potentially disastrous consequences.
How can you even test your code if its results are subject to change
due to the vagaries of storage, indexing and cacheing?
With the ANSI syntax there is no ambiguity. The UPDATE statement
compels the programmer to design an unambiguous assignment subquery
that returns no more than a single value.
UPDATE Countries
SET capitalcity =
(SELECT MIN(cityname)
FROM Cities
WHERE Countries.countryname = Cities.countryname);
At the very least this forces the developer to reconsider whether the
UPDATE statement makes logical sense. You might want to make an effort
to learn Standard SQL instead of a dialect that can change at any time,
which will not port, cannot be understood by other programmers, etc.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:eyB8e%23kdFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Andyza wrote on 21 Jun 2005 03:46:21 -0700:
>
> You can't do it like that - you're just selecting rows, and then doing an
> unrelated updated. Easiest way will be to use a join
> UPDATE Table2
> SET ColumnY = Table1.ColumnX
> FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
>
> Dan
>|||Uri wrote on Tue, 21 Jun 2005 13:59:04 +0300:

> Daniel
> You may want to avoid writing such techniques for UPDATETING because you
> can get a wrong output
> See , David Portas's example
Yeah, I can see that, thanks for the post. I assumed (perhaps incorrectly)
that the OP's UPDATE was being done joining on keys (as the column name was
ID which tends to be used for a PK/FK column), and therefore would always be
a single matching row in Table2.
Dan

Procedure to update one table with value from another table

I want to run a stored procedure that selects a value from one column
in a table and updates a column in another table with the value
selected from the first table.
CREATE PROCEDURE dbo.UpdateField
@.ID NVARCHAR(10)
AS
BEGIN
SET NOCOUNT ON
SELECT ColumnX AS "ColumnX_Value"
FROM Table1
WHERE Table1.ID = @.ID
UPDATE Table2
SET ColumnY = ColumnX_Value
WHERE Table2.ID = @.ID
END
GO
The procedure is called from an .asp page using:
EXEC dbo.UpdateField @.ID = 'SomeValue'"
SQL Server Query Analyzer gives me an "Invalid column name
'ColumnX_Value'" error when I run the above procedure.
What have I done wrong?Andyza wrote on 21 Jun 2005 03:46:21 -0700:
> I want to run a stored procedure that selects a value from one column
> in a table and updates a column in another table with the value
> selected from the first table.
> CREATE PROCEDURE dbo.UpdateField
> @.ID NVARCHAR(10)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT ColumnX AS "ColumnX_Value"
> FROM Table1
> WHERE Table1.ID = @.ID
> UPDATE Table2
> SET ColumnY = ColumnX_Value
> WHERE Table2.ID = @.ID
> END
> GO
> The procedure is called from an .asp page using:
> EXEC dbo.UpdateField @.ID = 'SomeValue'"
> SQL Server Query Analyzer gives me an "Invalid column name
> 'ColumnX_Value'" error when I run the above procedure.
> What have I done wrong?
You can't do it like that - you're just selecting rows, and then doing an
unrelated updated. Easiest way will be to use a join
UPDATE Table2
SET ColumnY = Table1.ColumnX
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
Dan|||Andyza
Why you are refering to the alias?
In the below example you will have to ensure that SELECT statement returns
only one row ,otherwise you could get a wrong value
I'd also add UPDLOCK hint to the SELECT statement to ensure that the data
has not changed since i last read id ( without blocking others readers). For
more details please refer the BOL
(Untested)
CREATE PROCEDURE dbo.UpdateField
@.ID NVARCHAR(10)
AS
DECLARE @.var INT
BEGIN
SET NOCOUNT ON
BEGIN TRAN
SELECT @.var=ColumnX AS "ColumnX_Value" (UPDLOCK)
FROM Table1
WHERE Table1.ID = @.ID
UPDATE Table2
SET ColumnY = @.var
WHERE Table2.ID = @.ID
COMMIT TRAN
END
GO
"Andyza" <andyza@.webmail.co.za> wrote in message
news:1119350781.771565.170710@.g14g2000cwa.googlegroups.com...
> I want to run a stored procedure that selects a value from one column
> in a table and updates a column in another table with the value
> selected from the first table.
> CREATE PROCEDURE dbo.UpdateField
> @.ID NVARCHAR(10)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT ColumnX AS "ColumnX_Value"
> FROM Table1
> WHERE Table1.ID = @.ID
> UPDATE Table2
> SET ColumnY = ColumnX_Value
> WHERE Table2.ID = @.ID
> END
> GO
> The procedure is called from an .asp page using:
> EXEC dbo.UpdateField @.ID = 'SomeValue'"
> SQL Server Query Analyzer gives me an "Invalid column name
> 'ColumnX_Value'" error when I run the above procedure.
> What have I done wrong?
>|||Daniel
You may want to avoid writing such techniques for UPDATETING because you
can get a wrong output
See , David Portas's example
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
(though these results aren't guaranteed - that's part of the problem).
Why did the result change? The physical implementation has affected the
meaning of the code, with serious, potentially disastrous consequences.
How can you even test your code if its results are subject to change
due to the vagaries of storage, indexing and cacheing?
With the ANSI syntax there is no ambiguity. The UPDATE statement
compels the programmer to design an unambiguous assignment subquery
that returns no more than a single value.
UPDATE Countries
SET capitalcity = (SELECT MIN(cityname)
FROM Cities
WHERE Countries.countryname = Cities.countryname);
At the very least this forces the developer to reconsider whether the
UPDATE statement makes logical sense. You might want to make an effort
to learn Standard SQL instead of a dialect that can change at any time,
which will not port, cannot be understood by other programmers, etc.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:eyB8e%23kdFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Andyza wrote on 21 Jun 2005 03:46:21 -0700:
> >
> > I want to run a stored procedure that selects a value from one column
> > in a table and updates a column in another table with the value
> > selected from the first table.
> >
> > CREATE PROCEDURE dbo.UpdateField
> > @.ID NVARCHAR(10)
> > AS
> > BEGIN
> > SET NOCOUNT ON
> > SELECT ColumnX AS "ColumnX_Value"
> > FROM Table1
> > WHERE Table1.ID = @.ID
> >
> > UPDATE Table2
> > SET ColumnY = ColumnX_Value
> > WHERE Table2.ID = @.ID
> > END
> > GO
> >
> > The procedure is called from an .asp page using:
> >
> > EXEC dbo.UpdateField @.ID = 'SomeValue'"
> >
> > SQL Server Query Analyzer gives me an "Invalid column name
> > 'ColumnX_Value'" error when I run the above procedure.
> > What have I done wrong?
> You can't do it like that - you're just selecting rows, and then doing an
> unrelated updated. Easiest way will be to use a join
> UPDATE Table2
> SET ColumnY = Table1.ColumnX
> FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
>
> Dan
>|||Uri wrote on Tue, 21 Jun 2005 13:59:04 +0300:
> Daniel
> You may want to avoid writing such techniques for UPDATETING because you
> can get a wrong output
> See , David Portas's example
Yeah, I can see that, thanks for the post. I assumed (perhaps incorrectly)
that the OP's UPDATE was being done joining on keys (as the column name was
ID which tends to be used for a PK/FK column), and therefore would always be
a single matching row in Table2.
Dan