0

Is there any way to save the PRINT buffer?

Example, I have a process that runs many Stored Procedures (nested) that prints a lot of information which I need to save. The Stored Procedures are encrypted. I would like to create a Stored Procedure that calls the main SP of the process and save the PRINT statement results. Is it possible?

I thought of a work around: execute master..xp_cmdshell and sqlcmd with -o parameter and import the output file to a table. Anybody know a better solution?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Nilson VisualStudio
  • 1
  • 1
    "The Stored Procedures are encrypted" - my sympathy. How did you end-up in that situation? (And you do know it's often straightforward to decrypt them, right?) – Dai Apr 12 '22 at 23:37
  • 1
    Have you considered an SQLCLR approach? The `print` output appears via the [`SqlConnection.InfoMessage`](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.infomessage) event handler as does the output from `raiserror` messages, so it's not part of the tabular rows that you can typically capture by inserting the stored procedure output into a table. – AlwaysLearning Apr 13 '22 at 00:15
  • Another option is a Powershell or C# script, which can also use `SqlConnection`. You cannot capture `PRINT` messages via T-SQL – Charlieface Apr 13 '22 at 00:24
  • @Charlieface Just a mad thought, but can't T-SQL `OPENROWSET` connections (which use OLE-DB) capture print messages? And if so, could a `localhost`-loopback `OPENROWSET` be used to capture print messages that way? – Dai Apr 13 '22 at 07:34
  • @Dai That's not a hack I want to even try (and it is a hack). `PRINT` messages are for debugging T-SQL, they should be used as such. They are not meant for consumption by T-SQL, it's simply the wrong tool for the job. Honestly, using `SqlConnection` is so much easier – Charlieface Apr 13 '22 at 09:10
  • "`PRINT` messages are for debugging T-SQL" - They have other uses: I use `RAISERROR NOWAIT` messages for passing result-set-names to clients invoking multiple-result-set procedures and batches, so my hand-written mapping methods can correctly populate multi-table datasets and object-graphs from a single sproc call. – Dai Apr 13 '22 at 09:15
  • look at this: https://stackoverflow.com/questions/15292273/how-can-i-capture-the-print-message-from-a-sql-server-stored-procedure – Johannes Krackowizer Apr 13 '22 at 13:04

0 Answers0