ash_m (8/30/2016)
Hi Jeff,Data doesn't get loaded in temp table, fails at EXEC(@tempsql) statement.
Error:
Cannot process the object "exec ash_test". The OLE DB provider "SQLNCLI11" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
ash_test procedure does 2 things:
1> Print validation messages
2> Print output data
If I'm guessing it correctly, it is trying to grab validation messages in resultset and fails with aforesaid error.
Nonetheless, I need to ignore validation messages and load "output data" into temp table.
Later, I'll add header and footer to it and send it to output text file.
I don't believe there's a way to do that with OPENROWSET or any other built in functionality. You either need to add a parameter to each stored procedure and modify the messaging in the proc to "switch off" the validation messages depending on the value of the parameter or you're going to need to do a bulk operation where you can capture the lines of output and decide if they're data or not, parse the lines of data, and then "warp" them into a file. In other words, because the mix of the presentation and data layers isn't option in your procs, you may have to resort to proverbial "screen scraping" on the return. The SQLCMD call through xp_CmdShell I was talking about is one way to do that but only if the returns aren't too wide. I don't remember what the max width of the output is without an eventual forced wrap but you may have to embed a DOS "MODE" command to change the "COLS" setting to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.