Link to home
Start Free TrialLog in
Avatar of Eric3141
Eric3141Flag for Afghanistan

asked on

Dynamic T-SQL getting truncated despite using varchar(max)

When I run this t-sql the length of @vchSQL is only 3990 bytes long.  Also when I print it, it's not the complete text of the stored procedure I'm trying to copy from the other database -- it's getting cut off.  I've gone into SSMS > tools > options and maxed out everything I could find related to queries but this keeps happening.  I tried commenting-out the PRINT statement but the code still throws an error at the same line # anyway.
The procs I'm trying to copy from the other database work fine there so the original code does not have a syntax error.

Suggestions?

declare
	@vchSQL				varchar(max),
	@vchDB_Name			varchar(100),
	@vchRoutine_Name	varchar(100);
	
	SET @vchDB_Name = 'Key_Indicators'
	
	declare c1 cursor for 
	
	SELECT ROUTINE_NAME, ROUTINE_DEFINITION
	FROM Key_Indicators_0.INFORMATION_SCHEMA.ROUTINES
	WHERE ROUTINE_TYPE IN ('Function', 'Procedure') AND
	      ROUTINE_NAME LIKE '%usp%'
	ORDER BY ROUTINE_NAME;
	
	open c1
	fetch next from c1 into @vchRoutine_Name, @vchSQL

	While @@fetch_status <> -1
		begin

			SET @vchSQL = REPLACE(@vchSQL, 'CREATE PROC [dbo].[' + @vchRoutine_Name + ']', ' ');
			SET @vchSQL = REPLACE(@vchSQL, 'CREATE PROCEDURE [dbo].[' + @vchRoutine_Name + ']', ' ');
			SET @vchSQL = 'CREATE PROC ' + @vchRoutine_Name + ' ' + CHAR(13) + @vchSQL;
			SET @vchSQL = REPLACE(@vchSQL, 'Key_Indicators_0', 'Key_Indicators');
			
			IF NOT EXISTS(SELECT 1 FROM Key_Indicators.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = @vchRoutine_Name)
			BEGIN
				
				select LEN(@vchSQL);
				
				PRINT @vchSQL;
				
				exec(@vchSQL)
			END
			
			fetch next from c1 into @vchRoutine_Name, @vchSQL
		end
		
	close c1
	deallocate c1

Open in new window

Avatar of Eric3141
Eric3141
Flag of Afghanistan image

ASKER

PS:  The code above successfully creates the procs from the other database that are shorter.  For the shorter ones it works fine but for the longer ones then the dynamic SQL gets truncated and does not contain the whole procedure definition.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
      FROM Key_Indicators_0.INFORMATION_SCHEMA.ROUTINES

Is only returning part of the routine definition.  It seems that there is some setting in SSMS that needs to be changed.
Try using

EXEC sp_HelpText 'your procedure name'

instead - I think the way using the info schema chops the text, as you've found, when it's too long

hth

Mike
Mike,

That seems like it might work except that I'm getting a zero (0) along with the proc code.

exec @vchSQL = Key_Indicators_0.dbo.sp_helptext @vchRoutine_Name;
				
print @vchSQL

Open in new window


Results:
0

sp_helptext puts the text of the proc on-screen but the proc definition is not getting into the variable @vchSQL.   @vchSQL has datatype = varchar(max).

Ideas?
Found out why.  From an on-line article:

The assignment usage you're trying only takes the RETURN value and puts it into a variable. The sp_helptext proc returns the text via a SELECT statement. So, assuming the sp_helptext call works, it RETURNs a value of 0. So if you do #1 above, you'll simply get '0' in @text.
ASKER CERTIFIED SOLUTION
Avatar of Eric3141
Eric3141
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I thought you knew that! It's been around even in the Enterprise Manager of SQL Server 7! Sorry!!! I thought you needed it especially into code for some reason. Boy do I feel dumb :)
I came up with the solution for this.