I am trying to debug a sql user defined function
I created a stored proc
ALTER PROCEDURE [dbo].[sp_call_bcp_function]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- Add the parameters for the function here
Declare @servername varchar(50)
declare @databasename varchar(50)
Declare @tablename varchar(500)
Declare @FullFilePath varchar(1000)
declare @IncludeColumnHeaders varchar(1)
Declare @WhereClause varchar(1000)
set @servername = 'GVCBA'
set @databasename = 'MarketingDirectMailLists'
set @tablename = 'vDirectMail_Welcome_MailHouseFile'
set @FullFilePath = '\\gvcba\Marketing\DirectMail\2017\May\Welcome\Mailhouse\Welcome_2017_May_2017_04_24_Time_16_29_MailHouse_790.csv'
set @IncludeColumnHeaders= 'Y'
set @WhereClause = 'where campaignid = 381'
SELECT dbo.fnBuildBCPStatement(@servername, @databasename, @tablename, @FullFilePath, @IncludeColumnHeaders, @WhereClause) as BCPSQL
The function is concatenating column names into a variable named @sql which is defined as
DECLARE @Sql varchar(max)
DECLARE BCP_FieldNames_cursor_pass1 CURSOR
FOR SELECT column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS where table_name = @tablename
end
OPEN BCP_FieldNames_cursor_pass1
FETCH NEXT FROM BCP_FieldNames_cursor_pass1 into @ColumnName,@DataType;
SET @Sql = 'bcp "select '
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql += char(39) + convert(varchar(1000),@ColumnName) + char(39) + ','
FETCH NEXT FROM BCP_FieldNames_cursor_pass1 into @ColumnName,@DataType;
END
If I put a break point and look at value of @sql, it only has 255 characters
if I run the entire stored proc with no break points in debug. I get almost 2000 characters
anybody have an idea why???????
only difference is whether breakpoint is set or not
Driving me nuts