I am trying to query the bamboo database to get the following information. I want to find out information about a trigger , which is that the environment is launched using which branch the plan is based on. The problem is that some environments may be launched by other environments..
So far I have been looking for the Bamboo database, and I seem to have a lot, if not all the information I need. What I did is a table in the Bamboo database called dbo.Deployment_Environment , which has fields EnvironmentID and Triggers_XML_Data strong>, which is what I need from this. Then there is another table called dbo. Deployment_Result , which has an EnvironmentID , Deployment_State, and Trigger_Reason .
From these two tables, it seems to me that I need to do this. select all of these columns, but if trigger_reason contains an environment , and also if triggers_xml_data strong> contains an environment , then getfrom xml_Data strong> triggers (this is under xml node / item / value ). After you get this identifier, select all the same columns and repeat the process if the environment again causes deployment, until it is. The triggers_xml_data strong> data type is ntext and trigger_reason is nvarchar .
I'm not quite sure how I can do this in SQL, this is what I am trying so far, but I cannot get the correct information:
alter proc dbo.myStoredProc3
as
Declare @EnvironmentID int
select
@EnvironmentID as ENVIRONMENT_ID,
de.ENVIRONMENT_ID, dr.DEPLOYMENT_STATE,
de.TRIGGERS_XML_DATA, dr.TRIGGER_REASON, de.NAME
from
dbo.BUILDRESULTSUMMARY as br,
dbo.DEPLOYMENT_ENVIRONMENT as de,
dbo.DEPLOYMENT_RESULT as dr
where
dr.TRIGGER_REASON like '%environment%'
(
select de2.ENVIRONMENT_ID as 'test'
from dbo.DEPLOYMENT_ENVIRONMENT as de2
)
I know that this will not help to get the correct information, but every time I tried to do business or if errors were created in the SQL statement, also: how can I execute this query correctly? I think I need all the information, but if not, I can add more.
I also tried to do the following:
While (@Counter <= 5)
Begin
Select de.NAME As 'Deployment Name', dr.TRIGGER_REASON as 'Trigger Reason', dr.DEPLOYMENT_STATE as 'Status'
from dbo.DEPLOYMENT_ENVIRONMENT as de, dbo.DEPLOYMENT_RESULT as dr
Where de.TRIGGERS_XML_DATA like '%environment%' and dr.TRIGGER_REASON like '%environment%'
SET @Counter = @Counter + 1
END
But this will not bind the columns that I get. If Trigger_Reason belongs to the environment , then in ntext it will have a node that has environmentID , I want to get this and set it as a variable, , , Select name from dbo.Environment where @NewEnvironmentID = de.EnvironmentID.
Edit
SQL-:
alter proc getEnvironmentTriggers
@EnvironmentID int
as
Select a.NAME, a.TRIGGERS_XML_DATA, a.TRIGGER_REASON, a.ENVIRONMENT_ID
From (Select de.NAME, de.TRIGGERS_XML_DATA, de.ENVIRONMENT_ID, dr.TRIGGER_REASON From dbo.DEPLOYMENT_ENVIRONMENT as de
Inner join dbo.DEPLOYMENT_RESULT as dr
on dr.ENVIRONMENT_ID = de.ENVIRONMENT_ID
Where @EnvironmentID = de.ENVIRONMENT_ID and de.TRIGGERS_XML_DATA like '%Environment%' and dr.TRIGGER_REASON like '%Environment%' ) a
, . : exec dbo.getEnvironmentTriggers 15892483, ntext l TRIGGERS_XML_DATA, 18317322, 19234819, , .
:
- , .
- ID (, - ).
ntext SQL, ( node /item/value.- , , .
- , , TRIGGER_XML_DATA, .
, ?