Thursday, February 6, 2020

[Powershell] Getting the full text out of a field in SQL when SSMS won't work

We have some code that is basically table-driven SSIS. The advantage is: no digging through SSIS! The disadvantage is that if someone decides to put in code instead of a stored procedure, it's hard to get it out of SSMS.

I wish I knew a better way to handle this, but this works fine.

#This uses the wondrous invoke-sqlcmd
. c:\powershell_scripts\invoke-sqlcmd2.ps1

$query = @"
SELECT source_sql FROM etl_me.dbo.data_flow_me_config where id = 1
"@

$fullquery = invoke-sqlcmd2 -ServerInstance mem-pr-cde-01 -Query $query


First, to get the data out, I tried:
$fullquery.source_sql

Then I realized I could do this, if I only had one column:
$fullquery[0]

But that can fall down.  This one is slightly better.  Add more columns if you need them - if they're nonexistent, it still works.

$fullquery|%{$_[0] #, $_[1], $_[2]
"-------------------"}