Execute various scripts present in directory without using query builder + urgent

Question

Post reply

Execute various scripts present in directory without using query builder + urgent

manjushree.nimbalkar

SSC Rookie

Points: 47
More actions
May 10, 2010 at 3:20 am

#220892

Hi,
I have lot of scripts present in one directory. Want to execute this scripts without opening then in query builder and pressing execute button. Is there any way I can automate this process.
Any help will be appreciates. Thanks in advance.
Manju

Viewing 7 posts - 1 through 7 (of 7 total)

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

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 1

Potentially you could build an SSIS package to loop through each file in the directory and execute with sqlcmd:

From BOL:

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

Hope this helps

Rich

Hope this helps,
Rich

[p]
[/p]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi, there is something called OSQL utility which allows us to run .sql files from the command prompt. So , what you can do is,

1. Do a "DIR" on the command prompt,

2. Copy the file names one by one, then put them in Filename parameter one-by-one for all the files in the below code

OSQL -E -S"Server" -d"Database" -i"FileName.sql" -n -E> %LOG%

3. Paste the entire thing in Notepad

4. Save the notepad is "Scripts.cmd"

5. Run (double-click) that cmd file

This will run all the .sql file without you having to execute them manually one by one! Also the %LOG% is the location where you want the log file of the entire operation to be created!

Hope this helps; if it doesn, please give me some time, i will give u a scripted way to do this!

Cheers!

manjushree.nimbalkar SSC Rookie Points: 47 More actions · Answer 3

Hey,

Thanks for such quick reply. If in case some error occurs in one of the script, where will it get logged?

Manju

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Try a batch file ?

for %%f in(*.sql) do sqlcmd -s %servername -d %databasename -u %username -p %password -i "%%f"

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

manjushree.nimbalkar (5/10/2010)
Hey,
Thanks for such quick reply. If in case some error occurs in one of the script, where will it get logged?

Manju, replace %LOG% location with something like "C:\Temp\Logfile.log"; This is log the execution results of the sql files in Logfile.log!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Manju, as i promised, here is the script ; U can run this from Query Editor itself

SET NOCOUNT ON;

DECLARE @SCRIPTS TABLE (SCRIPT_NAME_LOCATION VARCHAR(100))

INSERT INTO @SCRIPTS

EXEC master..xp_cmdshell 'dir /b /s D:\Test'

--SELECT * FROM @SCRIPTS

DECLARE @QUERY VARCHAR(4000)

SET @QUERY = ''

SELECT @QUERY = @QUERY + ' OSQL -E -S"." -d"MASTER" -i"'+ SCRIPT_NAME_LOCATION + '" -n -E>> "C:\TEMP\LOG.LOG"' + CHAR(10)

FROM @SCRIPTS WHERE CHARINDEX('.sql',SCRIPT_NAME_LOCATION) > 0 AND SCRIPT_NAME_LOCATION IS NOT NULL

PRINT @QUERY

--EXEC master..xp_cmdshell @QUERY

Remove the commented section in the code to the script to "actually" perform the task!

Hope this helps you!

Cheers!