xp_smtp_sendmail from here, grab data from there... AARGH!!
-
It seemed a very straight forward design.
I have created a set of stored procs on the production server.
From the reporting server, I am creating a DTS package, a connection object made to production, calls the sprocs to do some work (suspending some bad customers etc...). The results are inserted into an "email" table created on production.
Now, xp_smtp_sendmail was set up on reporting server. I was going to use the same connection, select from production's table, loop through the rows, and call xp_smtp_sendmail. But it doesn't work!
The user login I used to the production server, does that need to be created and given access to in the reporting server too? What are my options?
My brain is dead at this hour, please help!
-
OK, my brain has been temporarily revived with some sleep last night
So, I created another connection object to the reporting server, (log in using sa because i have to call xp_smtp_sendmail in master db, probably bad huh?) after i insert data into the email table on production, i run a Data Tranformation Task to copy the data over to reporting (created a table in tempdb), now I can loop through the local table and run xp_smtp_sendmail.
PROBLEM! The DTS package reports that it ran successfully, but I'm not getting all the emails (there are 1021 rows of test data in the email table, so it should send out 1021 emails!) Can Exchange be choking up? I only got 12 emails!
Anyone?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply