_WA_SYS% indexes

Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes in
sysindexes impact performance on update, delete or insert?
Any answer will be helpful.
Thanks
Tarlo
No, These are not indexes, they don't have a b-tree associated with them, don't store any data.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tarlo" <Tarlo@.discussions.microsoft.com> wrote in message
news:500DE04C-0239-49B3-BD6C-6B90D44D6C1A@.microsoft.com...
> Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes in
> sysindexes impact performance on update, delete or insert?
> Any answer will be helpful.
> Thanks
> Tarlo
|||Thanks a bunch it helps.
Regards
Tarlo
"Tibor Karaszi" wrote:

> No, These are not indexes, they don't have a b-tree associated with them, don't store any data.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Tarlo" <Tarlo@.discussions.microsoft.com> wrote in message
> news:500DE04C-0239-49B3-BD6C-6B90D44D6C1A@.microsoft.com...
>
>

_WA_SYS% indexes

Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes i
n
sysindexes impact performance on update, delete or insert?
Any answer will be helpful.
Thanks
TarloNo, These are not indexes, they don't have a b-tree associated with them, do
n't store any data.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tarlo" <Tarlo@.discussions.microsoft.com> wrote in message
news:500DE04C-0239-49B3-BD6C-6B90D44D6C1A@.microsoft.com...
> Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes
in
> sysindexes impact performance on update, delete or insert?
> Any answer will be helpful.
> Thanks
> Tarlo|||Thanks a bunch it helps.
Regards
Tarlo
"Tibor Karaszi" wrote:

> No, These are not indexes, they don't have a b-tree associated with them,
don't store any data.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Tarlo" <Tarlo@.discussions.microsoft.com> wrote in message
> news:500DE04C-0239-49B3-BD6C-6B90D44D6C1A@.microsoft.com...
>
>

_WA_SYS% indexes

Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes in
sysindexes impact performance on update, delete or insert?
Any answer will be helpful.
Thanks
TarloNo, These are not indexes, they don't have a b-tree associated with them, don't store any data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tarlo" <Tarlo@.discussions.microsoft.com> wrote in message
news:500DE04C-0239-49B3-BD6C-6B90D44D6C1A@.microsoft.com...
> Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes in
> sysindexes impact performance on update, delete or insert?
> Any answer will be helpful.
> Thanks
> Tarlo|||Thanks a bunch it helps.
Regards
Tarlo
"Tibor Karaszi" wrote:
> No, These are not indexes, they don't have a b-tree associated with them, don't store any data.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Tarlo" <Tarlo@.discussions.microsoft.com> wrote in message
> news:500DE04C-0239-49B3-BD6C-6B90D44D6C1A@.microsoft.com...
> > Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes in
> > sysindexes impact performance on update, delete or insert?
> >
> > Any answer will be helpful.
> >
> > Thanks
> >
> > Tarlo
>
>

_hypmv error

I just tried to update a column type from varchar(32) to varchar(64) in a
table,but got some dependency error like:
The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
But,in the dependency listing for the table I cant view any of the above,but
I have
got admin access.What are these dependecies for?any pointer will be greatly
appreciated.
A wild guess is that someone has been running Index Tuning izard which has created "Hypothetical
Materialized Views" (called Indexed Views), and these from some reason haven't been dropped. I'd
check if these are views and if your applications aren't using these, consider dropping them, using
DROP VIEW command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:53B89EB6-C82A-49B2-96C2-84A98CB02FC0@.microsoft.com...
>I just tried to update a column type from varchar(32) to varchar(64) in a
> table,but got some dependency error like:
> The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
> But,in the dependency listing for the table I cant view any of the above,but
> I have
> got admin access.What are these dependecies for?any pointer will be greatly
> appreciated.
sql

_hypmv error

I just tried to update a column type from varchar(32) to varchar(64) in a
table,but got some dependency error like:
The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
But,in the dependency listing for the table I cant view any of the above,but
I have
got admin access.What are these dependecies for?any pointer will be greatly
appreciated.A wild guess is that someone has been running Index Tuning izard which has created "Hypothetical
Materialized Views" (called Indexed Views), and these from some reason haven't been dropped. I'd
check if these are views and if your applications aren't using these, consider dropping them, using
DROP VIEW command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:53B89EB6-C82A-49B2-96C2-84A98CB02FC0@.microsoft.com...
>I just tried to update a column type from varchar(32) to varchar(64) in a
> table,but got some dependency error like:
> The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
> But,in the dependency listing for the table I cant view any of the above,but
> I have
> got admin access.What are these dependecies for?any pointer will be greatly
> appreciated.

[SQL Server]Transaction (Process ID 52) was deadlocked

I am using SQL Server 2000 and running number of query simulatneously. While
running update query it is always giving above error. The error discriptions
are -
1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock} reso
urces
with another process and has been chosen as the deadlock victim
3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
I have tried cleaning up the database, increased the fixed memory size of
SQL Server to 800 MB, restart the machine. But nothing works.
Plz help out to resolve this problem.get output from
sp_who
sp_who2
sp_lock
when the trans is running
"Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>I am using SQL Server 2000 and running number of query simulatneously.
>While
> running update query it is always giving above error. The error
> discriptions
> are -
> 1. [SQL Server]Transaction (Process ID 52) was deadlocked on {loc
k}
> resources with another process and has been chosen as the deadlock victim
> 2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock}
> resources
> with another process and has been chosen as the deadlock victim
> 3. [SQL Server]Transaction (Process ID 56) was deadlocked on {loc
k}
> resources with another process and has been chosen as the deadlock victim
>
> I have tried cleaning up the database, increased the fixed memory size of
> SQL Server to 800 MB, restart the machine. But nothing works.
> Plz help out to resolve this problem.|||Thx. I will check that.
"David J. Cartwright" wrote:

> get output from
> sp_who
> sp_who2
> sp_lock
> when the trans is running
> "Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
> news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>
>

[SQL Server]Transaction (Process ID 52) was deadlocked

I am using SQL Server 2000 and running number of query simulatneously. While
running update query it is always giving above error. The error discriptions
are -
1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock} resources
with another process and has been chosen as the deadlock victim
3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
I have tried cleaning up the database, increased the fixed memory size of
SQL Server to 800 MB, restart the machine. But nothing works.
Plz help out to resolve this problem.
get output from
sp_who
sp_who2
sp_lock
when the trans is running
"Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>I am using SQL Server 2000 and running number of query simulatneously.
>While
> running update query it is always giving above error. The error
> discriptions
> are -
> 1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
> resources with another process and has been chosen as the deadlock victim
> 2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock}
> resources
> with another process and has been chosen as the deadlock victim
> 3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
> resources with another process and has been chosen as the deadlock victim
>
> I have tried cleaning up the database, increased the fixed memory size of
> SQL Server to 800 MB, restart the machine. But nothing works.
> Plz help out to resolve this problem.
|||Thx. I will check that.
"David J. Cartwright" wrote:

> get output from
> sp_who
> sp_who2
> sp_lock
> when the trans is running
> "Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
> news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>
>

[SQL Server 2000 Driver for JDBC]Must declare the variable '@P7where'

1) I'm using MS Sql Server 2000 JDBC driver at WSAD5.1.1.
2) When I try to update the database in my DAO by using:
ps.executeUpdate();
3) Server throws the exception:
java.sql.SQLException: [Microsoft][SQL Server 2000 Driver for
JDBC][SQL Server]Must declare the variable '@.P7where'.
Anything is wrong with the MS JDBC driver with IBM WSAD? shall I set
something somewhere?
Experts, please help!
Show the JDBC code that creates the statement, sets the parameters, and calls the execute().
nauna wrote:
> 1) I'm using MS Sql Server 2000 JDBC driver at WSAD5.1.1.
> 2) When I try to update the database in my DAO by using:
> ps.executeUpdate();
> 3) Server throws the exception:
> java.sql.SQLException: [Microsoft][SQL Server 2000 Driver for
> JDBC][SQL Server]Must declare the variable '@.P7where'.
> Anything is wrong with the MS JDBC driver with IBM WSAD? shall I set
> something somewhere?
> Experts, please help!

