I have a stored procedure that moves specific data from several tables from a database on one server to several others (using a bunch of openrowset queries). I don't have an app to call this procedure - it's usually runs from the query analyzer and the parameter values for the procedure are passed in from there.
The procedure currently skips over recipient tables that are already populated with the data that's being moved. I want the ability to either skip the table or delete the existing records from the recpient table data based on the user's response. Therefore, I somehow need to prompt the user to get a response when the data already exists in the existing table.
Is there any way to prompt the user from a stored procedure, or do I have to re-develop the procedure in DTS or write and application?
You need to write an application. SQL Server has no built in interface for interacting with the user.
Your simplest approach might be to create an Access Data Project tied to your SQL Server database. Then you could easily create forms and vb code to do what you want.
If it's not practically useful, then it's practically useless.