Asian characters converted to ? in ntext field

I have an ntext field labeled answer in one of my tables.
I had an error in one of my applications where the following sqlstatement
was executed:
INSERT INTO tblRESPONSE(ANSWER) VALUES('AsianCharactersHere')
The correct statment would have included the leadning "N" as follows:
INSERT INTO tblRESPONSE(ANSWER) VALUES(N'AsianCharactersHere')
This error resulted in question marks being inserted into the database
instead of the asian characters.
Is there *any* way to retrieve the asian characters? It appears that each
of the characters was truncated which is where the question marks come from.
I tried to use lumigent log explorer to recreate the erroneous rows from the
logs, but without success. Is the original insert command stored somewhere?
Any help would be greatly appreciated.I just talked to microsoft sqlserver tech support. If you use the default
character set, there is no way to retrieve the data. The text is converted
to the question mark characters before it is recorded anywhere in the sql
engine.
"PJBerry" wrote:
> I have an ntext field labeled answer in one of my tables.
> I had an error in one of my applications where the following sqlstatement
> was executed:
> INSERT INTO tblRESPONSE(ANSWER) VALUES('AsianCharactersHere')
> The correct statment would have included the leadning "N" as follows:
> INSERT INTO tblRESPONSE(ANSWER) VALUES(N'AsianCharactersHere')
> This error resulted in question marks being inserted into the database
> instead of the asian characters.
> Is there *any* way to retrieve the asian characters? It appears that each
> of the characters was truncated which is where the question marks come from.
> I tried to use lumigent log explorer to recreate the erroneous rows from the
> logs, but without success. Is the original insert command stored somewhere?
> Any help would be greatly appreciated.
>

AS400/DB2 different column names in different configurations

Hello!

I am creating a package that will be executed against different AS400/DB2 databases. (I use a Foreach Loop). The AS400/DB2 is configured a little bit differently. This results in that the same column can have different name in different AS400/DB2. For example the column nameA4￡ADG, A4$ADG and A4?ADG is the same column.

In the Data Flow I use DataReader. Does anyone know how to get past this issue without creating one package for each column name?

Best regard

Tina

I'm not an AS400 person (or DB2), so I have no idea if this will work. But, it seems like you could use an expression to set the SQL Statement on the DataReader source, and alias the differently named columns to the same name. To set an expression on the data reader, you have to select the data flow task in the control flow, go to properties, and go to expressions.|||

Hello!

Thank you for your help!

But know I have solved it. I use different Data Flow for each set of column names. Depending on the company that is executed I defined the Precedence Constraint to "Expression and Constraint" and use the Expression:

@.[User::varCompanyName]=="CDHU"

In the Data Reader I have to set ValidateExternalMetadata to False

Best regards,

Tina

Are there any MS SQL operations that are not transactional ?

I pretty new to MS SQL.
Long ago I heard that some MS SQL data definition operations are not
transactional.
Executed in the transaction's boundaries their results persists even if
transaction rolls back.
Could anyone ellaborat on that subject ?
Thank you.Table variables are non-transaction, that is to say, they aren't affected by
transaction scope - there is still logging on the transaction log.
declare @.tb table ( mycol int )
insert @.tb values( 1 )
begin tran
update @.tb set mycol = mycol + 1
rollback tran
select * from @.tb
The above returns 2, if you use a temporary table or permanent table you
will get 1...
create table #tb ( mycol int )
insert #tb values( 1 )
begin tran
update #tb set mycol = mycol + 1
rollback tran
select * from #tb
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||TRUNCATE table.
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||That's incorrect. TRUNCATE participates in a transaction like any other
DML operation (table variables excepted).
David Portas
SQL Server MVP
--|||> TRUNCATE table.
?
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
ROLLBACK
SELECT Col1 FROM MyTable
DROP TABLE MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:%23LIQ8RM0FHA.1192@.TK2MSFTNGP10.phx.gbl...
> TRUNCATE table.
> "Marek" <nospam@.nowhere.com> wrote in message
> news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>|||You can have non transaction DDL inside a transaction if you use xp_cmdshell
and osql to execute the DDL. Extended stored procedures are not included in
a transaction. But if you are looking for a bug or "feature" where plain DDL
(table variables excepted as mentioned in the other posts) is not
transactional, there is none I am aware of in SQL Server 2000, although
there might have been in long ago versions like 6.0 or 6.5.
Jacco Schalkwijk
SQL Server MVP
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||I stand corrected.
"JT" <someone@.microsoft.com> wrote in message
news:%23LIQ8RM0FHA.1192@.TK2MSFTNGP10.phx.gbl...
> TRUNCATE table.
> "Marek" <nospam@.nowhere.com> wrote in message
> news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>