Datensätze kopieren

[inoculator]

Hallo Team,
Ich muss Datensätze innerhalb einer Tabelle kopieren und verzweifele.
Bitte lest erst zuende, bevor Ihr standart Antworten gebt. Ich habe schon viele Lösungsversuche gelesen, die aber alle nichts brachten.

zunächst einmal die Situation:
MS SQL2000 auf Windows 2003 maximaler Patchstand März 2008 ENU
Die Datenbank speißt ein ASP-Web unter IIS6 mit Daten.

Nun die Lasten:
Berechtigungen werden ausschließlich auf den Stored Procedures vergeben.
Nur Entwickler und eine kleine Gruppe von Reportingleuten dürfen an die Tabellen per Insert/Select und Update.

Die Stammdatentabelle wird variabel an die Aufgaben angepasst. Je nach Kunde können neue Felder hinzukommen oder -bei wegfall des Kunden; wieder verschwinden.

Ein Datensatz umfasst etwa 250 Eigenschaften, welche in etwa 10 Haupt- und 40 referenztabellen zusammengefasst sind.

Es gibt auf der einen Seite die Möglichkeit, einen Datensatz von grund auf neu anzulegen. Das macht aber selten Sinn.
Meistens wird ein beliebiger Datensatz als Template verwendet und die abweichenden Daten dann angepasst.

Nun die Aufgabe an der ich im augenblick scheitere:
Ich muss eine Stored procedure schreiben, welche einen beliebigen Datensatz kopiert, ohne dabei auf einen statischen Select-befehl mit einer Spaltenliste zurückzugreifen.

Hier mein bisheriger Ansatz, der allerdings immer noch rechte auf der Tabelle benötigt:

set @table='[TabellemitDaten]'
	declare mycur cursor
	for
	select COLUMN_NAME
	from INFORMATION_SCHEMA.COLUMNS
	where TABLE_NAME = @table
	order by ORDINAL_POSITION
	OPEN mycur
	--jump ID (recordidentifier)
	 FETCH NEXT FROM mycur INTO @strFieldDef
	--jump servername (first changingfield)
	 FETCH NEXT FROM mycur INTO @strFieldDef
	--get first valid field
	 FETCH NEXT FROM mycur INTO @strFieldDef
	 set @strSQL='insert into [' + @table + '] select ' +char(39) + @newserver +char(39)+ ' as ServerName, ' + @strFieldDef
	 FETCH NEXT FROM mycur INTO @strFieldDef
	 WHILE (@@FETCH_STATUS = 0)
	 BEGIN
	  SET @strSQL = @strSQL + ', [' + @strFieldDef + ']'
	  FETCH NEXT FROM mycur INTO @strFieldDef
	 END
	 SET @strSQL = @strSQL + ' from [' + @table + '] where servername=' +char(39)+ @oldserver+char(39)
	CLOSE mycur
	deallocate mycur
	exec(@strSQL)
	select @newsid=@@identity

Wie ihr seht, baue ich ein insert-select-statement auf, welches die Spaltenliste dynamisch erstellt.
Leider baut der EXEC-Befehl nicht auf der ACL der Procedure auf, sondern will auf der Tabelle die Rechte haben -was nach Lastenbeschreibung nicht erlaubt ist.

Ein zweiter Ansatz war das hier:

 .....
SET @strSQL = @strSQL + ' from [' + @table + '] where servername=' +char(39)+ @oldserver+char(39)
	CLOSE mycur
	deallocate mycur

	set @strSQL='alter procedure web_function_copyserver_sub as ' + @strSQL + ' return @@identity'
	exec(@strSQL)
	exec @newsid=web_function_copyserver_sub

Hier war die Idee den select in eine SP einzupacken. Problem jetzt ist, das ich dem Benutzer kein ALTER Procedure-Recht auf eine SP geben kann.

Ein weiterer Lösungsansatz, welcher mir im Kopf schwebt, ob ich in einer SP einen eigenen Benutzer aufrufen -sozusagen ein eigenes login; kann?

Gibt es noch einen Befehl, welchen ich übersehe?

Welche Vorschläge habt Ihr?

Danke und Gruss

carsten

[Andreas Kosch]

Hallo,

das grundsätzliche Problem besteht darin, dass das ownership chaining einer Stored Procedure immer dann nicht mehr greift, wenn innerhalb der SP dynamisches SQL über EXEC oder sp_executesql ausgeführt wird. Das Problem kann auf 2 verschiedenen Wegen gelöst werden, wobei beide Wege jedoch erst ab dem MS SQL Server 2005 zur Verfügung stehen:

1. Die gespeicherte Prozedur wird mit einem Zertifikat signiert, wobei dieses Zertifikat die Zugriffsrechte an den Basistabellen erhält.
2. Die gespeicherte Prozedure wird über EXECUTE AS im Kontext eines anderen Benutzerkontos ausgeführt (CREATE PROCEDURE spMeinBeispiel WITH EXECUTE AS 'Benutzer' AS ...).

Gibt es noch einen Befehl, welchen ich übersehe?

Wenn der Einsatz des MS SQL Server 2005 aus irgend einem Grund nicht in Frage kommt, bleibt der Ausweg übrig, die Routine in ein Programmmodul zu verlegen, das generell unter einem bestimmten Benutzerkonto ausgeführt wird. Da Windows 2003 Server verwendet wird, bieten sich die Komponentendienst (alias COM+ alias .NET Enteprise Services) an. Wenn die DLL (Win32 oder .NET) mit dem Aufruf der SP als COM+ Serveranwendung auf dem Windows 2003 Server installiert wird, kann im Eigenschaftsdialog der COM+-Anwendung das Benutzerkonto definiert werden, unter dem dieses Modul ausgeführt wird. Die im Benutzerkontext ausgeführte aufrufende Stored Procedure ruft dann über sp_OACreate etc. die vom COM+-Objekt veröffentlichte Methode auf, um von dort aus die eigentlich für das Kopieren benötigte SP unter dem anderen Benutzerkonto auszuführen:

[highlight=SQL]
CREATE PROCEDURE spCallCOMplusObj
@sTXT VARCHAR(50) OUTPUT
AS
Declare @Object int, @hr int, @RetVal int, @iStatus int
SET @iStatus = 1
-- Objektinstanz erzeugen
Exec @hr = sp_OACreate '{0E9447C4-EF22-4570-B202-45D50D3B5EFB}', @Object OUTPUT
IF @hr = 0
BEGIN
SET @iStatus = 2
END
-- Methode des COM+ Objekts ausführen (dieses ruft dann die 2. SP auf)
Exec @hr = sp_OAMethod @Object, 'DoWork'
IF @hr=0
BEGIN
SET @iStatus = 4
END
-- COM+ Objektinstanz wieder zerstören
Exec @hr = sp_OADestroy @Object
Return @iStatus
[/highlight]

Das Upgrade zum MS SQL Server 2005 ist jedoch in jedem Fall die bessere Alternative ;-)

[Olaf Steffan]

Hallo,

ich hätte da auch noch eine Idee für den SQL-Server 2000 ohne Rechteproblem und ohne "fremde Hilfe" (z.B. COM). Die Idee ist folgende:
Erstelle Dir zuerst eine temporäre Tabelle mit einem Spaltennamen, der garantiert nicht in der Zieltabelle vorkommt...
create table #help (XYZ12345 int identity primary key) z.B.!
Nun erweiterst Du die Struktur der Tabelle mit Hilfe eines Cursors über information_schema.columns, wie Du es schon gemacht hast...
set @Exec = 'alter table #temp add [' + @Spalte + '] ' + @Datentyp (Pseudocode)
exec (@Exec)
Deine temporäre Tabelle wächst also. Parallel dazu kannst Du die später notwendigen SELECT-Statements basteln. So, nun kannst sogar die erste Hilfsspalte löschen, wenn Du willst. Fülle nun die Hilfstabelle mit den notwendigen Daten wieder über Exec(...), manipuliere sie und schließlich ab in die Zieltabelle:
insert into Zieltab
select * from #help
Das setzt natürlich voraus, dass Du in der Zieltabelle keine Identityspalten hast, die Dich zwingen würden eine Spaltenauflistung anzugeben. Spätestens hier wäre die Grenze erreicht. Allerdings habe ich in Deinem Ansatz gesehen, dass es wohl ohne geht.
Letztlich kannst Du auch noch einfacher diesen Weg versuchen:
set @Exec = 'select 1, ''Hallo'', cast(''20080101'' as datetime),...'
insert into Zieltab
exec (@Exec)

Gruß
Olaf