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!