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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.