Having many Databases with tons of stored procedure I thought it might be a good idea to have them in a Source Control repository. Having programmatic experience with TFS and a bit of time on my hands I decided to give it a shot.
These where my requirements for the applications:
1) As integrated as possible
2) No additional tools to be installed (avoid additional configuration on a different pc)
3) Avoid performance issues
4) Multi-user enabled
5) Scalable and extendable
After a few try-outs and experience from other users in forums I decided to use a trigger which is fired after the create/update of a stored procedure. This trigger should then put the stored procedure into the Source Repository.
Having googled the best possible approach, I identified these limitations:
1) It is not a good idea to call a Web service directly via T-SQL.
2) It is not recommended to register a DLL when the processing might take some time.
I always design my applications multi-tier where each application has its specific and dedicated function. So here is how I did it:
Application design
On all my database servers I have database called COMMON. Here I created a table called SQL_OBJECT:
CREATE TABLE SQL_OBJECT (
SOL_NUM INT PRIMARY KEY NOT NULL,
SOL_DATA NVARCHAR(MAX) NOT NULL,
SOL_USERNAME NVARCHARE(500)
SOL_DATETIME datetime NOT NULL,
SOL_TREATED bool NOT NULL)
From here, I created a stored procedure which handles the insert of the SQL text.
CREATE PROC PCMN001_I_InsertSQLText_001
@sText NVARCHAR(MAX), @sUsername NVARCHAR(500)
AS
BEGIN
INSERT INTO SQL_OBJECT
SELECT @Text, @sUsername, GETDATE(), 0
END
This stored procedure is called via a trigger. This trigger is registered to execute on create/update of a stored procedure.
CREATE TRIGGER TMAIN001_StoredSPInTemp_001 on database
for CREATE_PROCEDURE, ALTER_PROCEDURE
as begin
Declare @data xml
set @data=Eventdata()
exec PCMN001_I_InsertSQLText_001 @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(MAX)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(500)'))
END
Now the stored procedures are inserted in the SQL_OBJECT when you create/update your code.
The trigger stores the code and the username in the COMMON.dbo.SQL_OBJECT
Next up we will get all the lines in the SQL_OBJECT which have not yet been treated. To avoid false loading, we need additional handling:
CREATE PROC PCMN002_SU_GetSQLObjects_001
AS
BEGIN
create table #tempids
(
ids int
)
INSERT INTO #tempids
SELECT SOL_NUM
FROM SQL_OBJECT
WHERE SOL_TREATED = 0
SELECT SOL_TEXT, SOL_USERNAME
FROM SQL_OBJECT
WHERE SOL_NUM IN (SELECT ids FROM #tempids)
UPDATE SQL_OBJECT
SET SOL_TREATED = 1
WHERE SOL_NUM IN (SELECT ids FROM #tempids)
END
I call this stored procedure in a Console application which adds them into the Code Repository.
The console application is called via a Windows Scheduled job.
This kept me busy for 2 hours