Trouble Formatting Strings In Email Stored Procedure
Aug 9, 2007
Hello,
I have a sqlserver stored procedure that calls the stored procedure sp_send_cdosysmail_htm to send reminder emails to customers. I am experiencing problems when trying to concatenate the id being renewed in the subject field. I'm always getting the message "error 170: line 10: Incorrect syntax near '+'."
Does anyone know what the error means or can point me to a resource on the web?
Many thanks
Ritao
CREATE PROCEDURE dbo.SendRenewalEmail
@ID INT
AS
BEGIN
exec dbo.sp_send_cdosysmail_htm
@From = 'yosemite.sam@acme.com',
@To = 'road.runner@acme.com',
@Cc = null,
@BCC = null,
@Subject = 'Order ' + @ID + 'Renewal Reminder',
@Body = 'Hello roadrunner....'
END
GO
View 4 Replies
ADVERTISEMENT
Jun 1, 2007
Hi All..
I am trying to parameterize a tablename in a StoredProcedure something like below and use the below code in my other Stored procedure..
CREATE PROCEDURE SP_BACKUPTABLE @TABLENAME VARCHAR(255)
AS
DECLARE @BACKUPTABLE VARCHAR(255)
DECLARE @ALIASTABLE VARCHAR(255)
SET @BACKUPTABLE = @TABLENAME + '_BK'
SET @ALIASTABLE = @TABLENAME + '_ALIAS'
SELECT @ALIASTABLE.* FROM @TABLENAME AS @ALIASTABLE
i get error 'near .'...
How do i achieve the above code-functionality
Thanks in advance
View 2 Replies
View Related
Aug 14, 2006
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure
For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.
So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>"
Is there an way to accomplish this inside a stored procedure?
View 2 Replies
View Related
Jun 10, 2007
Well, I managed to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@IDList varchar(500),
@ReorderLevel int,
@ProductName nvarchar(30)
)
AS
BEGIN
SET NOCOUNT ON
EXEC('Update dbo.Products
SET ReorderLevel = (' + @ReorderLevel + ') ,ProductName = (''' + @ProductName + ''')
WHERE ProductID IN (' + @IDList + ')')
END
----------------------
THis works fine inside Sql Server 2005 Query analyser.
But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.
Any help would be appreciated
And can someone convert it to support XML instead of list of strings. thanks.
View 3 Replies
View Related
Jul 11, 2001
Hi guys,
I'm trying to parse a character string in a stored procedure but I'm not sure how to do it. For example:
-------------------------------
create procedure sp_test (
@fld varchar(1000)
)
AS
???
???
.
.
.
.
----------------------------------
sp_test '123:abc,456:def,789:ghi'
I need to put 123 into a seperate local variable, abc into a local variable, and so on...
What would be the best function to use?
Any help would greatly be appreciated.
- Gary
View 1 Replies
View Related
Feb 4, 2008
When I try to run these queries in my stored procedure by hardcoding officeids, they run fine, but when I use the parameter @officeid and then try to run the procedure by using
getEmployeeInfo_proc '001' I get no results. Can anyone see what I could be doing wrong? Thanks Here is my code:
1 SET QUOTED_IDENTIFIER ON
2 GO
3 SET ANSI_NULLS ON
4 GO
5
6
7 ALTER PROCEDURE getEmployeeInfo_proc (@officeid varchar(10))AS
8 select distinct
9 a.emplid, a.name,a.last_name, a.first_name, a.status, a.officeid,
10 b.deptid_child_node, b.dept_child_descr, a.job_descr,
11 d.officename
12 from employeeinfo_vie a,
13 dept_all_nodes_tbl b,
14 office_tbl d
15 where
16 a.status in ('A','L','P') and
17 a.officeid = d.officeid and
18 a.deptid = b.deptid_child_node and
19 a.officeid = '@officeid'
20
21 UNION
22
23 select distinct
24 e.emplid, e.name, e.last_name, e.first_name, e.status, e.officeid,
25 b.deptid_child_node, b.dept_child_descr, f.job_descr,
26 d.officename
27 from phone_admin_tbl e,
28 dept_all_nodes_tbl b,
29 office_tbl d,
30 jobs_tbl f
31 where
32 e.status in ('A','L','P') and
33 e.officeid = d.officeid and
34 e.deptid = b.deptid_child_node and
35 e.job_code = f.job_code and
36 e.officeid = '@officeid'
37 order by name
38
39 return
40
41 GO
42 SET QUOTED_IDENTIFIER OFF
43 GO
44 SET ANSI_NULLS ON
45 GO
46
47
48
49
50
View 4 Replies
View Related
Mar 11, 2008
I'm having trouble with this stored procedure, it gets down to the insert and then times out.
Can anyone see why the insert might be wrong?
I did check and at least one of the workshops is open and the @Status is showing it as Open on the client.ALTER PROCEDURE [dbo].[AddNewWorkshopAssigned]
@Workshop_ID int,
@Client_ID int,
@Wait_list bit,
@DateEntered datetime,
@EnteredBy nvarchar(50),
@Status nvarchar(10) OUTPUT
AS
BEGIN
SET @Status = (SELECT
CASE
WHEN (tblWorkshop.Workshop_Status) = 'Canceled' THEN 'Canceled'
WHEN (tblWorkshop.Workshop_Size - COALESCE(COUNT(tblWorkshopsAttended.client_ID),0)) >= 1 THEN 'Open'
ELSE 'Closed' END AS Current_Status
FROM tblWorkshop INNER JOIN
tblWorkshopsAttended ON tblWorkshop.Workshop_ID = tblWorkshopsAttended.workshop_id
WHERE tblWorkshopsAttended.Workshop_ID=@Workshop_ID AND tblWorkshopsAttended.Wait_list = 0
GROUP BY tblWorkshop.Workshop_ID,tblWorkshop.Workshop_Size,tblWorkshop.Workshop_Status)
--Change Workshop Status if NOT canceled.
If @Status <> 'Canceled'
UPDATE tblWorkshop SET
Workshop_Status=@Status
WHERE tblWorkshop.Workshop_ID=@Workshop_ID
IF @Status = 'Open'
INSERT INTO tblWorkshopsAttended (Workshop_ID,Client_ID,Wait_list,DateEntered,EnteredBy)
Values (@Workshop_ID,@Client_ID,@Wait_list,@DateEntered,@EnteredBy)
END
View 3 Replies
View Related
Jan 19, 2007
hi all,
I want to know that how can i write a stored procedure that takes an input param, i.e., a string and returns the count of the matching words between the input parameter and the value of a column in a table. e.g. I have a table Person with a column address. now my stored procedure matches and counts the number of words that are common between the address of the person and the input param string. I am looking forward for any help in this matter. I am using Sql server 2005.
View 1 Replies
View Related
May 12, 2005
Hello,
I'm trying to use the "sp_columns" stored procedure to pull in some
information about a table in my db, but I'm continuing to get the same
error:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_columns' expects parameter '@table_name', which was not supplied.
I'm not sure why, but I've re-written my code several times and can't
figure out just why this error is happening. Here's a snippet of
the code:
Private Function GetDataType(ByVal columnName As String, ByVal table As String) As String
Dim con As New
OdbcConnection(ConfigurationSettings.AppSettings.Get("LiquorLiabilityConnection"))
Dim com As New OdbcCommand("sp_columns", con)
com.CommandType = CommandType.StoredProcedure
Dim param As New OdbcParameter("@table_name", table)
param.OdbcType = OdbcType.VarChar
com.Parameters.Add(param)
param = New OdbcParameter("@column_name", columnName)
param.OdbcType = OdbcType.VarChar
com.Parameters.Add(param)
Dim dr As OdbcDataReader
Dim name As String
con.Open()
dr = com.ExecuteReader() 'THIS TRIGGERS THE ERROR
While dr.Read
name = dr("TYPE_NAME")
End While
dr.Close()
con.Close()
Return name
End Function
Any suggestions would be appreciated.
View 3 Replies
View Related
May 9, 2006
Dear Forum,
I am adding a new column name to my Stored Procedure called HeadlinerID. It is an Int that is 4 characters long. I seem to be putting this in incorrectly in my stored procedure. I have tried it like: @HeadlinerID int(4), and @HeadlinerID int, and both ways I get the error below:
Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’.
Is there a trick to putting in integers in a stored procedure?
Thanks,
Jeff Wood
Boise, ID
CREATE PROCEDURE Item_Insert( @Title varchar(50), @_Date datetime, @Venue varchar(50), @HeadlinerID int(4), @Opener varchar(150), @Doorstime varchar(50), @Showtime varchar(50), @Price varchar(50), @Onsaledate datetime, @Ticketvendor varchar(50), @TicketURL varchar(150), @Description varchar(1000),
)AS
INSERT INTO shows( Title, _Date, Venue, HeadlinerID, Opener, Doorstime, Showtime, Price, Onsaledate, Ticketvendor, TicketURL, Description)VALUES( @Title, @_Date, @Venue, @HeadlinerID, @Opener, @Doorstime, @Showtime, @Price, @Onsaledate, @Ticketvendor, @TicketURL, @Description )GO
View 3 Replies
View Related
Feb 13, 2008
Hi All, I'm a newbie learning windows applications in visual basic express edition, am using sqlexpress 2005 So i have a log in form with username and password text fields.the form passes these values to stored procedure 'CheckUser' Checkuser then returns a value for groupid. If its 1 they are normal user, if its 2 its admin user. Then opens another form called Organisations, and closes the log in form. However when i run the project, and enter a username and password and press ok ti tells me that there is incorrect syntax beside a line. I have no idea, and I'm sure that there is probably other things wrong in there. here is the code for the login button click event: Public Class Login Dim connString As String = "server = .SQL2005;" & "integrated security = true;" & "database = EVOC"
'Dim connString As String = _ '"server = .sqlexpress;" _ '& "database = EVOC;" _ '& "integrated security = true;"
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login_Log_In.Click ' Create connection
Dim conn As SqlConnection = New SqlConnection(connString) ' Create command
Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = conn cmd.CommandText = "CheckUser"
Dim inparam1 As SqlParameter = cmd.Parameters.Add("@Username", SqlDbType.NVarChar) inparam1.Value = Login_Username.ToString Dim inparam2 As SqlParameter = cmd.Parameters.Add("@Password", SqlDbType.NVarChar) inparam2.Value = Login_Password.ToString inparam1.Direction = ParameterDirection.Input inparam2.Direction = ParameterDirection.Input 'Return value
Dim return_value As SqlParameter = cmd.Parameters.Add("@return_value", SqlDbType.Int) return_value.Direction = ParameterDirection.ReturnValue cmd.Connection.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader Dim groupID As Integer
groupID = return_value.Value
If groupID < 0 Then
MessageBox.Show("Access is Denied") Else Dim Username = Me.Login_Username Dim org As New Organisations org.Show() End If
conn.Close()
End Sub The stored procedure code is ok as i know it works as it should, but if it helps the code is: ALTER PROCEDURE [dbo].[CheckUser] -- Add the parameters for the stored procedure here
@UserName nvarchar(50) = N'', @Password nvarchar(50) = N''
ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
IF (SELECT COUNT(*) FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)=1BEGINPRINT 'User ' + @Username + ' exists'
SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@PasswordRETURN (SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)ENDELSE BEGINPRINT 'User ' + @Username + ' does not exist'
RETURN (-1)ENDEND
All help greatly appreciated to get me past this first hurdle in my first application!! CheersTom
View 16 Replies
View Related
Jan 30, 2004
If I run a update stored procedure on my SQLServer It work Fine.
But When I try it in my VB code, it's just do nothing not even an error message.
What I've got to do for being able to Update SQLTable with a stored procedure?
That's my VB code:
Dim objConnect As SqlConnection
Dim strConnect As String = System.Configuration.ConfigurationSettings.AppSettings("StringConn")
objConnect = New SqlConnection(strConnect)
Dim objCommand As New SqlCommand("internUpdate", objConnect)
objCommand.CommandType = CommandType.StoredProcedure
Try
Dim objParam As SqlParameter
objParam = objCommand.Parameters.Add("Id", SqlDbType.Int)
objParam.Direction = ParameterDirection.Input
objParam.Value = InternIDValue
objParam = objCommand.Parameters.Add("Address", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Address.Value.Trim()
objParam = objCommand.Parameters.Add("City", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = City.Value.Trim()
objParam = objCommand.Parameters.Add("ProvinceCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myProvince.SelectedValue
objParam = objCommand.Parameters.Add("PostalCode", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = PostalCode.Value.Trim()
objParam = objCommand.Parameters.Add("Phone", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone1.Value.Trim()
objParam = objCommand.Parameters.Add("Phone2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone2.Value.Trim()
objParam = objCommand.Parameters.Add("Email", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress1.Value.Trim()
objParam = objCommand.Parameters.Add("Email2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress2.Value.Trim()
objParam = objCommand.Parameters.Add("EmploymentStatusCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myStatus.SelectedValue
objConnect.Open()
objCommand.ExecuteNonQuery()
objConnect.Close()
Catch ex As Exception
Exit Sub
End Try
Thanks!!
View 1 Replies
View Related
Mar 31, 2005
I am having a bit of trouble with a stored procedure on the SQL Server that my web host is running.
The stored procedure I have created for testing is a simple SELECT statement:
SELECT * FROM table
This code works fine with the query tool in Sqlwebadmin. But using that same code from my ASP.NET page doesn't work, it reports the error "Invalid object name 'table'". So I read a bit more about stored procedures (newbie to this) and came to the conslusion that I need to write database.dbo.table instead.
But after changing the code to SELECT * FROM database.dbo.table, I get the "Invalid object name"-error in Sqlwebadmin too.
The name of the database contains a "-", so I write the statements as SELECT * FROM [database].[dbo].[table].
Any suggestions what is wrong with the code?
I have tried it locally with WebMatrix and MSDE before I uploaded it to the web host and it works fine locally, without specifying database.dbo.
View 2 Replies
View Related
Jul 10, 2014
All of a sudden hen I script out a Stored Procedure it encloses strings (edit) in Double Quotes?
For example ANDPromo.[Group] IN (''FL_Small'',''FL_Large'')
Also it generates this code that I do not want. I just was Create Procedure...
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_IncentiveReport]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
What options do I need to set?
View 5 Replies
View Related
Jan 4, 2001
Does anyone know how i can send an email from within a stored procedure ? Can i simply use xp_sendmail ?
thanks
View 3 Replies
View Related
Aug 3, 2007
i want to know how can i send emails from SQL stored procedure? is it possible?
View 3 Replies
View Related
Jun 14, 2007
I created a stored procedure (see snippet below) and the owner is "dbo".
I created a data connection (slcbathena.SLCPrint.AdamsK) using Windows authentication.
I added a new datasource to my application in VS 2005 which created a dataset (slcprintDataSet.xsd).
I opened up the dataset in Designer so I could get to the table adapter.
I selected the table adapter and went to the properties panel.
I changed the DeleteCommand as follows: CommandType = StoredProcedure; CommandText = usp_OpConDeleteDirectory. When I clicked on the Parameters Collection to pull up the Parameters Collection Editor, there was no parameters listed to edit even though there is one defined in the stored procedure. Why?
If I create the stored procedure as "AdamsK.usp_OpConDeleteDirectory", the parameters show up correctly in the Parameters Collection Editor. Is there a relationship between the owner name of the stored procedure and the data connection name? If so, how can I create a data connection that uses "dbo" instead of "AdamsK" so I can use the stored procedure under owner "dbo"?
Any help will be greatly appreciated!
Code SnippetCREATE PROCEDURE dbo.usp_OpConDeleteDirectory
(
@DirectoryID int
)
AS
SET NOCOUNT ON
DELETE FROM OpConDirectories WHERE (DirectoryID = @DirectoryID)
View 1 Replies
View Related
Jul 2, 2006
I thought I could just copy over some asp.net code like: System.Web.Mail.MailMessage mailMessage = new System.Web.Mail.MailMessage();
But VS2005 doesn't seem to want me touching System.Web.Mail.
Any ideas?
Thanks,
Allen
View 6 Replies
View Related
Mar 17, 2004
Hi,
I am trying to write a stored procedure in Sql Server that should send an email containing a query result everyday at 2:00 PM. How can I do this?? Im trying to use xp_sendmail but this gives me the following error:
Could not find stored procedure 'xp_sendmail'.
Plz let me know if there is any easy way to handle this.
Thanks a lot
View 3 Replies
View Related
Jan 30, 2014
I have an store procedure and I want to build an email with this store procedure to email me How can I use the email command to incorporate into my sql SP?
View 4 Replies
View Related
May 25, 2007
I have a situation that I need to accomodate as follows:
Customer account table as a bit value as to whether to send an email
For each customer with a true value set I need to run a query against their products table to check whether the reorder value is less than current stock.
if the recordcount for this query is > 0 then I need to send the customer an email using the address in the customer file.
This process needs to be run once per week.
Can anybody offer advice, samples as to resolve this problem
Thanks
View 3 Replies
View Related
Feb 18, 2005
Hi,
i am using xp_sendmail to notify system users when certain actions happen in the database but cannot control the format of the output. I am building a string for the message in the a trigger and then use
exec xp_sendmail @recipients='email address', @Subject='subject',@message=@msg
however in the @msg variable it would make a lot more sense if i could include linebreaks.
I have tried using html tags and setting the outlook installation on the server to send in html format but that doesnt work, the html tags are output as text in the message.
i have tried putting CHAR(13) in the @msg variable as i build it up but that doesnt work either.
does anyone have any ideas please?
View 2 Replies
View Related
May 15, 2007
hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
View 2 Replies
View Related
Oct 24, 2007
hi there,
the subject line says it all. is there a way to send nicely formatted html email from within a stored procedure that returns a dataset?
thanks!
chris
View 2 Replies
View Related
Nov 3, 2007
Everyday morning I email the sql query/stored procedure output results to the users, I was wondering if I can use some kind of t-sql code or DTS packages so that I can automate this process where I want to send the sql/stored proc results in the body of the email.
View 7 Replies
View Related
Sep 20, 2007
Hi Everybody,
I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.
Code Snippet
CREATE PROCEDURE [dbo].[hrDB_SendEmail]
AS
BEGIN
DECLARE @FirstName nvarchar(256),
@LastName nvarchar(256),
@To nvarchar(256),
@ToMgr nvarchar(256),
@Subject nvarchar(256),
@Msg nvarchar(256),
@DateToSend datetime,
@Sent nvarchar(256),
@ReminderID int,
@RowCount int,
@Today datetime,
@Result nvarchar(256)
-- Get the reminders to send
SELECT
@ReminderID = r.intReminderID,
@DateToSend = r.datDateToSend,
@FirstName = e.txtFirstName,
@LastName = e.txtLastName,
@To = e.txtEmail,
@Subject = t.txtReminderSubject,
@Sent = r.txtSent
FROM
(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID
WHERE
(((r.datDateToSend)<20/12/09) AND
((r.txtSent)='False'))
-- Send the Emails
WHILE(LEN(@To) > 0)
BEGIN
EXEC @Result = sp_send_cdosysmail @To, @ToMgr, @Subject, @Msg
END
-- Mark the records as sent
IF @Result = 'sp_OAGetErrorInfo'
BEGIN
SELECT @Sent = 'Error'
END
ELSE
BEGIN
SELECT @Sent = 'True'
END
UPDATE auto_reminders
SET
auto_reminders.txtSent = @Sent, auto_reminders.datDateSent = @Today
WHERE
intReminderID = @ReminderID
END
GO
From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-
how to loop through each record
how to get todays date
whether the struture of the procedure is correct
Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,
Thanks in advance
Ben
View 1 Replies
View Related
Jul 23, 2005
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies
View Related
Apr 28, 2008
Env: Microsoft SQL Server Reporting Services Version 8.00.1042.00,
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
Last week a number of reports that were working fine began rendering incorrectly when sent out in report subscription emails - they work fine when directly rendered using report manager. The weird issues include broken alignment (left instead of right), missing borders, and changed fonts.
I checked the report deployment dates and these reports have not changed since well before the issues arose. I am digging thru the event logs to see what might have changed, but has anyone seen this issue before?
TIA,
-Peter
View 5 Replies
View Related
May 27, 2008
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Jul 31, 2001
Hi Guys,
I have written a piece of code in a stored procedure that builds a string called "filter$" based on fielde in a table.
How do I use this string as my where clause?
in vb I would use:
rs.open filter$,cn
I hope this makes sense and someone can help me
View 2 Replies
View Related
Oct 28, 2004
Hi all,
If I have a query string that is to be stored in a database, for example
Code:
SELECT prod_id, prod_name, prod_desc FROM products WHERE prod_id = 'variable'
how can I put a variable identifier into this string so that when I need to run the query I call it from the database and simply insert the relevant variable in the correct place.
Is there an appropriate way of doing this in MS SQL Server?
Thanks
Tryst
View 1 Replies
View Related
Dec 21, 2007
Hey, i have multiple search strings and I don't want to keep track of the search strings in SQL format.
As in, i don't want to do this anymore:
Select *
From tbl_Name
where FirstName = 'John' or
FirstName = 'Mike' or
FirstName Like 'Jen%' or
...
and the list continues for at least a 100...
what i want to do is store all the search values in a table and then join on that table as a search...
Create Table as tbl_SearchValues
[FirstName] as Varchar(50)
Insert Table tbl_SearchValues values ('John', 'Mike', 'Jen%')
Select *
From tbl_name n Join tbl_SearchValues s
on n.FirstName Like s.FirstName
This doesn't work cause I tried it... but does anyone know a different workaround??
View 2 Replies
View Related
