The @IMGRPNAME, if it is two words, the second word is not a weblink. ANy idea? @IMGRPNAME is declare as varchar(1000)
****************************
Set @IMGRPNAME = (select IMGrpname from IM where IMID = @IMID2)
Set @IMEmail = (select IMEmail from IM where IMID = @IMID2)
Set @IMID3 = (select IMID from IM where IMID = @IMID2)
SET @MsgText = ' has been without a last resort implementer for the past 24 hours. Please click here to assign a last resort implementer: http://ssx_emps.aspx?imid=' + @IMID3 + '&imgrpname=' + @IMGrpName
SET @FullText = @IMGRPName + @MsgText
EXEC master.dbo.xp_sendmail
@recipients = @IMEmail,
@Message = @FullText,
@Subject = 'SAMS Implementation Group needs attention'
I looked online and couldn't find anything to help me make this change. I want to change the default URL for reporting services to another url. Is this possible? Any assistance would be greatly appreciated.
I have setup SSRS 2000 and gotten it to work but I am having trouble with SSRS 2005. I can't access to reportserver anywhere on the network. The only way to get to reportserver is termserv into the server and hit it with http://localhost/reportserver The server is Windows 2003 server Standard Ed. running SQL 2005 SP2 and Sharepoint Portal Server 2007. Can somebody please help? Thank you.
I am trying to use xp_sendmail with no luck. My SQL mail works fine and I am logged in as sa. Here is the csript that I am using. The script works on my local box(which is running Win 2K Advanced Server and SQL 7). It will not work on my production boxes. Any suggestions?
I am using xp_sendmail in a stored procedure.I need to update the fields after the mail sent. Is there a way to capture the errors or server messages that occurs. I am using the following code.....
If @send_Mail=0 Begin update MasterleadPool/*Updates the MasterleadPool table once the mail sent */ Set EmailSent='Y', Dateout=getdate() where AssToID=@AssToID and EmailSent='N' and AssToFlag='Y' End
I used @@error to capture the error but it does'nt work. I got server message as follows.... So the problem is it should'nt go to update statement if there is any type of errors in sending a mail..
Server: Msg 17903, Level 18, State 1, Line 0 MAPI login failure.
However in doing this, it never works (go figure or I wouldn't be submitting this). I have defined the variable @EMAIL_ADDRESS_LNK appropriately to reference a field in a table and I'm positive that the value in the field is valid.
What am I missing? If this is not possible, is there a workaround. Thanks in advance for any assistance you can provide.
How robust is xp_sendmail? I would like to roll thru 10 - 20 thousand records, strip the email and send a newsletter...
I'm a definite newby when it comes to mail servers (and how they interact with SQL Server 7.0)...so I'm not sure whether this type of processing would crash/stall the server.
can xp_sendmail handle this type of processing?
I appreciate your help,
p.s. can u point me to any good articles on the subject?
I have a query that works on its own, however when I put it into xp_sendmail it fails. It appears that the "set" command does not work. Here is the query. Any suggestions? Can you use variables within xp_sendmail @ query section?
EXEC master.dbo.xp_sendmail @recipients = 'Richard Peoples', @subject = 'The following Budget Checked items need attention.', @query ='DECLARE @A1 CHAR (20) DECLARE @A2 CHAR (2) SET @A1 = 'Yvette Palomo' SET @A2 = 'N' IF (select COUNT (*) from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK where RETURN_TO_ANALYST = @A1 AND BUDGET_CHECK_CLEAR= @A2) > 0 begin select A.JOURNAL_ID, A.PROJECT_ID, A.ACCOUNT, B.XLATLONGNAME from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK A INNER JOIN FSPROD75.dbo.XLATTABLE B ON A.BCM_LINE_STATUS = B.FIELDVALUE where (((A.RETURN_TO_ANALYST)= @A1) AND ((A.BUDGET_CHECK_CLEAR)=@A2)) AND (B.FIELDNAME = "BCM_LINE_STATUS") end'
I'm using XP_Sendmail on an NT Server and it works fine. We have a new Windows 2000 server, which the SQL Mail has been set up correctly, and the test passes.
When I use execute xp_sendmail in the query analyzer on the 2000 server, it just sits there and processes for over an hour, without any error messages.
select @MB_Free = MB_Free from #FreeSpace where Drive = 'J'
if @MB_Free < 550 exec master.dbo.xp_sendmail @recipients = 'test@yahoo.com', @message = 'Running low on free space'
I can run it as a job step. However, the problem is that we don't have Microsoft Outlook to run xp_sendmail proc to get notify when the free space on the drive is low. I can run mailsend which is os command and add that as a job step. But I don't know how to combine the code above and mailsend, since one is the t-sql and the other one is command script. Any help is appreciated.
I have recently added columns to a table that is part of a xp_send mail script. meaning i run a query off of the table that had the columns added. I am now getting the error: failed with operating system error 32
I have no idea what this means. This is a copy of the script. exec xp_sendmail 'dionne, jim;eddens, david;Wiggs, Alexander;Miller, Debbie;conmdi', '(scrbbususcnc01) Failure 322 Load', @Attachments = '322Error.txt;\scrbbususcnc01archive322msg322M SG.txt;', @query = 'Use [maersk data warehouse] create table #Duplicate_Records (Equipment_Number varchar(10) ,Activity_Date varchar(6) ,Activity_Time varchar(4) ,Sighting_Code varchar(2) ,CountOfEquipment_Number int) insert into #Duplicate_Records SELECT STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code, Count(STG_INTERMODAL_322MSG_TBL.Equipment_Number) AS CountOfEquipment_Number FROM STG_INTERMODAL_322MSG_TBL GROUP BY STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code HAVING (((Count(STG_INTERMODAL_322MSG_TBL.Equipment_Numbe r))>1))
insert into #None SELECT DISTINCT STG_INTERMODAL_322MSG_TBL.* FROM [#Duplicate_Records] INNER JOIN STG_INTERMODAL_322MSG_TBL ON ([#Duplicate_Records].Sighting_Code = STG_INTERMODAL_322MSG_TBL.Sighting_Code) AND ([#Duplicate_Records].Activity_Time = STG_INTERMODAL_322MSG_TBL.Activity_Time) AND ([#Duplicate_Records].Activity_Date = STG_INTERMODAL_322MSG_TBL.Activity_Date) AND ([#Duplicate_Records].Equipment_Number = STG_INTERMODAL_322MSG_TBL.Equipment_Number) ORDER BY STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code;
create table #Real (Equipment_Number varchar(10) ,Activity_Date varchar(6) ,Activity_Time varchar(4) ,Sighting_Code varchar(2) ,CountOfEquipment_Number int) insert into #Real SELECT [#None].Equipment_Number, [#None].Activity_Date, [#None].Activity_Time, [#None].Sighting_Code, Count([#None].Equipment_Number) AS CountOfEquipment_Number FROM [#None] GROUP BY [#None].Equipment_Number, [#None].Activity_Date, [#None].Activity_Time, [#None].Sighting_Code HAVING (((Count([#None].Equipment_Number))>1));
SELECT STG_INTERMODAL_322MSG_TBL.* FROM [#Real] INNER JOIN STG_INTERMODAL_322MSG_TBL ON ([#Real].Equipment_Number = STG_INTERMODAL_322MSG_TBL.Equipment_Number) AND ([#Real].Activity_Date = STG_INTERMODAL_322MSG_TBL.Activity_Date) AND ([#Real].Activity_Time = STG_INTERMODAL_322MSG_TBL.Activity_Time) AND ([#Real].Sighting_Code = STG_INTERMODAL_322MSG_TBL.Sighting_Code) ORDER BY STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code;
drop table #Duplicate_Records drop table #None drop table #Real', @subject = '(scrbbususcnc01) Failure 322 Load' , @Attach_Results = true, @width = 3000, @Separator = '|'
it gives error saying that @table_var must be declared. even if i use temporary table, the message i get is "cannot reference object in tempdb database."
I want to send a reslut as a xls file. so I did it throug BCP. The same time i need to send file in mail. Here i am stucked. Could any one please advise me to solve this problem?
when creating a sp in SQL 2K that uses xp_sendmail i get a messagesaying:Cannot add rows to sysdepends for the current stored procedure becauseit depends on the missing object 'xp_sendmail'. The stored procedurewill still be created.does this mean that only dependencies involving xp_sendmail will notbe stored? the other dependencies appear to be fine. i'm not surewhy sql would even bother telling me this info. thoughts?
I migrated dts pckg into ssis . In that pckg i have one activex script for xp_sendmail. exec maseter xp_sendmail, @recipients='abc@gmail.com', @query=@sqlsrd, @subject='member load validation', @attach_results=true,@width=250
above code run in ssis or i have to change if i have to chang then tell me where should i have to change.
I'm using SQL 2000 and would like to send a generated email using this stored procedure: select Libraryrequest.LoanRequestID, Titles.Title, requestors.fname+ ' ' + requestors.lname as [Name], libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedatefrom libraryrequestjoin requestors on requestors.requestorid=libraryrequest.requestoridjoin Titles on Titles.Titleid = Libraryrequest.titleidwhere duedate < DATEADD(day, DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101') I know I need to go to Management, SQL Server Agent, Jobs, New Job. Do I put the stored procedure in the descriptions part? After that I'm lost what do I do.
When excuting the xp_sendmail in the Query analyzer, my mail gets send. When doing this in my code it doesn't work eventhough I explicitly use startmailn then xp_sendmail and then sp_stopmail? I think this is a problem of user rights? I gave my user as parameter but even then it didn't work. Any suggestions? I'm pretty sure, the used code is correct.
I am using xp_sendmail to send mail messages from SQL Server Everything is ok on the development box, but on the production box , Am getting "xp_sendmail: failed with mail error 0x80004005"
Has anyone else noticed that if you create a non-existing file as an attachment using xp_sendmail in SQL Server 2000, it does not create a copy of that file on the Hard Drive, nor does it format the attached file sensibly if you attach it as a .csv file ?
I am using the procedure below :-
CREATE PROCEDURE mailtest AS
declare @sql varchar (255)
SELECT PERSONID, FORENAME, SURNAME INTO ##TEMP FROM PERSON
In the example above, the file MARKTEST.CSV does not currently exist, but the procedure should create it, put it in the root of C: and e:mail it, as it did when it ran under SQL Server 6.5. However, under 2000 it now doesn`t put a copy on the Hard Drive and it formats the .csv file in a very odd manner.
I have an app that emails automatically from an ASP page - it works fine but for one specific user I get the error message "MSG 17914, Level 18, State 1, line 0 - Unknown recipient: Parameter '@recipients', recipient 'nameofuser'" where 'nameofuser' is of course, the name of the user. Am I missing something? What's different about this guy that he can't use xp_sendmail? HELP!
I have a table of records which includes the field 'owner'. What I want to do is send each owner a list of their records using xp_sendmail. Is there anyway of doing this? The actual selection of the records is pretty much straight forward.... SELECT * FROM [myTable] GROUP BY [owner]
But how do I go about looping through the owner groups and send an email for each one?
Goal--I want this script to go through some tables and look at some equipment ID's and whoever has equipment assigned to them I want to send an e-mail to them telling them to bring in the equipment for inventory.
Problem--On line 28 I set my xp_sendmail variable "@query" equal to a select statement. Inside that select statement I refer to a variable @USERNAME from outside of the query. I get a must declare variable error. Is there any way to refer to a variable from outside this select statement?
Any suggestions?
Thanks!!!
SET NOCOUNT ON go PRINT 'Determining which users to email ...' PRINT ' ' go DECLARE @USERNAME varchar(30) DECLARE USERNAME_CURSOR CURSOR FOR select DISTINCT A.UserName from tblMISFixedAssetTable2 A, tblMISFixedAssetTable B where A.BarCodeID = B.BarCodeID and A.UserName is not null and B.MobileEquipment = '-1' and A.LastUpdate = (Select Max(C.LastUpdate) FROM tblMISFixedAssetTable2 C WHERE C.BarCodeID = A.BarCodeID)
OPEN USERNAME_CURSOR FETCH NEXT FROM USERNAME_CURSOR INTO @USERNAME WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN SELECT @USERNAME = RTRIM(@USERNAME) exec master.dbo.xp_sendmail @recipients = @USERNAME, @subject = 'Inventory 2002', @message = 'Please bring in your Portable/Mobile Equipment', @query = 'SELECT A.BarCodeID, B.ItemCategory, B.ItemDescription from MISAsset.dbo.tblMISFixedAssetTable2 A, MISAsset.dbo.tblMISFixedAssetTable B where A.BarCodeID = B.BarCodeID and A.UserName = @USERNAME and B.MobileEquipment = "-1" and A.LastUpdate = (Select Max(C.LastUpdate) FROM MISAsset.dbo.tblMISFixedAssetTable2 C WHERE C.BarCodeID = A.BarCodeID)' EXEC ("PRINT 'Emailing the user * " + @USERNAME + " with inventory items.*'") END FETCH NEXT FROM USERNAME_CURSOR INTO @USERNAME END DEALLOCATE USERNAME_CURSOR PRINT ' ' PRINT 'Done!' PRINT ' ' go
