Floating Point Error - Order By Mystery

I'm having a problem that I think is due to corrupt data. Depending on

the column I use in my order by clause two problems are occuring.

1. No results are returned and I get this error:
A floating point exception occured in the user process.

2. Results are returned but there are a different number of rows depending on which columns I use in my Order By clause.

Examples
SELECT * FROM SymbolStats
ORDER BY calc_date, symbol

Returns - 12207 rows but only includes one of the 25 dates in the table.

-

SELECT * from SymbolStats
ORDER BY current_hv

Returns - 0 rows.

-

SELECT * from SymbolStats
ORDER BY average_hv

Returns - floating point error

With more conditions in the WHERE clause the number of results returned varies greatly.

The

fact that different numbers of rows can be returned from the same query

only differing in how they are ordered seems like a bug.

Does this sound like corrupt data? If so, what are the best methods for fixing it?

Thanks,
patrickYou can run DBCC CHECKDB on your database to look for any possible corruption. You should also check your NT Eventlog for any hardware failures or driver issues or OS related problems.

flatten out a normalized child table?

I need to extract Customer Order data, and join it to normalized ship-to
table so I can get their address on a single line/row of data. The Column
names are not importat in that final flat file, just what was in
row1,2,3,...6
GARY C Test Row1
LISA C Test Row2
816 RIVERVIEW PLACE Row3
WASHINGTON, MO 63090 Row4
Row5
Row6
THOMAS H Other-Test Row1
2102 N SHAMROCK RD Row2
BEL AIR, MD 21014 Row3
Row4 ,etc.
I have to account for double names names, and possibably titles, Suite #,
etc.
TIAPlease post DDL with your sample data. What is the key of the table you
posted? What relates the address lines together to make a single
address? Apparently nothing links an address together in the sketch you
posted except for the order in which you typed them out. We know that
tables have no fixed order so it isn't possible to combine the rows
reliably to make addresses out of each one.
If you had an additional column such as contact_name or contact_no for
each address line then you could do something like:
SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
MAX(CASE WHEN row_num = 2 THEN addr END),
MAX(CASE WHEN row_num = 3 THEN addr END),
... etc
FROM your_table
GROUP BY contact_name ;
Hope this helps.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129058354.597302.39690@.g44g2000cwa.googlegroups.com...
> Please post DDL with your sample data. What is the key of the table you
> posted? What relates the address lines together to make a single
> address? Apparently nothing links an address together in the sketch you
> posted except for the order in which you typed them out. We know that
> tables have no fixed order so it isn't possible to combine the rows
> reliably to make addresses out of each one.
> If you had an additional column such as contact_name or contact_no for
> each address line then you could do something like:
> SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
> MAX(CASE WHEN row_num = 2 THEN addr END),
> MAX(CASE WHEN row_num = 3 THEN addr END),
> ... etc
> FROM your_table
> GROUP BY contact_name ;
Thanks

Flat file to table - rows out of order

Hi,

I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table.

In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file.

All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case.

Is this normal?

Thanks

Is it normal? Well...its not not normal!

There is no concept of order in a database table. You should never assume that rows will get returned to you in the order that (you assume) they were inserted.

-Jamie

|||

That is not my understanding. For example, if you create a table, then insert a bunch of rows, one at a time, they will most definitely be returned in the order they were inserted. I have *never* seen an exception to this.

Perhaps the SSIS package is not inserting the rows in the order they are in the file?

Anyhow, I could be wrong, but this goes against my experience completely.

|||Not to sound mean or anything, but Jamie is absolutely right. There is no such thing as ordering in database land. Just because your experience "proves" otherwise, doesn't make it fact. The only way to guarantee order is to use an ORDER BY clause on your SQL statement which only controls the PRESENTATION of the data, not the way it's stored.

Do you have a situation that the records are out of order when ordering by an identity column, or are you merely using a "select * from table" statement without an ORDER BY clause?

This is perfectly normal behavior. You might want to add a sort transformation right before the destination. But still, there are no guarantees that the data will be stored "in order."|||By its definition, a database table is an unordered set of rows. While "most" of the time, a select without an ORDER BY clause will return the rows in the order they were entered, it is never guaranteed. The only way to guarantee retrieving rows in the order you want is with an ORDER BY clause on the query.|||

There are many factors that influence the order in which rows are returned. The most obvious being the presence of indexes.

Other possible causes may be the number of processors, what data is cached, datafile placement, datafile fill factors, hard drive configuration. There are a million and one things.

These same factors that affect the retrieval of data can also affect the insertion of data. Hopefully you can see how the order in which data is retrieved can be affected.

There is no concept of order in a database table. Period.

-Jamie

|||

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

|||

sadie519590 wrote:

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

No worries. All the training courses in the world wouldn't have taught you this. The only way you learn a product is by using it. I've been using this damn thing for seven years now and I only know a fraction of it

-Jamie

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?

Fixed Height - Invoices

Hi,
I am trying to create invoices, quotes, order documents using RS.
Typically for these documents, the totals go at the footer of the report.
We also have lines indicating columns so it's easy to look down the totals
column. How might you do this in RS?
many thanks
MattTo elaborate further, this would be partially resolved if we could put a
total in the page footer. Is there a way we can sum a field and use the
result of this in our page footer?
Finally, how would we make our column lines go from the page header to the
page footer?
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uHm0yg8kEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am trying to create invoices, quotes, order documents using RS.
> Typically for these documents, the totals go at the footer of the report.
> We also have lines indicating columns so it's easy to look down the totals
> column. How might you do this in RS?
> many thanks
> Matt
>
>

First Time Delay on client systems

Sorry to repost this, but need to in order to hopefully get a response from
Microsoft:
I've just set up a SQL Reporting Services server.
All seems to be working fine, however I've noticed that when a report is
opened from a user system accessing the report server for the first time, the
report takes a very long time to open (a few minutes in some cases), and
sometimes I have to abort it and start again.
This is a report that should take a couple of seconds at most. Once the
report does finally open, it opens fine from then on, even when refreshed, or
run by different parameters. Also all other reports availale to the user also
run fine after that first one finally opens.
There are no errors, just the Report is being generated message. It's like
the server has to get aquainted with the machine accessing it the first
time...
Again, once the report has run, this problem goes away. I have tested this
on around 10 different user systems, with the same results on all of them,
some are Win2k, some are XP. The report server is running on a dual
processor, clean install Server 2003, SQL Server 2000 sp 3a.
So far it appears to be machine, rather than user account, related...
Any assistance would be greatly appreciated. I'm holding off a full on roll
out of this until I get this solved, as it will certainly drive users crazy.
Thanks,
TomTHi Tomt,
Thanks for your posting!
From your descriptions, I understood that you would like to know why there
will be a delay (a few minutes in some cases) when you first time access
Reports. Have I understood you? Correct me if I was wrong.
Based on my konwledge, this is Reporting Services by design behavior. The
first user who runs the report with a unique region code creates a cached
report that contains data for that region. Subsequent users who request a
report using the same region code get the cached copy. create that report
will cost a lot of time.
The report server caches reports based on report execution options.
Execution options determine whether a report is cached and the length of
time it stays in cache. After some number of minutes or at a scheduled
time, the cache is emptied. The cache stays empty until a new report
execution operation occurs and a new copy of the report is cached.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
I'm not sure what you mean by region code...
What I'm seeing is for example: I open a report on my system, which has
previously accessed the report server and experience no delay. I go to
another system, which has never accessed that server, and run the same
report, and get a delay of up to a few minutes.
Hope that is clear...
Thanks for your help,
Tom
"Michael Cheng [MSFT]" wrote:
> Hi Tomt,
> Thanks for your posting!
> From your descriptions, I understood that you would like to know why there
> will be a delay (a few minutes in some cases) when you first time access
> Reports. Have I understood you? Correct me if I was wrong.
> Based on my konwledge, this is Reporting Services by design behavior. The
> first user who runs the report with a unique region code creates a cached
> report that contains data for that region. Subsequent users who request a
> report using the same region code get the cached copy. create that report
> will cost a lot of time.
> The report server caches reports based on report execution options.
> Execution options determine whether a report is cached and the length of
> time it stays in cache. After some number of minutes or at a scheduled
> time, the cache is emptied. The cache stays empty until a new report
> execution operation occurs and a new copy of the report is cached.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I'm sure he meant report query parameters and region code was the specific
one in his head for an example.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:FC1995F8-D81D-4049-87EC-4B88A4DDB8BB@.microsoft.com...
> Michael,
> I'm not sure what you mean by region code...
> What I'm seeing is for example: I open a report on my system, which has
> previously accessed the report server and experience no delay. I go to
> another system, which has never accessed that server, and run the same
> report, and get a delay of up to a few minutes.
> Hope that is clear...
> Thanks for your help,
> Tom
> "Michael Cheng [MSFT]" wrote:
>> Hi Tomt,
>> Thanks for your posting!
>> From your descriptions, I understood that you would like to know why
>> there
>> will be a delay (a few minutes in some cases) when you first time access
>> Reports. Have I understood you? Correct me if I was wrong.
>> Based on my konwledge, this is Reporting Services by design behavior. The
>> first user who runs the report with a unique region code creates a cached
>> report that contains data for that region. Subsequent users who request a
>> report using the same region code get the cached copy. create that report
>> will cost a lot of time.
>> The report server caches reports based on report execution options.
>> Execution options determine whether a report is cached and the length of
>> time it stays in cache. After some number of minutes or at a scheduled
>> time, the cache is emptied. The cache stays empty until a new report
>> execution operation occurs and a new copy of the report is cached.
>> Thank you for your patience and corporation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Ok, thanks. If that is the case, that is not the situation I'm talking about,
it seems to be related to whether or not a system has ever accessed the
server at all, there are no delays on systems that have, regardless of
parameters.
Thanks
"Jeff A. Stucker" wrote:
> I'm sure he meant report query parameters and region code was the specific
> one in his head for an example.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:FC1995F8-D81D-4049-87EC-4B88A4DDB8BB@.microsoft.com...
> > Michael,
> >
> > I'm not sure what you mean by region code...
> >
> > What I'm seeing is for example: I open a report on my system, which has
> > previously accessed the report server and experience no delay. I go to
> > another system, which has never accessed that server, and run the same
> > report, and get a delay of up to a few minutes.
> >
> > Hope that is clear...
> >
> > Thanks for your help,
> >
> > Tom
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> >> Hi Tomt,
> >>
> >> Thanks for your posting!
> >>
> >> From your descriptions, I understood that you would like to know why
> >> there
> >> will be a delay (a few minutes in some cases) when you first time access
> >> Reports. Have I understood you? Correct me if I was wrong.
> >>
> >> Based on my konwledge, this is Reporting Services by design behavior. The
> >> first user who runs the report with a unique region code creates a cached
> >> report that contains data for that region. Subsequent users who request a
> >> report using the same region code get the cached copy. create that report
> >> will cost a lot of time.
> >>
> >> The report server caches reports based on report execution options.
> >> Execution options determine whether a report is cached and the length of
> >> time it stays in cache. After some number of minutes or at a scheduled
> >> time, the cache is emptied. The cache stays empty until a new report
> >> execution operation occurs and a new copy of the report is cached.
> >>
> >> Thank you for your patience and corporation. If you have any questions or
> >> concerns, don't hesitate to let me know. We are always here to be of
> >> assistance!
> >>
> >>
> >> Sincerely yours,
> >>
> >> Michael Cheng
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >> =====================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
>
>|||Look for an earlier thread on this topic. Bruce and others have invented a
keep-alive type solution that periodically runs a trivial report on schedule
to keep the process from unloading.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:FACEA9F4-41FE-4C02-AD27-8FB118FB73AB@.microsoft.com...
> Ok, thanks. If that is the case, that is not the situation I'm talking
> about,
> it seems to be related to whether or not a system has ever accessed the
> server at all, there are no delays on systems that have, regardless of
> parameters.
> Thanks
> "Jeff A. Stucker" wrote:
>> I'm sure he meant report query parameters and region code was the
>> specific
>> one in his head for an example.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "TomT" <tomt@.newsgroup.nospam> wrote in message
>> news:FC1995F8-D81D-4049-87EC-4B88A4DDB8BB@.microsoft.com...
>> > Michael,
>> >
>> > I'm not sure what you mean by region code...
>> >
>> > What I'm seeing is for example: I open a report on my system, which has
>> > previously accessed the report server and experience no delay. I go to
>> > another system, which has never accessed that server, and run the same
>> > report, and get a delay of up to a few minutes.
>> >
>> > Hope that is clear...
>> >
>> > Thanks for your help,
>> >
>> > Tom
>> >
>> > "Michael Cheng [MSFT]" wrote:
>> >
>> >> Hi Tomt,
>> >>
>> >> Thanks for your posting!
>> >>
>> >> From your descriptions, I understood that you would like to know why
>> >> there
>> >> will be a delay (a few minutes in some cases) when you first time
>> >> access
>> >> Reports. Have I understood you? Correct me if I was wrong.
>> >>
>> >> Based on my konwledge, this is Reporting Services by design behavior.
>> >> The
>> >> first user who runs the report with a unique region code creates a
>> >> cached
>> >> report that contains data for that region. Subsequent users who
>> >> request a
>> >> report using the same region code get the cached copy. create that
>> >> report
>> >> will cost a lot of time.
>> >>
>> >> The report server caches reports based on report execution options.
>> >> Execution options determine whether a report is cached and the length
>> >> of
>> >> time it stays in cache. After some number of minutes or at a scheduled
>> >> time, the cache is emptied. The cache stays empty until a new report
>> >> execution operation occurs and a new copy of the report is cached.
>> >>
>> >> Thank you for your patience and corporation. If you have any questions
>> >> or
>> >> concerns, don't hesitate to let me know. We are always here to be of
>> >> assistance!
>> >>
>> >>
>> >> Sincerely yours,
>> >>
>> >> Michael Cheng
>> >> Microsoft Online Partner Support
>> >>
>> >> When responding to posts, please "Reply to Group" via your newsreader
>> >> so
>> >> that others may learn and benefit from your issue.
>> >> =====================================================>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>>|||Thanks Jeff, that was my thread, I believe, which I started over because of
profile issues - and to get MS involved.
Unfortunately, that solution is not applicable to the problem I am
describing, apparently not very well...:-)
Here's a clearer scenario (I hope): I run report A on my system, it opens
immediately (my system has run reports previously, not necessarily report A,
however).
I goimmediately to another system , which has never run any reports at all,
and run report A. In many (although not all) cases, minutes will pass before
the report processing is competed. Since the time between running the report
on one system and the other is miniscule, I don't think the process is
unloading - there appears to be something else going on...
"Jeff A. Stucker" wrote:
> Look for an earlier thread on this topic. Bruce and others have invented a
> keep-alive type solution that periodically runs a trivial report on schedule
> to keep the process from unloading.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:FACEA9F4-41FE-4C02-AD27-8FB118FB73AB@.microsoft.com...
> > Ok, thanks. If that is the case, that is not the situation I'm talking
> > about,
> > it seems to be related to whether or not a system has ever accessed the
> > server at all, there are no delays on systems that have, regardless of
> > parameters.
> >
> > Thanks
> >
> > "Jeff A. Stucker" wrote:
> >
> >> I'm sure he meant report query parameters and region code was the
> >> specific
> >> one in his head for an example.
> >>
> >> --
> >> Cheers,
> >>
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "TomT" <tomt@.newsgroup.nospam> wrote in message
> >> news:FC1995F8-D81D-4049-87EC-4B88A4DDB8BB@.microsoft.com...
> >> > Michael,
> >> >
> >> > I'm not sure what you mean by region code...
> >> >
> >> > What I'm seeing is for example: I open a report on my system, which has
> >> > previously accessed the report server and experience no delay. I go to
> >> > another system, which has never accessed that server, and run the same
> >> > report, and get a delay of up to a few minutes.
> >> >
> >> > Hope that is clear...
> >> >
> >> > Thanks for your help,
> >> >
> >> > Tom
> >> >
> >> > "Michael Cheng [MSFT]" wrote:
> >> >
> >> >> Hi Tomt,
> >> >>
> >> >> Thanks for your posting!
> >> >>
> >> >> From your descriptions, I understood that you would like to know why
> >> >> there
> >> >> will be a delay (a few minutes in some cases) when you first time
> >> >> access
> >> >> Reports. Have I understood you? Correct me if I was wrong.
> >> >>
> >> >> Based on my konwledge, this is Reporting Services by design behavior.
> >> >> The
> >> >> first user who runs the report with a unique region code creates a
> >> >> cached
> >> >> report that contains data for that region. Subsequent users who
> >> >> request a
> >> >> report using the same region code get the cached copy. create that
> >> >> report
> >> >> will cost a lot of time.
> >> >>
> >> >> The report server caches reports based on report execution options.
> >> >> Execution options determine whether a report is cached and the length
> >> >> of
> >> >> time it stays in cache. After some number of minutes or at a scheduled
> >> >> time, the cache is emptied. The cache stays empty until a new report
> >> >> execution operation occurs and a new copy of the report is cached.
> >> >>
> >> >> Thank you for your patience and corporation. If you have any questions
> >> >> or
> >> >> concerns, don't hesitate to let me know. We are always here to be of
> >> >> assistance!
> >> >>
> >> >>
> >> >> Sincerely yours,
> >> >>
> >> >> Michael Cheng
> >> >> Microsoft Online Partner Support
> >> >>
> >> >> When responding to posts, please "Reply to Group" via your newsreader
> >> >> so
> >> >> that others may learn and benefit from your issue.
> >> >> =====================================================> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Hi Tom,
What kind of credentials are used against the datasource?
Each IE and IIS do some hand shaking on the first request. It is also
possible that domain authentication or the first connection to the data
source lead to this kind of delay.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
Thanks for your response. It does appear to be related to the user. I logged
onto a system which had never accessed the report server, and got the report
right away. I then had another person log on to the same machine, and he got
a delay.
I am using a shared data source to access the actual sql server data, so
that is common to everyone, in other words the authentication for the reports
to the sql server data is not specific to individual users, their credentials
are not used.
I wonder if the IIS server (which is the same machine as the Report Server)
is a factor in the delay? People can get to the server without delays, it
just happens when they run their first report...
"Michael Cheng [MSFT]" wrote:
> Hi Tom,
> What kind of credentials are used against the datasource?
> Each IE and IIS do some hand shaking on the first request. It is also
> possible that domain authentication or the first connection to the data
> source lead to this kind of delay.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

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?