sp_refreshview

[dbfreak]

Hallo,

wir setzen den SQLSERVER 2008 ein.
Problem: Wir mussten unser Datenmodell ändern (Spalte in einer Tabelle wurde von 250 auf varchar(4000) geändert).

Anschließend wurde über die Views ein sp_refreshview gemacht, um die Metadaten zu aktualisieren. Da die Dependencies zu dieser Tabelle häftig sind, haben wir eine kleine Prozedur geschrieben.

alter procedure [dbo].[refresh_all_views]
as
begin
	DECLARE @view AS VARCHAR(255);
	DECLARE views_cursor CURSOR FOR 
	SELECT TABLE_SCHEMA + '.' +TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
	WHERE TABLE_TYPE = 'VIEW' 
	AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0 
	ORDER BY TABLE_SCHEMA,TABLE_NAME 
	OPEN views_cursor 
	FETCH NEXT FROM views_cursor 
	INTO @view 
	WHILE (@@FETCH_STATUS <> -1) 
	BEGIN
		BEGIN TRY
			begin transaction
			EXECute sp_refreshview @view;

			PRINT @view;
			commit transaction 
		END TRY
		BEGIN CATCH
			PRINT 'Error during refreshing view "' + @view + '".';
			Print error_message ()
			rollback transaction
		END CATCH;
	FETCH NEXT FROM views_cursor 
	INTO @view 
	END 
	CLOSE views_cursor; 
	DEALLOCATE views_cursor;
end

Die funkt auch. Problem, es werden bei einem View mal 6 Spalten abgeschnitten.
Also Rücksicherung Datenbank und sp_refreshview nur auf diesen View angewendet. -> Geht fein und die Spalten sind noch da!
Stellt sich die Frage, warum beim Durchlauf der Prozedur sp_refreshview anders tickt.
Hat hier jemand eine Antwort? Vielen Dank...

[defo]

Ich bin kein MSSQL Spezialist, also mal ne grobe Vermutung / Richtung:
Der zerstörte View wurde ggF in Vergangenheit geändert (mglw in Kombi mit 'Select *' statt 'Select <exlizite Feldliste>').
Besonders Select * in Views kann bei Modelländerungen in MSSQL problematisch sein. > Fehler 4502, die Dictionary Infos zum View sind geschrottet.

Ein Backup wird vermutlich zerstörte Dictionary Infos beheben, die refresh Funktion läuft danach durch.

Wenn es das war, kann man es natürlich nun schlecht nachvollziehen.
Aber in einer Testdb mit einem "Select *" View etwas rumspielen, könnte zu ähnlichen Fehlern führen.

[Ralf Jansen]

Eventuell liegts an der Refresh Reihenfolge. Wenn du Views refreshed die Views verwenden die selbst noch nicht refreshed wurden aber hätten sollen ....
* ist grundsätzlich eine schlechte Idee. Insbesondere auch im SQL Server wenn man entsprechende Views anfängt zu schachteln.

[dbfreak]

Hallo Ihr zwei und danke für die Antworten.
Leider...
Theorie1: Es ist kein select * sondern eine Feldliste..
Theorie2: Wenn das zutreffen würde, dann würde bei einem sp_refreshview nur auf diesen View der Fehler ja auch auftreten. Ist aber nicht so. Wir haben das nach Rücksicherung der DB getestet.


Aber, nun haben wir es. Die Ursache scheint folgende zu sein,
der View X wurde wahrscheinlich im SQL-Manager umbenannt (als Sicherung..X1) und dann wurde der View X erweitert neu creiert. Das heißt, der umbenannte View X1 steht noch in der sys.sql_modules Tabelle mit der alten Definition und dem "alten Viewnamen X". Da der alte View X1 in unserer Prozedur refresh_all_views offensichtlich nach dem "neuen View X" aufgerufen wird, wird die Definition einfach überschrieben. Also liebe Leute --VORSICHT mir dem umbenennen im SQL-Server-Manager. Das ist Soft und nicht echt...:=))

Zum Glück betrifft es bei uns nur den einen View und das in der Testumgebung.

gruß dbfreak