Using xp_sendmail to TAB delimit a file..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using xp_sendmail to TAB delimit a file..

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-23 : 15:52:15

Hello again. I'm currently using xp_sendmail to send the results of a query.

Do you know if any way to delimit the file using a TAB instead of a COMMA.

ie. @SEPERATOR = '[TAB]'

Will this work?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 15:56:05

@separator=CHAR(9)

or

@separator='\t'

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-23 : 15:56:49

thanks rob!

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-23 : 16:23:40

Actually, when I try that I get:

10/22/02 CHAR(9)WEBMACS CHAR(9)I1435965

same with /t

any ideas?

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-23 : 16:23:46

Actually, when I try that I get:

10/22/02 CHAR(9)WEBMACS CHAR(9)I1435965

same with /t

any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 16:24:32

Can you post the entire xp_sendmail call you're using?

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-23 : 16:27:18

declare @sub varchar(100)
set @sub = (select 'Webmacs Demand for '+convert(varchar(10),getdate()-1,1))

exec master..xp_sendmail

@recipients = 'Michael Delgado',
@Subject = @sub,
@query =
'
select convert(varchar(10),Date,1) as ''Entry Date'',[Entry-oper] as ''Logon'',[Order-no] as ''Order'', [item-no] as ''Item'', Description,Price as ''Total'',
[item-qtys] as ''Qty'', Status, Source
from salecomm2k.dbo.demandhistorical
where
(([Order-no] like ''i%'') or (source like ''%web%'' or source like ''%yah%''))
and
convert(smalldatetime,date,1) = convert(smalldatetime, convert(varchar(10),getdate()-1,1),1)
',
@attach_results = 'True',
@separator = 'CHAR(9)',
@attachments = 'WebmacsDemand.csv',
@ansi_attachment = TRUE,
@width = 8000

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 16:32:31

Don't put single quotes around it:

@separator = CHAR(9),

That's why it put in the literal text "CHAR(9)".

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-23 : 16:43:19

when I use that syntax I get the following Error:

Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near '9'.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 16:46:19

Try this:

DECLARE @sep varchar(1)
SET @sep=CHAR(9)
...
@separator = @sep,
...

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-23 : 16:53:21

that gives me squares...

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-23 : 17:05:36

sorry, I thought I had copied and pasted an excerpt from Excel.

WHen I changed the filenmae to an xls from an csv extenstion it worked perfectly.

thanks Rob!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources