Float data

Hello.

We have a third party app that's database holds allsorts of info as floats. I have to update records in this database from my source database that stores it's data as decimal(8,2).

so when I update a row with 25.00 it goes into the 3rd party app database as 25 because it's a float. Is it possible to put the data in so it reads 25.00 without changing the datatype from a float?

Thanks.

You should not worry about the format of numbers as they come from a query. Use your application to apply any formatting that is needed.|||The "float" data type does not store non-significant digits. The trailing 0 is non-significant. So the answer is no.

You would have to reformat it to decimal(8,2) when you get it back out of the float.|||

Thanks for the replies, I thought as much after reading BOL but you see my source database is an Ingres database (remember them!) and it's float data type does store the non-significant digits so I was curious to see if SQL could do the same.

Thanks again

Flat file to table

Hi,

I have a set of flat files and transforming it to SQL server. If I do that in 2000 it was done with in 45 seconds for 1.5 M records. If I do the same in SSIS it takes 3 minutes. Why there is difference in time that too lower when compared to the previous version. I used the data access mode as "Fast load". Am I missing anything while doing through SSIS?

There's so many "it depends" answers to this its not really worth posting a possible reason.

What exactly is the data flow doing? Where is the bottleneck?

-Jamie

|||

Its a very straight transformation. CSV file to a table and all the fields are set as Varchar,

- No validations made on the transformation

- No Calculations.

- No aggregations

again its a very straight transformation.

|||one thing i forget to mention. In 2000 I am using the global variable for looping the source files. In SSIS i used "For each loop" container.|||

And where is the bottleneck? Is it in sourcing the data or loading it to the target?

Check this out for tips on diagnosing bottlenecks:

http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx

-Jamie

|||

Jamie,

Thanks for sending the link, I will go through it in the evening as I am now in office. In the mean time I fixed and the performance is increased from 3 minutes to just 21 seconds (2000 took 45 seconds for the same transformation). The change I made is previously it was Native OLE DB but I changed it to MS OLE DB. If you find time could you please send any link or explain how this has created the dramatic change in performance.

Thanks for your time.

|||

I'm not sure what you mean by "native OLE DB". Can you send a link to the OLE DB driver that you were using?

-Jamie

|||

Jamie,

The link you provided was awesome. Thanks to Donald farmer for wonderful explanation and for you to identifing it to me on the right time.

Initially i had the provider as "Native OLE DB\SQL Native client" in the connection manager when it gives outpu on 3 minutes. When I changed this to "Native OLE DB \ Microsft OLE DB Provider for SQL server" it was processint the same task in less than 30 minutes. Is this due to the driver? how do i choose the best dirver?

|||

Dhanasu wrote:

...it was processint the same task in less than 30 minutes...

Based on your above comment, I'm assuming you mean "30 seconds" not 30 minutes.

|||Yes you're correct. it is 30 seconds.|||

That is an interesting observation. I would expect the opposite results, as SQL Native Client is the more recent provider.

It is almost certain that the difference lies in the used provider. I would try to ask why that is on the Data Access forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thanks.

sql

flat file to excel

hi,

i have a flat file with over 66,000 records. When I transfer it to excel (2003), it does not fit in due to limitation of rows. What can I do so that the remaining rows which will not fit in will go to another sheet. thanks a lot!

cherriesh

You can add a Conditional Split Transformation to route the data to multiple Excel destinations based on ranges of values in a specific column (i.e. ID). For more information about using the Conditional Split Transformation, see "Conditional Split Transformation" at http://msdn2.microsoft.com/en-us/library/ms137886.aspx.

|||

Take a look at this post for an example of how to create and output rows to multiple sheets:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

Flat File Source with Ragged Right Problems

For some reason, when I try to use the Flat File Source and set the record type to Ragged Right it does not seem to recognize 'short' records. It seems to be confused by the CRLF set delimiters and not recognize these in 'some' records. The input does not seem consistent. What am I missing?

SSIS parses column by column, not rows then columns. There are a fair number of posts on this. Basically the work around today is to bring in each row as a single string column, then parse it into indiviudal columns in the package.

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

Flat File Records Dropped During Import

Hello,
I am attempting to import a fixed width flat file into a SQL Server table. When I import the file, 704 records don't make it into the table. I know this because if I do the import with MS Access 2003 into an Access table, all of the records from the flat file make it into the table. The flat files have a .txt extension.

The only possible problem that I can see is that some of the rows in the flat file do not contain the full set of characters. When I do the import into SQL Server and create a table on the fly, I still end up 704 records short. There are no error messages during or after the import.

I suppose I could isolate some of the missing records, put them into a different file and try to import them to see what would happen. Other than that, how do I begin to troubleshoot this problem? Are there known issues where records can be dropped from a fixed width file?

Thank you for your help!

cdun2I may have found the problem. The first record does not contain a full set of characters, and when I set up the fixed field column positions originally, I was not able to define the columns for the full string. Only the first one third of the row characters need to be imported, so I ignored this issue.

I have moved a single full length record to the top of the flat file, and the text file properties box now can 'see' the full string. I'll include the rest of the columns (which are not imported) and see if that works.|||No, that didn't work. I moved a record containing all characters to the top of the file, redefined the columns based on the full string, changed the column mappings, and reconfigured the transformations. When I did the import, the destination SQL Server table received even fewer records.

What can I do about this problem?

Thanks again.

cdun2|||If this is a one time only situation, why don't you just import the flat file into Access 2003 and then import the Access table to SQL server?|||What are you using to import the data? DTS? BCP?sql

flat file destination -data all on one line

I have a flat file destination that Im sending data to, from an OLE DB data source. There are two records, but for some reason they are both going on the same line in the output. This is after setting the output to fixed width, from comma delimited.

Help ?

If you are viewing the flat file from a text editor, recheck the option on your editor to verify word wrap is not occurring. The file may be perfectly fine, it's the representation in the editor.|||

If you are using the pure FixedWidth format the row delimiter is not going to be inserted. It assumes all your columns are fixed width and it does not put any delimiters.

If you want to break your rows in new lines, choose the Fixed Width with Row Delimiters option at the time you create the flat file connection from the Flat File Destination. This will configure the connection to use the RaggedRight format and add a dummy column with the new line delimiter at the end. You should be able to do this manually as well, if you are changing an existing connection.

HTH.

Flagging duplicate records help!

Hi all, hoping you can help me.
Here's some sample data:
DATE PHONE DUPE
1/27/2006 8888888888 0
2/22/2006 8888888888 0
2/25/2006 8888888888 0
3/30/2006 8888888888 0
2/10/2006 7777777777 0
2/10/2006 7777777777 0
3/18/2006 7777777777 0
etc...
What i'd like to do is make DUPE = 1 if less than 30 days has passed since
he last called, so in the above sample it should look like this after:
DATE PHONE DUPE
1/27/2006 8888888888 0
2/22/2006 8888888888 1
2/25/2006 8888888888 1
3/30/2006 8888888888 0
2/10/2006 7777777777 0
2/10/2006 7777777777 1
3/18/2006 7777777777 0
etc...
There are hundreds of thousands of records in the table and i'm currently
using a proc that uses cursors and it's taking way too long and is very
resource heavy.
Thanks in advance to anyone that can helpDo you plan on updating DUPE for all the records with the same phone
number or only the only the most recent entry...with DUPE. Because if
the person called 3 years ago, and then yesterday and then today, will
all 3 have DUPE updated, because of todays call.In this case you are
updating records which could be so far down in the table and this may
be unneccesary'
Have you considered making a view which shows all distinct phone
numbers which were called in the last 30 days and the last time it was
called ...MAX(calldate) ...then using a JOIN to get occurences after
the date in the view.and update the record based on that.|||Are you saving time part also?
create table t1 (
[date] datetime,
phone varchar(10),
dupe tinyint
)
go
insert into t1 values('2006-01-27 00:00:00.000', '8888888888', 0)
insert into t1 values('2006-02-22 00:00:00.000', '8888888888', 0)
insert into t1 values('2006-02-25 00:00:00.000', '8888888888', 0)
insert into t1 values('2006-03-30 00:00:00.000', '8888888888', 0)
insert into t1 values('2006-02-10 00:00:00.000', '7777777777', 0)
insert into t1 values('2006-02-10 01:00:00.000', '7777777777', 0) <--
trick to diff calls
insert into t1 values('2006-03-18 00:00:00.000', '7777777777', 0)
go
update t1
set dupe = 1
where [date] < dateadd(day, 30, (select min(t2.[date]) from t1 as t2 where
t2.phone = t1.phone and t2.[date] < t1.[date]))
go
select
*
from
t1
order by
phone, [date]
go
AMB
"ttrottier" wrote:

> Hi all, hoping you can help me.
> Here's some sample data:
> DATE PHONE DUPE
> 1/27/2006 8888888888 0
> 2/22/2006 8888888888 0
> 2/25/2006 8888888888 0
> 3/30/2006 8888888888 0
> 2/10/2006 7777777777 0
> 2/10/2006 7777777777 0
> 3/18/2006 7777777777 0
> etc...
> What i'd like to do is make DUPE = 1 if less than 30 days has passed since
> he last called, so in the above sample it should look like this after:
> DATE PHONE DUPE
> 1/27/2006 8888888888 0
> 2/22/2006 8888888888 1
> 2/25/2006 8888888888 1
> 3/30/2006 8888888888 0
> 2/10/2006 7777777777 0
> 2/10/2006 7777777777 1
> 3/18/2006 7777777777 0
> etc...
>
> There are hundreds of thousands of records in the table and i'm currently
> using a proc that uses cursors and it's taking way too long and is very
> resource heavy.
> Thanks in advance to anyone that can help|||UPDATE Whatever
SET DUPE = 1
WHERE EXISTS
(select * from Whatever as D
where Whatever.PHONE = D.PHONE
and Whatever.Date < D.Date)
This would be helped a lot by an index on (PHONE, DATE).
Roy Harvey
Beacon Falls, CT
On Wed, 29 Mar 2006 08:30:02 -0800, ttrottier
<ttrottier@.discussions.microsoft.com> wrote:

>Hi all, hoping you can help me.
>Here's some sample data:
>DATE PHONE DUPE
>1/27/2006 8888888888 0
>2/22/2006 8888888888 0
>2/25/2006 8888888888 0
>3/30/2006 8888888888 0
>2/10/2006 7777777777 0
>2/10/2006 7777777777 0
>3/18/2006 7777777777 0
>etc...
>What i'd like to do is make DUPE = 1 if less than 30 days has passed since
>he last called, so in the above sample it should look like this after:
>DATE PHONE DUPE
>1/27/2006 8888888888 0
>2/22/2006 8888888888 1
>2/25/2006 8888888888 1
>3/30/2006 8888888888 0
>2/10/2006 7777777777 0
>2/10/2006 7777777777 1
>3/18/2006 7777777777 0
>etc...
>
>There are hundreds of thousands of records in the table and i'm currently
>using a proc that uses cursors and it's taking way too long and is very
>resource heavy.
>Thanks in advance to anyone that can help|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Is this what you meant?
CREATE TABLE PhoneLog
(call_date DATETIME NOT NULL,
phone_nbr CHAR(10) NOT NULL,
dup_flag INTEGER DEFAULT 0 NOT NULL
CHECK (dupe_flag IN (0,1)),
PRIMARY KEY (call_date, phone_nbr));
Try another approach -- relational, not computatinal! Adjust the
following table for 30 business days rather than calendar days if you
need to.
CREATE TABLE ReportRanges30days
(start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (start_date, end_date));
Ten years will fit into main storage so now use a join whch can get the
covering index:
UPDATE PhoneLog
SET dup_flag
= CASE WHEN
EXISTS (SELECT *
FROM ReportRanges30days
WHERE call_date
BETWEEN start_date AND end_date)
THEN 1 ELSE 0 END
You might also put this into a VIEW and avoid updating all the time.|||Hi and thanks for the reply.. but this isn't quite what i need,
if you run the following code, you'll see that the last record
is not flagged as a duplicate, but it should be since it was
within 30 days of the previous record.. any ideas?
Thanks very much.
drop table t1
create table t1 (
[date] datetime,
phone varchar(10),
dupe tinyint
)
go
insert into t1 values('2005-08-30 13:39:00', '8888888888', 0)
insert into t1 values('2005-08-30 13:41:00', '8888888888', 0)
insert into t1 values('2005-12-21 10:03:00', '8888888888', 0)
insert into t1 values('2005-12-21 10:05:00', '8888888888', 0)
go
update t1
set dupe = 1
where [date] < dateadd(day, 30, (select min(t2.[date]) from t1
as t2 where
t2.phone = t1.phone and t2.[date] < t1.[date]))
go
select * from t1 order by phone, [date] go
---
Posted with NewsLeecher v2.0 Beta 5
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
---|||On Fri, 31 Mar 2006 15:10:35 GMT, ttrottier wrote:

