Automate stored procedure creation

Question

Automate stored procedure creation

ErickTreeTops

Ten Centuries

Points: 1253
More actions
March 14, 2016 at 7:03 pm

#314104

I have a Test database which is overwritten each week by a fresh new production copy.
But we have changes in our Test environment which I script back in manually each Monday morning.
Is there a way to schedule script code to run which can generate these stored procedures etc. for me in case I'm away ?
The Job scheduler in SQL Server can import a SQL script, but it's not dynamic.
I need something that I can use so that at run time it will pick up the latest script changes and generate the objects.

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

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

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

sys.sql_modules should be helpful in getting the current definitions from the database. Using this system table you can extract the procedure's current definitions to compare them to wherever you keep your definitions and generate what's needed to put the correct versions back in.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Do you have the database code in source control? Do you have any kind of build server (msbuild, jenkins, ...)? If both are ture you should be able to create a build process that you can schedule to run after the restore happens.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ErickTreeTops Ten Centuries Points: 1253 More actions · Answer 3

Obtianing the script for creating the stored procedures is not the problem creating stored procedures unattended is.

ErickTreeTops Ten Centuries Points: 1253 More actions · Answer 4

I have the code in an SQL file.

We don't use source safe for SQL and do not have a build server.

brk5tr Ten Centuries Points: 1360 More actions · Answer 5

Create an Agent Job to execute a script containing the object definitions each day. Alternatively, run a windows batch file to execute the creation scripts.

Lowell SSC Guru Points: 323518 More actions · Answer 6

you could do something like this:

a combination of the ccommandline type command and xp_cmdshell, you can ge tthe file contents of your *.sql files and execute them.

I just built this prototype, which assumes a single file, but it could use xp_cmdnshell to list all *.sql files in a specific folder,and do each file.

declare @TheCommand varchar(max)

--i KNOW where this specific file is on the server

--C:\_VMShared\sp_dbspaceused.sql

--table to capture results

IF OBJECT_ID('tempdb.[dbo].[#Results]') IS NOT NULL

DROP TABLE [dbo].[#Results]

CREATE TABLE [dbo].[#Results] (

[ResultsID] INT IDENTITY(1,1) NOT NULL,

[ResultsText] VARCHAR(max) NULL)

--use the TYPE command to echo out the flat file contents, and get it into my table

insert into #Results (ResultsText)

exec master..xp_cmdshell 'type C:\_VMShared\sp_dbspaceused.sql'

--lets review what's really in it

SELECT * FROM #Results WHERE ResultsText IS NOT NULL ORDER BY ResultsID

--get this multi row data into a single variable so i can execute it!

SELECT @TheCommand = s.Colzs

FROM(SELECT

Colzs = STUFF((SELECT ' ' + CONVERT(varchar,ResultsText )

FROM #Results

ORDER BY ResultsID

FOR XML PATH('')

),1,1,'')

) s

select @TheCommand

--EXEC(@TheCommand)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jon.Morisi SSChampion Points: 12846 More actions · Answer 7

You could use snapshot replication and just snap the data, not the stored procs...

Specify Article Types (Replication Transact-SQL Programming)