FTS Index Size: row

显示标签为“row”的博文。显示所有博文

2012年3月27日星期二

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

2012年3月26日星期一

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.

|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

Flat File to SQL table

I am looking to evaluate a text field in the row and change it to an integer in the sql table based on the text.

What is the best data flow tranformation for me to use inbetween the flat file source and the ole db destination?

it depends on what logic you are using for your evaluation but Derived Column will probably do it. If not, the script component.

-Jamie

|||Can you help with an example If then expression?|||

With the information you have provided, no. What evaluation do you want to do?

-Jamie

|||

Something like:

If [Treatment] = "No Deposit Required" then 1 else 0

I'm not sure how to write this in an expression.

|||

[Treatment] == "No Deposit Required" ? (DT_I4)1 : (DT_I4)0

-Jamie

|||Thanks. your great..

2012年3月22日星期四

Flat file return NULL value

Hi,
I am facing problem while extracting data from flat file through query.

It return NULL value for the column EmpID for the 3rd row ('x')

i have a flat file for eg:

EmpID, EmpName, ParentEmpID
1,test1,10
2,test2,13
x,text3,15

Below mentioned two type of query I tried

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\','select * from test.txt')

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\;Extended Properties="text;FMT=Delimited"')...test#txt

Pls help me out this issue

If you don't specify a schema.ini file providing mapping of fields and data types then the provider will try to determine the data type based on sampling the data file. Since the first column of two rows have numeric values the provider will treat that column as numeric. This might result in the 3rd row having NULL value in first column because 'x' is invalid. Check out the provider documentation for more details and you can also learn about schema.ini file. Using a schema.ini file you should be able to specify the first column as character or string.|||

Thanks for your reply

my requirement is that from a perticular path i have to read the flat files and insert into a temporary table. In the path contain more than one file and file have no predefined format or order.From the temp table i have to do some process. so i have to read the file as it is.

so through schema.ini is it possible or can you suggest me how to do the same

Thanks
Sunil

|||Man,
If the file don't have predefined format or order, give a dummy order in the ini file & you can have different sections in that, which specifies the which sections belongs to which flat file name.
as in my previous reply, now you may have your .ini file as
"SCHEMA.INI"

[test.txt]
ColNameHeader=True
Format=CSVDelimited
CharacterSet=ANSI
Col1=EmpID Char Width 20
Col2=EmpName Char Width 20
col3=ParentEmpID Char Width 20

[test1.txt]
ColNameHeader=True
Format=CSVDelimited
CharacterSet=ANSI
Col1=EmpID1 Char Width 20
Col2=EmpName1 Char Width 20
col3=ParentEmpID1 Char Width 20

[test2.txt]

ColNameHeader=True

Format=CSVDelimited

CharacterSet=ANSI

Col1=EmpID2 Char Width 20

Col2=EmpName2 Char Width 20

col3=ParentEmpID2 Char Width 20

this file contains the example of three flat file named test.txt , text1.txt. &text2.txt.

Hope this may help you little.

Gurpreet S. Gill|||

Hi Gurpreet,

thanks a lot.
my situation is like i don't know the colname (because flat file is generating daily), in that situation how can we include Col1="colname" Char width 20?
or any other way to do the same

Pls help me..

|||Hi SUNIL
if you do not know the ColName then just follow this

[test.txt]
ColNameHeader=false
Format=CSVDelimited
CharacterSet=ANSI

This will make the column name automatically (in my case it is F1, F2 & F3, may be different in your case, may be you need to set the first line as blank in the flat file).

But please note that if you use the this ini file, it will again leads to the problem of NULL, so some where, i think, you need to make compromise(either the null or make the column name with width).
I will again suggest you to count the number of columns & have the dummy name & set the width for each.
Read more at
http://www.devx.com/tips/Tip/12566
to create quick Schema.ini file.

Gurpreet S. Gill

Flat File Connection with different row types

I have a file format that uses many rows to describe relationships between one-to-many entries. Basically in many tables I one file with the only relations between tables been the order of the rows.

From what I’ve found so far, the Flat file Connection is not the correct Connection type to use for this. Is there something better suited, or documentation of creating Connection types.

Cheers Simeon.

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamiesql

2012年3月21日星期三

Flat file connection is skipping columns

The data file contains column names in the first row. Excel imports the file correctly. I can see the tabs in UltraEdit32. But...the flat file connection just skips over a column.
In the preview window, it appears to skip the column entirely.

However, when the data is imported, data from the non recognized column goes into a column that is mapped to receive data from another column shifting the data in to the next column.

I am using the CopyColumn and the SQL Destination controls.

Any ideas on how to make this work?

Thanks,
IanOCan you copy 'n paste some of the data here for us to look at?

Flat file - row delimiter problem

Hi,

I'm trying to design this package where i take data from a source and need to transform it into a flatfile with some extra static information.

I use a SQL script like this (ex.):

SELECT '

BS0220131264202400000130001'+cast(wa.perf_applicant_number as nvarchar)+'000000000' + wa.perf_firstname + ' ' + wa.perf_lastname + CHAR(13)+

'BS0220131264202400000330001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000' + WAPD2.strvalue+ '

BS0520131264202410001130001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000 tekst der skal st? p? kortet' as nvarchar

FROM dbo.WAIT_Applicant WA (nolock)

This makes the text (from one record) split up over several lines in the output.

I succeded with this in a SQL2000 DTS package and the flat txt-file looked liked I wan't it to. But now i tried doing it in 2005. And now it is not workin' anymore

In my Flat File Connection Manager Editor i chose {LF} as the row delimiter and the preview looks really nice. Like this:

BS0220131264202400000130001000000015826727000000000S?ren Hesth

BS0220131264202400000330001000000015826727000000000adfasdf

BS0520131264202410001130001000000015827207000000000 tekst der skal st? p? kortet

But in the file that is created it doesn't split up over several lines. Instead of a carriage return it puts a [black box] - a sign which counts as the carriage return.

I don't know if I have explained this well enough, but I hope that someone can help me. I've been trying for 3 days now.

I'm guessing you want the Flat File to output records on individual lines (if you opened the file in notepad). If that is the case use {cr}{lf} as your row delimiter, since in a Windows environment that is the standard newline character combination. As single {lf} is often employed in Unix/Mainframe environments as a newline character, which is why it is an option.
Larry Pope
|||

That was also what i started with, but then i read in another discussion inhere, where they suggested to use {LF}, so i changed it.

What i want is, that one record is printed over several lines in the text-file. After that record, then the next record is printed, also over several lines in the text-file.

I have tried to change it back and tried almost every possible combination of

- rowdelimiter ( CRLF, CR, LF...)

- format (ragged right, delimied...)

and so on.

And nothing works.

Is there any other way of doing this. Maybe there is something I can do in the script.

|||If what you want is something like the following (Assume #is a comment line and doesn't exist in the file).
#Record1
Column1Column2
Column3Column4
Column5Column6
#Record2
Column1Column2
Column3Column4
Column5Column6
...
If that is what you want, then you will need to build both a custom component either through a script transform or a full-fledged component.
The code would be something similar to the following
Dim sw As New System.IO.StreamWriter("c:\temp\test.txt", True)
sw.WriteLine(Row.Column1.ToString & Row.Column2.ToString)
sw.WriteLine(Row.Column3.ToString & Row.Column4.ToString)
sw.WriteLine(Row.Column5.ToString & Row.Column6.ToString)
sw.Close()
This will append to an existing file, so if you may need to create a task that deletes the existing file prior to the data flow task. You'll also should check for errors (null values, stream writer was created, etc).
Larry Pope
|||

But i succeded with doing this with my the package i wrote in SQL server 2000.

There must be a way that I can make a carriage return, so that the notepad will read it correctly.

2012年3月19日星期一

FK constraint when FK row is there! Always takes 10 mins to fail!

I am developing a SQLServer/JSP/Java (Jrun4) web app that intermittently has a FK constraint that always hoses up the transaction for exactly 10 minutes.

The user can save a record from a form that inserts a few rows in the db. The second insert is dependent on the FK of the first insert. All the inserts are done in an entity bean that calls stored procs.

We've had this intermittant error where the inserts take exactly 10 minutes (every time!), then it fails with a FK constraint, yet out logging shows that it's using an existing foreign key. (the stored procs returns the FK)

We have a dev server and a test server, and this has yet to occur on the dev server! I feel like there must be something set up incorrectly.

Any ideas?

thanks in advance!

-DanFlagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||And the same indexes and statistics?|||There isn't much data: under 100 rows, 10 column.

The problem only happens intermittently. In fact it hasn't happed in the last several hundered inserts (we've been testing it)

Originally posted by strader
Flagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||Both servers are set up the same. The more I think about it- based on the infrequency of the problem it may just be a coincidence that it only happed on the one server.

I'm investigating a possible answer to the problem- I noticed an unhandled exception in the bean and I think it's possible the 1st insert worked, then some unhandled exception occured and rolled back the transaction, then the bean continuted to attempt to insert the child row- just a theory since this is so infrequent.

Originally posted by Paul Young
And the same indexes and statistics?|||Here's another possibility - if you're running simultaneous transactions from different threads or processes against that table, and if your java code or your interface (jdbc?) is hanging, causing that transaction to sit there indefinitely, it could be causing blocking or deadlocks on that small table. If it puts an exclusive lock on the table, and you're holding that transaction open, then all the other waiting processes will fail or timeout.

It might be happening on only one server vs. the other because you're stressing one more than the other.

If it happens again, try using SET LOCK_TIMEOUT to change it to a shorter time for that connection. The more I think about it, the more likely it seems this is what's happening, since it's timing out after exactly ten minutes each time.|||I wanted to try one more thing before I tried your suggestion- and it looks like I fixed the problem, but I'm not 100% sure why the transaction was failing. Here's what the code was doing:

When the user saves form data, the entity bean inserts rows into the database, and in the same transaction (I think) proceeds to update those rows. My guess is that once in a while sqlserver locks those rows so that the records cannot be re-read. My fix was to prevent the extraneous update from occuring. It was happening because I didn't know enough about Java beans when I wrote the bean code. (I only used the store method in the bean for UPDATES and I handled inserts manually, but the store is called automatically for all bean calls so I ended up inserting, then updating).

Our beans are container managed, so that their transactions begin when the bean is called and end when the bean returns. I thought that one could manipulate an uncommited row inserted within a transaction but I guess that's not ALWAYS the case because 1 out of 100 times it times out and fails.

I'd say the problem's solved since it's been a week since my fix was in and no more problems!

thanks for the help.

2012年3月11日星期日

Fixed Header in Reporting Service 2000

I want to create a report with SQL Server Reporting Service 2000 where I need to have a fixed header and one fixed column containg names of each row items. I tried to find "Fixed Header" property in table but could not locate it anywhere.Can anyone please kindly tell me where should i look for it?Or how to achieve this functionality?

Thank you in advance.

Bharat Gadhia.

FixedHeader is a new feature that got added in RS 2005. It was not available in 2000.|||

Hi Fang,

Thank you for the reply.

Can you tell me how to achieve this functionality in SQL Server Reporting Service 2000?

Bharat Gadhia.

2012年3月7日星期三

First, Last, Middle ??

I have a Matrix that always displays two rows of data.
One row show values from Jan 1 of the current year.
The second row shows values for today.
In the foot I show the % the values have changed so YTD with the following
formula:
=(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
First(Fields!Core.Value) * 100
This works great and looks like this:
Date | Core
--
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25%
I have been asked to add in the value from a year ago today but still
display the change in value from just Jan 1.
Date | Core
--
Aug 31 2004 | $300
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25% (Diff between Jan 1 and Aug 31 2005)
How would I calc the % change in the footer. My formula will not work as the
"First" value is a year ago today not Jan 1. Is there a "Middle" function ?
:) :)
Thoughts ?
Thanks in Advance
Pete MitchellTry something like this:
=(Last(Fields!Core.Value) - CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))))
/
CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
GeoSynch
"PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
>I have a Matrix that always displays two rows of data.
> One row show values from Jan 1 of the current year.
> The second row shows values for today.
> In the foot I show the % the values have changed so YTD with the following
> formula:
> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> First(Fields!Core.Value) * 100
> This works great and looks like this:
> Date | Core
> --
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25%
> I have been asked to add in the value from a year ago today but still
> display the change in value from just Jan 1.
> Date | Core
> --
> Aug 31 2004 | $300
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25% (Diff between Jan 1 and Aug 31 2005)
> How would I calc the % change in the footer. My formula will not work as the
> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> :) :)
> Thoughts ?
> Thanks in Advance
> Pete Mitchell|||Actually, it probably shoud be:
=(Last(Fields!Core.Value) - CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))))
/ CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) * 100
GeoSynch
"GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
news:ecLK3yprFHA.3884@.TK2MSFTNGP11.phx.gbl...
> Try something like this:
> =(Last(Fields!Core.Value) -
> CDate("1/1/"&CStr(Year(First(Fields!Core.Value))))) /
> CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
>
> GeoSynch
>
> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
>>I have a Matrix that always displays two rows of data.
>> One row show values from Jan 1 of the current year.
>> The second row shows values for today.
>> In the foot I show the % the values have changed so YTD with the following
>> formula:
>> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
>> First(Fields!Core.Value) * 100
>> This works great and looks like this:
>> Date | Core
>> --
>> Jan 1 2005 | $400
>> Aug 31 2005 | $500
>> --
>> Footer +25%
>> I have been asked to add in the value from a year ago today but still
>> display the change in value from just Jan 1.
>> Date | Core
>> --
>> Aug 31 2004 | $300
>> Jan 1 2005 | $400
>> Aug 31 2005 | $500
>> --
>> Footer +25% (Diff between Jan 1 and Aug 31 2005)
>> How would I calc the % change in the footer. My formula will not work as the
>> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
>> :) :)
>> Thoughts ?
>> Thanks in Advance
>> Pete Mitchell
>|||Thanks a bunch.
How does that work ?
There are two fields in play here : Date and Core
How is that get the Core.value when the Date.value = Jan 1 2005 ?
Pete
"GeoSynch" wrote:
> Actually, it probably shoud be:
> =(Last(Fields!Core.Value) - CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))))
> / CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) * 100
>
> GeoSynch
>
> "GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
> news:ecLK3yprFHA.3884@.TK2MSFTNGP11.phx.gbl...
> > Try something like this:
> > =(Last(Fields!Core.Value) -
> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value))))) /
> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
> >
> >
> > GeoSynch
> >
> >
> > "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> > news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
> >>I have a Matrix that always displays two rows of data.
> >> One row show values from Jan 1 of the current year.
> >> The second row shows values for today.
> >>
> >> In the foot I show the % the values have changed so YTD with the following
> >> formula:
> >>
> >> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> >> First(Fields!Core.Value) * 100
> >>
> >> This works great and looks like this:
> >> Date | Core
> >> --
> >> Jan 1 2005 | $400
> >> Aug 31 2005 | $500
> >> --
> >> Footer +25%
> >>
> >> I have been asked to add in the value from a year ago today but still
> >> display the change in value from just Jan 1.
> >>
> >> Date | Core
> >> --
> >> Aug 31 2004 | $300
> >> Jan 1 2005 | $400
> >> Aug 31 2005 | $500
> >> --
> >> Footer +25% (Diff between Jan 1 and Aug 31 2005)
> >>
> >> How would I calc the % change in the footer. My formula will not work as the
> >> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> >> :) :)
> >>
> >> Thoughts ?
> >>
> >> Thanks in Advance
> >>
> >> Pete Mitchell
> >
> >
>
>|||CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) evaluates thusly:
Last(Fields!Core.Value) = '08/31/2005' data type Date
Year(Last(Fields!Core.Value)) = '2005' data type Integer
CStr(Year(Last(Fields!Core.Value))) converts it to a string value
so that when concatenated with "1/1/" it will evaluate to string value
"1/1/2005"
CDate converts it back to an actual date value of '01/01/2005'
GeoSynch
"PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
news:940A01C6-D8A5-4C44-8BA4-3AF232AD790F@.microsoft.com...
> Thanks a bunch.
> How does that work ?
> There are two fields in play here : Date and Core
> How is that get the Core.value when the Date.value = Jan 1 2005 ?
> Pete
>
> "GeoSynch" wrote:
>> Actually, it probably shoud be:
>> =(Last(Fields!Core.Value) -
>> CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))))
>> / CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) * 100
>>
>> GeoSynch
>>
>> "GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
>> news:ecLK3yprFHA.3884@.TK2MSFTNGP11.phx.gbl...
>> > Try something like this:
>> > =(Last(Fields!Core.Value) -
>> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value))))) /
>> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
>> >
>> >
>> > GeoSynch
>> >
>> >
>> > "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
>> > news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
>> >>I have a Matrix that always displays two rows of data.
>> >> One row show values from Jan 1 of the current year.
>> >> The second row shows values for today.
>> >>
>> >> In the foot I show the % the values have changed so YTD with the following
>> >> formula:
>> >>
>> >> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
>> >> First(Fields!Core.Value) * 100
>> >>
>> >> This works great and looks like this:
>> >> Date | Core
>> >> --
>> >> Jan 1 2005 | $400
>> >> Aug 31 2005 | $500
>> >> --
>> >> Footer +25%
>> >>
>> >> I have been asked to add in the value from a year ago today but still
>> >> display the change in value from just Jan 1.
>> >>
>> >> Date | Core
>> >> --
>> >> Aug 31 2004 | $300
>> >> Jan 1 2005 | $400
>> >> Aug 31 2005 | $500
>> >> --
>> >> Footer +25% (Diff between Jan 1 and Aug 31 2005)
>> >>
>> >> How would I calc the % change in the footer. My formula will not work as
>> >> the
>> >> "First" value is a year ago today not Jan 1. Is there a "Middle" function
>> >> ?
>> >> :) :)
>> >>
>> >> Thoughts ?
>> >>
>> >> Thanks in Advance
>> >>
>> >> Pete Mitchell
>> >
>> >
>>

First, Last, Again

first()

Hi all

I'm trying to do some calculations using java.....when I execute my query and want to ensure that I am at the first row I'm using ResultSet.first()...but that gives me an "Unsupported method"-error.

I've tried ResultSet.absolute(1) as well - with the same result unfortunately :(

Does anybody know what I'm doing wrong ??

Thanks

KimDid you make your resultset scrollable ?|||That looks more like vb than java...

http://java.sun.com/docs/books/tutorial/index.html|||Nope - its java.

2012年2月26日星期日

first row in each page

Hi,
Can i know the first RowNumber in each page on a table ?
Thanks
--
Pony TsuiHi Pony,
Thank you for using Microsoft MSDN Managed Newsgroup Support.
In Reporting Services, you could use the RowNumber function to refer the
RowNumber of each row.
Here is an article for your reference:
http://msdn2.microsoft.com/en-us/library/ms159225(d=ide).aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community 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 Lu,
Thanks for your reply.
I saw the RowNumber online help page before, RowNumber(Nothing) get the
RowNumber of a table, but i don't know how to get the first row in each page,
please help me.
--
Pony Tsui
"Wei Lu [MSFT]" wrote:
> Hi Pony,
> Thank you for using Microsoft MSDN Managed Newsgroup Support.
> In Reporting Services, you could use the RowNumber function to refer the
> RowNumber of each row.
> Here is an article for your reference:
> http://msdn2.microsoft.com/en-us/library/ms159225(d=ide).aspx
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community 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 Pony,
Thank you for your update.
I would like to know how you pagination the report. Do you use any group?
If so, you could add a hidden column and add a textbox which contain a
value of First row of your group.
For example, if you are using the Group "tbl_Group", and your dataset have
a field named "field1". You could use the following expression in the
hidden textbox:
=first(Fields!field1.Value,"tbl_Group")
Then, you could use the expression to control the visibility of the
Rownumber textbox:
=IIf(ReportItems!txt_field1.Value = ReportItems!First_field1.Value
, false,true)
Hope this will be helpful for you. If you have any quesion, please feel
free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Lu,
Thanks for your reply.
Acturally, i need to change the row backgroud color, for example, set odd
rows to blue, other to red, i use the table, but the rows of each page was
not fixed, maybe page 1 is 15, page 2 is 16, but i try to set the first row
to blue in each page.
i don't use any gruop, any suggest ?
--
Pony Tsui|||Hi Lu,
I don't see any attachment, can you send to me again?
Thanks,
--
Pony Tsui|||Hi Lu,
I got the Demo Report, thanks.
I try the Demo Report, the backgroud color of first row in each page is
blue, other rows is red, but i need alternate background color, and the rows
in each page is not fix number, i can set alternate background color, but i
want the color of first row in each page is fix.
For example:
=iif(RowNumber(Nothing) Mod 2=1, "blue", "red")
page 1(15 Rows)
row 1 blue
row 2 red
row 3 blue
row 4 red
row 5 blue
row 6 red
...........
...........
...........
row 15 blue
page 2(14 Rows)
row 1 red
row 2 blue
row 3 red
row 4 blue
row 5 red
row 6 blue
...........
...........
...........
row 14 red
the background color of first row in page 1 is blue
the background color of first row in page 2 is red
because the rows in each page is not fix in 15 or 14, so the background
color of first row in each page is variation also.
is there any way to solve this problem?
Pony Tsui|||Hello Pony,
Thank you for your reply.
In this case, I suggest you to limit the rows per page to a fixed amount so
that you could use the expression in your post to control the background
color.
To limit the rows per page to a fixed number, you could do the following:
1. Add a details grouping on the table based on a group expression similar
to this:
=Ceiling(RowNumber(Nothing)/20)
2. Make sure to select "page break at end" on the detail group dialog.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Pony,
How is everythin going? If you have any question, please feel free to let
me know.
Sincerely,
Wei Lu
Microsoft Online Community Support

First returns incorrect row

This is a multi-part message in MIME format.
--020206060805020100000401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
It seems First is returning the first row returned by the Dataset, not
the first row according to the sorting of the dataregion or grouping (as
the First function's documentation implies). This ends up with some
mysterious results in my reports. I created a simple example using the
SQL Northwind that shows the problem and attached it. Is this a bug or
intended behavior? It makes some of my reports using sorted groups have
seemingly bogus results when viewed.
Thanks,
Aaron
--020206060805020100000401
Content-Type: text/xml;
name="FirstLastAnomaly1.rdl"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="FirstLastAnomaly1.rdl"
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<DataSetName>Northwind</DataSetName>
<Top>0.125in</Top>
<Width>3.25in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!FirstName.Value & " " & Fields!LastName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="1st:" & First(Fields!City.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox17</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="1st:" & First(Fields!OrderID.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<CanGrow>true</CanGrow>
<Value>="Last:" & Last(Fields!City.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Last:" & Last(Fields!OrderID.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!FirstName.Value & " " & Fields!LastName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!FirstName.Value & " " & Fields!LastName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#80ff80</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox19</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#80ff80</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox20</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!OrderID.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!City.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!City.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>b54bf116-08cc-4b39-aa96-57013b5de15d</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>3.375in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="LastName">
<DataField>LastName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="FirstName">
<DataField>FirstName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EmployeeID">
<DataField>EmployeeID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.CompanyName, Customers.CustomerID, Orders.OrderID, Employees.LastName, Employees.FirstName, Employees.EmployeeID,
Customers.City
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
Employees ON Orders.EmployeeID = Employees.EmployeeID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>c31d746f-9224-4fcd-8568-e5376409e635</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
--020206060805020100000401--Yes, you're right. Do your sort in the dataset query not in the dataregion
Sorting tab.
Use ORDER BY in the query, that is.
Charles Kangai, MCT, MCDBA
"Aaron S." wrote:
> It seems First is returning the first row returned by the Dataset, not
> the first row according to the sorting of the dataregion or grouping (as
> the First function's documentation implies). This ends up with some
> mysterious results in my reports. I created a simple example using the
> SQL Northwind that shows the problem and attached it. Is this a bug or
> intended behavior? It makes some of my reports using sorted groups have
> seemingly bogus results when viewed.
> Thanks,
> Aaron
>|||Thank you for the reply. I have added an order by to the SProc in my
production report and it works, this is not ideal for me as I'd rather
not have the SProc's sort dependent on a report. So is this a bug we
should expect to be corrected?
Thanks again for responding.
Charles Kangai wrote:
> Yes, you're right. Do your sort in the dataset query not in the dataregion
> Sorting tab.
> Use ORDER BY in the query, that is.
> Charles Kangai, MCT, MCDBA
> "Aaron S." wrote:
>
>>It seems First is returning the first row returned by the Dataset, not
>>the first row according to the sorting of the dataregion or grouping (as
>>the First function's documentation implies). This ends up with some
>>mysterious results in my reports. I created a simple example using the
>>SQL Northwind that shows the problem and attached it. Is this a bug or
>>intended behavior? It makes some of my reports using sorted groups have
>>seemingly bogus results when viewed.
>>Thanks,
>>Aaron|||I just want to confirm that SRS ignores the Sort order of the table and
uses the sort order of the SP query. Is version 2 out yet ?!!
Aaron S. wrote:
> Thank you for the reply. I have added an order by to the SProc in my
> production report and it works, this is not ideal for me as I'd
rather
> not have the SProc's sort dependent on a report. So is this a bug we
> should expect to be corrected?
> Thanks again for responding.
>
> Charles Kangai wrote:
> > Yes, you're right. Do your sort in the dataset query not in the
dataregion
> > Sorting tab.
> > Use ORDER BY in the query, that is.
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Aaron S." wrote:
> >
> >
> >>It seems First is returning the first row returned by the Dataset,
not
> >>the first row according to the sorting of the dataregion or
grouping (as
> >>the First function's documentation implies). This ends up with some
> >>mysterious results in my reports. I created a simple example using
the
> >>SQL Northwind that shows the problem and attached it. Is this a bug
or
> >>intended behavior? It makes some of my reports using sorted groups
have
> >>seemingly bogus results when viewed.
> >>
> >>Thanks,
> >>
> >>Aaron
> >>

First or last row in T-SQL

Hi every body

I want to know what is similar to Last and First in Group By cluse like Access 2000.

I want to get last row of a column at the same time as get sum of another column

hi koosha,

i'm not sure if this is what you want

select max(orderid),customerid from
dbo.Orders
group by customerid

select min(orderid),customerid from
dbo.Orders
group by customerid

|||On Sat, 7 Jan 2006 05:33:00 -0800,

wrote:

>Hi every body

>I want to know what is similar to Last and First in Group By cluse like

>Access 2000.

>I want to get last row of a column at the same time as get sum of

>another column

Hi Koosha,

No, there is no such thing. Tables in a relational database have no

implied ordering, by definition. There can only be an order if you

specify it (e.g. using ORDER BY).

If you want the lowest or highest value of a column, use MIN(column) or

MAX(column). If you want the "first" or "last" row as defined by some

other order, use TOP 1, a subquery or a join to a derived table. Here's

an example using a subquery:

SELECT SUM(Column1) AS SumOfColumn1,

(SELECT TOP 1 Column2

FROM TheTable

ORDER BY SortColumn) AS FirstOfColumn2

FROM TheTable

Hugo Kornelis, SQL Server MVP|||

Jose is correct. There is no direct equivalent to "First" and "Last". You can sometimes use "Min" and "Max" as substitutes if that makes sense for the underlying data type.

|||hi

thanks for ur message.but i need to use for like this :

select sum(Orderprice) as exp1 , last(orderprice) as exp2 from table group by customerId

in that way u mentioned , it 's not posible to use with sum and other agregate function

I found 2 way to simulate but I don't know which one is better .

1- to design a class and add to assembly for sql 2005 .(I tried and was unsuccess)

2- to use another function inside my select (it answered)

First or last row in T-SQL

Hi every body

I want to know what is similar to Last and First in Group By cluse like Access 2000.

I want to get last row of a column at the same time as get sum of another column

hi koosha,

i'm not sure if this is what you want

select max(orderid),customerid from
dbo.Orders
group by customerid

select min(orderid),customerid from
dbo.Orders
group by customerid

|||On Sat, 7 Jan 2006 05:33:00 -0800,

wrote:

>Hi every body

>I want to know what is similar to Last and First in Group By cluse like

>Access 2000.

>I want to get last row of a column at the same time as get sum of

>another column

Hi Koosha,

No, there is no such thing. Tables in a relational database have no

implied ordering, by definition. There can only be an order if you

specify it (e.g. using ORDER BY).

If you want the lowest or highest value of a column, use MIN(column) or

MAX(column). If you want the "first" or "last" row as defined by some

other order, use TOP 1, a subquery or a join to a derived table. Here's

an example using a subquery:

SELECT SUM(Column1) AS SumOfColumn1,

(SELECT TOP 1 Column2

FROM TheTable

ORDER BY SortColumn) AS FirstOfColumn2

FROM TheTable

Hugo Kornelis, SQL Server MVP|||

Jose is correct. There is no direct equivalent to "First" and "Last". You can sometimes use "Min" and "Max" as substitutes if that makes sense for the underlying data type.

|||hi

thanks for ur message.but i need to use for like this :

select sum(Orderprice) as exp1 , last(orderprice) as exp2 from table group by customerId

in that way u mentioned , it 's not posible to use with sum and other agregate function

I found 2 way to simulate but I don't know which one is better .

1- to design a class and add to assembly for sql 2005 .(I tried and was unsuccess)

2- to use another function inside my select (it answered)

2012年2月19日星期日

firehouse mode

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.)

firehouse mode

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.)

订阅：博文 (Atom)

2012年3月27日星期二

flatten out a normalized child table?

2012年3月26日星期一

Flat File with random bad rows.

Flat File with random bad rows.

Flat File with random bad rows.

Flat File to SQL table

2012年3月22日星期四

Flat file return NULL value

Flat File Connection with different row types

2012年3月21日星期三

Flat file connection is skipping columns

Flat file - row delimiter problem

2012年3月19日星期一

FK constraint when FK row is there! Always takes 10 mins to fail!

2012年3月11日星期日

Fixed Header in Reporting Service 2000

2012年3月7日星期三

First, Last, Middle ??

First, Last, Again

first()

2012年2月26日星期日

first row in each page

First returns incorrect row

First or last row in T-SQL

First or last row in T-SQL

2012年2月19日星期日

firehouse mode

firehouse mode

FTS Index Size

Blog Archive

About Me