BUG? Extracting Data Stored in Multiple Languages

Hi,

I have an access database that is storing text in english and chinese characters. I am importing the data using SSIS and it is putting a 'square' character in place of all the chinese characters. Can I associate multiple code pages to a control? If this is not possible, I would just like to Extract the data and Load it into a table, and i will just handle the errors and write the key fields to an error log. The problem is that the error handler is not redirecting the row. It just simply fails. I have changed all the error actions to Redirect Row too. I currently have a script checking if the 'square' character exists but i feel this should be handled a better way? Has anyone run into this issue or have any suggestions?

This is the error code i get

Data conversion failed. The data conversion for column "FDCDOPS0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Access stores string data as Unicode. Is it possible to keep it that way? Where do you transfer data to? If you are moving it to SQL Server tables, is it possible to use Unicode string types (nchar/nvarchar), and avoid conversions?

Thanks.

|||

I am storing the field in question as nvarchar(256). I'm transferring data to a SQL Server 2005 database. I still have had no luck in solving the issue. Unicode is the answer i think. I am using a Data Source and Data Source View in SSIS. When I view the data in the data view the conversion has already taken place. Is there a way to set the field types in the data source or view?

Thanks

|||

It is not quite clear to me what you are doing. Could you explain what components you are using in your package or even better attach a simplified version of it.

Thanks.

sql

Bubble Chart

I would like to show static values on the Y-Axis of a bubble chart, say High,
Medium and Low. When I select the descriptive text in the dataset the
bubbles are not plotted correctly whilst if I retrieve the numeric
equivalent the bubbles are plotted the right way, but the Y-axis will show
numbers and not text.
Any help would be greatly appreciated.I too would like to do this and have not found any help so far. Has anyone
been successful in this? Or does anyone know of good documentation and
specific examples for the bubble chart?
"Adrian Theuma" wrote:
> I would like to show static values on the Y-Axis of a bubble chart, say High,
> Medium and Low. When I select the descriptive text in the dataset the
> bubbles are not plotted correctly whilst if I retrieve the numeric
> equivalent the bubbles are plotted the right way, but the Y-axis will show
> numbers and not text.
> Any help would be greatly appreciated.
>

Browser Based Report Builder

Hi,
I am looking at building a simple browser based report builder. I would like
to provide options to move fields and add text\captions to exsisting reports
using a browser(IE)
To startwith I am looking at displaying the fields(those are present in
report) in a grid(where the user could change the order of them) and have a
report preview pane(where the user could preview the report.
Could someone please let me know how I could have a preview pane in web page
and how I could make changes to the report as soon as a user changes the
fields order in grid.
Any help\suggestions on this would be greatly appreciated.
Thanks
PonnuIf you have SS2005 then dont reinvent the wheel, it is already there as
Report Model / Report Builder which i browser based.
Amarnath
"Ponnu" wrote:
> Hi,
> I am looking at building a simple browser based report builder. I would like
> to provide options to move fields and add text\captions to exsisting reports
> using a browser(IE)
> To startwith I am looking at displaying the fields(those are present in
> report) in a grid(where the user could change the order of them) and have a
> report preview pane(where the user could preview the report.
> Could someone please let me know how I could have a preview pane in web page
> and how I could make changes to the report as soon as a user changes the
> fields order in grid.
> Any help\suggestions on this would be greatly appreciated.
> Thanks
> Ponnu
>
>|||Hi Amarnath,
I am planning to do in SSRS 2005.
I could use ReportViewer for previewing reports but I need to change the
order of columns in the reoprt once the user changes the order in the grid.
Any ideas how I could programatically change the report from vb.net to
change the order of columns?
Thanks
Ponnu
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:8A4339DA-1E37-4959-8B3C-7DED2DDE4272@.microsoft.com...
> If you have SS2005 then dont reinvent the wheel, it is already there as
> Report Model / Report Builder which i browser based.
> Amarnath
> "Ponnu" wrote:
>> Hi,
>> I am looking at building a simple browser based report builder. I would
>> like
>> to provide options to move fields and add text\captions to exsisting
>> reports
>> using a browser(IE)
>> To startwith I am looking at displaying the fields(those are present in
>> report) in a grid(where the user could change the order of them) and have
>> a
>> report preview pane(where the user could preview the report.
>> Could someone please let me know how I could have a preview pane in web
>> page
>> and how I could make changes to the report as soon as a user changes the
>> fields order in grid.
>> Any help\suggestions on this would be greatly appreciated.
>> Thanks
>> Ponnu
>>

Bringing text into a database

I have a report that is generated out of another system, that is 180
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BCThe limit is 8000. Perhaps the wizard is the issue. Can you create the DTS
package manually? It may be that the input row contains a character that's
supposed to be used as a delimiter.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%237EjS3KPGHA.2668@.tk2msftngp13.phx.gbl...
I have a report that is generated out of another system, that is 180
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BC|||Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the D
TS
> package manually? It may be that the input row contains a character that'
s
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC|||CRLF is the standard row terminator. You may need a hex editor to track
down exactly where it is.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23RijHHLPGHA.1760@.TK2MSFTNGP10.phx.gbl...
Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the
> DTS
> package manually? It may be that the input row contains a character
> that's
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC

Breaking out data from a text field type

In my database there is a text field type that is used to enter street
address. This address could be a few lines long, each line with a
carriage return at the end.
Is there a way to search for these carriage returns and break out what
is in each line seperately?

Thanks.
Mike[posted and mailed, please reply in news]

Mike (mrea@.ohiotravelbag.com) writes:
> In my database there is a text field type that is used to enter street
> address. This address could be a few lines long, each line with a
> carriage return at the end.
> Is there a way to search for these carriage returns and break out what
> is in each line seperately?

Is that really the datatype text? That seems a bit over kill for a street
address. They would very rarely be over 8000 bytes. Or even 4000 if you
are using varchar.

The functions to use are substring and charindex. And char(13) for the
CRs. Or char(13) + char(10) if it's actually CR + LF. charindex does not
handle text beyond the varchar limit, but I don't think this would be
an issue.

You could also do:

SELECT @.adr = adr FROM tbl WHERE ..
SELECT str
FROM iter_charlist_to_table(@.adr, char(13))
ORDER BY listpos

You find this function on
http://www.sommarskog.se/arrays-in-...list-of-strings

Note that if you need to use char(13) + char(10) as delimiter, you
will have to change the function. (And not only the length of delimiter.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Breaking data into 1500 byte chunks

Hi,

I have a text file (5 MB). It appears as a single line in a text editor. But actually it has records of 1500 byte length each.

I want to strip it down to 1500 byte records. So 1500*3500 = 5 MB (approx). The record size is always 1500 bytes.

Does anyone have a script that I can run on this file to achieve this break.

ThanksYou have 1 row of 5,000,000 bytes?

There is no CR LF chars?

Are there any delimiters?|||No there are no delimiters. All I know is they are 1500 byte records. Each record has fixed column length. But all on a single line.|||Where did the file come from?

An lrecl of 5mb?

Tell them to fix the file...

I guess you would need to write a pearl script or something...

Breaking apart Column into rows

I have a column that has text delimited by a percent sign that I wish
to turn into rows.
Example:
A column contains ROBERT%CAMARDA, I want to turn that into two rows,
one row with ROBERT and antoher row with CAMARDA.
I will have source rows that have zero, one, or many percent sign
delimiters that will correspond to that many rows (One percent sign
will create 2 rows, 2 percent signs will create 3 rows and so forth).

Any thoughts?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> I have a column that has text delimited by a percent sign that I wish
> to turn into rows.
> Example:
> A column contains ROBERT%CAMARDA, I want to turn that into two rows,
> one row with ROBERT and antoher row with CAMARDA.
> I will have source rows that have zero, one, or many percent sign
> delimiters that will correspond to that many rows (One percent sign
> will create 2 rows, 2 percent signs will create 3 rows and so forth).

Have a look at an article on my web site:
http://www.sommarskog.se/arrays-in-sql.html.

Specifically, look at "List-of-strings" and "Unpacking a table column".

You did not say which version of SQL Server you are using. The article
is written for SQL 2000. SQL 2005 provides a new operator CROSS APPLY
which is good for the column bit. In SQL 2000, you will have to run
it row by row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

bottom to top formating

hi
i had luck formatering a text box top to botto in formatting:
t
e
s
t
i
n
g
but i would like bottom to top and i cant seem to find it
g
n
i
t
s
e
t
any ideas ?On Oct 5, 5:05 pm, "michael vardinghus" <xx> wrote:
> hi
> i had luck formatering a text box top to botto in formatting:
> t
> e
> s
> t
> i
> n
> g
> but i would like bottom to top and i cant seem to find it
> g
> n
> i
> t
> s
> e
> t
> any ideas ?
You might be able to use the StrReplace function (i.e., an expression
like):
=StrReplace(Fields!SomeField.Value)
If this doesn't work, you may need to create a single-column table
control and populate it from a dataset that is custom sorted before
getting to the report (in the stored procedure/query that sources the
report).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Oct 7, 1:32 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Oct 5, 5:05 pm, "michael vardinghus" <xx> wrote:
>
> > hi
> > i had luck formatering a text box top to botto in formatting:
> > t
> > e
> > s
> > t
> > i
> > n
> > g
> > but i would like bottom to top and i cant seem to find it
> > g
> > n
> > i
> > t
> > s
> > e
> > t
> > any ideas ?
> You might be able to use the StrReplace function (i.e., an expression
> like):
> =StrReplace(Fields!SomeField.Value)
> If this doesn't work, you may need to create a single-column table
> control and populate it from a dataset that is custom sorted before
> getting to the report (in the stored procedure/query that sources the
> report).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I think you might mean the StrRev() function. (String Reverse)
Because essentially, the formatting is correct, you just want the
letters to appear in reverse order.
- Jason|||On Oct 8, 12:32 pm, Jason <flaja...@.gmail.com> wrote:
> On Oct 7, 1:32 pm, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Oct 5, 5:05 pm, "michael vardinghus" <xx> wrote:
> > > hi
> > > i had luck formatering a text box top to botto in formatting:
> > > t
> > > e
> > > s
> > > t
> > > i
> > > n
> > > g
> > > but i would like bottom to top and i cant seem to find it
> > > g
> > > n
> > > i
> > > t
> > > s
> > > e
> > > t
> > > any ideas ?
> > You might be able to use the StrReplace function (i.e., an expression
> > like):
> > =StrReplace(Fields!SomeField.Value)
> > If this doesn't work, you may need to create a single-column table
> > control and populate it from a dataset that is custom sorted before
> > getting to the report (in the stored procedure/query that sources the
> > report).
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> I think you might mean the StrRev() function. (String Reverse)
> Because essentially, the formatting is correct, you just want the
> letters to appear in reverse order.
> - Jason
Whoops, sorry I got the function name wrong too. Must be contagious.
It is actually StrReverse
(strrev is correct for PHP, not VBScript. I deal with too many
languages)
Or if you want you can also reverse it in the SQL query if you don't
want to do an expression with the T-SQL REVERSE function.
- Jason|||On Oct 8, 11:36 am, Jason <flaja...@.gmail.com> wrote:
> On Oct 8, 12:32 pm, Jason <flaja...@.gmail.com> wrote:
>
> > On Oct 7, 1:32 pm, EMartinez <emartinez...@.gmail.com> wrote:
> > > On Oct 5, 5:05 pm, "michael vardinghus" <xx> wrote:
> > > > hi
> > > > i had luck formatering a text box top to botto in formatting:
> > > > t
> > > > e
> > > > s
> > > > t
> > > > i
> > > > n
> > > > g
> > > > but i would like bottom to top and i cant seem to find it
> > > > g
> > > > n
> > > > i
> > > > t
> > > > s
> > > > e
> > > > t
> > > > any ideas ?
> > > You might be able to use the StrReplace function (i.e., an expression
> > > like):
> > > =StrReplace(Fields!SomeField.Value)
> > > If this doesn't work, you may need to create a single-column table
> > > control and populate it from a dataset that is custom sorted before
> > > getting to the report (in the stored procedure/query that sources the
> > > report).
> > > Hope this helps.
> > > Regards,
> > > Enrique Martinez
> > > Sr. Software Consultant
> > I think you might mean the StrRev() function. (String Reverse)
> > Because essentially, the formatting is correct, you just want the
> > letters to appear in reverse order.
> > - Jason
> Whoops, sorry I got the function name wrong too. Must be contagious.
> It is actually StrReverse
> (strrev is correct for PHP, not VBScript. I deal with too many
> languages)
> Or if you want you can also reverse it in the SQL query if you don't
> want to do an expression with the T-SQL REVERSE function.
> - Jason
Oops, sorry about that, that's what I meant. Thanks for catching that
Jason.
Regards,
Enrique Martinez
Sr. Software Consultant

Border around group of textboxes

Hi- I'm trying to enclose a group of textboxes by a border, essentially a VB
groupbox or panel. I've tried using both a rectangle and text box object with
a border to accomplish this. When you preview it looks right; however, when
the report is rendered, the html generates a separate table with a border
then all the textboxes below it. How can I get the html to render properly?
ThanksHTML does not support overlapping reportitems, they will get pushed around.
It sounds like you don't want overlapping reportitems, but you want the
textboxes being contained inside the rectangle.
You can achieve this by using drag-and-drop in report designer to drop the
textboxes into the rectangle - rather than having them overlapping. If a
textbox is contained inside a rectangle and you try to move the textbox with
the mouse, you will notice that the rectangle determines the boundaries for
moving the textbox (unless you move the textbox completely out of the
rectangle).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tony Ballman" <TonyBallman@.discussions.microsoft.com> wrote in message
news:59DF8C12-2087-4BF1-9579-C44F6DAE738D@.microsoft.com...
> Hi- I'm trying to enclose a group of textboxes by a border, essentially a
VB
> groupbox or panel. I've tried using both a rectangle and text box object
with
> a border to accomplish this. When you preview it looks right; however,
when
> the report is rendered, the html generates a separate table with a border
> then all the textboxes below it. How can I get the html to render
properly?
> Thanks
>|||Thanks that worked. Now I see I made the mistake of placing a new rectangle
over-top the txtboxes I wanted enclosed, so the textboxes arenâ't referencing
the new parent rectangle.
"Robert Bruckner [MSFT]" wrote:
> HTML does not support overlapping reportitems, they will get pushed around.
> It sounds like you don't want overlapping reportitems, but you want the
> textboxes being contained inside the rectangle.
> You can achieve this by using drag-and-drop in report designer to drop the
> textboxes into the rectangle - rather than having them overlapping. If a
> textbox is contained inside a rectangle and you try to move the textbox with
> the mouse, you will notice that the rectangle determines the boundaries for
> moving the textbox (unless you move the textbox completely out of the
> rectangle).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tony Ballman" <TonyBallman@.discussions.microsoft.com> wrote in message
> news:59DF8C12-2087-4BF1-9579-C44F6DAE738D@.microsoft.com...
> > Hi- I'm trying to enclose a group of textboxes by a border, essentially a
> VB
> > groupbox or panel. I've tried using both a rectangle and text box object
> with
> > a border to accomplish this. When you preview it looks right; however,
> when
> > the report is rendered, the html generates a separate table with a border
> > then all the textboxes below it. How can I get the html to render
> properly?
> > Thanks
> >
>
>