sp_executesql PROBLEM

Question

Post reply

sp_executesql PROBLEM

tygun

SSCrazy

Points: 2381
More actions
October 22, 2002 at 8:20 am

#79586

I have a stored procedure that creates a string, per different variables. This NVarchar string is then executed utilizing sp_executsql. My problem is this. My string has exceed 4000 characters and is erroring.
Is there a way around this?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

Never had that problem, but might try one of these two options. Hope this helps:

-- MIGHT TRY SOMETHING LIKE THIS

declare @x varchar(8000)

set @x = 'SELECT ''Place string longer than 4000 bytes here'', * FROM SYSOBJECTS'

PRINT @X

exec sp_executesql N'EXEC (@x)',N'@x varchar(8000)',@x=@x

-- OR THIS

set @x = 'SELECT ''Place string longer than 4000 bytes here'', * FROM SYSOBJECTS'

EXEC (@X)

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

tygun SSCrazy Points: 2381 More actions · Answer 2

Very nice Greg. I tried the first one and it worked great.

I truly appreciate your help.

Scott

quote:
Never had that problem, but might try one of these two options. Hope this helps:
-- MIGHT TRY SOMETHING LIKE THIS
declare @x varchar(8000)
set @x = 'SELECT ''Place string longer than 4000 bytes here'', * FROM SYSOBJECTS'
PRINT @X
exec sp_executesql N'EXEC (@x)',N'@x varchar(8000)',@x=@x
-- OR THIS
set @x = 'SELECT ''Place string longer than 4000 bytes here'', * FROM SYSOBJECTS'
EXEC (@X)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

Both these work because they convert the data to varchars rather than nvarchars. So if you are using unicode you will have a problem.

To get around it you have to declare n strings to hold the data, substring column into relevant sections.

i.e.

select @data1 = substring(laregcolumn,1,4000)

,@data2 = substring(laregcolumn,4001,4000)

My script at http://www.sqlservercentral.com/memberservices/updatescript.asp?Approve=y&scriptid=527 takes this to the unlimited level

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

tygun SSCrazy Points: 2381 More actions · Answer 4

Greg, how would you handle this matter if it goes over 8000. My problem is that I am building tables dynamically for production to a website. Our customers can continue to add information which will cause the dynamically created script to grow. At this point your idea works, but what about down the road? Any help you can give would be great.

If you need more info, please do not hesitate to ask.

Thanks

quote:
Very nice Greg. I tried the first one and it worked great.
I truly appreciate your help.
Scott
quote:
Never had that problem, but might try one of these two options. Hope this helps:
-- MIGHT TRY SOMETHING LIKE THIS
declare @x varchar(8000)
set @x = 'SELECT ''Place string longer than 4000 bytes here'', * FROM SYSOBJECTS'
PRINT @X
exec sp_executesql N'EXEC (@x)',N'@x varchar(8000)',@x=@x
-- OR THIS
set @x = 'SELECT ''Place string longer than 4000 bytes here'', * FROM SYSOBJECTS'
EXEC (@X)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 5

You will need to use more variables and then executed the like this

EXECUTE (@data1 + @data2 + @data3 +....)

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 6

-- might try this, althouhg I suppose you might want to support many more than 3 8000

-- character strings, possibly you could dynamically build the number of strings needed.

-- Also note that these could be nvarchar(4000) variables if you really are concerned

-- about the unicode as pointed out.

declare @x varchar(8000)

declare @y varchar(8000)

declare @z varchar(8000)

set @x = 'SELECT ''Place first 8000 characters here, '

set @y = 'second 8000 here,'

set @z = ' third 800 here, and so on '', * FROM SYSOBJECTS'

--exec sp_executesql N'EXEC (@x + @y + @z)',N'@x varchar(8000),@y varchar(8000),@z varchar(8000) ',@x=@x,@y=@y,@z=@z

--OR THIS

EXEC (@X + @y + @z)

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 7

Exec (@A1+@A2..) works since 6.5 with 255 character limit.

Generate command strings, as needed, inserting into a table.

Get proc to run the whole lot one after the other(limit round tripping).

What about using DMO if its table creation,etc.