Execute All .SQL Script easily

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  May 18, 2015 at 11:58 am

  #308327

  Comments posted to this topic are about the item Execute All .SQL Script easily

  Thanks.

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  June 10, 2015 at 6:56 am

  #1804315

  Thanks for another good script.

• corey lawson

  Hall of Fame

  Points: 3773

  More actions

  June 10, 2015 at 10:58 am

  #1804421

  hmm... DOS/CMD instead?

  for %f in (*.sql) do @osql -Sserver -E -i"%f"

• bauke.dijkstra

  SSC Enthusiast

  Points: 143

  More actions

  June 18, 2015 at 2:20 am

  #1806283

  I am getting an error.

  I got sql files in the map C:\DATA\testsql\Procedure\*.sql

  i run the script, getting an error:

  Cannot open input file - C:\DATA\testsql\table\time.sql

  No such file or directory

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  June 18, 2015 at 2:27 pm

  #1806532

  Both the path details you mentioned are diff

  Thanks.

• bauke.dijkstra

  SSC Enthusiast

  Points: 143

  More actions

  June 19, 2015 at 12:32 am

  #1806590

  Yes, because this is also like this in the solution offred.

  INSERT INTO ##SQLFiles

  EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\testsql\Procedure\*.sql"'

  GO

  select * from ##SQLFiles

  --DROP TABLE ##SQLFiles

  DECLARE cFiles CURSOR LOCAL FOR

  SELECT DISTINCT [SQLFileName]

  FROM ##SQLFiles

  WHERE [SQLFileName] IS NOT NULL AND

  [SQLFileName] != 'NULL'

  ORDER BY [SQLFileName]

  DECLARE @vFileName VARCHAR(200)

  DECLARE @vSQLStmt VARCHAR(4000)

  DECLARE @OutPutFolder VARCHAR(4000) = 'C:\DATA\testsql\output';

  DECLARE @inputFolder VARCHAR(4000) = 'C:\DATA\testsql\table';

• Suth

  SSCommitted

  Points: 1559

  More actions

  October 16, 2015 at 3:27 am

  #1833716

  It is possible by using this script add a number of sps at once , rather than keep changing the path to point to a different sp

  For example;

  INSERT INTO ##SQLFiles

  EXECUTE master.dbo.xp_cmdshell 'dir /b "\\ServerUNC\Folder1\DBA_TSQL_Scripts\Procedure\*.sql"'

  GO

  INSERT INTO ##SQLFiles

  EXECUTE master.dbo.xp_cmdshell 'dir /b "\\ServerUNC\Folder1\DBA_TSQL_Scripts\Procedure\test.sql"'

  GO

  INSERT INTO ##SQLFiles

  EXECUTE master.dbo.xp_cmdshell 'dir /b "\\ServerUNC\Folder1\DBA_TSQL_Scripts\Procedure\test2.sql"'

  GO

  ........

• bojned

  Valued Member

  Points: 65

  More actions

  May 24, 2016 at 1:26 am

  #1880952

  It's risky to use xp_cmdshell if system is not properly secured. Although easy to use, it's a potential security threat for this SP to be enabled...

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 24, 2016 at 6:33 am

  #1881023

  Nice to see this one reposted. Thanks again.