Execute All .SQL Script easily

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

    Thanks.

  • Thanks for another good script.

  • hmm... DOS/CMD instead?

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

  • 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

  • Both the path details you mentioned are diff

    Thanks.

  • 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';

  • 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

    ........

  • 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...

  • Nice to see this one reposted. Thanks again.

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

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