SQL query to connect to database directly from Orchestrator server to fetch the data tables and the details related to them.
From Orchestrator server machine, open PowerShell ISE and execute the below command to get the desired output. This is useful when user does not have access to SQL server machine directly.
Query:
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Data Source=[SERVER_NAME];Initial Catalog=[DB_NAME];User ID=[LOGIN_Username];Password=[LOGIN_Password]" # it can also be used with Integrated Security=True
$sqlConn.Open()
Write-Output $sqlConn.ConnectionString
$sqlcmd = $sqlConn.CreateCommand()
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection=$sqlConn
Write-Output $sqlcmd.Connection
$query = "SELECT * from dbo.logs" # This is a place where you modify your required query.
$sqlcmd.CommandText = $query
$Reader = $sqlcmd.ExecuteReader()
while ($Reader.Read()) {
Write-Output $Reader.GetValue(0) #The value 0 indicates the column no. You can modify as per your requirement which column details you want to fetch.
}
$sqlConn.Close()