Apply scripts to UAT server

Question

Apply scripts to UAT server

Mr.No

SSC-Addicted

Points: 440
More actions
April 26, 2007 at 3:11 am

#98280

Hi
I have around 100 sql scripts from development server to the UAT database. I have once script per table creation, and one script per stored procedure and I wanted to know if there's an alternative to me loading each script one at a time in Query Analyser and executing it.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

You could use EM to generate a single T-SQL script. Right-click on the database name > All Tasks > Generate Sql Script. Click on the show all button and you can then select which objects to script.

Click on the Options tab and ensure that any permissions, indexes, triggers and keys are copied as necessary. Also check that the Files To Generate option is set to Create One File.

You can then run this single script in query analyzer.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Mr.No SSC-Addicted Points: 440 More actions · Answer 2

Thanks Adrian but I wanted to avoid the route you suggest since the EM sometimes generates other informations like collation etc. which I'd prefer not to carry over.

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

Do you have these scripts already saved as .sql or .whatever files in a directory?

* Added *

If you have, you could use the procedure below utilising a function that reads files. This function can be found at: http://www.simple-talk.com/code/WorkingWithFiles/uftReadFileAsTable.txt

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spExecuteSqlFromFiles' AND xtype = 'P')

DROP PROCEDURE spExecuteSqlFromFiles

GO

CREATE PROC spExecuteSqlFromFiles(

@path VARCHAR(2000)

&nbsp

AS

SET NOCOUNT ON

DECLARE @dirCmd VARCHAR(2000)

DECLARE @sqlCmd VARCHAR(8000)

DECLARE @file VARCHAR(2000)

-- Retrieve Directory contents to temporary table

CREATE TABLE #dirContents (dirLine VARCHAR(100))

SET @dirCmd = 'DIR ' + @path

INSERT INTO #dirContents

EXEC master.dbo.xp_cmdshell @dirCmd

-- Remove all lines that are not filenames

DELETE FROM #dirContents

WHERE dirLine NOT LIKE '%/.sql%' ESCAPE '/' -- Not a .sql File

-- Loop through files executing each one using oSql

DECLARE csr_FilesToExecute CURSOR FOR

SELECT RTRIM(RIGHT(dirLine, LEN(dirLine) - 39))

FROM #dirContents

OPEN csr_FilesToExecute

FETCH NEXT FROM csr_FilesToExecute INTO @file

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sqlCmd = ''

-- Build Sql Command from file using utfReadFileAsTable

SELECT @sqlCmd = @sqlCmd + line

FROM Dbo.uftReadfileAsTable(@path, @file)

WHERE line not like '#%'

AND line not like '%error%'

EXEC (@sqlCmd)

FETCH NEXT FROM csr_FilesToExecute INTO @file

END

-- Tidy Up

CLOSE csr_FilesToExecute

DEALLOCATE csr_FilesToExecute

DROP TABLE #dirContents

GO

-- Execute the procedure

DECLARE @path VARCHAR(2000)

SET @path = 'C:\scripts\automation\'

EXEC spExecuteSqlFromFiles @path

This is okay if you're sql files are less than 8000 characters. I'm now working on something that is less of a "quick fix" strategy.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Mr.No SSC-Addicted Points: 440 More actions · Answer 4

Thanks Adrian.

I think I'll use the same concept but use a bat file containing one line per osql call where the osql will execute the script.

cheers