Importing Carriage Returns - Now LOST DATA

Jun 6, 2007

I am migrating from 2000 to 2005 and in the process of rewriting DTS to SSIS. So far so good.

I have an import of a flat file that I need to reproduce that works fine in DTS but throws SSIS.

The file contains details of products, and has a Long Description field that may contain lots of different chars - CR & LF amongst them. It's source and destination columns are Text type.

The file is tab delimited, text delimited with double quotes, with row delimiters of CRLF and column names on row 1.

The error SSIS throws is "The column delimiter for column "LongDescription" was not found"

I imagine (perhaps wrongly) that this is because of the extra CRLFs but it worked fine in DTS. It also previews fine, and lets me define the column properties OK in SSIS package designer.

Any help greatly appreciated. I am trying to avoid the obvious thing of replacing those chars in that field at this point as currently the export is shared between a live production server, and my test 2005 rig.

Thanks v much.

Chris.

View 1 Replies


ADVERTISEMENT

Keep Carriage Returns?

Oct 2, 2003

hello,

could any one tell how to go about keeping carriage returns and line breaks when inserting a chunk of text so that when that text is recieved it will still contain those carriage returns and line breaks?

also, what datatype would be most appropriate?

thakyou,
zaffi.

View 2 Replies View Related

Pasting Carriage Returns

May 20, 2008

I'm using Microsoft SQL 2005.

I am attempting to store text in a column defined as varchar(2048). The text contains line feed and carriage return characters. After examining the database column the text is truncated after the first set of line feed and carriage return characters.

Is there a way to force SQL Server to store these characters?

Much Thanks, Robert

View 4 Replies View Related

Finding Embedded Carriage Returns

Aug 3, 1998

What is the best way to query for columns which contain embedded carriage returns?

View 2 Replies View Related

Detecting Carriage Returns In A Column

Jun 12, 2006

Can anyone provide me with some SQL that will identify rows from a table where a varchar column named "Notes" contain Carriage Returns?

I know that with report writer SQR I can translate CR's to white space but I do not know of any Sybase function that will allow me to do the same, any ideas on this well would be appreciated.

View 3 Replies View Related

Export To MS Excel Carriage Returns

Jan 23, 2007

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

View 5 Replies View Related

SQL INSERT Changes Carriage Returns Into Question Marks???

Jan 8, 2008

Hi all,I have an old ASP .NET 1.1 application that I haven't had time to rebuild with .NET 2.0. No changes have been made to the application or the SQL Server the application uses. I have a web form where users can type multiple lines of text, and it is entered into an SQL database (SQL 2000 Enterprise) into a column with a datatype of "text". Recently (it seems out of nowhere), If my users enter a carriage return into the webform, it becomes a question mark in the sql database. It's really a bit funny, but also annoying, lol. Does anyone have any idea why this might be happening? Thanks so much! 

View 3 Replies View Related

How Do You Format A Column Heading To Use Carriage Returns?

Mar 12, 2008



In SSRS/RDL, How do you format a column heading to use carriage returns?

I have a couple of instance where I have a column heading that I want spread over 3 lines. For example, the column heading "= Actual Amount" I would want centered and displayed on 3 lines, as follows:

=
Actual
Amount

View 4 Replies View Related

How Do I Exclude Carriage Returns As Part Of A IS NULL Exclusion Clause...

Jan 6, 2005

Hi

I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.

I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.

Does anyone know how I can elegantly get around this problem other than my best guess below:

Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.

Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]


Cheers - Andy

View 4 Replies View Related

Integration Services :: Dealing With Carriage Returns Within Flat File Source Fields

Apr 2, 2015

I'm trying to import a flat file source into a SQL Server table.

The flat file is pipe-delimited and text qualified with " (double-quotes).

The import job is failing because there is a "comments" field in the flat file and there are carriage returns within some records in the "comments" field. When SSIS encounters a record with a carriage return within that field, it sees the carriage return and assumes the end of the record, even though the field is text qualified with " ".

The actual error message I see is: "

Error 0xc0202055: Data Flow Task 1: The column delimiter for column "comments" was not found.
(SQL Server Import and Export Wizard)

Sample Record:
"418186"|"94"|"Staff Only-Minimum charge out of 3 hours
Plus travel & accommodation costs (if required) – at cost.

All trauma response services & associated fees/costs are required to be formally authorised by the Company prior to delivery."|""|"690"|""

I can't think of a way to get SSIS to ignore the carriage returns within the Comments field in the source flat file!

View 14 Replies View Related

Using TEXT Data Type With Carriage Return

Jul 29, 2007

Hi,

I'm using quite odd combination of technology for my project, I'm using PHP and MSSQL 2000, at one certain page, I want to insert to a table where one of the column is TEXT data type, and I want to get the value from the TEXTAREA at the page, of course, with carriage return captured, I manage to get it done in MySQL, where it automatically store the carriage return keyed in by user at the TEXTAREA, while for MSSQL I no luck in finding solution for this, is there any settings I can set or I need to convert the carriage return keystroke to HTML tag at my PHP?

Thanks

View 1 Replies View Related

Carriage Return Inside A Field Of Text Data Type?

Nov 24, 2004

how can i insert a carriage return when i update the field?

say i want to put the following inside a field:
firstline
secondline

how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'

the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines

i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'

but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline

thanks

View 3 Replies View Related

Transact SQL :: FOR XML To Write Data With Carriage Return / Line Feed At End Of Elements?

Sep 30, 2015

I am trying to use FOR XML under SQL Server 2014 to write out a large XML data set. I want it to look like

<CVS_Member_Add_Change>
    <RecordType>3</RecordType>
    <Carrier>1266</Carrier>
    <MultiBirthCode>0000000</MultiBirthCode>
    <MemberType></MemberType>

[Code] ....

That's how it looks when you click on the results of a small subset of the query.  Just what I want.  Unfortunately when you try to right click and save it you get 

<dataroot><CVS_Member_Add_Change><RecordType>3</RecordType><Carrier>1266</Carrier<MultiBirthCode>0000000</MultiBirthCode><MemberType></MemberType<LanguageCode>1</LanguageCode><DURFlag></DURFlag><DURKey></DURKey><SocialSecurityNumber>000000000</SocialSecurityNumber</CVS_Member_Add_Change>

Everything being on one line blows up the translator application that reads the data.

The FOR XML statement copied out of the query is below.

FOR XML RAW ('CVS_Member_Add_Change'), ROOT('dataroot'), ELEMENTS 
GO

Is there a way in the T-SQL to force it to break lines neatly?

Is there a way to force it to a specific file name or directory?

View 3 Replies View Related

Lost All SQL Data, Help !!

Aug 18, 2004

I used sql 2000 create a data base at home call 'Mysite' several months ago, there are about eight tables and 30 stored procedures. When I run my asp.net program at home yesterday, and found can't connect to database. The error as follow: "Cannot open database requested in login 'Mysite'. Login fails. Login failed for user 'MINGYANG-MSONXHASPNET'. "

When I open the database, very surpriced to found that my database was suspect and not any item inside it. The suspect database include CommunityStarterKit. But Northwind and pubs database still there?

What happen? Last week I just download new Norton antivirus software, does have any relationship with it? Does data really gone? How to solve this problem?

Please help me if you have these experience.

View 2 Replies View Related

Data Lost

Oct 22, 2000

We lost all the data we had in a table. We restored the db and the transaction log up to a point in time, but could still not recover the table. We eventually only do db restore without the transaction log. We recovered the data in the table in question, but lost all the data entered that particular day. Does any body have a solution or suggestion that can help? I will be greatly appreciated.

View 2 Replies View Related

Lost Data

Aug 18, 2004

this morning, one user asked me that she entered a data (only one record,)on the database yesterday, but later she checked that is gone, I check the database, i am sure the data is now there. how do i retrieve the data back, or how do i know whehter this is user problem? Thanks in advance

View 2 Replies View Related

Lost Data

Dec 20, 2005

Hi Guru,This weekend, I migrated SQL Server7.0 to 2000 and below is thescenarios:1.) At exactly 4:00PM on Sturday 17th, do full backup(T-SQL) and put adatabase into single user mode.2.) Copy both Saturday 4PM and Friday night full dump to the SAN.3.) Delete WinNT4.0.4.) Reinstall Win2003 and service pack3a.5.) Copy Saturday 4PM backup file to the server and do restore and douser ID mapping.This database is in full mode and I do log backup every hour. However,after restoring the database, any changes after the 14th were missing.Now, When I restored Friday night backup, we still recovered data tothe 16th. My question is, why the 17th 4PM backup did not copy anychange from 15th to the 17th Saturday?I still had Sturday backup file, howe can I recover the 17th data? Logexplorer or ApexSQLLog?Is this Disk issue?Thanks very much,Silaphet,

View 1 Replies View Related

Data Lost

May 24, 2006

Hi,

I have encountered problem in the merge replication with conflict caused by foreign key constraint. As I did some reseach that it can be avoided by increase the upload and download batch generations.

My question is how can I recover the data which has been lost?



Thanks

View 6 Replies View Related

Data Lost , Help !!!

Dec 6, 2006

i make an application to insert data to sdf file and save it

when i colse the application and open it again i find my stored data on the sdf file well

and if i closed the application 1000 times i'll find my data ok

that's ok and that's not the problem

the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data

i realized that management studio delete's the data from my db

Any Ideas Here ???

View 3 Replies View Related

Retreiving Lost Data

Jan 29, 2005

I Somehow restored the latest database onto the test database. Can I now retrieve the lost data. If Yes , How is it possible.

Akash

View 2 Replies View Related

Recovering Lost Data

Jul 9, 2007

Is there any way to get the deleted records from LDF or MDF file. Today by accidentally I executed one query and that dropped my table Inbox(table name only) from database. And in that table there was more 4,000 records, all these lost. So anybody can help me how to recover this table records?. Is there any way to get these?
I don't have any backup for this table.

shaji

View 3 Replies View Related

Recover Lost Data

Aug 9, 2007



Hello!

Hopefully someone can help me that i have lost my data on my D: drive.

driven shown all folder but in side of the folder there is nothing.

i have lost my Doc, xls, Jpeg etc. all files.

please help me what sould i have to do.

thanks

View 1 Replies View Related

Data Lost Automatically?

Apr 17, 2008

hi,
i want to lost data automatically after 3days.
but i don't know how to use job scheduling in sql2000 and how to query to add date to datatime column automatically when the data is inserted.
Plz help me!

View 13 Replies View Related

Urgesnt..data Lost When Run The Script

Aug 9, 2005

Urgesnt..data lost when run the script.i generate a script a data base then i run it on query analizer in same data base .But problem is data base and all design view is safe but all data is lose.how can recover it.thanks.

View 2 Replies View Related

Lost Delayed - Write Data

Dec 4, 2001

Hello All,

Thanks in advance for your help.

I am wondering if there is a correlation between the size of a database and the amount of RAM required in the server. Here's what's going on:

NT4.0 SP 6a, SQL 7.0
2-800 MHz Pentium III Processors
512 MB RAM
Swap Files: two, divided evenly between two partitions, min 512 per partition (dynamic)
3- 9.2 GB Drives, RAID 5

SQL Database size: 2.5 GB

The SQL administrators have a scheduled job set to dump the database every four hours, so they have 6 of these dump files per day. Seagate Backup Exec v 6.? backs up those files every evening. Of course, this is a mission-critical application.

As the NT/Hardware administrator, I was tasked to flash upgrade all the hardware that could be flashed. That happened about three weeks ago. Ever since I flashed it, getting the data dumps has been an inconsistent process. Sometimes it works, sometimes it errors out. The error message is " Lost Delayed - Write Data: The system was attempting to transfer file data from buffers to (path). The write operation failed, and only some to the data may have been written to the file."

On and off during the day, you can't even copy a small file on the server without receiving the same message.

Before I flashed the server, it was blue-screening and needing to be rebooted about once every two weeks. Since I flashed it, the server hasn't been rebooted, because it hasn't blue-screened.

The database dumps are about 2 GB. We were thinking that the way the dump process worked was that a snapshot of the data was put into memory before it was written to disk. We thought since the dump size was larger than Physical Memory + Virtual Memory, that that could be causing the error.

What do you guys think?
1. Is dumping the database first the preferred way to get a backup, or should a backup directly to tape be scheduled instead?
2. Do we have enough RAM for the database?
3. Are there special settings we should set for the RAID controller card to maximize throughput for SQL?

Thanks again. I am definitely not a SQL DBA and any information you can provide would be really helpful.

View 2 Replies View Related

Suspend When *.ldf Was Lost ,I Can Repair This Data

Jan 6, 2004

a sql server database always contains two file.*.ldf *.mdf
When *.ldf was deleted the database may suspend.we can repair such database through about 20 steps.

if you want repair your database .please contact me with email:cn9526@163.net or msn/email :paulyang8848@hotmail.com

you can also see our homepage http://www.haou.net/sqlother/

the service price is begin at 200 US DOLLOR.

View 3 Replies View Related

Recover Lost Data Files?

Jul 20, 2005

HiWhen we issue a DBCC SHRINKFILE with empty option, the data fileis removed and the space is alloted back to the OS.Same way when we issue a an ALTER DATABASE to remove a database,all the data files and transaction log files are dropped.But can these files be recovered again by using any of the diskrecovery tools?Achilles

View 1 Replies View Related

Data Lost After INSERT Command

Dec 21, 2006

I have a website that is used to enter dates from a calendar. For example, user A might go to the website and select 20 different days in January. When user A clicks on the Save button the dates are written to the database with an INSERT command. This works fine. However, when user B then goes through the same procedure, say selecting 5 dates that are in the set selected by user A, those dates are properly saved in the database but the rows containing user A's same 5 dates disappear. I suspect this is something simple but I'm new and ignorant. Please help.

View 4 Replies View Related

Excel Source, Lost Some Data

Dec 20, 2007



I have Excel file with data and I need load these data to database. One of the colume consit following data:

1
2
3
4
test5
6
7
8
9


but when I created connection (Excel Source) and click Preview... in Excel Source Editor, I got following data:


1
2
3
4
NULL
6
7
8
9

Why? How can I get all data as string?

View 1 Replies View Related

Data Contained In A CDATA Section In XML Is Lost

Aug 1, 2006

I know that anything in a CDATA section will be ignored by an XML parser. Does that hold true for the SSIS XML Source?

I am trying to import a large quantity of movie information and all of the reviews, synopsis, etc are contained in CDATA. example:

<synopsis size="100"><![CDATA[Four vignettes feature thugs in a pool hall, a tormented ex-con, a cop and a gangster.]]></synopsis>

Sounds like a good one, no?

The record gets inserted into the database however it contains a NULL in the field for the synopsis text. I would imagine that the reason for this would fall at the feet of CDATA's nature and that SSIS is ignoring it.

Any thoughts would be appreciated. Thanks.

View 4 Replies View Related

Lost Connection To Remote Data On SBS SQL 2005

Aug 21, 2007

I need help!

I have Outlook 2007 w/BCM running on XP Pro workstation connecting to a SBS 2003 R2 hosting my SQL 2005 database. I have two problems: First I can get the BCM to connect to the remote data but periodically the connection is lost and many times it requires a reboot to reconnect. Secondly related to teh first, I can never get MS Accounting 2007 to connect to the same server and database from my client.

I have already set up the server to use port 5356 via TCP amd added a hole through my client windows firewall for the same port. Both client and server are set to share files and printers.

Any suggestions?

View 2 Replies View Related

Data Flow: Decimal Precision Is Lost

Apr 17, 2008

Hello,

Data is being trnasferred from an Oracle view to a SQL Server 2005 table.

Decimals can be previewed in the from the "SQL Command Text Window" but the columns in the target table which are defined as float shows the data being rounded to zero decimal places.

For the Data Source the always use default code page is selected.

Is there a way to retain the decimals?


Thank you,

Rod

View 3 Replies View Related

Each Time I Build Program, Data Is Lost

Sep 8, 2007



I am using visual basic 2008
I am making a program, I used sql server compact edition (sdf) (i think it is no more only for mobile device, I am working for desktop application)which i created with the same visual basic. i update data by using table adapters,

when I close the program and build again, the data previosly updated are deleted, and I get empty database?? why is that. do i need to set some copy to.........properties. i have used copy if new.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved