|
|
You're in the
sql server programming
group:Table Variable Problem, Please help!!!!!!
sql server programming:
which loops through some records and does stuff based on that, I have an update state ment like following: Declare @TMPVAR TABLE (myCol nvarchar(3)) /*Loop starts here, @TMPVAR is populated with a 3 character string*/ /*some code here*/ Update myTable set Stat=1 When otherCol IN (Select myCol from @TMPVAR) /*some code here*/ /* Loop ends here */ The above works fine, but just gets stuck on some values. @TMPVAR might have two or most often 1 rows. Now if I use that value hard coded like this: Update myTable set Stat=1 When otherCol IN ('XXX') it works fine. Strange enoguh if I use a normal variable such as Declare @myVar as nvarchar(3) And dump that value into this variable from the table variable, it works fine. Just that table variable, and just on that special value causes the script to get stuck. I know it totally doesnt make any sense, as it doesnt to me, but, I am frustrated. I tries to put the whole string into a sp to debug it, but enterprise manager hangs on me! So the questions are: 1-Has anybody have such an experience? 2-Where should I look at? (I am not using any reserved word, I am sure) 3-Is there anyway to see what actually is happening at that point of time when it's stuck? 4-If it is a deadlock, how long should sql server wait for it? I have waited about an hour and nothing happened, this part of the script doesnt take more than 50 s to run normally. Why a deadlock anyway? Thanks
Scarlet
It is hard to suggest something without seeing the actual source + sample data. Can you post some data to reproduce the problem? [quoted text, click to view] "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message news:55B8B127-C616-40EF-9EF1-8244DF414293@microsoft.com... > This totally weird, I have no clue why it's happening. As a part of a > script > which loops through some records and does stuff based on that, I have an > update state ment like following: > > Declare @TMPVAR TABLE (myCol nvarchar(3)) > /*Loop starts here, @TMPVAR is populated with a 3 character string*/ > > /*some code here*/ > Update myTable set Stat=1 When otherCol IN (Select myCol from @TMPVAR) > /*some code here*/ > > /* Loop ends here */ > > The above works fine, but just gets stuck on some values. @TMPVAR might > have > two or most often 1 rows. > > Now if I use that value hard coded like this: > > Update myTable set Stat=1 When otherCol IN ('XXX') > > it works fine. Strange enoguh if I use a normal variable such as > > Declare @myVar as nvarchar(3) > > And dump that value into this variable from the table variable, it works > fine. Just that table variable, and just on that special value causes the > script to get stuck. I know it totally doesnt make any sense, as it doesnt > to > me, but, I am frustrated. I tries to put the whole string into a sp to > debug > it, but enterprise manager hangs on me! > > So the questions are: > 1-Has anybody have such an experience? > 2-Where should I look at? (I am not using any reserved word, I am sure) > 3-Is there anyway to see what actually is happening at that point of time > when it's stuck? > 4-If it is a deadlock, how long should sql server wait for it? I have > waited > about an hour and nothing happened, this part of the script doesnt take > more > than 50 s to run normally. Why a deadlock anyway? > > Thanks > >
Hi
I did want you to put a real data here. Just to reproduce the problem that's all create table #t ( col1 int not null primary key, ..... ) insert into #t values (1,'bla bla') [quoted text, click to view] "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message news:D102C759-C7E2-488A-8F41-B21394DD459E@microsoft.com... > Hi Uri > > No sorry, I can't. The data and the actual script are strictly > confidential > and as I said this behaviour is very strange and the situation is exactly > as > I described. I haven't tried on a subset of data to see what happens, and > also I am going to use EXISTS instead of IN, and get back to you with the > results. > > Thanks > > "Uri Dimant" wrote: > >> Scarlet >> >> It is hard to suggest something without seeing the actual source + sample >> data. >> >> Can you post some data to reproduce the problem? >> >> >> >> "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message >> news:55B8B127-C616-40EF-9EF1-8244DF414293@microsoft.com... >> > This totally weird, I have no clue why it's happening. As a part of a >> > script >> > which loops through some records and does stuff based on that, I have >> > an >> > update state ment like following: >> > >> > Declare @TMPVAR TABLE (myCol nvarchar(3)) >> > /*Loop starts here, @TMPVAR is populated with a 3 character string*/ >> > >> > /*some code here*/ >> > Update myTable set Stat=1 When otherCol IN (Select myCol from @TMPVAR) >> > /*some code here*/ >> > >> > /* Loop ends here */ >> > >> > The above works fine, but just gets stuck on some values. @TMPVAR might >> > have >> > two or most often 1 rows. >> > >> > Now if I use that value hard coded like this: >> > >> > Update myTable set Stat=1 When otherCol IN ('XXX') >> > >> > it works fine. Strange enoguh if I use a normal variable such as >> > >> > Declare @myVar as nvarchar(3) >> > >> > And dump that value into this variable from the table variable, it >> > works >> > fine. Just that table variable, and just on that special value causes >> > the >> > script to get stuck. I know it totally doesnt make any sense, as it >> > doesnt >> > to >> > me, but, I am frustrated. I tries to put the whole string into a sp to >> > debug >> > it, but enterprise manager hangs on me! >> > >> > So the questions are: >> > 1-Has anybody have such an experience? >> > 2-Where should I look at? (I am not using any reserved word, I am sure) >> > 3-Is there anyway to see what actually is happening at that point of >> > time >> > when it's stuck? >> > 4-If it is a deadlock, how long should sql server wait for it? I have >> > waited >> > about an hour and nothing happened, this part of the script doesnt take >> > more >> > than 50 s to run normally. Why a deadlock anyway? >> > >> > Thanks >> > >> > >> >> >>
Uh, sorry
It should be I did NOT want you to put a real data here. [quoted text, click to view] "Uri Dimant" <urid@iscar.co.il> wrote in message news:%23h8wck9JGHA.3064@TK2MSFTNGP10.phx.gbl... > Hi > I did want you to put a real data here. Just to reproduce the problem > that's all > > create table #t > ( > col1 int not null primary key, > > .... > ) > > insert into #t values (1,'bla bla') > > > > "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message > news:D102C759-C7E2-488A-8F41-B21394DD459E@microsoft.com... >> Hi Uri >> >> No sorry, I can't. The data and the actual script are strictly >> confidential >> and as I said this behaviour is very strange and the situation is exactly >> as >> I described. I haven't tried on a subset of data to see what happens, and >> also I am going to use EXISTS instead of IN, and get back to you with the >> results. >> >> Thanks >> >> "Uri Dimant" wrote: >> >>> Scarlet >>> >>> It is hard to suggest something without seeing the actual source + >>> sample >>> data. >>> >>> Can you post some data to reproduce the problem? >>> >>> >>> >>> "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message >>> news:55B8B127-C616-40EF-9EF1-8244DF414293@microsoft.com... >>> > This totally weird, I have no clue why it's happening. As a part of a >>> > script >>> > which loops through some records and does stuff based on that, I have >>> > an >>> > update state ment like following: >>> > >>> > Declare @TMPVAR TABLE (myCol nvarchar(3)) >>> > /*Loop starts here, @TMPVAR is populated with a 3 character string*/ >>> > >>> > /*some code here*/ >>> > Update myTable set Stat=1 When otherCol IN (Select myCol from @TMPVAR) >>> > /*some code here*/ >>> > >>> > /* Loop ends here */ >>> > >>> > The above works fine, but just gets stuck on some values. @TMPVAR >>> > might >>> > have >>> > two or most often 1 rows. >>> > >>> > Now if I use that value hard coded like this: >>> > >>> > Update myTable set Stat=1 When otherCol IN ('XXX') >>> > >>> > it works fine. Strange enoguh if I use a normal variable such as >>> > >>> > Declare @myVar as nvarchar(3) >>> > >>> > And dump that value into this variable from the table variable, it >>> > works >>> > fine. Just that table variable, and just on that special value causes >>> > the >>> > script to get stuck. I know it totally doesnt make any sense, as it >>> > doesnt >>> > to >>> > me, but, I am frustrated. I tries to put the whole string into a sp to >>> > debug >>> > it, but enterprise manager hangs on me! >>> > >>> > So the questions are: >>> > 1-Has anybody have such an experience? >>> > 2-Where should I look at? (I am not using any reserved word, I am >>> > sure) >>> > 3-Is there anyway to see what actually is happening at that point of >>> > time >>> > when it's stuck? >>> > 4-If it is a deadlock, how long should sql server wait for it? I have >>> > waited >>> > about an hour and nothing happened, this part of the script doesnt >>> > take >>> > more >>> > than 50 s to run normally. Why a deadlock anyway? >>> > >>> > Thanks >>> > >>> > >>> >>> >>> > >
Scalet
Try Update myTable set Stat=1 When otherCol IN (N'XXX') declare @myvar NVARCHAR(3) SET myvar =N'blblabnala' Update myTable set Stat=1 When otherCol=myvar or Update myTable set Stat=1 When otherCol IN (Select myCol from @TMPVAR as t WHERE t.PK=myTable.PK) By PK I mean a Primary Key [quoted text, click to view] "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message news:17A91422-1319-42BA-ABB1-2A765E34FB07@microsoft.com... > Uri, > > I understand you, but the point is the situations is so bizzar, that I am > not sure if it is reproducible by sample data. The actual codes loops > through > a table with 35 rows and for each row performs several tasks, a lot of > things > happen, but only for one of the rows, and only in one update statement > (there > are few with almost the same syntax) gets stuck, without error, nothing, > just > stuck there. > And if I hard code or use a normal variable it will be fine. > > "Uri Dimant" wrote: > >> Uh, sorry >> It should be >> I did NOT want you to put a real data here. >> >> >> >> "Uri Dimant" <urid@iscar.co.il> wrote in message >> news:%23h8wck9JGHA.3064@TK2MSFTNGP10.phx.gbl... >> > Hi >> > I did want you to put a real data here. Just to reproduce the problem >> > that's all >> > >> > create table #t >> > ( >> > col1 int not null primary key, >> > >> > .... >> > ) >> > >> > insert into #t values (1,'bla bla') >> > >> > >> > >> > "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message >> > news:D102C759-C7E2-488A-8F41-B21394DD459E@microsoft.com... >> >> Hi Uri >> >> >> >> No sorry, I can't. The data and the actual script are strictly >> >> confidential >> >> and as I said this behaviour is very strange and the situation is >> >> exactly >> >> as >> >> I described. I haven't tried on a subset of data to see what happens, >> >> and >> >> also I am going to use EXISTS instead of IN, and get back to you with >> >> the >> >> results. >> >> >> >> Thanks >> >> >> >> "Uri Dimant" wrote: >> >> >> >>> Scarlet >> >>> >> >>> It is hard to suggest something without seeing the actual source + >> >>> sample >> >>> data. >> >>> >> >>> Can you post some data to reproduce the problem? >> >>> >> >>> >> >>> >> >>> "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message >> >>> news:55B8B127-C616-40EF-9EF1-8244DF414293@microsoft.com... >> >>> > This totally weird, I have no clue why it's happening. As a part of >> >>> > a >> >>> > script >> >>> > which loops through some records and does stuff based on that, I >> >>> > have >> >>> > an >> >>> > update state ment like following: >> >>> > >> >>> > Declare @TMPVAR TABLE (myCol nvarchar(3)) >> >>> > /*Loop starts here, @TMPVAR is populated with a 3 character >> >>> > string*/ >> >>> > >> >>> > /*some code here*/ >> >>> > Update myTable set Stat=1 When otherCol IN (Select myCol from >> >>> > @TMPVAR) >> >>> > /*some code here*/ >> >>> > >> >>> > /* Loop ends here */ >> >>> > >> >>> > The above works fine, but just gets stuck on some values. @TMPVAR >> >>> > might >> >>> > have >> >>> > two or most often 1 rows. >> >>> > >> >>> > Now if I use that value hard coded like this: >> >>> > >> >>> > Update myTable set Stat=1 When otherCol IN ('XXX') >> >>> > >> >>> > it works fine. Strange enoguh if I use a normal variable such as >> >>> > >> >>> > Declare @myVar as nvarchar(3) >> >>> > >> >>> > And dump that value into this variable from the table variable, it >> >>> > works >> >>> > fine. Just that table variable, and just on that special value >> >>> > causes >> >>> > the >> >>> > script to get stuck. I know it totally doesnt make any sense, as it >> >>> > doesnt >> >>> > to >> >>> > me, but, I am frustrated. I tries to put the whole string into a sp >> >>> > to >> >>> > debug >> >>> > it, but enterprise manager hangs on me! >> >>> > >> >>> > So the questions are: >> >>> > 1-Has anybody have such an experience? >> >>> > 2-Where should I look at? (I am not using any reserved word, I am >> >>> > sure) >> >>> > 3-Is there anyway to see what actually is happening at that point >> >>> > of >> >>> > time >> >>> > when it's stuck? >> >>> > 4-If it is a deadlock, how long should sql server wait for it? I >> >>> > have >> >>> > waited >> >>> > about an hour and nothing happened, this part of the script doesnt >> >>> > take >> >>> > more >> >>> > than 50 s to run normally. Why a deadlock anyway? >> >>> > >> >>> > Thanks >> >>> > >> >>> > >> >>> >> >>> >> >>> >> > >> > >> >> >>
Hi Uri
No sorry, I can't. The data and the actual script are strictly confidential and as I said this behaviour is very strange and the situation is exactly as I described. I haven't tried on a subset of data to see what happens, and also I am going to use EXISTS instead of IN, and get back to you with the results. Thanks [quoted text, click to view] "Uri Dimant" wrote:
> Scarlet > > It is hard to suggest something without seeing the actual source + sample > data. > > Can you post some data to reproduce the problem? > > > > "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message > news:55B8B127-C616-40EF-9EF1-8244DF414293@microsoft.com... > > This totally weird, I have no clue why it's happening. As a part of a > > script > > which loops through some records and does stuff based on that, I have an > > update state ment like following: > > > > Declare @TMPVAR TABLE (myCol nvarchar(3)) > > /*Loop starts here, @TMPVAR is populated with a 3 character string*/ > > > > /*some code here*/ > > Update myTable set Stat=1 When otherCol IN (Select myCol from @TMPVAR) > > /*some code here*/ > > > > /* Loop ends here */ > > > > The above works fine, but just gets stuck on some values. @TMPVAR might > > have > > two or most often 1 rows. > > > > Now if I use that value hard coded like this: > > > > Update myTable set Stat=1 When otherCol IN ('XXX') > > > > it works fine. Strange enoguh if I use a normal variable such as > > > > Declare @myVar as nvarchar(3) > > > > And dump that value into this variable from the table variable, it works > > fine. Just that table variable, and just on that special value causes the > > script to get stuck. I know it totally doesnt make any sense, as it doesnt > > to > > me, but, I am frustrated. I tries to put the whole string into a sp to > > debug > > it, but enterprise manager hangs on me! > > > > So the questions are: > > 1-Has anybody have such an experience? > > 2-Where should I look at? (I am not using any reserved word, I am sure) > > 3-Is there anyway to see what actually is happening at that point of time > > when it's stuck? > > 4-If it is a deadlock, how long should sql server wait for it? I have > > waited > > about an hour and nothing happened, this part of the script doesnt take > > more > > than 50 s to run normally. Why a deadlock anyway? > > > > Thanks > > > > > >
Uri,
I understand you, but the point is the situations is so bizzar, that I am not sure if it is reproducible by sample data. The actual codes loops through a table with 35 rows and for each row performs several tasks, a lot of things happen, but only for one of the rows, and only in one update statement (there are few with almost the same syntax) gets stuck, without error, nothing, just stuck there. And if I hard code or use a normal variable it will be fine. [quoted text, click to view] "Uri Dimant" wrote:
> Uh, sorry > It should be > I did NOT want you to put a real data here. > > > > "Uri Dimant" <urid@iscar.co.il> wrote in message > news:%23h8wck9JGHA.3064@TK2MSFTNGP10.phx.gbl... > > Hi > > I did want you to put a real data here. Just to reproduce the problem > > that's all > > > > create table #t > > ( > > col1 int not null primary key, > > > > .... > > ) > > > > insert into #t values (1,'bla bla') > > > > > > > > "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message > > news:D102C759-C7E2-488A-8F41-B21394DD459E@microsoft.com... > >> Hi Uri > >> > >> No sorry, I can't. The data and the actual script are strictly > >> confidential > >> and as I said this behaviour is very strange and the situation is exactly > >> as > >> I described. I haven't tried on a subset of data to see what happens, and > >> also I am going to use EXISTS instead of IN, and get back to you with the > >> results. > >> > >> Thanks > >> > >> "Uri Dimant" wrote: > >> > >>> Scarlet > >>> > >>> It is hard to suggest something without seeing the actual source + > >>> sample > >>> data. > >>> > >>> Can you post some data to reproduce the problem? > >>> > >>> > >>> > >>> "Scarlet" <Scarlet@discussions.microsoft.com> wrote in message > >>> news:55B8B127-C616-40EF-9EF1-8244DF414293@microsoft.com... > >>> > This totally weird, I have no clue why it's happening. As a part of a > >>> > script > >>> > which loops through some records and does stuff based on that, I have > >>> > an > >>> > update state ment like following: > >>> > > >>> > Declare @TMPVAR TABLE (myCol nvarchar(3)) > >>> > /*Loop starts here, @TMPVAR is populated with a 3 character string*/ > >>> > > >>> > /*some code here*/ > >>> > Update myTable set Stat=1 When otherCol IN (Select myCol from @TMPVAR) > >>> > /*some code here*/ > >>> > > >>> > /* Loop ends here */ > >>> > > >>> > The above works fine, but just gets stuck on some values. @TMPVAR > >>> > might > >>> > have > >>> > two or most often 1 rows. > >>> > > >>> > Now if I use that value hard coded like this: > >>> > > >>> > Update myTable set Stat=1 When otherCol IN ('XXX') > >>> > > >>> > it works fine. Strange enoguh if I use a normal variable such as > >>> > > >>> > Declare @myVar as nvarchar(3) > >>> > > >>> > And dump that value into this variable from the table variable, it > >>> > works > >>> > fine. Just that table variable, and just on that special value causes > >>> > the > >>> > script to get stuck. I know it totally doesnt make any sense, as it > >>> > doesnt > >>> > to > >>> > me, but, I am frustrated. I tries to put the whole string into a sp to > >>> > debug > >>> > it, but enterprise manager hangs on me! > >>> > > >>> > So the questions are: > >>> > 1-Has anybody have such an experience? > >>> > 2-Where should I look at? (I am not using any reserved word, I am > >>> > sure) > >>> > 3-Is there anyway to see what actually is happening at that point of > >>> > time > >>> > when it's stuck? > >>> > 4-If it is a deadlock, how long should sql server wait for it? I have > >>> > waited > >>> > about an hour and nothing happened, this part of the script doesnt > >>> > take > >>> > more > >>> > than 50 s to run normally. Why a deadlock anyway? > >>> > > >>> > Thanks > >>> > > >>> > > >>> > >>> > >>> > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |