TSQL wid email - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

TSQL wid email

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-04 : 10:40:31

Hi,
Iam trying to send email to specific persons in the company, after t sql code runs successfully.

Now when i run the whole code( including msdb.dbo.sp_send_dbmail ) there is an error in hte line below:-

LEFT(LTRIM(RTRIM(IM.ITEMNMBR)), (CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR))) - 1)),

The error says :-

Incorrect syntax near '-'.

BUT when i run this code separetly(just tsql part), it runs fine...
Wondering..

Regards,
Sushant

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-04 : 10:58:29

Now when i run hte code there is an error in hte line below:-

LEFT(LTRIM(RTRIM(IM.ITEMNMBR)), (CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR))) - 1)),

The error says :-

Incorrect syntax near '-'.

BUT when i run this code separately, it runs fine...

CODE:---

---START OF CODE
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Sushant yadav',
@recipients='syadav@xyz.com;sushantk@xyz.com',
@subject = 'SQl 2005 email test',
@body_format = 'HTML',
@query = 'INSERT INTO IV00104
(ITEMNMBR,
SEQNUMBR,
CMPTITNM,
CMPITUOM,
CMPITQTY,
CMPSERNM)
SELECT
IM.ITEMNMBR,
16384,
LEFT(LTRIM(RTRIM(IM.ITEMNMBR)), (CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR))) - 1)),
UL.UOFM,
1.00000,
0

FROM
IV00101 IM LEFT JOIN IV00104 KT ON IM.ITEMNMBR = KT.ITEMNMBR
JOIN IV40202 UL ON (IM.UOMSCHDL = UL.UOMSCHDL AND
(RIGHT(LTRIM(RTRIM(IM.ITEMNMBR)), LEN(LTRIM(RTRIM(IM.ITEMNMBR))) -
(CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR)))))) = UL.UOFM)

WHERE
IM.ITEMNMBR != '_' AND
IM.ITEMTYPE = 3 AND KT.ITEMNMBR IS NULL

ORDER BY

IM.ITEMNMBR',

@query_result_header = 1,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 0,
@query_result_no_padding = 0
--end of code

Regards,
Sushant
DBA
West Indies

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-04 : 11:28:15

Replace the single quotes with two single quotes as in

LEFT(LTRIM(RTRIM(IM.ITEMNMBR)), (CHARINDEX(''-'', LTRIM(RTRIM(IM.ITEMNMBR))) - 1)),

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-04 : 11:41:57

@ sunita....thanks..

That error went, I got command commited successfuly
BUT , in the email , i received :-
Msg 208, Level 16, State 1, Server servername, Line 1 Invalid object name 'IV00101'

I know for sure that there exists a table like that...

Regards,
Sushant

Regards,
Sushant
DBA
West Indies

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-04 : 11:48:06

You may need to qualify your table names with the database and schema name, for example

FROM
YourDatabaseName.YourSchemaName.IV00101 IM LEFT JOIN YourDatabaseName.YourSchemaName.IV00104 KT ON IM.ITEMNMBR = KT.ITEMNMBR

The default schema is dbo, so if you don't know what the schema name is, try dbo for YourSchemaName.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-04 : 11:53:01

wow...That was it !!
U R genious :)
Thanks a lot !!

Regards,
Sushant
DBA
West Indies

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-04 : 13:21:23

You are very welcome; glad it worked out for you.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-04 : 13:58:36

Hi,
I get email as ( 1 row affected)
Is it possible to see the 1 row which is inserted , just the item number, i want to see.

Thanks,

Regards,
Sushant
DBA
West Indies

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-04 : 14:17:07

I have never used sp_send_dbmail, so don't know for sure, but something like this should work:

quote:
Originally posted by skybvi

[stuff deleted]
@body_format = 'HTML',
@query = 'INSERT INTO IV00104
(ITEMNMBR,
SEQNUMBR,
CMPTITNM,
CMPITUOM,
CMPITQTY,
CMPSERNM)

OUTPUT inserted.ITEMNMBR

SELECT
IM.ITEMNMBR,
16384,
LEFT(LTRIM(RTRIM(IM.ITEMNMBR)), (CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR))) - 1)),
UL.UOFM,
1.00000,
0

FROM
IV00101 IM LEFT JOIN IV00104 KT ON IM.ITEMNMBR = KT.ITEMNMBR
JOIN IV40202 UL ON (IM.UOMSCHDL = UL.UOMSCHDL AND
[stuff deleted]

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-04 : 14:51:09

okk,
I have no data now, to check the output in the email, may be after some days i can check..
thxs a lot.

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-08 : 09:24:29

yes, the output comes this way ( output inserted.itemnumber) but the out put looks like this --> itemnumber apple1banana3choclate9

I want them to be separated by a comma or maybe in a table..
How to do that?

Regards,
Sushant

Regards,
Sushant
DBA
West Indies

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-08 : 13:18:00

You might try to change the output clause to the following:

OUTPUT cast(inserted.ITEMNMBR as varchar(32))+', ' as [Items]

But, db_mail is a topic I have no experience or familiarity with, so....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources