August 31, 2004 at 11:33 am
I'm trying to recompile all views, functions, triggers and SPS of one of my databases. I'm aware of sp_recompile for the sp and sp_refreshview for the views but as I understand they work only at next execution of the object. The problem is that I need the process to be executed right away because I want the information in sysdepends to be updated and then shipped in a documentation DB.
So I decided to write an sp that reads the text in syscomments, change the create statement to an alter statement and exec that code but I can't get that exec part to work :
(I've also considered dropping and recreating the objects but that causes other problems in the sysdepends table if the order in which they are created is not perfect)
CREATE PROCEDURE [dbo].[sp_RebuildDependencies]
AS
SET NOCOUNT ON
Declare @Temp as nVarChar(4000)
Declare @XType as varchar(3)
Declare @Colid as smallint
Declare @Start as int
Declare @CrLf as char (2)
Declare @Name as varchar(256)
Declare @Col01 as nVarChar(4000)
Declare @Col02 as nVarChar(4000)
Declare @Col03 as nVarChar(4000)
Declare @Col04 as nVarChar(4000)
Declare @Col05 as nVarChar(4000)
Declare @Col06 as nVarChar(4000)
Declare @Col07 as nVarChar(4000)
Declare @Col08 as nVarChar(4000)
Declare @Col09 as nVarChar(4000)
Declare @Col10 as nVarChar(4000)
Declare @Col11 as nVarChar(4000)
Declare @Col12 as nVarChar(4000)
Declare @Col13 as nVarChar(4000)
Declare @Col14 as nVarChar(4000)
Declare @Col15 as nVarChar(4000)
Declare @Col16 as nVarChar(4000)
Declare @Col17 as nVarChar(4000)
Declare @Col18 as nVarChar(4000)
Declare @Col19 as nVarChar(4000)
Declare @Col20 as nVarChar(4000)
Declare @AnsiNulls as nVarchar(100)
Declare @AnsiRewind as nVarchar(100)
Declare @id as int
Declare @Total as int
SET @CrLf = char(13) + char(10)
SET @AnsiRewind = @CrLF + 'GO' + @CrLF + 'SET QUOTED_IDENTIFIER OFF' + @CrLF + 'GO' + @CrLF + 'SET ANSI_NULLS ON' + @CrLF + 'GO' + @CrLF + @CrLF
--this query will be simplified if this process works
Declare objects_cur CURSOR LOCAL FAST_FORWARD
FOR Select id, (Select count(*) from (Select distinct D.id from dbo.sysdepends D inner join dbo.SysComments SC on SC.id = D.id and SC.encrypted = 0 where D.depid = O.id) A) as Total, O.XType, name
from dbo.sysobjects O where O.Status >= 0 and O.XType in ('FN', 'IF', 'P', 'TF', 'TR', 'V')
Order by case O.XType when 'FN' then 1 when 'IF' then 2 when 'TF' then 3 when 'V' then 4 when 'P' then 5 when 'TR' then 6 end, total desc
OPEN objects_cur
FETCH NEXT FROM objects_cur INTO @id, @Total, @XType, @Name
WHILE (@@fetch_status <> -1)
BEGIN
Declare code_cur CURSOR LOCAL FAST_FORWARD
FOR Select Colid, Text from dbo.syscomments where id = @id order by Colid
Open code_cur
Fetch next from code_cur into @Colid, @Temp
SET @AnsiNulls = (Select @Crlf + 'SET QUOTED_IDENTIFIER ' + case OBJECTPROPERTY(@id, N'ExecIsQuotedIdentOn') when 1 then 'ON' else 'OFF' end
+ @CrLF + 'GO' + @CrLF + 'SET ANSI_NULLS ' + case OBJECTPROPERTY(@id, N'ExecIsAnsiNullsOn') when 1 then 'ON' else 'OFF' end + @CrLF + 'GO' + @CrLF + @Crlf)
SET @Start = -1
SET @Col01 = ''
SET @Col02 = ''
SET @Col03 = ''
SET @Col04 = ''
SET @Col05 = ''
SET @Col06 = ''
SET @Col07 = ''
SET @Col08 = ''
SET @Col09 = ''
SET @Col10 = ''
SET @Col11 = ''
SET @Col12 = ''
SET @Col13 = ''
SET @Col14 = ''
SET @Col15 = ''
SET @Col16 = ''
SET @Col17 = ''
SET @Col18 = ''
SET @Col19 = ''
SET @Col20 = ''
WHILE @@fetch_status <> -1
BEGIN
if @Start = -1
BEGIN
SET @Start = CHARINDEX ('CREATE ', @Temp, 1)
IF @Start > -1
BEGIN
SET @Temp = SUBSTRING(@Temp, 1, @Start -1) + 'ALTER' + SUBSTRING(@Temp, @Start + 6, datalength(@Temp))
END
END
if @colid = 1
SET @Col01 = @Temp
else if @colid = 2
SET @Col02 = @Temp
else if @colid = 3
SET @Col03 = @Temp
else if @colid = 4
SET @Col04 = @Temp
else if @colid = 5
SET @Col05 = @Temp
else if @colid = 6
SET @Col06 = @Temp
else if @colid = 7
SET @Col07 = @Temp
else if @colid = 8
SET @Col08 = @Temp
else if @colid = 9
SET @Col09 = @Temp
else if @colid = 10
SET @Col10 = @Temp
else if @colid = 11
SET @Col11 = @Temp
else if @colid = 12
SET @Col12 = @Temp
else if @colid = 13
SET @Col13 = @Temp
else if @colid = 14
SET @Col14 = @Temp
else if @colid = 15
SET @Col15 = @Temp
else if @colid = 16
SET @Col16 = @Temp
else if @colid = 17
SET @Col17 = @Temp
else if @colid = 18
SET @Col18 = @Temp
else if @colid = 19
SET @Col19 = @Temp
else if @colid = 20
SET @Col20 = @Temp
else if @colid > 20
begin
print Object_name(@id) + ' was skipped because it''s over 80 000 characters'
goto CloseCurCode
end
Fetch next from code_cur into @Colid, @Temp
END
print (@AnsiNulls + @Col01 + @Col02 + @Col03 + @Col04 + @Col05 + @Col06 + @Col07 + @Col08 + @Col09 + @Col10 + @Col11 + @Col12 + @Col13 + @Col14 + @Col15 + @Col16 + @Col17 + @Col18 + @Col19 + @Col20 + @AnsiRewind + @Crlf)
--exec (@AnsiNulls + @Col01 + @Col02 + @Col03 + @Col04 + @Col05 + @Col06 + @Col07 + @Col08 + @Col09 + @Col10 + @Col11 + @Col12 + @Col13 + @Col14 + @Col15 + @Col16 + @Col17 + @Col18 + @Col19 + @Col20 + @AnsiRewind + @Crlf)
CloseCurCode:
CLOSE code_cur
DEALLOCATE code_cur
FETCH NEXT FROM objects_cur INTO @id, @Total, @XType, @Name
END
CLOSE objects_cur
DEALLOCATE objects_cur
SET NOCOUNT OFF
GO
if I do print (@AnsiNulls + @Col01 +...), then execute the printed statement manually in QA it works fine but when I do the exec (@AnsiNulls + @Col01 +...) I get a bunch of errors... here's a sample of a printed statement :
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue) - 2)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
here are the errors when I execute from the sp for that function :
Server: Msg 170, Level 15, State 1, Line 3
Ligne 3 : syntaxe incorrecte vers 'GO'.
Server: Msg 170, Level 15, State 1, Line 5
Ligne 5 : syntaxe incorrecte vers 'GO'.
Server: Msg 111, Level 15, State 1, Line 9
'ALTER FUNCTION' doit être la première instruction d'un lot de requêtes.
Server: Msg 137, Level 15, State 1, Line 13
La variable '@sDefaultValue' doit être déclarée.
Server: Msg 170, Level 15, State 1, Line 23
Ligne 23 : syntaxe incorrecte vers 'GO'.
Server: Msg 170, Level 15, State 1, Line 25
Ligne 25 : syntaxe incorrecte vers 'GO'.
Can anyone tell me why this is hapenning? (I think I could make it work if I made all the work on a client station but I don't want to send 10 mb of data back and forth on the network everytime I do this)
August 31, 2004 at 11:56 am
I forgot to tell that when I execute the printed statment I get nothing beyond Col01 printed (even id Col02 has text in it)... maybe that's another problem of its own or it may be connected but I got no clue how to check that out.
August 31, 2004 at 3:10 pm
Aside from the fact that doing something like that would make me really nervous, here are some possible issues I can see.
First, you would need to be carefull with quotes since any ' will terminate the dynamic SQL should you start using the EXEC instead of the Print. Also, your script will convert all occasions of CREATE to ALTER. If a comment anywhere mentions that they needed to "create" something it will have been changed to "ALTER" which would be undesirable.
Second, "print (@AnsiNulls + @Col01 " statement starts with an nvarchar(100) which I believe causes the "+ @Col01" to return an nvarchar result which is internally limited to 4000 characters. Since @Col01 was filled up to 4000 characters nothing else survices. In fact, if you print just @Col01 all by itself you will likely find that there are up to 100 bytes present at the very end that were missing from your given print statement's results (because the space was consumed by @AnsiNulls). Using N'' as the first component of the string might make it work as in "print ( N'' + @AnsiNulls + @Col01 ".
August 31, 2004 at 8:49 pm
Actually I replace only the first occurance of the "create", so if I replace a comment instead of the script then I get an error that the object already exists.
You are actually right about the print statement being blocked at 4000 characters. As for the nVarchar limitation I'm not too sure I understand your workaround. Could you or someone else elaborate on this (N''...)?
Also wouldn't it be a better idea to do a dts or vb app to do this.. since I'm already using 2 cursors anyway, performance would be about the same but I wouldn't be limited by the lenght of the string in VB which would greatly shorten and simplify the code?
September 1, 2004 at 6:35 am
You may be right about it being easier to use a VB app. Anyhow, apparently either my memory is failing me or 2000 works different than 7 for this (likely the former), but the leading constant doesn't appear to fix the issue. FYI, the leading N makes it a Unicode (a.k.a. nvarchar) string constant. The limiter is actually the print statement. If you convert it to an EXEC the truncation will not occur. Throw the following into Query Analyzer to see what I mean. The print will only produce the "A" row while the EXEC will produce both the "A" and "B" row.
declare @Temp1 varchar(8000)
declare @Temp2 varchar(8000)
set @Temp1 = 'Print ''' + REPLICATE( 'A', 7990 ) + '''
'
set @Temp2 = 'Print ''' + REPLICATE( 'B', 7990 ) + '''
'
print @Temp1 + @Temp2
EXEC( @Temp1 + @Temp2 )
September 1, 2004 at 7:31 am
Thanx for the clarifications, that will solve my problems on the short term.. but I think I'm gonna go back to vb to do this one since I already have an app that does some dba tasks like this one... it just won't be calling an sp this time but I guess I can live with that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply