March 14, 2016 at 7:03 pm
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.
March 15, 2016 at 2:59 am
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.
March 15, 2016 at 10:00 am
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
March 16, 2016 at 2:26 am
Obtianing the script for creating the stored procedures is not the problem creating stored procedures unattended is.
March 16, 2016 at 2:27 am
I have the code in an SQL file.
We don't use source safe for SQL and do not have a build server.
March 16, 2016 at 3:05 am
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.
March 16, 2016 at 6:51 am
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
March 16, 2016 at 9:13 am
You could use snapshot replication and just snap the data, not the stored procs...
Specify Article Types (Replication Transact-SQL Programming)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply