Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

debug user defined function in visual studio 2015

Avatar of johnnyg123
johnnyg123Flag for United States of America asked on
Microsoft SQL Server 2008Microsoft Visual Studio
2 Comments1 Solution307 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor MontalvãoFlag of Switzerland imageIT Engineer
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers