Hi,
I am saving a couple of paragraphs of text into a varchar(max) field in a SQL database table. But when I try to use display the information again, then all the linefeed and return characters seem to be removed. How do I keep this text formating in the database field?
I am sure that I am missing something simple here, but I can not figure out what. Can someone please help with some advise?
Thanks
Regards
Jan
Hi All,This may seem like a stupid question but is there a way to keep textformatting when you add it to a database? Such as Paragraphs and linebreaks?Any help greatly appreciated.Blaine
View 2 Replies View RelatedHello, I am using SQL SERVER Reporting Services 2005. I am having a formatting problem. The problem is that I hav a textbox in which numeric field is coming. I want to show the currency format of the Regional Setting of the user pc. I have set the Language setting of the textbox to Default. Now when I am changing the Regional setting from US to UK then in the criteria section the datetime criteria (calendar's value) is changed according to the UK setting, but in the report section the selected date time is not changed (coming in US setting). And the currecy format is also not changing to UK format. Any help ?Regards,Furqan
View 1 Replies View RelatedI have a problem.... I have a TSQL procedure that calculates employee overtime based upon days 1-5 of week being "in-week", and days 6-7 oweek being week-end overtime. The resulting data is then mailed to dept managers. My problem: for some reason the calculated colums are being generated at a width of 40 char per column. I somehow need to reduce the width to 8-10 characters per colum (this will eliminate the wraping that I currently get in my e-mails)... Any help would be appreciated... Thanks..Tom
View 1 Replies View RelatedI have a "report" that is more like a form with a great many fields on it that are not arranged like columns. The data is displayed in text boxes. My problem is that the "fields" or text boxes do not always form a nice single line where they connect - at least in the displayed format (HTML4.0), but actually do in printed format. It looks like the fields/textboxes may have a varying width, and possibly height.
Is there any way to make sure that the width and height are static?
Thanks,
Menno Homburg
Dear All,
I have a problem formatting a field in Reporting Services (minutes to hours).
I have a field called duration which stores time in whole minutes only. I can format this into hours within mssql using the following:
cast(sn.duration/60 as varchar(5)) + ':' + RIGHT('0' + cast(sn.duration%60 as varchar(2)), 2)
But I need to have totals and average columns in my report, which means that the data must come through to RS in the minutes format so I can perform the calculations there.
I have the first part (I think!!):
=string.format("{0:0}",Fields!SalesTime.Value / 60) + ":"
But I cannot get the minutes part working!
Any help would be gratefully received.
Dan
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
I am trying to output data from SQL Server to an email body message with a little formatting (just to make it legible)
Using this SQL statement:
select top 1 'Name', app_first_name+' '+app_last_name [name] ,'Submission Date', submission_date ,'Address', app_address+', '+app_city+', '+app_state+' '+app_zip from vw_bpa_export where app_email = dave@email.com order by submission_date desc
the resulting output displays this in the email body
"Name","Brian Vogler","Submission Date","2008-01-30 07:45:00","Address","6285 Harrow Trace, Norcross, GA 30092 "
but what I want is the text to look more like this:
Name Brian VoglerSubmission Date 2008-01-30 07:45:00Address 6285 Harrow Trace, Norcross, GA 30092
Is there a way to do this in the SQL statement (without HTML tags)?
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
View 9 Replies View RelatedDoes anyone know if there is a way SQLMail can always send a message using a fixed font like Courrier? I use SQLMail to send out an invoice and even though I format the spacing, the email looks unformatted on some systems.
I am using Exchange 5.5, SQL server 6.5 sp4 and the recipients of the emails could have any email client.
thanks,
-Darin.
Is it possible to format one word in a text box to the color blue and another word in the SAME textbox to the color red?
View 1 Replies View RelatedI am designing a report using SQL server Reporting Services. I need to format my report and it requires for the text in my textbox to appear inclined ( as we have it in Excel). Is anyone aware of such an option in SSRS where we can set the direction of the text to be inclined ? Any help is appreciated !!
Thanks,
Deepti
The value expression for one of the column in my report is
="Notes: " + Fields!Notes.Value. I want to make this Notes caption to bold. I can't move "Notes: ", Fields!Notes.Value to the 2 different columns. So I want to know, Is there a way to do it?
hey all,
does anyone know of a way to format part of a text box in sql reporting services 2005? i am creating a form letter and need to concatenate static text with a column and need just the column value to be bold. like this:
"This letter is confirmation that Acme Agency has been chosen..."
my expression to build this is:
="This letter is confirmation that " + Fields!agency_name.Value + " has been chosen..."
Is there a way to apply formatting just to Fields!agency_name.Value ?
thanks!
jen
What is the best way to parse large amounts of formatted text data
into a table so that it can be retrieved with as much formatting
retained as possible - particularly paragraphs? Will each
paragraph need to be inserted into its own row to be retrieved as a
paragraph?
Is it possible to retain text or paragraph formatting in a SqlServer 2005 Express edition table? If so, how?
I am particularly interested in keeping a linebreak between paragraphs and the only way I can think to do that is to put each paragraph in its own row. But I want some input before I undertake that substantial task.
Thanks for any help provided.
I have a data table in my project that has a "text" (not varchar) field in it. I am trying to load this field with little paragraphs of text for showing on my pages. How do I get the free text loaded into the table? The database explorer and all the data grid controls cut the text off at one line. There doesn't seem to be any way to get multiline text into this table.
View 2 Replies View RelatedI have a field where the data is stored with RTF. I know SQL Reporting Services cannot display it properly. Is there way to remove the RTF formatting codes through a function to just pull out the text and displya it in the text box?
View 4 Replies View Related
Hello,
I am having a problem trying to do some conditional formatting on the text color.
This textbox is in the Group Footer so i essentially want change the color to red if the SUM of one field in the group is less than the SUM of another field in the same group.
I am doing this:
=Iif(Sum(Fields!YTDChargeHours)< Sum(Fields!YTDForecast), "Red", "Black")
but i am getting this error:
[rsRuntimeErrorInExpression] The Color expression for the textbox €˜YTDChargeHours€™ contains an error: Operation is not valid due to the current state of the object.
Thanks in advance!
Trying to use this tool to create a form letter. I have a text box that has a paragraph of data within that paragraph I'd like to bold a word or underline a word. Is this just a gross misuse of this tool or is there a way to format text within the text box?
View 3 Replies View RelatedHey everyone,
using SQL 6.5 sp_makewebtask in a stored procedure I want to create a
press-info file.
My query calls four fields, a datum field, two varchar fields and a text
field, it's
the field "PrText" in my example:
EXEC sp_makewebtask @outputfile='F:EntwicklungRedaktionZeitwenden27.ht m',
@query='SELECT Datum, Ort, Headline, PrText
FROM news
WHERE ID = 27',
@templatefile='F:EntwicklungRedaktionZeitwendenTem plates.htm'
My question: Am I limited to 255 characters in this case? After 255
characters the text is cut off and
I have only a truncated text fragment.
Is there a possibility to get the whole text without using @blobfmt (I don't
want to use this parameter because
I want to have the datum data, the varchar headline AND the text in ONE
HTML-file.)
I have tried the same as dynamic query in ASP where I have no problem to get
the whole content of the text field, but I want
to create a static file because this information will be read often, but
won't be updated frequently
and I don't want to charge the server resources to much.
Has anybody an idea? Any help would be appreciated.
Thank You
Dorothee Wiethoff
I'm unable to run a Union query with text fields in it. I get the following error:
'The text, ntext, or image data type cannot be selected at DISTINCT'
Is there no way to run a UNion statement for two tables that have text fields in them??? Please help.
The Text datatype can hold several million characters
The size of a record can be no more than 8 KB in normal data.
Created a table with one text field. For some reason can only store 1K of data into it. Even if the 8K limit was a factor (which it shouldn't for a text field) I cannot add more than 1K of data into my text field. 1023 characters to be exact.
Why is SQL Server 2000 doing this and more importantly how to resolve?
Hi, I'm sure that this is probably a really easy question to most of you, but I seem to be spending ages on it as I am new to SQL. how do I add the carraige return char between joining two text fields ie.
a_blah.blah+CHAR(13)+b_blah.blah
Thanks for your help.
New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:
Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....
I've created the following query that displays the columns:
CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE
FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)
The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE
Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?
Please advise - thanks!
I've just gotten my data into SQL Server, and changed the connection stringon the program.There was a memo field (Access 2000) and we put that in a text field (with adefault length of 16?).Now, when displaying the data online (through asp pages), there are bigwhite-space gaps in the pages (that display the memo field data), imageshave a "double" url(http://www.mysite.com/thisfolder/ht...thelinksite.com)there are lots of place with " ".The field data has html tags in it, if that has anything to do with it.It seems like I've heard that there are issues with text fields. Is thiswhat they meant? Is there anything I can do?Thanks, JA
View 4 Replies View RelatedI am trying to update text fields where the field only has the value"closed." So a field that has closed and anything else will not beupdated.update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed_')update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed%') and len(convert(varchar(7000),notes)) = 7Neither code works and i get this error: "Subquery returned more than1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.The statement has been terminated."What's another way to make the update?
View 1 Replies View RelatedI have the following data.
CustomerID Address
1 Spencer St
2 Frank Way
3 Jolly Dr
and i need to extract the address as 2 seperate fileds like so,
CustomerID Street Suffix
1 Spencer St
2 Frank Way
3 Jolly Dr
keep in mind that the Customer table has 2 million records.
Hi All,
I am trying to get at some text fields from an AS400(JBA) system. the problem i'm having is that for each new line of text on AS400 it creates a new row with a line number associated in the SQL table. When trying to export to excel this causes problems because i need the text in one cell not spread over 15/20 lines.
I need to create a query/function that will concatenate the text lines together. I'm fairly new to T-SQL so could do with some help.
Example:-
ID TxtLn Text
R262965
1
Text 1
R262965
2
Text 2
R262965
3
Text 3
What i'm aiming for is:-
ID ConcatText
R262965 Text1 Text2 Text3
Can anyone please help me or guide me towards a starting point.
Thanks
I have three user tables (identical structure) that holds customer information. Each has about 1200 records, give or take a dozen, 99% of which are identical. However, there are a few records in each table that differ. Im trying to select the differences in these tables using a query.Each customer has a custmer ID, but cust_id 1234 in table a may not be the same customer as cust_id 1234 in table b. Therefore, i am trying to compare using the cuLoginName field which is the username/email address text field. This is what differentiates the records.
I have tried using
select cust_id, cuLoginNamefrom tblCustomerAwhere NOT exists (select cuLoginName from tblCustomerB)
to get the different records between the two tables (i.e. those in A but not in B), but even though there are two users in A that do not appear in B, the results are coming back with nothing. I am guessing this is a problem comparing on text fields?
What is an effective way around this problem?
Thanks
Hi,
I am trying to do a bulk insert using a text file that is comma delimited.
In the text file there are fields which are surrounded by quotes and contain commas; these commans should not be considered delimiters.
Is there a way to tell the BULK INSERT statement not to treat these as delimiters?
thanks,
sivilian
in SQL 7, can i set up a constraint or rule where a data entered in [field 1, table a, database 1] has to exist in [field 3, table a, database 2]?
i know i can't set up a relationship, and i haven't been able to get a constraint or rule to work either.
if i can't do the above, what is the recomended way to do this?
i don't want to, nor should i have to duplicate the table.
also, when importing data from one sql server to another, any field that was a data type of text came across as null?
any help would be great
thanks
spiralstairs
Hi:
I'm trying to transfer a table from SQL Server 7 database to another SQL server 7 database on
another server. This table has a text field with lots of data (~.5-1 G). I'm using the export wizard
and the transfer appears to complete successfully, but when I view it, the text field data has
been truncated.
Any ideas?
Thanks, Nicole Lane