If numeric query

I have a table with a memo field that sometimes contains an account
number at the same location. I need to find a record related to that
account in another table. I can write and run a query for a record
which works fine if the account number exists, but (obviously) get an
error if it does not. I wrote the following query which I don't
understand why it will not work.
IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1)
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
END
ELSE
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate
FROM tblRegister
END
Any help on this would be greatly appreciated.
Thanks
CharlesA couple of comments.
First, isnumeric isn't foolproof. You may wish to review this source:
isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390
Second, it appears that you are attempting to compare a string to a number,
e.g.,
SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the compari
son. And this will not use indexing, so expect performance to be poor.
If you were to add another column to the Register table, set its value to be
the substring of the Memo field (or better yet, have the application provid
e the number), and then index that column, performance would be rather sprig
htly.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegrou
ps.com...
>I have a table with a memo field that sometimes contains an account
> number at the same location. I need to find a record related to that
> account in another table. I can write and run a query for a record
> which works fine if the account number exists, but (obviously) get an
> error if it does not. I wrote the following query which I don't
> understand why it will not work.
>
> IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1)
>
> BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate, fldNote
> FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> (tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> END
>
> ELSE
>
> BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate
> FROM tblRegister
> END
>
> Any help on this would be greatly appreciated.
> Thanks
> Charles
>|||Arnie,
Thanks for the reply. My main issue is when I try to run this as an IF
statement I get an error "Incorrect syntax near the keyword 'BEGIN'."
I actually tried it without the 'Exists' after IF. If I run
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo with a where clause to
locate a record with a valid member number, it works fine. My issue is
running this when I don't have a valid member ID, which is what I am
trying to accomplish with the if statement.
It would have been better to include the member number in my register
table, but this was not in the original scope and I really don't want
to modify my table structure.
Charles
Arnie Rowland wrote:
> A couple of comments.
> First, isnumeric isn't foolproof. You may wish to review this source:
> isnumeric -What is wrong?
> http://www.aspfaq.com/show.asp?id=2390
>
> Second, it appears that you are attempting to compare a string to a number
, e.g.,
> SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> (I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
>
> You need to cast the first part as a numeric value in order make the compa
rison. And this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register table, set its value to
be the substring of the Memo field (or better yet, have the application prov
ide the number), and then index that column, performance would be rather spr
ightly.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e top yourself.
> - H. Norman Schwarzkopf
>
> <cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g
2000cwa.googlegroups.com...
> --=_NextPart_000_048F_01C6FC3D.3D5AC000
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5062
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>A couple of comments.</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>First, isnumeric isn't foolproof. You may wis
h to
> review this source:</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial; mso-no-proof: yes
">isnumeric
> -What is wrong?<BR></SPAN><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><A
> href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=
2390</A>
> </SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><FONT
> face=Arial size=2></FONT></SPAN> </P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">Second,
> it appears that you are attempting to compare a string to a number, e.g.,
> </SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><FONT
> face="Courier New">SUBSTRING(tblRegister.fldMemo,6,9) =
> tblNotes.fldMemberNo</FONT></SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">(I'm assuming
> that tblNotes.fldMemberNo is a numeric datatype.)<BR></P></SPAN>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">You
> need to cast the first part as a numeric value in order make the compariso
n.
> </SPAN><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">And
> this will not use indexing, so expect performance to be poor.</P></SPAN></
DIV>
> <DIV><FONT face=Arial size=2>If you were to add another column to the Regi
ster
> table, set its value to be the substring of the Memo field (or better yet,
have
> the application provide the number), and then index that column, performan
ce
> would be rather sprightly.</FONT><BR><FONT face=Arial size=2>-- <BR>Arnie
> Rowland, Ph.D.<BR>Westwood Consulting, Inc</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR
>Most
> experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>You can't help someone get up a hill without
> getting a little closer to the top yourself.<BR>- H. Norman
> Schwarzkopf</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><</FONT><A
> href="http://links.10026.com/?link=mailto:cbanks@.bjtsupport.com"><FONT face=Arial
> size=2>cbanks@.bjtsupport.com</FONT></A><FONT face=Arial size=2>> wrote
in
> message </FONT><A
> href="http://links.10026.com/?link=news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com"><FONT
> face=Arial
> size=2>news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com</FONT></A
><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>>I have a tab
le
> with a memo field that sometimes contains an account<BR>> number at the
same
> location. I need to find a record related to that<BR>> accoun
t in
> another table. I can write and run a query for a record<BR>>
which
> works fine if the account number exists, but (obviously) get an<BR>> er
ror if
> it does not. I wrote the following query which I don't<BR>> u
nderstand
> why it will not work.<BR>> <BR>> IF EXISTS (SELECT fldMemo FROM
> tblRegister WHERE ISNUMERIC(SUBSTRING<BR>> (tblRegister.fldMemo,6,9)) =
> 1)<BR>> <BR>> BEGIN<BR>> SELECT fldChkNo, fldDate, fldName, fldMe
mo,
> fldAmt, fldTransmitted,<BR>> fldStatus, fldLogDate, fldNote<BR>> FRO
M
> tblRegister INNER JOIN tblNotes ON SUBSTRING<BR>> (tblRegister.fldMemo,
6,9) =
> tblNotes.fldMemberNo<BR>> END<BR>> <BR>> ELSE<BR>> <BR>>
> BEGIN<BR>> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt,
> fldTransmitted,<BR>> fldStatus, fldLogDate<BR>> FROM tblRegister<BR>
> END<BR>> <BR>> Any help on this would be greatly appreciated.<BR>>
;
> Thanks<BR>> Charles<BR>></FONT></BODY></HTML>
> --=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Charles,
exists is really for subqueries. However you could use:
IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1) > 0
....
or
SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1
if @.@.rowcount > 0
....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||> exists is really for subqueries. However you could use:
> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1) > 0
I'm not sure why this is better than
IF EXISTS (SELECT 1 FROM tblRegister ...)
BEGIN
END
? The exists is potentially faster, and certainly not any slower, than a
select count.
The syntax problem he had was likely just a missing parenthesis...
A|||Thanks Aaron - my goof .
For some obscure reason I had assumed that it was returning a single row
(long night and needed more coffee!), but as I look more closely at what I
wrote I see issues with both solutions - Charles please disregard my earlier
post.
Regards,
Paul Ibison|||Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]
which made the statement invalid. The statement 'should' work as:
IF EXISTS
( SELECT fldMemo
FROM tblRegister
WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
)
BEGIN
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:uFkIfWP
$GHA.4708@.TK2MSFTNGP05.phx.gbl...
>
> I'm not sure why this is better than
>
> IF EXISTS (SELECT 1 FROM tblRegister ...)
> BEGIN
> END
>
> ? The exists is potentially faster, and certainly not any slower, than a
> select count.
>
> The syntax problem he had was likely just a missing parenthesis...
>
> A
>
>|||> Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]">
> which made the statement invalid.
Where? I didn't see his code, I just saw fragments of it.
A

If numeric query

I have a table with a memo field that sometimes contains an account
number at the same location. I need to find a record related to that
account in another table. I can write and run a query for a record
which works fine if the account number exists, but (obviously) get an
error if it does not. I wrote the following query which I don't
understand why it will not work.
IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1)
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
END
ELSE
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate
FROM tblRegister
END
Any help on this would be greatly appreciated.
Thanks
CharlesThis is a multi-part message in MIME format.
--=_NextPart_000_048F_01C6FC3D.3D5AC000
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
A couple of comments.
First, isnumeric isn't foolproof. You may wish to review this source:
isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=3D2390=20
Second, it appears that you are attempting to compare a string to a =number, e.g.,
SUBSTRING(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the =comparison. And this will not use indexing, so expect performance to be =poor.
If you were to add another column to the Register table, set its value =to be the substring of the Memo field (or better yet, have the =application provide the number), and then index that column, performance =would be rather sprightly.
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
<cbanks@.bjtsupport.com> wrote in message =news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com...
>I have a table with a memo field that sometimes contains an account
> number at the same location. I need to find a record related to that
> account in another table. I can write and run a query for a record
> which works fine if the account number exists, but (obviously) get an
> error if it does not. I wrote the following query which I don't
> understand why it will not work.
> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) =3D 1)
> > BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate, fldNote
> FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> (tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo
> END
> > ELSE
> > BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate
> FROM tblRegister
> END
> > Any help on this would be greatly appreciated.
> Thanks
> Charles
>
--=_NextPart_000_048F_01C6FC3D.3D5AC000
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

A couple of comments.
First, isnumeric isn't foolproof. You =may wish to review this source:
isnumeric -What is wrong?http://www.aspfaq.com/show.asp?id=3D2390">http://www.aspfaq.com/s=how.asp?id=3D2390
Second, it appears that you are attempting to compare a string to a number, =e.g., SUBSTRING(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the =comparison. And this will not use indexing, so expect performance to be =poor.
If you were to add another column to =the Register table, set its value to be the substring of the Memo field (or better =yet, have the application provide the number), and then index that column, =performance would be rather sprightly.-- =Arnie Rowland, Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com=...>I =have a table with a memo field that sometimes contains an account> number at =the same location. I need to find a record related to that> account =in another table. I can write and run a query for a record> =which works fine if the account number exists, but (obviously) get an> =error if it does not. I wrote the following query which I don't> =understand why it will not work.> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING> (tblRegister.fldMemo,6,9)) ==3D 1)> > BEGIN> SELECT fldChkNo, fldDate, fldName, =fldMemo, fldAmt, fldTransmitted,> fldStatus, fldLogDate, fldNote> =FROM tblRegister INNER JOIN tblNotes ON SUBSTRING> =(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo> END> > ELSE> > BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,> fldStatus, fldLogDate> FROM =tblRegister> END> > Any help on this would be greatly =appreciated.> Thanks> Charles>

---

--=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Arnie,
Thanks for the reply. My main issue is when I try to run this as an IF
statement I get an error "Incorrect syntax near the keyword 'BEGIN'."
I actually tried it without the 'Exists' after IF. If I run
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo with a where clause to
locate a record with a valid member number, it works fine. My issue is
running this when I don't have a valid member ID, which is what I am
trying to accomplish with the if statement.
It would have been better to include the member number in my register
table, but this was not in the original scope and I really don't want
to modify my table structure.
Charles
Arnie Rowland wrote:
> A couple of comments.
> First, isnumeric isn't foolproof. You may wish to review this source:
> isnumeric -What is wrong?
> http://www.aspfaq.com/show.asp?id=2390
>
> Second, it appears that you are attempting to compare a string to a number, e.g.,
> SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> (I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
>
> You need to cast the first part as a numeric value in order make the comparison. And this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register table, set its value to be the substring of the Memo field (or better yet, have the application provide the number), and then index that column, performance would be rather sprightly.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> <cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com...
> >I have a table with a memo field that sometimes contains an account
> > number at the same location. I need to find a record related to that
> > account in another table. I can write and run a query for a record
> > which works fine if the account number exists, but (obviously) get an
> > error if it does not. I wrote the following query which I don't
> > understand why it will not work.
> >
> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> > (tblRegister.fldMemo,6,9)) = 1)
> >
> > BEGIN
> > SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> > fldStatus, fldLogDate, fldNote
> > FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> > (tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> > END
> >
> > ELSE
> >
> > BEGIN
> > SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> > fldStatus, fldLogDate
> > FROM tblRegister
> > END
> >
> > Any help on this would be greatly appreciated.
> > Thanks
> > Charles
> >
> --=_NextPart_000_048F_01C6FC3D.3D5AC000
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5062
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &

---

>
>
>
>
>
> A couple of comments.
>
> First, isnumeric isn't foolproof. You may wish to
> review this source:
>
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial; mso-no-proof: yes">isnumeric
> -What is wrong? style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=2390
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> face=Arial size=2>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">Second,
> it appears that you are attempting to compare a string to a number, e.g.,
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> face="Courier New">SUBSTRING(tblRegister.fldMemo,6,9) => tblNotes.fldMemberNo
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">(I'm assuming
> that tblNotes.fldMemberNo is a numeric datatype.)
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">You
> need to cast the first part as a numeric value in order make the comparison.
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">And
> this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register
> table, set its value to be the substring of the Memo field (or better yet, have
> the application provide the number), and then index that column, performance
> would be rather sprightly.-- Arnie
> Rowland, Ph.D.Westwood Consulting, Inc
>
> Most good judgment comes from experience. Most
> experience comes from bad judgment. - Anonymous
>
> You can't help someone get up a hill without
> getting a little closer to the top yourself.- H. Norman
> Schwarzkopf
>
>
>< href="http://links.10026.com/?link=mailto:cbanks@.bjtsupport.com"> size=2>cbanks@.bjtsupport.com> wrote in
> message href="http://links.10026.com/?link=news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com"> face=Arial
> size=2>news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com face=Arial size=2>...>I have a table
> with a memo field that sometimes contains an account> number at the same
> location. I need to find a record related to that> account in
> another table. I can write and run a query for a record> which
> works fine if the account number exists, but (obviously) get an> error if
> it does not. I wrote the following query which I don't> understand
> why it will not work.> > IF EXISTS (SELECT fldMemo FROM
> tblRegister WHERE ISNUMERIC(SUBSTRING> (tblRegister.fldMemo,6,9)) => 1)> > BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo,
> fldAmt, fldTransmitted,> fldStatus, fldLogDate, fldNote> FROM
> tblRegister INNER JOIN tblNotes ON SUBSTRING> (tblRegister.fldMemo,6,9) => tblNotes.fldMemberNo> END> > ELSE> >
> BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt,
> fldTransmitted,> fldStatus, fldLogDate> FROM tblRegister>
> END> > Any help on this would be greatly appreciated.>
> Thanks> Charles>

---

> --=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Charles,
exists is really for subqueries. However you could use:
IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1) > 0
....
or
SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1
if @.@.rowcount > 0
....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||> exists is really for subqueries. However you could use:
> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1) > 0
I'm not sure why this is better than
IF EXISTS (SELECT 1 FROM tblRegister ...)
BEGIN
END
? The exists is potentially faster, and certainly not any slower, than a
select count.
The syntax problem he had was likely just a missing parenthesis...
A|||Thanks Aaron - my goof :(.
For some obscure reason I had assumed that it was returning a single row
(long night and needed more coffee!), but as I look more closely at what I
wrote I see issues with both solutions - Charles please disregard my earlier
post.
Regards,
Paul Ibison|||This is a multi-part message in MIME format.
--=_NextPart_000_057B_01C6FCC2.47296CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Actually, not a missing parens, but including an equality [ =3D 1 ) > 0 =] which made the statement invalid. The statement 'should' work as:
IF EXISTS ( SELECT fldMemo FROM tblRegister WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
)
BEGIN
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in =message news:uFkIfWP$GHA.4708@.TK2MSFTNGP05.phx.gbl...
>> exists is really for subqueries. However you could use:
>> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING >> (tblRegister.fldMemo,6,9)) =3D 1) > 0
> > I'm not sure why this is better than
> > IF EXISTS (SELECT 1 FROM tblRegister ...)
> BEGIN
> END
> > ? The exists is potentially faster, and certainly not any slower, =than a > select count.
> > The syntax problem he had was likely just a missing parenthesis...
> > A > >
--=_NextPart_000_057B_01C6FCC2.47296CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Actually, not a missing parens, but =including an equality [ =3D 1 ) > 0 ] which made the statement invalid. The =statement 'should' work as:
IF EXISTS
( SELECT =fldMemo =FROM tblRegister
=WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
=)
=BEGIN-- Arnie =Rowland, Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" = wrote in message news:uFkIfWP$GHA.4708@.TK2MSFTNGP05.phx.gbl...> exists is really for subqueries. However you could use:> IF (SELECT =count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING > (tblRegister.fldMemo,6,9)) =3D 1) > 0> > I'm not sure =why this is better than> > IF EXISTS (SELECT 1 FROM tblRegister =...)> BEGIN> END> > ? The exists is potentially =faster, and certainly not any slower, than a > select count.> > =The syntax problem he had was likely just a missing parenthesis...> => A > >

---

--=_NextPart_000_057B_01C6FCC2.47296CF0--|||> Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]
> which made the statement invalid.
Where? I didn't see his code, I just saw fragments of it.
A

If a running total is null then how to show 0.00?

Hi, I'm using Crystal Reports 8.5 and I have this issue:
I have a numeric running total field which is evaluated using a formula.
If no records satisfy that formula then the running total field becomes empty, but, instead of showing nothing (empty) I would like to show 0.00
How can I do this?

Thanks in advance

PS: I use visual basic 6.0 and reports are .rpt files outside of the .exe file.Ok, I solved it. I used a formula field which evaluates the running total, and I put the formula instead the running total on the report.|||How did you do that, Thanks.|||Create a formula having the code

If {RunningTotalField} is null then
0
else
{RunningTotalField}

IDENTITY_INSERT Problem

Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
2.8) in that I cannot insert a specific value to an identity field. (lines
below with >>> are code lines. I am using Python, but the syntax should be
about the same as VBScript)

First, I create an ADO Connection and create my table.

>>> c = win32com.client.Dispatch('ADODB.Connection')

>>> dsn = 'DRIVER=SQL
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'

>>> c.Open(dsn)

>>> sql = 'CREATE TABLE Table_Name ('

>>> sql += 'ID_Field INTEGER PRIMARY KEY IDENTITY(1,1), '

>>> sql += 'Field_2 nchar(50) NOT NULL, '

>>> sql += 'Field_3 FLOAT DEFAULT 0.0)'

>>> c.Execute(sql)

This works fine. Then, I attempt to allow insertion into the ID_Field.

>>> c.Execute("SET IDENTITY_INSERT Table_Name ON")

This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset

>>> r = win32com.client.Dispatch('ADODB.Recordset')

>>> r.Open('Table_Name', c, 2, 4)

Last, I am attempt to add a record to the recordset with an explicit ID,

>>> r.AddNew()

>>> r.Fields.Item('ID_Field').Value = 45

but this fails with the error of

"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."

Even worse, if I now try to set the identity field to allow inserts again,
Updating() causes an error that I must use an explicit value for ID_Field,
but if I try to give it one, it fails with the above error. I have to
destroy the recordset object at this point to get any further.

I am told that SET IDENTITY_INSERT only remains active for one statement and
thus must be combined with the insert, but I do not know how to do this.

There is a similar sounding bug w/ SQL 7
(http://support.microsoft.com/defaul...b;EN-US;253157), but there
is no indication that it affects newer versions of the DB. Does anyone have
any suggestions or ideas?

Thanks for any help,

-d"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in message news:<10gas90gh9n8j44@.corp.supernews.com>...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8) in that I cannot insert a specific value to an identity field. (lines
> below with >>> are code lines. I am using Python, but the syntax should be
> about the same as VBScript)

<snip
I haven't done much ADO programming, so I can't really say much about
the specific error, except to note that this KB article suggests
reviewing the connection string:

http://support.microsoft.com/defaul...kb;EN-US;269495

You might want to try connecting like this instead, to see if it makes
a difference:

c.Provider = 'sqloledb'
dsn = 'Server=MyServer;Database=MyDB;Trusted_Connection= Yes'
c.Open(dsn)

Apart from that, SET IDENTITY_INSERT remains on for your session until
you turn it off, and it can only be on for one table at a time. So I'm
not sure what you mean by putting it together with the INSERT.

One option to consider is to encapsulate your INSERT in a stored
procedure, then call the stored procedure rather than updating the
recordset directly. I don't know how well this fits with what you're
trying to do, but using stored procedures is good practice anyway:

create proc dbo.MyProc
@.ID_Field int,
@.Field_2 nchar(50),
@.Field_3 float
as
set nocount on
begin
set identity_insert dbo.Table_Name on
insert into dbo.Table_Name
(ID_Field, Field_2, Field_3)
values (@.ID_Field, @.Field_2, @.Field_3)
set identity_insert dbo.Table_Name off
end

Simon

Identity_Insert OFF

I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
What i should do ?
thank you
This is sample code pasted from SQL Books Online from the SET
IDENTITY_INSERT property:
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO
You can find the answers to almost every question about syntax in BOL.
HTH,
Mary
On Wed, 14 Apr 2004 23:06:03 -0700, George
<anonymous@.discussions.microsoft.com> wrote:

>I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
>What i should do ?
>thank you

identity, plus pk?

We have a table that has an identity field along with 5 other domain
fields. The identity field is not declared as a primary key. The
table has 3.5 million records.

A consultant was hired recently to provide insight. His major
recommendation: modify the table to make the identity field a primary
key (i.e., alter table add constraint...)

Is that sound advice? Is it OK to have a table with identity but no
primary keys? What would be the impact on performance?Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.

Actually, IDENTITY cannot be a relational key by definition. I would
drop that column and construct a proper key from the other columns. I
am willing to bet that you will fidn that you have a lot of invalid and
redudant data in this "non-table".|||>From a strict performance perspective, the presence or absence of keys
has no real effect, especially given the fact that you've managed to
collect 3.5 million records of data without relational constraints.

Your consultant probably meant to encourage you to build a unique
clustered index, which is built by default when you add a primary key
constraint. However, they are not the same; a clustered unique index
can exist without a primary key, and a primary key need not be
clustered (it must, however, be unique). Check the Books OnLine for
clustered indexes, or visit www.sql-server-performance.com for more
help with indexes.

Stu|||(newtophp2000@.yahoo.com) writes:
> We have a table that has an identity field along with 5 other domain
> fields. The identity field is not declared as a primary key. The
> table has 3.5 million records.
> A consultant was hired recently to provide insight. His major
> recommendation: modify the table to make the identity field a primary
> key (i.e., alter table add constraint...)
> Is that sound advice? Is it OK to have a table with identity but no
> primary keys? What would be the impact on performance?

If the table does have a primary key, defining one is a very good idea.
If the identity column is the only column that is unique in the table,
then there is not much choice.

It's difficult to say what the performance might be, since I don't know what
indexes there are on the table today. But if there are none at all, then
adding an index on the identity column is likely to improve things.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> If the table does have a primary key, defining one is a very good idea.
> If the identity column is the only column that is unique in the table,
> then there is not much choice.
> It's difficult to say what the performance might be, since I don't know what
> indexes there are on the table today. But if there are none at all, then
> adding an index on the identity column is likely to improve things.

Erland & Stu,

Thank you very much for your input; it was right on the money. The
table as it stands does not have any indexes and the identity field
provides the uniqueness criteria for us. The performance is quite
good. We will do some tests to see the impact of a primary
key/clustered index on overall performance before moving forward.

identity seed questions

