Floating Point Numbers in BCP File

Hi,
I am BCPing in a tab-delimited text file and I am getting this error
message:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
The text file records look like below:
200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
Not sure if that floating point number at the end of the file is the cause
of this problem or not.
Any ideas on how to solve the problem?
JDJoe Delphi wrote:
> Hi,
> I am BCPing in a tab-delimited text file and I am getting this
> error message:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value
> for cast specification
> The text file records look like below:
> 200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
> 200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
> Not sure if that floating point number at the end of the file is the
> cause of this problem or not.
> Any ideas on how to solve the problem?
> JD
Have you verified there are actually TAB characters in the file where
they should be? You might try using the DTS Import Wizard to see if that
works. The wizard will show you the parsed data based on your
delimiters, so that might clue you in to the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Joe Delphi (delphi561@.nospam.cox.net) writes:
> I am BCPing in a tab-delimited text file and I am getting this error
> message:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast specification
> The text file records look like below:
> 200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
> 200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
> Not sure if that floating point number at the end of the file is the cause
> of this problem or not.
> Any ideas on how to solve the problem?
Please post the CREATE TABLE statement for the table. It's impossible to
tell without that information what is going on.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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

\n

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.

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

\n

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.

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

\n

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

|||

OK

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

-Jamie

|||Thanks. your great..

flat file source and destination - need fixed width output

I have a text file that is comma delimited and im pulling it in with a flatfile connection manager. I want to read some of the data, then output another flat file but in a fixed column width. What settings do I made to the connection manager of the output flatfile ?Choose a fixed-width format when setting up the destination. And then define your columns.|||

Hi

A solution is:

- Go to advanced properties of output flat file.

- Define the fields as text and the property OutputColumnWith with the size you want

- Export your data to that file and convert it to text.

Raul

|||I had some other steps in the middle, so I took them out just to simplify things. In the connection mgr for my destination, it wants to know the input column widths. Should I really need to bother with this, since in the end, I just want whatever output i get during any previous steps, to simply be output to the flat file as fixed width ?|||Ok, ive managed to get my output in fixed width in the output file, but it appears the lines arent terminating where they should be. What controls where the lines terminate ? I dont have a header row (no column names in the first row), so what, if anything should the "header row" settings be set to ?|||Ive got a data file with values seperated with commas. I want to read in this text file, do a lookup and add the lookup column on the front of the other columns and save the output in a fixed width format, similar to what you would get if you saved the query reqults to a file in sql mgt studio.

What I have so far is just a flat file source , a lookup and a flat file destination.

I can get the output to generate, but there doesnt seem to be any row terminating, its all one big string.

help ?|||Use "Fixed width with Row Delimiters" option for the flat file connection manager.|||

Eric Wisdahl wrote:

Use "Fixed width with Row Delimiters" option for the flat file connection manager.

Where is that option available ? Under the general tab on my flat file conn mgr, I have only the options:
"fixed width"
"delimited"
"ragged right"

If I have fixed width selected, and go to the advanced tab, start entering my own columns, the column delimiter option is greyed out.|||When you are first creating the flat file connection manager it gives you the option of delimited, fixed width, fixed width with row delimiters, ragged right. All that fixed width with row delimiters does is add another column to each record which contains the row delimiter. You can accomplish the same thing by adding it in with the derived column and adding it to the end of your output record.

Flat file processing dilemma

Hello,

How do I import data from a text file into columns (DB_table) that I have created in SQL server 2005?

Data in text file does not have any commas, semicolons, or spaces in between them.

Each instance in the text file consists of 109 characters (real numbers).

I also know that first 4 numbers go in particular field, next 4 go in another field, and next 6 are particular field, and so on.

I also have created columns in a table respectively.

Ravi,

have you tried using Fixed Width (or Ragged Right if you have row delimiters) format to process your files?

|||Use a fixed width file format and your problem is solved.

Flat file Import performance question

Hi there,

I have a question regarding 2 approaches importing data from a flat text file. I'm taking the approach suggested by Phil Brammer to use a checksum to see whether a row has exists, and if it does check whether it has changed.

http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed

I would like to hear some opinions on 2 approaches:

1) Importing the data int a temp table first then do the data processing from the temp table.
Or
2) Importing the data by doing the data processing direcly from the text file without placing it in a temp table first.

A hunch told me that it will be faster doing approach 1 but I would like to hear some experienced opinions first

Best regards
Mike

I would think #2 would be *slightly* faster just because there are less components at work.

With that said, I prefer #1 always because it quickly loads the data to a table which I can then use SQL to get the data out. This might be advantageous for sorting the data, filtering the data, joining the data to another table, etc... Basically I like to push as much work off to the database engine that I can. Sure, SSIS can merge (join) data sets together, but the database engine will do a much better job. Also, having the data staged will help me to restart the process should it fail, work in batches (perhaps) by loading more than one file to the table if needed, and guarantee that the data will be there when I need it in an environment that might not guarantee that the file will always be around for use.

|||Thanks Phil,

I really appreciate your valuable input.

Best regards
Mike
|||

If you can afford, I would suggest to test both approaches, in your own environment, and then base your decisions on that way collected (real) data.

Thanks,

Bob

sql

Flat file Import performance question

Hi there,

I have a question regarding 2 approaches importing data from a flat text file. I'm taking the approach suggested by Phil Brammer to use a checksum to see whether a row has exists, and if it does check whether it has changed.

http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed

I would like to hear some opinions on 2 approaches:

1) Importing the data int a temp table first then do the data processing from the temp table.
Or
2) Importing the data by doing the data processing direcly from the text file without placing it in a temp table first.

A hunch told me that it will be faster doing approach 1 but I would like to hear some experienced opinions first

Best regards
Mike

I would think #2 would be *slightly* faster just because there are less components at work.

With that said, I prefer #1 always because it quickly loads the data to a table which I can then use SQL to get the data out. This might be advantageous for sorting the data, filtering the data, joining the data to another table, etc... Basically I like to push as much work off to the database engine that I can. Sure, SSIS can merge (join) data sets together, but the database engine will do a much better job. Also, having the data staged will help me to restart the process should it fail, work in batches (perhaps) by loading more than one file to the table if needed, and guarantee that the data will be there when I need it in an environment that might not guarantee that the file will always be around for use.

|||Thanks Phil,

I really appreciate your valuable input.

Best regards
Mike
|||

If you can afford, I would suggest to test both approaches, in your own environment, and then base your decisions on that way collected (real) data.

Thanks,

Bob

Flat file connection manager question

Hi,

I am having problems setting up columns in a flat file connection manager.

The text file I am trying to parse is in this format:

col1 col2 col3 col4 [] col1 col2 col3 col4 [] col1 col2 col3 col4

where [] = {LF}

I've tried all of the different column delimiters, but apparently this file does not use any of the built-in delimiters, such as tab. I think there are just blank spaces between the columns.

I've requested that the file be comma-delimited instead, but in the event that this is not possible, how should I handle this situation?

Thank you

Are you sure it isn't a fixed-width format?|||

Actually I think it is.

|||Yep, start there and see how far you get. Try working with either the fixed-width format or the ragged-right format.

Flat file connection manager not parsing file correctly

Hi,

I have a flat file, comma-delimited, with strings in double-quotes.

In the connection manager for the file, I have specified that the Text Qualifier = ""

However, in the preview tab, it still shows the strings as surrounded by the quotes, e.g. "mycol1" whereas it should show mycol1 without the quotes.

Next, when I examine the data in the database after the load, it's messed up there also.

"mycol1" ends up in the database as "mycol1

"mycol2" ends up as "mycol2

This is not right.

I have format set to delimited, header row delimiter crlf, etc.

Any ideas?

Thanks

I sort of see what's happening.

The "mycol1 problem is due to the fact that I have mycol1 set as a string with a length of 6. Because it's not stripping off the double-quotes correctly, it counts the first six characters of "mycol1", which is of course "mycol1. It's counting the first double-quote as a character.

So the question remains, HOW do I get it to recognize the fact that "" are the text delimiters?

I remember setting up this same flat file connection before and it worked, but now it's not.

|||

Nevermind!

The text delimiter is ", not ""!!

doh

sql

Fixing a expression

Hi,
I have the following expression for one of the text boxes in my report:
=Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
But now if division by 0 occurs, it shows up as 'NaN' when run, I would like
it to show up as 0 if division by zero occurs.
Any suggestions on how this expression can be modified to do this?
ThanksTry this:
= iif(Fields!openduring.Value = 0, 0, Sum(Fields!totallagtime.Value) /
iif(Fields!openduring.Value = 0, 1, Fields!openduring.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> Hi,
> I have the following expression for one of the text boxes in my report:
> =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> But now if division by 0 occurs, it shows up as 'NaN' when run, I would
like
> it to show up as 0 if division by zero occurs.
> Any suggestions on how this expression can be modified to do this?
> Thanks
>|||=Iif(sum(Fields!openduring.Value)=0,0,Sum(Fields!totallagtime.Value)/Sum(Fie
lds!openduring.Value))
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
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> Hi,
> I have the following expression for one of the text boxes in my report:
> =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> But now if division by 0 occurs, it shows up as 'NaN' when run, I would
like
> it to show up as 0 if division by zero occurs.
> Any suggestions on how this expression can be modified to do this?
> Thanks
>|||Hey Rebert,
Could you explain to me what this is doing in english becaue I have never
used iff before and I am not sure what it is doing.
Thanks
"Robert Bruckner [MSFT]" wrote:
> Try this:
> = iif(Fields!openduring.Value = 0, 0, Sum(Fields!totallagtime.Value) /
> iif(Fields!openduring.Value = 0, 1, Fields!openduring.Value))
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
> news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> > Hi,
> >
> > I have the following expression for one of the text boxes in my report:
> >
> > =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> >
> > But now if division by 0 occurs, it shows up as 'NaN' when run, I would
> like
> > it to show up as 0 if division by zero occurs.
> >
> > Any suggestions on how this expression can be modified to do this?
> >
> > Thanks
> >
>
>|||Thanks Wayne
"Wayne Snyder" wrote:
> =Iif(sum(Fields!openduring.Value)=0,0,Sum(Fields!totallagtime.Value)/Sum(Fie
> lds!openduring.Value))
>
> --
> 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
> "Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
> news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> > Hi,
> >
> > I have the following expression for one of the text boxes in my report:
> >
> > =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> >
> > But now if division by 0 occurs, it shows up as 'NaN' when run, I would
> like
> > it to show up as 0 if division by zero occurs.
> >
> > Any suggestions on how this expression can be modified to do this?
> >
> > Thanks
> >
>
>|||IIF is a function call which essentially gives you similar functionality as
an IF statement - however since IIF is a function it will evaluate all
arguments before it is invoked. Therefore you have to avoid divisions by
zero also in the arguments of the IIF function. This makes it necessary to
write the expression as shown in my previous posting.
More information on IIF is available on MSDN:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
Among others, all the following VB.NET runtime functions are available for
writing expression in Reporting Services:
http://msdn.microsoft.com/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:71BEBB45-521A-4FAC-8D3F-65245709A897@.microsoft.com...
> Hey Rebert,
> Could you explain to me what this is doing in english becaue I have never
> used iff before and I am not sure what it is doing.
> Thanks
> "Robert Bruckner [MSFT]" wrote:
> > Try this:
> > = iif(Fields!openduring.Value = 0, 0, Sum(Fields!totallagtime.Value) /
> > iif(Fields!openduring.Value = 0, 1, Fields!openduring.Value))
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
> > news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> > > Hi,
> > >
> > > I have the following expression for one of the text boxes in my
report:
> > >
> > > =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> > >
> > > But now if division by 0 occurs, it shows up as 'NaN' when run, I
would
> > like
> > > it to show up as 0 if division by zero occurs.
> > >
> > > Any suggestions on how this expression can be modified to do this?
> > >
> > > Thanks
> > >
> >
> >
> >

Fixed Width Text Report

I'm trying to create a fixed width text report. I've created a function that
takes a string and a length and returns the string either truncated or padded
with spaces to the given length.
I then use url parameters to modify the CSV device information settings to
change the encoding to ascii, change the extension to txt and change the
FieldDelimiter to %1f (unicode symbol for some kind of field grouping or
something).
Things seems to properly but I don't like having to set the FieldDelimiter
to anything. I tried setting it to null by saying isnull=true but that
generates an error about referencing a null object. I read something that
said to make it an empty string but I can seem to be able to do that using
url parameters. I could try it programmatically but I would prefer using the
url.
Does anyone have any ideas?(The parent post is mine, I just changed my login)
I decided that setting the FieldDelimiter to %1f was not a good idea. I
did try to set the parameter to an empty string programmatically but it
just went to the default comma delimiter.
I've decided to just plug in the url encoded value of which stands
for a null ascii character. I don't know if this is the best solution
but I am going with it. Here is my final url:
http://localhost/ReportServer?/Devel/TestFile&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=ascii
If anyone else comes up with any other ideas of how to use RS to create
fixed width text files I would be glad to hear them. I can't find
anything that explains a way of doing this. This would work perfect if
I could set the FieldDemlimiter parameter to nothing but it keeps going
to the default comma.
Thanks.
Gary

Fixed text in SQL being truncated

Hi

This might be an obvious question to some of you, but I'm stumped and any help would be appreciated! I used to work in SQL Server, and am new to Teradata SQL. I'm not sure if this query is not working because of limitation differences between the two, or if it is a pure SQL problem. (Seeing how I couldn't find a teradata forum, I posted this problem here).

This is an excerpt of a query I'm trying to run in Teradata SQL Assistant:
SELECT
'TYLastRollUpCompWSSI' as WSSISet,
'FcastClosing Stock' as Measure,
sum(ZEROIFNULL(a.CCP_Closing_Stk_SV)) as Amt
FROM VWI0GPP_WKLY_SGRP_RPAS_PLAN a

In the resultset, however, the fixed text 'FcastClosing Stock' is being truncated to just 10 characters (i.e. 'FcastClosi) and the fixed text 'TYLastRollUpCompWSSI' is being truncated to just 20 characters (i.e. 'TYLastRollUpCompW').

It might seem silly that I need this ridiculously long names in the first place, but my resultset needs to conform with what is currently in the table.OK ... I managed to come up with a solution that seems to work: I have used "cast" in the select:

SELECT
cast('TYLastRollUpCompWSSI' as CHAR(30)) as WSSISet,
cast('FcastClosing Stock' AS CHAR(25)) as Measure, ... etc.

fixed table header overlays text

Wondering if anyone else has had this problem? I'm using RS for SQL 2005 and
checked the option "Header should remain visible while scrolling" on my
table. And the header line does remain visible, but it's on top of the data
as you scroll down the page. (the header line is 3 lines long - if that
matters).
Thanks for any help.
MarkWe had this issue. To keep it from looking badly when the user scrolled
down the resultset, we set the background of the header to White instead of
transparent.
Regards.
Chris E.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16C3E1FF-7CB5-47F6-9C6B-C4136B96CE5E@.microsoft.com...
> Wondering if anyone else has had this problem? I'm using RS for SQL 2005
> and
> checked the option "Header should remain visible while scrolling" on my
> table. And the header line does remain visible, but it's on top of the
> data
> as you scroll down the page. (the header line is 3 lines long - if that
> matters).
> Thanks for any help.
> Mark|||Chris - thanks. worked perfectly.
m
"Chris" wrote:
> We had this issue. To keep it from looking badly when the user scrolled
> down the resultset, we set the background of the header to White instead of
> transparent.
> Regards.
> Chris E.
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:16C3E1FF-7CB5-47F6-9C6B-C4136B96CE5E@.microsoft.com...
> > Wondering if anyone else has had this problem? I'm using RS for SQL 2005
> > and
> > checked the option "Header should remain visible while scrolling" on my
> > table. And the header line does remain visible, but it's on top of the
> > data
> > as you scroll down the page. (the header line is 3 lines long - if that
> > matters).
> >
> > Thanks for any help.
> > Mark
>
>|||I just want to say "Thank you!". work for me too.
"Chris" wrote:
> We had this issue. To keep it from looking badly when the user scrolled
> down the resultset, we set the background of the header to White instead of
> transparent.
> Regards.
> Chris E.
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:16C3E1FF-7CB5-47F6-9C6B-C4136B96CE5E@.microsoft.com...
> > Wondering if anyone else has had this problem? I'm using RS for SQL 2005
> > and
> > checked the option "Header should remain visible while scrolling" on my
> > table. And the header line does remain visible, but it's on top of the
> > data
> > as you scroll down the page. (the header line is 3 lines long - if that
> > matters).
> >
> > Thanks for any help.
> > Mark
>
>

Fixed position of table at bottom of report problem...

I have a table that just contains text at the bottom, but depending on
the query, it might land anywhere. it always needs to be one inch from
the bottom.
Any help is appreciated.
trinttrint wrote:
> I have a table that just contains text at the bottom, but depending on
> the query, it might land anywhere. it always needs to be one inch
> from the bottom.
> Any help is appreciated.
You can set the location for the absolute position of the table relative to
the position of its container!
You know your page-size...so fill the top- and left-coordinates with the
right values
regards
Frank|||Ok,
That works...but I can't get the top to go higher than one inch from
the top
Frank Matthiesen wrote:
> trint wrote:
> > I have a table that just contains text at the bottom, but depending
on
> > the query, it might land anywhere. it always needs to be one inch
> > from the bottom.
> > Any help is appreciated.
> You can set the location for the absolute position of the table
relative to
> the position of its container!
> You know your page-size...so fill the top- and left-coordinates with
the
> right values
> regards
> Frank|||trint wrote:
> Ok,
> That works...but I can't get the top to go higher than one inch from
> the top
You can set the top margin / lower margin in report-settings for the whole
report to 0
regards
Frank|||I have a similar issue with absolute positioning. I have a table and a text
box in a report. The textbox should be at the bottom always. The table can
span any number of pages. In this case, fixing the left and top locations is
not working. Can you please suggest me a solution.

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 in 1 page in a list doesn't work??

This is a multi-part message in MIME format.
--=_NextPart_000_0014_01C4AAC9.04BD4DC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I've a report with some queries :
1. CLient infos (which return the client name and other text info)
2. Orders
3. Historical sales
My query 1 and 2 are displayed in top of my report.
My historical sales is displayed in 3 charts
I want to group these charts in 1 page (specially when exported in PDF), = but these charts appears AFTER the "orders" query.
and I want to display the name of the client in top of these 3 charts = (because I can't put it in the header of my page)
I've setup a list around my query 3
I've setup the keeptogether option for this list
My charts are in the list I've added a textbox in the same list with the formula:
=3DFirst(Fields!NomBT.Value, "ClientInfos")
When I display the result in PDF or TIFF, the name of the client appear = at the end of the first page and not in top of the second page.
But I there is enough space in the second page to display this name.
So why RS don't group the content in my list?
I don't want to setup "page break before" because if the user display = the result other the web, I prefer to scroll the page instead-of = clicking "page 2"
Thanks.
Jerome.
--=_NextPart_000_0014_01C4AAC9.04BD4DC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,

I've a report with some queries = :
1. CLient infos (which return the = client name and other text info)
2. Orders
3. Historical sales

My query 1 and 2 are displayed in top = of my report.

My historical sales is displayed in 3 charts
I want to group these charts in 1 page = (specially when exported in PDF), but these charts appears AFTER the "orders" query.
and I want to display the name of the = client in top of these 3 charts (because I can't put it in the header of my = page)

I've setup a list around my query = 3
I've setup the keeptogether option for = this list
My charts are in the list
I've added a textbox in the same list = with the formula:
=3DFirst(Fields!NomBT.Value, "ClientInfos")

When I display the result in PDF or = TIFF, the name of the client appear at the end of the first page and not in top of = the second page.
But I there is enough space in the = second page to display this name.
So why RS don't group the = content in my list?

I don't want to setup "page break = before" because if the user display the result other the web, I prefer to scroll the = page instead-of clicking "page 2"

Thanks.

Jerome.

--=_NextPart_000_0014_01C4AAC9.04BD4DC0--This is a multi-part message in MIME format.
--=_NextPart_000_00D2_01C4AADA.172F1310
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
All charts and text boxes instances are part of the list, not only the =content of one group. You need to add a reactangle around your text box and chart.
-- Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no =rights.
"J=E9j=E9" <willgart@._A_hAotmail_A_.com> wrote in message =news:Oee5RpuqEHA.1712@.tk2msftngp13.phx.gbl...
Hi,
I've a report with some queries :
1. CLient infos (which return the client name and other text info)
2. Orders
3. Historical sales
My query 1 and 2 are displayed in top of my report.
My historical sales is displayed in 3 charts
I want to group these charts in 1 page (specially when exported in =PDF), but these charts appears AFTER the "orders" query.
and I want to display the name of the client in top of these 3 charts =(because I can't put it in the header of my page)
I've setup a list around my query 3
I've setup the keeptogether option for this list
My charts are in the list I've added a textbox in the same list with the formula:
=3DFirst(Fields!NomBT.Value, "ClientInfos")
When I display the result in PDF or TIFF, the name of the client =appear at the end of the first page and not in top of the second page.
But I there is enough space in the second page to display this name.
So why RS don't group the content in my list?
I don't want to setup "page break before" because if the user display =the result other the web, I prefer to scroll the page instead-of =clicking "page 2"
Thanks.
Jerome.
--=_NextPart_000_00D2_01C4AADA.172F1310
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

All charts and text boxes =instances are part of the list, not only the content of one group.
You need to add a reactangle around =your text box and chart.
-- Nico Cristache [MSFT]Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no =rights.
"J=E9j=E9" wrote in message news:Oee5RpuqEHA.1712=@.tk2msftngp13.phx.gbl...
Hi,

I've a report with some queries =:
1. CLient infos (which return the =client name and other text info)
2. Orders
3. Historical sales

My query 1 and 2 are displayed in top =of my report.

My historical sales is displayed in 3 = charts
I want to group these charts in 1 =page (specially when exported in PDF), but these charts appears AFTER the "orders" query.
and I want to display the name of the =client in top of these 3 charts (because I can't put it in the header of my page)

I've setup a list around my query =3
I've setup the keeptogether option =for this list
My charts are in the list = I've added a textbox in the same list =with the formula:
=3DFirst(Fields!NomBT.Value, "ClientInfos")

When I display the result in PDF or =TIFF, the name of the client appear at the end of the first page and not in =top of the second page.
But I there is enough space in the =second page to display this name.
So why RS don't group the =content in my list?

I don't want to setup "page break =before" because if the user display the result other the web, I prefer to scroll the =page instead-of clicking "page 2"

Thanks.

Jerome.

---

--=_NextPart_000_00D2_01C4AADA.172F1310--|||This is a multi-part message in MIME format.
--=_NextPart_000_0032_01C4AAF9.4301DA30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I've also try this without more result.
I've this:
content from Query 1 & 2 (some textboxes and tables)
List (based on query 3)
--> Rectangle
--> Textbox (=3Dfirst...)
--> chart 1
--> chart 2
--> chart 3
--> end of rectangle
End of list
and my list continue to not fit in 1 page.
"Nico Cristache [MSFT]" <nipirvan@.microsoft.com> a =E9crit dans le =message de news:et1hETxqEHA.3428@.TK2MSFTNGP11.phx.gbl...
All charts and text boxes instances are part of the list, not only the =content of one group. You need to add a reactangle around your text box and chart.
-- Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no =rights.
"J=E9j=E9" <willgart@._A_hAotmail_A_.com> wrote in message =news:Oee5RpuqEHA.1712@.tk2msftngp13.phx.gbl...
Hi,
I've a report with some queries :
1. CLient infos (which return the client name and other text info)
2. Orders
3. Historical sales
My query 1 and 2 are displayed in top of my report.
My historical sales is displayed in 3 charts
I want to group these charts in 1 page (specially when exported in =PDF), but these charts appears AFTER the "orders" query.
and I want to display the name of the client in top of these 3 =charts (because I can't put it in the header of my page)
I've setup a list around my query 3
I've setup the keeptogether option for this list
My charts are in the list I've added a textbox in the same list with the formula:
=3DFirst(Fields!NomBT.Value, "ClientInfos")
When I display the result in PDF or TIFF, the name of the client =appear at the end of the first page and not in top of the second page.
But I there is enough space in the second page to display this name.
So why RS don't group the content in my list?
I don't want to setup "page break before" because if the user =display the result other the web, I prefer to scroll the page instead-of =clicking "page 2"
Thanks.
Jerome.
--=_NextPart_000_0032_01C4AAF9.4301DA30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I've also try this without more result.
I've this:
content from Query 1 & 2 (some =textboxes and tables)
List (based on query 3)
--> Rectangle
--> Textbox =(=3Dfirst...)
--> chart 1
--> chart 2
--> chart 3
--> end of rectangle
End of list
and my list continue to not fit in 1 page.
"Nico Cristache [MSFT]" =a =E9crit dans le message de news:et1hETxqEHA.3428=@.TK2MSFTNGP11.phx.gbl...
All charts and text boxes =instances are part of the list, not only the content of one group. = You need to add a reactangle around =your text box and chart.
-- Nico Cristache [MSFT]Microsoft SQL Server Reporting Services

This posting is provided "AS IS" with no warranties, and confers =no rights.


"J=E9j=E9" wrote in message news:Oee5RpuqEHA.1712=@.tk2msftngp13.phx.gbl...
Hi,

I've a report with some queries =:
1. CLient infos (which return the =client name and other text info)
2. Orders
3. Historical sales

My query 1 and 2 are displayed in =top of my report.

My historical sales is displayed in =3 charts
I want to group these charts in 1 =page (specially when exported in PDF), but these charts appears AFTER the = "orders" query.
and I want to display the name of =the client in top of these 3 charts (because I can't put it in the header of my page)

I've setup a list around my query 3
I've setup the keeptogether option =for this list
My charts are in the list = I've added a textbox in the same =list with the formula:
=3DFirst(Fields!NomBT.Value, "ClientInfos")

When I display the result in PDF or =TIFF, the name of the client appear at the end of the first page and not =in top of the second page.
But I there is enough space in the =second page to display this name.
So why RS don't group the =content in my list?

I don't want to setup "page break =before" because if the user display the result other the web, I prefer to =scroll the page instead-of clicking "page 2"

Thanks.

Jerome.

---

--=_NextPart_000_0032_01C4AAF9.4301DA30--

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 connection timeout when using full text indexing

After rebooting the computer (anytime), the first call to the database will almost always time out. This did not happen prior to adding the full text index to the database. It will time out even in the SQL Express Management Suite.

After that first time out, it works fine.

We tried a workaround by making our first call to the database a "dummy" call and wrapping it in a try/catch.

That didn't work either. Any suggestions?

Regards,

Pat

Someone suggested that your issue may be described in the KB, check it out here.

Mike

|||

The article you referred to is about querying the database. However, our problem is at connection time. When the appliction try to start a connection to the database after the SQL server machine reboot, it always timeout. We then try to make the connection again and it always works!

First of all, we didn't have this problem before we add Full-Text index to our database. The happens ONLY with SQL Express with the default security setup, which means the SQL Express instance is owned by "Network Services". When we make "Local System" to be the owner of the SQL Express instance, the problem goes away. This problem also NEVER happens on a server running the Full SQL Server version which default to use "Local System".

We could not find a way to default SQL Express to run on "Local System". If anyone knows of a way to do this, it will solve our problem too.

|||

Mike Wachal - MSFT wrote:

Someone suggested that your issue may be described in the KB, check it out here.

Mike

Thank you for your help, I solved my problem with sp_fulltext_service 'verify_signature', 0;