Connect to ODBC DB from SQL Server SPROC

Hello,
I have taken on a project where I'm wondering if I'm in a little over my
head. Using SQL Mail I need to send out e-mails every hour for orders that
have shipped. There are two databases. One is SQL Server and the other is
only accessible via ODBC (I'm not even sure what type of database it is yet,
but it's not SQL Server). The SQL Server database contains the basic order
information and the other database contains the order status.
I have the queries I need, but how do I connect via ODBC to the other
database from SQL Server? I know (very) little about SQL Mail. Do I set it up
as a job under SQL Server Agent jobs so it will run every hour? Can someone
give me a push in the right direction?
Thank you very much for any help, I really appreciate it.
Thanks,
Nick
Hello,
I think you can perform the following steps:
1. Run the query in SQL query analyzer(QA) to make sure it works fine. You
can create a stored procedure.
2. Configure the SQL mail.
3. Run xp_sendmail in SQL QA to send the query results via email.
4. Create a job in SQL agent so it will run every hour.
Generally we can create a linked server to access other data source.
However, it denpends on what database you use. If it is a third party
database, I suggest that you contact the third-party support to consult
with them abouht the issue.
The following information is for your reference:
SQL Server E-mail
http://msdn.microsoft.com/library/de...us/dnovba01/ht
ml/SQLServerE-mail.asp
INF: How to Configure SQL Mail
http://support.microsoft.com/kb/263556
Support WebCast: Troubleshooting Microsoft SQL Mail
http://support.microsoft.com/kb/324689
xp_sendmail
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
xp_aa-sz_6hbg.asp
sp_addlinkedserver
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
sp_adda_8gqa.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.