Help Split List Of Email Add Comma For Evry Email
need help
split list of email add comma for evry email
i have tabe "tblLogin" and in this table i have field emall
like this
emall
-----------------------------------------
aaa@hhhh.mm
nnn@hhhh.mm
mmm@hhhh.mm
need to do ilke this
Code Snippet
@list_email = (SELECT emall FROM tblLogin)
--------------------------i get this
-----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm
@recipients = @list_email
Code Snippet
IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] )
BEGIN
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
Date_born AS 'td','' ,
Age AS 'td','' ,
BirthdayToday AS 'td','' ,
BirthdayThisWeek AS 'td'
FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl>
<tr>
<td>name</td>
<td>fname</td>
<td>date</td>
<td>age</td>
<td>aaaaaaaaa</td>
<td>bbbbbbbbbbbbbbb</td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'rrr@iec.co.il',
@copy_recipients='rrrrr@iec.co.il',
@body = @body,
@body_format ='HTML',
@subject ='ggggggggggggggggggggg',
@profile_name ='ilan'
END
ELSE
print 'no email today'
View Complete Forum Thread with Replies
Related Forum Messages:
UDF To Split A Comma Separated List
Hello. I need to write a UDF that would split a comma separated list and return 4 values. I need to return the first 4 values and ignore the commas after that. If there are no commas in the string that's passed then just return the table with empty strings. The UDF should accept 2 inputs. The ntext and a position and return a value based on the position.For example: 1,2,3,textshould createPosition | Value-------------------------1|12|23|34|textand return a value based on the position. If there are more than 3 commas for example1,2,3,This string, though short, contains a commashould createPosition | Value-------------------------1|12|23|34|This string, though short, contains a commaand return a value based on the position. And if there are are less than 3 commas in the string passedFor example: 1,2 or NULL or 2:3.5 or This is a string with no commasshould createPosition | Value ------------------------- 1| (empty string) 2| (empty string) 3| (empty string) 4| (empty string)and return a value based on the position.This is what I wrote so far. CREATE function GetValueFromPosition (@Input nvarchar(4000), @position int)Returns nvarchar(4000)AsBegin -- Declare the return Variable Declare @ReturnValue nvarchar(4000) Select @ReturnValue = LTRIM(RTRIM(member_id)) From dbo.SplitString(@Input, ',') Where position = @position Return @ReturnValueEnd CREATE Function SplitString(@text varchar(8000), @delimiter varchar(1) = ',')-- This function splits a string of CSV values and creates a table variable with the values.-- Returns the table variable that it createsRETURNS @Strings TABLE( position int IDENTITY PRIMARY KEY, member_id varchar(8000))ASBEGIN Declare @index int Set @index = -1 WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter , @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) SET @text = RIGHT(@text, (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text, (LEN(@text) - @index)) END RETURNEND I am trying to modify these according to what I need but its not working. Please help. Thank you.
View Replies !
Email Distribution List
Good Morning, Can you please let me know if it is possible to use a distribution list created in Outlook for sending reports via the automated email delivery system within SSRS? or do I have to set up distribution lists within SSRS?. Thanks for you help. Steve
View Replies !
Mass Email: Text Body Keeps Looping In Each Email
First off, my server uses CDO, the updated version of CDONTS, to send mail. The script works however, it sends the emails out and keeps looping the message body within the text of the email. here's an example: I added fake users to my table called REALTORS. this was the result of the email sent out: <!-- START EMAIL MAIL OUT --> I am an realtor in Florida and I found you from surfing around Poop Title http://www.poop.com I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a "vote" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation. If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories: http://www.realestate321.com/resources/resources.asp Once added you will be placed in a "holding bin" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a "robot" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot. Best Regards, Nick McGirr Real Estate Agent http://www.realestate321.com/ "Buying and Selling Homes in the (321) Area Code."I am an realtor in Florida and I found you from surfing around Haha titleage http://www.hahaha.com I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a "vote" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation. If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories: http://www.realestate321.com/resources/resources.asp Once added you will be placed in a "holding bin" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a "robot" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot. Best Regards, Nick McGirr Real Estate Agent http://www.realestate321.com/ "Buying and Selling Homes in the (321) Area Code."I am an realtor in Florida and I found you from surfing around niclipse . com http://www.niclipse.com I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a "vote" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation. If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories: http://www.realestate321.com/resources/resources.asp Once added you will be placed in a "holding bin" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a "robot" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot. Best Regards, Nick McGirr Real Estate Agent http://www.realestate321.com/ "Buying and Selling Homes in the (321) Area Code."I am an realtor in Florida and I found you from surfing around. I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a "vote" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation. If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories: http://www.realestate321.com/resources/resources.asp Once added you will be placed in a "holding bin" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a "robot" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot. Best Regards, Nick McGirr Real Estate Agent http://www.realestate321.com/ "Buying and Selling Homes in the (321) Area Code." <!--[==-- EMAIL MAIL OUT FINISHED -------> ANY IDEAS ON HOW TO SEND EACH EMAIL TO EACH USER INDIVIDUALLY WHILE USING THE INFORMATION LINKED IN THE DATABASE WITHOUT LOOPING OTHER PEOPLE'S DB INFO IN THE SAME EMAIL? Here's the script below.... <title>Email The List</title> <% nickemail = request.form("sender") subject = request.form("subject") If Len(subject) > 0 Then dsn="Provider=SQLOLEDB;Data Source=sql.string.com, etc ect" Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open dsn Set RS = Server.CreateObject("ADODB.Recordset") SQL = "SELECT title, url, email FROM Realtors" RS.open SQL, conn Do While NOT RS.eof strTXT = strTXT & "I am an realtor in Florida and I found you from surfing around" If Len(RS("title")) > 0 Then strTXT = strTXT & vbcrlf & vbcrlf & RS("title") & vbcrlf strTXT = strTXT & RS("url") & vbcrlf & vbcrlf Else strTXT = strTXT & "." & vbcrlf & vbcrlf End If strTXT = strTXT & "I have a real estate site and I am interested in exchanging links with your site. This is benefitial to you and I because each external website that links to your page counts as a ""vote"" for your website. The more links to your site, the better search engine placement you will recieve in google (it's called Google PageRank)...which ultimately means more exposure. It's a win/win situation." &vbcrlf &vbcrlf strTXT = strTXT & "If you are interested, you may add your website to my link directory at the address below (it's all automated). Make sure to pick your correct category and sub-categories:" &vbcrlf strTXT = strTXT & "http://www.realestate321.com/resources/resources.asp" & vbcrlf strTXT = strTXT & "Once added you will be placed in a ""holding bin"" waiting to be approved. Your site will be approved when I find a link on your website to my website. My website will send out a ""robot"" once a month to make sure that my link is still on your site. If it's not understandably, your link will be removed from my page by that robot." & vbcrlf &vbcrlf strTXT = strTXT & "Best Regards," & vbcrlf & vbcrlf strTXT = strTXT & "Nick McGirr" & vbcrlf strTXT = strTXT & "Real Estate Agent" & vbcrlf strTXT = strTXT & "http://www.realestate321.com/" & vbcrlf strTXT = strTXT & """Buying and Selling Homes in the (321) Area Code.""" set objMessage = createobject("cdo.message") set objConfig = createobject("cdo.configuration") ' Setting the SMTP Server Set Flds = objConfig.Fields Flds.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 Flds.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost" Flds.update Set objMessage.Configuration = objConfig objMessage.To = RS("email") objMessage.From = nickemail objMessage.Subject = subject objMessage.TextBody = strTXT 'objMessage.HtmlBody = strHTML objMessage.fields.update objMessage.Send RS.movenext Loop Conn.close set Conn = nothing set objMessage = nothing set objConfig = nothing ShowThanksMsg Else End If ShowForm Sub ShowForm %> <% Session("department")="home" %> <center> <form action=email.asp method=post> <table border=0 cellpadding=4 cellspacing=2> <table> <tr><td><font class=tbody><b>From:</b></font></td><td><input type=text name=sender value="me@me.com"></td></tr> <tr><td><font class=tbody><b>To:</b></font></td><td><font class=tbody>Brevard County Emails</font></td></tr> <tr><td><font class=tbody><b>Subject:</b></font></td><td><input class=inputfield type=text name=subject size=22></td></tr> <tr><td colspan=2><input type=image align=right src=/_images/submit.gif border=0 name=B1></td></tr> </table> </form></center> <!-- <tr><td colspan=2><font class=tbody><b>Comments:</b></font><br><textarea class=inputfield rows=3 maxlength=100 name=message cols=40></textarea></td></tr> --> <% End Sub Sub ShowThanksMsg() response.write "<center><font size=5><b>Message Sent!</b></font><br><br>" response.write "your message has been delivered to all members." End Sub %>
View Replies !
Separate Email List For Errors
Hello All, I have an email subscription set up in Report Manager. Sometimes, when report execution fails, is there a way to sent the failure notification to a separate email list? Something like: on errror, send to abc@abc.com on success send to xyz@xyz.com? Thanks Phewa
View Replies !
SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule
Hi, I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly. So, is this possible using data driven subscriptions? Scenario is: 1. User enters parameter used for query, as well as email addresses. 2. Report is generated and displayed on screen. 3. Report is emailed to addresses specified by user. Any tips on how to get this working? Thanks Mark Smith
View Replies !
Help Generate A List Of Hours + Mintes Evry 5 Minte
need help Generate a list of hours + mintes evry 5 minte need create new table of hours + mintes evry 5 minte like this round_time time_list -------------------------------------------------------------------------- 0 00:00 0 00:05 0 00:10 ................................. ........................................ 0 00:55 1 01:05 1 01:10 ..................................... ..................................... 1 01:55 2 02:00 2 02:05 .................................. ....................................... ........................... 23 23:50 23 23:55 TNX
View Replies !
Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter. However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance). For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed). Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac How can I accomplish this? I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell. Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View Replies !
SELECT WHERE (any Value In Comma Delimited List) IN (comma Delimited List)
I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist. Here is an approach I am trying: TABLE Events(EventID int, Categories varchar(200)) EventID Catetories -------------------------- 1 ‘6,8,9’ 2 ‘2,3’ PROCEDURE ListFilteredEvents @FilterList varchar(200) -- contains ‘3,5’ AS SELECT EventID FROM Events WHERE (any value in Categories) IN @FilterList Result: EventID ---------- 2 How can I select all records where any value in the Categories column matches a value in @FilterList. In this example, record 2 would be selected since it belongs to category 3, which is also in @FilterList. I’ve looked at the table of numbers approach, which works when selecting records where a column value is in the parameter list, but I can’t see how to make this work when the column itself also contains a comma delimited list. Can someone suggest an approach? Any examples would be greatly appreciated! Gary
View Replies !
Split Up Comma-delimited Field
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas. Any ideas how I could do this in T-SQL?
View Replies !
Please - Split Select From Table To Comma 1,2,3,...
help please i have this table number_row fld_index vtext ---------------------------------------- 1 101 a 2 101 b 3 101 c 4 102 d 5 102 e 6 102 f 7 103 g 8 103 h 9 103 i ...... ...... .... now i need to do this SELECT fld_index FROM table_index GROUP BY fld_index ---------------------------- and i get 101 102 103 104 how split this select and do this in('101' ,'102','103','104') ------------------------------ Code Snippet DECLARE @aaa set @aaa =(SELECT fld_index FROM table_index GROUP BY fld_index) set fld1 = CASE WHEN fld1 in(@aaa ) then '*' else fld1 end ---- need to split the @aaa ,'101' ,'102' , '103','104' , ---------------------------------------------------------------------------------------------------------------------------- instead of this update update [dbo].[tb_pivot_big] set fld1 = CASE WHEN fld1 in('101' ,'102','103','104') then '*' else fld1 end , fld2 = CASE WHEN fld2 in('101' ,'102','103','104') then '*' else fld2 end , fld3 = CASE WHEN fld3 in('101' ,'102','103','104') then '*' else fld3 end , fld4 = CASE WHEN fld4 in('101' ,'102','103','104') then '*' else fld4 end , fld5 = CASE WHEN fld5 in('101' ,'102','103','104') then '*' else fld5 end from [dbo].[tb_pivot_big]
View Replies !
SQL Comma Seperated List
Is there a way to return a comma seperated list in a query?For example if I have this simple querySelect member_name From members It returns all the members in diff rows (lets assume we have 3)So the result will bemember_name--------------name1name2name3Instead of this I need to have the result in one row with values seperated by commas.member_name---------------name1,name2,name3I hope I am clear enough. Any help would be appreciated. Thank you.
View Replies !
Creating A Comma Delimited List
Hi, I have a complex query where each row in the final dataset is a product. However each product has a number of authors associated with it. What I would like to do is have a query/subroutine join the authors to the product, as a string: ProductID Title Authors 1 The Sacred and the Profane John Rieggle, George Alexi 2 Dancing in the Dark Dan Brown, Peter Kay, Paul Dwebinski Products Table ============== ProductID Title Authors Table ============= AuthorID Name Product Authors Table ===================== AuthorID ProductID Is this at all possible? Thanks jr.
View Replies !
Inner Join On A Comma Delimited List?
Hi, I have the following query: SELECT * FROM News INNER JOIN Newsletters ON News.ID = Newsletters.fkNewsID My problem is that fkNewsID can contain a comma delimited list of various IDs. Is there a way to properly do the join in this case?
View Replies !
How To Use Comma Separated Value List In The Where Clause?
How to use comma separated value list in the where clause? I would like to do something like the following (Set voted = true for all rows in tblVoters where EmpID is in the comma separated value list). update tbl_Voters set voted = true where EmpID in @empIdsCsv Where, @empIdsCsv = €™12,23,345,€™ (IDs of the employees) Since the above is not possible I have done the following dynamic query: -- Convert the comma separated values to conditional statement like EmpID = {id} or EmpliD = {id}€¦ set @empIdsCsv = 'EmpID=' + substring(@empIdsCsv , 0, len(@empIdsCsv )) -- Remove trailing comma set @empIdsCsv = replace(@empIdsCsv , ',', ' or EmpID=') declare @markVoters varchar(8000) set @markVoters = ' update tbl_Voters set voted = true where €™ + @empIdsCsv --Execute the dinamic query exec (@markVoters) The above code generates the following dynamic query: update tbl_Voters set voted = true where EmpID= 12 or EmpID=23 or EmpID=345 The obvious drawback here is the performance and the limitation of the dynamic query length (8000 chars). Can someone suggest a better solution with the ability to use comma seperated values in the where clause?
View Replies !
Help With Subquery: Comma-delimited Id List To Value
Hello any MS SQL experts out there! please help if you can. i'm trying to run a subquery within a query to keep myself from having to loop over the original query on display and then run additional queries to get the further info. here's the setup. i have two tables: persons table column: name (varchar) column: vehicleids (varchar) vehicles table column: id (int pk) column: vehiclename (varchar) - The persons table is a list of peoples' names and what kind of vehicle/s they own. - The persons.vehicleids field is a comma-delimited list of one or more integers which correspond to the vehicles.id field. - The vehicles table contains a list of vehicles like car, bicycle, motorcycle, etc, distinguished by the vehicles.id field. The result i want returned by the query is: NAME - VEHICLES Joe Somebody - car,bicycle Sheila Johnson - van,pogostick,motorcycle John Nobody - skateboard,car The query i'm trying to run to get this result is: Code Snippet SELECT pe.name, ( SELECT ve.vehiclename FROM vehicles ve WHERE CAST(ve.id AS VARCHAR) IN (pe.vehicleids) ) AS vehicles FROM persons pe ORDER BY pe.name It returns the persons names in the first column, but only returns vehicle names in the second column when there's a single id in the persons.vehicleids field. if there's more than one integer in the field, it returns an empty string. Can anyone tell me what I'm doing wrong? I do have the option of table restructuring if its necessary, but I'm not looking for a stored procedure solution or a temp table solution. Any takers? I would be in the kharmic debt of anyone providing a workable avenue. Thank you, Tyler
View Replies !
Matching Comma Separated List
I have the following SQL Query: SELECT user_profiles.userFirstName, user_profiles.userInitial, user_profiles.userLastName, user_types.userTypeDesc, user_profiles.userOfficeIDs, user_profiles.userEmail FROM user_profiles INNER JOIN user_types ON user_types.userTypeID = user_types.userTypeID The field userOfficeID contains a comma separated list of values such as "1,2" to identify that the user is in both the NJ and NY office. Table office_locations OfficeID officeState 1 NJ 2 NY 3 CT I would like the output to be something similar to: Name Office userOfficeIDs value John Smith NJ 1 Mary Smith NJ/NY 1,2 Jim Smith NJ/CT 1,3 Mike Smith NY 2 Any direction would be appreciated. Thanks
View Replies !
Query Result As Comma-separated List
Hi,I'n in an environment where I cannot make stored procedures. Now I needto make a query with a subquery in the SELECT part which gives a commaseparated list of results:SELECTp.id,listFunction(SELECT name FROM names WHERE name_parent=p.id) AS'nameList'FROM projects AS pThis query should return something like:1, "john,mike,petra"2, "bob,carl,sandra,peter,etclistFunction is (of course) not (yet) defined. Is this possible withoutthe use of stored procedures?Mike
View Replies !
How Do I Create An In-line Comma Seperated List
Scenario:Table 1 (a id, b name)Table 2 (a FKid, d value)A standard join on a gives me something like:a1 b1 d1a1 b1 d2What I want is:a1 b1 d1,d2I can easily do this with a function or cursor, but is is somewhatslow, and I need to do this a lot and I don't really want to have tomaintain tons of functions or cursors.Thoughts?
View Replies !
Comma Delimited List Of IDs To A Recordset From 2 Tables...
Have 2 tables in SQL Server 05 DB: First one is MyList user_id -> unique value list -> comma-delimited list of user_ids notes -> random varchar data Second one is MyProfile user_id -> unique value name address email phone I need a stored proc to return rows from MyProfile that match the comma-delimited contents in the "list" column of MyList, based on the user_id matched in MyList. The stored proc should receive as input a @user_id for MyList then return all this data. The format of the comma-delimited data is as such (all values are 10-digit alphanumerics): d25ef46bp3,s46ji25tn9,p53fy76nc9 The data returned should be all the columns of MyProfile, and the columns of MyList (which will obviously be duplicated for each row returned). Thank you!
View Replies !
Showing A Comma Seperated List In A Field
I want to have a query that selects member information as well as a list of all the groupid's that the member is a part of. I was trying to do a subquery: select member.username, (select groupid from membergroup where membergroup.memberid = member.memberid) as groups from member where memberid=3 but as soon as the member got more than one group record within the membergroup table it would produce an error >Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. How can i select a comma seperated list of all the groupid's the member is a part of within a single query? Thanks!
View Replies !
Using Comma Seperated List Of Number S In An IN Statement
am using sql server 2005 , and am passing sql server a comma seperated list of numbers that I want to use in an IN statement so in the stored proc @internal varchar(100) (e.g @internal gets populate with '32,50') then the statement select count (contactid) from contacts where contactid in(@internal) but I am getting an error Conversion failed when converting the varchar value '32,50' to data type int. If as an experiment I hard code the 32,50 into the sp it works fine , I have tried using CAST to no use , the field contactid is an integer , but I would have thought sql server would be ok having a comma seperated varchar to compare to an int , cant seem to find a solution , any help appreciated thanks
View Replies !
How To Select Multiple Rows As Comma Separated List?
Hi, I have a table of users, a table of categories, and a many-to-many table linking users to categories. My problem is that I want to select all the users with an extra column containing a comma-separated list of the categories they belong to. Here is a stripped-down version of the table fields: tbl_User UserId, Email tbl_Category CatId, CatName tbl_User_Category UserId, CatId I have tried using the coalesce function to build a string, but can only get this to work for one row at a time: DECLARE @list nvarchar(100) SELECT @list = COALESCE(@list + ', ', '') + CAST(CatId AS varchar(4)) FROM tbl_User_Category WHERE UserId = @UserId SELECT @list as List Any ideas on how to add to this to get it to do each row in tbl_Page? Or am I attacking this from the wrong angle????? Any help would be fantastic! thanks, Rob
View Replies !
Comma Delimited List Update Stored Procedure
I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list. Example: name1 = value1, value2, value3, etc. name2 = value1, value2, value3, etc. name3 = value1, value2, value3, etc. Here is how I wrote my stored procedure: CREATE PROCEDURE dbo.Sp_Update_ABR_Record ( @abrID int, @ddo varchar(50), @ay varchar(50), @strategy varchar(10), @budgacct varchar(10), @budgobj varchar(10), @origamt varchar(50), @incrdecr varchar(50), @review char(10), @abrdetlsID varchar(50) ) AS UPDATE DIM_ABR_REQ_HDR SET ABR_review = @review WHERE ABR_ID = @abrID UPDATE DIM_ABR_REQ_DETLS SET ABR_DETLS_DDO = @ddo, ABR_DETLS_AY = @ay, ABR_DETLS_STRATEGY = @strategy, ABR_DETLS_BUDG_ACCT = @budgacct, ABR_DETLS_BUDG_OBJ = @budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @incrdecr) WHERE ABR_DETLS_ID = @abrdetlsID GO The second update is where the comma delimited list needs to be handled. The first update is only updating one field once. Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this? Thanks for any help. -D-
View Replies !
Pass Comma Delimited List To Stored Procedure
I am trying to pass a comma delimited llist of names to a stored procedure. I am getting a syntax error and I can't seem to figure out why. When i plug the names by hand into my select statement in query analyzer it all works fine. Just a little background so i don't have to put all the code in... the list of name is built dynamically. Below are all the code snippets. Thanks for your help in advance. The is the list of names and the call to the stored procedure: Code: employeeList = 'name1','name2',name3','name4','name5' SQL="sp_REVIEW @ACTION='lde', @CURRENT_USER='" & currentUser & "', " &_ "@EMPLOYEE_LIST='" & employeeList & "'" Here is the stored procedure Code: SELECT ww.ORACLE_USER_NAME, ww.LAST_NAME + ', ' + ww.FIRST_NAME as employeeName, ww1.DIVISION + ' - ' + ww1.COST_CENTER + ' - ' + ww1.COST_CENTER_DESC as department FROM WHOS_WHO.dbo.HR_PEOPLE_V ww LEFT JOIN WHOS_WHO.dbo.HR_DEPARTMENTS_V ww1 ON ww.DEPARTMENT_ID = ww1.ORG_ID WHERE ww.SEARCHABLE = 1 AND ww.ORACLE_USER_NAME IS NOT NULL AND ww.PERSON_TYPE = 'Employee' AND (ww.ORACLE_USER_NAME IN (@EMPLOYEE_LIST) OR ww.DEPARTMENT_ID IN (SELECT ud.department_id FROM USER_DEPARTMENT ud WHERE ud.nt_id = @CURRENT_USER)) ORDER BY ww.LAST_NAME, ww.FIRST_NAME
View Replies !
Transforming Comma-delimited List Row Data To Column
Hi, I have 2 Tables Table 1, Row 1 1. Id = 1 2. GraphPoints = 023, 045, 078 (text - data type) I need to move data to Table 2. Table 2 should have 1st row 1. Id = 1 2. GraphPoint = 023 (float data type) 2nd row 1. Id = 1 2. GraphPoint = 045 (float data type) and so on How do I do that? Thanks.
View Replies !
Transforming Comma-delimited List Row Data To Column
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below ID Products -------------------------------------------------------------------------- 1 12, 45 2 10, 20 and I would like to have results as ID Products -------------------------------------------------------------------------- 1 12 1 45 2 10 2 20 I would appreciate if someone could offer me some guidance here.
View Replies !
Function To Create Comma Separated List From Any Given Column/table.
Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris
View Replies !
Email
Quick question ..does DatabaseMail work in sql server express 2008.And if not ... is there a simple work around / solution to get thesame effect .. ie to send email out from sql expressRegardsRob
View Replies !
SQL Email
I want to write a stored procedure that will email Sales Associates and notify them if a customer has had a return number issed for return of defective product. I wanted to do this daily. I am using SQL Server 2000. Is there a way to get SQL to send an email without using Microsoft Exchange? I have a network that is using Workgroup mode and not domains. I can provide SQL a mailbox on our Linux mail server to send the mail.
View Replies !
Email
Hi all me again I gotta question, the Sr executive asst has a number of reports that gets emailed to different department, only problem is those departments are on a totally different server. I am guessing that we need to start SQL AGENT Emailing MAPI to do this for us, of course I could be wrong. This is one of the stored procedures that creates one of the reports CREATE PROCEDURE createSecurityListtobeEmailed AS SELECT SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type INTO SecurityList FROM SecurityListX GROUP BY SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type HAVING (((SecurityListX."Last Name") Is Not Null)) GO
View Replies !
Email Through SQL
I am trying to setup my sql to send automatic emails to different companies but I am not doing so well. The user puts an email address in the database and then presses a button (command button) that will execute a stored procedure xp_sendmail to a recepient. I need to know how to include read receipts and delivery receipts into the stored procedure, because right now its set up in his outlook as a rule to do this and I want to set it up in sql. The user deals with companies and these companies have several contacts with several email addresses which sql will store in a seperate table.
View Replies !
Email
How can I implement the functionality of sending mail with the help of SP or DTS? Subhasish
View Replies !
Sql Email
I have created a dts email task to attach an externally created file. Outlook is installed on the local server which we use to process email. The problem is that email messages appear in the sent items folder in outlook as unsent items (Envelope Closed icon) If i manually forward the email under outlook everything works fine. Any Ideas Sql 2000 sp4 Outlook 2000 Thanks in advance Mike Osbourn
View Replies !
Email From A Job
Trying to get a job step to send an email with an explaination in certain failure conditions. The problem is I want the recipient to be the job's operator. don't know of a way to accesss the job's operator email address cleanly from inside a step Any ideas?
View Replies !
Email
HI, I have sucessfully setup email on SQL 2000 using Exchange. It works fine with xp_sendmail but when I test the email account on operator it tells me that the mail was sent successfully but I never get the email? Help?? Thanks!!
View Replies !
DTS- Email How TO
IF I HAVE ONE OR MANY PACKAGES HOW DO SEND AN EMAIL TO MY BOSS IF IT BLOWS UP?? ex. INPUT IS MY TRUNCATE STEP, CONNECTION 1 IS MY ODBC, CONECTION 2 IS WHERE THE RESULT WILL BE PLACED INTO. AT ANY ONE OF THOSE STEPS, It BLOWS HOW TO SEND AN EMAIL. THE EMAIL IS ALREADY SETUP BECAUSE I TESTED IT IN THE QUERY ANALYZER WITH THIS STATEMENT ( EXEC xp_sendmail @recipients = 'robertk;laurac', @message = 'The master database is full.', @copy_recipients = 'anned;michaels', @subject = 'Master Database Status') AND IT WORKS.
View Replies !
Email From SQL
How do i get the email to be send automatically from SQL My SQL Server is configured so if sends an email after running a job. The email is been succesfully sent by SQL, but is not atually sent until you login and start up Microsoft Outlook and client on Send and Receive. Surely there is a way to get SQL to automatically send the message. I have got outlook to send message immeditally. My mail server is SendMail - SCO Open Server 5.06 Thanks David
View Replies !
SQL Email
I've setting up SQL email for alerts and I've got Outlook Express working with our SMTP server. But when I try testing email to an operator, I'm getting a "Error 22022: SqlServerAgen Error: The SQLServerAgent mail session is not running, check the mail profile and/or the SQLServerAgent service startup account in the SQLServerAgent Properties dialog." I checked the archives of this list and found an identical discussion, but the person said to check the properties of SQL Server Agent and then look at Mail. Is there supposed to be a Mail tab or is it the "Mail Session" box under the General tab (where I have the Mail Profile name). Thanks for any help. Ed
View Replies !
|
TRACKER
