Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Why does an insert from a #table show '\x0d' at the end of one column.
I had a situation where a '\x0d' charater was appearing (appended) to the data in a single column.
Scenario:
In a stored procedure, data is retrieved and a #table is used for data manipulation.
At the end of the proc, the data is inserted into a permenant table, selecting from the #table.
However, in 1 column (not all) a carriage return, '\x0d', was appended to the data.
this was not in the #table and I could not see why this was happening.
I recompiled the permenant table and the error disappeared.
My concern is this is a production procedure & table, and it is not feasible to re-create the table and rerun the procedure every time (if) it reappears.
Any experience of this issue?
Any theories??
TIA
Scenario:
In a stored procedure, data is retrieved and a #table is used for data manipulation.
At the end of the proc, the data is inserted into a permenant table, selecting from the #table.
However, in 1 column (not all) a carriage return, '\x0d', was appended to the data.
this was not in the #table and I could not see why this was happening.
I recompiled the permenant table and the error disappeared.
My concern is this is a production procedure & table, and it is not feasible to re-create the table and rerun the procedure every time (if) it reappears.
Any experience of this issue?
Any theories??
TIA
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Exactly the reason for my question. I have never seen this before either. (after nearly 20 years working with ASE)
The '\x0d' character was not in the source data. (checked)
It was also not in the #table. I debugged the stored procedure and printed / saved the data. (Not my first time on the carrossel)
'recompile' was incorrect.
I Dropped and (re)Created the permenant table.
My inital thought was that it had something to do with Varchar() datatype. That was my reason for re-creating the table with a different, shorter, varchar length. This resolved the issue.
Then I reverted to the original, longer, length but the error did not re-appear.
I am working on ASE 15.7 server.
I can't print the code in this forum - unfortunately.
The '\x0d' character was not in the source data. (checked)
It was also not in the #table. I debugged the stored procedure and printed / saved the data. (Not my first time on the carrossel)
'recompile' was incorrect.
I Dropped and (re)Created the permenant table.
My inital thought was that it had something to do with Varchar() datatype. That was my reason for re-creating the table with a different, shorter, varchar length. This resolved the issue.
Then I reverted to the original, longer, length but the error did not re-appear.
I am working on ASE 15.7 server.
I can't print the code in this forum - unfortunately.
I've added Sybase Database topic to the question. Sybase experts could tell whether this happened to them already...
My explanation is still the same: 1) trigger or 2) engine behavior anomaly which is not obvious.
I would not expect such behavior in SQL Server or in Oracle. I would accept (or expect rare occurrence - 1:1000000) such anomaly in MS Access or in another low-cost engine running under Windows. I am not using Sybase ASE at all.
If you are sure this was not caused by a trigger created on the table for a fun or as a joke then I am sure this problem does not have simple solution most probably.
My explanation is still the same: 1) trigger or 2) engine behavior anomaly which is not obvious.
I would not expect such behavior in SQL Server or in Oracle. I would accept (or expect rare occurrence - 1:1000000) such anomaly in MS Access or in another low-cost engine running under Windows. I am not using Sybase ASE at all.
If you are sure this was not caused by a trigger created on the table for a fun or as a joke then I am sure this problem does not have simple solution most probably.
what is source of this data, loading from flat-files or csv?
or you are using the SQL loaders or something. if yes. there might be issue with source date or control file issue.
you can replicate this in pre-prod or lower environments with same data.
if the source data is file or external table etc. you can easly replicate.
if its the source from the different tables you can do export and import the tables in lower environments and test.
or you are using the SQL loaders or something. if yes. there might be issue with source date or control file issue.
you can replicate this in pre-prod or lower environments with same data.
if the source data is file or external table etc. you can easly replicate.
if its the source from the different tables you can do export and import the tables in lower environments and test.
thank you pcelba for adding the 'Sybase database' tag on the question.
There are no triggers on the table - it is a very simple process and no historical or dependent data held.
I think as you say 'engine behavior anomaly which is not obvious' may be the correct assumption.
Ganesh, it was an interesting point that this error occurred on both test and production environments.
Even with a change of source data (a newer version) the error re-occured.
Finally, I will have to monitor it. It is resolved - for now - but there is no guarantee it will not reoccur, without knowing the cause.
Many thanks for the suggestions.
There are no triggers on the table - it is a very simple process and no historical or dependent data held.
I think as you say 'engine behavior anomaly which is not obvious' may be the correct assumption.
Ganesh, it was an interesting point that this error occurred on both test and production environments.
Even with a change of source data (a newer version) the error re-occured.
Finally, I will have to monitor it. It is resolved - for now - but there is no guarantee it will not reoccur, without knowing the cause.
Many thanks for the suggestions.
I dropped and re-created the table.
As it does not hold historical data, this was an option.
When the procedure, that populated the table, was re-run, the error did not re-occur.
After serveral re-runs of the procedure, on different environments, the data is "clean"
As it does not hold historical data, this was an option.
When the procedure, that populated the table, was re-run, the error did not re-occur.
After serveral re-runs of the procedure, on different environments, the data is "clean"
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
How can you tell "this was not in the #table"? Did you print the data? Did you save the data somewhere? The #table is not accessible outside the SP...
I also don't understand the sentence "I recompiled the permenant table" ... What database engine allows to recompile tables?
So please post the SP code, the SQL engine used and we may tell where is the problem.
Without additional info I can only state the '\x0d' charater was in original data or it was added by some trigger. It cannot appear from nothing.