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 = '|'
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.
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
I want to use xp_sendmail within a transaction as the result of a trigger which is straightforward enough. The problem I have is how best to handle a rollback occuring. For example, an update triggers a mail message being sent to an operator but the transaction then fails and rolls back. The message however has already been sent giving false information. Does anyone have any tips for handling this?
I'm usning xp_sendmail to send e-mails to customers. In the From field of e-mail it shows the user id which runs this process. From: web_admin Can I mask it or change this like FROM: Customer service
