Solved

Problem with triggering SP_EXECUTESQL with a @stmt-Parameter > 4000 characters ...

Posted on 2006-06-20
8
693 Views
Last Modified: 2012-05-05
Hi all,

My platform is Windows 2000 Server with a SQL Server 2000 running.

I hav a Stored Procedure on my SQL-Database which builds and executes a specific query dynamically using 'exec SP_EXECUTESQL @MySQLStatement' .

Therefor I declared a local var @SQL of type nvarchar(4000).
This variable is filled up with a large SQL-Statement (depending on some input-parameters of the Stored Procedure).

Now the problem is that in some cases my built SQL-Statement is getting larger than 4000 characters. And then I get an error of course because my SQL-Statement is not complete.

Is there a way to work around this problem and make use of another type of variable or do some conversion or ...?

I found something about ntext-variables, but I can't declare them as local vars in my Stored Procedure.

Anyone having a solution for my problem ?
Thanks in advance,

The Mayor.
0
Comment
Question by:wimmeyvaert
  • 4
  • 3
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16940995
I am not aware of workarounds.
the only thing I would check out is if you could not make the query shorter.
for example, if you use table names -> use short aliases
you might create views for this kind of things with shorted column names (for the internal use only)
etc
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16941037
wimmeyvaert,
> I found something about ntext-variables, but I can't declare them as
> local vars in my Stored Procedure.

Create the nText variable as an argument, and pass a null value from the front end, use this variable to store the dynamic string

CREATE Proc TestSP
@arg1 varchar(10)
,@nSQL ntext = null
AS
BEGIN
     SET @Nsql = ''
     SET @nSQL  = 'SELECT ...'

   EXEc  sp_ExecuteSQL @nText


END
GO



sp call will be like this

exec TestSP @arg1='tablename'
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 16941141
Hi,

@AngelIII : I aleardy make use of aliases
                 Maybe you have a point when you talk anout views. I will investigate this.

@aneeshattingal : Looks good. But I get an error when assigning something to the ntext-parameter. The error is 'Error 409 : THe assignment operator cannot take a ntext data type as an argument'.

I will do some investigation with the 2 comments I got this far. I'll get back from the moment I have news.

Thanks a lot for your fast support.

Best regards,

The Mayor.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 6

Author Comment

by:wimmeyvaert
ID: 16941647
Hi all,

I tried to work with a View instead, but it is not possible to setup a View because it is the SELECT-clause of my SQL-statement that takes the most characters.

I was not able to get aneeshattinga's code to work (so far).

Since the stored procedure is triggered from within a self written application (in Delphi), I made the workaround in my application.
I changed the Stored procedure so that it now returns the SQL-Statement instead of running it.
Therefor I make use of a varchar(8000) output parameter.

From within my application I now trigger the Stored Procedure and the catch the output-param. Next I trigger a new query which I fill with the value of the output-param of my Stored Proc.

I did it this way because I needed to have a quick solution for my problem (for urgent reporting tools used in our company).

This is working now, altough I prefer to have a final solution and make use of the  sp_ExecuteSQL from within my Store Procedure.

So I will leave this question open for some time and pray that there is someone out there who has a solution.

Best regards,

The Mayor.

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16941730
> I was not able to get aneeshattinga's code to work (so far).
I will try on this
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 16941778
read this

sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005, use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @sql1 nvarchar(4000),
        @sql2 nvarchar(4000),
        @state char(2)
SELECT @state = 'CA'
SELECT @sql1 = N'SELECT COUNT(*)'
SELECT @sql2 = N'FROM dbo.authors WHERE state = @state'
EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
                         N''@state char(2)'',
                         @state = ''' + @state + '''')
This works, because the @stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.

You can even use output parameters by using INSERT-EXEC, as in this example:

CREATE TABLE #result (cnt int NOT NULL)
DECLARE @sql1  nvarchar(4000),
        @sql2  nvarchar(4000),
        @state char(2),
        @mycnt int
SELECT @state = 'CA'
SELECT @sql1 = N'SELECT @cnt = COUNT(*)'
SELECT @sql2 = N'FROM dbo.authors WHERE state = @state'
INSERT #result (cnt)
   EXEC('DECLARE @cnt int
         EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
                            N''@state char(2),
                               @cnt   int OUTPUT'',
                            @state = ''' + @state + ''',
                            @cnt = @cnt OUTPUT
         SELECT @cnt')
SELECT @mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.



the original article can be viewed from
http://www.sommarskog.se/dynamic_sql.html

0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 16941934
Hi aneeshattingal,

This could be a solution for me.
I test it out and let you know.

Thanks.

The Mayor.
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 16949050
Thanks to all for your help/input !!!

Best regards,

The Mayor.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
script to send html report 3 32
SSRS 2013 - Overlapping reports 2 19
VBScript Write Column Headers 3 33
SQL - Update field defined as Text 6 15
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question