Avatar of johnnyg123
johnnyg123
Flag for United States of America

asked on 

debug user defined function in visual studio 2015

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
Microsoft SQL Server 2008Microsoft Visual Studio

Avatar of undefined
Last Comment
johnnyg123

8/22/2022 - Mon