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
      -- 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

                  OPEN BCP_FieldNames_cursor_pass1

                  FETCH NEXT FROM BCP_FieldNames_cursor_pass1 into  @ColumnName,@DataType;
                  SET @Sql = 'bcp "select '
                  WHILE @@FETCH_STATUS = 0
                                                      SET @Sql += char(39) +  convert(varchar(1000),@ColumnName)  + char(39) + ','

                          FETCH NEXT FROM BCP_FieldNames_cursor_pass1 into  @ColumnName,@DataType;

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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros