February 20, 2009 at 6:51 pm
I am trying to run below query
exec Server2005.msdb.dbo.sp_send_dbmail
@profile_name = 'Sample',
@recipients='ABC@net.com',
@subject='FAILED JOBS REPORT',
@body ='FAILED JOBS REPORT',
@query='select * from ##temp_text'
from a SQL server 2000 server using a linked server.
#temp_text is a temporary table on SQL server 2000.
Kindly advice how I could use this table for getting the report.
I get error message
Invalid object name '##temp_text'.
Server: Msg 22050, Level 16, State 1, Procedure usp_failed_jobs_report, Line 122
Error formatting query, probably invalid parameters
This is because this table ##temp_text is not present in 2005. Please advice.
M&M
February 20, 2009 at 7:10 pm
I'm not 100% certain but you may be able to do the following...
exec Server2005.msdb.dbo.sp_send_dbmail
@profile_name = 'Sample',
@recipients='ABC@net.com',
@subject='FAILED JOBS REPORT',
@body ='FAILED JOBS REPORT',
@query='select * from Server2000.##temp_text'
February 22, 2009 at 10:45 am
When I give like this
exec SQL2005.msdb.dbo.sp_send_dbmail
@profile_name = 'Sample',
@recipients=abc@abc.com',
@subject='FAILED JOBS REPORT',
@body ='FAILED JOBS REPORT',
@query='select * from SQL2000.tempdb.temp_text'
I get below error message
Query execution failed: Msg 208, Level 16, State 1, Server SQL2005, Line 1
Invalid object name 'SQL2000.tempdb.temp_text'.
Server: Msg 22050, Level 16, State 1, Procedure usp_failed_jobs_report, Line 122
Error formatting query, probably invalid parameters
How do we access temp table in SQL server 2000. Any inputs?
M&M
February 22, 2009 at 1:35 pm
Did you make a Linked Server on SQL2005 called SQL2000 to point back to the SQL2000 server?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2009 at 1:38 pm
If you do have the Linked server above defined, then I believe that the correct syntax shoud be:
exec SQL2005.msdb.dbo.sp_send_dbmail
@profile_name = 'Sample',
@recipients=abc@abc.com',
@subject='FAILED JOBS REPORT',
@body ='FAILED JOBS REPORT',
@query='select * from SQL2000.tempdb.dbo.##temp_text'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2009 at 1:39 pm
Your other choice is to copy your ##temp_text table over to SQL2005 first.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply