January 3, 2007 at 12:46 pm
This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure.
I have my SQL string exeeding more than 4000 characters.
The sp_executesql expects its parameters to be declared as nvarchar/ntext.
ntext cannot be declared for a local variable and nvarchar has a maximum limit of 4000 characters.
The following was the original approach.
===============
DECLARE @sql nvarchar(4000),
---
--
select @sql=@sql+'select.................'
--
--
INSERT INTO #Temp
EXEC sp_executesql @sql,@paramlist
===============================
The above sql was split into two sql variables and tried in the following way.
==============================
DECLARE @sql nvarchar(4000), @sql1 nvarchar(4000),@paramlist nvarchar(4000)
---
--
select @sql=@sql+'select.................'
select @sql1=@sql1+'from table1, table2.........'
--
--
INSERT INTO #Temp
exec('EXEC sp_executesql ''' + @sql + @sql1 + ''',@paramlist')
====================
However, it compiles correctly but during the execution, the error indicates as syntax error.
I ran the resulting SQL string (@sql + @sql1) separately, and it runs fine without any syntax errors returning rows, but when execute through
sp_executesql, it gives me error
Appreciate if anyone can suggest a workaround for this situation.
January 3, 2007 at 1:00 pm
the EXEC command has no limitation to the length of the command, so you can append variables to death with it..., but sp_executesql has a parameter that has a limit.
can you just run EXEC(sql1+sql2+sql3) instead of sp_executesql? are you returning parameters that need to be captured, and that is why you are using executesql? maybe you can put the results in a temp table and use them after the EXEC command?
Lowell
January 4, 2007 at 2:15 am
You must also watch out for spaces or actually the lack thereof.
Eg: SET @Sql1 = 'Select field'
SET @Sql2 = 'From Table'
EXEC (@Sql1 + @Sql2) will fail bcause @Sql1 + @Sql2 = 'Select fieldFrom Table'
5ilverFox
Consulting DBA / Developer
South Africa
January 4, 2007 at 4:09 am
Sometimes it's unavoidable but I try not to use dynamic SQL whenever I have a choice - especially if it's going to be that big ! What are you trying to achieve? Is there another approach that you could take?
January 4, 2007 at 8:09 am
Hi,
the workaround you are attempting is correct. Keep in mind that sp_executesql requires an NVARCHAR parameter. Prefix your string with an N. See this example:
DECLARE @str1 VARCHAR(8000) DECLARE @str2 VARCHAR(8000) SET @str1 = 'SELECT ''''This is a really long SQL statement ''''' SET @str2 = 'UNION ALL SELECT ''''This is a really long SQL statement''''' EXEC(N'sp_executesql N''' + @str1 + @str2 + '''')
SQL guy and Houston Magician
January 4, 2007 at 10:28 am
The Problem is resolved. I am now using EXEC(sql1+sql2) instead of sp_executesql. I have changed the logic such that I need not require any of the returning parameters that needs to be captured through sp_executesql.
Thanks for your suggestions.
April 22, 2012 at 1:24 am
Hi all,
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. As mentioned in this thread, the most common solution discussed is to to split the query into 4000-character variables and then do:
EXEC (@SQLQuery1 + @SQLQuery2)
This doesn't work for me - the query is still truncated at the end of @SQLQuery1.
Now, I've seen samples how people "force" a long query (similar to the example in this thread, but my query involves executing a "CREATE VIEW" statement.
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 respond to this message, 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 excerpt, 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 Declared Variables. I've tried a variety of methods (see below), but my current attempt pulls the query into 2 declared variables from the syscomments table, 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)
I've discovered 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. After I load the 2 declared variables, I then do a "Search and Replace" for the string "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, which is the first part of the full query's text. But that variable is only the first 3159 characters of the statement.
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 into the 2 declared variables, and they are what I expect - the first variable has the first 3159 characters, and the second variable continues with the remainder of the statement.
I've tried various other ways of retrieving the original CREATE VIEW statement, such as:
a. 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
b. 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 PRINT @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, except reference Company_B's view and table names, Company_C's view and table names, etc.
So, has anyone ever had to do this? I'm guessing that it MIGHT be because I'm manipulating the text string before executing it, but don't have any real proof - but there are message-thread posts that imply that this might be the issue.
Any other ideas? Does anyone think that perhaps "re-loading" the text of the query into a new variable, perhaps with a loop 500 characters at a time, might help? I know it's a wierd workaround, but the standard solution, which both people on this forum and Microsoft say works, does not work for me.
Any help or options would be appreciated.
Ron
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply