ColdFusion Muse

Great Tip - Capturing "PRINT" output from T-SQL

When I write stored procedures I often throw in a few print statements to help me understand what is going on. For example, if I have a routine that loops through table A and updates table B based on some conditions, I might include a print statement that said something like PRINT 'Condition Met' or PRINT 'Condition Not Met'. I could also output the values of a column or 2 to indicate why a condition was or was not met. That's a neat trick in Query analyzer and it makes debugging stored procedures easier. When you move the procedure to CF the PRINT output is lost or ignored. That is usually exactly what you want. But what if you did want to see the PRINT output? Is there a Coldfusion way to do id? Thanks to this great tip from Shlomy Gantz you can!

This tip requires isql.exe. That's a command line tool for running SQL statements. Put simply, you can use Cfexecute to run the SP on the command line and capture the output back to a variable. If you are running an SP as a batch you could capture it back to a file. Here's the code.

<!--- Creating SP --->
<cfquery name="createSP" datasource="test">
PRINT 'Coldfusion Muse'
PRINT 'Is a real Snooze'

   name="C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql.exe"
   arguments='--U [baba] -P [ganush] -S "[fancypants]" -d "test" -Q "test.dbo.SP_bogusPrint"'


<!--- Display the result set e --->
<cfdump var="#printOut#">

Hugh Neutron says, "Now ya gotta admit that's pretty neat!"

  • Share: