I want to create a SQL Select Statement but I need to check whether the table in BI Blend exists first.

[OSAdmin]

Originally posted by Eric Osmanski

4/13/2020

I want to create a SQL Select Statement but I need to check whether the table in BI Blend exists first.

[OSAdmin]

Originally posted by Ryan Connors

Dim sqlQuery As New Text.StringBuilder
sqlQuery.Append("Select Count(*) ")
sqlQuery.Append("From INFORMATION_SCHEMA.TABLES " )
sqlQuery.Append("Where 1=1 " )
sqlQuery.Append("AND TABLE_SCHEMA = 'dbo' " )
sqlQuery.Append("AND TABLE_NAME = 'YOUR TABLE NAME'")

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Connect To App DB, Execute SQL, obtain a DataTable With all the records returned By the SQL
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim ds As Datatable
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)

ds = BRApi.Database.ExecuteSql(dbConn, sqlQuery.ToString, True)

End Using

If ds.Rows.Count > 1 Then
brapi.ErrorLog.LogMessage(si, "Exist")
Else
brapi.ErrorLog.LogMessage(si, "Does not exists")
End If

sql.Append("And TRIM('M' FROM RIGHT(TABLE_NAME, 2)) <= " & monthNo & " ")

[amoore]

this should work also!

If (args.FunctionName.XFEqualsIgnoreCase("GetBiBlendTableName"))Then

Dim tableName As String = SharedStringConstants.Unknown
'Get the BI Blend Table Name and throw and error if the table does not exist.
Using dbConnApp As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
Dim biBlendInfo As StageBiBlendInfo = StageBiBlendInfoCrud.GetInfo(dbConnApp, False, si.WorkflowClusterPk)
tableName = biBlendInfo.BlendTableName
If tableName.XFEqualsIgnoreCase(SharedStringConstants.Unknown) Then
Throw New XFException(si, Nothing, Nothing, "BI-Blend Table Does NOT exist for current Workflow.")
End If
End Using

Return TableName