Eric3141
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?
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
ASKER
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM Key_Indicators_0.INFORMATI ON_SCHEMA. ROUTINES
Is only returning part of the routine definition. It seems that there is some setting in SSMS that needs to be changed.
FROM Key_Indicators_0.INFORMATI
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
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
ASKER
Mike,
That seems like it might work except that I'm getting a zero (0) along with the proc code.
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?
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
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?
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :)
ASKER
I came up with the solution for this.
ASKER