CR/LF In Comment Field In Emails Sent Using DataDrivenSubscriptions

Mar 15, 2007

I am setting up a data driven subscription to send out an email which includes a report. The Sql for the data driven subscription outputs the EmailTo, Subject, Comment(Body) and some report parameters. My issue is with the Comment (Body) field which I would like to format with CR/LF to make the email body look neat and readable. I've tried many different permutations of inserting the CR/LF, From concatenating Char(13) + Char(10) in which the email does recognize the Cr/LF to concatenating binary 0x0D + 0x0A which after the first concatenation of binary nothing else can be concatenated. Here is the code I've tried:
---~~~~~~~~
declare @iEndDate datetime
set @iEndDate = dbo.fn_today()
Declare @Account_group int
Set @Account_group = 999
/*
DECLARE @mybin1 binary(5), @mybin2 binary(5)
SET @mybin1 = 0x0D
SET @mybin2 = 0x0A
*/
Declare @CrLf VarChar(5)
set @CrLf = CHAR(13) + CHAR(10)
Declare @Str1 VarChar(255)
Declare @Str2 VarChar(255)
Declare @Str3 VarChar(255)
Declare @Str4 VarChar(255)
Declare @Str5 VarChar(255)
Declare @Str6 VarChar(255)
Declare @Str7 VarChar(255)
Declare @Str8 VarChar(255)
Declare @Str9 VarChar(255)
Set @Str1 = 'Good Morning,'
Set @Str2 = 'We are exposed by '
Set @Str3 = 'Attached please find the margin call detail.'
Set @Str4 = 'Please let us know if you have any questions.'
Set @Str5 = 'Thank you.'
Set @Str6 = 'Please respond to:'
Set @Str7 = ''
Set @Str8 = ''
Set @Str9 = ''
declare @t table
(
EmailTo VarChar(255),
ReplyTo VarChar(255),
Subject VarChar(255),
Comment Text,
Account_group Int,
Trading_Account_Id Int,
Broker_Code_Ky VarChar(255),
EndDate Datetime
)
insert into @t
select
EmailTo = '', -- isnull(bf.firm_email,''),
ReplyTo = '',
Subject = 'Margin Call Notice: - ' + Trading_account_name + ' - Exposure: ' + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) ,
Comment = @Str1 + @CrLf + @CrLf + @Str2 + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) + '.' + @CrLf + @CrLf + @Str3 + @CrLf + @CrLf + @Str4 + @CrLf + @CrLf + @Str5 + @CrLf + @CrLf + @Str6 + @CrLf + @Str7+ @CrLf + @Str8 + @CrLf + @Str9,
Account_group = @Account_group,
Trading_Account_Id = trading_account_Id,
Broker_Code_Ky = EGS.Broker_Code_Ky,
EndDate = @iEndDate
from fni_ExposureGovernmentSummary (@iEndDate) EGS
join broker_group bg
on EGS.broker_code_ky = bg.broker_code_ky
Join broker_firm bf
on bg.fbe_firm_id = bf.broker_firm_id
Order by
Broker_Group_Name,
Trading_account_Name
select top 2 * from @t

--~~~~~~~~~~~~~

The Email my company is using is Lotus Notes.

The Question is how do I code the the Comment field so that my email will recognize the Cr/LF.

Thanks

Elias

View 2 Replies


ADVERTISEMENT

Remove A Comment String From Text Field

Oct 24, 2007

I have a field that contains some text. Each field will have none or atleast one comment in it. A comment can be a string of any length with *** on both sides. Ex: ***comment***


Declare @Test_tbl Table(TextField Text)
Insert Into @Test_tbl
Select 'Some text ***comment*** some more text' Union all
Select 'Other text ***another comment*** more and more text' Union all
Select '***Comment*** some text ***More Comments***' Union all
Select 'some text with no comment'


I need the output be ...

TextField
--------------
Some text some more text
Other text more and more text
some text
some text with no comment




Thanks in advance.

View 4 Replies View Related

Can I Get A Comment Or Two On This

Sep 24, 2006

Hey guys,
I have a Access adp that backs onto SQL Server 2005, its taken a while but tis nearly done.

Just one last thing to figure out.. and thats how to send a email. I had a look at all the Access ways and they just wont work out for this.

So I decided to use the server to send them, I would insert/update a row into a table which has a trigger on it.

The table has amoung other things a Approved and Declined column.
Both at 0 Sends an email to an Approver
App 1 Dec 0 Sends on further
and App 0 Dec 1 sends back to the original sender.

All I really want to know is could a trigger execute a package or something that will send the email? (Yes thats right someone isnt asking for code for once:D meh must be winter in hell)

If theres any flaws in my idea please feel free to tell me (Saves me denting the desk with my head half a week and 10 pages of code from now)

And where do I create triggers:P (Will probly have found this one in BOL by the time anyone replys)

Sorry about this but I really have no clue about triggers.. seems the site doesnt like me searching for triggers
'This page was generated in 17.03 seconds.'
:P

View 2 Replies View Related

Comment Column

Apr 23, 2008

Hi

i have a comment column which is varchar(200). when a user types any say for example a couple of small paragraph with commas , colon, hard enter key.

when i run a select statement and save it to csv file and then open with excel, if the comments has comma then i get these info in separate column and if there is hard enter key then it goes into new line.

Is there a way to get these info in one column
Thanks,

View 2 Replies View Related

Comment On Replication

Jul 20, 2005

Hai friendsCould u pleas give me some noites on replication . I worked out thruwizards but not succeded.With thanksRaghu

View 1 Replies View Related

Comment To Table

Mar 26, 2008

Hello,

can I create a comment for a table in SQL Server 2005? For a column I can create a comment in the description-field.

finchen

View 3 Replies View Related

Comment In Column Definition

Apr 30, 2007

Plesae tell me the MSSQL Server equivalent of the below MySQL query .create table temp2(a varchar(23) comment 'male m');What is the use of specifying a keyword 'comment' in the column definition. Will it make any difference

View 14 Replies View Related

T-SQL (SS2K8) :: Latest Comment Per ID

Jul 30, 2015

I want to get the latest comment for each id based on the maximum createdDate and not sure how to do this, tried to do Max but it was still returning all the rows.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Audit](
[id] [nvarchar](64) NOT NULL,

[Code] ....

View 2 Replies View Related

Formatting A Subscription Comment

Nov 10, 2006

I have looked everywhere for an answer for this one, so if it's out there and I've missed it, sorry.

I want to include a list of instructions with a report that I am sending out to a list of subscribers (not Data Driven, each is an individual subscription). It seems no matter how I enter the text in the Comments box, the body of the email comes out as a continuous stream of text with no line breaks, which is difficult for the recipients to read.

Is there a way to embed line breaks as a minimum, but preferrably additional formatting, in a subscription comment?

Thanks

Trevor

View 4 Replies View Related

Getting Comment In Excel Cells

Apr 7, 2006

Hi,

Can any tell me how to check whether any Comment been entered in a Cell in the Excel Sheet?

It is pretty urgent. Solutions are greatly appreciable.

Thanks & Regards,

Prakash Srinivasan.

View 2 Replies View Related

Blog Select With Comment Count

Nov 22, 2003

The following sql works great when the field for my main blog message is type nvarchar but doesn't work for text which I need to convert to.

select
a.id, b.textField
count(b.a_id) as myCount
from a left join b on a.id = b.a_id
group by a.id, b.textField

What other methods could i use to get "myCount" within one sql statement?

Thanks in advance, Jeff

View 3 Replies View Related

Comment Syntax Breaks Code

May 26, 2000

inserted a record with an identity field
I had a '-- comment' right
Select @data = @@identity

This select failed - @data was NULL
I changed it to SET @data = @@identity
This also failed @data was null

I then change to the follow which worked - Why would the different form of a
comment make a difference? I was the only one in the database, there were no
other changes. I could make the logic block fail or succed by just changing
the comment syntax.

inserted a record with an identity field
I had a '/** comment**/' right
Select @data = @@identity

View 1 Replies View Related

Table Design Request For Comment. (Help Please)

Jul 14, 2006

Hi there gurus, can you please add your 2 cents on this design? We'rehaving trouble relating these tables in a diagram because of the keys.Is it necesary to have the references setup? I would assume yes so theforign keys can be setup.If you look at this link, you'll see our diagram. In Red are therelationships that we would like to make for referential integrity, butcannot because of the keys.http://rullo.ca/linktome/QuestionsDB.jpgOur goal in all of this is to have a facility wherin we can store aquestion, that has multiple names over multiple Languages. Forinstance:-Q1| QNameID = 1 | "Do you have a dog in your appartment?" | LangID =1(eng)-Q1| QNameID = 2 | "Do you have a dog in your house?" | LangID =1(eng)-Q1| QNameID = 1 | "-French - Do you have a chien in your appartment?"| LangID = 2(fr)-Q1| QNameID = 2 | "-French - Do you have a chien in your house?" |LangID = 2(fr)The difficulty is when we try and put this in the group details table.We don't want to outline the Language, we'd just pass the language intoa proc to retreive a specific group with a specific language. If youfolks would be so kind as to add your comments to the design I would betruely grateful.CREATE TABLE [Question] ([QuestionID] [int] NOT NULL ,[SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED([QuestionID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [QuestionAnswer] ([QuestionID] [int] NOT NULL ,[QuestionAnswerID] [int] NOT NULL ,[SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_QuestionAnswer] PRIMARY KEY CLUSTERED([QuestionID],[QuestionAnswerID]) ON [PRIMARY] ,CONSTRAINT [FK_QuestionAnswer_Question] FOREIGN KEY([QuestionID]) REFERENCES [Question] ([QuestionID])) ON [PRIMARY]GOCREATE TABLE [QuestionAnswerName] ([QuestionAnswerID] [int] NOT NULL ,[QuestionAnswerNameID] [int] NOT NULL ,[LanguageID] [int] NOT NULL ,[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,CONSTRAINT [PK_QuestionAnswerName] PRIMARY KEY CLUSTERED([QuestionAnswerID],[QuestionAnswerNameID],[LanguageID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [QuestionGroup] ([QuestionGroupID] [int] NOT NULL ,[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,CONSTRAINT [PK_QuestionGroup] PRIMARY KEY CLUSTERED([QuestionGroupID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [QuestionGroupDetails] ([QuestionGroupID] [int] NOT NULL ,[QuestionNameID] [int] NOT NULL ,[QuestionAnswerNameID] [int] NOT NULL ,[QuestionSortOrder] [int] NULL ,[AnswerSortOrder] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[DisplayLevel] [int] NULL ,CONSTRAINT [PK_QuestionGroupDetails] PRIMARY KEY CLUSTERED([QuestionGroupID],[QuestionNameID],[QuestionAnswerNameID]) ON [PRIMARY] ,CONSTRAINT [FK_QuestionGroupDetails_QuestionGroup1] FOREIGN KEY([QuestionGroupID]) REFERENCES [QuestionGroup] ([QuestionGroupID])) ON [PRIMARY]GOCREATE TABLE [QuestionNames] ([QuestionID] [int] NOT NULL ,[QuestionNameID] [int] NOT NULL ,[LanguageID] [int] NOT NULL ,[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Desciption] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[ControlTypeID] [uniqueidentifier] NOT NULL ,CONSTRAINT [PK_QuestionNames] PRIMARY KEY CLUSTERED([QuestionID],[QuestionNameID],[LanguageID]) ON [PRIMARY] ,CONSTRAINT [FK_QuestionNames_Question] FOREIGN KEY([QuestionID]) REFERENCES [Question] ([QuestionID])) ON [PRIMARY]GO

View 1 Replies View Related

How To Add Comment Which Only Appears On The First Page Of The Report?

Sep 13, 2007

Hi, experts,

How can we add comment which only appears on the first page of the report?

I am looking forward to hearing from you for your help and thanks a lot in advance.

With kindest regards,

Yours sincerely,

View 2 Replies View Related

Display No Comment In The Text If There Is No Data

Oct 3, 2007



Hi all,

I have a report where I have to display State, Category Name and the text. Below is the sample report


State Category Name Text


TX Unanticipated High NT due to RADD clean up- drive replacement.

NJ Unanticipated SAR efforts (SEM configuration)


But now my question is if there is no comment in any other state I would like to show no comment in the text line. Is there any formula I can use in the report level that will take care of the issue. I know in Business object there is a formula you can create which is =If (IsNull([Text]);"No Comments Reported";[Text]). I need to know in ssrs there is something.

please let me know ASAP and the steps of doing that.

Thanks

Rozarm02

View 8 Replies View Related

Apostrophe In Comment Confuses ADODB

Jun 5, 2007

We just encountered an odd failure in ADODB. If it gets an SQL query with a comment embedded in it, and the comment has an apostrophe as one of the characters, ADODB gets confused as it tries to plug in parameters for placeholders later in the query. For example:





SELECT id, name

FROM doc_type /* this won't work! */

WHERE name LIKE ?

If you take out the apostrophe ("this will not work!") or move the comment to follow the placeholder, the query works.

Is this a known bug (couldn't find it in the KB)?

Thanks,
Bob Kline

View 28 Replies View Related

SQL Server 2005 Comment Error

Sep 11, 2007

I am running SQL Server 2005 SP2 and I am getting an SQL error due to the server not recognizing the "--" as a comment. (I am using the 32-bit version) The same code runs on SQL Server 2000 with no problem.

Is there a setting to correct this? Do I need to install the cumulative update pkg 3 to correct this problem?

View 9 Replies View Related

Comment Thread For This Forum's Hint Sticky

Sep 27, 2005

My God! What happens if I miss a step, or put it in a different order??? I know I can specify ORDER BY StepID, but what about a missing step? I do have "missing ranges" script, but posting questions filtered through the script process may become a full-time job in itself...What to do, what to do...

View 14 Replies View Related

SQL Server Error Missing End Comment Mark

Jun 23, 2006

Hi,Is there anyone encountered this error before & how it is being resolved?[Microsoft][ODBC SQL Server Driver][SQL Server]Missing end comment mark '*/'The error pops-up when I was running a DTS Import/Export from a SQL server(source) to another SQL server (destination) residing on a differentmachine. I'm copying all tables, views, & stored procedures of a database.Thanks for any input.Regards,Maricel

View 1 Replies View Related

Musing About Dataset And Database Schema Synchronization, Please Comment....

Mar 8, 2008

All- I'm a newbie working on a VWD project. A couple questions arose when attempting to work with a project's dataset and its underlying SQL Server Express database: 


Is it true that when changing the schema of a database in SQL Server Express through the VWD Database Explorer, one must take care to manually make parallel changes in the dataset? For example, if you make a relation between two tables in Database Explorer, you must do the same with the counterpart tables in the dataset? For example, I find that if I delete or create a relationship between two tables in the database, I must manually do the same in the dataset.

View 2 Replies View Related

Profiler Does Not Comment Out Security Related Commands When There Is An Error In The Batch

Aug 1, 2006

i am testing some encryption
scenarios ,in profiler the statements like "OPEN KEY" and all "Encrypt"
and "Decrypt" functions are removed automaticly from the trace
and replaced with a comment ,create a trace and try the code i attached ,
you will see in profiler trace that that all encryption related commands
are commented out ,this is what expected.

but now go to the batch and comment out the "SELECT @rrr' statment,
and run the batch ,this batch will fail beacuse "@rrr" is not declared,
now go back to profiler and you will see that for the failed batch
all the encryption command are NOT COMMENTED OUT !!!
esspecially important is the visibility of the password of the open key command.


seems like a very dangerous bug to me!!!

CREATE CERTIFICATE test1
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Sammamish Shipping Records',
EXPIRY_DATE = '10/31/2009';
GO

CREATE SYMMETRIC KEY Key09 WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE test1;
GO
declare @Str nvarchar(100)
declare @Enc varbinary(max)
set @Str = 'encrypt this'
OPEN SYMMETRIC KEY Key09
decryption by CERTIFICATE test1 WITH PASSWORD = 'pGFD4bb925DGvbd2439587y'

SET @Enc
= EncryptByKey(Key_GUID('Key09'), @Str);
---select @rrr
select CONVERT(nvarchar(100), DecryptByKey(@Enc))
go




View 1 Replies View Related

SQL Server 2012 :: Replication - Missing Stored Procedure Comment Header

Jul 25, 2013

I have recently started using replication in SQL 2012 SP1. When a stored procedure is altered on the source, the changes are replicated to the subscribers; however, the comment headers are removed at the subscribers. Due to the vast number of stored procedures I have, I do not want to move the comments below the Create Procedure statement. Are there any other ways to have comment header move with the stored procedures?

Here is what I am experiencing

Source SP

ALTER PROCEDURE [dbo].[SPTest]
AS
BEGIN
SELECT GETDATE()
END

Destination SP

ALTER PROCEDURE [dbo].[SPTest]
AS
BEGIN
SELECT GETDATE()
END

View 7 Replies View Related

SQL Job Notification Emails

Feb 15, 2008



Hi, looking for some help on job notification emails in SQL 2005.

I've created a backup job in the Maintenance Plan section of SQL2005, and added a "Notify Operator" task to notify me when the job completes.
I've setup Database Mail, and I am getting the notification email whenever I run the job. So this is working fine.

The problem is when I got to the 'Jobs' folder, and into the properties of my backup job, I set it up to notify me from the notifications option, but I am not getting any emails. For now I have set it to notify me when the job "succeeds".

Any ideas why it works from the Maintenance Plan section, but not the "Jobs" notification section? Am I forgetting something?

I have recently migrated a SQL2000 instance to SQL2005, and would now like to setup all the existing jobs to notify me if they fail.

Any advice would be helpful

Thanks

View 1 Replies View Related

Sending Automated Sql Emails

Mar 12, 2007

Hi
I am using asp.net framwork 2 with sql 2005 express edition. Can anyone please provide me any sample of sending automated emails (like welcoming people when they register to my website, or if their personal details have been updated....). or any e-book/website ref will do. I am not very expert in sql/db and smtp (or other email) stuffs. So please help me.
With thanks

View 6 Replies View Related

Processing Incoming Emails

Sep 14, 2004

How would I go about checking incoming e-mails? For example, on a certain e-mail address, I would get e-mails formatted in a certain way. According to the response, some scripts need to run/ some sql tables updates etc. How can one do this in (ASP) .NET with SQL Server? Anyone did this kind of stuff before?

View 3 Replies View Related

Sending Emails To A Database

Feb 14, 2006

I'm new to programming and to databases so apologies if this sounds like a stupid question.

I have a html page with a simple javascript function that uses the href mailto tag to send an email to a normal email address. I was wondering if it would be posssible to send the email directly to an SQL Server database, without having to pass through an intermediate parsing app. It would mean assigning an email address to the database. Is this possible with SQL Server? I know there would still be problems with parsing the email content to the appropriate fields, but would the sending of the email to the database in itself be possible?

I haven't much experience using databases and I don't currently have access to the SQL Server Express database that will be used to store the email content, so I'm not sure how difficult an operation this would be. I've looked on the internet but I haven't been able to find anything. I was hoping someone here might have done something similar, either with SQL Server or with any other database.

Thanks in advance.

View 2 Replies View Related

Removing Emails From Table

Sep 11, 2014

I'm trying to remove emails from a table but the simple update query I wrote doesn't seem to do it. I figured I'd test one record instead of the entire dbase. I've probably given more info than needed (the select statement) but wanted to give a frame of reference.

SELECT
Name.ID,
EMAIL,
COMPANY,
MEMBER_TYPE
FROM
APSCU_PROD.dbo.Name

[Code] ....

View 9 Replies View Related

SQL Server And Automated Emails

Jul 23, 2005

Hi, folks. I'm fairly new to SQL Server, so forgive any stupidityinherent in this question. I work at a university and a certainfaculty member would like to have her students create a simple auctionweb site as an exercise. As part of the assignment, she wants theauction site to be able to send an email to a bidder when they havebeen outbid.So she has asked me if this is possible, and how to do it. I've read abit about sending an automated email from SQL Server, but I can't findanything about how to trigger the email based upon a field comparisonin the database. I also don't know how to incorporate a field value asa variable into the body of the email.Of course, I don't expect anyone to solve my entire problem in thisforum. I was just hoping that someone could point me in the rightdirection. Can anyone give me any advice about where I should belooking for such info? Can you recommend a web page, or some otherreference material? Is there a specific terminology I should be usingwhen I search? I just don't seem to be able to find what I need.Please reply to Join Bytes! -- I don't read newsgroups regularly.Many thanks in advance!--Sue

View 3 Replies View Related

Automatic Import Of Emails

Jul 14, 2006

Is there a way to automatically import incoming emails into a SQLServer Database?I know it is inefficient, but we have to run a number of rules on theemail after it is received. Store it in a database and be able toconnect other things, meetings, contacts, etc. Moving it back andforth in chunks can be difficult and the overhead could spiral out ofcontrol.So we were thinking of moving it to the DB as it was retrieved.What do you think would be a better solution to retrieve it?Does my email make sense?

View 2 Replies View Related

Spam Emails From Pinnacle

Jul 20, 2005

Just wondering if there are other people getting mail from Pinnaclesoftware demanding payment of $179 for a subscription that they didn'trequest. I am now on the"Fourth Notice - Invoice past due"I know only one other SQL Server DBA and that person is also gettingthe mail each month. Information on the invoice says the paymentshould be made to Ragan Communication. Is there anyone else out therewho's getting these mails demanding money. I personally find itdisgusting that the company operates in this fashion.

View 1 Replies View Related

Subscription Sends 2 Emails

Jan 23, 2007

We have SQL server 2005 Reporting Services SP1. In Report Manager I have a shared subscription that runs every hour between 8am and 6pm (I created the shared subscription in Report Manager, then modified it in Sql Server Management Studio to run the hours I wanted). I have a second shared schedule that runs Tuesday and Friday at 7am. For 2 separate report subscriptions - one uses the hourly schedule and the other uses the 2-day schedule, the first time they run during the day it sends 2 email - after that it sends only 1 email, as it should. I created a subscription to email a report when the report content is refreshed from a report snapshot. In Properties...Execution, I selected to render this report from a report execution snapshot, and selected my shared schedule. For the History property I have selected Allow report history to be created manually and Store all report execution snapshots in history; I am not using a schedule to add snapshots to report history in the History property.

When I look at the job in Sql Server Management Studio, it has only ran once each hour. When I look at the snapshot history, there is only one record for each hour. Why is the first scheduled instance sent twice instead of once?

View 8 Replies View Related

Auto Reminder Emails?

Mar 30, 2007

I don't know much about broker service so I have question. We have a content management system the we developed locally, and what we have are catagories and subcatagries. When people choose a topic we send them an email about the topic they are interested in. We are now selling webinars and white papers and articles on demand. We would like to be able to send a reminder email to anyone who signed up for a webinar or special event. Is this possible with broker service?

View 4 Replies View Related

Error In Sending Emails

Apr 23, 2007

Hi,

i am getting the following error when i try to schedule a report and Delivery it through an email

"The e-mail address of one or more recipients is not valid"



although, i have mentioned my pop3 mail account, i have included the smtp server details in the .config files as per the msdn help.



pl help me solve this



T & R,

Bharath V V

View 3 Replies View Related







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