Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Unanswered: Executing Multiple Scripts

    Hey guys,
    What is the best way to run multiple sql scripts against a database.
    Vendor provided a DBupdate utility but it isn't working properly.....
    any suggestions....
    thanks,
    jonathan

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OSQL with a batch file. DTS/SSIS. Scheduled job.
    Many ways to do this....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    do you know of a site or book where I can read how to do so.....
    a good search string for google.....

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    just giving it away today...


    Code:
    DECLARE @SQLServer VARCHAR(100)
    DECLARE @Database VARCHAR(100)
    DECLARE @UserName VARCHAR(100)
    DECLARE @Password VARCHAR(100)
    DECLARE @UseWindowsAuthentication BIT
    DECLARE @Path VARCHAR(1000)
    DECLARE @FilePathToSQLFiles VARCHAR(200)
    
    /*############################################################################ 
    If you are unsure of your sql server name, you can use the following
    SELECT @@SERVERNAME. This should be the sql server where the database resides
    that you are updating.
    #############################################################################*/
    SET @SQLServer = 'MyServer'
    
    /*####################################################
    @Database is the name of the database you are updating.
    ######################################################*/
    SET @Database = 'MyDB'
    
    /*####################################################
    Is the path to the sql files that you wish to execute.
    Example FilePath : C:\SQL Scripts\
    #####################################################*/
    SET @FilePathToSQLFiles  = 'C:\SQL Scripts\Create Scripts\'
    
    /*####################################################################################
    If you choose to use windows auth, you do not have to fill in a user name or password,
    but your network account has to be a sysadmin on the sql server. 
    1 = use windows auth 
    0  = sql auth
    ####################################################################################*/
     
    SET @UseWindowsAuthentication = 1
    SET @UserName = ''
    SET @Password = ''
    
    CREATE TABLE #SQLFiles ( SQLFileName VARCHAR(2000))
    
    SET @Path = 'dir /b "' + @FilePathToSQLFiles + '*.sql"'
    
    INSERT INTO #SQLFiles
    EXECUTE master.dbo.xp_cmdshell @Path 
    
    DECLARE cFiles CURSOR 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)
    
    OPEN cFiles
    
    IF @UseWindowsAuthentication = 0
    BEGIN
    
    	FETCH NEXT FROM cFiles INTO @vFileName
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    	    SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @SQLServer + ' -U ' + @UserName + ' -P ' + @Password + ' -d ' + @Database + ' -i "' + @FilePathToSQLFiles + @vFileName + '" >>"' + @FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @SQLServer + '_' + @Database + '.txt"'''
    	    --PRINT @vSQLStmt
    			EXECUTE (@vSQLStmt)
    			FETCH NEXT FROM cFiles INTO @vFileName
    	END
    
    END
    
    IF @UseWindowsAuthentication = 1
    BEGIN
    	
    	FETCH NEXT FROM cFiles INTO @vFileName
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    	    SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @SQLServer + ' -E -d ' + @Database + ' -i "' + @FilePathToSQLFiles + @vFileName + '" >>"' + @FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @SQLServer + '_' + @Database + '.txt"'''
    	    --PRINT @vSQLStmt
    			EXECUTE (@vSQLStmt)
    			FETCH NEXT FROM cFiles INTO @vFileName
    	END
    
    END
    
    CLOSE cFiles
    DEALLOCATE cFiles
    
    Print '#################################################################################################'
    Print 'Please review the log file located at ' + @FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @SQLServer + '_' + @Database + '.txt'
    Print '#################################################################################################'
    GO
    DROP TABLE #SQLFiles
    GO
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Woah... didn't expect that

    Much much appreciated....

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Thrasymachus
    just giving it away today...
    Slut

    This message is to short
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •