Linked Server query in xp_sendmail

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

SQL Server Development (2000)

Linked Server query in xp_sendmail

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-28 : 10:51:05

I get this error:

ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER.

When running a query on a linked SQL Server destination inside an xp_sendmail procedure. So I have to start running this procedure from MASTER here at home. The query itself runs fine here but gives this error inside xp_sendmail so I was thinking maybe I need to take an extra step backward from the MASTER database, but I have done, and can't be much more obvious in the script to point out where the server and database are... Here is the script. What can I do?

exec master.dbo.xp_sendmail
@recipients = 'michael.bird@axiseurope.com',
@subject = 'Please verify these SOR Codes',
@query = 'select distinct ccs_qos_item_ref "SOR Code",
ccs_qos_title "Description",
ccs_qos_quoted_price#6 "Cost",
ccs_quo_user_date#1 "Completed Stamp" -- current datetime from jobcompletedUpdate
from [ken-sql-002].ConstructMobile.dbo.ccs_q_sections qs
inner join [ken-sql-002].ConstructMobile.dbo.ccs_quotes q
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
where q.ccs_quo_analysis_code = ''PENDING'''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 11:28:06

Use ANSI style writing, as I showed before...

exec master.dbo.xp_sendmail
		@recipients = 'michael.bird@axiseurope.com',
		@subject = 'Please verify these SOR Codes',
		@query = '
			select distinct	ccs_qos_item_ref AS [SOR Code],
					ccs_qos_title AS Description,
					[ccs_qos_quoted_price#6] AS Cost,
					[ccs_quo_user_date#1] AS [Completed Stamp]
			from		[ken-sql-002].ConstructMobile.dbo.ccs_q_sections AS qs
			inner join	[ken-sql-002].ConstructMobile.dbo.ccs_quotes as q on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
			where		q.ccs_quo_analysis_code = ''PENDING''
			'

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97954

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 11:33:05

And also learn to prefix your columns when having more than one table in a query.
It makes things so much easier for you, and especially for us, to debug.

exec master.dbo.xp_sendmail
		@recipients = 'michael.bird@axiseurope.com',
		@subject = 'Please verify these SOR Codes',
		@query = '
			select distinct	{ qs | q }.ccs_qos_item_ref AS [SOR Code],
					{ qs | q }.ccs_qos_title AS Description,
					{ qs | q }.[ccs_qos_quoted_price#6] AS Cost,
					{ qs | q }.[ccs_quo_user_date#1] AS [Completed Stamp]
			from		[ken-sql-002].ConstructMobile.dbo.ccs_q_sections AS qs
			inner join	[ken-sql-002].ConstructMobile.dbo.ccs_quotes as q on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
			where		q.ccs_quo_analysis_code = ''PENDING''
			'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources