Create View using Extender rule

[Krishna]

Hi All - I am trying to create a View using a Extender BR and It is compainling without any issue but when I try to execute it am getting the below error. Any help would be appreciated.

 Dim vSQL As New Text.StringBuilder()
			   vSQL.AppendLine("CREATE View dbo.v_stageData AS
SELECT
WFP.ProfileName As WorkFLowProfileName,
S.Sn AS SrcScenario,
T.SnT AS TgtScenario,
S.Tm AS SrcTime,
T.TmT AS TgtTime, 
S.Et AS SrcEntity,
T.EtT AS TgtEntity,
S.Ac AS SrcAccount,
T.AcT AS TgtAccount,
S.Fw AS SrcFlow,
T.FwT AS TgtFlow,
S.Og AS SrcOrgin,
T.OgT AS TgtOrgin,
S.Cn AS SrcConsol,
T.CnT AS TgtConsol,
S.Ic AS SrcICP,
T.IcT AS TgtICP,
S.U1 AS SrcU1,
T.U1T AS TgtU1,
S.U2 AS SrcU2,
T.U2T AS TgtU2,
S.U3 AS SrcU3,
T.U3T AS TgtU3,
CAST(S.Am AS DECIMAL(18,2)) AS Amount
FROM StageSourceData S  
INNER JOIN StageTargetData T ON  S.Wfk=T.Wfk AND S.WtK = T.WtK AND S.Ri = T.Ri
INNER JOIN WorkflowProfileHierarchy WFP ON WFP.ProfileKey = S.Wfk
WHERE T.SnT = 'Actual'
AND T.TmT = '2023M1'
AND WFP.ProfileName = 'USA_DataLoad'")
			   
			   Using aDbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
			   Brapi.Database.ExecuteSql(aDbConn,aDbConn.ToString,False)
			   
		   End Using

[prash4030]

Double check your SQL connection string. may be there is any syntax error.

[JackLacava]

I suspect the issue is not in this SQL, but somewhere else in your code - possibly the Imports section. Did you compile it successfully ?

[Henning · Accepted solution]

I believe you have an error in your execution command:

 Brapi.Database.ExecuteSql(aDbConn,aDbConn.ToString,False)

This should be the SQL command you wish to execute I believe

I.e. you need to use the (in your case) vSQL in the second position:

Brapi.Database.ExecuteSql(aDbConn, vSQL, False)

If that does not work, write your vSQL into the error log and analyze it there in order to see the final string that is supposed to be executed:
brapi.ErrorLog.LogMessage(si,"vSQL script: See details below.", vSQL)

[Krishna]

Thanks Henning. It was a good Catch and Now it is working.

Appreciate your quick help.

[Krishna]

There is an another way to create view using the  Table Data Manager..FYI...