>Hi and thanks for the reply.. but this isn't quite what i need,
>if you run the following code, you'll see that the last record
>is not flagged as a duplicate, but it should be since it was
>within 30 days of the previous record.. any ideas?
Hi ttrottier,
UPDATE t1
SET dupe = 1
WHERE EXISTS
(SELECT *
FROM t1 AS d
WHERE t1.Date > d.Date
AND t1.Date <= DATEADD(day, 30, d.Date))
Hugo Kornelis, SQL Server MVP

Flagging duplicate records (again)

Hi to all and thanks for the replys, but i'm still having a problem. What i
need to do is set dupe = 1 for all records where the phone call (by date) is
less than 30 days from the previous one (having the same phone number). For
example:
call_date phone_no dupe
2005-08-30 13:39:00 888-888-8888 0
2005-08-30 13:41:00 888-888-8888 1
2005-12-12 15:23:00 888-888-8888 0
2005-12-15 07:54:00 888-888-8888 1
Thanks to all in advance. It seems easy but i can't seem to figure it outThe first time you posted this question you haven't provided DDL and sample
data, and you weren't happy with the answers. Are you going for a second tim
e?
Maybe you should read this (if you haven't already):
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/

FK. Can I do this?

Hello,

I have 3 tables:
[A] > Aid (PK)
[B] > BId (PK)
[C] > CId (PK), TargetId (FK)

TargetId should be related to both Aid and Bid.
Records created in C can be related to records in A or in B and TargetId can be either a Aid or Bid.

Can and/or should I do this?

Thanks,
Miguel

Hey,

You can relate TargetID to both A and B, but that means that that value has to reside in both tables, or you will get a constraint exception. That means that TargetID cannot be "either a Aid or Bid", but has to be both, if you set it up as a FK.

If that is the case, then sure, it's better to add constraints that are valid than to not have them for integrity sake.

|||

Although you cannot have an FK that say either A or B, you could build a trigger that checks the existance of id in either A or B prior to inserting the value in the referring table.

Or you could perhaps build a CHECK constraint. Dunno if it is possible to do a SELECT (from A and B) in a CHECK constraint though.

|||

Hi,

This seems really strange. I will try to explain it by using the real project I am working on:

I have 3 tables: Posts, Events and Files.
Each post, event and file can be a associated to one or many tags.

My idea was to create only one Tags table.
Note that each tag can have various associations.
It can be associate to various posts, events and files simultaneous.

My idea was to create a Tags table as follows:
[Tags] > TagId (PK), PostId (FK), EventId (FK), FileId (FK).

- Will I have problems with my Transact SQL queries?
- Will I have problems with .NET 3.5 LINQ?

The other 2 options I see are:

1. Having only one FK in table Tags, i.e. TargetId, which could be
associated with PostId, EventId or FileId ...
This does seem right to me. I feel I will have problems later on.

2. Have 3 Tags tables: for posts, for Events and for Files.
I would like to avoid having 3 tables but ...

I need to extend my decision to categories, ratings, etc.
So having 3 Tags tables, 3 Categories tables, 3 Ratings tables does not seem a good idea.

Could, someone, please advice me on this?

Thanks,
Miguel

|||

Hey,

I would have one tags table, but not have any foreign key. Because the tag could be in any one of those three tables, but not all of them, I wouldn't do that personally. That requires some extra care when you are dealing with the data, in ensuring that if you remove anything, you ensure that the tag isn't being used anywhere else.

|||

I am going for this:

Posts (PostId PK)
Files (FileId PK)

PostsTags (PostId PK, TagId PK)
FilesTags (FileId PK, TagId PK)

Tags (TagId PK, TagName)

I think it is the best option. I hope. :-)

Thanks,

Miguel

|||

Yeah, actually that would be better That does make some more sense than not having a FK.

Fixed v Changing v Historical attribute conflicts

We have an issue in a SCD where a number of records may be presented that have changes to their attributes of EVERY type.

Example,

BusinessKey: xxxxxxxx

BuildingTypeId: 7

BusinessUnitHistoryId: 4019

BusinessUnitId: 4019

CurrencyId: 26

DevelopmentTypeId: 14

MarketId: 182

Name: abcdefgh

CurrencyId is a fixed attribute

MarketId & BuildingTypeId and the BusinessUnitId & BusinessUnitHistoryIds are historical attributes

Name is a changing attribute

The behaviour of the ETL seems to suggest that if fixed attribute changes are detected, these rows will error and therefore the changing & historical attributes will NOT be amended during the SCD transformation. Is this correct... as it seems to be what is happening.

Yep, that's an error. A fixed attribute cannot change. How do you propose the SCD handles that scenario? To me, it's bad data and should be redirected.|||

Thanks for the confirmation Phil.

I figure we are going to amend CurrencyId from Fixed to Changing as it seems they just overwrite at source and do not keep the history.

So, fixed attributes aside, what happens if the same row contains both a changing attribute amendment AND a historical attribute amendment - do both get successfully changed or does one type take precedence over the other?

Let's say (in the above example) both the Name (changing) and MarketId (historical) had changed.... do both get done or just one... if it's just one, do you have a suggestion as to how we reflect both changes?

Thanks for your help

Will

|||The historical attribute change will always occur, and I think there's a setting on whether or not to change all historical instances with changing attributes.|||

Yes,

Ah i think i get it...

I'm aware that if a changing attribute is detected, you can elect to change all instances of that record.

What I was unclear about is whether during a single SCD pass, if a record contains a field which is a changing attribute AND a field which is a historical attribute, do BOTH get done ?

From what you have said, the historical attribute will get done AND the record will also pass down the changing attribute route if such a change is detected. ?

If that's that case then hats off the MSFT... I just have a sneaky feeling that only one will get done.... maybe I just need to set up some samle data and test this.

|||It works correctly in my test. The new row is inserted as a result of a historical attribute change with the changed attribute being written to it at the same time. If you tell it to change all attributes, they get changed as well.

Think about it -- it's just input into the historical row -- so when it writes the new row it just reads in the value of whatever's in that column. As for updating all of the "expired" rows, that's similar behavior to a multicast and is executed independent of the historical row change.

If that isn't clear, just know that it will work!|||

No... it's clear enough Phil and I never had cause to question it before..... You know when you start questioning stuff like this you've obviously been working on a problem for too long!!

Thanks alot

Will

Fixed number of Detail Lines

I have to create a report that will always show 12 detail lines per group, no matter how many records are in the group. For example, if there are only 8 records in the group I need to print 4 blank detail lines. I have it printing and breaking on the group just fine. How Do I get it to force the needed blank lines?
There will never be more than 12 per group.
I already have the report doing the new page on new group.
At this point the report works other than filling in missing detail with blank lines.
The report is a class roster which shows the students that have signed up for the class in a grid type layout. I just need the extra blank detail lines as a nice formating step. Some classes only have 4-5 students some have a full enrollment of 12Place a formula in the group footer to print 'n' blank lines.
e.g.

whileprintingrecords;
local numbervar lines := 12 - count({table.field}, {table.group});
local stringvar text;

while lines > 0 do
(
lines := lines - 1;
text := text & chr(13); //you may need chr(10) too / instead.
);

text

Tick the 'Can Grow' property of the object (right click, format field, common tab) and use the section expert to make the footer 'Suppress Blank Section'.

Fixed length records

I have to build a text file that has 80 character records. I have a number
of fixed length fields in the file but need to add a 'filler' at the end of
the fields to get to 80. Do I have to create a dummy variable to do that and
if so, how?
Thanks
Stan Gosselin
On Mon, 31 Oct 2005 12:36:06 -0800, Stan wrote:

>I have to build a text file that has 80 character records. I have a number
>of fixed length fields in the file but need to add a 'filler' at the end of
>the fields to get to 80. Do I have to create a dummy variable to do that and
>if so, how?
>Thanks
Hi Stan,
You can't use regular SQL queries to create a text file. You'll have to
use an external utility for that. The ones most commonly used are bcp or
DTS. Both are described in Books Online.
For bcp, the way to add extra space to pad the record length to 80
characters is to use a format file. For DTS, you'll have to look into
the transformation possibilities.
If you need further help, I advise you to post to another newsgroup.
This group is intended for support of English Query, and it's only used
by few people. The group microsoft.public.sqlserver.tools is intended to
support the tools that come with SQL Server (such as bcp and DTS).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo and thanks for educating me about site usage. We are a shop that
is in the process of converting from a COBOL based legacy system to a SQL
Server environment and we are all new at this.
Stan Gosselin
"Hugo Kornelis" wrote:

> On Mon, 31 Oct 2005 12:36:06 -0800, Stan wrote:
>
> Hi Stan,
> You can't use regular SQL queries to create a text file. You'll have to
> use an external utility for that. The ones most commonly used are bcp or
> DTS. Both are described in Books Online.
> For bcp, the way to add extra space to pad the record length to 80
> characters is to use a format file. For DTS, you'll have to look into
> the transformation possibilities.
> If you need further help, I advise you to post to another newsgroup.
> This group is intended for support of English Query, and it's only used
> by few people. The group microsoft.public.sqlserver.tools is intended to
> support the tools that come with SQL Server (such as bcp and DTS).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Wed, 2 Nov 2005 13:17:23 -0800, Stan wrote:

>Thanks Hugo and thanks for educating me about site usage. We are a shop that
>is in the process of converting from a COBOL based legacy system to a SQL
>Server environment and we are all new at this.
Hi Stan,
Good luck, than. Keep in mind that converting the data will be the
easiest part of the job. Changing your mindset will be the hardest.
COBOL is a third generation, algorithmic language. The basic structure
of Cobol data processing is "read record - check if data qualifies for
operation - do operation - write record - read next record - repeat
until end of file".
SQL is a fourth-generation, declarative language. The basic structure of
data processing in SQL is "do something on all qualifying rows at once".
Quite a difference!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Fit dataset on one page

Hey guys,
Let's assume I have dataset with two columns (A,B) and it has 100 records.
I'd like to split this dataset on the same page with 25 records in every column. Side by side.

Example:

ColA ColB ColA ColB ColA ColB ColA ColB 25 rec 25 rec 25 rec 25 rec

What should I use and what properties I have to play with?
Thanks.anyone? :))|||Just a quick drop answer:

- I asume regular column format does not work for you because you want balanced columns and RS gives you unbalanced ones.
- I asume you have a static format= always 100 records, always 25 records/column, or alike.

In those circumstances:
- I'll put 4 table regions side by side on the report design area
- Create a sort of paging expression. Based on Chris Hays blog entry (http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx)
- But instead of grouping items, I'll use the expression for filtering contents on each table. So, table data region 1 will filter for those records with "group" value of 1, table 2 for group 2, and so on...

Hope this helps,

Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning|||A related query to you...

how do i control how many records are displayed on the page?. currently I do not see any property and RS just gives me some defaullt number of records on a page.

The problem is, in one case I get 3 records on a page and 2900 pages which I do not want.

Thanks.|||I'm guessing again, because I've not tried this in code, but I think you can follow the same dynamic grouping solution that Chris Hays suggest in their bloeg (see previous link) to have another grouping every 100 records and then adding a page break based on that grouping.

Let me sumarize the solution:

- One list region with grouping each 100 records (expression1) and page break after each group
- Inside the list: 4 table regions, each one holding 25 records filtered according to the block expression2 (now you may want take "page number" in account).

Hope this helps,

Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning|||Thanks Jordi,
However your assumption wasn't right enough. :)) Sorry.
In my situation recordset can be between 0 and 100 records.
I will try to manipulate with "region" tables.|||Hey,

I was still not able to control how many records are shown on a single page? Any ideas if there is a property I can configure to control this?

Thanks.|||Hi cvajre (whatever this means ;-)

No, there is no property to control how many reports are shown on a single page.
Keep in mind that RS is designed to support and handle rich free-form reports in an open number of presentation formats. That way, even the "page" concept is a tricky one.

You can set page breaks on groups (only?), therefore the trick mentioned above for creating groups that fit your expected recordnumber size.

Hope this helps,

Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning

Fiscal year search

Hi

I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this

Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00

I have tried

Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @.year and 6/30/ + (@.year +1)

Plus a variety of other fruitless concoctions...but nothing seems to
work. Any help would be appreciated.Discovered answer on my own. Thanks anyway.

Twobridge wrote:

Quote:

Originally Posted by

Hi
>
I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this
>
Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00
>
I have tried
>
Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @.year and 6/30/ + (@.year +1)
>
Plus a variety of other fruitless concoctions...but nothing seems to
work. Any help would be appreciated.

|||I've got a simlar problem - can you post your solution?
Dan

On Nov 29, 1:50 am, "Twobridge" <Twobri...@.gmail.comwrote:

Quote:

Originally Posted by

Discovered answer on my own. Thanks anyway.
>
>
>
Twobridge wrote:

Quote:

Originally Posted by

Hi

>

Quote:

Originally Posted by

I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this

>

Quote:

Originally Posted by

Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00

>

Quote:

Originally Posted by

I have tried

>

Quote:

Originally Posted by

Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @.year and 6/30/ + (@.year +1)

>

Quote:

Originally Posted by

Plus a variety of other fruitless concoctions...but nothing seems to
work. Any help would be appreciated.- Hide quoted text -- Show quoted text -

|||On 29 Nov 2006 01:28:49 -0800, Dan wrote:

Quote:

Originally Posted by

>I've got a simlar problem - can you post your solution?
>Dan

Hi Dan,

The best way to solve this is to have a calendar table (see
http://sqlserver2000.databases.aspf...dar-table.html),
with FiscalYear as one of it's columns.

Second best is to build a date in string format, using a format that is
guaranteed to be unabiguous WRT the order of day and month: yyyymmdd.
For isntance, for a fiscal year that starts on july first:

DECLARE @.FiscalYear int
SET @.FiscalYear = 2006
SELECT something
FROM sometable
WHERE TheDate >= CAST(@.Year AS varchar) + '0701'
AND TheDate < CAST(@.Year + 1 AS varchar) + '0701'

You might want to read this as well:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo

Thanks - i'll take a look

Dan
On Nov 30, 9:13 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

On 29 Nov 2006 01:28:49 -0800, Dan wrote:
>

Quote:

Originally Posted by

I've got a simlar problem - can you post your solution?
DanHi Dan,

>
The best way to solve this is to have a calendar table (seehttp://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using...),
with FiscalYear as one of it's columns.
>
Second best is to build a date in string format, using a format that is
guaranteed to be unabiguous WRT the order of day and month: yyyymmdd.
For isntance, for a fiscal year that starts on july first:
>
DECLARE @.FiscalYear int
SET @.FiscalYear = 2006
SELECT something
FROM sometable
WHERE TheDate >= CAST(@.Year AS varchar) + '0701'
AND TheDate < CAST(@.Year + 1 AS varchar) + '0701'
>
You might want to read this as well:http://www.karaszi.com/SQLServer/info_datetime.asp
>
--
Hugo Kornelis, SQL Server MVP

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 5 Related Records?

I am trying to determine what the first 5 related records to another record
are. I have created a table of matches where I have returned the unique key
value for each record in a one to many relationship. Preferrably, I would
like to update the table that contains the F_rel_num with the 5 (or less)
values for RE_rel_num in fields such as RE_rel_num1,
RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would not
work as I need the top 5 for each F_rel_num. Does anyone know how to do this
?
I could easily write this in ASP or VB, but I need to be able to run this as
a regular SQL job, so I imagine I need to do it completely with T-SQL.
F_relNum RE_rel_num
3 1633955
3 1353526
3 1137500
3 905264
3 732204
3 639101
3 488182
3 377705
3 365446
3 365445
3 313125
3 256899
3 254183
3 133409
6 214174
6 139273
6 117524
6 117520
7 1053160
11 1126433
11 857312
11 464240
13 555629
13 316781
13 302905
13 231447
14 644116
14 164926Try:
select
o1.F_relNum
from
Orders o1
where
o1.RE_rel_num in
(
select top 5
o2.RE_rel_num
from
Orders o2
where
o2.F_relNum = o1.F_relNum
order by
o2.RE_rel_num desc
)
order by
o1.F_relNum
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Wendy" <Wendy@.discussions.microsoft.com> wrote in message
news:D93DB121-1CDD-4B4E-82AB-37C7207A9B09@.microsoft.com...
I am trying to determine what the first 5 related records to another record
are. I have created a table of matches where I have returned the unique key
value for each record in a one to many relationship. Preferrably, I would
like to update the table that contains the F_rel_num with the 5 (or less)
values for RE_rel_num in fields such as RE_rel_num1,
RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would not
work as I need the top 5 for each F_rel_num. Does anyone know how to do
this?
I could easily write this in ASP or VB, but I need to be able to run this as
a regular SQL job, so I imagine I need to do it completely with T-SQL.
F_relNum RE_rel_num
3 1633955
3 1353526
3 1137500
3 905264
3 732204
3 639101
3 488182
3 377705
3 365446
3 365445
3 313125
3 256899
3 254183
3 133409
6 214174
6 139273
6 117524
6 117520
7 1053160
11 1126433
11 857312
11 464240
13 555629
13 316781
13 302905
13 231447
14 644116
14 164926|||That worked great! Thank You
"Tom Moreau" wrote:

> Try:
> select
> o1.F_relNum
> from
> Orders o1
> where
> o1.RE_rel_num in
> (
> select top 5
> o2.RE_rel_num
> from
> Orders o2
> where
> o2.F_relNum = o1.F_relNum
> order by
> o2.RE_rel_num desc
> )
> order by
> o1.F_relNum
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Wendy" <Wendy@.discussions.microsoft.com> wrote in message
> news:D93DB121-1CDD-4B4E-82AB-37C7207A9B09@.microsoft.com...
> I am trying to determine what the first 5 related records to another recor
d
> are. I have created a table of matches where I have returned the unique ke
y
> value for each record in a one to many relationship. Preferrably, I would
> like to update the table that contains the F_rel_num with the 5 (or less)
> values for RE_rel_num in fields such as RE_rel_num1,
> RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
> RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would n
ot
> work as I need the top 5 for each F_rel_num. Does anyone know how to do
> this?
> I could easily write this in ASP or VB, but I need to be able to run this
as
> a regular SQL job, so I imagine I need to do it completely with T-SQL.
> F_relNum RE_rel_num
> 3 1633955
> 3 1353526
> 3 1137500
> 3 905264
> 3 732204
> 3 639101
> 3 488182
> 3 377705
> 3 365446
> 3 365445
> 3 313125
> 3 256899
> 3 254183
> 3 133409
> 6 214174
> 6 139273
> 6 117524
> 6 117520
> 7 1053160
> 11 1126433
> 11 857312
> 11 464240
> 13 555629
> 13 316781
> 13 302905
> 13 231447
> 14 644116
> 14 164926
>

firehouse mode

I just got the following message after trying to update
records in a sql table:
"transaction cannot start while in firehouse mode"
any ideas what the problem is? please advise and thanksWhat does your code look like? My suggestion is to not use recordset
objects in ADO to modify data. Send UPDATE statements instead...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks|||hi Aaron -
I am entering data directly into a table, no query.
>--Original Message--
>What does your code look like? My suggestion is to not
use recordset
>objects in ADO to modify data. Send UPDATE statements
instead...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
message
>news:940101c478a1$06a5e660$a601280a@.phx.gbl...
>> I just got the following message after trying to update
>> records in a sql table:
>> "transaction cannot start while in firehouse mode"
>> any ideas what the problem is? please advise and thanks
>
>.
>|||Use an UPDATE statement in Query Analyzer, do not "enter data directly into
a table"...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:973d01c478a7$ed278c00$a301280a@.phx.gbl...
> hi Aaron -
> I am entering data directly into a table, no query.
> >--Original Message--
> >What does your code look like? My suggestion is to not
> use recordset
> >objects in ADO to modify data. Send UPDATE statements
> instead...
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> >> I just got the following message after trying to update
> >> records in a sql table:
> >> "transaction cannot start while in firehouse mode"
> >> any ideas what the problem is? please advise and thanks
> >
> >
> >.
> >|||SYMPTOMS
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.
>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>|||thanks Jim, I'll try it - Aaron's suggestion did not work.
regards!
>--Original Message--
>SYMPTOMS
>If you attempt to make changes to a row in a table
>displayed in SQL Server Enterprise Manager (SEM), unless
>you scroll down to the end of the table (the last row of
>the table), Enterprise Manager returns the following
>error:
>Cannot start transaction while in firehose mode.
>CAUSE
>When using SEM to display the rows from a table, all rows
>are returned by a "firehose cursor"; however, only the
>rows that are displayed have been processed. A "firehose
>cursor" refers to how the server sends rows to the client
>as fast as the client can process them. Rows that are not
>displayed in the Enterprise Manager are not processed
and,
>therefore, they remain in the network buffer.
>The "Cannot start transaction while in firehose mode"
>error occurs when an OLE-DB provider attempts to perform
a
>join transaction with results pending and while not in an
>updateable cursor mode.
>WORKAROUND
>Scroll all the way down to the last row of the table.
This
>forces all the rows to be processed. You can then edit
the
>row needed and execute the update.
>
>>--Original Message--
>>I just got the following message after trying to update
>>records in a sql table:
>>"transaction cannot start while in firehouse mode"
>>any ideas what the problem is? please advise and thanks
>>.
>.
>|||> Aaron's suggestion did not work.
What does "did not work" mean?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||If you are in SEM, the Jim's response is the most likely cure... Until all
of the rows which you have chosen are displayed, the cursor for displaying
those rows is still open , and the connection can not do anything else...
So scroll to the bottom, then go back up and make your changes...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks