reading a sql server table field which is a paragraph
stored with carriage return. Need to format it (break it into
different lines wherever it encounters a carriage return)in a DTS job.

I tried to split the variable whc stores the value of paragraph whereever it encounters chr(13) and put it into a array bt doesnt work

from books online
Building Statements at Run Time
  New Information - SQL Server 2000 SP3.

Most Microsoft® SQL Server™ applications that have to dynamically build SQL statements at run time do so before calling a database API function or method to execute the statement. For example, a C-language application using ODBC can dynamically build one or more SQL statements into a character array, then pass that array to the ODBC SQLPrepare or SQLExecDirect functions.

Transact-SQL supports two methods of building SQL statements at run time in Transact-SQL scripts, stored procedures, and triggers:

Use the sp_executesql system stored procedure to execute a Unicode string. sp_executesql supports parameter substitution similar to the RAISERROR statement.


Use the EXECUTE statement to execute a character string. The EXECUTE statement does not support parameter substitution in the executed string.
This is a simple example of using sp_executesql to execute a dynamically built string containing an SQL statement:

USE Northwind
DECLARE @SQLString NVARCHAR(500)

/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @SQLString = N'SELECT FirstName, LastName, Title' + CHAR(13)

/* Set FROM clause with carriage return, line feed. */
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)

/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''

EXEC sp_executesql @SQLString
GO