Mettre à jour le schéma et les lignes d'une transaction, SQL Server 2005

Je suis en train de mettre à jour un système hérité qui permet aux users de dicter une partie du schéma de l'une de ses tables. Les users peuvent créer et supprimer des colonnes de la table via cette interface. Ce système hérité utilise ADO 2.8, et utilise SQL Server 2005 comme database (vous ne voulez même pas savoir quelle database il utilisait avant de tenter de moderniser cette bête a commencé … mais je digresse. =))

Dans ce même process d'édition, les users peuvent définir (et modifier) une list de valeurs valides pouvant être stockées dans ces champs créés par l'user (si l'user souhaite limiter ce qui peut être dans le champ).

Lorsque l'user modifie la list des inputs valides pour un champ, s'il supprime l'une des valeurs valides, il est autorisé à choisir une nouvelle "valeur valide" pour mapper toutes les lignes ayant cette valeur (maintenant invalide), afin que ils ont maintenant une valeur valide à nouveau.

En regardant à travers l'ancien code, j'ai remarqué qu'il est extrêmement vulnérable de mettre le système dans un état invalide, parce que les changements mentionnés ci-dessus ne sont pas fait dans une transaction (donc si quelqu'un d'autre venait à mi-path du process propres changements … eh bien, vous pouvez imaginer les problèmes qui pourraient causer).

Le problème est que j'ai essayé de les mettre à jour sous une seule transaction, mais chaque fois que le code arrive à la partie où il change le schéma de cette table, tous les autres changements (mise à jour des valeurs dans les lignes, la table où le schéma a changé ou non … ils peuvent même être des tables complètement indépendantes) faites jusqu'à ce point dans la transaction semblent être silencieusement abandonnées. Je ne reçois aucun message d'erreur indiquant qu'ils ont été supprimés, et quand je commets la transaction à la fin aucune erreur n'est soulevée … mais quand je vais regarder dans les tables qui étaient censées être mises à jour dans la transaction, seules les nouvelles colonnes sont là. Aucune des modifications non-schéma effectuées sont enregistrées.

Regarder sur le net pour find des réponses s'est jusqu'à présent avéré être une perte de quelques heures … alors je me tourne vers l'aide. Quelqu'un at-il déjà essayé d'effectuer une transaction via ADO qui met à jour le schéma d'une table et met à jour les lignes dans les tables (que ce soit la même table ou d'autres)? Est-ce que ce n'est pas autorisé? Y a-t-il de la documentation qui pourrait être utile dans cette situation?

MODIFIER:

Ok, j'ai fait une trace, et ces commands ont été envoyées à la database (explications entre parenthèses)

(Je ne sais pas ce qui se passe ici, on dirait qu'il crée une procédure stockée temporaire …?)

declare @p1 int set @p1=180150003 declare @p3 int set @p3=2 declare @p4 int set @p4=4 declare @p5 int set @p5=-1

(Retrait de la table contenant les informations de définition pour les champs générés par l'user)

 exec sp_cursoropen @p1 output,N'SELECT * FROM CustomFieldDefs ORDER BY Sequence',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 go

(Je pense que mon code itérait à travers la list d'entre eux ici, en saisissant les informations actuelles)

 exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,1025,1,1 go exec sp_cursorfetch 180150003,1028,1,1 go exec sp_cursorfetch 180150003,32,1,1 go

(Cela semble être l'endroit où j'entre datatables modifiées pour les définitions, je les passe en revue et je mets à jour les changements survenus dans les définitions pour les champs personnalisés eux-mêmes)

 exec sp_cursor 180150003,33,1,N'[CustomFieldDefs]',@Sequence=1,@Description='asdf',@Format='U|',@IsLookUp=1,@Length=50,@Properties='U|',@Required=1,@Title='__asdf',@Type='',@_Version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,N'[CustomFieldDefs]',@Sequence=2,@Description='give',@Format='Y',@IsLookUp=0,@Length=0,@Properties='',@Required=0,@Title='_give',@Type='B',@_Version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,N'[CustomFieldDefs]',@Sequence=3,@Description='up',@Format='###-##-####',@IsLookUp=0,@Length=0,@Properties='',@Required=0,@Title='_up',@Type='N',@_Version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,N'[CustomFieldDefs]',@Sequence=4,@Description='Testy',@Format='',@IsLookUp=0,@Length=50,@Properties='',@Required=0,@Title='_Testy',@Type='',@_Version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,N'[CustomFieldDefs]',@Sequence=5,@Description='you',@Format='U|',@IsLookUp=0,@Length=250,@Properties='U|',@Required=0,@Title='_you',@Type='',@_Version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,N'[CustomFieldDefs]',@Sequence=6,@Description='never',@Format='mm/dd/yyyy',@IsLookUp=0,@Length=0,@Properties='',@Required=0,@Title='_never',@Type='D',@_Version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,N'[CustomFieldDefs]',@Sequence=7,@Description='gonna',@Format='###-###-####',@IsLookUp=0,@Length=0,@Properties='',@Required=0,@Title='_gonna',@Type='C',@_Version=1 go exec sp_cursorfetch 180150003,32,1,1 go

(C'est là que mon code supprime le supprimé à travers l'interface avant que cette sauvegarde ne commence] … c'est aussi la seule chose pour autant que je sache qui se passe réellement pendant cette transaction)

 ALTER TABLE CustomizableTable DROP COLUMN _weveknown;

(Maintenant, si l'une des définitions a été modifiée de telle manière que les propriétés de la colonne créées par l'user doivent être modifiées ou que des index doivent être ajoutés / supprimés sur les colonnes, cela est fait ici avec une valeur par défaut cela n'avait pas encore de valeur pour la colonne donnée … notez que, pour autant que je sache, AUCUN de ce qui se passe réellement se produit lorsque la procédure stockée se termine.)

 aller
 SELECT * FROM sys.columns O object object_id = OBJECT_ID (N'CustomizableTable ') ET name =' __asdf '
 aller
 ALTER TABLE Personnalisable Table ALTER COLUMN __asdf VarChar (50) NULL
 aller
 IF EXISTS (SELECT * FROM sys.indexes O object object_id = OBJECT_ID (N '[dbo]. [CustomizableTable]') AND nom = N'idx ___ asdf ') CREATE INDEX NON CLUSTERED idx___asdf ON Tableau personnalisable ( 
 __asdf ASC) WITH (PAD_INDEX = NON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF);
 aller
 select * from IF EXISTS (SELECT * FROM sys.indexes O object object_id = OBJECT_ID (N '[dbo]. [Table personnalisable]') AND nom = N'idx ___ asdf ') CREATE INDEX NON CLUSTERED idx___asdf ON 
 CustomizableTable (__asdf ASC) WITH (PAD_INDEX = NON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF);
 aller
 UPDATE CustomizableTable SET [__asdf] = '' WHERE [__asdf] EST NUL
 aller
 SELECT * FROM sys.columns O WH object_id = OBJECT_ID (N'CustomizableTable ') AND name =' _give '
 aller
 ALTER TABLE PersonnalizableTable ALTER COLUMN _give Bit NULL
 aller
 IF EXISTS (SELECT * FROM sys.indexes O object object_id = OBJECT_ID (N '[dbo]. [CustomizableTable]') AND nom = N'idx__give ') DROP INDEX idx__give ON Tableau personnalisable WITH (ONLINE = OFF);
 aller
 UPDATE CustomizableTable SET [_give] = 0 WHERE [_give] EST NUL
 aller
 SELECT * FROM sys.columns O WH object_id = OBJECT_ID (N'CustomizableTable ') ET name =' _up '
 aller
 ALTER TABLE CustomizableTable ALTER COLUMN _up Int NULL
 aller
 IF EXISTS (SELECT * FROM sys.indexes O object object_id = OBJECT_ID (N '[dbo]. [CustomizableTable]') AND nom = N'idx__up ') DROP INDEX idx__up ON Tableau personnalisable WITH (ONLINE = OFF);
 aller
 UPDATE CustomizableTable SET [_up] = 0 WHERE [_up] EST NUL
 aller
 SELECT * FROM sys.columns O WH object_id = OBJECT_ID (N'CustomizableTable ') ET name =' _Testy '
 aller
 ALTER TABLE CustomizableTable AJOUTER _Testy VarChar (50) NULL
 aller
 IF EXISTS (SELECT * FROM sys.indexes O object object_id = OBJECT_ID (N '[dbo]. [CustomizableTable]') AND nom = N'idx__Testy ') DROP INDEX idx__Testy ON Tableau personnalisable WITH (ONLINE = OFF);
 aller
 UPDATE CustomizableTable SET [_Testy] = '' WHERE [_Testy] IS NULL
 aller
 SELECT * FROM sys.columns O WH object_id = OBJECT_ID (N'CustomizableTable ') ET name =' _you '
 aller
 ALTER TABLE CustomizableTable ALTER COLUMN _you VarChar (250) NULL
 aller
 SI EXISTE (SELECT * FROM sys.indexes O object object_id = OBJECT_ID (N '[dbo]. [CustomizableTable]') ET nom = N'idx__you ') DROP INDEX idx__you ON Tableau personnalisable WITH (ONLINE = OFF);
 aller
 UPDATE CustomizableTable SET [_you] = '' WHERE [_you] EST NUL
 aller
 SELECT * FROM sys.columns O WH object_id = OBJECT_ID (N'CustomizableTable ') ET name =' _never '
 aller
 ALTER TABLE PersonnalizableTable ALTER COLUMN _never DateTime NULL
 aller
 IF EXISTS (SELECT * FROM sys.indexes O object object_id = OBJECT_ID (N '[dbo]. [CustomizableTable]') AND nom = N'idx__never ') DROP INDEX idx__never ON Tableau personnalisable WITH (ONLINE = OFF);
 aller
 UPDATE CustomizableTable SET [_never] = '1/1/1900' WHERE [_never] EST NUL
 aller
 SELECT * FROM sys.columns O WH object_id = OBJECT_ID (N'CustomizableTable ') ET name =' _gonna '
 aller
 ALTER TABLE Personnalisable Table ALTER COLUMN _gonna Money NULL
 aller
 IF EXISTS (SELECT * FROM sys.indexes O object object_id = OBJECT_ID (N '[dbo]. [CustomizableTable]') ET nom = N'idx__gonna ') DROP INDEX idx__gonna ON Table personnalisable WITH (ONLINE = OFF);
 aller
 UPDATE CustomizableTable SET [_gonna] = 0 WHERE [_gonna] EST NUL
 aller

(Fermeture de la transaction …?)

 exec sp_cursorclose 180150003
 aller

Après tout ce qui précède, seule la suppression de la colonne se produit. Tout ce qu'il y a avant et après dans la transaction semble être ignoré, et il n'y avait aucun message dans la trace SQL pour indiquer que quelque chose s'est mal passé pendant la transaction.

Le code utilise un slider côté server, c'est ce que ces appels sont pour. Le premier set d'appels prépare / ouvre le slider. Puis aller chercher des lignes du slider. Enfin, fermant le slider. Ces sprocs sont analogues aux instructions T-SQL OPEN CURSOR, FETCH NEXT et CLOSE CURSOR.

Je devrais regarder de plus près (ce que je vais faire), mais je suppose qu'il se passe quelque chose avec le slider côté server, la transaction d'encapsulation et le DDL.

Quelques autres questions:

Voulez-vous utiliser des sliders côté server dans ce cas?
Les commands ADO utilisent-elles toutes la même connection active?

Mettre à jour:

Je ne suis pas vraiment sûr de ce qui se passe.

Il semble que vous utilisiez des sliders côté server afin que vous puissiez utiliser Recordset.Update () pour renvoyer les modifications au server, en plus d'exécuter des instructions SQL générées pour modifier le schéma et mettre à jour datatables dans les tables dynamics. En utilisant la même connection, dans une transaction explicite.

Je ne suis pas sûr de l'effet que les opérations du slider auront sur le rest de la transaction, ou vice-versa, et pour être honnête, je suis surpris que cela ne fonctionne pas.

Je ne sais pas quelle serait l'ampleur d'un changement, mais je reorderais de quitter les sliders côté server et de créer les instructions UPDATE pour vos mises à jour de table.

Désolé, je ne pourrais pas être plus utile.

BTW- J'ai trouvé les informations suivantes sur les appels sp_cursor:

http://jtds.sourceforge.net/apiCursors.html

Le comportement que vous décrivez est autorisé. Comment le code modifie-t-il le schéma? Construction de SQL à la volée et exécution via une command ADO? Ou en utilisant ADOX?

Si vous avez access au server de database, essayez d'exécuter une trace du profileur SQL tout en testant le scénario que vous avez décrit. Voyez si la trace enregistre des erreurs / annulations.