• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

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

0
Eric3141
Asked:
Eric3141
  • 6
  • 2
1 Solution
 
Eric3141Author Commented:
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.
0
 
Eric3141Author Commented:
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.
0
 
DcpKingCommented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Eric3141Author Commented:
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?
0
 
Eric3141Author Commented:
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.
0
 
Eric3141Author Commented:
Oh my gosh.  In SQL 2008 R2 there is a tool called Generate Scripts under the Tasks menu which I can use to create a script to create every proc in the database.  I route it to the clipboard, go to the target db and open a query window, paste the script and run it.  
Voila!  All the procs have been copied from the source db to the target db.
0
 
DcpKingCommented:
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 :)
0
 
Eric3141Author Commented:
I came up with the solution for this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now