I would move towards complete automation in two or three steps.
First, change the stored proc to accept two parameters, @File_name and @Mailer_Type like shown below:ALTER PROCEDURE [dbo].[sp_ALT_Append_Fields]
@File_Name varchar(50),
@Mailer_Type varchar(50)
AS
BEGIN
SET NOCOUNT ON;
--DECLARE @File_Name varchar(50), @Mailer_Type varchar(50)
--SET @File_Name = 'Your_mail_file_table_name'
--SET @Mailer_Type = 'Choose_value_below'
Now, instead of step 2 and 3, simply do:EXEC [dbo].[sp_ALT_Append_Fields] 'YourFilenameHere', 'YourMailerTypeHere';
Once you have that working correctly, use SSIS (SQL Server Integration Services) to import the data, run the stored proc and output the data the way you want it. There is quite a bit to it, but none too hard. You should look up some basic tutorials on SSIS - it is designed to facilitate exactly the kind of tasks that you are trying to do.
execute multiple scripts
Posted - 11/08/2012 : 02:11:09
