February 22, 2008 at 8:32 am
HI Guys
i have created a store procedure that will copy over procedures from one database to another database. i am using INFORMATION_SCHEMA.ROUTINES table to get the ROUTINE_DEFINITION of the procedure and then store into a varible and then execute on other database.
This store procedure is working fine upto a certain level. actually i have couple of procedure in which length of char is more then 8000. and i define a varible [varible] nvarchar(max). in this case my created procedure gives an error.
acutally what i m looking is that, is there any varible or a way that i can store a value more then 8000 into a local varible..
Thanks and looking forward.
-MALIK
February 22, 2008 at 8:40 am
VARCHAR(MAX) stored 2^31-1 or 2gb of data. Nvarchar(max) holds the same amount of data 2gb but actually holds half as many characters. This is because unicode characters require 2 bytes.
Try to declare you variable as VARCHAR(MAX) instead.
February 22, 2008 at 8:56 am
Thank for reply.. please find blow the code that i want to execute.. when the value goes more then 8000 charater it's give error and procedure can't able to copy over
declare @m nvarchar(max)
DECLARE ProcedureScripingCursor CURSOR FOR
SELECT routine_definition AS PROCODE from [Database].information_schema.routines
OPEN ProcedureScripingCursor
FETCH NEXT FROM ProcedureScripingCursor
INTO @m
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @m
FETCH NEXT FROM ProcedureScripingCursor
INTO @m
END
CLOSE ProcedureScripingCursor
DEALLOCATE ProcedureScripingCursor
February 22, 2008 at 9:54 am
I would simply use a different method. This one works quite well. Note that if you do not filter the routine type you will return functions and stored procedures. You can filter udf out by using routine_type = 'Procedure'.
exec master..xp_cmdshell
'bcp "Select routine_definition + '' GO '' from DBNAME.information_Schema.routines where ROUTINE_Type = ''PROCEDURE'' order by routine_name" queryout "C:\scripts.sql" -n -c -T -S SERVERNAME';
exec master..xp_cmdshell
'osql -i C:\scripts.sql -d DBNAME -E -S SERVER -n'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply