Execute All .SQL Script easily

  • SQL_Hunt

    SSC-Dedicated

    Points: 33453

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

    Thanks.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for another good script.

  • corey lawson

    Hall of Fame

    Points: 3731

    hmm... DOS/CMD instead?

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

  • bauke.dijkstra

    SSC Enthusiast

    Points: 122

    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: 33453

    Both the path details you mentioned are diff

    Thanks.

  • bauke.dijkstra

    SSC Enthusiast

    Points: 122

    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

    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

    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

    Nice to see this one reposted. Thanks again.

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

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