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

Profile value + Sql Value, a login problem

Hi all. Quick question. I'm using VS2005, C#, aspx page.

I'm creating a Profile to store login and password. That part is working... I can call the values (and display them) using this code <%= Profile.login %> and <%=Profile.password %
Now I want to create a Grid View that will connect to the SQL db, see if the login and password value stored in the Profile match that of ones in the SQL db.

So if the profile is login: bob password: dog, the grid view will output all application ID numbers associated with the bob and dog. Here is the SQL code...trying to use the <%=Profile.login %> as a filter on the login and password doesn't seem to work...

Can anyone tell me what I'm doing wrong? How can I reference a value in the Profile within an SQL statement?

SELECT ApplicationStatus.Description, Customer.CustomerName, Application.ApplicationDate
FROM Application INNER JOIN
ApplicationStatus ON Application.ApplicationStatusID = ApplicationStatus.ApplicationStatusID INNER JOIN
Customer ON Application.ApplicationID = Customer.ApplicationID INNER JOIN
[User] ON Application.DealerId = [User].UserId
WHERE ([User].LoginId = '<%= Profile.login %>') AND ([User].LoginPwd = '<%= Profile.password %>') AND (ApplicationStatus.Description = 'Pending')

Try getting the value into a variable and use the variable in the SQL.

SELECT ApplicationStatus.Description, Customer.CustomerName, Application.ApplicationDate
FROM Application INNER JOIN
ApplicationStatus ON Application.ApplicationStatusID = ApplicationStatus.ApplicationStatusID INNER JOIN
Customer ON Application.ApplicationID = Customer.ApplicationID INNER JOIN
[User] ON Application.DealerId = [User].UserId
WHERE ([User].LoginId = @.login) AND ([User].LoginPwd = @.pwd) AND (ApplicationStatus.Description = 'Pending')

Then add the parameters to the command object and set their values appropriately.

|||I agree with ndinakar.

Processor count

How do I get the value of Processor Count in a variable
from xp_msver?
DECLARE @.CPUS int
exec xp_msver N'CprocessorCount'
ThanksNot sure if xpmsver would support output parameters for that specific column
(I doubt it), so you could try this:
CREATE TABLE #xpmsver
(
a INT, b VARCHAR(32), c INT, d VARCHAR(255)
)
INSERT #xpmsver
EXEC master..xp_msver N'processorCount'
DECLARE @.CPUS INT
SELECT @.CPUS = CONVERT(INT, d) FROM #xpmsver
DROP TABLE #xpmsver
PRINT @.cpus
"Bubba Ole" <anoah@.canberra.com> wrote in message
news:00b001c38f40$69096c50$a101280a@.phx.gbl...
> How do I get the value of Processor Count in a variable
> from xp_msver?
> DECLARE @.CPUS int
> exec xp_msver N'CprocessorCount'
> Thanks|||Thanks a million!!!!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Processing NULL values

I have an input file that may have NULL's in it's fields. The fields are of a various data types.

The NULL value is represented by ? - question mark.

Is there a way to set package or file connection up in a way - so it treats ? as NULL?

The only other way I found is to use Derived column - and so I have to create derived field for most of the fields (150 of them) since a lot of them may contain ? - or NULL.

Any ideas?

You could build your own custom source adapter but it be quicker to use the derived column.

-Jamie

|||

what adds pain is that two operations must occur for each column: relace ? with null and data conversion.

So, I have to create following expression in Derived Column for most columns:

[fiel1] == "?" ? NULL : (DT_DTAE) [fiel1]

and repeat this 150 times...

Process: %processor time

Hi,
I am monitoring the above counter on a 4 processor box
and have 2 questions:
(1) the value for 2 instances (there are 5 all together)
is ~200, so how is this a %? Is this to be read as 200/4
so 50%?
(2) the instances are listed as sqlservr #1, sqlservr #2
etc - how can I resolve this to the instance name?
TIA,
Mary
Mary,
1: Yes, divide the total percent by the number of instances. Double check
with Task Manager.
2. What operating system are you using? On Windows XP, Perfmon separates the
counters by instance name.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:112801c4f721$51e21910$a501280a@.phx.gbl...
> Hi,
> I am monitoring the above counter on a 4 processor box
> and have 2 questions:
> (1) the value for 2 instances (there are 5 all together)
> is ~200, so how is this a %? Is this to be read as 200/4
> so 50%?
> (2) the instances are listed as sqlservr #1, sqlservr #2
> etc - how can I resolve this to the instance name?
> TIA,
> Mary
|||Thanks Ron,
I'm using Win 2000 Server. The counters are separated by
instance name for most things, but for Process they are
not - they appear as a numbered list in the bottom right-
hand corner.
Thanks,
Mary

Process: %processor time

Hi,
I am monitoring the above counter on a 4 processor box
and have 2 questions:
(1) the value for 2 instances (there are 5 all together)
is ~200, so how is this a %? Is this to be read as 200/4
so 50%?
(2) the instances are listed as sqlservr #1, sqlservr #2
etc - how can I resolve this to the instance name?
TIA,
MaryMary,
1: Yes, divide the total percent by the number of instances. Double check
with Task Manager.
2. What operating system are you using? On Windows XP, Perfmon separates the
counters by instance name.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:112801c4f721$51e21910$a501280a@.phx.gbl...
> Hi,
> I am monitoring the above counter on a 4 processor box
> and have 2 questions:
> (1) the value for 2 instances (there are 5 all together)
> is ~200, so how is this a %? Is this to be read as 200/4
> so 50%?
> (2) the instances are listed as sqlservr #1, sqlservr #2
> etc - how can I resolve this to the instance name?
> TIA,
> Mary|||Thanks Ron,
I'm using Win 2000 Server. The counters are separated by
instance name for most things, but for Process they are
not - they appear as a numbered list in the bottom right-
hand corner.
Thanks,
Mary

Process: %processor time

Hi,
I am monitoring the above counter on a 4 processor box
and have 2 questions:
(1) the value for 2 instances (there are 5 all together)
is ~200, so how is this a %? Is this to be read as 200/4
so 50%?
(2) the instances are listed as sqlservr #1, sqlservr #2
etc - how can I resolve this to the instance name?
TIA,
MaryMary,
1: Yes, divide the total percent by the number of instances. Double check
with Task Manager.
2. What operating system are you using? On Windows XP, Perfmon separates the
counters by instance name.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:112801c4f721$51e21910$a501280a@.phx.gbl...
> Hi,
> I am monitoring the above counter on a 4 processor box
> and have 2 questions:
> (1) the value for 2 instances (there are 5 all together)
> is ~200, so how is this a %? Is this to be read as 200/4
> so 50%?
> (2) the instances are listed as sqlservr #1, sqlservr #2
> etc - how can I resolve this to the instance name?
> TIA,
> Mary|||Thanks Ron,
I'm using Win 2000 Server. The counters are separated by
instance name for most things, but for Process they are
not - they appear as a numbered list in the bottom right-
hand corner.
Thanks,
Mary

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