String Max Length

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » String Max Length

String Max Length

Rate Topic

Display Mode

Topic Options

Author

Message

Simon Su

Posted Thursday, March 19, 2009 3:48 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 6:39 PM
Points: 23, Visits: 61

I want to delete all the stored procedure I created in the database. I write the following statements:
DECLARE @ssql varchar(8000)
SET @ssql = ''
SELECT @ssql = @ssql + 'Drop procedure ' + name + ' '
FROM sys.objects
WHERE type = 'p'
SELECT LEN(@ssql)
EXECUTE ( @ssql )

An error is appear
I found the max length of the @ssql is 4000.

Thanks

Post #679253

Mark-101232

Posted Thursday, March 19, 2009 4:19 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 2:46 AM
Points: 1,501, Visits: 18,203

sys.objects.name is NVARCHAR

SELECT @ssql = @ssql + 'Drop procedure ' + CAST(name AS VARCHAR(128)) + ' '

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #679264

Ashok.S

Posted Thursday, March 19, 2009 5:02 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, September 27, 2011 10:42 PM
Points: 309, Visits: 135

An error is appear
I found the max length of the @ssql is 4000.

Hi,
The issue is, eventhough you have set the @ssql variable as varchar(8000), it will take only 4000 characters. And the error you are getting is because of the dynamic query what you are constructing is having more than 4000 characters. Hence, I suggest you to use NVarchar(max) for @ssql variable. Hope this will solve the issue.
Regards,
Ashok S

Post #679277

Simon Su

Posted Thursday, March 19, 2009 8:33 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 6:39 PM
Points: 23, Visits: 61

Thank you man!
Tongue

Post #680094

« Prev Topic | Next Topic »

Permissions