How to execute a long dynamic query (greater than 4000) characters - again

Question

Note: I'm running under SQL Server 2008 R2...

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:

EXEC (@SQLQuery1 + @SQLQuery2)

This doesn't work for me - the query is truncated at the end of @SQLQuery1.

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.

I have SQL View with a name of "Company_A_ItemView".

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.

If you offer help, please don't ask why there are multiple views - just accept that I need to do it this way, OK?

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:

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  

...you get the idea

So, what I am currently doing is:

a. Pulling the contents of the CREATE VIEW statement into 2 Declared Variables, e.g.

Set @SQLQuery1 = (select text 
                  from syscomments 
                  where ID = 1382894081 and colid = 1)  
Set @SQLQuery2 = (select 
                  from syscomments 
                  where ID = 1382894081 and colid = 2)

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.

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.

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

SET @SQLQueryNew1 = @SQLQuery1   
SET @SQLQueryNew1 = REPLACE(@SQLQueryNew1, 'Company_A', @CompanyID)  
SET @SQLQueryNew2  = @SQLQuery2   
SET @SQLQueryNew2 = REPLACE(@SQLQueryNew2, 'Company_A',@CompanyID)

c. I then try:

EXEC (@SQLQueryNew1 + @SQLQueryNew2)

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.

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

I've looked at the result of retrieving the original CREATE VIEW statement, and it's fine.

I've tried various other ways of retrieving the original CREATE VIEW statement, such as:

Set @SQLQuery1 = (select VIEW_DEFINITION) 
                  FROM [MY_DATABASE].[INFORMATION_SCHEMA].[VIEWS] 
                  where TABLE_NAME = 'Company_A_ItemView')`

This one returns only the first 4000 characters of the CREATE VIEW

Set @SQLQuery1 = (SELECT (OBJECT_DEFINITION(@ObjectID))

If I do a

SELECT LEN(OBJECT_DEFINITION(@ObjectID))

it returns the correct length of the query (e.g. 5191), but if I look at @SQLQuery1, or try to

EXEC(@SQLQuery1), the statement is still truncated.

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.

SET @SQLQueryNew1 = SELECT (CAST(REPLACE(@SQLQueryNew1, 
                                 'Company_A', 
                                 @CompanyID) AS varchar(max))

Same result.

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.

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.

Solution

Just use VARCHAR(MAX) or NVARCHAR(MAX). They work fine for EXEC(string).

---

FYI,

Note that this is how SQL stores long definitions - when you create the view, it stores the text into multiple syscomments records.

This is not correct. This is how it used to be done on SQL Server 2000. Since SQL Server 2005 and higher they are saved as NVARCHAR(MAX) in a single entry in sys.sql_modules.

syscomments is still around, but it is retained read-only solely for compatibility.

---

So all you should need to do is to change your @SQLQuery1,2,etc. variables to a single NVARCHAR(MAX) variable, and pull your View code from the [definition] column of the sys.sql_modules table instead.

Note that you should be careful with your string manipulations as there are certain functions that will revert to (N)VARCHAR(4000) output if all of their input arguments are not (N)VARCHAR(MAX). (Sorry, I do not know which ones, but REPLACE() may be one). In fact, this may be what has been causing so much confusion in your tests.