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