Missing data in the SQL Server Agent Job Output to a File

Question

I have below script which would give me the definitions of all the views in one of my DBs.

SELECT OBJECT_DEFINITION(object_id) FROM sys.objects WHERE[type] = 'V'

This works real good when I run it as a query in SSMS. But my goal is to run this via SQL Server Agent as a batch job, and output the result to a file. When I do that, I find that some parts of the text of the definitions are missing in the file. For example if the query window output is something like below:

CREATE View [xxx].[vw1] as SELECT * FROM [xxx].[vw2]

I get something like below:

CREATE View [xxx].[vw1] as SELECT * FR

Any idea what's going wrong here?

This is how my batch job configuration looks like.

I'd expect to have all the output result in the text file without losing anything.

Answer 1

So I was able to finally put an end to this. The real problem was that the Sql Server could only output a limited number of characters (8192) as text, with SELECT. But there seems to be no such limitation with PRINT. So the solution is;

• Use a cursor
• Loop through all the rows
• PRINT them
• Use sqlcmd to output the result to a plain text file

The query should be something like this.

DECLARE @sql nvarchar(max)

DECLARE cur CURSOR FOR
SELECT OBJECT_DEFINITION(object_id)
FROM sys.objects
WHERE [type] = 'V'

OPEN cur

FETCH NEXT FROM cur INTO @sql;
WHILE @@FETCH_STATUS = 0
BEGIN   
PRINT REPLACE(@sql, '\n', '\r\n')
FETCH NEXT FROM cur INTO @sql;
END

CLOSE cur;
DEALLOCATE cur;

This has to be in a file, so we can let sqlcmd to use that as input.