Hello Group!
I submmit a SP with a update query that use multiple UNION.
This SP fire multiple sub-threads for the same SPID and fail with deadlock condition.
There is a way for suppress that sub-threads ?
TIA,
Aldair.Need more info on your sp. I'd be leary about running update queries against Union joins. You might be better off using your union query to create a temporary table or table variable that holds the index keys of the values you want to update, and then update your values in a second step.
blindman|||The threads shoukd all enlist in the same transaction and not cause a deadlock on it's own.
If you think it is causing a problem the try it with maxdop=1|||Thanks for all.
My update comand that has a where condition with several UNIONs fail when run at the production server (more than one CPU) but it work fine at test server (single CPU). This look like a bug!
When I use the OPTION clause MAXDOP=1, it work at the production box.
Thank you for help.
Aldair.|||Do you have the latest service pack?
There have been a number of bugs fixed to do with this.|||Yes, I do.
Microsoft SQL Server 2000 - 8.00.760
This is the SP 3, isnt it ?
We use the SQL Profiler to see the procedures steps and during the update the SQL Server build a multi-thread plan (we can see it using SP_WHO: SPID= n, ECID= 0,1,2,3,4), and at this point the procedure failed with error 1205 (Dead Lock victim).
Thank you for your help.
Aldair
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
Query to update 1 record in a duplicate set of records
How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.
Without more info it's hard to tell, but you just need to qualify what you want to update:
UPDATE Table
SET column = 'New Value'
WHERE column = 'youNeedADateHere'
AND orderid = 'Whatever'
GO
Something along the lines of:
UPDATE Table1
SET NewCol = 1
FROM Table1 AS i
INNER JOIN (SELECT OrderID, COUNT(*) AS c, MAX(OrderDate) AS OrderDate
FROM Table1
GROUP BY OrderID
HAVING COUNT(*) > 1) AS Dupes
ON i.OrderID = Dupes.OrderID
AND i.OrderDate = Dupes.OrderDate
|||Thank you that is close enough to what I was looking for. I appreciate your response
Query to sequentially number Null fields in a column
I'm trying to write a Query that will Update all the Null fields in Table1
column1 to 'P' and a 6 digit sequential number starting from 000001
including the leading zeros. Can someone help me figure out the correct
syntax? So far, nothing I've come up with is working right.
TIA
MattWell, if you don't want to add an IDENTITY column, and just want to add the
zero-padded char, you could:
1.) Create temp table with IDENTITY column and primary key from source table
1.) Generate identity values for all rows in target table in the temp table
2.) Update target table to include a zero-padded version of the identity
value
Example:
Let's say your table is called Customer and the primary key is CustomerKey
varchar(10)
BEGIN TRANSACTION
CREATE TABLE
#KeyGen
(
CustomerKey varchar(10) NOT NULL,
NewID int NOT NULL IDENTITY (1,1)
)
INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
WITH(TABLOCKX)
ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
Customer.CustomerKey)
DROP TABLE #KeyGen
COMMIT TRANSACTION
Error handling is an exercise for the reader.
Cheers,
James Hokes
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> I'm trying to write a Query that will Update all the Null fields in Table1
> column1 to 'P' and a 6 digit sequential number starting from 000001
> including the leading zeros. Can someone help me figure out the correct
> syntax? So far, nothing I've come up with is working right.
> TIA
> Matt
>|||The problem is the source table doesn't have a primary key. That's what I'm
creating with this query.
I've been working with this code that I found in the archive, but I can't
get it to work
update temp_Reports tr1
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
I created this table as a temporary test
CREATE TABLE [temp_Reports] (
[identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
And added these values:
1 | Test1
2 | Test2
3 | Test3
Null | Test4
Null | Test5
I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'tr1'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '+'.
but I'm not clear why.
Matt
"James Hokes" <noway@.nospamthanksanyway.com> wrote in message
news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Well, if you don't want to add an IDENTITY column, and just want to add
> the zero-padded char, you could:
> 1.) Create temp table with IDENTITY column and primary key from source
> table
> 1.) Generate identity values for all rows in target table in the temp
> table
> 2.) Update target table to include a zero-padded version of the identity
> value
> Example:
> Let's say your table is called Customer and the primary key is CustomerKey
> varchar(10)
> BEGIN TRANSACTION
> CREATE TABLE
> #KeyGen
> (
> CustomerKey varchar(10) NOT NULL,
> NewID int NOT NULL IDENTITY (1,1)
> )
> INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
> WITH(TABLOCKX)
> ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
> UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
> varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
> Customer.CustomerKey)
> DROP TABLE #KeyGen
> COMMIT TRANSACTION
>
> Error handling is an exercise for the reader.
> Cheers,
> James Hokes
> "Matt Williamson" <ih8spam@.spamsux.org> wrote in message
> news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||>> The problem is the source table doesn't have a primary key. That's what
Make sure, in the future, to declare a primary key at the time of table
definition itself. Also, unless you have at least one set of columns that
are unique in the table, you have no way out.
The error is due to the alias used in the UPDATE clause. Moreover the logic
does not take into account the rows are already NULL. Assuming the second
column is unique within the table here is a workaround:
UPDATE tbl
SET col1 = ( SELECT COUNT( * )
FROM tbl t
WHERE t.col2 <= tbl.col2
AND t.col1 IS NULL )
+ ( SELECT MAX( col1 )
FROM tbl )
WHERE col1 IS NULL ;
Anith|||Matt,
1 -
> update temp_Reports tr1
Can not use alias in this way. Try:
update temp_Reports
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= temp_Reports.identifier_id) + (select
MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
go
2 -
The code will not give the result you are expecting, because the update runs
in a transaction, so the rows updated will not be seen by the "select"
statement that is doing the counting.
AMB
"Matt Williamson" wrote:
> The problem is the source table doesn't have a primary key. That's what I'
m
> creating with this query.
> I've been working with this code that I found in the archive, but I can't
> get it to work
> update temp_Reports tr1
> set identifier_id = (select count(*) from temp_Reports tr2
> where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
> FROM temp_Reports)
> Where identifier_id is Null
> I created this table as a temporary test
> CREATE TABLE [temp_Reports] (
> [identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> And added these values:
> 1 | Test1
> 2 | Test2
> 3 | Test3
> Null | Test4
> Null | Test5
> I get
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'tr1'.
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '+'.
> but I'm not clear why.
> Matt
> "James Hokes" <noway@.nospamthanksanyway.com> wrote in message
> news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
>
>
column1 to 'P' and a 6 digit sequential number starting from 000001
including the leading zeros. Can someone help me figure out the correct
syntax? So far, nothing I've come up with is working right.
TIA
MattWell, if you don't want to add an IDENTITY column, and just want to add the
zero-padded char, you could:
1.) Create temp table with IDENTITY column and primary key from source table
1.) Generate identity values for all rows in target table in the temp table
2.) Update target table to include a zero-padded version of the identity
value
Example:
Let's say your table is called Customer and the primary key is CustomerKey
varchar(10)
BEGIN TRANSACTION
CREATE TABLE
#KeyGen
(
CustomerKey varchar(10) NOT NULL,
NewID int NOT NULL IDENTITY (1,1)
)
INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
WITH(TABLOCKX)
ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
Customer.CustomerKey)
DROP TABLE #KeyGen
COMMIT TRANSACTION
Error handling is an exercise for the reader.
Cheers,
James Hokes
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> I'm trying to write a Query that will Update all the Null fields in Table1
> column1 to 'P' and a 6 digit sequential number starting from 000001
> including the leading zeros. Can someone help me figure out the correct
> syntax? So far, nothing I've come up with is working right.
> TIA
> Matt
>|||The problem is the source table doesn't have a primary key. That's what I'm
creating with this query.
I've been working with this code that I found in the archive, but I can't
get it to work
update temp_Reports tr1
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
I created this table as a temporary test
CREATE TABLE [temp_Reports] (
[identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
And added these values:
1 | Test1
2 | Test2
3 | Test3
Null | Test4
Null | Test5
I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'tr1'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '+'.
but I'm not clear why.
Matt
"James Hokes" <noway@.nospamthanksanyway.com> wrote in message
news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Well, if you don't want to add an IDENTITY column, and just want to add
> the zero-padded char, you could:
> 1.) Create temp table with IDENTITY column and primary key from source
> table
> 1.) Generate identity values for all rows in target table in the temp
> table
> 2.) Update target table to include a zero-padded version of the identity
> value
> Example:
> Let's say your table is called Customer and the primary key is CustomerKey
> varchar(10)
> BEGIN TRANSACTION
> CREATE TABLE
> #KeyGen
> (
> CustomerKey varchar(10) NOT NULL,
> NewID int NOT NULL IDENTITY (1,1)
> )
> INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
> WITH(TABLOCKX)
> ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
> UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
> varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
> Customer.CustomerKey)
> DROP TABLE #KeyGen
> COMMIT TRANSACTION
>
> Error handling is an exercise for the reader.
> Cheers,
> James Hokes
> "Matt Williamson" <ih8spam@.spamsux.org> wrote in message
> news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||>> The problem is the source table doesn't have a primary key. That's what
Make sure, in the future, to declare a primary key at the time of table
definition itself. Also, unless you have at least one set of columns that
are unique in the table, you have no way out.
The error is due to the alias used in the UPDATE clause. Moreover the logic
does not take into account the rows are already NULL. Assuming the second
column is unique within the table here is a workaround:
UPDATE tbl
SET col1 = ( SELECT COUNT( * )
FROM tbl t
WHERE t.col2 <= tbl.col2
AND t.col1 IS NULL )
+ ( SELECT MAX( col1 )
FROM tbl )
WHERE col1 IS NULL ;
Anith|||Matt,
1 -
> update temp_Reports tr1
Can not use alias in this way. Try:
update temp_Reports
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= temp_Reports.identifier_id) + (select
MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
go
2 -
The code will not give the result you are expecting, because the update runs
in a transaction, so the rows updated will not be seen by the "select"
statement that is doing the counting.
AMB
"Matt Williamson" wrote:
> The problem is the source table doesn't have a primary key. That's what I'
m
> creating with this query.
> I've been working with this code that I found in the archive, but I can't
> get it to work
> update temp_Reports tr1
> set identifier_id = (select count(*) from temp_Reports tr2
> where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
> FROM temp_Reports)
> Where identifier_id is Null
> I created this table as a temporary test
> CREATE TABLE [temp_Reports] (
> [identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> And added these values:
> 1 | Test1
> 2 | Test2
> 3 | Test3
> Null | Test4
> Null | Test5
> I get
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'tr1'.
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '+'.
> but I'm not clear why.
> Matt
> "James Hokes" <noway@.nospamthanksanyway.com> wrote in message
> news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
>
>
Wednesday, March 7, 2012
query timeout expired
I am performing a update to 2m+ records, when it reaches 200 records it
times out ?
rsobj = db.execute("select * from Somefile where t2 is null;")
do while
db.commantimeout = 0
******** newexp & newfactor are calculated *****
SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
='" & NewFactor & "' where t1 = '" & business & "';"
DBobj.Execute(SQLLine)
Loop
When I monitor the current processors they are all awaitting commandHi
Define timeout value on an application level. Set it to default
"Tlink" <Tlink@.online.nospam> wrote in message
news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>I am performing a update to 2m+ records, when it reaches 200 records it
>times out ?
> rsobj = db.execute("select * from Somefile where t2 is null;")
> do while
> db.commantimeout = 0
> ******** newexp & newfactor are calculated *****
> SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
> ='" & NewFactor & "' where t1 = '" & business & "';"
> DBobj.Execute(SQLLine)
> Loop
> When I monitor the current processors they are all awaitting command
>|||I am unsure as to what this means and how to do it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
> Hi
> Define timeout value on an application level. Set it to default
>
>
> "Tlink" <Tlink@.online.nospam> wrote in message
> news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>|||Tlink
Set cnAdo = New ADODB.Connection
strConnect = "driver={SQL
Server};uid=...;pwd=...;server=..;database=....;Network=dbmssocn"
cnAdo.Provider = "SQLOLEDB"
cnAdo.ConnectionString = strConnect
cnAdo.CommandTimeout = 0--or what do you have here?
cnAdo.CursorLocation = adUseServer
cnAdo.Mode = adModeRead
cnAdo.Open
"Tlink" <Tlink@.online.nospam> wrote in message
news:uUUdQ4eWGHA.3624@.TK2MSFTNGP04.phx.gbl...
>I am unsure as to what this means and how to do it.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
>|||try this
SELECT subsnp_id,pop_id,allele_id
FROM AlleleFreqBySsPop as A,
(SELECT Omim_No
FROM av
WHERE Description LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM tx
WHERE Omim_Text LIKE '%LIVER%' ) as B
WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'|||Hi Omnibuzz
I think you are
about this thread. See ,what does the OP ask for ?
:-))))))))))))))))
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
> try this
> SELECT subsnp_id,pop_id,allele_id
> FROM AlleleFreqBySsPop as A,
> (SELECT Omim_No
> FROM av
> WHERE Description LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM cs
> WHERE CS_Description LIKE '%LIVER%'
> OR CS_DATA LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti
> WHERE Omim_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti_alt_title
> WHERE Omim_Alt_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM tx
> WHERE Omim_Text LIKE '%LIVER%' ) as B
> WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'
>|||Oops.. sorry wrong number :)
"Uri Dimant" wrote:
> Hi Omnibuzz
> I think you are about this thread. See ,what does the OP ask for
?
> :-))))))))))))))))
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
>
>|||Wrong query, too. :) Check out the correct post.
ML
http://milambda.blogspot.com/|||Guess I better take a break for sometime :) Thanks for pointing it out.|||You'll feel better after a peaceful w
end. :)
ML
http://milambda.blogspot.com/
times out ?
rsobj = db.execute("select * from Somefile where t2 is null;")
do while
db.commantimeout = 0
******** newexp & newfactor are calculated *****
SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
='" & NewFactor & "' where t1 = '" & business & "';"
DBobj.Execute(SQLLine)
Loop
When I monitor the current processors they are all awaitting commandHi
Define timeout value on an application level. Set it to default
"Tlink" <Tlink@.online.nospam> wrote in message
news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>I am performing a update to 2m+ records, when it reaches 200 records it
>times out ?
> rsobj = db.execute("select * from Somefile where t2 is null;")
> do while
> db.commantimeout = 0
> ******** newexp & newfactor are calculated *****
> SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
> ='" & NewFactor & "' where t1 = '" & business & "';"
> DBobj.Execute(SQLLine)
> Loop
> When I monitor the current processors they are all awaitting command
>|||I am unsure as to what this means and how to do it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
> Hi
> Define timeout value on an application level. Set it to default
>
>
> "Tlink" <Tlink@.online.nospam> wrote in message
> news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>|||Tlink
Set cnAdo = New ADODB.Connection
strConnect = "driver={SQL
Server};uid=...;pwd=...;server=..;database=....;Network=dbmssocn"
cnAdo.Provider = "SQLOLEDB"
cnAdo.ConnectionString = strConnect
cnAdo.CommandTimeout = 0--or what do you have here?
cnAdo.CursorLocation = adUseServer
cnAdo.Mode = adModeRead
cnAdo.Open
"Tlink" <Tlink@.online.nospam> wrote in message
news:uUUdQ4eWGHA.3624@.TK2MSFTNGP04.phx.gbl...
>I am unsure as to what this means and how to do it.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
>|||try this
SELECT subsnp_id,pop_id,allele_id
FROM AlleleFreqBySsPop as A,
(SELECT Omim_No
FROM av
WHERE Description LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM tx
WHERE Omim_Text LIKE '%LIVER%' ) as B
WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'|||Hi Omnibuzz
I think you are
about this thread. See ,what does the OP ask for ?:-))))))))))))))))
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
> try this
> SELECT subsnp_id,pop_id,allele_id
> FROM AlleleFreqBySsPop as A,
> (SELECT Omim_No
> FROM av
> WHERE Description LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM cs
> WHERE CS_Description LIKE '%LIVER%'
> OR CS_DATA LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti
> WHERE Omim_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti_alt_title
> WHERE Omim_Alt_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM tx
> WHERE Omim_Text LIKE '%LIVER%' ) as B
> WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'
>|||Oops.. sorry wrong number :)
"Uri Dimant" wrote:
> Hi Omnibuzz
> I think you are
about this thread. See ,what does the OP ask for ?
> :-))))))))))))))))
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
>
>|||Wrong query, too. :) Check out the correct post.
ML
http://milambda.blogspot.com/|||Guess I better take a break for sometime :) Thanks for pointing it out.|||You'll feel better after a peaceful w
end. :)ML
http://milambda.blogspot.com/
Query Timeout
Hi there,
I have a java application which connects to an sqlServer database and issues
an SQL update statement.
When you attempt to connect to the database if there is no connection then
this is fine, it will retry for x times and timeout at the time you have
set.
The problem is if you have made a valid connection and then disconnect the
database from the network and try to run the sql UPDATE. The update will
only attempt one time and basically hang until the connection is
re-established.
The other way round, if the machine the java application is running on is
disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
times and times out at the interval set.
It only seems to hang if the java application is still connected but the
database is disconnected. Is this a general problem with the jdbc driver?
Regards
Jamie
Jamie wrote:
> Hi there,
> I have a java application which connects to an sqlServer database and issues
> an SQL update statement.
> When you attempt to connect to the database if there is no connection then
> this is fine, it will retry for x times and timeout at the time you have
> set.
> The problem is if you have made a valid connection and then disconnect the
> database from the network and try to run the sql UPDATE. The update will
> only attempt one time and basically hang until the connection is
> re-established.
> The other way round, if the machine the java application is running on is
> disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
> times and times out at the interval set.
> It only seems to hang if the java application is still connected but the
> database is disconnected. Is this a general problem with the jdbc driver?
> Regards
> Jamie
It's at a lower level than that. The failure you cause means the TCP stack will
take minutes before it notifies the driver that the socket is dead. You might
try setting the query timeout on your statement before executing it. Then the
driver may be able to return control to you sooner.
Joe Weinstein at BEA
I have a java application which connects to an sqlServer database and issues
an SQL update statement.
When you attempt to connect to the database if there is no connection then
this is fine, it will retry for x times and timeout at the time you have
set.
The problem is if you have made a valid connection and then disconnect the
database from the network and try to run the sql UPDATE. The update will
only attempt one time and basically hang until the connection is
re-established.
The other way round, if the machine the java application is running on is
disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
times and times out at the interval set.
It only seems to hang if the java application is still connected but the
database is disconnected. Is this a general problem with the jdbc driver?
Regards
Jamie
Jamie wrote:
> Hi there,
> I have a java application which connects to an sqlServer database and issues
> an SQL update statement.
> When you attempt to connect to the database if there is no connection then
> this is fine, it will retry for x times and timeout at the time you have
> set.
> The problem is if you have made a valid connection and then disconnect the
> database from the network and try to run the sql UPDATE. The update will
> only attempt one time and basically hang until the connection is
> re-established.
> The other way round, if the machine the java application is running on is
> disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
> times and times out at the interval set.
> It only seems to hang if the java application is still connected but the
> database is disconnected. Is this a general problem with the jdbc driver?
> Regards
> Jamie
It's at a lower level than that. The failure you cause means the TCP stack will
take minutes before it notifies the driver that the socket is dead. You might
try setting the query timeout on your statement before executing it. Then the
driver may be able to return control to you sooner.
Joe Weinstein at BEA
Query Time Outs
Hi All.
I have some rather large SQL Server 2000 databases (around 60GB).
I have set up jobs to re-index the tables and update statistics every
sunday. This worked will for a few months. Now after a day or two of
using it the connections to it keep timing out. If i start the jobs
manually, all is well for two days or so.
Surely there can be a better solution to this ?
TIA.
Ryan,.Are the timeouts happening on the tables or on the queries run against
the tables ? The tables shouldn't time out. The queries could well
time out. Try running your queries with the execution plan showing. It
may give you some indication of what is causing the delays.
Also, do you get the same results on each PC and the server ? Try
running the query on the server if possible to see if it is server
based or networking. Networking issues may be causing a bottleneck
which then slows you down. Worth looking at if possible.
The trick will be narrowing this down to where the problem actually
lies, not just where it shows up.
budgie@.doormat.za.org (Ryan Budge) wrote in message news:<8b601867.0311200322.281b643d@.posting.google.com>...
> Hi All.
> I have some rather large SQL Server 2000 databases (around 60GB).
> I have set up jobs to re-index the tables and update statistics every
> sunday. This worked will for a few months. Now after a day or two of
> using it the connections to it keep timing out. If i start the jobs
> manually, all is well for two days or so.
> Surely there can be a better solution to this ?
> TIA.
> Ryan,.|||Hi.
ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0311200631.7f7fdf6a@.posting.google.com>...
> Are the timeouts happening on the tables or on the queries run against
> the tables ? The tables shouldn't time out. The queries could well
> time out. Try running your queries with the execution plan showing. It
> may give you some indication of what is causing the delays.
OK. Will do.
> Also, do you get the same results on each PC and the server ? Try
> running the query on the server if possible to see if it is server
> based or networking. Networking issues may be causing a bottleneck
> which then slows you down. Worth looking at if possible.
I have tested the application on the server and on client PCs. It
does not seem to make a difference. Timeouts and slow performance is
on both...
> The trick will be narrowing this down to where the problem actually
> lies, not just where it shows up.
Yep... Im sure. :-).
I remeber googling around or reading some of the books online and
reading about the sample percentage when updating statics and indexes.
Is it possible to re-index a table with a greater percentage to
enable the job to only run once a week ?
What kinda percentage is safe to use ?
Thanks for the suggestions.
Ryan.
Subscribe to:
Posts (Atom)