i have a stored procedure that backups database on server A which i execute this SP from server B in a scheduled job. when i run this job it gives me an error:
Executed as user: domain\user_name. Could not relay results of procedure 'usp_BackupDBOnServerA' from remote server 'ServerB'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.
i searched MS KB, everywhere but could not find the solution or even explanation why this happenning.
both sql servers are 2000 and they are linked.
It seems like an account permissions issue. I would look over the SP and make sure that the account which SQLServer service, SQLAgent service, and the scheduled job has access to the SP as well as the network share which the backup is being written too.
Does the whole backup fail or does it just not return what happened (the result or output)?
actually there's no issue with account's permissions. and to asnwer your question the job itself fails.
but i just figured out how to solve the issue: i increased the Remote query timeout in the Server properties.
thanks for the help anyway.