float vs decimal

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.
Thanks.(othellomy@.yahoo.com) writes:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.

1.2334e+006 is not a legal literal for decimal. You will have to convert
in two steps, first to float, then to decimal.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Am 21 Nov 2006 22:09:05 -0800 schrieb othellomy@.yahoo.com:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.
Thanks.

select convert(decimal(20,2),cast('1.2334e+006' as float))

bye,
Helmut

float to decimal without rounding

select 'Customer_Greeting_Section_total'=
Case when
Customer_Greeting_Section_total is null then 0.00
else left (ROUND(Customer_Greeting_Section_Total , 2, 1),4)
end
from DTSTEMP1
The col is defined as float
When I run this I get
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.Hi
It is not clear why you are using left, as this a string function as doing
this may be meaningless. Removing the left function will (probably) stop the
error. See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL an
d
example data will help to solve your problem. Posting your desired results
from the query is also useful.
John
"Disney" wrote:

> select 'Customer_Greeting_Section_total'=
> Case when
> Customer_Greeting_Section_total is null then 0.00
> else left (ROUND(Customer_Greeting_Section_Total , 2, 1),4)
> end
> from DTSTEMP1
> The col is defined as float
> When I run this I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.sql

float point error

We are still seeing this error:
http://support.microsoft.com/default...b;en-us;818899
on the latest version of sqlserver2k :
select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Does anyone know when MS will release an official patch. Suposedly, the
have a dll then can give you but nothing official. This is kindof a pretty
bad bug to leave open.
> We are still seeing this error:
> http://support.microsoft.com/default...b;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/sec.../ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.

> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
http://www.aspfaq.com/
(Reverse address to reply.)
|||As you know, SQL Server QFEs are cumulative. The fix described in this
article is Build 2000.00.0807. The latest publicly available hot fix is
Build .0878, which would include a fix for this error.
http://support.microsoft.com/?kbid=838166
Also know that SP4 is currently in Beta testing and will include builds up
to .0972.
Sincerely,
Anthony Thomas

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrJl5DOAFHA.824@.TK2MSFTNGP11.phx.gbl...
> We are still seeing this error:
> http://support.microsoft.com/default...b;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/sec.../ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.

> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
http://www.aspfaq.com/
(Reverse address to reply.)
|||> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).

> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
..878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.

> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A
|||Yes, I found this out with .0859. The biggest reason I jumped on .0878, it
seemed more stable.
I also noticed the large build increase on deploying the SP4 Beta (2026?)
However, from the fix list off of the beta description, it only includes
fixes through .0972 and I've seen KB listing all the way up to .1000 +. So,
I'm not sure how a Build including only fixes to the 972 level could be
labeled 2026. That's got me stumped. Not to mention that it bombs the
replication if you've already applied 878. I've got that thread running in
the Beta newsgroup.
Now, I wouldn't want to slam PSS, because they've been very helpful,
especially in crash circumstances; however, they have been known to push hot
fixes a little too eagerly as well. I figure once it has gone public, at
least, it has somewhat stabilized. But yes, I realize, that even SP level
code bases can introduce new bugs.
As always, thanks for your insight.
Sincerely,
Anthony Thomas

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zfCiKbAFHA.3940@.TK2MSFTNGP09.phx.gbl...
> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).

> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
..878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.

> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A
|||> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.

> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).
|||> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A
|||Yea, I agree; they have to cut the beta release off at some point. I've
asked about the build number with no response though. I think it was a goof
but until I hear otherwise, we'll have to take it as they put it.
Anthony Thomas

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23c8UxPcAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A
|||Yea, that's why I said .1000 +, I know I came across one article that listed
a .1193 or something like that and have been searching ever since but no
luck...yet.
I also came across a slip-steamed install for MSDE for the new, free MS ADS
(?) server. It's an SUS system for deploying patches to Server Systems. It
had a build of 8.00.0880 but can't find a build description or why it was
inserted into this installation. MS also slip-streamed SP3a, of course, and
MS03-031, individually, like 880 wouldn't install without it.
Anthony Thomas

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCtTUMcAFHA.3088@.TK2MSFTNGP10.phx.gbl...
> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.

> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).

float point error

We are still seeing this error:
http://support.microsoft.com/defaul...kb;en-us;818899
on the latest version of sqlserver2k :
select @.@.version
--
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Does anyone know when MS will release an official patch. Suposedly, the
have a dll then can give you but nothing official. This is kindof a pretty
bad bug to leave open.> We are still seeing this error:
> http://support.microsoft.com/defaul...kb;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/se...n/ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.

> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
http://www.aspfaq.com/
(Reverse address to reply.)|||As you know, SQL Server QFEs are cumulative. The fix described in this
article is Build 2000.00.0807. The latest publicly available hot fix is
Build .0878, which would include a fix for this error.
http://support.microsoft.com/?kbid=838166
Also know that SP4 is currently in Beta testing and will include builds up
to .0972.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrJl5DOAFHA.824@.TK2MSFTNGP11.phx.gbl...
> We are still seeing this error:
> http://support.microsoft.com/defaul...kb;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/se...n/ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.

> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
http://www.aspfaq.com/
(Reverse address to reply.)|||> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).

> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
.878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.

> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A|||Yes, I found this out with .0859. The biggest reason I jumped on .0878, it
seemed more stable.
I also noticed the large build increase on deploying the SP4 Beta (2026?)
However, from the fix list off of the beta description, it only includes
fixes through .0972 and I've seen KB listing all the way up to .1000 +. So,
I'm not sure how a Build including only fixes to the 972 level could be
labeled 2026. That's got me stumped. Not to mention that it bombs the
replication if you've already applied 878. I've got that thread running in
the Beta newsgroup.
Now, I wouldn't want to slam PSS, because they've been very helpful,
especially in crash circumstances; however, they have been known to push hot
fixes a little too eagerly as well. I figure once it has gone public, at
least, it has somewhat stabilized. But yes, I realize, that even SP level
code bases can introduce new bugs.
As always, thanks for your insight.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zfCiKbAFHA.3940@.TK2MSFTNGP09.phx.gbl...
> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).

> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
.878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.

> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A|||> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.

> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).|||> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A|||Yea, I agree; they have to cut the beta release off at some point. I've
asked about the build number with no response though. I think it was a goof
but until I hear otherwise, we'll have to take it as they put it.
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23c8UxPcAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A|||Yea, that's why I said .1000 +, I know I came across one article that listed
a .1193 or something like that and have been searching ever since but no
luck...yet.
I also came across a slip-steamed install for MSDE for the new, free MS ADS
(?) server. It's an SUS system for deploying patches to Server Systems. It
had a build of 8.00.0880 but can't find a build description or why it was
inserted into this installation. MS also slip-streamed SP3a, of course, and
MS03-031, individually, like 880 wouldn't install without it.
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCtTUMcAFHA.3088@.TK2MSFTNGP10.phx.gbl...
> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.

> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).

float point error

We are still seeing this error:
http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
on the latest version of sqlserver2k :
select @.@.version
--
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Does anyone know when MS will release an official patch. Suposedly, the
have a dll then can give you but nothing official. This is kindof a pretty
bad bug to leave open.> We are still seeing this error:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/security/bulletin/ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.
> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||As you know, SQL Server QFEs are cumulative. The fix described in this
article is Build 2000.00.0807. The latest publicly available hot fix is
Build .0878, which would include a fix for this error.
http://support.microsoft.com/?kbid=838166
Also know that SP4 is currently in Beta testing and will include builds up
to .0972.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrJl5DOAFHA.824@.TK2MSFTNGP11.phx.gbl...
> We are still seeing this error:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/security/bulletin/ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.
> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).
> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
.878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.
> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A|||Yes, I found this out with .0859. The biggest reason I jumped on .0878, it
seemed more stable.
I also noticed the large build increase on deploying the SP4 Beta (2026?)
However, from the fix list off of the beta description, it only includes
fixes through .0972 and I've seen KB listing all the way up to .1000 +. So,
I'm not sure how a Build including only fixes to the 972 level could be
labeled 2026. That's got me stumped. Not to mention that it bombs the
replication if you've already applied 878. I've got that thread running in
the Beta newsgroup.
Now, I wouldn't want to slam PSS, because they've been very helpful,
especially in crash circumstances; however, they have been known to push hot
fixes a little too eagerly as well. I figure once it has gone public, at
least, it has somewhat stabilized. But yes, I realize, that even SP level
code bases can introduce new bugs.
As always, thanks for your insight.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zfCiKbAFHA.3940@.TK2MSFTNGP09.phx.gbl...
> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).
> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
.878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.
> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A|||> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).|||> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A|||Yea, I agree; they have to cut the beta release off at some point. I've
asked about the build number with no response though. I think it was a goof
but until I hear otherwise, we'll have to take it as they put it.
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23c8UxPcAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A|||Yea, that's why I said .1000 +, I know I came across one article that listed
a .1193 or something like that and have been searching ever since but no
luck...yet.
I also came across a slip-steamed install for MSDE for the new, free MS ADS
(?) server. It's an SUS system for deploying patches to Server Systems. It
had a build of 8.00.0880 but can't find a build description or why it was
inserted into this installation. MS also slip-streamed SP3a, of course, and
MS03-031, individually, like 880 wouldn't install without it.
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCtTUMcAFHA.3088@.TK2MSFTNGP10.phx.gbl...
> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).

Float Errors

