un trigger bloque ma table [Résolu]

[ldiaz]

(sql server 2005)

Bonjour a tous,

j'ai creer un trigger qui doit ecrir dans un fichier txt lorsque un INSERT est realisé sur une table specifique.

J'ai une question et un probleme a vous soumettre.
La question est:
Le trigger porte sur la table_A qui se trouve sur la base B (par exemple)
Dois je creer le trigger en me connectant d'abord a la base de données oB u se trouve ma table_A?

Code :

1
2
3
4
5
6
USE [base B]
 
CREATE TRIGGER OVO_notification
ON Table_A
AFTER INSERT 
AS

Ou puis je creer le trigger connecté a Master et en faisant ceci:

Code :

1
2
3
4
CREATE TRIGGER OVO_notification
ON Base_B.Table_A
AFTER INSERT 
AS

Et le probleme est que lorsque je creer le trigger, la table_A n'est plus mis a jour par l'application...Plus d'insert. Essayant de comprendre, j'ai droper le trigger et la, toutes les transactions se sont insertées d'un coup.
Ensuite j'ai re creer le trigger et voila que ça recommence, plus d'insert.
Est il possible que le trigger bloque les inserts sur la table?

Je donne le code du trigger:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE TRIGGER OVO_notification
ON VE_event
AFTER INSERT 
AS
  DECLARE @cmd nvarchar(1024)
  DECLARE @OpC_MsgText nvarchar(1024)
  DECLARE @OpC_Severity nvarchar(20)
  DECLARE @View_EventID int
  DECLARE @View_EventType nvarchar(512)
  DECLARE @View_Module nvarchar(512)
  DECLARE @View_Severity nvarchar(512)
  DECLARE @View_EventMsg nvarchar(512)
  DECLARE @View_Time datetime
 
  SELECT @View_EventID = (SELECT EventID FROM INSERTED)
  SELECT @View_EventType = (SELECT EventType FROM INSERTED)
  SELECT @View_Module = (SELECT Module FROM INSERTED)
  SELECT @View_Severity = (SELECT Severity FROM INSERTED)
  SELECT @View_EventMsg = (SELECT ModuleAndEventText FROM INSERTED)
  SELECT @View_Time = (SELECT Time FROM INSERTED)
 
  SELECT @OpC_Severity =
    CASE
      WHEN @View_Severity ='INFO' THEN 'normal'
      WHEN @View_Severity ='WARNING' THEN 'warning'
      WHEN @View_Severity ='ERROR' THEN 'critical'
      WHEN @View_Severity ='AUDIT_SUCCESS' THEN 'normal'
      WHEN @View_Severity ='AUDIT_FAIL' THEN 'normal'
      ELSE 'critical'
    END;
 
  BEGIN TRY
    SET @OpC_MsgText = 'MSG: "' + @View_EventID + '|' + @View_EventType + '|' + @View_Module + '|' + @View_Severity + '|' + @View_EventMsg + '|' + @View_Time + '"'
    SET @cmd = 'echo ' + @OpC_MsgText + ' > E:\temp\VMwareView-events.txt'
    EXEC master..xp_cmdshell @cmd, no_output
  END TRY
  BEGIN CATCH
	IF (XACT_STATE()) = -1  
	BEGIN
		ROLLBACK TRANSACTION;
	END; 
  END CATCH;
GO

D'avance merci si vous avez des pistes.

[zinzineti]

il faut créer le trigger dans la base B.

Rapidement, j'ai constaté une petite coquille dans ton code :

Tu as déclaré la variable @View_EventID comme étant un entier, mais tu as oublié de la caster lors de la concaténation dans la chaine @OpC_MsgText. Il en est de même pour les autres variables non littérales.

Code :

1
2
DECLARE @View_EventID int
SET @OpC_MsgText = 'MSG: "' + @View_EventID + '|' + @View_EventType + '|' + @View_Module + '|' + @View_Severity + '|' + @View_EventMsg + '|' + @View_Time + '"'

Exemple renvoyant un message d'erreur

Code :

1
2
3
4
DECLARE @View_EventID int
DECLARE @OpC_MsgText nvarchar(1024)
SET @View_EventID = 1
SELECT @OpC_MsgText = 'MSG: "' + @View_EventID + '"'

Message d'erreur :

Code :

1
2
Msg*245, Niveau*16, État*1, Ligne*4
Échec de la conversion de la valeur varchar 'MSG: "' en type de données int.

[ldiaz]

Salut Étienne

Ça donnerait ceci alors? La syntaxe est OK ?

Code :

SET @OpC_MsgText = 'MSG: "' + CAST(@View_EventID AS nvarchar) + '|' + @View_EventType + '|' + @View_Module + '|' + @View_Severity + '|' + @View_EventMsg + '|' + CAST(@View_Time AS nvarchar)+ '"'

Tu crois que c'est cette erreur qui bloque l'insertion de nouvelles lignes dans ma table_A?
C'est incroyable non?

Merci pour tes conseils.

[zinzineti]

Envoyé par ldiaz

Salut Etienne

Ça donnerait ceci alors? La sintaxe ext ok?

Code :

SET @OpC_MsgText = 'MSG: "' + CAST(@View_EventID AS nvarchar) + '|' + @View_EventType + '|' + @View_Module + '|' + @View_Severity + '|' + @View_EventMsg + '|' + CAST(@View_Time AS nvarchar)+ '"'

Tu crois que c'est cette erreur qui bloque l'insertion de nouvelles lignes dans ma table_A?
C'est incroyable non?

Merci pour tes conseils.

Attention si tu utilises la fonction CAST (ou CONVERT) et que tu ne précises pas la longueur du type de données, alors il peut y avoir un problème si la longueur de la chaîne dépasse 30

Après correction de la procédure, supprime et recrée le trigger sur la base B puis fait le test

[ldiaz]

Ok
c'est ça l'erreur...

Code :

SQL Server blocked access TO procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component IS turned off AS part of the security configuration FOR this server. A system administrator can enable the USE of 'xp_cmdshell' BY USING sp_configure. FOR more information about enabling 'xp_cmdshell', see "Surface Area Configuration" IN SQL Server Books Online.

[zinzineti]

Envoyé par ldiaz

Ok
c'est ça l'erreur...

Code :

SQL Server blocked access TO procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component IS turned off AS part of the security configuration FOR this server. A system administrator can enable the USE of 'xp_cmdshell' BY USING sp_configure. FOR more information about enabling 'xp_cmdshell', see "Surface Area Configuration" IN SQL Server Books Online.

Il faut donc activer xp_cmdshell. Il faut exécuter ce lot de commandes :

Code :

1
2
3
4
5
6
7
8
9
10
11
--Activer les options avancées
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- Activer xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Prise en compte de la configuration
RECONFIGURE
GO

[ldiaz]

Bonjour

alors voici les dernieres nouvelles,
si je fais un INSERT a la manuel le trigger se declenche et creer le fichier txt..ok
Mais normalement c'est mon application qui doit creer les inserts et la ça bloque.
J'ai enlevé le rollback que j'avais placer dans la section de capture d'erreur.

C'est etrange, si je drop le trigger, toutes les transactions en attente s'inserent, et si je creer de nouveau le trigger, les transaction se rebloques.
Quelqu'un a deja vu ça? Comment puis-je debeuger un tel comportement?

D'avance merci

[zinzineti]

Peux-tu montrer le bout de code de ton appli qui effectue l'INSERT ?

une remarque à propos de cette ligne

Code :

SET @cmd = 'echo ' + @OpC_MsgText + ' > E:\temp\VMwareView-events.txt'

Avec ça tu auras toujours une ligne dans le fichier E:\temp\VMwareView-events.txt

par contre en faisant

Code :

SET @cmd = 'echo ' + @OpC_MsgText + ' >> E:\temp\VMwareView-events.txt'

tu peux avoir toute l'historique dans le fichier.

[ldiaz]

Salut

je oeux pas te montrer le code qui fait l'insert car il s'agit de de WMWARE, lorsque tu entre dans l'admin web ça laisse un EVENT dans la base SQL server, et ce que l'on cherche c'est detecter les EVENT et les relier avec le system d'alert OVO de HP.
Ce qui explique le fichier txt avec une seule ligne, c'est exactement ce que l'on recherche car OVO peu lire une ligne de txt et recuperer le contenu pour gerer les alertes.

Je vais essayer avec un trigger plus simple d'une seule variable, et qui ecrit dans le txt.

[elsuket]

Bonjour,

Admettons maintenant que votre table subisse un grand nombre d'INSERT ...
Cela m'étonnerait beaucoup que votre base de données tienne le coup en terme de vitesse de traitement ...

J'aurai plutôt vu un travail de l'Agent SQL Server, planifié pour s'exécuter toutes les minutes, qui viendrait lire cette table pour en extraire les données dans un fichier.

On peut faire cela avec BCP, qui exécute ce style de tâche à une vitesse vertigineuse, ou avec un package SSIS.
Cela évite par ailleurs d'ouvrir la brèche de xp_cmdshell ...

@++

[ldiaz]

Bonjour

ha ok une nouvelle voie.
J'ai 2 questions par rapport a ce que tu dit.

Existe t-il des documents qui parlent de cette breche de securité de xp_cmdshell?
Et d'autre part pourrais tu pointer vers la doc des 2 outils que tu preconise?
BCP et SSIS, ça m'aiderais bien pour continuer.

D'avance merci

[elsuket]

Bonjour,

Existe t-il des documents qui parlent de cette breche de securité de xp_cmdshell?

Je ne crois pas; on trouvera probablement des blogs qui en parlent
En tout cas, on peut toujours faire :

Code :

EXEC xp_cmdshell 'FORMAT S:'

Et le résultat peut ne pas faire rire du tout ...
De la même façon je suppose qu'on peut faire des attaques par injection en tentant d'exécuter une telle commande ... ou d'autres

Et d'autre part pourrais tu pointer vers la doc des 2 outils que tu preconise?

Voici la documentation de l'utilitaire BCP

Pour SSIS, Fleur-Anne Blain en a fait une très bonne introduction ici.
Je pense que cela devrait suffire pour réaliser l'export

@++

[Ptit_Dje]

Pour ajouter une autre possibilite:

- Lancer une fenetre sqlps.exe (ou executer l'executable - Commande dos et ecrire: sqlps)
- Utiliser la commande suivante:
invoke-sqlcmd -ServerInstance <instanceName> -Query <la Query> | export-csv <fichier de destination>

Ca realise un export en csv de la query passee.

Il est aussi possible de passer un script directement a sqlps.exe...

[elsuket]

Ha classe !

Un petit billet ?

@++

[ldiaz]

Salut a tous,
alors j'ai essayé cette sintaxe:

Code :

1
2
3
PS SQLSERVER:\> invoke-sqlcmd -ServerInstance "MSSQLSERVER1" -Query "use [VMware
 View Events];select * from VE_event where EventID=(select MAX(EventID)from VE_e
vent)" | export-csv "E:\temp\prueba.csv"

Et j'ai cette erreur, j'ai verifier le nom de l'instance avec ceci:

Code :

SELECT SERVERPROPERTY ('InstanceName')

Je suppose que c'est correct.


Et l'erreur:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Invoke-Sqlcmd : A network-related OR instance-specific error occurred while est
ablishing a connection TO SQL Server. The server was NOT found OR was NOT acces
sible. Verify that the instance name IS correct AND that SQL Server IS configur
ed TO allow remote connections. (provider: Named Pipes Provider, error: 40 - Co
uld NOT open a connection TO SQL Server)
At line:1 char:14
+ invoke-sqlcmd <<<<  -ServerInstance "MSSQLSERVER1" -Query "use [VMware View E
vents];select * from VE_event where EventID=(select MAX(EventID)from VE_event)"
 | export-csv "E:\temp\prueba.csv"
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlExcept
   ion
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.
   PowerShell.GetScriptCommand
 
Invoke-Sqlcmd :
At line:1 char:14
+ invoke-sqlcmd <<<<  -ServerInstance "MSSQLSERVER1" -Query "use [VMware View E
vents];select * from VE_event where EventID=(select MAX(EventID)from VE_event)"
 | export-csv "E:\temp\prueba.csv"
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserExceptio

Vue que l'instance name est correcte, je suppose que le probleme se situe avec l'autorisation de conection REMOTE, non?
Comment puis je etre sur?

A et dans ma Query remarquez que je place le use [base name] avant le select...Je sais pas trop si je peux faire comme ça.

D'avance merci

[ldiaz]

J'ai aussi essayé avec BCP et je n'arrive pas a pointer sur ma base, c'est un nom de base avec des espaces:

Code :

1
2
3
4
5
6
7
8
9
C:\Users\Administrator.ATCLAB>bcp "select * from [VMware View Events].VE_event w
here EventID=(select MAX(EventID)from VE_event)"  queryout "Jarrod Rana.dat" -T
-c
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object nam
e 'VMware View Events.VE_event'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could
 NOT be prepared.

Les crochets ça n'a pas l'air de plaire ici...

[zinzineti]

Envoyé par ldiaz

J'ai aussi essayé avec BCP et je n'arrive pas a pointer sur ma base, c'est un nom de base avec des espaces:

Code :

Les crochets ça n'a pas l'air de plaire ici...

pour contourner les problèmes de crochets et de parenthèses, tu peux créer une vue SQL et l'utiliser dans le select du BCP.
autre chose, il faut indiquer le chemin absolu du fichier de sortie.
A+

[ldiaz]

Salut etienne
je comprends pas trop, une vue SQL tu dis?
Une vue dans mon jargon c'est une representation en memoire, de données extraites par une query, donc la vue appartient a une base.schema.
Dans ce cas, je dois quand meme preciser dans quelle base se trouve cette vue n'est ce pas?

Pourrais tu me donner un exemple?
D'avance merci

[ldiaz]

ok j'ai trouvé la forme de le faire:

Code :

bcp "select * from [VMware View Events].dbo.VE_event where EventID=(select MAX(EventID)from [VMware View Events].dbo.VE_event)"  queryout "E:\temp\ovoman.dat" -T -c

Excellent, je vous remercie a tous !!

[Ptit_Dje]

Envoyé par ldiaz

Salut a tous,
alors j'ai essayé cette sintaxe:

Code :

Et j'ai cette erreur, j'ai verifier le nom de l'instance avec ceci:

Code :

SELECT SERVERPROPERTY ('InstanceName')

Je suppose que c'est correct.


Et l'erreur:

Code :

Vue que l'instance name est correcte, je suppose que le probleme se situe avec l'autorisation de conection REMOTE, non?
Comment puis je etre sur?

A et dans ma Query remarquez que je place le use [base name] avant le select...Je sais pas trop si je peux faire comme ça.

D'avance merci

Quel est le nom de votre serveur ?
J'ai ete legerement incomplet dans la commande:
invoke-sqlcmd -ServerInstance <instanceName> -Query <la Query> | export-csv <fichier de destination>

Une commande efficace en powershell et disponible avec sqlps donc est la commande get-help qui s'utilise comme ceci par exemple pour vous retourner l'aide sur la commande Invoke-Sqlcmd:

Code :

get-help Invoke-Sqlcmd

Et pour en avoir des exemples d'utilisation:

Code :

get-help Invoke-Sqlcmd -examples

-------------------------- EXAMPLE 1 --------------------------
C:\PS>Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"


NB: Elsuket, je ferai un petit billet la dessus apres les fetes