My client has a need for the auto identity field to be 6 digits in length starting with the number 001000. They want the leading 0's preserved since this will be a casenumber. Even if I set the identity seed to 001000 it gets rid of the leading 0's. How can I get it to keep those?

Since the identity property works on numeric column leading zeroes doesn't matter. You can however use an identity column with seed 1000 and then use a computed column that formats the value in the desired format. See example below:

create table #t ( i int identity(1000, 1) not null check(i between 1000 and 999999), inum as right(replicate('0', 6) + cast(i as varchar), 6))

insert into #t default values

select * from #t

drop table #t

Identity seed lost...?

Dear all,
The last value I see for a identity field is 174. That's fine.
But the next value after insert which appears is 217 instead of 175. How do
I force the sequence 'natural' again'
I suppose that it happen due to I deleted some rows...
I would need in order to add a new row into a another table.
Thanks in advance,
EnricEnric
SET IDENTITY_INSERT
Be aware that an IDENTITY property may have gaps as well , and if it is
important , you can change to the natural key and add value to maximal key
SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How
> do
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||Thanks for your post, anyway I will not know which will be the next value in
case I delete some rows.
"Uri Dimant" wrote:
> Enric
> SET IDENTITY_INSERT
> Be aware that an IDENTITY property may have gaps as well , and if it is
> important , you can change to the natural key and add value to maximal k
ey
> SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
>
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
>
>|||Try this ...
DBCC CHECKIDENT(tablename, RESEED, 0)
DBCC CHECKIDENT(tablename, RESEED)
"Enric" wrote:

> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How d
o
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||"Enric" wrote:
> Thanks for your post, anyway I will not know which will be the next value
in
> case I delete some rows.
> "Uri Dimant" wrote:
If you want a gapless sequence then IDENTITY is the wrong solution. Don't
use IDENTITY in a way that has meaning for your users precisely because you
can't always control the IDENTITY value. IDENTITY is intended to be used as
an artificial surrogate key only.
Why do you need an IDENTITY column and why do you care if the sequence has
gaps?
David Portas
SQL Server MVP
--

Identity Property

I am setting up a field with the Identity property. I need the Seed value to
be R1 and then increment by 1. Example - R1, R2, R3 etc. Is there a way to
set the Seed Value to have the prefix of R?
Thanks!
Deb
>> Is there a way to set the Seed Value to have the prefix of R?
No, identity columns must be of numeric datatypes. However, it is easy to do
the conversion to a character string in your SELECT statement. If you must
have the values in the table, an alternative is to use a computed column
like:
CREATE TABLE tbl (
idcol INT NOT NULL IDENTITY,
...
compcol AS 'R' + CAST( idcol AS VARCHAR )
... );
Anith

Identity Property

I am setting up a field with the Identity property. I need the Seed value t
o
be R1 and then increment by 1. Example - R1, R2, R3 etc. Is there a way to
set the Seed Value to have the prefix of R?
Thanks!
Deb>> Is there a way to set the Seed Value to have the prefix of R?
No, identity columns must be of numeric datatypes. However, it is easy to do
the conversion to a character string in your SELECT statement. If you must
have the values in the table, an alternative is to use a computed column
like:
CREATE TABLE tbl (
idcol INT NOT NULL IDENTITY,
..
compcol AS 'R' + CAST( idcol AS VARCHAR )
.. );
Anith

Identity Primary Field in Merge Replication

Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.

What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e

On Server 1 - Values start from 1 then 2,3,4 etc etc

Server 2 - 24001, 24002, 24003 etc etc

Server 3 - 46001, 46002, 46003 etc etc

Server 4 - 68001, 68002, 68003

My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?

Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.

cheers

I wouldn;t advice you use only this for your primary key.

Check out this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1

Identity Primary Field in Merge Replication

Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.

What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e

On Server 1 - Values start from 1 then 2,3,4 etc etc

Server 2 - 24001, 24002, 24003 etc etc

Server 3 - 46001, 46002, 46003 etc etc

Server 4 - 68001, 68002, 68003

My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?

Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.

cheers

I wouldn;t advice you use only this for your primary key.

Check out this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1

identity on / off

does anyone know syntext to turn on / off Identity field? I usually do this
via EnterpriseMgr but I want to do this via SQL.
You can't 'turn' it on and off.
You can add a new column, transfer data if appropriate, and remove the old
column.
Enterprise Mangler creates a new table and transfers the data from the old
table to the new table. (One of the many reasons that EM is NOT a good tool
to use to manage your databases.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"David Kwon" <tjk_guard-msnewsgp@.yahoo.com> wrote in message
news:e0MFUC86GHA.1188@.TK2MSFTNGP05.phx.gbl...
> does anyone know syntext to turn on / off Identity field? I usually do
> this
> via EnterpriseMgr but I want to do this via SQL.
>
>
|||Hi,
To add on, you can not switch of the Identity property using a TSQL. But you
could reseed the identity
value using DBCC CHECKIDENT command.While resseding ensure that primary key
violation will
not happen incase the identity column is associated with PKey.
Thanks
Hari
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23yU32r86GHA.4604@.TK2MSFTNGP03.phx.gbl...
> You can't 'turn' it on and off.
> You can add a new column, transfer data if appropriate, and remove the old
> column.
> Enterprise Mangler creates a new table and transfers the data from the old
> table to the new table. (One of the many reasons that EM is NOT a good
> tool to use to manage your databases.)
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> lue "David Kwon" <tjk_guard-msnewsgp@.yahoo.com> wrote in message
> news:e0MFUC86GHA.1188@.TK2MSFTNGP05.phx.gbl...
>
|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23yU32r86GHA.4604@.TK2MSFTNGP03.phx.gbl...
> You can't 'turn' it on and off.
Unless I'm misunderstanding the question, yes you can turn it on and off. I
do it this way:
set identity_insert MyTable on
insert into MyTable
(MyIdentityColumn, MyColumn2, etc... )
values
(Value1, etc... )
set identity_insert MyTable off
--Rob Roberts

IDENTITY limit

I defined a primary field using integer 4 bytes as IDENTITY.
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I defined a primary field using integer 4 bytes as IDENTITY.
>> What will be the limit of this number?
>> I am not sure the number is big enough in 10 years of time after my
>> application have been running.
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>

IDENTITY limit

"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>

IDENTITY limit

I defined a primary field using integer 4 bytes as IDENTITY.
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>

Identity increament field?

Hi all,
I have a Identity increament column name "ID" (int). Now i have values
in that column :
+--+
| ID |
+--+
| 1 |
| 2 |
| 100 |
| 101 |
| 102 |
| 104 |
| 105 |
| 106 |
| ... |
In which situation that the value of ID column will be 3 to 99, 103 ?(
Or it's values will increase up to MAX INT and never re-use 3-99, 103
).
Thanks for reading.Hi There,
The values 3-99 and 103 will not be used. Having gaps for Identity
columns isn't anything to stress about, though.
Gaps don't really matter as the Identity column will still do what it's
supposed to do and that is give you a value in that column that is
unique among all other values in that column.|||On 28 Apr 2006 01:37:16 -0700, tienlx wrote:
>Hi all,
>I have a Identity increament column name "ID" (int). Now i have values
>in that column :
>+--+
> | ID |
>+--+
>| 1 |
>| 2 |
>| 100 |
>| 101 |
>| 102 |
>| 104 |
>| 105 |
>| 106 |
>| ... |
>In which situation that the value of ID column will be 3 to 99, 103 ?(
>Or it's values will increase up to MAX INT and never re-use 3-99, 103
>).
>Thanks for reading.
Hi tienlx,
You can use DBCC CHECKIDENT to control the next value of IDENTITY, so
that would be a way to force creation of 3 as the next value. Note that
this won't detect that 100 is already is use - if you don't reset the
identity (using DBCC CHECKIDENT again) after value 99 is generated, the
next value will be 100 and you'll either get a constraint violation or a
duplicate row (if you forgot to include a constraint).
--
Hugo Kornelis, SQL Server MVP

Identity increament field?

Hi all,
I have a Identity increament column name "ID" (int). Now i have values
in that column :
+--+
| ID |
+--+
| 1 |
| 2 |
| 100 |
| 101 |
| 102 |
| 104 |
| 105 |
| 106 |
| ... |
In which situation that the value of ID column will be 3 to 99, 103 ?(
Or it's values will increase up to MAX INT and never re-use 3-99, 103
).
Thanks for reading.Hi There,
The values 3-99 and 103 will not be used. Having gaps for Identity
columns isn't anything to stress about, though.
Gaps don't really matter as the Identity column will still do what it's
supposed to do and that is give you a value in that column that is
unique among all other values in that column.|||On 28 Apr 2006 01:37:16 -0700, tienlx wrote:

>Hi all,
>I have a Identity increament column name "ID" (int). Now i have values
>in that column :
>+--+
> | ID |
>+--+
>| 1 |
>| 2 |
>| 100 |
>| 101 |
>| 102 |
>| 104 |
>| 105 |
>| 106 |
>| ... |
>In which situation that the value of ID column will be 3 to 99, 103 ?(
>Or it's values will increase up to MAX INT and never re-use 3-99, 103
> ).
>Thanks for reading.
Hi tienlx,
You can use DBCC CHECKIDENT to control the next value of IDENTITY, so
that would be a way to force creation of 3 as the next value. Note that
this won't detect that 100 is already is use - if you don't reset the
identity (using DBCC CHECKIDENT again) after value 99 is generated, the
next value will be 100 and you'll either get a constraint violation or a
duplicate row (if you forgot to include a constraint).
Hugo Kornelis, SQL Server MVP

Identity In SQL VS Autonumber In Access

Hi

I wonder if anyone can help with the following:

when using an autonumber in access when you use .addnew you automatically
get the field in an autonumber field i.e.

rs.addnew
jno=autonofld
rs.update

jno will have a value. however in SQL you have to update first and then find
the record (I may be wrong) is there a way to get
the field value before doing the update in SQL Server ?

TIA
SteveNormally you use SCOPE_IDENTITY to return the IDENTITY value after the
INSERT. Could you explain why you want the IDENTITY value before
insertion? How do you intend to use the returned value? There are some
strategies you could use, such as generating a value first and then
INSERTing it. If you want to use an IDENTITY column though I'm not sure
what benefit you would gain by knowing the value beforehand.

Serializing INSERTs isn't recommended because that approach doesn't
scale well. It shouldn't be necessary with an IDENTITY column anyway.

--
David Portas
SQL Server MVP
--|||Thanks for the response

I need the Identity value at the time of insertion so I can display a Job
Number to the user as soon as the record is
added. The reason I used the Access example is to illustrate the retrieving
of the value before .update was to show what
I wanted to do (sorry it gave the wrong idea).

I will try the SCOPE_IDENTITY.

Thanks Again
Steve

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110275881.484473.10290@.z14g2000cwz.googlegro ups.com...
> Normally you use SCOPE_IDENTITY to return the IDENTITY value after the
> INSERT. Could you explain why you want the IDENTITY value before
> insertion? How do you intend to use the returned value? There are some
> strategies you could use, such as generating a value first and then
> INSERTing it. If you want to use an IDENTITY column though I'm not sure
> what benefit you would gain by knowing the value beforehand.
> Serializing INSERTs isn't recommended because that approach doesn't
> scale well. It shouldn't be necessary with an IDENTITY column anyway.
> --
> David Portas
> SQL Server MVP
> --|||SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
forgot to mention which version of
SQL Server I am using) do you have any other suggestions ?

Steve

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110275881.484473.10290@.z14g2000cwz.googlegro ups.com...
> Normally you use SCOPE_IDENTITY to return the IDENTITY value after the
> INSERT. Could you explain why you want the IDENTITY value before
> insertion? How do you intend to use the returned value? There are some
> strategies you could use, such as generating a value first and then
> INSERTing it. If you want to use an IDENTITY column though I'm not sure
> what benefit you would gain by knowing the value beforehand.
> Serializing INSERTs isn't recommended because that approach doesn't
> scale well. It shouldn't be necessary with an IDENTITY column anyway.
> --
> David Portas
> SQL Server MVP
> --|||Steve,

Select Max(IdentityField)+1 from TableName

Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> Select Max(IdentityField)+1 from TableName

That's not a good thing, since the value you get may not actually be
that value, if another process comes in between.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Steve (stevej@.ufrmsa1.uniforum.org.za) writes:
> SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
> forgot to mention which version of
> SQL Server I am using) do you have any other suggestions ?

Use @.@.identity instead. If you have a trigger on the table that inserts
into a secont identity table, @.@.identity will have the value from that
table. This is why scope_identity() is usually recommended, since it
returns the most recently used identity value in the current scope.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, but I have to agree with Erland

Steve
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1110279816.145987.160610@.f14g2000cwb.googlegr oups.com...
> Steve,
> Select Max(IdentityField)+1 from TableName
> Madhivanan|||Thanks

I am going this route, luckily I do not need to use triggers on the table.

Steve

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns961380405DD2Yazorman@.127.0.0.1...
> Steve (stevej@.ufrmsa1.uniforum.org.za) writes:
> > SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
> > forgot to mention which version of
> > SQL Server I am using) do you have any other suggestions ?
> Use @.@.identity instead. If you have a trigger on the table that inserts
> into a secont identity table, @.@.identity will have the value from that
> table. This is why scope_identity() is usually recommended, since it
> returns the most recently used identity value in the current scope.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

identity fields

how do I start an auto increment field at a certain number? other than 1CREATE TABLE test (
test_id int NOT NULL IDENTITY (100, 1)
)

will create a table where the test_id column starts at 100|||Open desired table in design mode
Set the Identity property to "Yes" for the desired field
Set Identity Seed property to the desired value

You can even set the increment value here.

?