Help for stored procedure and Null...

Hi,
I have write a stored procedure which makes update in a numeric (int) field.

Depending on data in other table, in some case the result of query get a
Null value instead a zero value...

How can I tell to Update query to NOT update field if the value is Null ?

I hope my word clear...

here the stored procedure:

UPDATE dbo.ANAUTENTI

SET dist1punti = dist1punti +

(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepunti

FROM TEMPIMPORTAZIONEDIST1 INNER JOIN

ANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =
ANAARTICOLI.codartdist1

WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))

WHERE (piva IN

(SELECT piva

FROM TEMPIMPORTAZIONEDIST1

GROUP BY piva))

Thanks in advance

Piero

Italypiero (g.pagnoni@.pesaroservice.com) writes:
> Depending on data in other table, in some case the result of query get a
> Null value instead a zero value...
> How can I tell to Update query to NOT update field if the value is Null ?

UPDATE tbl
SET col = col + coalesce((SELECT ...), 0)

The coalesce function takes list of arguments and returns the first non-NULL
value in the list, or NULL if all values are NULL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns944153FCAB58Yazorman@.127.0.0.1...
> UPDATE tbl
> SET col = col + coalesce((SELECT ...), 0)
>
> The coalesce function takes list of arguments and returns the first
non-NULL
> value in the list, or NULL if all values are NULL.

It works fine !
Thank You very much !

Piero
Italysql

Help Finding and Update/Dupes in 2 Tables

Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed
on my issue.
I have a MSDE database with 2 tables that need to modified. I am changing to
a standard 12 digit code in my PATIENTS table for the field sChartCode. That
code will be in the form of 110012345678. 1100 will preceed the actualy 8
digit chartcode In the PATIENTS table, the same person may be dupliced many
times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH,
012345678 SMITH. For each of these records, they are linked to the RECORDS
db using the field lPatientId.
I have already manually updated about 20K records in the RECORDS db which
takes way to many hours of time. New records will be imported at about 10K a
week or so and will be over 100K soon. By the way, the SQL server is on the
way.
What I am looking for is an easier way to find the records that have not been
converted in the PATIENTS db and see if they match one that has already been
converted. If it has, it would need to update all records in the RECORDS db
with the correct updated lPatientId and then delete the duplicate record(s)
from the PATENTS db. If not, it would only need to add '1100...' to the
lPatientId field.
Any help or guidance that can give will be most appreciated.
hi,
dale_durham via droptable.com wrote:
> Being fairly new to SQL and SQL scripting, I am at a loss on how to
> proceed on my issue.
> I have a MSDE database with 2 tables that need to modified. I am
> changing to a standard 12 digit code in my PATIENTS table for the
> field sChartCode. That code will be in the form of 110012345678.
> 1100 will preceed the actualy 8 digit chartcode In the PATIENTS
> table, the same person may be dupliced many times using vaiations
> such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH.
> For each of these records, they are linked to the RECORDS db using
> the field lPatientId.
> I have already manually updated about 20K records in the RECORDS db
> which takes way to many hours of time. New records will be imported
> at about 10K a week or so and will be over 100K soon. By the way,
> the SQL server is on the way.
> What I am looking for is an easier way to find the records that have
> not been converted in the PATIENTS db and see if they match one that
> has already been converted. If it has, it would need to update all
> records in the RECORDS db with the correct updated lPatientId and
> then delete the duplicate record(s) from the PATENTS db. If not, it
> would only need to add '1100...' to the lPatientId field.
> Any help or guidance that can give will be most appreciated.
can you please post your actual DDL for the involved tables with some INSERT
INTO sample statament?
what I understood is you do not have a good normalization of your
architecture, but I could misunderstood your word...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

help and advice on waittype 0x0044

Hi,
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
6 13 151115052 10 0x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write of
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.

help and advice on waittype 0x0044

Hi,
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
613151115052100x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.
Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write of
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:

> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 613151115052100x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>
|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange
|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.

help and advice on waittype 0x0044

Hi,
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
6 13 151115052 10 0x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write o
f
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:

> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.

HELP Access to sql server 2005 express connection FAILS UPDATE

Hi

I got an access 2002 application front end with a sql server 2005 express back end. Some of my clients are having some difficulties. After using the application for a while, some of the users are finding that the system just hangs up. It usually happens after the front end application has been running for about an hour (sometimes sooner and sometimes later). There are perhaps 1 to 5 concurrent users and I have checked to see if there are any firewalls stalling it (I think I check all of them)- Is there any way that SQL Server 2005 express could be caused to just stall- This even occurs with the odd laptop. All the appropriate protocols are enabled as well. These databses are not very large.

ANY HELP WOULD BE GREATLY APPRETIATED!!!

Thanks

Frank Srebot

Moved thread to the SQL Server Express forum.|||

hi Frank,

what do you mean by "just hangs up"? does it completely stalls requiring a reboot, or it's "sleeeping" for just a while and then restarts working "as expected" or the like?

to start, few things to consider..

SQLExpress sets the "autoclose" property of it's created databases to true, and this causes the dbs to be shut down when not in use, meaning that tyey will be closed if no active connection references them.. this involves a little overhead at next re-use as the dbs must be re-open, but I do not think this is your problem... anyway, the eventual related "problem" can be workaround modifying the relative database property via sp_dboption database's system stored procedure call...

"autoshrink" database property is even set to true, and this causes, at engine scheduled time frames, the eventual shrinking of the involved databases, so that when lots of insert/delete operations are performed (actually lots of deletes), the engine wakes up a thread to shrink (when necessary) the databases, requiring some time to execute..

if the autogrowth property of the database's datafiles and logfiles is set to true and the engine states new file space is required, the engine enlarges the files (when needed) and this will obviously involve some time as well...

other non SQL Server related issues includes OS's scheduled tasks requiring lots of CPU and/or I/O..

but it's hard to solve this way

regards

|||

Thanks for the great and quick response.

To clarify, sql server just hangs up meaning that the application displays an hourglass and eventually the sql connection is lost and an error message is given. I was doing some research and I was wondering if the problem could be in the connection pooling configuration- currently the setting are that pool connections are enabled by default in the ODBC config settings- the databases which I am dealing are quite small- would any one have any ideas perhaps along these lines?

Thanks

Frank srebot

|||

Hi

This is an update to my connection Problem with Access 2002 to SQL SERVER EXPRESS 2005.

We are having random disconnects on the client side with Access putting up a "Connection Failure" dialog box even when the user is actively entering records into the system. Have any of you ever encountered this situation? We have disabled all TCP offloading engine technology on the machine thinking this was causing a problem with SQL Server as well as changing network cards to a whole different brand. We've pretty much ruled out the physical network at this point because we have changed cables and moved to another port on a different switch to no avail.

Is there some timeout setting or connection pooling setting that I am unaware of at the SQL Server level that has a problem interacting with Windows Server 2003 or Windows XP? I have checked and double checked all of the server settings between the old machine and the new and they are identical.

I did read that the connection pooling may be stressed and the pool of connections are 'Leaking'. This might be due to a bad cable or connection, but thats all I have found out.

Any info would be greatly appreciated.

Thanks

Frank

help : Update statement

Here's the statement I need some help with:

SELECT UID FROM sandbox.jobs WHERE username = 'username' order by UID desc
FROM sandbox.timespent;
UPDATE sandbox.timespent
SET UID = UID, Username = 'username', Job_Description = 'test_job'
WHERE Username = 'username'
;

what' i'm trying to do is insert the job description to the most recent entry (which is why i sorted) but it's adding the job description everywhere it sees username (which is more than once) so i need it to update the latest line

something like:

UID | Username | Job description
1 username test_job
2 username test_job1
3 username test_job2

when it updates it should add to UID 3 since it's the most recent. if anyone knows how to write the update statement please let me know. THANK YOU!

What determines if a row is 'most recent'?

Is it the larger UID?

What are the other columns in the tables?

|||I'm not sure how to make it so it selects the most recent. I wanted to sort it and select the first one (which would be most recent) but I'm not sure how to select the first one.

SELECT UID FROM sandbox.jobs WHERE username = 'username' order by UID desc LIMIT 1
FROM sandbox.timespent;
UPDATE sandbox.timespent
SET UID = UID, Username = 'username', Job_Description = 'test_job100'
WHERE UID = UID
;

that's what i wanted it to do, but instead of
WHERE UID = UID i want it to be something like WHERE UID = "most recent UID" some how

|||

So how do you determine which is the 'most recent UID'?

Is is the largest UID value? (It's still not clear.)

|||alright i'll try to explain myself better... I'm writing a program in vb.net

the table looks something like this:

UID | Username | Job | PLC_option | PLC_software
0 user1 9 option1 software1

that's what I want the data to look like. When the program runs, it adds a new UID and the users username to the table... another form opens and the user selects a job, i want the job to be added to that same column (where '9' is) but what's happening is the username dissapears. Another form will come up and the user selects the PLC option

what i'm trying to accomplish is the ID and username gets inserted and then the job and plc options get updated into that same row...

hope that's a little more clear.. thank you
|||

Kenny,

There are at least a couple of ways to go on this. First, let me ask a couple more questions.

What is the code that inserts the new UID and UserName into the table?

How is that code executed?

|||that's where i'm not sure... right now i have this code...

this is in my main form where the UID and username gets sent to the table...

strsuccess = " INSERT INTO sanbox.timespent "
strsuccess &= " ( `username ` ) VALUES ( "
strsuccess &= " '" & GlobalV.strUsername & "');"
x.dbCommand = strsuccess
x.ExecuteNonQuery()

strTime = "Update(sandbox.timespent) "
strTime &= " SET UID = UID , Username = '" & GlobalV.strUsername & "';"
strTime &= " WHERE(uid = uid) "
x.dbCommand = strTime
x.ExecuteNonQuery()

not sure if i did that part right but what I want to do is add a new UID to the form and add the username right next to that UID as a new session kind of deal... once the user proceeds to the next form this code is executed:

strjob = "Update(sandbox.timespent) "
strjob &= " SET job_description = '" & lstJobList.SelectedItem & "'"
strjob &= " WHERE username = '" & GlobalV.strUsername & "';"
x.dbCommand = strjob
x.ExecuteNonQuery()
x = Nothing

i'm pretty sure i'm not doing that part right, i want the job item to be added right next to the username... but what's happening is, the job item is filling in everywhere it finds the username to be the variable value...

i'd like to have it added to the newest UID, which would only add once... that's why i mentioned possibly making it so " WHERE UID = " some kind of uid variable "

it's a little hard to explain but if you can figure out how to add the job item once to the newest column i'd really appreciate it. thanks!

|||

OK, I see a couple of problems.

A couple more questions.

Where is the UID created?

Is it a IDENTITY field value from the table?

And What version/Edition of SQL Server? ( 2000 / 2005 ) ( Express / Standard / Enterprise )

|||the UID is unique and is incrimented by 1, it's the primary key if that helps.|||

Kenny,

You didn't respond about the version/edition of SQL Server, so I will assume that you are using SQL 2005.

This will not execute in your application, but it should point you in the direction that you need to go to make it work as you desire.

First, you need to capture the UID into a newly created application variable (scope it appropriately).

To do so, change the query type to ExecuteQuery so you can get a resultset.

Change the query to your version of this:

INSERT INTO SandBox.TimeSpent ( UserName )
OUTPUT inserted.UID
VALUES ( GlobalV.strUserName )

The application will do something like:

GlobalV.UID = x.ExecuteQuery

At the execution of the insert query, you will have the UID for the newly inserted row -which contains the UserName.

Then your UPDATE statement should be somewhat like this:

UPDATE SandBox.Timespent
SET Job_Description = {Your listbox.SelectedItem for JobDescription}
WHERE UID = GlobalV.UID

By using the UID, you will UPDATE ONLY the row you desire. AS you noticed, when you UPDATE by UserName, you are updating all rows with that UserName.

HELP ....difficulty in insert of 2 tables data with id

Hello frdz,

I have two tables in sqlserver 2005.

I have created the stored procedure for insert,update data.I m creating my application in asp.net with C#

Table-1 CUSTOMER
Fields:
customerid int identity,
cardid int,
customername varchar(20) not null,
address varchar(20) not null,
city varchar(20) not null,
emailid varchar(20)
Table-2 CARD
Fields:
cardid int identity,
cardtype varchar(20) not null,
carddetails varchar(20) not null
 INSERT INTO CUSTOMER (customername ,address,city,emailid)
 VALUES (@.customername,@.address,@.city,@.emailid)
 SELECT @.customerid = SCOPE_IDENTITY()
/* HELP HERE NOT ABLE TO GET DATA OF CARD */
 SELECT @.cardid = cardid from CARD where customerid =@.cardid

Pls tell me how to insert the data ...

There is only one cardid for only one customerid both should be unique no duplication...

One customer can have only one and one card... 

If I understand your design correctly, you'll have to insert the CARD record first and get the new identity fieldcardid into a variable. Then you insert into CUSTOMER, using the variable to populate CUSTOMERS.cardid.

This is because cardid is a foreign key in the CUSTOMER table - so it has to be generated first. Just switch your order of inserts and you should be fine. And I advise that you put both insert statements within a transaction (BEGIN TRAN and COMMIT) - if one insert fails, then the other one shouldn't be left in the table.

If you need more on stored procedures, here's a link to a free chapter ondeveloping stored procedures for sql server.

|||

Your database to me seems odd, I would think that each customer could have many cards, while each card could only have 1 customer, but you seem to have it reversed. In any case, this is how you would insert:

DECLARE @.cardid int

INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)

SET @.cardid=SCOPE_IDENTITY()

INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)

and optionally:

SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid

|||

Thanxs everyone for the replies...

Motley...thanxs ... ya i want that 1 customer can have only 1 card.
and optionally:
SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid

I have created stored procedur for card and customer.

I don't understand what is the above line and how it worksSELECT @.cardid,SCOPE_IDENTITY() ??

This should be written in which stored procedure ? I mean card or customer ?

Should a single stored procedure be created for this ?

Thanxs......waiting for reply

|||

That would return a resultset with 1 row and 2 columns. The first column would be the @.cardid variable that we set earlier in the code, and the second column would be SCOPE_IDENTITY(), which is a function that returns the value of the identity column for the row that was just inserted.

When I said optionally, I meant you could put at the end of the other code like this:

DECLARE @.cardid int

INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)

SET @.cardid=SCOPE_IDENTITY()

INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)

SELECT @.cardid,SCOPE_IDENTITY()

|||

thanxs very much....

Help !!!!!using case statements in update

Hi,
I have this update statement that works, it updates the totalamount to calc amount, but I want to update totalamount only when it is not equal to calcamt.I have tried many things but in vain.Can some one please help me.
How do i use case statements to update only when totalamount!=calcamt.

update c
set totalamount= calcamt
from Prepay c
JOIN
( select sum(amt) as calcamt, payid
from pay
group by payid
)b ON b.payid= c.payid
where c.cust_no='somenum'

Try this:

Code Snippet

update c
set totalamount= calcamt
from Prepay c
JOIN
( select sum(amt) as calcamt, payid
from pay
group by payid
)b ON b.payid= c.payid and b.calcamt <> c.dailytotalamount

where c.cust_no='somenum'

Help ! Error after (windows update) of .NET Framework

Hi all,
today i updated my .NET framework to 1.1 SP 1 (via windows update).
After than, my reporting services failed with the error key
"rsReportServerDisabled".
I have just a german full error message, but it is something with
"...decode symetric key ...". I use Win XP Professional as operating system.
Please apologize my english.
Thank your help in advance.
ThomasMost likely the ASP.NET account got reset. If you have the encryption key
saved, you can restore it using rskeymgmt. If not, you will need to reset
the encryption via rskeymgmt -d.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:D5EEE105-6F19-411C-A820-FBCE3940E699@.microsoft.com...
> Hi all,
> today i updated my .NET framework to 1.1 SP 1 (via windows update).
> After than, my reporting services failed with the error key
> "rsReportServerDisabled".
> I have just a german full error message, but it is something with
> "...decode symetric key ...". I use Win XP Professional as operating
> system.
> Please apologize my english.
> Thank your help in advance.
> Thomas
>
>|||I just tried it and you will probably also need to reconfigure the
connection string via rsconfig before you restore the encryption key.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:uCvrjDElEHA.3428@.TK2MSFTNGP14.phx.gbl...
> Most likely the ASP.NET account got reset. If you have the encryption key
> saved, you can restore it using rskeymgmt. If not, you will need to reset
> the encryption via rskeymgmt -d.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:D5EEE105-6F19-411C-A820-FBCE3940E699@.microsoft.com...
>> Hi all,
>> today i updated my .NET framework to 1.1 SP 1 (via windows update).
>> After than, my reporting services failed with the error key
>> "rsReportServerDisabled".
>> I have just a german full error message, but it is something with
>> "...decode symetric key ...". I use Win XP Professional as operating
>> system.
>> Please apologize my english.
>> Thank your help in advance.
>> Thomas
>>
>>
>|||OK, one more correction. :) I was told by the developers that you won't need
to restore the encryption key. After resetting the connection string, you
just need to do an rsactivate -r -c and things will start working again.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ePMQzNFlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I just tried it and you will probably also need to reconfigure the
>connection string via rsconfig before you restore the encryption key.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:uCvrjDElEHA.3428@.TK2MSFTNGP14.phx.gbl...
>> Most likely the ASP.NET account got reset. If you have the encryption key
>> saved, you can restore it using rskeymgmt. If not, you will need to reset
>> the encryption via rskeymgmt -d.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
>> news:D5EEE105-6F19-411C-A820-FBCE3940E699@.microsoft.com...
>> Hi all,
>> today i updated my .NET framework to 1.1 SP 1 (via windows update).
>> After than, my reporting services failed with the error key
>> "rsReportServerDisabled".
>> I have just a german full error message, but it is something with
>> "...decode symetric key ...". I use Win XP Professional as operating
>> system.
>> Please apologize my english.
>> Thank your help in advance.
>> Thomas
>>
>>
>>
>|||Thank you Brian !
I will try it at once...
Best regards from Germany
Thomas
"Brian Welcker [MSFT]" wrote:
> OK, one more correction. :) I was told by the developers that you won't need
> to restore the encryption key. After resetting the connection string, you
> just need to do an rsactivate -r -c and things will start working again.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ePMQzNFlEHA.3452@.TK2MSFTNGP15.phx.gbl...
> >I just tried it and you will probably also need to reconfigure the
> >connection string via rsconfig before you restore the encryption key.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:uCvrjDElEHA.3428@.TK2MSFTNGP14.phx.gbl...
> >> Most likely the ASP.NET account got reset. If you have the encryption key
> >> saved, you can restore it using rskeymgmt. If not, you will need to reset
> >> the encryption via rskeymgmt -d.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> >> news:D5EEE105-6F19-411C-A820-FBCE3940E699@.microsoft.com...
> >> Hi all,
> >> today i updated my .NET framework to 1.1 SP 1 (via windows update).
> >> After than, my reporting services failed with the error key
> >> "rsReportServerDisabled".
> >> I have just a german full error message, but it is something with
> >> "...decode symetric key ...". I use Win XP Professional as operating
> >> system.
> >> Please apologize my english.
> >>
> >> Thank your help in advance.
> >> Thomas
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

Help - Update Field in SQL Server

I am trying to write a password change page for my website. I want logged in users to be able to change their password which is stored in a SQL table. I was able to get the following code to work

Dim myCommand As New SqlCommand( _
"cmdChangePassword @.Email='"+request("email")+"', @.OldPassword='"+TextBox1.text+"',@.NewPassword='"+TextBox3.text+"'", myConnection)

This information is passed to a stored procedure which updates the table. However, I want to have a return value that shows that the password was changed. I changed the code as follows:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50).Value = request("email")

Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = "+TextBox1.text+"

Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = "+TextBox3.text+"

With this new code the password is not being changed. However, I am not receiving any errors.

BDyou can check your db if the pwd is being changed, to see if the sp is working properly.

(1) in your SP, you can return an integer 0/1 for success or failure and appropriately throw a msgbox saying pswd has been changed.
and modify your code slightly and have an output parameter.

or
(2) query the db again with username=@.username and pwd=@.newpswd and see if you get any records. to make sure you get the xact record you can add more conditions. so if you do get a record, then the db has been updated with the new pswd.

apparently, this one requires an xtra trip to the db.

HTH|||The SP is set to return the integer. I haven't added that code yet. I have tested the SP and know that it works. However, the password is not being changed. The email address, old password and new password need to be passed to the SP. As long as the email address and old password match, the password will be changed. I am assuming there is something wrong with my code and that those items are not being passed successfully.|||heres an example of the code that i am using

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
Dim myDataReader As SqlDataReader
myConnection = New SqlConnection("server=local;database=Northwind;Integrated Security=SSPI ")
myCommand = New SqlCommand()
myCommand.Connection = myConnection
myCommand.CommandText = "finalize_movein"
myCommand.CommandType = CommandType.StoredProcedure
'input parameter
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.userid"
myParameter.Direction = ParameterDirection.Input
myParameter.SqlDbType = SqlDbType.int
myParameter.Value = id
myCommand.Parameters.Add(myParameter)
'output parameter:
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.finalcnum"
myParameter.Direction = ParameterDirection.Output
myParameter.SqlDbType = SqlDbType.int
myCommand.Parameters.Add(myParameter)
' Open the connection to the SQL Server
myConnection.Open()
myCommand.ExecuteNonQuery()
return convert.toint32((myCommand.Parameters("@.finalcnum").Value))
myconnection.close

and have your SP defined like this :

CREATE PROCEDURE finalize_movein (@.userid int ,@.finalcnum bigint OUTPUT) as
begin...

HTH|||BD - please wrap your code in CODE tags to make it distinguishable from your comments.

It looks to me that your problem is in the way you are setting your parameter values. For some reason you have surrounded them with double quotes(") and plus signs(+). Remove those and you should have better luck. I changed the way you were setting up your @.email parameter to make it more consistent with the rest of your code, plus I added a ReturnValue parameter:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim myParm0 As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Integer)
myParm0.Direction = ParameterDirection.ReturnValue
Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = request("email")
Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = TextBox1.text
Dim myParm3 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm3.Direction = ParameterDirection.Input
myParm3.Value = TextBox3.text

|||That worked! Thanks for your help!

Brian|||Sorry, I have one more question. I am now able to change the password. However, I want to display a message to the user that the password has been changed. I have the following variable defined.

Code

Dim Result As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int)

Result.Direction = ParameterDirection.ReturnValue

myCommand.Connection.Open()
myCommand.ExecuteNonQuery()

if Result.Value>0 then MSG.text= "Your password has been changed"

myCommand.Connection.Close()

End Code

In my html I have the following:

<asp:Label id="MSG" runat="server"></asp:Label
Any ideas what I am missing?

Brian|||Normally, the ReturnValue will be 0 if there are no errors. I don't know what your stored procedure looks like, but assuming it follows normal practice, this line:

if Result.Value>0 then MSG.text= "Your password has been changed"

should be this:

if myCommand.Parameters("@.ReturnValue").Value=0 then MSG.text= "Your password has been changed"

Terri|||That's perfect. Thanks again for everybody's help.

Brian

help

I have two temp table name #table1 and #table
when I run the following update, I get an error
" Cannot use the column prefix 't'. This must match the object in the UPDATE clause '#test1'.
update #test
set t.name=tt.nam
from #test1 t, #test2 t
where t.last=tt.las
This in SQL 2000. Why would this happend
Thanks
Jasontry this
update t
set t.name=tt.name
from #test1 t, #test2 tt
where t.last=tt.last
>--Original Message--
>I have two temp table name #table1 and #table2
>when I run the following update, I get an error
>" Cannot use the column prefix 't'. This must match the
object in the UPDATE clause '#test1'. "
>update #test1
>set t.name=tt.name
>from #test1 t, #test2 tt
>where t.last=tt.last
>
>This in SQL 2000. Why would this happend.
>Thanks,
>Jason
>.
>|||Or, better still,
UPDATE
t
SET
t.name = tt.name
FROM
#test1 AS t
INNER JOIN #test2 AS tt ON
tt.last = t.last
James Hokes
> update t
> set t.name=tt.name
> from #test1 t, #test2 tt
> where t.last=tt.last
"Scott Dexter" <anonymous@.discussions.microsoft.com> wrote in message
news:033501c3c5ad$e9b73d20$a401280a@.phx.gbl...
> try this
> update t
> set t.name=tt.name
> from #test1 t, #test2 tt
> where t.last=tt.last
>
>
> >--Original Message--
> >I have two temp table name #table1 and #table2
> >
> >when I run the following update, I get an error
> >" Cannot use the column prefix 't'. This must match the
> object in the UPDATE clause '#test1'. "
> >
> >update #test1
> >set t.name=tt.name
> >from #test1 t, #test2 tt
> >where t.last=tt.last
> >
> >
> >This in SQL 2000. Why would this happend.
> >
> >Thanks,
> >Jason
> >
> >.
> >