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

BUG - SQL Agent Jobs - SP3 - Multiple Steps

I have come across what seems to be a bug in SQL 2K SP3.
When I have a job with multiple job steps and I right-click to choose which step I want to start with, the job steps are in no particular order.
I am also using an ActiveX script that uses SQLDMO to script out the jobs to a text file. When the job is scripted, it puts the job steps in what seems like alphabetical order by step name and not in step number order. If you try to run this script to c
reate the job, it will fail because it requires the job steps to be in step number order.
I have talked to others that have used this script prior to SP3 so it seems to only have occurred in this Service Pack.
I just wanted to get this information out there so it can be addressed and corrected in either a patch or at least the next SP.
This is indeed a known bug for SQL2000. Its because sp_help_jobstep doesn't
contain an order by when returning results IIRC. Generally you get the right
result but without the order by its not guaranteed
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hawg" <anonymous@.discussions.microsoft.com> wrote in message
news:6115B24E-5496-49CB-ABCC-864C14E56DC1@.microsoft.com...
> I have come across what seems to be a bug in SQL 2K SP3.
> When I have a job with multiple job steps and I right-click to choose
which step I want to start with, the job steps are in no particular order.
> I am also using an ActiveX script that uses SQLDMO to script out the jobs
to a text file. When the job is scripted, it puts the job steps in what
seems like alphabetical order by step name and not in step number order. If
you try to run this script to create the job, it will fail because it
requires the job steps to be in step number order.
> I have talked to others that have used this script prior to SP3 so it
seems to only have occurred in this Service Pack.
> I just wanted to get this information out there so it can be addressed and
corrected in either a patch or at least the next SP.

BUG - SQL Agent Jobs - SP3 - Multiple Steps

I have come across what seems to be a bug in SQL 2K SP3.
When I have a job with multiple job steps and I right-click to choose which
step I want to start with, the job steps are in no particular order.
I am also using an ActiveX script that uses SQLDMO to script out the jobs to
a text file. When the job is scripted, it puts the job steps in what seems
like alphabetical order by step name and not in step number order. If you
try to run this script to c
reate the job, it will fail because it requires the job steps to be in step
number order.
I have talked to others that have used this script prior to SP3 so it seems
to only have occurred in this Service Pack.
I just wanted to get this information out there so it can be addressed and c
orrected in either a patch or at least the next SP.This is indeed a known bug for SQL2000. Its because sp_help_jobstep doesn't
contain an order by when returning results IIRC. Generally you get the right
result but without the order by its not guaranteed
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hawg" <anonymous@.discussions.microsoft.com> wrote in message
news:6115B24E-5496-49CB-ABCC-864C14E56DC1@.microsoft.com...
> I have come across what seems to be a bug in SQL 2K SP3.
> When I have a job with multiple job steps and I right-click to choose
which step I want to start with, the job steps are in no particular order.
> I am also using an ActiveX script that uses SQLDMO to script out the jobs
to a text file. When the job is scripted, it puts the job steps in what
seems like alphabetical order by step name and not in step number order. If
you try to run this script to create the job, it will fail because it
requires the job steps to be in step number order.
> I have talked to others that have used this script prior to SP3 so it
seems to only have occurred in this Service Pack.
> I just wanted to get this information out there so it can be addressed and
corrected in either a patch or at least the next SP.

Breaking up a string column into multiple records

Hopefully someone can help me. I have a table of records with the following fields:

AnswerID (int)
MultipleChoiceMultipleAnswer (varchar)
QuestionID (int)

now the multipleChoiceMultipleAnswer field data is in the following format:

1234,5678,99867
2345,456,7891

I want to break that field up into multiple records using the same AnswerID and QuestionID for each new multiple choice answer, so it would look like this:

10001 1234 9999
10001 5678 9999
10001 99867 9999
10002 2345 9998
10002 456 9998
10002 7891 9998

Is there an optimized method of doing this without using a cursor to iterate through each record?

Any help would be greatly appreciated.

ThanksLook at the following link - if you need something else, let me know -

link (http://dbforums.com/showthread.php?threadid=586248)

Breaking a row of data into multiple CSV rows

Does anyone have a routine that takes a row of data from database, duplicates/triplicates it, appends some information to it and writes it out as 2/3 CSV rows.

Basically I need to do the following.

Selected from database:
Row1 Col1 Col2 Col3

Output:
Row1 Col1, Col2, Col3, abc
Row1 Col1, Col2, Col3, def
Row1 Col1, Col2, Col3, ghi

Thank youThis might help with part of it:

SELECT '"' + Row1 + '","' + Col1 + '","' + Col2 + '","' + Col3 + + '";'|||You could do a SELECT INTO 3 times for each "row" in the original table. It would be like:

SELECT INTO newtable
Col1, Col2, Col3, 'abc' AS Col4
FROM oldtable
WHERE Row=1

SELECT INTO newtable
Col1, Col2, Col3, 'def' AS Col4
FROM oldtable
WHERE Row=1

SELECT INTO newtable
Col1, Col2, Col3, 'ghi' AS Col4
FROM oldtable
WHERE Row=1

Then you would convert newtable to CSV afterward (with BCP maybe).
That's assuming there is a column named Row with a value of 1. But if you have a key column ( a field with unique values), the new table can't use that as a key column because you'll be making three copies of each row.
But since your example wasn't very specific, either is the answer.

Another method is to use a cursor and process each row.|||I think using the cursor would be a better option since the number of rows that i need to "break" are in thousands.

I am just not sure how to use cursor.|||Have a look in BOL (Books online) to see how cursors, but be warned that cursors can be inefficient, particularly with a 'large' number of rows - some people on this forum positively detest them!
Think about using temporary tables/table variables instead.

Lempster|||Funny you should ask

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_CSVTable]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_CSVTable]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION udf_CSVTable(@.Str varchar(7000))
RETURNS @.t table (numberval int, stringval varchar(100), DateVal datetime)
AS
BEGIN

DECLARE @.i int, @.c varchar(100)

SELECT @.Str = @.Str + ',', @.i = 1, @.c = ''

WHILE @.i <= len(@.Str)
BEGIN
IF substring(@.Str,@.i,1) = ','
BEGIN
INSERT INTO @.t(numberval, stringval, DateVal)
VALUES ( CASE WHEN isnumeric(@.c)=1 THEN @.c else Null END
, rtrim(ltrim(@.c))
, CASE WHEN isdate(@.c)=1 then @.c else Null END)
SET @.c = ''
END
ELSE
SET @.c = @.c + SUBSTRING(@.Str,@.i,1)
SET @.i = @.i +1
END
RETURN
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Break Apart Data Using While Loop

I need to break apart the following data into multiple records but I am not
sure how to write the code. The record identifier is the ;
RecordID DataInfo
1 "abc", "def"; "ghi", "jkl"
2 "abc", "def", "ghi"; "jkl", "mno"
Data in a new table will be
RecordID DataInfo
1 "abc", "def"
1 "ghi", "jkl"
2 "abc", "def", "ghi"
2 "jkl", "mno"
Thanks for the help!If the delimiter ; only appears once in the DataInfo column you could
do something like this...
Create Table #TableA
(
Record Integer,
DataInfo varchar(50)
)
Insert Into #TableA
Select 1, '"abc", "def"; "ghi", "jkl"'
Union
Select 2, '"abc", "def", "ghi"; "jkl", "mno" '
Create Table #TableB
(
Record Integer,
DateInfo Varchar(50)
)
Insert Into #TableB
Select Record, Replace((Ltrim(Rtrim(Left(DataInfo,
(CharIndex(';',DataInfo)))))), ';','')
>From #TableA
Union
Select Record, Replace((Ltrim(Rtrim(Right(DataInfo,
(CharIndex(';',Reverse(DataInfo))))))), ';','')
>From #TableA
Select * From #TableA
Select * From #TableB
Drop Table #TableA
Drop Table #TableB
HTH
Barry|||Sorry my example wasn't specific enough. The delimiter can appear multiple
times in the column.
"Barry" wrote:

> If the delimiter ; only appears once in the DataInfo column you could
> do something like this...
>
> Create Table #TableA
> (
> Record Integer,
> DataInfo varchar(50)
> )
> Insert Into #TableA
> Select 1, '"abc", "def"; "ghi", "jkl"'
> Union
> Select 2, '"abc", "def", "ghi"; "jkl", "mno" '
> Create Table #TableB
> (
> Record Integer,
> DateInfo Varchar(50)
> )
>
> Insert Into #TableB
> Select Record, Replace((Ltrim(Rtrim(Left(DataInfo,
> (CharIndex(';',DataInfo)))))), ';','')
> Union
> Select Record, Replace((Ltrim(Rtrim(Right(DataInfo,
> (CharIndex(';',Reverse(DataInfo))))))), ';','')
>
> Select * From #TableA
> Select * From #TableB
> Drop Table #TableA
> Drop Table #TableB
>
> HTH
> Barry
>|||
As an alternative you can use a numbers table
as in http://www.aspfaq.com/show.asp?id=2516
and can do this
select Record,
ltrim(substring(DataInfo,
Number,
charindex(';',
DataInfo + ';',
Number) - Number)) as DataInfo
from #TableA
inner join Numbers on Number between 1 and len(DataInfo) + 1
and substring(';' + DataInfo, Number, 1) = ';'|||Or regular expressions...
http://www.sqlservercentral.com/col...oolkitpart2.asp
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:37F08AA1-FC30-42C9-B50F-50B0FA9FD7B5@.microsoft.com...
>I need to break apart the following data into multiple records but I am not
> sure how to write the code. The record identifier is the ;
> RecordID DataInfo
> 1 "abc", "def"; "ghi", "jkl"
> 2 "abc", "def", "ghi"; "jkl", "mno"
> Data in a new table will be
> RecordID DataInfo
> 1 "abc", "def"
> 1 "ghi", "jkl"
> 2 "abc", "def", "ghi"
> 2 "jkl", "mno"
> Thanks for the help!
>

BPA Feedback and Question

I've been exploring the best practices tool and find most things great! I have installed it on multiple machines and then changed the database that each client logs into to be the same one so that we have one central repository and it seems to be working
well. I would like to see a way to schedule a job that would run the scans at off hours since we are trying to make it mandatory for our developers to run a clean scan before they move out of our development environment. I was also surprised when i not
iced that there were no stored procedures in the slqbpa database, isn't that going against best practices ;)
Debi
In the released version there will be a command line version that will let
you use sql agent or some other scheduler to do what you want.
About stored procedures... not that strictly a best practice... depends on
your application or on what you're trying to do. The extensible architecture
of bpa called for some adhoc query building which may be better in the app
than doing dynamic sql inside stored procs. That said, I would like to see
some of the logic moved to stored procs, post v1. In the pre-7.0 days,
stored procs were way more a perf best practice.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debi" <anonymous@.discussions.microsoft.com> wrote in message
news:B924F619-4B1C-4657-B61B-41DA672541CE@.microsoft.com...
> I've been exploring the best practices tool and find most things great! I
have installed it on multiple machines and then changed the database that
each client logs into to be the same one so that we have one central
repository and it seems to be working well. I would like to see a way to
schedule a job that would run the scans at off hours since we are trying to
make it mandatory for our developers to run a clean scan before they move
out of our development environment. I was also surprised when i noticed
that there were no stored procedures in the slqbpa database, isn't that
going against best practices ;)
|||Hello Debi,
how did you manage this?
I tried to install BPA on different machines...
but the tool doesn't support the same database for the different
installations.
did you install BPA on different machines with different databases?
what did you do with these databases - drop them?
how did you change the database to connect to?
Thanks Thomas
"Debi" wrote:

> I've been exploring the best practices tool and find most things great! I have installed it on multiple machines and then changed the database that each client logs into to be the same one so that we have one central repository and it seems to be worki
ng well. I would like to see a way to schedule a job that would run the scans at off hours since we are trying to make it mandatory for our developers to run a clean scan before they move out of our development environment. I was also surprised when i n
oticed that there were no stored procedures in the slqbpa database, isn't that going against best practices ;)
|||Have a look at http://www.sqldbatips.com/showarticle.asp?ID=9
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mueller-Lynch Thomas" <MuellerLynchThomas@.discussions.microsoft.com> wrote
in message news:1EBAEBEF-38D1-471A-B277-B685AE3EB4BE@.microsoft.com...[vbcol=seagreen]
> Hello Debi,
> how did you manage this?
> I tried to install BPA on different machines...
> but the tool doesn't support the same database for the different
> installations.
> did you install BPA on different machines with different databases?
> what did you do with these databases - drop them?
> how did you change the database to connect to?
> Thanks Thomas
> "Debi" wrote: