Variables And Xp_sendmail

May 9, 2002

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

View 1 Replies


ADVERTISEMENT

Xp_sendmail Variables

Oct 16, 2006

Is there a way where I can put variables into the subject line of an xp_sendmail execution? I would like to take a column out of the inserted table and put it into the subject line. The only solution I can find is @query which cannot use the inserted table.

View 1 Replies View Related

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

Sep 4, 2006

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

João





View 8 Replies View Related

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

Jan 24, 2006

Hi,

I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"

'



thanks,



Guangming

View 2 Replies View Related

Xp_sendmail

Jul 22, 2002

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?

EXEC master..xp_sendmail
@recipients = 'Richard Peoples,
@message = 'Test',
@subject = 'Long-Running Job to Check'


Thanks!!!

View 2 Replies View Related

Xp_sendmail

Mar 27, 2001

Hi,

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.....


select @myquery= 'Execute LeadDistribution..sp_sendmailB ' + convert(char,@AssToID) /*calling the sp_sendmailB procedure */

execute master.dbo.xp_sendmail @recipients='lakshmip@oasisnetwork.com', @message='Leads Information',@query=@myquery

Select @send_Mail=@@Error

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.

(1 row(s) affected)

Please help me if anyone knows....

Thanks,
Sailaja

View 1 Replies View Related

Xp_sendmail

Dec 21, 2000

Greetings,

I am trying to utilize xp_sendmail passing a variable as the @recipients value.

EXEC master..xp_sendmail
@recipients = @EMAIL_ADDRESS_LNK,
etc.......

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.

View 7 Replies View Related

Xp_sendmail

Jan 9, 2001

Is there anyway to format the message text when using xp_sendmail ?? E.g. making some of it bold or in italics ??

View 1 Replies View Related

Xp_sendmail

Jan 18, 2001

I am using xp_sendmail in stored procedure and sending query using @query option. Is there a way to format the query output?

View 4 Replies View Related

Xp_sendmail

Feb 8, 2000

Hello,

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?

View 1 Replies View Related

Xp_sendmail

Mar 14, 2000

Hi
can the receiver list (xp_sendmail parameter) be a query. Becuase i must to concatenate. Thanks

View 1 Replies View Related

Xp_sendmail

Mar 6, 2003

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'

View 1 Replies View Related

Xp_sendmail

May 15, 2003

i'm executing this statement:

exec xp_sendmail @recipients='[email add]', @message='hello'

and i get an error 0x80040111

what does that mean? what am i doing wrong?

i'm already using SQL Server authentication. it was working last week, but now it isn't.

help please

View 3 Replies View Related

Xp_sendmail

Jul 18, 2001

Is it possible to set the "From" address using the xp_sendmail procedure?

View 1 Replies View Related

Xp_sendmail

Jul 7, 2005

Dear Friends,

I need to configure SQL Server to send mails automatically. How to configure this?

Also what all are required for this? Is exchange server a must?

Hoping to get reply

Vipin

View 3 Replies View Related

Xp_sendmail

Jun 10, 2004

i'm trying to send email using sql server, but every time i try to run xp_sendmail i get this message:

xp_sendmail: failed with mail error 0x80040111


can anyone tell me what this error means???

View 3 Replies View Related

Xp_sendmail

Jun 23, 2004

Hi,

I want to use xp_sendmail like this

declare @user varchar
select @user = user
from users
where userid =1


exec.xp_sendmail @user, 'The master database is full.'



But I get an error message saying ambiguous recipient

Is there any way I cn do this?

View 5 Replies View Related

XP_Sendmail

Sep 22, 2004

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.

Has anyone seen this before?

View 1 Replies View Related

Xp_sendmail

Dec 23, 2005

Hi All,

I have the following script that checks the amount of free space on the drive:

declare @MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

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.

View 5 Replies View Related

Xp_sendmail

Mar 11, 2004

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))

create table #None ([Equipment_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Equip_Nbr_11_Digit] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Activity_Date] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Activity_Time] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sighting_Code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filler] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location_City] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location_State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Train_Junction] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_Date] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_Time] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Road] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Destination_City] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Destination_State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Source] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Flatcar_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chassis_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Work_Order_Number] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BKG_BL_Number] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Train_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Free_Date] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Process_Date] [datetime] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[splc_code] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS null,
[Origin_SPLC] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Operator] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Shpt_Type] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pickup_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )

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 = '|'



any thoughts would be great.
Jim

View 2 Replies View Related

Xp_sendmail

Mar 31, 2004

I am trying to send a resultset via email using xp_sendmail.
I need to send the email when an earlier executed query has any results.

Got the query results into a table variable/temp table and then
in xp_sendmail, using

"Declare @table_var table(...)

..query excution..

EXEC master.dbo.xp_sendmail @recipients = 'xx@xx.com',
@query = 'select * from @table_var'"

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."

any ideas on this.

Thanks in advance.

View 2 Replies View Related

XP_Sendmail Help

Jun 19, 2008

Hello,

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?

Declare @date varchar(100), @SqlBcp Varchar(1000)
Select @date = Convert( varchar, getdate(),112)+Replace (Convert(varchar, Getdate(),114),':','')
Set @SqlBcp = 'Master..Xp_CmdShell '+'''bcp "Select * from KK_TEST..JobStatus" queryout "C: esting'+@date+'.xls" -c -T'''

EXEC master.dbo.xp_sendmail
@recipients = N'xxxx@xxxxxxxxx.com',
@query = N'Exec ('+ @SqlBcp +')',
@subject = N'Failed Job Details',
@message = N'The contents of JobStatus ',
@attach_results = 'TRUE',
@width = 250 ;


Thanks
Krishna

View 2 Replies View Related

Xp_sendmail

Jul 20, 2005

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?

View 1 Replies View Related

Xp_sendmail

Jan 28, 2008

I am having a problem sending an email using the stored procedure "xp_sendmail".
This query is good.


SELECT h.order_id, h.order_date, h.responsible2 as "requested by", sum(d.cur_amount) as amount

FROM table1 as h inner join table2 as d

on h.order_id = d.order_id

where h.accept_flag = 0

and h.order_date > ' jan 1 2006 '

GROUP BY h.order_id,h.order_date, h.responsible2

ORDER BY h.order_id


The same query used to send an email does not work, don't know why.



master.dbo.xp_sendmail 'email@domainname.com,

@subject= 'POs that need approval',

@dbuse= 'database',

@query = '

SELECT h.order_id, h.order_date, h.responsible2 as "requested by", sum(d.cur_amount) as amount

FROM table1 as h inner join table2 as d

on h.order_id = d.order_id

where h.accept_flag = 0

and h.order_date > ' jan 1 2006 '

GROUP BY h.order_id,h.order_date, h.responsible2

ORDER BY h.order_id

'

Here is the error msg


Msg 170, Level 15, State 1, Line 10

Line 10: Incorrect syntax near 'jan'.

Any help would be appreciated!

View 4 Replies View Related

Xp_sendmail

Nov 23, 2007

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.


Thanks
Aric

View 5 Replies View Related

Xp_sendmail

Apr 16, 2008



I am trying to use xp_sendmail with operator instead email id.

it is not taking operator

is there anyway to use operator with xp_sendmail

I have valid operator it is working with jobs

Thanks
Gan

View 3 Replies View Related

Xp_sendmail

Oct 1, 2006

Ok all...I'm not having much luck with this.

I'm using SQL Server 2005 MSE

I try to run

xp_sendmail 'adam@getpaid4spam.biz', 'This is a test'

And I get the following error...

Msg 17750, Level 16, State 0, Procedure xp_sendmail, Line 1

Could not load the DLL sqlmap90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

Any ideas?

Adamus

View 7 Replies View Related

Need Assistance In Using Xp_sendmail

Feb 5, 2008

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.
 
Thanks!
 

View 10 Replies View Related

Excuting Xp_sendmail

May 10, 2004

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.

greetings,
Geoff

View 1 Replies View Related

Xp_sendmail Error

Nov 21, 2004

Hello Pros

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"

Help Please

View 1 Replies View Related

Xp_sendmail Driving Me Mad !

Jan 11, 2002

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

SELECT @sql='SELECT * FROM ##TEMP'

IF @@ROWCOUNT > 0

begin

exec master.dbo.xp_sendmail
@recipients = 'itsmarkdavies@hotmail.com',
@Message = 'Test file',
@Query = @sql,
@attachments = 'C:MARKTEST.CSV',
@Attach_Results = 'True',
@Message = '',
@Subject = 'test',
@No_Header = 'True',
@Width = 500,
@Separator = ','

end
DROP TABLE ##TEMP
GO

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.

View 1 Replies View Related

Xp_sendmail Question

Apr 1, 2002

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!

View 1 Replies View Related

Xp_sendmail - Dynamic &#39;To&#39;

Apr 30, 2002

Hi,

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?

Your help is appreciated.
Gary

View 1 Replies View Related







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