Want to create all procedures located in a folder one by one and creating/saving output file in a folder.

Question

Want to create all procedures located in a folder one by one and creating/saving output file in a folder.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
May 15, 2015 at 3:02 pm

#308140

Hello,
I am asked to create 100 procedures to a database. Can you suggest a best way to create them in a database one by one by calling the files and saving the execution output files in a folder?
Please suggest me a convenient way
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

Do the 100 files contain SQL scripts to create the stored procedures? If so, is the purpose of the output being saved to a folder to tell if the stored procedures were successfully created?

If that's the case, I would write a C# or VB application to loop through each script file and perform the relevant query execution and save the resulting query results to file. I could give more details on that approach if required.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Hi Andrew,

Could you plz provide more details or else if you have any such script in C# or powershell script which I can use..

I need the o/p files to review that after each .sql script is executed to ensure that it did nt fail...so o/p files wil be great help.

Thanks..

Thanks.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Just use the old DOS "FOR" command to cycle through your files and call SQLCMD for each file. Look for it on the Internet.

You wouldn't like the way that I'd do it, which is ultra simple, because it uses xp_CmdShell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

I am using sqlcmd servename database -i "input sql" -o "outputfile.txt".

But I m not really liking this method.... as ineed to update all file name. Instead if I could just pass the path and it could loop thru all .sql files running it in sequence n produce each o/p file, would help me a lot for debug purposes..

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

Hi Andrew P,

Let me know if you have any better solution in my case to use VBA (C#) or Powershell may be ..

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

Hello Jeff Moden,

PEr your suggestion, I used FORFILES but as I have placed the .sql scripts in the UNC path, getting this error: =

ERROR: UNC paths (\\machine\share) are not supported.

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 7

SET NOCOUNT ON

declare @servername nvarchar(4000), @dbname nvarchar(200), @cmd nvarchar(4000) , @return_value INT;

SET @servername = (select @@servername)

SET @dbname = 'database'

Print @servername

Print @dbname

SET @cmd = 'FORFILES -p \\drive\SQL_scripts\db_TSQL_Scripts\Procedure\ /s /m *.sql -C "CMD /c sqlcmd -i@FILE"'

SELECT @cmd

EXEC @return_value = master.dbo.xp_cmdshell @cmd

IF @return_value=0

BEGIN

SELECT 'Executed Scripts: ' + @@SERVERNAME;

END

SET NOCOUNT OFF

Thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Have you tried a ForEach Loop in SSIS? Would you be interested?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 9

I've had this in my powershell library for a long time; i use this powershell script to export everything out via SMO as individual files

http://www.sqlservercentral.com/Forums/Topic1240480-1351-1.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!