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
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.
What is the best way to query for columns which contain embedded carriage returns?
View 2 Replies View RelatedCan 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.
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.
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 RelatedI 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.
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
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
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!
Hi all,
I'm trying to import around 4000 rows of data into SQL from an Excel spreadsheet. I thought I might be able to simply copy and paste into SQL Server (Tables > Right click on table > Open Table), but when I try this only one value is pasted in. There's only around eight columns and I've been trying to do each seperately but to no avail.
Can anyone suggest a means importing this data (as a whole, or in , either through some simple copy and paste function, or through some code?
TIA,
SamuelT
Hi
We have some reports that are published as PDF files. The layout of the report is fairly free, using lists within lists.
Some of our users want to cut and paste some of the data. When they do so, the order and layout of the fields does not resemble the layout of the report. Any way around this.
Note that when the report is published as mhtml or excel, cutting and pasting from these formats is much better.
thanks
Peter
How do I copy a column of data from excel and paste into an sql table?
View 1 Replies View RelatedPrevious to 2012, grid results from SSMS queries pasted into Excel beautifully and were easy to format.in 2008, The results from this query pasted into a spreadsheet with the query contained in a single Excel cell:
(sample - not the whole script)
select top 300
Avg_CPU_Time
,Total_Physical_Reads
,convert(datetime,Last_execution_time) as Timestamp
,Stored_Procedure
,Query_text
from dbadmin.dbo.History_CPU_IO_ByQueryAndSP
This was wonderful. I could deliver to developers wonderfully tidy reports on query resource usage with timestamps along with which stored procedure the queries came from.
Can't do that in SSMS 2012. Try it. It's a disaster. The Query_text, when pasted, spreads across multiple Excel columns, including the ones designated for other data. The result is totally unreadable.how to make the query_text stay in it's own cell? I've tried converting query_text to varchar and ntext. Same results.
I have a complex model with lots of tables and measures. It's got to the point where pasting measures in the measure grid is getting very slow with a few seconds flashing "finishing". The calculation mode is set to manual and I'm not switching between the workbook and the PP window. How to speed this up?
View 2 Replies View RelatedI can't paste text (directly into table row via enterprise manager) into a varchar (5000) field, truncates after about 960 char. Length of string trying to paste is about 1400 characters including spaces. No special characters (one apostrophe). Error happens intermittently.
SQL server v2000
While retrieving user input from an input control, eg: multi-line textbox, and inserting it into the database, the carriage return or the 'Enter' key is not getting inserting into the database.. instead it inserts a quad ( square ) in the database.. also the text typed after the 'Enter' key is not getting inserted into the database.. please help.
View 5 Replies View Relatedhello everyone,
I have a a texarea box and user type in the comments, and then I insert it into sql database, i retrieve it and write to a text file, but the text file shows multiple lines bc user hit "Enter Key" or "Tab key" now i want to remove all the Carriage Return character or tab key, and i want the text file shows countinous lines.
any suggestions are greatly appreciated
thanks
I want to add a carriage return in ActiveX to seperate lines... IE:
This currently appears in 1 line in the email message.
Mail.Body = "Error creating template : " + DTSGlobalVariables("DirectoryOut").Value + " Error Message: " + DTSGlobalVariables("ErrorMessage").Value
I want it to appear in seperate lines... like so.
Error creating template : templatename
Error Message: Message
backslash n doesn't work, can I put an ASCII character in there? (code 13?)
I have a string with multiple carriage returns in it. I need to removethe second carriage return but leave all the rest. Anyone know of afunction that will do this?I've come up with this:/***********************************************/DECLARE @MyString varchar(100)SET @MyString='Line one.Line two.Line three.Line four.'SELECT @MyStringSELECT @MyString = LEFT(@MyString, CHARINDEX(CHAR(13), @MyString,CHARINDEX(CHAR(13), @MyString, 0)+1)-1)+ RIGHT(@MyString, LEN(@MyString)-(CHARINDEX(CHAR(13), @MyString,CHARINDEX(CHAR(13), @MyString, 0)+1)+1))SELECT @MyString/***********************************************/However, if there is a less convoluted way of doing this, I'd love toknow.
View 1 Replies View RelatedHow to get a carriage return (or new line) in the result of select query.
Example: Select 'a' + 'b'
display
a
b
and not ab
Hi All,
I am experiencing problem to select text wich has carriage return in my search functionality.
I have two tables called @searchwordTable and @DataTable.
@searchWordTable will have search criteria words(data type is varchar) and @DataTable will titles(data type is ntext) need to be searched. Some of titles have carriage return,line feed and tab characters. I am preseting here script to reproduce my problem.
DECLARE @searchwordTable TABLE
(
searchword VARCHAR(MAX)
)
INSERT INTO @searchwordTable (searchword) VALUES('carriage long description')
DECLARE @DataTable TABLE
(
title ntext
)
INSERT INTO @DataTable (title) values('carriage long description'+char(13)+char(10)+'carriagelong')
SELECT * FROM @DataTable dTable,@searchwordTable srcWrdTable
WHERE '% ' + REPLACE(CAST(dTable.title AS VARCHAR(MAX)),char(13),'') + ' %' like '% ' + srcWrdTable.searchword + ' %'
I am expecting the above select statement should select title from @DataTable but not getting .I am not understanding what is going wrong with above select.
Thanks in Advance.
I am using BCP and one of the tables in my SQL contains a column of type nvarchar(500) - whose data are actually email messages such as:
Sent: Wednesday, January 09, 2008 11:03 PM
To: Kathy Shouner
Cc: Brent Ford; William Dew
Subject: RE: Revision of presentation schedule
I support the recommended revisions.
When I open the datafile, it spans 6 separate lines - probably ending in carriage returns (not sure how to confirm this). Hence, I am having a problem importing it to my table. When I query the database, it appears as one line under that column - which is the way it should be.
My BCP command is something like this:
BCP "select email_msg, email_id from emails" QUERYOUT "c:email.txt" -c -T -S sunny.erwin.com
I experimented with -w, -n, -N already but still am failing to get the proper output.
Please help... Thanks in advance.
Hey everybody!
How do i insert a carriage return at the end of an record that's being sent to a flat file? Currently, I get one long string, and would like for SSIS to put carriage returns at the end of each line.
Any ideas?
Thanks!
Jim Work
How do I enable SQL Management Studio to allow me to directly copy text with carriage returns into a table?
I know that I can use the CHAR(13) statement when using T-SQL insert/update statements ('some text' + char(13) + 'some more text').
However when I copy text with carriage returns directly into a table column by right clicking the table, opening the table and editing the row directly, all text after the first carriage return vanishes. Only the text before the first carriage return is committed.
Please help.
I've been looking for this online and on MSDN but no luck. I simply want to find all my CR in specific columns and later Replace them with a string (ie. --THIS-IS-A-CR--). The problem is I cannot even find/search CHAR(13) by using variations of the query below.
SELECT *
FROM Incident
WHERE (description LIKE '%CHAR(13)%') --I know this is incorrect
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View 6 Replies View RelatedWhen I make a call to a stored procedure to update a record, the update does not happen. No error is thrown, there are just no rows updated. When I look at SQL Server Profiler, this is what I see:
exec TTN_Update_RecurringIssueVehicle @RecurringIssueVehicleID='00962233-6EC3-42CE-ABBD-1851F1176D63',@RecurringIssueID='66FE821A-9881-4368-B975-5B04975C5E16',@RecurringIssueProblemID='ABED20CD-CB37-4491-903F-553555EEE47C',@MarketID=1,@VehicleNumber=268,@AddedDate=N'12/14/2006 3:30:00 PM',@AddedBy=12,@ResolvedDate=N'12/15/2006 10:19:45 AM',@ResolvedBy=12
It seems to be putting a carriage return in the middle of one of my date parameter values, and also seems to be putting those values in double quotes. If I try to run this in SQL Management Studio, it throws an error. If I take out the carriage return, and fix the quotes, it succeeds. Here is my VB.Net code:
<DataObjectMethod(DataObjectMethodType.Update)> _
Public Function Update_RecurringIssueVehicle( _
ByVal RecurringIssueVehicleID As Guid, _
ByVal RecurringIssueID As Guid, _
ByVal RecurringIssueProblemID As Guid, _
ByVal MarketID As Integer, _
ByVal VehicleNumber As Integer, _
ByVal AddedDate As DateTime, _
ByVal AddedBy As Integer, _
ByVal ResolvedDate As DateTime, _
ByVal ResolvedBy As Integer) As Integer
dbCmd.CommandText = "TTN_Update_RecurringIssueVehicle"
dbCmd.Parameters.Clear()
dbCmd.Parameters.AddWithValue("@RecurringIssueVehicleID", RecurringIssueVehicleID)
dbCmd.Parameters.AddWithValue("@RecurringIssueID", RecurringIssueID)
If RecurringIssueProblemID <> Guid.Empty Then dbCmd.Parameters.AddWithValue("@RecurringIssueProblemID", RecurringIssueProblemID)
dbCmd.Parameters.AddWithValue("@MarketID", MarketID)
dbCmd.Parameters.AddWithValue("@VehicleNumber", VehicleNumber)
dbCmd.Parameters.AddWithValue("@AddedDate", AddedDate.ToString())
dbCmd.Parameters.AddWithValue("@AddedBy", AddedBy)
If ResolvedDate <> DateTime.MinValue Then dbCmd.Parameters.AddWithValue("@ResolvedDate", ResolvedDate.ToString())
If ResolvedBy <> -1 Then dbCmd.Parameters.AddWithValue("@ResolvedBy", ResolvedBy)
Dim retVal As New SqlParameter("@RetVal", SqlDbType.Int)
retVal.Direction = ParameterDirection.ReturnValue
dbConn.Open()
dbCmd.ExecuteNonQuery()
dbConn.Close()
Return CInt(retVal.Value)
End Function
Any ideas are greatly appreciated - thanks
I have a stored proc that builds a character string from a number of rows returned from a select query. Is there anyway to insert a carriage return after I append the value of each row to the string variable?
Thanks in advance,
Chris
Hi there, I hope you can help us.
We're using this statement to import some values into a database:
SQLStatement = "INSERT MyTable (ID, VALUE) VALUES ('" & IDString & "', '" & VALUEString & "')"
The problem is that the strings might contain carriage returns/line feeds, i.e.
VALUEString = "Line 1." & vbCrLf & "Line 2."
It only imports up until the first vbCrLf. We have tried replacing vbCrLf with "", but this doesn't work either. What is the correct format for inserting a value that contains new line characters?
Thanks for your time,
Lasse.
Hi
How do i remove Carriage return in a varchar column?
Thanks
Hi I have a text file which I need to import into Access or SQL.
It is 500,000 records which is pipe delimited. The problem is, is that it can contain carriage returns: (square symbol).
Therefore I need to find and replace these characters
Does anybody know of any free ware text file viewers that can do this??
Best Regareds
David
Hi, I am having trouble removing what must be a carriage return or line feed from a report when exporting. The data in query builder looks like a square.
i am using this expression, but it is not working, and if you import the report into excel, there is a new line after this column.
=Replace((Fields!description.Value),VbCrLF," ")
Thanks