Simple way of testing this
CREATE TABLE TEST (COL1 FLOAT)
INSERT INTO TEST (COL1) VALUES (8746.02)
SELECT * FROM TEST
This is the result.
8746.0200000000004
How do I stop this from happening, I am inserting into someone else's system
so I can not change the data type.
Any help would be appreciated.
Thanks,
DanielSorry
SQL Server 2000 SP3
Dan
"Daniel Jeffrey" <daniel@.enprisesolutions.com> wrote in message
news:eFQtcyZ9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Simple way of testing this
> CREATE TABLE TEST (COL1 FLOAT)
> INSERT INTO TEST (COL1) VALUES (8746.02)
> SELECT * FROM TEST
> This is the result.
> 8746.0200000000004
> How do I stop this from happening, I am inserting into someone else's
system
> so I can not change the data type.
> Any help would be appreciated.
> Thanks,
> Daniel
>|||The problem is in the datatype. BOL will tell you that float is an approxim
ate datatype. Which means that the exact number stored is not always what w
as inteded for storage. There is no way to get around this unless you handl
e your own rounding (See Ro
und function in BOL). Even then you can get unexpected results.|||... so if you expect to get out what you put in, use an exact datatype, lik
e
a NUMERIC datatype, for instance.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:5D884697-8F47-45A7-88A3-4E0B38DA61DF@.microsoft.com...
> The problem is in the datatype. BOL will tell you that float is an
approximate datatype. Which means that the exact number stored is not
always what was inteded for storage. There is no way to get around this
unless you handle your own rounding (See Round function in BOL). Even then
you can get unexpected results.|||Daniel,
The FLOAT data type can only represent finitely many of the
infinitely-many real numbers. The exact values FLOAT can represent are
those of the form N/power(2,k) where N is an integer with absolute value
between 2^52 and 2^53 and k is an integer between -930 and +1077 (or
something close to this - I didn't verify the exact details). The real
number 8746.02 cannot be written in that form, so the closest
representable float is inserted into the table.
So basically, whoever created the table TEST did not provide a place
to put the exact value 8746.02. If, however, you know that all values
inserted into TEST.COL1 were base-ten decimals with at most 10
significant digits and at most 2 decimal places, the value inserted can
be retrieved with SELECT CAST(COL1 AS DECIMAL(10,2)) FROM TEST, since
there is a unique decimal(10,2) that could have produced each value of
COL1 between -100000000.00 and 100000000.00 in the table.
SK
Daniel Jeffrey wrote:

>Simple way of testing this
>CREATE TABLE TEST (COL1 FLOAT)
>INSERT INTO TEST (COL1) VALUES (8746.02)
>SELECT * FROM TEST
>This is the result.
>8746.0200000000004
>How do I stop this from happening, I am inserting into someone else's syste
m
>so I can not change the data type.
>Any help would be appreciated.
>Thanks,
>Daniel
>
>|||I have found rounding issues with this operation as well

Float Errors

Simple way of testing this
CREATE TABLE TEST (COL1 FLOAT)
INSERT INTO TEST (COL1) VALUES (8746.02)
SELECT * FROM TEST
This is the result.
8746.0200000000004
How do I stop this from happening, I am inserting into someone else's system
so I can not change the data type.
Any help would be appreciated.
Thanks,
DanielSorry
SQL Server 2000 SP3
Dan
"Daniel Jeffrey" <daniel@.enprisesolutions.com> wrote in message
news:eFQtcyZ9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Simple way of testing this
> CREATE TABLE TEST (COL1 FLOAT)
> INSERT INTO TEST (COL1) VALUES (8746.02)
> SELECT * FROM TEST
> This is the result.
> 8746.0200000000004
> How do I stop this from happening, I am inserting into someone else's
system
> so I can not change the data type.
> Any help would be appreciated.
> Thanks,
> Daniel
>|||The problem is in the datatype. BOL will tell you that float is an approximate datatype. Which means that the exact number stored is not always what was inteded for storage. There is no way to get around this unless you handle your own rounding (See Round function in BOL). Even then you can get unexpected results.|||... so if you expect to get out what you put in, use an exact datatype, like
a NUMERIC datatype, for instance.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:5D884697-8F47-45A7-88A3-4E0B38DA61DF@.microsoft.com...
> The problem is in the datatype. BOL will tell you that float is an
approximate datatype. Which means that the exact number stored is not
always what was inteded for storage. There is no way to get around this
unless you handle your own rounding (See Round function in BOL). Even then
you can get unexpected results.|||Daniel,
The FLOAT data type can only represent finitely many of the
infinitely-many real numbers. The exact values FLOAT can represent are
those of the form N/power(2,k) where N is an integer with absolute value
between 2^52 and 2^53 and k is an integer between -930 and +1077 (or
something close to this - I didn't verify the exact details). The real
number 8746.02 cannot be written in that form, so the closest
representable float is inserted into the table.
So basically, whoever created the table TEST did not provide a place
to put the exact value 8746.02. If, however, you know that all values
inserted into TEST.COL1 were base-ten decimals with at most 10
significant digits and at most 2 decimal places, the value inserted can
be retrieved with SELECT CAST(COL1 AS DECIMAL(10,2)) FROM TEST, since
there is a unique decimal(10,2) that could have produced each value of
COL1 between -100000000.00 and 100000000.00 in the table.
SK
Daniel Jeffrey wrote:
>Simple way of testing this
>CREATE TABLE TEST (COL1 FLOAT)
>INSERT INTO TEST (COL1) VALUES (8746.02)
>SELECT * FROM TEST
>This is the result.
>8746.0200000000004
>How do I stop this from happening, I am inserting into someone else's system
>so I can not change the data type.
>Any help would be appreciated.
>Thanks,
>Daniel
>
>|||I have found rounding issues with this operation as wellsql

Float Datatype Truncation Bug

Hi Friends,

I have a table

Create table #table1(a float)

insert into #table1 values(123456789.987654321)

select a from #table1

drop table #table1

when i run this in SQLServer 2005 Management Studio i get the following truncated output

(1 row(s) affected)

a

-

123456789.987654

(1 row(s) affected)

when i run this query using SQL Query Analyser or OSQL Utility i get the following output

(1 row(s) affected)

a
--
123456789.98765431

(1 row(s) affected)

I want the full output in SQLServer 2005 itself.... Is this a microsoft bug?

I would like to know how to fix this?

Thanks and Regards,

It is not a bug. Your data is not truncated on the table(while storing). its bcs of the Management Console only.(MC result only truncate the values). Connect the same SQL Server 2005 from QA you will get the same result as 2000 (in your case your proper result).

If you want to trust your result use the following query.(explicit precision setting)

Code Snippet

Create table #table1(a float)

Insert into #table1 values(123456789.987654321)

Select cast(a as numeric(38,8)) from #table1

Drop table #table1

Float Datatype

Hi Friends,

I have a table

Create table #table1(a float)

insert into #table1 values(123456789.987654321)

select a from #table1

drop table #table1

when i run this in SQLServer 2005 Management Studio i get the following truncated output

(1 row(s) affected)

a

-

123456789.987654

(1 row(s) affected)

when i run this query using SQL Query Analyser or OSQL Utility i get the following output

(1 row(s) affected)

a
--
123456789.98765431

(1 row(s) affected)

I want the full output in SQLServer 2005 itself.... Is this a microsoft bug?

I would like to know how to fix this?

Thanks and Regards,

It is not a bug. Your data is not truncated on the table(while storing). its bcs of the Management Console only.(MC result only truncate the values). Connect the same SQL Server 2005 from QA you will get the same result as 2000 (in your case your proper result).

If you want to trust your result use the following query.(explicit precision setting)

Code Snippet

Create table #table1(a float)

Insert into #table1 values(123456789.987654321)

Select cast(a as numeric(38,8)) from #table1

Drop table #table1

sql

Flat file with a standard of 4

Hi

I am trying to import a flat file into a table, and from there select values from the table and insert the appropriate values into different tables

The flat file is pipe delimited. I.E

File Example:

01|Name|Surname|BenCode|Counter||||||DateTime

02|Name|Surname|BenCode|SchemeID|SchemeName|

03|Name|Surname|BenCode|ID||||Date_From|Date_To||||||||||

04|Name|Surname|BenCode|SchemeID|SchemeName||||CodeID|CodeDescription||

All these different fields are in one flat file. (It would be nice if they were in 4 seperate flat files but they're not)

I want to take the file, where the ID = 01 then the data must go into table Q1

WHERE the ID = 02 then the data must go into table Q2 and so on

When i tried to do it with SSIS, it started creating columns according to the file, but it takes the first row and counts only that rows fields and calculates the columns based on the firs record, but some of the records have more fields than that of the first row.

If i can just get this flat file imported into a single table then i can split the data up based on the table.

Any ideas will be welcome. I'm quite new to SSIS.

Kind Regards

Carel Greaves

To handle the varying number of columns, you can bring each row in as a single column, then parse it in a script component. By adding multiple outputs to the script task, you can send each record type to it's own unique output. Here's a few examples:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx

Flag Random Record

I am using this select statement to radomly display a record

SelectCommand="SELECT TOP 1 * FROM [TBL_Example] ORDER BY NEWID()

I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.

I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement. I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.

Any ideas?I probably need a statement like this in my code behind:

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
SqlDataSource1.UpdateParameters("Flag").DefaultValue = "True"
End Sub

and then I probably need some insert parameters, here is what I am working with right now with my sqldatasource

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT TOP 1 * FROM [CIT_ContactInfo] ORDER BY NEWID()"
updatecommand="update [CIT_ContactInfo] set [Flag] = @.Flag where [UserName] = @.UserName">
<UpdateParameters>
<asp:Parameter Name="Flag" Type="boolean" />
</UpdateParameters>
</asp:SqlDataSource
Does any of this look remotely correct?

First(), Last(0 function

Hello,
I have this query in access and I woul like to convert it to SQL.
--
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
--
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
-ScottScott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
returns[vbcol=seagreen]
>|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> returns
>

First(), Last(0 function

Hello,
I have this query in access and I woul like to convert it to SQL.
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
-Scott
Scott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>
|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
returns
>
|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> returns
>

First(), Last(0 function

Hello,
I have this query in access and I woul like to convert it to SQL.
--
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
--
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
--
-ScottScott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> > Hello,
> > I have this query in access and I woul like to convert it to SQL.
> > --
> > SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> > FROM DIMAGE INNER JOIN KEYC
> > ON DIMAGE.ProvID = KEYC.ProvID
> > WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> > ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> > GROUP BY KEYC.ProvID;
> > --
> >
> > the problem here is the Last function. I am running SQL 7 and it
returns
> > with "'Last' is not a known function."
> > Any sugestions?
> >
> > --
> > -Scott
> >
> >
>|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
>> Scott,
>> Try MIN() and MAX().
>> HTH
>> Jerry
>> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
>> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
>> > Hello,
>> > I have this query in access and I woul like to convert it to SQL.
>> > --
>> > SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
>> > FROM DIMAGE INNER JOIN KEYC
>> > ON DIMAGE.ProvID = KEYC.ProvID
>> > WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
>> > ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
>> > GROUP BY KEYC.ProvID;
>> > --
>> >
>> > the problem here is the Last function. I am running SQL 7 and it
> returns
>> > with "'Last' is not a known function."
>> > Any sugestions?
>> >
>> > --
>> > -Scott
>> >
>> >
>>
>

First Occurence of the record

Can any one let me know as how can I query the first occurence of a record. I need to select all distinct Order Types from a table. If there is more than one record with the same Order Type, then I need to get the first record.
It would be a great help to me.
Thanks,
JohnHow do you determine which one is the first record ?|||I mean any randon first record. I used min (rowid) but it did not work.

Please help me out.

John|||Did you try to use Top 1 * with Order By RowID (a primary key)|||john, what's the primary key of this table?

first n records

How can I select only the first n records from a table ?

Regards,

Ciornei Mihai

Simple:

select top n * from table

e.g.

select top 100 * from suppliers

or

select top 3000 address, contact from suppliers

Although this doesn't seem to work in SQL 2005 Compact Edition.

|||

OK I know top n dosen't work.

Also set rowcount = n dosen't work.

So ... what would be the solution?

|||Sorry, you didn't actually mention you were using CE, but I should have guessed. I'm not sure you can do it in CE, if I'm wrong, someone please let me know!|||Get all the records and only use the first x - or wait for SQL CE 3.5, which will support TOP.

first date

I wrote 03.20.2005. datetime, and I want from Select to find first date that
is smaller then curent date.
Can I do that from Select?SELECT dt
FROM YourTable
WHERE dt < '20050320'
David Portas
SQL Server MVP
--|||By "first" I take it you mean the earliest
SELECT MIN(dt)
FROM YourTable
WHERE dt < '20050320'
David Portas
SQL Server MVP
--|||This i need to modify:
SELECT MAX(Dat) AS Dat, km
FROM tblZap
GROUP BY ZapID, km
HAVING (ZapID = 1) AND (MAX(Dat) < CONVERT(DATETIME, '2005-03-07
00:00:00', 102))
I need to get km from first date that is smaller then this date:
'2005-03-07.
2005-03-05
2005-03-04
2005-03-03
I need to get 2005-03-05
'

> SELECT MIN(dt)
> FROM YourTable
> WHERE dt < '20050320'
> --
> David Portas
> SQL Server MVP
> --
>|||SELECT DISTINCT dat, km
FROM tblZap
WHERE zapid = 1
AND dat =
(SELECT MAX(dat)
FROM tblZap
WHERE dat < '20050307'
AND zapid = 1)
Note that if you format your date literals without a hyphen then you
won't need to use CONVERT. The string '20050307' will be reliably
converted to a DATETIME.
David Portas
SQL Server MVP
--|||Thanks, this works but I have one more problem. If I have two equal date,
this select gave me two records, but i need only one record.
ID;Date;km
122;2005-03-05;10
123;2005-03-05;27
In this case, I need last one with km = 27. Can I search MAX ID in select
bellow?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110809018.766783.256520@.o13g2000cwo.googlegroups.com...
> SELECT DISTINCT dat, km
> FROM tblZap
> WHERE zapid = 1
> AND dat =
> (SELECT MAX(dat)
> FROM tblZap
> WHERE dat < '20050307'
> AND zapid = 1)
> Note that if you format your date literals without a hyphen then you
> won't need to use CONVERT. The string '20050307' will be reliably
> converted to a DATETIME.
> --
> David Portas
> SQL Server MVP
> --
>

First CTP and spatial

I just download and install first CTP. When I execute query

Code Snippet

select * from sys.types

i don't see any spatial data types?

May be i do something wrong? Or spatial unsupported in first CTP?

Spatial data feature is not included in the June CTP. The demo at Tech-Ed is based on a non-CTP build.

Hope this helps!

Changqing|||

>>Or spatial unsupported in first CTP?

No, this is not available in the first CTP. For a list of features in the CTP, open Books Online and search for " What's New". There's a What's New (Database Engine), What's New (Reporting Services), etc. topic for each SQL Server component that describes the new features available in the CTP with links to the relevant topics.

Regards,

Gail

|||Can you give us a time estimate when this WILL be included ;-)|||I've seen a similar question here already, and the correct answer is: When it's finished. Microsoft develop using a different strategy now, they include features when they seem to be ready for the market, so as far as I've understood the situation no new features are officially scheduled for a specific release or a specific date.|||

The spatial functionality I demo'ed at TechEd is not part of CTP3 or the upcoming CTP4. We cannot give an official delivery date yet, but we hope to have it available after CTP4. ;-)

Best regards

Michael

First CTP and spatial

I just download and install first CTP. When I execute query

Code Snippet

select * from sys.types

i don't see any spatial data types?

May be i do something wrong? Or spatial unsupported in first CTP?

Spatial data feature is not included in the June CTP. The demo at Tech-Ed is based on a non-CTP build.

Hope this helps!

Changqing|||

>>Or spatial unsupported in first CTP?

No, this is not available in the first CTP. For a list of features in the CTP, open Books Online and search for " What's New". There's a What's New (Database Engine), What's New (Reporting Services), etc. topic for each SQL Server component that describes the new features available in the CTP with links to the relevant topics.

Regards,

Gail

|||Can you give us a time estimate when this WILL be included ;-)|||I've seen a similar question here already, and the correct answer is: When it's finished. Microsoft develop using a different strategy now, they include features when they seem to be ready for the market, so as far as I've understood the situation no new features are officially scheduled for a specific release or a specific date.|||

The spatial functionality I demo'ed at TechEd is not part of CTP3 or the upcoming CTP4. We cannot give an official delivery date yet, but we hope to have it available after CTP4. ;-)

Best regards

Michael

First and Last day of week

Hi All
I have a report that brings back data based on the week a user selects.
The user will select a week number (e.g. week 1) then the report will automatically generate the first date (Monday) and last date of that week (Sunday), based on a whole week for the current year.
So, user selects week 3 for this year. The report generates the first date of the week: 16/01/2006 and last date of the week: 22/01/2006.

Note: I’m in the UK, so I am using the UK date format.

Try this:

Code/

Declare @.DOW As Char(10), @.weekdate Datetime, @.Monday DateTime, @.Sunday DateTime,

@.Working As Int, @.WeekNo Int

-- Find first day of year

Set @.WeekDate = (select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

--Find a date in this week - any date

Set @.Working = @.WeekNo * 7

Select @.WeekDate = DateAdd(dd,@.Working,@.WeekDate)

Get day of week and subtract number of days to get the Monday

Set @.DOW = DateName(dw,@.Weekdate)

Set @.Monday = @.Weekdate

Set @.Monday = Case @.DOW

When 'Sunday' Then DateAdd(dd,-6,@.WeekDate)

When 'Saturday' Then DateAdd(dd,-5,@.WeekDate)

When 'Friday' Then DateAdd(dd,-4,@.WeekDate)

When 'Thursday' Then DateAdd(dd,-3,@.WeekDate)

When 'Wednesday' Then DateAdd(dd,-2,@.WeekDate)

When 'Tuesday' Then DateAdd(dd,-1,@.WeekDate)

Else @.Monday

End

Set @.Sunday = DateAdd(dd,6,@.Monday)

Select @.Monday, @.Sunday

/Code

You feed in @.WeekNo