I have searched up and down for a solution and cannot find anything that helps. Everything that I have tried doesnt seem to work.
I have two database. I cannot modify Database A by adding a table or anything like that. I cannot enable cross-database access. I believe I cant use sp.Start_job because I need to pass parameters. I was able to enable xp_cmdshell.
My Database Definition:
Database A
- Order table
Database B
- Email notification Table
What I need to do:
Any time an order is entered into Database A, I need to enter a row into Database B with values of the order. So I need to have parameters
What I have tried:
- xp_cmdshell @query='DTEXEC /f "\\Server\Folder\SSIS Packages\Order confirmations\Order confirmations\Package.dtsx" /DECRYPT password'
1. I get an error saying access denied. I tried setting up sp_xp_cmdshell_proxy_account (##xp_cmdshell_proxy_account##) to an account that has access to the network drive where the file is stored, no luck.
2. whoami.exe shows NT/Authority \System instead of my proxy account.
3. GRANT EXECUTE on xp_cmdshell to [mydomain\myAccount] didnt have any affect.
Any suggestions? The job runs fine if I set it up with SQL Server Agent. Eventually I will just call xp_cmdshell from a trigger on Database A table Orders to enter the data into Database B and then fire off an email with sp_send_dbmail if some conditions are met.
The only way that I could currently find (Without resturcturing permissions in SQL and the network was to give read/execute permission to SQL server. Not my ideal solution, but it works.
Hope this helps someone
