July 18, 2011 at 2:19 am
Hi All,
Please help. I have some 100 Sql scripts in my local folder and i want to run all those scripts at a time. How can i read the file and execute the same in SQL server so that i can automate the the same using SQL Job.
Thanks in Aadvance.
Vijay
July 18, 2011 at 2:58 am
Use osql command to execute a sql script file
July 18, 2011 at 3:13 am
Is there an order to the way the scripts need to be executed , i.e script tables first then indexes and keys and then proc etc.
You could also look for tools which contatenate text files into a single file.
I think textpad and notepad++ have features or way to achieve this.
Something else i tot might be useful
July 18, 2011 at 3:33 am
Thanks for the replies guys.
osql is working. There is no order for the scripts.
Thanks,
Vijay
July 18, 2011 at 12:32 pm
Note that osql.exe is deprecated as of SQL 2005. The recommended replacement is sqlcmd.exe. Consider using sqlcmd for new development. The command line options are very similar so converting to use sqlcmd will be trivial.
http://technet.microsoft.com/en-us/library/ms170207(SQL.90).aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 3:57 am
you can create the batch file to execute all the scripts and for running the scrips in order , tag the scripts with sequence number ... you will be fine...
Cheers....
Pankaj Sharma
July 19, 2011 at 12:42 pm
This script will loop through a directory, executing all SQL files within...
--Enable xp_cmdshell
EXEC [master].[dbo].[sp_configure] 'show advanced options', 1
RECONFIGURE
GO
EXEC [master].[dbo].[sp_configure] 'xp_cmdshell', 1
RECONFIGURE
GO
--Execute all sql files in dir
EXEC xp_cmdshell 'for %f in ("C:\Scripts\*.sql") do sqlcmd -S servername -U username -P password -d databasename -i "%f" -b'
GO
July 19, 2011 at 12:53 pm
tleezer (7/19/2011)
This script will loop through a directory, executing all SQL files within...--Enable xp_cmdshell
EXEC [master].[dbo].[sp_configure] 'show advanced options', 1
RECONFIGURE
GO
EXEC [master].[dbo].[sp_configure] 'xp_cmdshell', 1
RECONFIGURE
GO
--Execute all sql files in dir
EXEC xp_cmdshell 'for %f in ("C:\Scripts\*.sql") do sqlcmd -S servername -U username -P password -d databasename -i "%f" -b'
GO
Why in the world would you recommend xp_CmdShell here, especially if it is already disabled on the instance? It opens up a can of worms in terms of security and the OP already said they were willing to use a SQL Agent job to do the work.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply