Send print statements out to text file

Question

Hello, I need to send these print commands out to a text file. How is this done?

PRINT '@UserID, ' + Cast(@UserID AS VARCHAR(MAX));
PRINT '@UserDate, ' + Cast(@UserDate AS VARCHAR(MAX));
PRINT '@LocationID, ' + Cast(@LocationID AS VARCHAR(MAX));
PRINT '@CustomerID, ' + Cast(@CustomerID AS VARCHAR(MAX));
PRINT '@EquipmentID, ' + Cast(@EquipmentID AS VARCHAR(MAX));
PRINT '@ReadingDate, ' + Cast(@ReadingDate AS VARCHAR(MAX));
PRINT '@ReadingType, ' + Cast(@ReadingType AS VARCHAR(MAX));
PRINT '@NumberOfDays, ' + Cast(@NumberOfDays AS VARCHAR(MAX));
PRINT '@Consumption1, ' + Cast(@Consumption1 AS VARCHAR(MAX));
PRINT '@Consumption2, ' + Cast(@Consumption2 AS VARCHAR(MAX));
PRINT '@Consumption3, ' + Cast(@Consumption3 AS VARCHAR(MAX));
PRINT '@Reading, ' + Cast(@Reading AS VARCHAR(MAX));
PRINT '@PrevReadingValue, ' + Cast(@PrevReadingValue AS VARCHAR(MAX));
PRINT '@umBatchID ' + Cast(@umBatchID AS VARCHAR(MAX));

Answer 1

Here is the good example "How to Write in Text File from T-SQL in SQL Server?".

In the Second Step, you can replace the code at line 6 with the following code:

EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, 'PRINT ''@UserID, '' + Cast(@UserID AS VARCHAR(MAX));
PRINT ''@UserDate, '' + Cast(@UserDate AS VARCHAR(MAX));
PRINT ''@LocationID, '' + Cast(@LocationID AS VARCHAR(MAX));
PRINT ''@CustomerID, '' + Cast(@CustomerID AS VARCHAR(MAX));
PRINT ''@EquipmentID, '' + Cast(@EquipmentID AS VARCHAR(MAX));
PRINT ''@ReadingDate, '' + Cast(@ReadingDate AS VARCHAR(MAX));
PRINT ''@ReadingType, '' + Cast(@ReadingType AS VARCHAR(MAX));
PRINT ''@NumberOfDays, '' + Cast(@NumberOfDays AS VARCHAR(MAX));
PRINT ''@Consumption1, '' + Cast(@Consumption1 AS VARCHAR(MAX));
PRINT ''@Consumption2, '' + Cast(@Consumption2 AS VARCHAR(MAX));
PRINT ''@Consumption3, '' + Cast(@Consumption3 AS VARCHAR(MAX));
PRINT ''@Reading, '' + Cast(@Reading AS VARCHAR(MAX));
PRINT ''@PrevReadingValue, '' + Cast(@PrevReadingValue AS VARCHAR(MAX));
PRINT ''@umBatchID '' + Cast(@umBatchID AS VARCHAR(MAX));'

Basically you need to replace the single quote with the double one around the variables.

Answer 2

Thank you GuoxiongYuan-7218. I assume I will need to do this for all the print commands then.

Answer 3

Replace the code at line 6 with the following one:

EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, 'PRINT ''@UserID, '' + Cast(@UserID AS VARCHAR(MAX));
PRINT ''@UserDate, '' + Cast(@UserDate AS VARCHAR(MAX));
PRINT ''@LocationID, '' + Cast(@LocationID AS VARCHAR(MAX));
PRINT ''@CustomerID, '' + Cast(@CustomerID AS VARCHAR(MAX));
PRINT ''@EquipmentID, '' + Cast(@EquipmentID AS VARCHAR(MAX));
PRINT ''@ReadingDate, '' + Cast(@ReadingDate AS VARCHAR(MAX));
PRINT ''@ReadingType, '' + Cast(@ReadingType AS VARCHAR(MAX));
PRINT ''@NumberOfDays, '' + Cast(@NumberOfDays AS VARCHAR(MAX));
PRINT ''@Consumption1, '' + Cast(@Consumption1 AS VARCHAR(MAX));
PRINT ''@Consumption2, '' + Cast(@Consumption2 AS VARCHAR(MAX));
PRINT ''@Consumption3, '' + Cast(@Consumption3 AS VARCHAR(MAX));
PRINT ''@Reading, '' + Cast(@Reading AS VARCHAR(MAX));
PRINT ''@PrevReadingValue, '' + Cast(@PrevReadingValue AS VARCHAR(MAX));
PRINT ''@umBatchID '' + Cast(@umBatchID AS VARCHAR(MAX));'

Answer 4

It seems the posted code is blocked. Here is the screenshot:

Answer 5

If you want the output to file when you run from SSMS, you can press Ctrl-Shift-F to redirect the output to a file. (SSMS will prompt you for a file name.) This is also available in the Query->Results To submenu .

Another option is to use the command-line tool SQLCMD to run the script and use the -o option redirect the output to a file.

I'm mentioning this, as you may find this more lightweight than using sp_OAmethod.