Capturing MS SQL output

Question

Possible Duplicate:
How Can i display the output of SQL “PRINT” Command in C#?

I have SQL statement that generates another SQL statement by outputting it via PRINT command. Right now I have to manually run it in SQL editor, copy the resulting string query and run that query in different window.

I'd like to automate that using a .NET console app. Is it possible to capture the PRINT statement output in .NET? Is there another way to capture SQL PRINT output?

score 2 · Answer 1 · answered Sep 29 '12 at 20:29

While I came here looking for the answer to your direct question, that is "How do I capture the output of the print command", I want to expand upon the prior answer.

If you are building an sql command using multiple print statements you can concatenate the pieces in a set command

SET @sqlCommand = @sqlCommand + @newPart;

It's unfortunate but sometimes I run into problems because sqlserver thinks I've exceeded the max size for @sqlCommand when I have not. I get around this by holding the pieces separately, I often need varations of the same piece anyway, then concatenate them in the exec command like this:

EXEC ( 
 'MERGE ' + @targetTable + ' t ' +
 'USING ' + @sourceTable + ' s ' +
 'ON ' + @keycondition + ' ' +
 'WHEN MATCHED AND ' + @updateCondition + ' THEN ' +
   'UPDATE SET ' + @updateColumns + ' ' +
 'WHEN NOT MATCHED BY TARGET THEN ' +
   'INSERT (' + @insertTargetColumns + ') ' +
   'VALUES (' + @insertSourceColumns + ') ' +
 'WHEN NOT MATCHED BY SOURCE THEN '
   'UPDATE SET deleteFlag = ''D'', updDtTm = GETDATE()'
)

Hope this helps. I'm moving away from snapshots to incremental updates so the sourceTable has deleteFlag in it and I don't use the last clause.

score 1 · Answer 2 · answered Jun 08 '12 at 17:00

1

Instead of

Print @sql

Do a

Select @sql

Capture it how you want (ADO, Linq2Sql, EF) and then run the statement normally. By returning the sql as in a recordset, you can rely on the normal way any framework deals with data instead of trying to do something that wasn't designed to actually return data (your sql is actually data being returned).

You can even skip that step if you wanted and do

EXEC sp_executesql @sql

to bypass the entire round trip. EXEC and sp_ExecuteSql documentation.

answered Jun 08 '12 at 17:00

Erik Philips

53,428
11
128
150

What if I have to use Print statement? I don't just select from tables but also output strings that create another sql query. – dev.e.loper Jun 11 '12 at 20:42
If you have to, then I feel sorry for you to be in that position. I have no experience (nor do I wish to) with extracting data out of a print statement. – Erik Philips Jun 11 '12 at 22:08

score 0 · Answer 3 · answered Jun 08 '12 at 17:01

If you are using sql server / oracle you could execute dynamic sql pretty easily

For sql server you can use either EXEC or sp_executesql

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT * FROM SOME_TABLE'
--Use Exec
EXEC (@sqlCommand)
--or you can use the sp_executesql
EXECUTE sp_executesql @sqlCommand

If you are using oracle, then you would require use EXECUTE IMMEDIATE

Capturing MS SQL output

3 Answers3