ash_m (8/31/2016)

---

Thank you Jeff.

I tweaked code a bit by introducing a view and it worked!

I created a view in ash_test procedure which holds the required output data (that way ignored validation messages).

Now, OPENROWSET is happy to load data from a view to temp table.

Rest of code was smooth enough to create a output text file with header and footer 🙂

Changed code as follows:

IF object_id('tempdb..##SwitchInput') IS NOT NULL DROP TABLE ##SwitchInput;

DECLARE

@tempsql AS varchar(max)

,@execstring AS varchar(max) = 'SELECT * FROM ash_vw';

SET @tempsql = 'SELECT * INTO ##SwitchInput FROM OPENROWSET(''SQLNCLI'', ''Server=vm01;Trusted_Connection=Yes;'','''+@execstring+''')';

EXEC (@tempsql);

ash_test:

CREATE PROCEDURE [dbo].[ash_test]

AS

BEGIN

SET NOCOUNT ON;

DECLARE

@str1 AS varchar(100)=''

,@str2 AS varchar(100);

EXEC Caspar.dbo.[inside_proc] 'I am proc inside proc'

SET @str2 = 'I am top most proc';

CREATE VIEW AS ash_vw

select @str1 as str1,@str2 as str2

SET NOCOUNT OFF;

END

GO

Now, when I execute ash_test procedure, validation errors could be seen on the screen, if any.

Once validation is passed, it creates a view with required data.

Later, execute aforesaid steps to create output file.

Thank you very much for your suggestions and timely response.:-)