Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-09-05
8
Medium Priority
?
621 Views
Last Modified: 2012-09-11
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
Comment
Question by:Eric3141
  • 6
  • 2
8 Comments
 
LVL 2

Author Comment

by:Eric3141
ID: 38369375
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
 
LVL 2

Author Comment

by:Eric3141
ID: 38369872
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 38370612
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 2

Author Comment

by:Eric3141
ID: 38372255
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
 
LVL 2

Author Comment

by:Eric3141
ID: 38372314
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
 
LVL 2

Accepted Solution

by:
Eric3141 earned 0 total points
ID: 38373868
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 38374858
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
 
LVL 2

Author Closing Comment

by:Eric3141
ID: 38386059
I came up with the solution for this.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 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