title: How to execute a long dynamic query (greater than 4000) characters - again
body: <p>Note: I'm running under SQL Server 2008 R2...</p>
  
  <p>I've taken the time to read dozens of posts on this site and other sites on how to execute dynamic SQL where the query is more than 4000 characters.  I've tried more than a dozen solutions proposed.  The consensus seems to be to split the query into 4000-character variables and then do:</p>
  
  <pre><code>EXEC (@SQLQuery1 + @SQLQuery2)
  </code></pre>
  
  <p>This doesn't work for me - the query is truncated at the end of @SQLQuery1.</p>
  
  <p>Now, I've seen samples how people "force" a long query by using REPLICATE a bunch of spaces, etc., but this is a real query - but it gets a little more sophisticated than that.</p>
  
  <p>I have SQL View with a name of "Company_A_ItemView".</p>
  
  <p>I have 10 companies that I want to create the same exact view, with different names, e.g.
  "Company_B_ItemView"
  "Company_C_ItemView"
  ..etc.</p>
  
  <p>If you offer help, please don't ask why there are multiple views - just accept that I need to do it this way, OK?</p>
  
  <p>Each company has its own set of tables, and the CREATE VIEW statement references several tables by name.  Here's  BRIEF sample, but remember, the total length of the query is around 6000 characters:</p>
  
  <pre><code>CREATE view [dbo].[Company_A_ItemView] as  
  select  
  WE.[Item No_],  
  WE.[Location Code],   
      LOC.[Bin Number],  
  [..more fields, etc.]  
  from  
  [Company_A_Warehouse_Entry] WE  
  left join  
  [Company_A_Location] LOC  
  </code></pre>
  
  <p>...you get the idea</p>
  
  <p>So, what I am currently doing is:</p>
  
  <p>a. Pulling the contents of the CREATE VIEW statement into 2 Declared Variables, e.g.</p>
  
  <pre><code>Set @SQLQuery1 = (select text 
                    from syscomments 
                    where ID = 1382894081 and colid = 1)  
  Set @SQLQuery2 = (select 
                    from syscomments 
                    where ID = 1382894081 and colid = 2)
  </code></pre>
  
  <p>Note that this is how SQL stores long definitions - when you create the view, it stores the text into multiple syscomments records.  In my case, the view is split into a text chunk of 3591 characters into the first syscomment record and the rest of the text is in the second record.  I have no idea why SQL doesn't use all 4000 characters in the syscomment field.  And the statement is broken in the middle of a word.</p>
  
  <p>Please note in all my examples, all @SQLQueryxxx variables are declared as varchar(max).  I've also tried declaring them as nvarchar(max) and varchar(8000) and nvarchar(8000) with the same results.</p>
  
  <p>b.  I then do a "Search and Replace" for "Company_A" and replace it with "Company_B".  In the code below, the variable "@CompanyID" is first set to "Company_B":</p>
  
  <pre><code>SET @SQLQueryNew1 = @SQLQuery1   
  SET @SQLQueryNew1 = REPLACE(@SQLQueryNew1, 'Company_A', @CompanyID)  
  SET @SQLQueryNew2  = @SQLQuery2   
  SET @SQLQueryNew2 = REPLACE(@SQLQueryNew2, 'Company_A',@CompanyID)
  </code></pre>
  
  <p>c. I then try:</p>
  
  <pre><code>EXEC (@SQLQueryNew1 + @SQLQueryNew2)
  </code></pre>
  
  <p>The message returned indicates that it's trying to execute the statement truncated at the end of @SQLQueryNew1, e.g. 80% (approx) of the query's text.</p>
  
  <p>I've tried CAST'ing the final result into a new varchar(max) and nvarchar(max) - no luck
  I've tried CAST'ing the original query a new varchar(max) and nvarchar(max)- no luck</p>
  
  <p>I've looked at the result of retrieving the original CREATE VIEW statement, and it's fine.</p>
  
  <p>I've tried various other ways of retrieving the original CREATE VIEW statement, such as:</p>
  
  <pre><code>Set @SQLQuery1 = (select VIEW_DEFINITION) 
                    FROM [MY_DATABASE].[INFORMATION_SCHEMA].[VIEWS] 
                    where TABLE_NAME = 'Company_A_ItemView')`
  </code></pre>
  
  <p>This one returns only the first 4000 characters of the CREATE VIEW </p>
  
  <pre><code>Set @SQLQuery1 = (SELECT (OBJECT_DEFINITION(@ObjectID))
  </code></pre>
  
  <p>If I do a </p>
  
  <pre><code>SELECT LEN(OBJECT_DEFINITION(@ObjectID))
  </code></pre>
  
  <p>it returns the correct length of the query (e.g. 5191), but if I look at @SQLQuery1, or try to </p>
  
  <pre><code>EXEC(@SQLQuery1), the statement is still truncated.
  </code></pre>
  
  <p>c.  There are some references that state that since I'm manipulating the text of the query after retrieving it, the resulting variables are then truncated to 4000 characters.  I've tried CAST'ing the result as I do the REPLACE, e.g.</p>
  
  <pre><code>SET @SQLQueryNew1 = SELECT (CAST(REPLACE(@SQLQueryNew1, 
                                   'Company_A', 
                                   @CompanyID) AS varchar(max))
  </code></pre>
  
  <p>Same result.</p>
  
  <p>I know there are other methods, such as creating stored procedures for creating the views.  But the views are being developed and are somewhat "in flux", so placing the text of the CREATE VIEW inside a stored proc is cumbersome.  My goal is to be able to take Company_A's view and replicate it exactly - multiple times, except reference Company_B's view name and table names, Company_C's view name and table names, etc.</p>
  
  <p>I'm wondering if there is anyone out there who has done this type of manipulation of a long SQL "CREATE VIEW" statement and try to execute it.</p>