SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 execute multiple scripts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbonneau
Yak Posting Veteran

50 Posts

Posted - 11/08/2012 :  02:11:09  Show Profile  Reply with Quote
Hello,

I have about 20 sql scripts in stored procedures in sql sever 2008. I currently open each script, change file name, and type name, and then run the execute button to get the result.
I was wondering if there is a way to automate most of my manual job.

Here is my manual process for this job.
1) First, I import raw data (csv) into SQL server using Import and Export Wizard.
2) Open a script in stored procedure (example code below), type my File_Name( the table I created in 1) )and Mailer_Type name (ex. ppp) in each script.
3) Highlight the syntax from "Begin" to the end of script and change db to the db I am allowed to run, and then execute the script.
4) Copy the output result and paste it in my excel

I do this procedure from 2) to 4) for about 20 to 30 times a day.

I was wondering if there any way to control many SQL scripts without open up each file and run one by one.

Thank you so much for your help.


USE [dev_db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_ALT_Append_Fields]
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'

--PPP
--ZZZ

..............
.............
...........

END

Edited by - dbonneau on 11/08/2012 02:16:21

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/08/2012 :  05:57:07  Show Profile  Reply with Quote
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.
Go to Top of Page

dbonneau
Yak Posting Veteran

50 Posts

Posted - 11/08/2012 :  18:17:18  Show Profile  Reply with Quote
Hi sunitabeck,

Thank you so much for your reply.

Is there a way not to change any syntax on the stored proc but just create some kind of global variables for my file name in different script to run the stored proc ?

Developers update their scripts every day in the stored proc, and I am not supposed to change any of their syntax..

Thank you

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 11/08/2012 :  21:28:22  Show Profile  Reply with Quote
Then ask the developer to change the stored procedure to allow parameters as sunita suggested.

It does not make sense to hard-code values in the stored procedure when these are suppose to be variable


KH
Time is always against us

Go to Top of Page

dbonneau
Yak Posting Veteran

50 Posts

Posted - 11/09/2012 :  21:31:57  Show Profile  Reply with Quote
Hi,

Thank you for your reply.
I wish I could have developer to change the stored proc. But because there are some compliance issues in company i work for, They will not change the code for my needs.

Is there any way to control the stored proc without changing their code ?

Thanks.

quote:
Originally posted by khtan

Then ask the developer to change the stored procedure to allow parameters as sunita suggested.

It does not make sense to hard-code values in the stored procedure when these are suppose to be variable


KH
Time is always against us



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 11/10/2012 :  10:02:28  Show Profile  Reply with Quote
write your own stored procedure ?


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000