[Microsoft][SQLServer 2000 Driver for JDBC]Row update failed

I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row update
failed" while updating an int value on result set. Below is the code snippet
that i use in java. The error is occurring occasionally. Not able to
replicate.
strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
PreparedStatement objPS = null;
...get the connection object, prepare object...using msjdbc driver with SQL
server 2000
ResultSet objRS = objPS.executeQuery();
objRS.updateInt("intCol", 2);
objRS.updateRow();
any help is appreciated.
Thanks in advance.
| Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| thread-index: AcUbT0uzY+NiEZNfSCeDqAYi6cibWQ==
| X-WBNR-Posting-Host: 152.160.15.58
| From: "=?Utf-8?B?U2FyYW4=?=" <Saran@.discussions.microsoft.com>
| Subject: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| Date: Fri, 25 Feb 2005 07:33:03 -0800
| Lines: 15
| Message-ID: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6702
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row
update
| failed" while updating an int value on result set. Below is the code
snippet
| that i use in java. The error is occurring occasionally. Not able to
| replicate.
|
| strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
| PreparedStatement objPS = null;
| ...get the connection object, prepare object...using msjdbc driver with
SQL
| server 2000
| ResultSet objRS = objPS.executeQuery();
| objRS.updateInt("intCol", 2);
| objRS.updateRow();
|
| any help is appreciated.
| Thanks in advance.
|
What is the frequency of this problem? Did the problem occur while other
activity the same database was occurring? Do you have a primary key
defined on the table? Which options did you use when preparing the
statement? Are you using the most recent build of the JDBC driver?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Hi Carb, thanks for your reply, here is my answers for your questions. Hope
that will help us to resolve the issue.
1. What is the frequency of this problem? - not able to determine,
occurring occasinoaly.
2. Did the problem occur while other activity the same database was
occurring? - I couldn't make sure it 100% though, i am possitive that there
could be some other activity on the database.
3. Do you have a primary key defined on the table? - Yes e.g. table script
tbl1(col1 varchar(100) NOT NULL, col2 int NULL)
here: col1 is primary key - but i am trying to update col2
4. Which options did you use when preparing the statement? - here is e.g.
objCon.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY)
5. Are you using the most recent build of the JDBC driver? MS-JDBC SP2
thanks
-Saran
""Carb Simien [MSFT]"" wrote:

> --
> | Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
> | thread-index: AcUbT0uzY+NiEZNfSCeDqAYi6cibWQ==
> | X-WBNR-Posting-Host: 152.160.15.58
> | From: "=?Utf-8?B?U2FyYW4=?=" <Saran@.discussions.microsoft.com>
> | Subject: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
> | Date: Fri, 25 Feb 2005 07:33:03 -0800
> | Lines: 15
> | Message-ID: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT NGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6702
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row
> update
> | failed" while updating an int value on result set. Below is the code
> snippet
> | that i use in java. The error is occurring occasionally. Not able to
> | replicate.
> |
> | strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
> | PreparedStatement objPS = null;
> | ...get the connection object, prepare object...using msjdbc driver with
> SQL
> | server 2000
> | ResultSet objRS = objPS.executeQuery();
> | objRS.updateInt("intCol", 2);
> | objRS.updateRow();
> |
> | any help is appreciated.
> | Thanks in advance.
> |
> What is the frequency of this problem? Did the problem occur while other
> activity the same database was occurring? Do you have a primary key
> defined on the table? Which options did you use when preparing the
> statement? Are you using the most recent build of the JDBC driver?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||saran wrote:
> 4. Which options did you use when preparing the statement? - here is
e.g.
> objCon.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY)
And if the ResultSet was created as read only how do you expect
updateRow() to work? Speaking of which, if the error message would have
been "General error" or simply "Error", it would have been just as easy
to track down the cause.
Alin,
The jTDS Project.
|||| Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| thread-index: AcUpfTtxzBvD5vNlRPaQXIKezzsWHg==
| X-WBNR-Posting-Host: 152.160.15.58
| From: "=?Utf-8?B?c2FyYW4=?=" <saran@.discussions.microsoft.com>
| References: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
<zQR4wCMKFHA.1136@.TK2MSFTNGXA02.phx.gbl>
| Subject: RE: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| Date: Tue, 15 Mar 2005 08:37:09 -0800
| Lines: 86
| Message-ID: <4F9F2476-D4EE-4EA1-8288-B9C2EB8D5A30@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6756
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi Carb, thanks for your reply, here is my answers for your questions.
Hope
| that will help us to resolve the issue.
|
| 1. What is the frequency of this problem? - not able to determine,
| occurring occasinoaly.
|
| 2. Did the problem occur while other activity the same database was
| occurring? - I couldn't make sure it 100% though, i am possitive that
there
| could be some other activity on the database.
|
| 3. Do you have a primary key defined on the table? - Yes e.g. table script
| tbl1(col1 varchar(100) NOT NULL, col2 int NULL)
| here: col1 is primary key - but i am trying to update col2
|
| 4. Which options did you use when preparing the statement? - here is e.g.
| objCon.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
| ResultSet.CONCUR_READ_ONLY)
|
| 5. Are you using the most recent build of the JDBC driver? MS-JDBC SP2
|
| thanks
| -Saran
|
| ""Carb Simien [MSFT]"" wrote:
|
| >
| > --
| > | Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update
failed
| > | thread-index: AcUbT0uzY+NiEZNfSCeDqAYi6cibWQ==
| > | X-WBNR-Posting-Host: 152.160.15.58
| > | From: "=?Utf-8?B?U2FyYW4=?=" <Saran@.discussions.microsoft.com>
| > | Subject: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| > | Date: Fri, 25 Feb 2005 07:33:03 -0800
| > | Lines: 15
| > | Message-ID: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT NGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6702
| > | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > |
| > | I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row
| > update
| > | failed" while updating an int value on result set. Below is the code
| > snippet
| > | that i use in java. The error is occurring occasionally. Not able to
| > | replicate.
| > |
| > | strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
| > | PreparedStatement objPS = null;
| > | ...get the connection object, prepare object...using msjdbc driver
with
| > SQL
| > | server 2000
| > | ResultSet objRS = objPS.executeQuery();
| > | objRS.updateInt("intCol", 2);
| > | objRS.updateRow();
| > |
| > | any help is appreciated.
| > | Thanks in advance.
| > |
| >
| > What is the frequency of this problem? Did the problem occur while
other
| > activity the same database was occurring? Do you have a primary key
| > defined on the table? Which options did you use when preparing the
| > statement? Are you using the most recent build of the JDBC driver?
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > Are you secure? For information about the Strategic Technology
Protection
| > Program and to order your FREE Security Tool Kit, please visit
| > http://www.microsoft.com/security.
| >
| >
|
Yes, you will need to use Resultset.CONCUR_UPDATABLE to allow the updates..
Also, the most recent build of the Microsoft JDBC driver is JDBC SP3
(build 2.2.0040):
SQL Server 2000 Driver for JDBC Service Pack 3
http://www.microsoft.com/downloads/d...B11-0502-461A-
B138-2AA54BFDC03A&displaylang=en
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

[DB2] Update Field in Table A to a Field in Table B

I am trying to change the values in Field2 of table A to the values in Field2 of table B wherever the tables have the same value for Field1.

This works in Microsoft Access, but I can't figure out how to make it work in DB2.

UPDATE A INNER JOIN B ON A.Field1 = B.Field1 SET A.Field2 = [B].[Field2];

Any ideas?I know this works in Oracle, but not tested in DB2...

UPDATE a
SET a.field2 = NVL( ( SELECT b.field2
FROM b
WHERE b.field1 = a.field1), a.field2);|||try this (untested; i don't have DB2, but i know it allows scalar subqueries in the UPDATE statement) --update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )|||Thanks, I just replaced NVL with Coalesce and it worked fine. I greatly appreciate the help.|||Rudy,

Just so you know... I tried your variation in an attempt to find a solution for d_lynch... problem I found was that:

update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )

...works for the fields that have a match, however, if there is no match, whatever was in A.FIELD2 is now replaced with a NULL.|||thanks, joe, i understand that

i wouldn't update A.Field2 with itself, though -- could be lotsa useless log activity

i'd use a WHERE clause to ensure that only those rows which had a match are actually updated|||Cool... not really into correcting other people's code, but had tried it so I thought I'd mention the results. BTW, always appreciate your answers to questions... very well thought out.

@original and stored procedures

When using @.original_{0}, how does this work if you are using a stored procedure instead of inline SQL? For example, if my update parameters are like this :

<

UpdateParameters><asp:ParameterType="String"Name="ProductType"/><asp:ParameterType="String"Name="OpportunityType"/><asp:ParameterType="Double"Name="Value"/><asp:ParameterType="String"Name="Probability"/><asp:ParameterType="Int32"Name="OpportunityID"/></UpdateParameters>

And my stored procedure like this :

CREATE procedure dbo.UpdateOpportunity

(
@.ProductType int,
@.OpportunityType int,
@.Value money,
@.Probability int,
@.OpportunityID int
)

as

update Opportunity
set ProductType = @.ProductType, OpportunityType = @.OpportunityType,
[Value] = @.Value, Probability = @.Probability
where OpportunityID = @.OpportunityID

Do I need to change both cases of @.OpportunityID in my stored procedure to @.original_OpportunityID for it to work?

Most likely. It also depends on what you have set for the conflictdetection property. If it's compareallvalues, then you'll need to accept @.original_ for each of the columns that you selected via your select statement.

@@servername NULL

I am running SQL 7 Standard on a W2K server, both at the most current
service packs. After applying the lastest security update to Windows
and rebooting the server, SQL has suddenly fogottern its name - SELECT
@.@.SERVERNAME returns NULL. In conjuction with this loss of identity,
various maintenance plan jobs seem to hang and never complete. This is
wreaking all sorts of havoc with my backups since none of them complete
successfully.
Will the sp_dropserver/sp_addserver combination fix the NULL problem?
Is there anything else I need to do?
Thanks,
M. E. HoustonYes...the dropserver, addserver should clean it up.
If you query the system table sysserver, you will probably
find that there isn't a server with the srvid = 0. The 0 is
used for the local server. If there is an entry for the
local server and srvid is not 0, you can remove it with
sp_dropserver. Uisng sp_addserver with 'local' adds the
server with the srvid = 0. If you don't specifiy 'local',
it's not added with the 0 for srvid.
-Sue
On Tue, 30 Sep 2003 11:19:09 -0500, meh
<margit.houston@.no_spam.gsiusa.com> wrote:
>I am running SQL 7 Standard on a W2K server, both at the most current
>service packs. After applying the lastest security update to Windows
>and rebooting the server, SQL has suddenly fogottern its name - SELECT
>@.@.SERVERNAME returns NULL. In conjuction with this loss of identity,
>various maintenance plan jobs seem to hang and never complete. This is
>wreaking all sorts of havoc with my backups since none of them complete
>successfully.
>Will the sp_dropserver/sp_addserver combination fix the NULL problem?
>Is there anything else I need to do?
>Thanks,
>M. E. Houston|||This solved my server name problem, but I'm still having
issues with jobs created by maintenance plans hanging. In
order to make them stop, I have to kill the process. When
I do that, the following message gets written to the log --
[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionRead (recv()).
[Microsoft][ODBC SQL Server Driver][Shared Memory]General
network error. Check your network documentation.
-- Anyone have any clue as to what this really means?
Thanks,
Margit
>--Original Message--
>Yes...the dropserver, addserver should clean it up.
>If you query the system table sysserver, you will probably
>find that there isn't a server with the srvid = 0. The 0
is
>used for the local server. If there is an entry for the
>local server and srvid is not 0, you can remove it with
>sp_dropserver. Uisng sp_addserver with 'local' adds the
>server with the srvid = 0. If you don't specifiy 'local',
>it's not added with the 0 for srvid.
>-Sue|||Just curious based on the first issue and not sure if it's
the issue or not but did you by any chance rename the server
after the jobs were created? Do you have any problems
changing, modifying jobs?
-Sue
On Thu, 2 Oct 2003 13:22:09 -0700, "M. E. Houston"
<margit.houston@.no_spam.gsiusa.com> wrote:
>This solved my server name problem, but I'm still having
>issues with jobs created by maintenance plans hanging. In
>order to make them stop, I have to kill the process. When
>I do that, the following message gets written to the log --
>[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0:
>[Microsoft][ODBC SQL Server Driver][Shared Memory]
>ConnectionRead (recv()).
>[Microsoft][ODBC SQL Server Driver][Shared Memory]General
>network error. Check your network documentation.
>-- Anyone have any clue as to what this really means?
>Thanks,
>Margit
>
>>--Original Message--
>>Yes...the dropserver, addserver should clean it up.
>>If you query the system table sysserver, you will probably
>>find that there isn't a server with the srvid = 0. The 0
>is
>>used for the local server. If there is an entry for the
>>local server and srvid is not 0, you can remove it with
>>sp_dropserver. Uisng sp_addserver with 'local' adds the
>>server with the srvid = 0. If you don't specifiy 'local',
>>it's not added with the 0 for srvid.
>>-Sue|||Sorry for the delay in reply.
I did not have any problems making changes to any of the
jobs/maintenance plans either before or after I renamed the server. I
thought the problem might have been a naming issue so I deleted all the
maintenance plans and all the jobs I had created and started over. The
new jobs under the correct server name still exhibit the same bad
behaviour. There doesn't seem to be any consistency as to where in the
job stream things fall apart, i.e., which database in the backup job it
hangs on. On some occassions, the jobs even complete as expected.
Thanks,
Margit
Sue Hoegemeier wrote:
> Just curious based on the first issue and not sure if it's
> the issue or not but did you by any chance rename the server
> after the jobs were created? Do you have any problems
> changing, modifying jobs?
> -Sue|||Sorry for the delay in reply.
I did not have any problems making changes to any of the
jobs/maintenance plans either before or after I renamed the server. I
thought the problem might have been a naming issue so I deleted all the
maintenance plans and all the jobs I had created and started over. The
new jobs under the correct server name still exhibit the same bad
behaviour. There doesn't seem to be any consistency as to where in the
job stream things fall apart, i.e., which database in the backup job it
hangs on. On some occassions, the jobs even complete as expected.
Thanks,
Margit
Sue Hoegemeier wrote:
> Just curious based on the first issue and not sure if it's
> the issue or not but did you by any chance rename the server
> after the jobs were created? Do you have any problems
> changing, modifying jobs?
> -Sue|||Can't tell you for sure why the jobs randomly hang - I've
seen jobs hang when they attempt to send mail and there are
problems somewhere in the mail configuration. You could run
profiler or a trace to try to track down what specifically
is going on when the jobs hang.
-Sue
On Wed, 08 Oct 2003 08:53:32 -0500, meh
<margit.houston@.no_spam.gsiusa.com> wrote:
>Sorry for the delay in reply.
>I did not have any problems making changes to any of the
>jobs/maintenance plans either before or after I renamed the server. I
>thought the problem might have been a naming issue so I deleted all the
>maintenance plans and all the jobs I had created and started over. The
>new jobs under the correct server name still exhibit the same bad
>behaviour. There doesn't seem to be any consistency as to where in the
>job stream things fall apart, i.e., which database in the backup job it
>hangs on. On some occassions, the jobs even complete as expected.
>Thanks,
>Margit
>Sue Hoegemeier wrote:
>> Just curious based on the first issue and not sure if it's
>> the issue or not but did you by any chance rename the server
>> after the jobs were created? Do you have any problems
>> changing, modifying jobs?
>> -Sue

@@servername

Hi,
The variable @.@.servername is not returning Machine
Name..is there a way to update the @.@.servername to reflect
actual machine name...
JohnHi,
Execute the below command:-
sp_addserver 'servername',local
Before adding the server check the server name using
sp_helpserver
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
> Hi,
> The variable @.@.servername is not returning Machine
> Name..is there a way to update the @.@.servername to reflect
> actual machine name...
> John|||Hi.
Thanks for your Input.
John
>--Original Message--
>Hi,
>Execute the below command:-
>sp_addserver 'servername',local
>Before adding the server check the server name using
>sp_helpserver
>Thanks
>Hari
>MCDBA
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
reflect[vbcol=seagreen]
>
>.
>|||Alternatively, if you are using SQL Server 2000, you can use the
SERVERPROPERTY().
So,
select SERVERPROPERTY('machineName'), SERVERPROPERTY('serverName')) will
give you the machine name and the instance name respectively
Al
"Hari Prasad" wrote:

> Hi,
> Execute the below command:-
> sp_addserver 'servername',local
> Before adding the server check the server name using
> sp_helpserver
> Thanks
> Hari
> MCDBA
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
>
>

@@servername

Hi,
The variable @.@.servername is not returning Machine
Name..is there a way to update the @.@.servername to reflect
actual machine name...
John
Hi,
Execute the below command:-
sp_addserver 'servername',local
Before adding the server check the server name using
sp_helpserver
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
> Hi,
> The variable @.@.servername is not returning Machine
> Name..is there a way to update the @.@.servername to reflect
> actual machine name...
> John
|||Hi.
Thanks for your Input.
John[vbcol=seagreen]
>--Original Message--
>Hi,
>Execute the below command:-
>sp_addserver 'servername',local
>Before adding the server check the server name using
>sp_helpserver
>Thanks
>Hari
>MCDBA
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
reflect
>
>.
>
|||Alternatively, if you are using SQL Server 2000, you can use the
SERVERPROPERTY().
So,
select SERVERPROPERTY('machineName'), SERVERPROPERTY('serverName')) will
give you the machine name and the instance name respectively
Al
"Hari Prasad" wrote:

> Hi,
> Execute the below command:-
> sp_addserver 'servername',local
> Before adding the server check the server name using
> sp_helpserver
> Thanks
> Hari
> MCDBA
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
>
>

@@servername

Hi,
The variable @.@.servername is not returning Machine
Name..is there a way to update the @.@.servername to reflect
actual machine name...
JohnHi,
Execute the below command:-
sp_addserver 'servername',local
Before adding the server check the server name using
sp_helpserver
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
> Hi,
> The variable @.@.servername is not returning Machine
> Name..is there a way to update the @.@.servername to reflect
> actual machine name...
> John|||Hi.
Thanks for your Input.
John
>--Original Message--
>Hi,
>Execute the below command:-
>sp_addserver 'servername',local
>Before adding the server check the server name using
>sp_helpserver
>Thanks
>Hari
>MCDBA
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
>> Hi,
>> The variable @.@.servername is not returning Machine
>> Name..is there a way to update the @.@.servername to
reflect
>> actual machine name...
>> John
>
>.
>|||Alternatively, if you are using SQL Server 2000, you can use the
SERVERPROPERTY().
So,
select SERVERPROPERTY('machineName'), SERVERPROPERTY('serverName')) will
give you the machine name and the instance name respectively
Al
"Hari Prasad" wrote:
> Hi,
> Execute the below command:-
> sp_addserver 'servername',local
> Before adding the server check the server name using
> sp_helpserver
> Thanks
> Hari
> MCDBA
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
> > Hi,
> >
> > The variable @.@.servername is not returning Machine
> > Name..is there a way to update the @.@.servername to reflect
> > actual machine name...
> >
> > John
>
>

@@ROWCOUNT Support?

I am trying to work a statement like the below - but am getting an error - I assume that @.@.ROWCOUNT is not supported?

UPDATE MobileInvoiceImport SET OrderDetailID = '123', QuantityReceived = '66' WHERE OrderDetailID = '123'

IF @.@.ROWCOUNT = 0

INSERT INTO MobileInvoiceImport (OrderDetailID, QuantityReceived) VALUES('123','1')

there is no support for @.@.ROWCOUNT but when you ExecuteNonQuery() on your insert command, the return value is the number of rows affected. you cannot batch together successive statements with SQL Mobile, so using this value in managed code is going to have to be your conditional value anyway.

Darren